Given the data provided in Table 8-2 and Table 8-3 for Red Tomato Tools, use the Excel model to determine the optimal aggregate plan for the planning horizon. There is a requirement for no stockouts at the end of June and at least 500 units in inventory. What are the total cost, revenue, and profit for this plan? Create a master production schedule (MPS) for the last month in the aggregate plan.

Aggregate Planning (Chapter 8) Demand Forecast Month January February March April May June Demand Forecast 1,600 3,000 3,200 3,800 2,200 2,200 Costs Item Cost Materials cost/unit $ Inventory holding costunit/month $ Marginal cost of stockout/unit/month $ Hiring and training cost/worker $ Layoff cost/worker $ Labor hours required/unit Regular time cost/hour $ Over time cost/hour $ Marginal subcontracting cost/unit $ 10 2 5 300 500 4 4 6 30 Aggregate Plan Decision Variables Period 0 1 2 3 4 5 6 St Stockout 0 0 0 0 0 0 0 Ht L: W: O It Hired Lald of Workforce Overtime Inventory 0 0 80 0 1,000 0 0 80 0 2.600 0 0 80 0 2,800 0 0 80 0 2.800 0 0 80 0 2.200 0 0 80 0 3,200 0 0 80 0 4,200 $ 534,800 Base Price 5 640,000 $ 105,200 PL Max. Subcontract Production Demand Price Overtime 0 Available 0 3.200 1,600 800 0 3,200 0 3.200 3.200 800 0 3.200 3,800 800 0 3,200 2.200 800 0 3,200 2,200 800 3,000 800 slslslslsle Total Cost - $ 40 Total Revenue Profit Aggregate Planning (Chapter 8) Demand Forecast Month January February March April May June Demand Forecast 1,600 3,000 3,200 3,800 2,200 2,200 Costs Item Cost Materials cost/unit $ Inventory holding costunit/month $ Marginal cost of stockout/unit/month $ Hiring and training cost/worker $ Layoff cost/worker $ Labor hours required/unit Regular time cost/hour $ Over time cost/hour $ Marginal subcontracting cost/unit $ 10 2 5 300 500 4 4 6 30 Aggregate Plan Decision Variables Period 0 1 2 3 4 5 6 St Stockout 0 0 0 0 0 0 0 Ht L: W: O It Hired Lald of Workforce Overtime Inventory 0 0 80 0 1,000 0 0 80 0 2.600 0 0 80 0 2,800 0 0 80 0 2.800 0 0 80 0 2.200 0 0 80 0 3,200 0 0 80 0 4,200 $ 534,800 Base Price 5 640,000 $ 105,200 PL Max. Subcontract Production Demand Price Overtime 0 Available 0 3.200 1,600 800 0 3,200 0 3.200 3.200 800 0 3.200 3,800 800 0 3,200 2.200 800 0 3,200 2,200 800 3,000 800 slslslslsle Total Cost - $ 40 Total Revenue Profit