THE RED BOXES ARE WRONG ANSWERS AND THE GREEN BOXES ARE RIGHT. PLEASE SHOW WORK IN EXCEL. THIS WAS POSTED ONCE BEFORE BUT THE PERSON WHO WORKED ON IT GOT IT WRONG

61,100 PP.63 Jupiter, a large candy company, is having great success with its "Swan" family of candy bars. Due to a number of factors they like to plan their production at least six months into the future. The table below contains their demand projections (in tons) for April through September: Supply/Demand Info Beginning Apr May Jun Jul Aug Sep Predicted Sales 46,500 62,700 56,200 65,000 59,300 Regular production Overtime production Subcontract production Ending inventory 7,600 Hired employees Fired employees Total employees 563 Cost variables are as follows: $14 $37 Cost Variables Labor cost/hour Overtime cost/ton Subcontracting cost/ton Holding cost/ton/month Hiring cost/employee Firing cost/employee $33 $14 $3,200 $5,700 4 Here is some additional relevant (capacity) information: Capacity Information Total labor hours/ton Regular production tons/employee/month 100 Max regular production (tons/month) 59,300 Max overtime production (tons/month) 3,200 Max subcontractor production (tons/ month) 4,500 Given the above information (and don't overlook beginning number of employees and inventory levels in the first table), create a LEVEL production plan with only the use of regular production and no inventory left over at the end of the six-month period. What is the regular production cost (over the six months from April through September) for a level production plan? (Display your answer to the nearest whole number.) 1281280 What is the total overtime production cost for this production plan? (Display your answer to the nearest whole number.) 0 What is the total subcontract cost for this production plan? (Display your answer to the nearest whole number.) What is the total holding cost for this production plan? (Display your answer to the nearest whole number.) 698600 What is the total hire cost for this production plan? (Display your answer to the nearest whole number.) 28800 What is the total fire cost for this production plan? (Display your answer to the nearest whole number.) 0 What is the total cost (sum of all costs) for this production plan? (Display your answer to the nearest whole number.) 13540200