Please include formulas to type in excel. Thanks!

Problem 6 (Aggregate Planning2) Demand data for 9 months for a luxury boat maker have been provided. Unit costs for producing the product in regular time, overtime, and via subcontracting are given. Inventory holding cost and backordering cost per period have been provided. The costs of hiring and firing each worker are given. The firm has a productivity of 0.5 units per worker per period. The number of workers employed in the last period of the previous aggregate plan was 70. The beginning inventory in period 1 is 5 units. Make an aggregate plan using level production technique for every three consecutive periods. Please note that the regular time production can't exceed 40 units in any period (capacity constraint) and the total number of units made in all nine months together should not exceed 15 units. Find the total cost of your aggregate plan. Try to be cost conscious (i.e. don't waste money if cheaper alternatives exist). F1 D F H N 1 Problem 6. 2 1 4 Tota Cost $85 12 13 15 Time 2 3 5 6 7 8 9 Unit 5 Demand 30 60 60 20 30 10 30 60 60 Cost 7 P(Reg) $30 8 P(OT) $45 9 P(Sub) 11 Inv(Beg) 5 Inv(End) Inv(Avg) $5 #W #Hired $300 #Fired $500 Backorders $10 Productivity 0.5 units/worker/period TOTALE 22 70 P(Reg) s 40 for any period (capacity constraint) P(OT) 15 Total for the 10 periods 25 Use level production technique for every three consecutive periods + 16 17 19 21 #Wold 23 24 26 27 28