Question

please solve this question excel solver and to excel file

MULTI-PERIOD PRODUCTION-SMOOTHINGG MODEL Example 2.4.4 on page 65 of Taha's book • A company will manufacture a product for the next four months: March, April, May, and June. The demands for each month are 520, 720, 520, and 620 units, respectively. • The company has a steady work force of 10 employees but can meet fluctuating production needs by hiring and firing temporary workers at the beginning of each month, if necessary. • The extra costs of hiring and firing in any month are $200 and $400 per worker, respectively. A permanent worker can produce 12 units per month, and a temporary worker, lacking comparable experience, only produce 10 units per month. • The company can produce more than needed in any month and carry the surplus over to a succeeding month at a holding cost of $50 per unit per month. 27

Answer 1

Set up Excel model as follows:

A B с F H Month Demand Production Hired Fired 1 520 720 520 620 2,380 120 120 120 120 480 2 3 March 4 April 5 May 6 June 7 Total 8 Unit Cost 9 Total Cost 10 11 12 13 D E Ending Temp Inventory workers 0 -400 0 -1,000 0 -1,400 0 -1,900 0 -4,700 0 $ 50 -235000 0 $ 200 0 0 $ 400 0 Total cost of this plan = -235000

EXCEL FORMULA:

A B с D E F G Month Demand Production Ending Inventory Temp workers Hired Fired 1 2 0 3 March 520 =10*12+E3*10 =D2-B3+C3 4. April 720 =10*12+E4*10 =D3-B4+C4 5 May 520 =10*12+E5*10 =D4-B5+C5 6 June 620 =10*12+E6* 10 =D5-B6+C6 7 Total -SUM(B3:36) -SUM(C3:C6) -SUM(D3:06) 8 Unit Cost 50 9 Total Cost =D7*D8 10 =E2+F3-G3 =E3+F4-64 =E4+F5-G5 =E5+F6-G6 =SUM(E3:56) =SUM(F3:F6) =SUM(G3:66) 200 400 =F7*F8 =G7*G8 11 Total cost of this plan = =SUM(D9:69) 12 13

Set up Solver Parameters as follows:

Solver Parameters Х Set Objective: $G$11 To: Max Min Value Of: 0 By Changing Variable Cells: $F$3:$G$6 Subject to the Constraints: $D$3:$D$6 >= 0 $F$3:$G$6 = integer > Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 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. Help Solve Close

Click Solve to generate the solution.

After that, values appear automatically in variable (yellow colored) cells

A B с F G H Month Demand Production Hired Fired 1 3 2 March 4 April May 6 June 7 Total 8 Unit Cost 9 Total Cost 10 LO 520 720 520 620 2,380 D Ε Ending Temp Inventory workers 0 100 50 0 50 50 45 0 45 150 190 $ 50 7500 620 620 570 570 2,380 50 0 0 0 50 $ 200 10000 0 0 5 0 5 $ 400 2000 11 Total cost of this plan = 19500 12 13 4 Solver Results Х 15 16 Solver found a solution. All Constraints and optimality conditions are satisfied. 17 of Reports Answer Sensitivity Limits Keep Solver Solution O Restore Original Values Return to Solver Parameters Dialog Outline Reports 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 OK Cancel Save Scenario... Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution.

Click OK

Total cost of the plan = $ 19,500