Question

Table below is a time-phased net requirements for Widgets over the next six weeks along with the capacity constraint. Week 1 2 3 4 5 6 Requirements 335 200 140 440 300 200 Capacity 600 600 600 400 200 200 The setup cost is $200 and the holding cost is $0.30 per unit per week. Accounting for the capacity constraint, the modified requirements schedule, with improvement step, is: Note: Enter your answer as integer. Week Requirement, y (Hint: You want to minimize the total holding + setup cost).

Answer 1

Let's solve using excel

Model is shown below

fix X Week A1 A В C D Е F G Н J Week Prod 1 Req Саp Inv Setup cost Inv cost 2 1 335 600 -335 0 -100.5 600 -160.5 3 2 200 -535 0 600 -675 4 3 140 0 -202.5 4 440 400 -1115 0 -334.5 200 6 5 300 -1415 0 -424.5 200 7 6 200 -1615 0 -484.5 8 9 10 Total cost -1707 11 12 13 14 Sheet1551 Ф Sheet1546 Sensitivity Report 91 Sheet1547 Sheet1549 Sheet1550 READY 190%

The formulas are shown below

fx A1 Week A C D E G Week Prod Setup cost 1 Req Сар Inv Inv cost -IF(D2>0,200,0) -E2*0.3 2 1 335 600 D2-B2 -E2+D3-B3 =IF(D3>0,200,0) -ЕЗ*0.3 3 2 200 600 =IF(D4>0,200,0) -E4*0.3 140 600 -E3+D4-B4 =IF(D5 0,200,0) - E4+D5-B5 -E5*0.3 5 4 440 400 6 5 =IF(D6>0,200,0) -E6*0.3 300 200 =E5+D6-B6 =IF(D7>0,200,0) E7*0.3 7 6 200 200 -E6+D7-B7 =SUM(F2:G7) 10 Total cost 11 12 13 14 15 16 17 18 19 Sheet1551 Sheet1546 Sensitivity Report 91 Sheet1547 Sheet1549 Sheet1550 READY 145% |으드 의

The parameters are shown below

fix Week Solver Parameters P Set Objective: A В C D E SBS10 Setup Week 1 Req Сар Prod Inv O Value Of: To: O Max O Min 2 1 335 600 -335 By Changing Variable Cells: 3 2 200 600 -535 SDS2:SDS7 -675 3 140 600 C Subject to the Constraints: SDS2:SDS7 SCS2:SCS7 SES2:SES7 0 Add 5 4 440 400 -1115 Change -1415 5 300 200 Delete 6 200 200 -1615 C Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method: GRG Nonlinear 10 Total cost -1707 Options Solving Method 11 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 12 13 Help Solve Close 14 Sheet1549 Sheet1546 Sensitivity Report 91 Sheet1547 Sheet1550 Sheet1551 ENTER 190% LO

The result is shown below. Answer is highlighted.

fx Week A1 A В C D F Н J Week Prod 1 Req Сaр Inv Setup costInv cost 2 1 335 600 335 0 200 0 600 3 2 200 200 0 200 0 Solver Results 140 4 3 140 600 280 200 42 Solver found a solution. All Constraints and optimality conditions are satisfied 400 200 30 4 440 400 100 Reports Answer 200 6 300 200 200 0 0 O Keep Solver Solution Sensitivity Limits 200 7 6 200 200 200 0 0 O Restore Original Values 8 Return to Solver Parameters Dialog Outline Reports Cancel OK Save Scenario... 10 Total cost 1272 Solver found a solution. All Constraints and optimality conditions are satisfied. 11 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. 12 13 14 Sheet1551 Sheet1546 Sensitivity Report 91 Sheet1547 Sheet1549 Sheet1550 190% READY