Design a spreadsheet that can be used to evaluate aggregate plans. The spreadsheet should enable the evaluation of:

1.pure strategy based on leveling production,

2.pure strategy based on minimizing inventory by laying off/hiring as needed,

3.a mix of the above two.

All but columns 1, 2, and 4 should be computed using cell formulas. Column 5 should allow for using cell formulas in the case of level production or direct input for mixed plans. The cost of any plan, in total and for each cost component, should be computed without additional effort. Assume an 8-month horizon and use the following data to test your spreadsheet:

I'm stuck at Production labor hour (in 000s) and Number of workers each period

The spreadsheet should have the following headings: - 1 2 4 7 8 9 10 11 Production 3 Sales in labor-hour (000s) Month Backorder Sales (Million) Working days (000s) Number of Workers Workers Hired Fired Beginning Inventory Ending Inventory $30 $ 300.0 $ 500.0 $ 15.00 $ 7.50 Sales per direct labor hour Hiring cost Firing cost |Regular labor cost per hour Overtime premium cost per hour Backorder cost per month Inventory carrying cost per month Beginning inventory Desired ending inventory in September Number of Employees as of end of December 2% 1% $ 400000 $ 0 1900 Month Sales (Million) Working days 21 January February March April May June July August September 8.0 7.4 11.2. 11.0 9.8 8.0 8.0 10.6 9.4 22 22 10 23 20 Months Sales (Million) Sales in labor- hour (000s) Working days Production labor-hour (000s) Number of workers Workers hired Workers fired Beginning inventory Ending inventory Backorder 1900 13.33 266.7 20 13.33 8 7.4 11.2 21 246.7 373.3 23 20 January February March April May June July August September 366.7 326.7 22 11 9.8 8 8 10.6 266.7 22 266.7 10 23 353.3 313.3 9.4 20 Total 83.4 2780.00 181