Question: I just solved this job assignment problem where I had to use excel and solver to get the answer. I was wondering if I solved

I just solved this job assignment problem where I had to use excel and solver to get the answer. I was wondering if I solved it correctly. Below I attached the problem, the data for the problem, my spreadsheet, my spreadsheet with formulas, and my solver.

Problem:

Job assignment data Job times Job 1 Job2 Job3 Job4 Person 1 29 30 30 Person 2 29 30 16 Person 3 18 21 18 18 Person 4 21 25 Person 5 24 15 26 Five employees are available to perform four jobs. The time it takes each person to perform each job is given in the file P05_50.xlsx. Determine the assignment of employees to jobs that minimizes the total time required to perform the four jobs. (A blank indicates that a person cannot do that particular job. Also, assume that no person can do more than one job.) A B C D E F G 1 Job assignment data 2 3 Job times 4 Job 1 Job 2 Job 3 Job 4 5 Person 1 29 30 30 6 Person 2 29 30 16 7 Person 3 18 21 18 18 8 Person 4 21 25 9 Person 5 24 15 26 10 11 12 Job Assignment 13 Performed 14 Job 1 Job 2 Job 3 Job 4 Row Sum 15 Person 1 16 Person 2 OHO 17 Person 3 OOOOH HOOOHO 18 Person 4 0 HPOOOO Person 5 20 Column Sum 21 Objective Total time 16A B C D E F 1 Job assignment data N 3 Job times 4 Job 1 Job 2 Job 3 Job 4 5 Person 1 29 30 30 6 Person 2 29 30 16 Person 3 18 21 18 18 8 Person 4 21 25 9 Person 5 24 15 26 10 11 12 Job Assignment 13 Performed 14 Job 1 Job 2 Job 3 Job 4 Row Sum 15 Person 1 16 Person 2 17 Person 3 OOOHO OOOHO HOOOO 18 Person 4 19 Person 5 1 20 Column Sum =SUM(B15:B19) =SUM(C15:C19) =SUM(D15:D19) =SUM(E15:E19) 21 Objective Total time =SUMPRODUCT(B5:E9,B1X V fx 1 B C D F F G H Set Objective: $C$21 Job assignment data To: Max . Min O Value Of: 0 Job times By Changing Variable Cells: Job 1 Job 2 Job 3 Job 4 $B$ 15:$E$ 19 Person 1 29 30 30 Person 2 29 30 16 Subject to the Constraints: Person 3 18 21 18 18 $B$20:$E$20 = 1 Add Person 4 21 25 $F$15:$F$19 = 1 Person 5 24 15 26 N8 Change Delete Job Assignment Performed Job 1 Job 2 Job 3 Job 4 Row Sum Reset All Person 1 O Load /Save Person 2 Person 3 OOOHC HOOOOH Make Unconstrained Variables Non-Negative Person 4 HOOOH Person 5 Select a Solving Method: Simplex LP Options Column Sum Objective Total time Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non- smooth. Close Solve

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!

CASE its lose in ROSE TREE REGIONAL AIRLINE or ad- ying Decision Support Using Microsoft Excel Solver PREVIEW onse Tree Regional Airline provides passenger transportation services and small-parcel...

Solve a, b, c, and d It's been a year since Cellar Door opened and in spite of the global economic conditions, Cellar Door at Orange Wines has somehow defied the odds and has quickly become a...

Good Morning, This is the 3rd homework assignment I am requesting of you as you have did excellent on the two prior which I greatly appreciate. This is a new course that is starting today and I am...

I would like an excel doc with columns and rows showing. One doc with just answers , one doc with the formulas expande d. This is the only way I can understand the answers and how to do the problem....

Abacus SFX Located in Hollywood, Abacus SFX is a small startup specializing in creating digital special effects for science fiction movies. Abacus costs for a given film project, if accepted, include...

This is a individual assignment, which is due on 27th of jan, wed, 1pm. Can u help me to do it? Financial Accounting 2 ACG 27, Study Period 4, 2015 Case Study for Annual Report Assignment The...

Decision Support Using Excel Solver PREVIEW You are a new investment manager working for a giant mutual fund. You will have $50 million to invest. You want to buy securities that are profitable but...

Can you please help with lesson 5.1 and 5.2 file is attached FNCE 300/ECON 300: Assignment 2 Due: After completion of Lesson 8. Marks: 100 Credit Weight: 20% of final grade. Prepare your responses to...

Solve a, b, c, and d. Maximising Profit from Wine Sales Orange Wines makes and sells wine to suit a range of tastes from the less expensive bulk wine, for younger demographics, through to Premium...

A 1/4 -in drill rod was heat-treated and ground. The measured hardness was found to be 490 Brinell. Estimate the endurance strength if the rod is used in rotating bending.

What are the seven major steps involved with sampling?

The ledger of Pharoah Company at the end of the current year shows Accounts Receivable $ 1 4 5 , 0 0 0 , Credit Sales $ 8 5 2 , 0 0 0 , and Sales Returns and Allowances $ 2 3 , 0 0 0 . Journalize the...

Financial Statements of a Manufacturing Firm The following events took place for Sorensen Manufacturing Company during January, the first month of its operations as a producer of digital video...