Aggregate Plan Assignment

K. Cunningham Furniture products needs to develop its aggregate schedule for sofa production. The cost accounting department has supplied the costs relevant to the aggregate plan and the marketing department has provided a four-quarter forecast. All are shown below.

Quarter	Forecast
1	1,400
2	1,200
3	1,500
4	1,300

Costs
Beginning inventory	0
Stock-out cost for backorders	$50
Inventory holding cost	$10 per unit held at the end of the quarter
Hiring workers	$400 per worker
Firing workers	$550 per worker
Unit material cost	$50
Regular wage rate	$30
Overtime wage rate	$45
Regular labor hours	8 hours per day 25 days per month
Maximum overtime labor hours	100 hours per worker per quarter
Labor hours required	5 hours per unit
Previous quarter demand	1500
Previous quarter output	1350
Subcontracting cost	$250
Current workforce	25 employees

Worksheet 1--Plan A: Develop a level plan that holds employment steady. Beginning workforce is 25. No subcontracting is allowed. Assume stock-outs at the end of the 4^th quarter are no greater than 100 units. If you hold employment steady is the result a level plan? Why/why not? If it is not a level plan, how do you recommend moving toward a level plan? Comment on the constraints used. Answer the questions and comment in a text box on this worksheet.

Worksheet 2--Plan B: Develop a chase plan by hiring and firing as necessary to hold down costs. Assume the stock-outs at the end of the 4^th quarter are no greater than 100 units. No overtime or subcontracting is allowed. Using a textbox on this worksheet, comment on the constraints used and the viability of the plan.Im not sure if I'm setting this up correctly

Decision Variables Number Hired, H Number Laid Off, Overtime Hours, o Ending Inventory, Backlogged Units, S. Subcontract Units, Focast Production Units, P, lo 0 lo 0 0 lo 0 JO 10 10 1400 1200 1500 1300 Costs 0 0 0 0 10 10 0 -SUM(G37:G48)*B9 JO 10 0 =SUM(E37:E40)* B15 0 JO JO 0 -SUM(F37:F40)*B10 10 lo 0 =SUM(H37:140)*B21 =SUM(C37:C40)*B11 -SUM(D37:040)*B11 jo -SUM(137:140)*813 Constraints 32 33 34 35 Quarter 36 37 1 38 2 39 3 40 4 41 42 43 44 45 Quarter 46 47 1 48 2 49 3 50 4 51 52 53 54 55 56 Production Overtime Limit Inventory Balance Capacity Workforce Size, w 1125 -B4B+C37-D37 -B474C-38-D38 -B484C39-039 B49+C40-D40 |ESUM(B47:650)*B25 -B47"WREFIE38WREF! -B48 MREFI4E39WREF! -B49 MREF4E4OREF! -B50'MREFI+MREFWREF! -B47*#REF! -B48*#REF! =B49*#REF! -B50*#REF! #REFI+F38 #REFI-HREF! #REF!4F394#REF!-B38-# =#REF!4F40+#REF!-B40- #REF! #REF! #REFI-#RE Decision Variables Number Hired, H Number Laid Off, Overtime Hours, o Ending Inventory, Backlogged Units, S. Subcontract Units, Focast Production Units, P, lo 0 lo 0 0 lo 0 JO 10 10 1400 1200 1500 1300 Costs 0 0 0 0 10 10 0 -SUM(G37:G48)*B9 JO 10 0 =SUM(E37:E40)* B15 0 JO JO 0 -SUM(F37:F40)*B10 10 lo 0 =SUM(H37:140)*B21 =SUM(C37:C40)*B11 -SUM(D37:040)*B11 jo -SUM(137:140)*813 Constraints 32 33 34 35 Quarter 36 37 1 38 2 39 3 40 4 41 42 43 44 45 Quarter 46 47 1 48 2 49 3 50 4 51 52 53 54 55 56 Production Overtime Limit Inventory Balance Capacity Workforce Size, w 1125 -B4B+C37-D37 -B474C-38-D38 -B484C39-039 B49+C40-D40 |ESUM(B47:650)*B25 -B47"WREFIE38WREF! -B48 MREFI4E39WREF! -B49 MREF4E4OREF! -B50'MREFI+MREFWREF! -B47*#REF! -B48*#REF! =B49*#REF! -B50*#REF! #REFI+F38 #REFI-HREF! #REF!4F394#REF!-B38-# =#REF!4F40+#REF!-B40- #REF! #REF! #REFI-#RE