1 / 36

Example 4.2

Example 4.2. Aggregate Planning Models. 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.

cailean
Download Presentation

Example 4.2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 month’s demand. • SureStep wants to us LP to determine its optimal production schedule and labor policy.

  4. Solution • To model SureStep’s 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. Developing the Model • To develop this model, proceed as follows. • Inputs. Enter the input data in the range B4:B14 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.

  7. 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 D17:E17. Then in cell B20 calculate the number of workers available in month 1 with the formula =B17+B18-B19 and copy this formula to the range C20:E20 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 =MaxOTHrs*B21 in cell B25 and copy it to the range C25:E25 to computer the overtime hours capacity for months 2 and 4.

  8. 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 =StdRTHrs*B21 and copy it to the range C22:E22 for the other months. Then calculate the total hours available for production in cell B27 with the formula =SUM(B23:B24) and copy it to the range C27:E27 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 C32:E32.

  9. Developing the Model -- continued • Inventory each month. Calculate the inventory after production in month 1 by entering the formula =InitInv+B30 in cell B34. For any other month, the inventory after production is the previous month’s ending inventory plus tat month’s production, so enter the formula =B37+C30 in cell C34 and copy it to the range D34:E34. Then calculate the month 1 ending inventory in cell B37 with the formula =B34-B36 and copy it to the range C37:E37. • Monthly costs. Calculate the various costs shown in rows 40 through 45 for month 1 by entering the formulas =UnitHireCost*B18, =UnitFireCost*B19, =RTWageRate*B20, =OTWageRate*B23, =UnitMatCost*B30, =UnitHoldCost*B37 in cells B40 through B45. Then copy the range B40:B45 to the range C40:E45 to calculate these costs for the other months.

  10. 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.

  11. 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<=ProdCap. This ensures that each month’s production does not exceed the limit set by the number of available hours.

  12. Developing the Model -- continued • Demand constraint. Enter the constraint OnHand>=Demand. This ensures that each month’s 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.

  13. 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.

  14. 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 month’s 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.

  15. 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.

  16. Model with Backlogging Allowed • In many situations backlogging is allowed, that is, customer demand, can be met later than it occurs. • We’ll 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 month’s 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.

  17. 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.

  18. 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(B38>0,UnitHoldCost*B38,0) in cell B46 and copy it across. For shortage costs, enter the formula =IF(B38<0,-UnitShortCost*B38,0) in cell B47 and copy it across.

  19. 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 Solver’s Standard GRG Nonlinear algorithm, as shown here.

  20. 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 Solver’s 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.

  21. 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.

  22. 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 B39:E40 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.

  23. 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 =UnitHoldCost*B39 in cell B51. Calculate the shortage cost for month 1 in cell B52 with the formula =UnitShortCost*B40. Then copy the range B51:B52 to the range C51:E52 for the other months. Make sure the totals in row 53 and column F are updated to include the shortage costs.

  24. 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 month’s amount leftover and amount short to be consistent with the desired ending inventory for the month. • Constraint on last month’s inventory. Change the constraints that were previously listed as Onhand>=Demand to LastOnhand>=LastDemand. 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.

  25. Model with Backlogging Allowed -- continued • Logical constraint on ending inventory. Add the constraints Net=EndInv. 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.

  26. Model with Backlogging Allowed -- continued • With more options – it can now backlog demand if it desires – the company’s 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.

  27. 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 B40:D40 as the output cells.

  28. 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.

  29. 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.

  30. 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.

  31. 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 1’s 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.

  32. 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!

  33. 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.

More Related