Question

please showing working.. formulas

10. A company produces sofas. The manager wants to prepare a 0. am for the next six months using the following information: plan Month 2 3 456 160 150 160 180 170 140 recast demand For Cost Per Unit Regular time $100 Overtime $150 Part-time $120 Inventory, per month $10 Back order, per month $50 There are five workers, each making 30 sofas a month. The maximum number of sofas produced during overtime is 30 per month. Part-time workers can handle a maximum of 20 units per month. Beginning inventory is zero. Develop production plan that minimizes total cost. No ending inventory or back orders are desired at the end of month 6

Answer 1

Use the following Excel formulation to obtain the minimum cost plan:

Regular time Overtime Part-timeEnding OutputB.I. E.I. + Month BackorderTotal output Forecast production production production Invent Backorder 2 160 4 150 SUM(D5 F5) 15 (G4-H4)-G5+H5 160 180 4 170 SUM(D8 F8) I8+(G7-H7)-G8+H8 140 10 Totals SUM(C4:C9) SUM(D4 D9 SUM(E4 E-SUM(F4F SUM(G4:G9) SUM(H4 H9) 11 Cost/ unit 100 150 120 10 50 TOTAL COST SUM(D12H12) Cost 12 13 Regular time Overtime Part-time Month 14 capacity capacity capacity -5*30 30 20 15 -5*30 16 2 30 20 -5*30 17 3 30 20 -5*30 30 20 18 -5*30 30 20 19 5 -5*30 30 20 20

Set Objective $1$12 O Value Of: By Changing Variable Cells: Subject to the Constraints: SD$4: $F$9 $D$15:$F$20 $G$9:$H$90 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

Solution

Month	Forecast	Regular time production	Overtime production	Part-time production	Ending Inventory	Backorder	Total output	Output + B.I. - E.I. + Backorder
0					0	0
1	160	150	0	10	0	0	160	160
2	150	150	0	0	0	0	150	150
3	160	150	0	20	10	0	170	160
4	180	150	0	20	0	0	170	180
5	170	150	0	20	0	0	170	170
6	140	140	0	0	0	0	140	140
Totals	960	890	0	70	10	0
Cost/ unit		$100	$150	$120	$10	$50		TOTAL COST
Cost		$89,000	$0	$8,400	$100	$0		$97,500