Fluent Essays

1) Review Chapter 8 “Aggregate Planning in a Supply Chain power point slides for you will have to write up what you learned from this chapter. PLEASE EXEMPLIFY HIGH LEVEL ANALYSIS WITH YOUR WRITING (i.e. 3 Paragraphs Minimum)   2) Link for Ted Talk: This will be part of you participation points: (i.e. 3 Paragraphs Minimum): Esther Ndichu: Hunger isn’t a food issue. It’s a logistics issue  https://www.youtube.com/watch?v=oXlMn3WGHkE&list=PLLXT9l2fAuFdyg0qVwazQbUIr9_yrKKUQ&index=6 Supply Chain Management: Strategy, Planning, and Operation Seventh Edition Chapter 8 Aggregate Planning in a Supply Chain Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved If this PowerPoint presentation contains mathematical equations, you may need to check that your computer has the following installed: 1) MathType Plugin 2) Math Player (free versions available) 3) NVDA Reader (free versions available) 1 Learning Objectives 8.1 Describe aggregate planning and its importance as a supply chain activity. 8.2 Explain the basic trade-offs to consider when creating an aggregate plan. 8.3 Model and solve the aggregate planning problem as a linear program. 8.4 Formulate and solve basic aggregate planning problems using Microsoft Excel. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning and Its Role in a Supply Chain Capacity has a cost and lead times are often long Aggregate planning: Given the demand forecast for each period in the planning horizon, determine the production level, inventory level, capacity level (internal and outsourced), and any backlogs (unmet demand) for each period that maximize the firm’s profit over the planning horizon. How can a firm best use the facilities it has? Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Role of Aggregate Planning in a Supply Chain Identify operational parameters over the specified time horizon Production rate Workforce Overtime Machine capacity level Subcontracting Backlog Inventory on hand All supply chain stages should work together on an aggregate plan that will optimize supply chain performance Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved The Aggregate Planning Problem Given the demand forecast for each period in the planning horizon, determine the production level, inventory level, and the capacity level for each period that maximizes the firm’s (supply chain’s) profit over the planning horizon Specify the planning horizon (typically 3-18 months) Specify the duration of each period Specify key information required to develop an aggregate plan Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Information Needed for An Aggregate Plan Aggregate demand forecast Ft for each Period t over T periods Production costs Labor costs, regular time ($/hr) and overtime ($/hr) Subcontracting costs ($/hr or $/unit) Cost of changing capacity – hiring or layoff ($/worker), adding or reducing machine capacity ($/machine) Labor/machine hours required per unit Inventory holding cost ($/unit/period) Stockout or backlog cost ($/unit/period) Constraints – overtime, layoffs, capital available, stockouts, backlogs, from suppliers Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Outputs of Aggregate Plan Production quantity from regular time, overtime, and subcontracted time Inventory held Backlog/stockout quantity Workforce hired/laid off Machine capacity increase/decrease A poor aggregate plan can result in lost sales, lost profits, excess inventory, or excess capacity Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Summary of Learning Objective 1 (1 of 2) To create an aggregate plan, a planner needs a demand forecast, cost and production information, and any supply constraints. The demand forecast consists of an estimate of demand for each period of time in the planning horizon. The production and cost data consist of capacity levels and costs to raise and lower them, production costs, costs to store the product, costs of stocking out the product, and any restrictions that limit these factors. Supply constraints determine limits on outsourcing, overtime, or materials. The aggregate plan then determines capacity, production, and inventory decisions over the next 3 to 18 months Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Summary of Learning Objective 1 (2 of 2) Good aggregate planning is done in collaboration with both customers and suppliers because accurate input is required from both stages. The quality of these inputs, in terms of both the demand forecast to be met and the constraints to be dealt with, determines the quality of the aggregate plan. The results of the aggregate plan must also be shared across the supply chain because they influence activities for both customers and suppliers. For suppliers, the aggregate plan determines anticipated orders; for customers, the aggregate plan determines planned supply. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Basic Tradeoffs in Aggregate Planning Trade-off between capacity, inventory, backlog/lost sales Chase strategy – using capacity as the lever Flexibility strategy – using utilization as the lever Level strategy – using inventory as the lever Tailored or hybrid strategy – a combination of strategies Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Chase Strategy Vary machine capacity or hire and lay off workers as demand varies Often difficult to vary capacity and workforce on short notice Expensive if cost of varying capacity is high Negative effect on workforce morale Results in low levels of inventory Used when inventory holding costs are high and costs of changing capacity are low Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Utilization Flexibility Strategy Use excess machine capacity Workforce stable, number of hours worked varies Use overtime or a flexible work schedule Flexible workforce, avoids morale problems Low levels of inventory, lower utilization Used when inventory holding costs are high and capacity is relatively inexpensive Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Level Strategy Stable machine capacity and workforce levels, constant output rate Inventory levels fluctuate over time Inventories carried over from high to low demand periods Better for worker morale Large inventories and backlogs may accumulate Used when inventory holding and backlog costs are relatively low Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Summary of Learning Objective 2 The basic trade-offs in aggregate planning involve balancing the cost of capacity, the cost of inventory, and the cost of stockouts to maximize profitability. Increasing any one of the three allows the planner to lower the other two. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Linear Programming Maximize profits while respecting supply chain constraints Red Tomato Tools Capacity determined mainly by workforce size Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Identifying Aggregate Units of Production Aggregate unit should be identified in a way that the resulting production schedule can be accomplished in practice Focus on the bottlenecks when selecting the aggregate unit and identifying capacity and production times Account for activities such as setups and maintenance Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (1 of 8) Table 8-1 Costs, Revenues, and Times at Red Tomato Tools Family Material Cost/ Unit ($) Revenue/ Unit ($) Setup Time/Batch (hour) Average Batch Size Production Time/ Unit (hour) Net Production Time/Unit (hour) Percentage Share of Units Sold A 15 54 8 50 5.60 5.76 10 B 7 30 6 150 3.00 3.04 25 C 9 39 8 100 3.80 3.88 20 D 12 49 10 50 4.80 5.00 10 E 9 36 6 100 3.60 3.66 20 F 13 48 5 75 4.30 4.37 15 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (2 of 8) Weighted average approach Material cost per aggregate unit = (15 × 0.10) + (7 × 0.25) + (9 × 0.20) + (12 × 0.10) + (9 × 0.20) + (13 × 0.15) = $10 Similarly Revenue per aggregate unit = $40 Net production time per aggregate unit = 4.00 hours Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Demand and Costs (1 of 3) Highly seasonal demand Options Adding workers during peak times Subcontract Build up inventory Develop a forecast Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Demand and Costs (2 of 3) Table 8-2 Demand Forecast Month Demand Forecast January 1,600 February 3,000 March 3,200 April 3,800 May 2,200 June 2,200 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Demand and Costs (3 of 3) Starting inventory in January = 1,000 20 working days each month Employees earn $4/hour regular time Regular time = 8 hours/day, then overtime Maximum 10 hours overtime/employee/month End June with 500 units in inventory Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (3 of 8) 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 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools Decision Variables For t = 1, ..., 6 Wt = Workforce size for month t Ht = Number of employees hired at the beginning of month t Lt = Number of employees laid off at the beginning of month t Pt = Production in month t It = Inventory at the end of month t St = Number of units stocked out at the end of month t Ct = Number of units subcontracted for month t Ot = Number of overtime hours worked in month t Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools Objective Function Minimize (Regular-time labor cost) + (Overtime labor cost) + (Cost of hiring and layoffs) + (Cost of holding inventory) + (Cost of stocking out) + (Cost of subcontracting) + (Material cost) Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools Constraints All for t = 1,..., 6 Workforce, hiring, and layoff constraints Capacity constraints Inventory balance constraints Overtime limit constraints Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved 25 Red Tomato Tools (4 of 8) Total cost over planning horizon = $422,660 Table 8-4 Aggregate Plan for Red Tomato Period, t No. Hired, Ht No. Laid Off, Lt Workforce Size, Wt Overtime, Ot Inventory, It Stockout, St Subcontract, Ct Total Production, Pt Demand, Dt 0 0 0 80 0 1,000 0 0 Blank Blank 1 0 16 64 0 1,960 0 0 2,583 1,600 2 0 0 64 0 1,520 0 0 2,583 3,000 3 0 0 64 0 880 0 0 2,583 3,200 4 0 0 64 0 0 220 140 2,583 3,800 5 0 0 64 0 140 0 0 2,583 2,200 6 0 0 64 0 500 0 0 2,583 2,200 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (5 of 8) Higher demand variability Table 8-5 Demand Forecast with Higher Seasonal Fluctuation Month Demand Forecast January 1,000 February 3,000 March 3,800 April 4,800 May 2,000 June 1,400 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (6 of 8) Total cost over planning horizon = $433,080 Table 8-6 Optimal Aggregate Plan for Demand in Table 8-5 Period, t No. Hired, Ht No. Laid Off, Lt Workforce Size, Wt Overtime, Ot Inventory, It Stockout, St Subcontract, Ct Total Production, Pt Demand, Dt 0 0 0 80 0 1,000 0 0 Blank Blank 1 0 16 64 0 2,560 0 0 2,560 1,000 2 0 0 64 0 2,120 0 0 2,560 3,000 3 0 0 64 0 880 0 0 2,560 3,800 4 0 0 64 0 0 1,220 140 2,560 4,800 5 0 0 64 0 0 660 0 2,560 2,000 6 0 0 64 0 500 0 0 2,560 1,400 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (7 of 8) Lower hiring and layoff costs – $50 Total cost over planning horizon = $412,770 Table 8-7 Optimal Aggregate Plan for Hiring and Layoff Cost of $50/Worker Period, t No. Hired, Ht No. Laid Off, Lt Workforce Size, Wt Overtime, Ot Inventory, It Stockout, St Subcontract, Ct Total Production, Pt Demand, Dt 0 0 0 80 0 1,000 0 0 Blank Blank 1 0 35 45 0 1,200 0 0 1,800 1,600 2 0 0 45 0 0 0 0 1,800 3,000 3 42 0 87 0 280 0 0 3,480 3,200 4 1 0 88 0 0 0 0 3,520 3,800 5 0 27 61 0 240 0 0 2,440 2,200 6 0 0 61 0 500 0 20 2,440 2,200 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Red Tomato Tools (8 of 8) Building a Rough Master Production Schedule Table 8-8 Disaggregating the Aggregate Plan at Red Tomato Tools for Period 1 Product Setup Time/ Batch (hour) Average Batch Size Production Time/ Unit (hour) Production Quantity Number of Setups Setup Time (hours) Production Time (hours) A 8 50 5.60 256 5 40 1,433.6 B 6 150 3.00 640 4 24 1,920.0 C 8 100 3.80 512 5 40 1,945.6 D 10 50 4.80 256 5 50 1,228.8 E 6 100 3.60 512 5 30 1,843.2 F 5 75 4.30 384 5 25 1,651.2 Planned production and setup = 10,231.4 hrs Available production time = 10,240 hrs Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Summary of Learning Objective 3 Given the goal of maximizing profits (or minimizing costs) subject to supply chain constraints, the aggregate planning problem can be modeled as a linear program. The first step is to identify a suitable aggregate unit of production and forecast demand in terms of the aggregate unit. The next step is to identify the various costs (such as material, inventory, production) and constraints in the supply chain. We then identify the set of decision variables and construct the objective function and constraints in terms of the decision variables. Linear programming then allows us to optimize the objective function subject to the specified constraints. The aggregate plan should then be converted to a feasible master production schedule. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning in Excel Table 8-9 Building the Basic Aggregate Planning Spreadsheet Output Cells Relationship to inputs Formula in Row 5 Copied to Calls Workforce D5:D10 W sub t = W sub t minus 1 + H sub t minus L sub t. = D4 + B5 – C5 D6:D10 Production I5:I10 P sub t = 40 times W sub t plus start fraction O sub t over 4 end fraction. = 40 times D 5 + left parenthesis E 5 over 4 right parenthesis I6:I10 Inventory F5:F10 I sub t = max of left parenthesis I sub t minus 1 plus P sub t + C sub t minus D sub t minus S sub t minus 1, 0 right parenthesis = max left parenthesis F 4 + I 5 + H 5 minus G 4 minus J 5, 0 right parenthesis F6:F10 Stockout G5:G10 S sub t minus max left parenthesis 0, S, sub t minus 1 + D sub t + I sub t minus 1 minus P sub t minus c sub t Equals max of left parenthesis 0, J 5 + G 4 minus I 5, minus H 5 minus F 4 G6:G10 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (1 of 8) Figure 8-1 Basic Aggregate Planning Spreadsheet Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (2 of 8) For t = 1, ..., 6 Wt = Workforce size for Month t Ht = Number of employees hired at the beginning of Month t Lt = Number of employees laid off at the beginning of Month t Pt = Production in Month t It = Inventory at the end of Month t St = Number of units stocked out at the end of Month t Ct = Number of units subcontracted for Month t Ot = Number of overtime hours worked in Month t Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (3 of 8) Figure 8-2 Spreadsheet Area for Decision Variables Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (4 of 8) Figure 8-3 Spreadsheet Area for Constraints Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (5 of 8) Figure 8-4 Spreadsheet Area for Cost Calculations Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (6 of 8) 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} Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (7 of 8) {Inventory at end of Period 6 is at least 500} G10 = 0 {Stockout at end of Period 6 equals 0} Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Aggregate Planning Using Solver (8 of 8) Figure 8-5 Solver Parameters Dialog Box Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Forecast Error in Aggregate Plans (1 of 2) Forecast errors must be considered, flexibility must be built in Safety inventory Build and carry extra inventories to satisfy higher than forecasted demand Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Forecast Error in Aggregate Plans (2 of 2) Safety capacity Capacity used to satisfy higher than forecast demand 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 Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved The Role of Software in Aggregate Planning The ability to handle large amounts of data Develop optimal solutions using linear programming The ability to handle complex problems (often using linear approximations of nonlinear functions) Stability and data accuracy are important Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Summary of Learning Objective 4 Aggregate planning problems can be solved in Excel by setting up cells for the objective function and the constraints and using the Solver tool to produce the solution. It is best if these plans can account for forecast error, resulting in a plan that has some degree of stability. Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved Copyright Copyright © 2019, 2016, 2013 Pearson Education, Inc. All Rights Reserved ==== ==== +++ ++++ åååå åååå tttt tttt tttt tttt MinWOHL ISPC 6666 1111 6666 1111 6406300500 251030 1 - =+- tttt WWHL 40 4 £+ t tt O PW 11 -- ++=++- ttttttt IPCDSIS 10 £ tt OW =+ tttt WW HL _1 – =´+ t tt O PW 40 4 tttt tt ImaxIPC DS –1 –––0 ( ) 1, =++ ttt ttt Smax SD IPC 0,–1 ––1– ( – =+ * æö ç÷ ø + è = 5 405 4 E D =++ maxFIH GJ 455 –4– ( 5, 0 ) 45 54 05 ( +- -- =max,J G I H F 221 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 try different inputs for each decision variable in the appropriate cells in worksheet Planning. It is * Wt and is shown in cells each period. For each set of inputs, the outputs are calculated as shown in Table 8-8. = *sum(D :D1 ) + *sum(E :E1 ) + 3 *sum(B :B1 ) + *sum(C :C1 ) + *sum(F :F1 ) + *sum(G :G1 ) + 1 *sum(I :I1 ) + 3 *sum(H :H1 ) The goal is to build an aggregate plan by changing inputs in a way that minimizes the total cost Building an Aggregate Planning Spreadsheet Using Solver To access Excel’s linear programming capabilities, use Solver (Data ! Analysis ! Solver). To Chapter8,9-examples), containing the following decision variables: Wt = workforce size for Month t, t = Ht = number of employees hired at the beginning of Month t, t = Lt = number of employees laid off at the beginning of Month t, t = Pt = number of units produced in Month t, t = It = inventory at the end of Month t, t = St = number of units stocked out at the end of Month t, t = Ct = number of units subcontracted for Month t, t = Ot = number of overtime hours worked in Month t, t = FIGURE 8-1 Basic Aggregate Planning Spreadsheet TABLE 8-8 Building the Basic Aggregate Planning Spreadsheet Output Cells Relationship to Inputs Formula in Row 5 Copied to Cells Workforce D5:D10 Wt = Wt-1 + Ht - Lt = D4 + B5 - C5 D6:D10 Production I5:I10 Pt = 40 * Wt + Ot/4 = 40*D5 + (E5/4) I6:I10 Inventory F5:F10 It = max(It-1 + Pt + Ct - Dt - St-1, 0) = max(F4 + I5 + H5 - G4 - J5,0) F6:F10 Stockout G5:G10 St = max(0, St-1 + Dt - It!1 - Pt - Ct) = max(0,J5 + G4 - I5 - H5 - F4) G6:G10 M08_CHOP0203_06_SE_C08.indd Page 221 19/09/14 8:35 AM f-445 /203/AW00176/9780133800203_CHOPRA/CHOPRA_SUPPLY_CHAN_MANAGEMENT06_SE_978013380020 ... 222 Planning) illustrates what this table should look like. The deci- - Also note that column J contains the actual demand. The demand information is included because it is required to calculate the aggregate plan. - straint table may be constructed as shown in Figure 8-3. the six periods. Each constraint will eventually be written in Solver as Cell value5 … , = , or Ú 6 In our case, we have constraints M :M1 = , N :N1 Ú , O :O1 = , : 1 Ú FIGURE 8-2 Spreadsheet Area for Decision Variables Cell M5 N5 O5 P5 Equation 8.2 8.3 8.4 8.5 Copied to M6:M10 N6:N10 O6:O10 P6:P10 Cell Formula =D5-D4-B5+C5 =40*D5+E5/4-I5 =F4-G4+I5+H5-J5-F5+G5 =-E5+10*D5 FIGURE 8-3 Spreadsheet Area for Constraints M08_CHOP0203_06_SE_C08.indd Page 222 19/09/14 8:35 AM f-445 /203/AW00176/9780133800203_CHOPRA/CHOPRA_SUPPLY_CHAN_MANAGEMENT06_SE_978013380020 ... Chapter 8 • Aggregate Planning in a Supply Chain 225 Cell M5 N5 O5 P5 Equation 8.2 8.3 8.4 8.5 Copied to M6:M10 N6:N10 O6:O10 P6:P10 Cell Formula =D5 - D4 - B5 + C5 =40*D5 + E5/4 -I5 =F4-G4+I5+H5-J5-F5+G5 =-E5 + 10*D5 FIGURE 8-2 Spreadsheet Area for Constraints The third step is to create a cell containing the objective function, which is how 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-3. 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 to I20, representing the total cost. The fourth step is to use Data Analysis Solver to invoke Solver. Within the Solver Parameters 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: {All decision variables are nonnegative} {Inventory at end of Period 6 is at least 500} {Stockout at end of Period 6 equals 0}G10 = 0 F10 Ú 500 B5:I10 Ú 0 || FIGURE 8-3 Spreadsheet Area for Cost Calculations M08_CHOP3952_05_SE_C08.QXD 10/27/11 4:41 PM Page 225 Chapter 8 • Aggregate Planning in a Supply Chain 225 Cell M5 N5 O5 P5 Equation 8.2 8.3 8.4 8.5 Copied to M6:M10 N6:N10 O6:O10 P6:P10 Cell Formula =D5 - D4 - B5 + C5 =40*D5 + E5/4 -I5 =F4-G4+I5+H5-J5-F5+G5 =-E5 + 10*D5 FIGURE 8-2 Spreadsheet Area for Constraints The third step is to create a cell containing the objective function, which is how 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-3. 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 to I20, representing the total cost. The fourth step is to use Data Analysis Solver to invoke Solver. Within the Solver Parameters 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: {All decision variables are nonnegative} {Inventory at end of Period 6 is at least 500} {Stockout at end of Period 6 equals 0}G10 = 0 F10 Ú 500 B5:I10 Ú 0 || FIGURE 8-3 Spreadsheet Area for Cost Calculations M08_CHOP3952_05_SE_C08.QXD 10/27/11 4:41 PM Page 225 : ³ B5I10 0 All decision variables are non neg { ative } ³ F10 500 =+== K ttt :WWHL t 1 M5M100––1–0for1, { ,6 } ³+³= K t tt O :WP t N5N10040–0for1, 4 { ,6 } =---++--+== K ttttttt :ISPCDIS t O5O100110for1, { ,6 } ³-³=¼ tt WO t P5:P100100for1, { ,6 } Chapter 8 ◆ Aggregate Planning in a Supply Chain 221 • 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. In addition to the suggestions listed above, a manager should perform sensitivity analysis on the inputs into an aggregate plan. For example, if the plan recommends expanding expensive capacity while facing uncertain demand, examine the outcome of a new aggregate plan when demand is higher and lower than expected. If this examination reveals a small savings from expanding capacity when demand is high but a large increase in cost when demand is lower than expected, deciding to postpone the capacity investment decision is a potentially attractive option. Using sensitivity analysis on the inputs into the aggregate plan enables a planner to choose the best solution for the range of possibilities that may occur. Even though an aggregate plan may provide a map for the next 3 to 18 months, it does not mean that a firm should run aggregate plans only once every 3 to 18 months. As inputs such as demand forecasts change, managers should use the latest values of these inputs and rerun the aggregate plan. Be careful, however, to modify plans in a way that limits volatility. Frequent changes may diminish the extent to which supply chain partners trust the aggregate plan. The Role of Software in Aggregate Planning Aggregate planning is arguably the supply chain area in which software has been used the most. The earliest supply chain software products were aggregate planning modules, often called fac- tory, production, or manufacturing planning. Some of the early modules focused only on obtain- ing a feasible production plan subject to constraints arising from demand and available capacity. Later modules provided tools that chose an optimal solution from among the feasible production plans, based on objectives such as increased output or lowered cost. Figure 8-5 Solver Parameters Dialog Box M08_CHOP1889_07_SE_C08.indd 221 8/23/17 7:54 PM