Aggregate Planning using Unear Programming Red Tomato Tools is a small manufocturer of gardening equipment with manutocturing facilities in Mexica. Red Tomoto's products are sold through retalers in the United States. Red Tomato's operations consist of the assombly of purchased parts into a muitipurpose gordening tool. Because of the limited equipment and space required for its assembly operations, fied Tomato's copacity is determined mainly by the size of its workforce. The demand for Red Tomato's gardening tools from consumers is highly seasonat, peoking in the spring as people plant their gardens. This seasonal demand ripples up the supply choin from the retailer to Red Tomoto, the manufocturer. The options Red Tomato has for handling the seasonolity are adding workers during the peck season. subcontrocting out some of the work, bulding up inventory during the slow months, or ballding up o backog of orders that will be delivered late to custorners. To determine how to best use these options through an aggregate plan, Red Tomato's vice president of supply chain starts with the first tosk-building a demand forecast. Although Red Tomoto could attempt to forecast this demand itseli, a much more occurate forecast comes from a colloborotive process used by both Red Tomato and its retailers to produce the forecast shown in Table 8-2 it is important that this demand account for the product mix that is expected to sell and be in terms of aggregate units detined earlier. Red Tomato seils each tool through retailers for $40. The company has a starting inventory in January of 1083 tools. At the boginning of January, the company has a workforce of 74 employees. Toble B-2 Demand Forecast at fied Tomato Tools MonthJanuaryfebruary2019MarchApriMayJuneDemand33963735277519512316 Table 8-3 Costs for Red Tomato The plant has a total of 20 working days in eoch month, and each employee earns $4 per hour regular time. Each employee works eight hours per doy on straight time and the rest on overtime. As discussed previously, the capocity of the proctuction operation is determined primarily by the total iabor hours worked. Therefore, mochine capacity does not limit the copacity of the production operation. Because of labor rules, no employee works more than to hours of overtime per month. The various costs are shown in Table 8-3. Currently, Red Tomato has no limits on subcontrocting. inventorles, and stockouts/ backlog. All stockouts are backlogged and supplied from the following months' production. inventory costs ore incurred on the ending inventory in the month. The supply chain manager's goal is to obtain the optimal aggregate plan that allows fed Tomato to end June with ot least 680 units (l.e, no stockouts at the end of June and at leost 680 units in inventory). The optimal oggregate plan is one that results in the highest profit over the six-month planning horizon. For now, given Red Tomato's desire for a high level of customer service, assume all demand is to be met, aithough it can be met late. Theretore, the revenues earned over the planning harizon ore fred. As a result, minimizing cost over the planning horizon is the same as maximizing profit. Formulate an aggregate planning for the Red Tomato's operations in a spreadsheet and find an optimal solution using the Excel Solver. Then answer to the following questions: 1. What is the total cost of hiring? 2.What is the total cost of layotf? 3.What is the total cost of regular time? 4. What is the total cost of overtime? 5. What is the total cost of inventory? 6. What is the total cost of stockout? 7. What is the total cost of subcontract? QWhot is the minimum value of objective function? Completion and Grading Submit your completed Word document to the Moode assignment dropbox by the due date indicatedi. You are only permitted to submit one Word (docx) file. Seo the Grading Criterion bolow for the full rubric on how you was be groded. Please make sure to write your full name on this answer sheet. Total marks: 9 (one for each), 5% weight Name: The Costs of Aggregate Production Plan for Red Tomato