Aggregate Planning in Excel in a Supply Chain

Next we discuss how to generate the aggregate plan for Red Tomato in Table 8-4 using Excel. We first do so by building a simple spreadsheet that allows what-if analysis and then build a more sophisticated model that allows optimization using linear programming.

1. Building a Basic Aggregate Planning Spreadsheet

The aggregate planner must decide on the number of people hired (Ht) or laid off (Lt) each month, along with any overtime (Ot) or subcontracting (Ct). Once these decisions have been made, one can determine the workforce (Wt), production (Pt), inventory (It) and stockout (St) for each month (see Table 8-8), thus completing the aggregate plan. Figure 8-1 shows the final spreadsheet (available as Chapter8-trial-aggplan) corresponding to Table 8-4. The planner can try different inputs for each decision variable in the appropriate cells in worksheet Planning. It is best to start with the number hired (B5:B10) and number laid off (C5:C10) in each period. The maximum overtime available in a given month is limited by 10 X Wt and is shown in cells M5:M10. One can then enter the overtime (E5:E10) and amount subcontracted (H5:H10) for each period. For each set of inputs, the outputs are calculated as shown in Table 8-8.

The total cost is evaluated in cell C12 as follows:

= 640*sum(D5:D10) + 6*sum(E5:E10) + 300*sum(B5:B10) + 500*sum(C5:C10)

+ 2*sum(F5:F10) + 5*sum(G5:G10) + 10*sum(I5:I10) + 30*sum(H5:H10)

The goal is to build an aggregate plan by changing inputs in a way that minimizes the total cost in cell C12.

2. Building an Aggregate Planning Spreadsheet Using Solver

To access Excel’s linear programming capabilities, use Solver (Data | Analysis | Solver). To begin, we need to create a table, which we illustrate with Figure 8-2 (see associated spreadsheet Chapter8,9-examples), containing the following decision variables:

Wt = workforce size for Month t, t = 1, . . . , 6

Ht = number of employees hired at the beginning of Month t, t = 1, . .         .   ,                6

Lt = number of employees laid off at the beginning of Month t, t = 1,          .  . .               , 6

Pt = number of units produced in Month t, t = 1, . . . , 6

It = inventory at the end of Month t, t = 1, . . . , 6

St = number of units stocked out at the end of Month t, t = 1, . . . , 6

Ct = number of units subcontracted for Month t, t = 1, . . . , 6

Ot = number of overtime hours worked in Month t, t = 1, . . . , 6

Figure 8-2 (use worksheet Planning) illustrates what this table should look like. The deci­sion variables are contained in cells B5:I10, with each cell corresponding to a decision variable. For example, cell D7 corresponds to the workforce size in Period 3. Begin by setting all the deci­sion variables to 0, as shown in Figure 8-2.

Also note that column J contains the actual demand. The demand information is included because it is required to calculate the aggregate plan.

The second step is to construct a table for the constraints in Equation 8.2 to 8.5. The con­straint table may be constructed as shown in Figure 8-3.

Column M contains workforce constraints (Equation 8.2), column N contains capacity constraints (Equation 8.3), column O contains inventory balance constraints (Equation 8.4), and column P contains overtime constraints (Equation 8.5). These constraints are applied to each of the six periods.

Each constraint will eventually be written in Solver as

Cell value{ <, =, or > }0

In our case, we have constraints

M5:M10 = 0, N5:N10 > 0, O5:O10 = 0, P5:P10 > 0

The third step is to create a cell containing the objective function, which is the way each solution is judged. This cell need not contain the entire formula but can be written as a formula using cells with intermediate cost calculations. For the Red Tomato example, the spreadsheet area for cost calculations is shown in Figure 8-4. Cell B15, for instance, contains the hiring costs incurred in Period 1. The formula in cell B15 is the product of cell B5 and the cell containing the hiring cost per worker, which is obtained from Table 8-3. Other cells are filled similarly. Cell C22 contains the sum of cells B15:I20, representing the total cost.

The fourth step is to use Data | Analysis | Solver to invoke Solver. Within the Solver Param­eters dialog box, enter the following information to represent the linear programming model:

Set Target Cell: C22 Equal to: Select Min By Changing Cells: B5:I10 Subject to the constraints:

B5:C10 = integer {Number of workers hired or laid off is integer}

B5:I10 > 0 {All decision variables are nonnegative}

F10 > 500 {Inventory at end of Period 6 is at least 500}

G10 = 0 {Stockout at end of Period 6 equals 0}

M5:M10 = 0{ W – W! – Ht + Lt = 0 for t = 1, . . . , 6}

N5:N10 > 0 { 40 Wt + Ot/4 – Pt > 0 for t = 1, . . . , 6}

O5:O10 = 0{It-1 – St-1  +  Pt +  Ct – Dt    –   It  + St = 0 for t = 1, . . . , 6}

P5:P10 > 0{10Wt – Ot > 0 for t = 1, . . . , 6}

The Solver Parameters dialog box is shown in Figure 8-5. Click on Solve. The optimal solution should be returned. If Solver does not return the optimal solution, solve the problem again after saving the solution that Solver has returned. (In some cases, multiple repetitions of this step may be required because of some flaws in the version of Solver that comes with Excel. Add-ins that do not have any of these issues are available at relatively low cost.) The optimal solution turns out to be the one shown in Table 8-4.

3. Forecast Error in Aggregate Plans

The aggregate planning methodology we have discussed in this chapter does not take into account any forecast error. However, we know that all forecasts have errors. To improve the quality of these aggregate plans, forecasting errors must be considered. Forecasting errors are dealt with using either safety inventory, defined as inventory held to satisfy demand that is higher than fore­cast (discussed thoroughly in Chapter 12), or safety capacity, defined as capacity used to satisfy demand that is higher than forecast. A company can create a buffer for forecast error using safety inventory and safety capacity in a variety of ways, some of which are listed here:

  • Use overtime as a form of safety capacity.
  • Carry extra workforce permanently as a form of safety capacity.
  • Use subcontractors as a form of safety capacity.
  • Build and carry extra inventories as a form of safety inventory.
  • Purchase capacity or product from an open or spot market as a form of safety capacity.

Source: Chopra Sunil, Meindl Peter (2014), Supply Chain Management: Strategy, Planning, and Operation, Pearson; 6th edition.

One thought on “Aggregate Planning in Excel in a Supply Chain

Leave a Reply

Your email address will not be published. Required fields are marked *