Please

COMPLETE ON EXCEL AND SHOW FORMULAS AND STEPS IN ORDER TO GET THE TOTAL COST OF THE PLAN.

SD Question 1: The Bogard Corporation produces bookcases. The department current has 20 workers, and each worker produces 5 bookcases per month. The operations manager is considering an aggregate production plan for the next six months. The manager knows that the opening inventory for this plan is 10 bookcases. The demand for each month is given in the following table. The information on cost and production is given as follows: Cost data Regular-time labour cost $6 per bookcase Overtime labour cost $9 per bookcase Subcontracting cost $12 per bookcase Holding cost $1 per bookcase per month Hiring cost $50 per worker Firing cost $40 per worker Production: Max. overtime output 30 bookcases per month Using the following table to develop three aggregate plans based on the requirements and find out total cost for each of plans. Aggregate Production Plan Production Breakdown Hiring & Firing Month Demand Total Workforce Inventory Regular Overtime Subcontract Hiring Firing (units) production Level Production Production Production 1 2 3 4 5 6 90 110 120 150 100 80 1) Use a level approach to develop an aggregate plan using the highest demand to be the constant output rate/month. Use the table below to assist your analysis and develop an aggregate plan. What is the total cost of this plan? A II-- -1 - . 11. 0 + 1 12 >> HITV w X 4 ctv SI all / A Aa 92