Question

PP.72 A manufacturer of solid state drives (SSDs) has projected the next six months of demand to be as shown the table below:

Supply/Demand Info	Beginning	Jan	Feb	Mar	Apr	May	Jun
Forecast (demand)		48,000	52,000	60,000	54,000	58,000	64,000
Regular production
Overtime production
Subcontract production
Ending inventory	6,000
Hired employees
Fired employees
Total employees	200

Cost variables as as follows:

Cost Variables
Labor cost/hour	$14
Overtime cost/unit	$32
Subcontracting cost/unit	$29
Holding cost/unit/month	$8
Hiring cost/employee	$2,500
Firing cost/employee	$4,200

Here is some additional relevant (capacity) information:

Capacity Information
Total labor hours/SSD	2
Regular production units/employee/month	200
Max regular production/month	56,000
Max overtime production/month	4,000
Max subcontractor production/month	5,000

Given the above information (and don't overlook beginning number of employees and inventory levels in the first table), create a combination production plan using Solver to determine the lowest cost solution by optimizing the set of trade-offs among hiring and firing, overtime and subcontractor production, and holding inventory.

What is the regular production cost (over the six months from January through June) for this optimized production plan? (Display your answer to the nearest whole number.)

What is the total overtime production cost for this production plan? (Display your answer to the nearest whole number.)

What is the total subcontract cost for this production plan? (Display your answer to the nearest whole number.)

What is the total holding cost for this production plan? (Display your answer to the nearest whole number.)

What is the total hire cost for this production plan? (Display your answer to the nearest whole number.)

What is the total fire cost for this production plan? (Display your answer to the nearest whole number.)

What is the total cost (sum of all costs) for this production plan? (Display your answer to the nearest whole number.)

Answer 1

Prepare an excel spreadsheet using given values and formulas 1 Supply/Demand Info 2 Forecast (demand 3 Regular production 4 Overtime 5 Subcontract production 6 Ending invento 7 Hired employees 8 Fired employees 9 Total employees 10 11 Cost calculations: 12 Regular production 13 Overtime 14 Subcontract 15 Ending invento 16 Hired employees 17 Fired emplovees 18 Feb inningJan Mar Total Jun 48,000 | 52,000 | 60,000 | 54,000 | 58,000 | 64,000 336,000 0,00040,000 40,000 40,000 40,00040,000 240,000 uction 6,000 2,000 -14,00034,00048,000-66,000-90,000254,000 Total 1,120,000 1,120,000 1,120,000 1,120,000 1,120,000 1,120,000 6,720,000 uction uction 16000 -112000272000384000528000-7200002,032,000 Tota4,688,000 Formulas used

C: 1 Supply/Demand Info Beginning 2 Forecast (demand 3 Regular production 4 Overtime production 5 Subcontract production 6 Ending inventory 7 Hired employees 8 Fired employees 9 Total employees 10 11 Cost calculations: 12 Regular production 13 Overtime production 14 Subcontract production 15 Ending inventory 16 Hired employee 17 Fired employees Total Mar SUM(C2:H2) SUM(C3:H3) SUM(CA:H4 SUM(C5:H5) 6000 C3+C4+C5+B6-C2D3+D4+D5+C6-D2 E3+E4+E5+D6-E2 F3+F4+F5+E6-F2G3+G4+G5+F6-G2H3+H4+H5+G6-H2 SUM(C6:H6) SUM(C7H7) SUM(C8:H8) F9*200 G9*200 -E9*200 200 -B9+C7-C8 G9+H7-H8 Total SUM(C12:H12) -SUM(C13:H13) -SUM(C14:H14) -SUM(C15:H15) SUM(C16:H16) SUM(C17:H17) F3 *2*14 -F4 32 -C3*2*14 D3 *2 14 E3*2 14 -G3*2*14 H3 *2*14 -C7 2500 -C8*4200 D7 2500 D8 4200 H7 2500 H8 4200 -G8*4200 -F8 4200 Total-SUM(I12:117

Put the following values in solver window: Solver Parameters SI$18 Set Objective ○Value Of: To: .) Min Max By Changing Variable Cells: Subject to the Constraints: SC$3:SH$356000 $C$4:SHS4 4000 SC$5:SH$55000 $C$6:$H$6 >= 0 Add Change Delete Beset All Make Unconstrained Variables Non-Negative Select a Solving 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 The final output is shown below:

1 Supply/Demand Info Beginning Jan 2 Forecast (demand 3 Regular production 4 Overtime production 5 Subcontract production 6 Ending invento 7 Hired employees 8 Fired employees 9 Total employees 10 11 Cost calculations: 12 Regular productiorn 13 Overtime production 14 Subcontract production 15 Ending invento 16 Hired employees 17 Fired employees 18 Feb Mar Ma Jun Total Apr 48,00052,000 60,000 54,00058,000 64,000 336,000 42,00052,000 54,00054,00054,00054,000 310,000 5,000 15,000 1,000 1000 6,000 0 0 0 1,000 0 10 10 0 210 200 260 270 270 270 270 Total 1,176,000 1,456,000 1,512,000 1,512,000 1,512,000 1,512,0008,680,000 128000160,000 145000145000435,000 8,000 175,000 32000 145000 8000 25000 125000 25000 Total 9,458,000

Regular production cost- $8,680,000 Total overtime production cost $160,000 Total subcontract cost $435.000 Total holding cost $8,000 total hire cost $175,000 Total fire cost $0 Total cost $9,458,000