Please use Excel to solve this problem while using the chart/format given, and show all steps/formulas used so that I am able to follow along on my computer. Thank you!

Q5 (25pt) Pennsylvania Replicas makes two types of traditional Shaker tables: The Simplicity and the Workman. The two lines are made in the same facility using the same labor and equipment. In addition, we know that: * Each Simplicity and Workman table requires, on average, about 3.2 hours of labor. * Each employee works 160 hours per month, and there is no effective limit on the number of employees * The cost of hiring or firing an employee is $900 * The monthly holding cost for a table is $9.50 * For planning purposes, the beginning and ending workforce is 35 employees * At the beginning of the planning period, there are 0 units in inventory Simplicity Workman Month Demand Demand November 750 6750 December 875 7875 1000 9000 February 1000 9000 March 1250 11250 April 1250 11250 May 1500 13500 June 1500 13500 1625 14625 August 1750 15750 January July 1) Develop a level S&OP for November - August by filling the following table. Show aggregated monthly production, workforce and ending inventory levels, monthly hirings and firings, and total hiring, firing, and inventory costs. To keep it simple, assume you can have fractional units in inventory, and that you can hire / fire fractional numbers of employees (ex. - 25.5 employees). What is the total cost? Month Simplicity Workman Demand Demand Total Demand Total hours Workers needed Actual workers 35 Hires Fires Ending Inventory 0 November December January February March April May June July August Ave: 35 Totals: Inventory cost: Hiring & Firing cost: Total: 2) Develop a chase S&OP for November - August by filling the following table. Follow the same instructions as in Problem a). What is the total cost? Month Simplicity Workman Demand Demand Total Demand Total hours Workers Actual needed workers 35 Hires Fires Ending Inventory November December January February March April May June July August Ave: 35 Totals: Inventory cost: Hiring & Firing cost: Total: Q5 (25pt) Pennsylvania Replicas makes two types of traditional Shaker tables: The Simplicity and the Workman. The two lines are made in the same facility using the same labor and equipment. In addition, we know that: * Each Simplicity and Workman table requires, on average, about 3.2 hours of labor. * Each employee works 160 hours per month, and there is no effective limit on the number of employees * The cost of hiring or firing an employee is $900 * The monthly holding cost for a table is $9.50 * For planning purposes, the beginning and ending workforce is 35 employees * At the beginning of the planning period, there are 0 units in inventory Simplicity Workman Month Demand Demand November 750 6750 December 875 7875 1000 9000 February 1000 9000 March 1250 11250 April 1250 11250 May 1500 13500 June 1500 13500 1625 14625 August 1750 15750 January July 1) Develop a level S&OP for November - August by filling the following table. Show aggregated monthly production, workforce and ending inventory levels, monthly hirings and firings, and total hiring, firing, and inventory costs. To keep it simple, assume you can have fractional units in inventory, and that you can hire / fire fractional numbers of employees (ex. - 25.5 employees). What is the total cost? Month Simplicity Workman Demand Demand Total Demand Total hours Workers needed Actual workers 35 Hires Fires Ending Inventory 0 November December January February March April May June July August Ave: 35 Totals: Inventory cost: Hiring & Firing cost: Total: 2) Develop a chase S&OP for November - August by filling the following table. Follow the same instructions as in Problem a). What is the total cost? Month Simplicity Workman Demand Demand Total Demand Total hours Workers Actual needed workers 35 Hires Fires Ending Inventory November December January February March April May June July August Ave: 35 Totals: Inventory cost: Hiring & Firing cost: Total