This is a Red Tomato Aggregate Planning question. In class the professor solved it using the solver in excel, and I would appreciate someone showing me how to use that software to solve this problem step by step. Thank you!

Red Tomato sells each tool through retailers for $40. The company has a starting inventory in January of 1,000 tools. At the beginning of January, the company has a workforce of 80 employees. The plant has a total of 20 working days in each month, and each employee earns $4 per hour regular time. Each employee works eight hours per day on straight time and the rest on overtime. As discussed previously, the capacity of the production operation is determined primarily by the total labor hours worked. Therefore, machine capacity does not limit the capacity of the production operation. Because of labor rules, no employee works more than 10 hours of overtime per month. The various costs are shown in Table. It is important that the costs and labor hours are in aggregate units. Currently, Red Tomato has no limits on subcontracting, inventories, and stockouts/ backlog. All stockouts are backlogged and supplied from the following months' production. Inventory costs are incurred on the ending inventory in the month. The supply chain manager's goal is to obtain the optimal aggregate plan that allows Red Tomato to end June with at least 500 units (i.e., no stockouts at the end of June and at least 500 units in inventory). a) Develop an aggregate plan for for the next 6 months. b) Increase in holding cost (from $2 to $6) c) Overtime cost drops to $4.1 per hour d) New demand fluctuation New Demand Period Dvd 1 1,000 2 3,000 3,800 4 4,800 2.000 6 1,400 Materials cost/unit Inventory holding cost/unit/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.0 $6.0 $30 Inptut Parameters Unit Price Starting Workforce Starting Inventory Working days per month Working hours per day Max overtime per worker per month End Stockout End Inventory $40 80 1,000 20 8 10 0 500 Period 1 2 3 4 5 6 Demand 1,600 3,000 3,200 3,800 2,200 2.200 New Demand Period Demand 1 1,000 2 3,000 3 3,800 4 4,800 5 2,000 6 1,400 Objective Function Total Cost = Total Revenue = Profit Decision Variables t Lt Wt Ot It St Ct Pt Period # Hired #Laid off #Workforce Overtime Inventory Stockout Subcontract Production 0 80 1,000 1 2 31 41 5 61 Constraints Calculations Costs Regular time Overtime Inventory Stockout Subcontract Hiring Lay off Workforce Balance Material Inventory Balance Extra Capacity Extra Over time Period 1 2 3 4 5 6 Period 1 2 3 4 5 6 Red Tomato sells each tool through retailers for $40. The company has a starting inventory in January of 1,000 tools. At the beginning of January, the company has a workforce of 80 employees. The plant has a total of 20 working days in each month, and each employee earns $4 per hour regular time. Each employee works eight hours per day on straight time and the rest on overtime. As discussed previously, the capacity of the production operation is determined primarily by the total labor hours worked. Therefore, machine capacity does not limit the capacity of the production operation. Because of labor rules, no employee works more than 10 hours of overtime per month. The various costs are shown in Table. It is important that the costs and labor hours are in aggregate units. Currently, Red Tomato has no limits on subcontracting, inventories, and stockouts/ backlog. All stockouts are backlogged and supplied from the following months' production. Inventory costs are incurred on the ending inventory in the month. The supply chain manager's goal is to obtain the optimal aggregate plan that allows Red Tomato to end June with at least 500 units (i.e., no stockouts at the end of June and at least 500 units in inventory). a) Develop an aggregate plan for for the next 6 months. b) Increase in holding cost (from $2 to $6) c) Overtime cost drops to $4.1 per hour d) New demand fluctuation New Demand Period Dvd 1 1,000 2 3,000 3,800 4 4,800 2.000 6 1,400 Materials cost/unit Inventory holding cost/unit/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.0 $6.0 $30 Inptut Parameters Unit Price Starting Workforce Starting Inventory Working days per month Working hours per day Max overtime per worker per month End Stockout End Inventory $40 80 1,000 20 8 10 0 500 Period 1 2 3 4 5 6 Demand 1,600 3,000 3,200 3,800 2,200 2.200 New Demand Period Demand 1 1,000 2 3,000 3 3,800 4 4,800 5 2,000 6 1,400 Objective Function Total Cost = Total Revenue = Profit Decision Variables t Lt Wt Ot It St Ct Pt Period # Hired #Laid off #Workforce Overtime Inventory Stockout Subcontract Production 0 80 1,000 1 2 31 41 5 61 Constraints Calculations Costs Regular time Overtime Inventory Stockout Subcontract Hiring Lay off Workforce Balance Material Inventory Balance Extra Capacity Extra Over time Period 1 2 3 4 5 6 Period 1 2 3 4 5 6