Question

PP.52 A manufacturer of solid state drives (SSDs) has projected the next six months of demand to be as shown the table below Jan 48,00052,00060,00054,00058,00064,000 Feb Mar Apr May Jun Supply/Demand Info Beginning Forecast (demand) Regular production Overtime production Subcontract production Ending inventory Hired employees Fired employees Total employees Cost variables as as follows: 6,000 200
Show excel work

Answer 1

Febr 52000 -D2 D10 200 Janua March Ma une Total 2 Forecast (demand 3 Required 4 Regular 5 Overtime production 6 Subcontract production 7 Ending invento 8 Hired employees 9 Fired em 10 Total em 11 Total -2 C2-B7 C10 200 -G2 -G10 200 uction -F2 -H2 H10*200 duction E10 200 F10 200 SUM( C4:H4 -SUM(C5:H5 -SUM(C6:H6 6000 =SUM(C8:H8) -SUM(C9:H9) B10+C8-C9 C10+D8-D9 D10+E8-E9 -E10+F8-F9 -F10+G8-G9 G4 G5+G6 G10+H8-H9 H4+H5+H6 uction 13 Labour cost /hour 14 Labour cost/unit 15 overtime cost/ unit 16 subcontracting cost/unit 17 holdi 18 hirin 19 firin 20 21 Total labour hours/SSD 22 Regualr production units/em 23 Max regular production/month 24 Max overtime production/month 25 Max subcontract 26 27 Total cost 13 Regular production cost -814 14 Total overtime production cost 15 B15 Total subcontract cost 16 B16 Total holding cost -17 B17 Total hire cost-18 818 Total fire cost 19 B19 -B13 B21 31 12 2500 Total cost SUM(114:119 month 200 or production/month 5000 -SUMPRODUCT(14:19,B14:B19)

January February March Apri May June Total 2 Forecast (demand 3 Required production 4 Regular production 5 Overtime production 6 Subcontract production 7 Ending invento 8 Hired employees 9 Fired employees 10 Total employees 11 Total production 48000 52000 60000 54000 58000 64000 42000 52000 60000 54000 58000 64000 40000 40000 40000 40000 40000 40000240000 0 0 14000-34000-48000-66000-90000254000 0 0 200 200 200 200 200 200 200 40000 40000 40000 40000 40000 40000 13 Labour cost/hour 14 Labour cost/unit 15 overtime cost/unit 16 subcontracting cost/unit 17 holdirn 18 hirin 19 firin 20 21 Total labour hours/SSD 22 Regualr production units/employee/month 23 Max regular production/month 24 Max overtime production/month 25 Max subcontractor production/month 13 Regular production cost6240000 0 0 Total holding cost -3048000 26 Total overtime production cost Total subcontract cost 31 12 Total hire cost Total fire cost 0 Total cost 3192000 200 27 Total cost 3192000

Solver Parameters Set Objective SB$27 0: Max Value Of By Changing Variable Cells: SC$8:$H$9,SC$5:$H$6 Subject to the Constraints: SC$3:SHS3 SC$11:SH$11 SC$4:$H$4 <= 56000 SC$5:$H$54000 $C$6:$H$6 <= 5000 Add Change Delete Reset All ロMake unconstrained variables Non-Negative Select a Solving Simplex LP 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

January February MarchApril May June Total 2 Forecast (demand 3 Required production 4 Regular production 5 Overtime production 6 Subcontract production 7 Ending invento 8 Hired employees 9 Fired employees 10 Total employees 11 Total production 48000 52000 60000 54000 58000 64000 42000 52000 60000 54000 58000 64000 42000 52000 54000 54000 56000 56000314000 0 30004000 이 1000 01 5000 0 0 0 2000500012000 0 0 0 10 10 0 10 0 210 260 270270 280280 42000 52000 60000 54000 58000 64000 13 Labour cost/hour 14 Labour cost/unit 15 overtime cost /unit 16 subcontracting cost/unit 17 holdirn 18 hirin 19firin 20 21 Total labour hours/SSD 22 Regualr production units/employee/month 23 Max regular production/month 24 Max overtime production/month 25 Max subcontractor production/month 13 Regular production cost8164000 Total overtime production cos 136000 Total subcontract cost 372000 26 31 12 Total holding cost Total hire cost 200000 Total fire cost Total cost 8872000 2 200 27 Total cost 8872000

Regular production cost8164000 Total overtime production cost 136000 Total subcontract cost372000 C) Total holding cost Total hire cost 200000 Total fire cost C) Total cost 8872000