I don't know if i'm setting this up correctly its and aggregated plan assignment and we are using level and chance
Quarter Focast 1 1400 2 1200 3 1500 4 1300 Given Info Beginning inventory 0 units 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 units Regular wage rate 30 per hour Overtime wage rate 45 per hour Regular labor hours hours per day 25 days per month Maximum overtime labor hours 100 hours per worker per quarter Labor hours required hours per unit Previous quarter demand 1500 Previous quarter output 1350 Subcontracting cost 250 Current workforce 25 employees Calculated Parameters Regular time labor cost per worker/ month Regular time production rate units/worker/month32 33 Decision Variables Overtime Ending Backlogged Subcontract Production 34 Number Number Units, S Units, C Units, P 35 Quarter Focast Hired, H. Laid Off, Lr Hours, Or Inventory, I, 36 37 1 1400 38 2 1200 0 39 3 1500 40 4 1300 =SUM[H37:H40) *B21 =SUM [137:140)*B13 41 Costs = -SUM (C37:C40]*811 -SUM (D37:D40]*B11 -SUM [E37:E40) *B15 -SUM [F37:F40] *B10 =SUM(G37:G48]* B9 42 Constraints 44 Work force Production Overtime Inventory Size, W. Capacity Limit Balance 45 Quarter 46 25 47 1 -B46+C37-D37 -B47'WREFI+E38WREF! -B47*#REF! -#REF!+F38+#REF!-#REF! 48 2 B47+C38-D38 -B48'WREFI+E39WREF -B48*#REF! #REF!+F39+#REFI-B38-# 49 B48+C39-D39 -B49'#REFI+ E40WREF! -B49*#REF! -#REF!+F40+#REFI-B40-# 50 B49+C40-D40 -B50'#REFI+AREFWREF! -B50*#REF! #REF!+#REF!+#REFI-#RE 51 SUM(B47:B50] * B25 52 53 54 55 56Aggregate 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.400 1,200 1,500 1.300 Costs Beginning inventory 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" 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" 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
