In-Residence Assignment

Red Tomato Tools Case

The demand for Red Tomato Tools gardening tools is highly seasonal, peaking in the summer when people plant their gardens. Red Tomato has decided to use aggregate planning to overcome this obstacle of seasonal demand and maximize their profits. 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 and building up backlog of orders that will be delivered late to customers.

To determine how to best use these options through an aggregate plan, Red Tomato’s VP for Supply Chain Operations starts with demand forecasts for its tools over the next six months. These are shown in Table 1.

Table 1: Demand Forecasts

Month / Demand Forecast
January / 1,600
February / 3,000
March / 3,200
April / 3,800
May / 2,200
June / 2,200

Red Tomato sells each tool to retailers for $40. The company has a starting inventory in January of 1,000 tools. At the beginning of January, the company has a work-force of 80 employees. The plant has a total of 20 working days each month, and each employee earns $4.00 per hour regular time. Each employee works a total of eight hours a day on straight time and the rest on overtime. The capacity of the production operation is determined primarily by the total labor hours worked. Due to labor rules, no employee works more than 10 hours of overtime per month.

Other costs are shown in Table 2. Currently, Red Tomato has no limits on subcontracting, inventories and stock-outs/backlogs. All stock-outs are backlogged and supplied from the following month’s production. Inventory costs are incurred on the ending inventory in the month. The supply chain planner’s goal is to obtain the optimal aggregate plan that allows Red Tomato to end June with at least 500 units which means no stock-outs at the end of June and at least 500 units of inventory.

The optimal aggregate plan is one that results in the highest profit over the six-month planning horizon. Given Red Tomato’s desire for a very high level of customer service, we will assume all demand is met. Therefore, revenues earned over the planning horizon are fixed given the fixed price. In this case, minimizing cost over the planning horizon is the same as maximizing profit.

Table 2: Costs for Red Tomato

Item / Cost
Material Cost / $10/Unit
Inventory Holding Cost / $2/Unit/Month
Marginal Cost of Stock-out/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
  1. Formulate this problem as a Linear Programming model. Then, using Solver on Excel, determine its optimal solution (Use the formulation and methodology shown in Chapter 8).
  2. Now, suppose that instead of hiring and lay-offing workers every period, the company decided to work with two categories of workers, fulltime employees and part-time employees. They start January with 60 fulltime workers and no part-time workers. During the first period of the plan, they can hire or fire fulltime employees. After this initial decision in period 1, these fulltime employees would be employed for the duration of the planning period of six months. That is, their numbers would remain constant. The cost of hiring and firing of fulltime and part-time workers is the same. On the other hand, the number of part-time employees could change from period to period through hiring and lay-offs. Fulltime workers will be paid $4.00 per hour and part-time workers will be paid $4.50 per hour. Now, assume the fulltime and part-time employees can work the same number of hours. That is, 40 hours a week. However, the fulltime employees can work 10 hours of overtime but the part-time workers can only work 5 hours of overtime in a month. Overtime is paid at one and half times normal pay.
    For this part, you are required to do the following:
  3. List and define the decision variables that would be needed to formulate this new aggregate plan.
  4. Formulate this new aggregate plan as a Linear Programming problem.
  5. Set up the template for this problem on Excel and show how you would solve this problem on Solver as shown on Page 221 of your book.

You are required to turn in the following:

  1. An Excel file with the solution to Part 1 (Filename: your name_Part1_A7)
  2. A Word file with Parts 2a and 2b. Please modify the file “Red Tomato LP Formulation I” as necessary for this part. (Filename: your name_Part2ab_A7)
  3. An Excel file with the template for Part 2c. Please note this will be similar to Figures 8.1. 8.2 and 8.3 in Chapter 8. You can put the instructions for solving the problem on Solver using a text box in this file (Filename: your name_Part2c_A7)

Submit your three files as an email attachment. Please make sure you use the file names as specified above. In particular, you need to have your name in the filename.