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.

AutoSave OFF AGES hw4q1 Home Insert Draw Page Layout Formulas Date Review View Tell me Share Comments X [G Calibri (Body) v 12 VA 2 Insert v 2 Wrap Text ... Ceneral 4 v Delete Paste BIU AvA V = = = Y Merge & Center Conditional Format Cell Formatting es Table Styles Y Ideas Sort Filter Sensitivity Format v Find & Select F18 fx C D H I K L L M N 0 P a R 0,5 units per worker hour A i Hiring cost 2 Firing worker 3 Inwentary holding cost 4 Backordering cost 5 Regular time 6 Overtime 7 $100 per temp worker $200 per temp worker $5 per unit quartar 519 per un toquarter $5 per hour $8 per hour E Productivity Hours per day Days per quarter Nor of worker's Reg. Warker Hrs Max. Overtime Hrs Max. Overtime Hrs Max. Nbr. Temp Wrk Temp Wrk Days per Qi 60 30 14400 50% 7200 15 20 pert! Chase with as few temp workers as possible nbr temp wres Overtime hours end in for summer unit east Fall 10000 200 Winter 2000 Spring 7000 Summer 12000 part Chase with as little overtime as possible nor temp wars overtime hours end in for summer unit cast part 1 Level strategy nor temp wrhrs Overtime hours end in for summer unit cost Reed End Inv for Surnrner 200 10 11 12 Forecast (units) 13 Beginning inwentary (units) 14 Production required units) 15 Production hours renuined 16 Worker regular hours available 17 Nor temp workers 18 Temp worker regular hours available 19 Total regular hours available 20 Overtime hours 21 Total hours available 22 Actual production 23 Ending inventory 24 Workers hired 25 Workers laid off 26 27 Costs 28 Straight time 29 Overtime 30 Inventory holding 31 Backorder 32 Hiring 33 Layoff 34 Tatal 35 36 Tatal units produced 37 Tatal cost 38 Unit cost 39 $0 HOV #DIV/0! Sheet1 + ED - + 100% %