Please solve in EXCEL and show formulas as well so I can learn instead of just get the answer! Thank you in advance I will be sure to give a thumbs up if it's done in excel.
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 49,200 52,000 52,900 50,300 52,000 52,600 Regular production Overtime production Subcontract production Ending inventory 13,200 Hired employees Fired employees Total employees 477 Cost variables are as follows: $18 $39 Cost Variables Labor cost/hour Overtime cost/ton Subcontracting cost/ton Holding cost/ton/month Hiring cost/employee Firing cost/employee $35 $7 $2,900 $6,000 Here is some additional relevant (capacity) information: Capacity Information Total labor hours/ton 2 Regular production tons/employee/month 100 Max regular production (tons/month) 52,100 Max overtime production (tons/month) 3,700 Max subcontractor production (tons/month) 4,800 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.) Number What is the total overtime production cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total subcontract cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total holding cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total hire cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total fire cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total cost (sum of all costs) for this production plan? (Display your answer to the nearest whole number.) Number 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 49,200 52,000 52,900 50,300 52,000 52,600 Regular production Overtime production Subcontract production Ending inventory 13,200 Hired employees Fired employees Total employees 477 Cost variables are as follows: $18 $39 Cost Variables Labor cost/hour Overtime cost/ton Subcontracting cost/ton Holding cost/ton/month Hiring cost/employee Firing cost/employee $35 $7 $2,900 $6,000 Here is some additional relevant (capacity) information: Capacity Information Total labor hours/ton 2 Regular production tons/employee/month 100 Max regular production (tons/month) 52,100 Max overtime production (tons/month) 3,700 Max subcontractor production (tons/month) 4,800 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.) Number What is the total overtime production cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total subcontract cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total holding cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total hire cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total fire cost for this production plan? (Display your answer to the nearest whole number.) Number What is the total cost (sum of all costs) for this production plan? (Display your answer to the nearest whole number.) Number
