Aggregate Planning using Linear Programming

Red Tomato Tools is a small manufacturer of gardening equipment with manufacturing facilities in Mexico. Red Tomatos products are sold through retailers in the United States. Red Tomatos operations consist of the assembly of purchased parts into a multipurpose gardening tool. Because of the limited equipment and space required for its assembly operations, Red Tomatos capacity is determined mainly by the size of its workforce. The demand for Red Tomatos gardening tools from consumers is highly seasonal, peaking in the spring as people plant their gardens. This seasonal demand ripples up the supply chain from the retailer to Red Tomato, the manufacturer. The options Red Tomato has for handling the seasonality are adding workers during the peak season, subcontracting out some of the work, building up inventory during the slow months, or building up a backlog of orders that will be delivered late to customers. To determine how to best use these options through an aggregate plan, Red Tomatos vice president of supply chain starts with the first taskbuilding a demand forecast. Although Red Tomato could attempt to forecast this demand itself, a much more accurate forecast comes from a collaborative 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 defined earlier. Red Tomato sells each tool through retailers for $40. The company has a starting inventory in January of 935 tools. At the beginning of January, the company has a workforce of 87 employees.

Table 8-2 Demand Forecast at Red Tomato Tools

Month

Demand Forecast

January

2349

February

1760

March

3022

April

3218

May

3188

June

2270

Table 8-3 Costs for Red Tomato

Item

Cost

Material cost

$10/unit

Inventory holding cost

$2/unit/month

Marginal cost of stockout/backlog

$5/unit/month

Hiring and training costs

$300/worker

Layoff cost

$500/worker

Labor hours required

4/unit

Regular time cost

$4/hour

Overtime cost

$6/hour

Cost of subcontracting

$30/unit

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 8-3. 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 managers goal is to obtain the optimal aggregate plan that allows Red Tomato to end June with at least 677 units (i.e., no stockouts at the end of June and at least 677 units in inventory). The optimal aggregate plan is one that results in the highest profit over the six-month planning horizon. For now, given Red Tomatos desire for a high level of customer service, assume all demand is to be met, although it can be met late. Therefore, the revenues earned over the planning horizon are fixed. As a result, minimizing cost over the planning horizon is the same as maximizing profit.

Formulate an aggregate planning for the Red Tomatos 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 layoff?

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?

8.What is the total cost of material?

9.What is the minimum value of objective function?