Aggregate Planning Models

1
Example 4.2

• Aggregate Planning Models

2
Background Information

• During the next four months the SureStep Company
  must meet (on time) the following demands for
  pairs of shoes 3,000 in month 1 5,000 in month
  2 2,000 in month 3 and 1,000 in month 4.
• At the beginning of month 1, 500 pairs of shoes
  are on hand, and SureStep has 100 workers.
• A worker is paid 1,500 per month. Each worker
  can work up to 160 hours a month before he or she
  receives overtime.
• A worker is forced to work 20 hours of overtime
  per month and is paid 13 per hour for overtime
  labor.

3
Background Information -- continued

• It takes 4 hours of labor and 15 of raw material
  to produce a pair of shoes.
• At the beginning of each month workers can be
  hired or fired. Each hired worker costs 1600,
  and each fired worker cost 2000.
• At the end of each month, a holding cost of 3
  per pair of shoes left in inventory is incurred.
  Production in a given month can be used to meet
  that months demand.
• SureStep wants to us LP to determine its optimal
  production schedule and labor policy.

4
Solution

• To model SureSteps problem with a spreadsheet,
  we must keep track of the following
• Number of workers hired, fired, and available
  during each month.
• Number of pairs of shoes produced each month with
  regular time and overtime labor
• Number of overtime hours used each month
• Beginning and ending inventory of shoes each
  month
• Monthly costs and the total costs

5
SURESTEP1.XLS

• This file shows the spreadsheet model for this
  problem.
• The spreadsheet figure on the next slide shows
  the model.

6
(No Transcript)
7
Developing the Model

• To develop this model, proceed as follows.
• Inputs. Enter the input data in the range B4B14
  and in the Demand range.
• Production, hiring and firing plans. Enter any
  trial values for the number of pairs of shoes
  produced each month in the Produced range, the
  overtime hours used each month in the OTHrs
  range, the workers hired each month in the Hired
  range, and the workers fired each month in the
  Fired range. These four ranges comprise the
  changing cells.
• Workers available each month. In cell B17 enter
  the initial number of workers available with the
  formula InitWorkers.

8
Developing the Model -- continued

• Because the number of workers available at the
  beginning of any other month is equal to the
  number of workers from the previous month, enter
  the formula B20 in cell C17 and copy it to the
  range D17E17. Then in cell B20 calculate the
  number of workers available in month 1 with the
  formula B17B18-B19 and copy this formula to the
  range C20E20 for months 2 through 4.
• Overtime capacity. Because each available worker
  can work up to 20 hours of overtime in a month,
  enter the formula MaxOTHrsB21 in cell B25 and
  copy it to the range C25E25 to computer the
  overtime hours capacity for months 2 and 4.

9
Developing the Model -- continued

• Production capacity. Because each worker can work
  160 regular-time hours per month, calculate the
  regular-time hours available in month 1 in cell
  B22 with the formula StdRTHrsB21 and copy it to
  the range C22E22 for the other months. Then
  calculate the total hours available for
  production in cell B27 with the formula
  SUM(B23B24) and copy it to the range C27E27
  for the other months. Finally, because it takes 4
  hours of labor to make a pair of shoes, calculate
  the production capacity for month 1 by entering
  the formula B28/HrsPerPair in cell B32, and copy
  it to the range C32E32.

10
Developing the Model -- continued

• Inventory each month. Calculate the inventory
  after production in month 1 by entering the
  formula InitInvB30 in cell B34. For any other
  month, the inventory after production is the
  previous months ending inventory plus tat
  months production, so enter the formula B37C30
  in cell C34 and copy it to the range D34E34.
  Then calculate the month 1 ending inventory in
  cell B37 with the formula B34-B36 and copy it to
  the range C37E37.
• Monthly costs. Calculate the various costs shown
  in rows 40 through 45 for month 1 by entering the
  formulas UnitHireCostB18, UnitFireCostB19,
  RTWageRateB20, OTWageRateB23,
  UnitMatCostB30, UnitHoldCostB37 in cells B40
  through B45. Then copy the range B40B45 to the
  range C40E45 to calculate these costs for the
  other months.

11
Developing the Model -- continued

• Totals. In row 46 and column F, use the SUM
  function to calculate cost totals, with the value
  in F46 being the overall total cost.
• Using Solver The Solver dialog box should appear
  as shown here. To accomplish this proceed as
  follows.

12
Developing the Model -- continued

• Objective. Select the TotCost cell as the target
  cell to minimize.
• Changing cells. Select the ranges Hired, Fired,
  Production, and OTHrs as changing cells
• Overtime constraints. Add the constraint OTHrs OTAvailable. This ensures that overtime hours
  during each month do not exceed the allowable
  amount.
• Production capacity constraint. Enter the
  constraint Production
  each months production does not exceed the limit
  set by the number of available hours.

13
Developing the Model -- continued

• Demand constraint. Enter the constraint
  OnHandDemand. This ensures that each months
  demand is met on time.
• Integer constraints. Although this is optional,
  we decided to constrain the number hired and
  fired to be integers. We could have also
  constrained the Production range to be integers.
  However, integer constraints typically require
  longer solution times. Therefore, it is often
  best to ignore such constraints, especially when
  the optimal values are fairly large, as are the
  production quantities in this model.

14
Developing the Model -- continued

• Specify nonnegativity and optimize. Under
  SolverOptions, check the nonnegativity box, and
  use the LP algorithm to obtain the optimal
  solution shown earlier.
• Observe that SureStep should never hire any
  workers, and it should fire 6 workers in month 1,
  1 worker in month 2, and 43 workers in month 3.
• Eighty hours of overtime are used, but only in
  month 2.
• The company produces slightly over 3700 pairs of
  shoes during each of the first 2 months, 200
  pairs in month 3, and 1000 in month 4. A total
  cost of 692,820 is incurred.

15
Developing the Model -- continued

• Again, we would not force the number of pairs of
  shoes produced each month to be an integer. It
  makes little difference whether the company
  produces 3760 or 3761 pairs of shoes during a
  month, and forcing each months shoe production
  to be an integer can greatly increase the time
  the computer needs to find an optimal solution.
• On the other hand, it is somewhat more important
  to ensure that the number of workers hired and
  fired each month is an integer, given the small
  number of workers involved.

16
Developing the Model -- continued

• Finally, if you want to ensure that Solver finds
  the optimal solution in a problem where some or
  all of the changing cells must be integers, it is
  a good idea to go into Options, then to Integer
  Options, and set the tolerance to 0.
• Otherwise, Solver might stop when it finds a
  solution that is close to optimal.

17
Model with Backlogging Allowed

• In many situations backlogging is allowed, that
  is, customer demand, can be met later than it
  occurs.
• Well modify this example to include the option
  of backlogged demand.
• We assume that at the end of each month a cost of
  20 is incurred for each unit of demand that
  remains unsatisfied at the end of the month.
• This is easily modeled by allowing a months
  ending inventory to be negative. The last month,
  month 4, should be nonnegative. This also ensures
  that all demand will eventually be met by the end
  of the four-month horizon.

18
Model with Backlogging Allowed -- continued

• We now need to modify the monthly cost
  computations to incorporate the costs due to
  shortages.
• We actually show two modeling approaches.
• The first is the more natural, but it results
  in a nonlinear model.
• It appears in the figure on the next slide.

19
(No Transcript)
20
SURESTEP2_NONLINEAR.XLS

• To begin, we enter the per unit monthly shortage
  cost in the UnitShortCost cell. Note in row 38
  how the ending inventory in months 1-3 can be
  positive or negative.
• We can account correctly for the resulting costs
  with IF functions in rows 46 and 47.
• For holding costs, enter the formula
  IF(B380,UnitHoldCostB38,0) in cell B46 and
  copy it across. For shortage costs, enter the
  formula IF(B38B47 and copy it across.

21
Model with Backlogging Allowed -- continued

• While these formulas accurately compute holding
  and shortage costs, the IF functions make the
  objective function nonlinear, and we must use
  Solvers Standard GRG Nonlinear algorithm, as
  shown here.

22
Model with Backlogging Allowed -- continued

• Even so, this algorithm is not guaranteed to find
  the optimal solution. It might succeed for some
  starting solutions and not for others.
• Alternatively, we could try Solvers Evolutionary
  algorithm.
• The Evolutionary Solver uses genetic algorithms
  to solve optimization problems.
• For most problems genetic algorithms are slower
  than the standard Solver algorithms. However,
  their advantage is that they can handle any
  spreadsheet model.

23
Model with Backlogging Allowed -- continued

• Although this nonlinear model is natural, the
  fact that we cannot guarantee it to find the
  optimal solution is disturbing.
• We can, however, handle shortages and maintain a
  linear formulation.
• This method is illustrated in on the next slide.

24
(No Transcript)
25
SURESTEP2_LINEAR.XLS

• To develop this modified spreadsheet model,
  starting from the original model in the
  SURESTEP1.XLS file, proceed as follows.
• Enter shortage cost. Insert a new row 14 and
  enter the shortage cost per pair of shoes per
  month in the UnitShotCost cell.
• Rows for amounts held and short. Insert 5 new
  rows between the Demand and Ending inventory
  rows. The range B39E40 will be changing cells.
  The Excess range in row 39 contains the amounts
  left in inventory, whereas the Shortage range in
  row 40 contains the shortages. Enter any values
  in these ranges.

26
Model with Backlogging Allowed -- continued

• Ending inventory (positive or negative). The key
  observation is the following. Let Lt be the
  amount leftover in inventory at the end of month
  t, and let St be the amount short at the end of
  month t. Then Lt 0 if St ? 0 and St 0 if Lt ?
  0. So if we allow ending inventory to be
  negative, then for each month we have It Lt
  St.
• Monthly costs. Insert a new row below the holding
  cost row. Modify the holding cost for month 1 by
  entering the formula UnitHoldCostB39 in cell
  B51. Calculate the shortage cost for month 1 in
  cell B52 with the formula UnitShortCostB40.
  Then copy the range B51B52 to the range C51E52
  for the other months. Make sure the totals in row
  53 and column F are updated to include the
  shortage costs.

27
Model with Backlogging Allowed -- continued

• Using the Solver for the Backlog Model The
  changes from the original Solver setup are as
  follows.
• Extra changing cells. Add the Excess and Shortage
  ranges as changing cells. This allows the Solver
  to adjust each months amount leftover and amount
  short to be consistent with the desired ending
  inventory for the month.
• Constraint on last months inventory. Change the
  constraints that were previously listed as
  OnhandDemand to LastOnhandLastDemand. This
  allows months 1 through 3 to have negative ending
  inventory, whereas it ensures that all demand is
  met by the end of month 4.

28
Model with Backlogging Allowed -- continued

• Logical constraint on ending inventory. Add the
  constraints NetEndInv. If you study the model
  closely, you will notice that we have calculated
  ending inventory in two different ways. This
  constraint ensures that both ways produce the
  same values.
• Optimize. Make sure the LP algorithm is selected,
  and click on Solve to obtain the optimal solution
  shown.
• Note that the linear and nonlinear solutions are
  the same. So this time it worked out, but it
  might not always work.
• This solution is quite similar to the solution
  with no backlogging allowed, but now SureStep
  fires more workers in month 3 than before, and it
  purposely incurs shortages in months 2 and 3.

29
Model with Backlogging Allowed -- continued

• With more options it can now backlog demand if
  it desires the companys total cost cannot be
  any more than when backlogging was not allowed.
• However, the decrease is a rather minor one from
  692,820 to 690,180.

30
Sensitivity Analysis

• There are many sensitivity analyses we could
  perform on this final SureStep model.
• We illustrate one of them, where we see how the
  total cost and the shortages SureStep is willing
  to incur in months 1-3 vary with the unit
  shortage cost.
• The model is all set up to handle the analysis.
  All we need to do is invoke SolverTable, specify
  a one-way table, specify the TotCost cell and the
  range B40D40 as the output cells.

31
Sensitivity Analysis -- continued

• The results appear in the table shown below.

• As we see, when the unit shortage cost is below
  20, SureStep is willing to incur large shortages
  at a significantly lower total cost.

32
Sensitivity Analysis -- continued

• However, shortages become much less attractive
  when the unit shortage cost increases, and no
  shortages are incurred at all when this unit cost
  is above 25.
• In this case, we get the same solution as when
  shortages are disallowed.

33
The Rolling Planning Horizon Approach

• In reality, an aggregate planning model is
  usually implemented via a rolling planning
  horizon.
• To illustrate, we assume that SureStep works with
  a 4-month planning horizon.
• To implement the SureStep model in the rolling
  planning horizon context, we view the demands
  as forecasts and solve a 4-month model with these
  forecasts.
• However, we implement only the month 1 production
  and work scheduling recommendation.

34
The Rolling Planning Horizon Approach -- continued

• Thus, SureStep should hire no workers, fire 6
  workers, and produce 3760 pairs of shoes with
  regular time labor in month 1.
• Next, we observe month 1s actual demand.
• Suppose it is 2950. Then SureStep begins month 2
  with 1310 pairs of shoes and 94 workers.
• We would now enter 1310 in cell B4 and 94 in cell
  B5. Then we would replace demands in the Demands
  range with the updated forecasts for the next 4
  months.

35
The Rolling Planning Horizon Approach -- continued

• Now we would rerun Solver and use the production
  levels and hiring and firing recommendations in
  column B as the production level and workforce
  policy for month 2.
• Just like the caissons, the planning horizon goes
  rolling along!

36
Modeling Issues

• Hiring costs include training costs as well as
  the cost of decreased productivity due to the
  fact that a new worker must learn his or her job.
• Firing costs include severance costs and costs
  due to loss of morale.
• Peterson and Silver recommend that when demand is
  seasonal, the planning horizon should extend
  beyond the next seasonal peak.
• Beyond a certain point, the cost of using extra
  hours of overtime labor increases because workers
  become less efficient.