You need to develop an annual aggregate production plan for a firm. You are given quarterly aggregate demand forecasts. You have 30 workers who work 8 hours a day 60 days a quarter. You can have them work overtime, but total quarterly overtime hours cannot exceed 50% of the total regular time of these workers. You can also hire temporary workers who work only regular time (no overtime) for 20 days (one month) in a given quarter. You are also limited to hire at most 15 temporary workers in a quarter.

You are given a spreadsheet template that contains all the relevant data. In order to have some safety stock for the next year, it is a policy decision to end the summer with at least 200 units of inventory.

1. Complete the formulas in the spreadsheet?
2. Use the spreadsheet model to come up with a production plan where you follow the chase strategy in which you hire temp workers only if you have to. In the space provided to the right of the model, copy-paste values your decisions, the ending inventory for the summer, and the unit cost?
3. Come up with a production plan where you follow the chase strategy in which you use only overtime only if you have to. In the space provided to the right of the model, copy-paste values your decisions, the ending inventory for the summer, and the unit cost?
4. Come up with a production plan where you follow the level strategy. In the space provided to the right of the model, copy-paste values your decisions, the ending inventory for the summer, and the unit cost?

Hiring cost Firing worker Inventory holding cost Backordering cost Regular time Overtime $100 per temp worker $200 per temp worker $5 per unit-quarter $10 per unit-quarter $5 per hour $8 per hour Productivity Hours per day Days per quarter Nbr of workers Reg. Worker Hrs Max. Overtime Hrs % Max. Overtime Hrs Max. Nbr. Ternp Wik Temp Wrk Days per a 0,5 units per worker hour 8 60 30 14400 50% 7200 15 20 part b] Chase with as few temp workers as possible nor temp was Overtime hours end invlor summer unit cost Fall 10000 300 Winter 8000 Spring 7000 Summer 12000 parte) Chase with as little overtime as possible mbr temp wika Overtime hours end invlor summer unit cost Forecast (units! Beginning inventory (units) Production required (units) Production tours required Worker regular hours available Nor temp workers Temp worker regular hours available Total regular hours available Overtime hours Total hours available Actual production Ending inventory Workers hired Workers laid oll part d) Level strategy nbr temp wik overtime hours end inv for summer unit cost Reqd End Inv for Summer 200 Costs Straight time Overtime Inventory holding Backorder Hiring Layoll Total Total units produced Total cost Unit cost SO #SAYYO! Hiring cost Firing worker Inventory holding cost Backordering cost Regular time Overtime $100 per temp worker $200 per temp worker $5 per unit-quarter $10 per unit-quarter $5 per hour $8 per hour Productivity Hours per day Days per quarter Nbr of workers Reg. Worker Hrs Max. Overtime Hrs % Max. Overtime Hrs Max. Nbr. Ternp Wik Temp Wrk Days per a 0,5 units per worker hour 8 60 30 14400 50% 7200 15 20 part b] Chase with as few temp workers as possible nor temp was Overtime hours end invlor summer unit cost Fall 10000 300 Winter 8000 Spring 7000 Summer 12000 parte) Chase with as little overtime as possible mbr temp wika Overtime hours end invlor summer unit cost Forecast (units! Beginning inventory (units) Production required (units) Production tours required Worker regular hours available Nor temp workers Temp worker regular hours available Total regular hours available Overtime hours Total hours available Actual production Ending inventory Workers hired Workers laid oll part d) Level strategy nbr temp wik overtime hours end inv for summer unit cost Reqd End Inv for Summer 200 Costs Straight time Overtime Inventory holding Backorder Hiring Layoll Total Total units produced Total cost Unit cost SO #SAYYO