USE THE FIRST 7 LETTERS (OR LESS FOR SHORTER NAMES) OF YOUR LAST NAME FOLLOWED BY THE HOMEWORK NUMBER TO NAME THE FILE (FOR EXAMPLE ZHAO2) AND SUBMIT THE HOMEWORK ON CANVAS. Design a spreadsheet that can be used to evaluate aggregate plans. The spreadsheet should enable the evaluation of the pure strategy based on leveling production. (5pts, 1pt for getting sales in labor-hour, 1 pt in getting production in labor-hour, 1 pt in getting number of workers, 1 pt in getting workers hired and workers hired, 1pt for getting beginning inventory, ending inventory and backorder) The spreadsheet should have the following headings: 1 2 3 4 5 6 7 8 9 10 11 Sales in Production Month Sales labor-hour Working labor-hour Number of Workers Workers Beginning Ending Backorder (Million) (000s) days (000s) Workers Hired Fired Inventory Inventory All but columns 1, 2, and 4 should be computed using cell formulas. Based on the spreadsheet, calculate the production cost, inventory holding cost, firing cost, hiring cost, and backorder cost. (5pts, 1pt for each cost) Assume an 8-month horizon and use the following data to test your spreadsheet: Sales per direct labor hour $30 Hiring cost $ 300.0 Firing cost $ 500.0 Regular labor cost per hour $ 15.00 Overtime premium cost per hour $ 7.50 Backorder cost per month 2% Inventory carrying cost per month 1% Beginning inventory $ 400000 Desired ending inventory in September $ 200,000 Number of Employees as of end of December 1900 Employee Productivity (in units/hour) 8 Month Sales Working (Million) days January 8.0 20 February 7.4 21 March 11.2 23 April 10.0 20 May 9.8 22 June 8.0 22 July 9.0 10 August 11.6 23 September 9.4 20