供应链管理英文原书第6版Specialty Packaging Corporation

The results for this case are obtained using the accompanying spreadsheet Chapter 7- Specialty Packaging Corporation. Given the clear seasonal pattern in the data, the forecast is made using the static forecasting method and Winter’s model (both outlined in Chapter 7.

Static Forecasting Method

The results for the static forecasting method is contained in the worksheet Static Forecast. For Black Plastics, the first step is to obtain deseasonalized demand using Equation (7.2). This is obtained in Cells D31:D46. The next step is to run a regression between deseasonalized demand and period value (contained in Cell B31:46). The results of the regression are contained in Cells R28:Z47. The deseasonalized demand in period 0 is given by the Intercept in Cell S46. The trend is given by the value in Cell S47. These two values are used to obtain the deseasonalized demand values in Cells E29:E48. The ratio of the actual demand in Cells C29:48 and the deseasonalized demand in Cells E29:E48 is used to estimate the seasonal factors in Cells F29:F48. The seasonal factors are then averaged in Cells S53:S56. The average seasonal factors are then used to evaluate the forecasts in Cells G29:G60 using Equation (7.1). The results are as follows:

The various error estimates are shown in Cells I29:P48.

For Clear plastics the methodology used is identical except that the results are shown in Cells A64:Z96. The static forecast for Clear Plastic is as follows:

Winter’s Model

The evaluations for Winter’s model are shown in worksheet Winter. The first step in Winter’s model is to obtain initial estimates of level, trend and seasonal factors. This is done exactly as in the Static Forecast method, except that the Intercept in Cell S46 (for Clear Plastic) becomes the level in period 0 (Cell D29), Cell S47 becomes the trend in period 0 (Cell E29) with initial seasonal factors being estimated in Cells

S53:S56 exactly as for the Static Model. The level, trend and seasonal factors are then updated using Equations (7.18)-(7.20) with values of α, β, and γ as shown in Cells K20:22. In the spreadsheet the values of α, β, and γ are chosen using Excel to minimiz e MAPE (in Cell O49). These may also be chosen to minimize MAD (Cell M49) or MSE (Cell L49). The results with Winter’s model t urn out to be the same as the results with the Static model because the optimal value of α turn out to be 0. One can use the sprea dsheet to experiment with other values of α, β, and γ.