This example illustrates how to use XLMiner's Holt-Winters Smoothing technique to uncover trends in a time series that contains seasonality. On the XLMiner ribbon, from the Applying Your Model tab, select Help - Examples, then Forecasting/Data Mining Examples, and open the example data set, Airpass.xlsx. This data set contains the monthly totals of international airline passengers from 1949-1960. After the example data set opens, click a cell in the data set, then on the XLMiner ribbon, from the Time Series tab, select Partition to open the Time Series Partition Data dialog.

Select Month as the Time Variable, and Passengers as the Variables in the Partition Data, then click OK to partition the data into Training and Validation Sets.

Time Series Partition Dialog 

The Data_PartitionTS worksheet is inserted to the right of the Data worksheet.

Click the Data_PartitionTS worksheet, then on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt Winters. Three additional menu items appear: Multiplicative, Additive, and No Trend. This example will create three different forecasts, one for each Holt-Winters model type, beginning with Multiplicative.

Select Multiplicative to open the Holt Winters Smoothing (Multiplicative Model) dialog.

Month has already been selected for the Time variable. Select Passengers as the Selected variable.

Since the data set contains airline passengers potentially flying during the summer and holiday months (i.e., December), this example contains some seasonality.

Since it takes 12 months to complete the seasonality cycle, enter 12 for Period. The # Complete seasons is automatically entered with the number 7. This example uses the defaults for the three parameters: Alpha, Beta, and Gamma.

Values between 0 and 1 can be entered for each parameter. As with Exponential Smoothing, values close to 1 will result in the most recent observations being weighted more than earlier observations.

In the Multiplicative model, it is assumed that the values for the different seasons differ by percentage amounts. Produce Forecast on validation is selected by default.

Holt Winters Smoothing Multiplicative Model Dialog 

Click OK to run the smoothing technique.

Two worksheets, HoltWinterMultOutput and HoltWinterMult_Stored, are inserted to the right of the Data_PartitionTS worksheet, and include the following results. For more information on the HoltWinterMult_Stored worksheet, see the Applying Your Model - Scoring New Data section.

      Time Plot of Actual Vs Forecast Training Data

  Training Error Measures

  Validation Error Measures

  Time Plot of Actual Vs Forecast Validation Data

When viewing the MSE (Mean Squared Error) term in the Error Measures (Validation) table, this value is fairly high, 1207.62. In addition, the peaks for the Forecast data appear to lag behind the peaks in the Validation Data. This suggests that the Trend (Beta) parameter is too large.

Return to the Multiplicative method one more time using the Optimize parameter. This parameter chooses the best values for the Alpha, Trend, and Seasonal parameters based on the Forecasting Mean Squared Error. It is recommended that this feature be used carefully, as this option can lead to overfitting. An overfit model rarely exhibits high predictive accuracy in the Validation Set.

Click back to the Data_PartitionTS worksheet, and on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt-Winters - Multiplicative to open the Holt Winters Smoothing (Multiplicative Model) dialog.

Select Passengers for Selected variable, and enter 12 for Period. Produce rorecast on validation is selected by default. Under Parameters - Weights, select Optimize.

Holt Wiinters Smoothing (Multiplicative Model) Dialog 

Click OK to proceed with the smoothing technique.

The worksheet HoltWinterMultOutput1 is inserted after the HoltWinterMultOutput worksheet, and includes the following results.

  Parameters/Options Table 

The Parameters/Options table displays the parameter settings as chosen by the Optimize feature for Alpha (0.858), Beta (0.0035), and Gamma (0.917). (Recall that the default settings were 0.20 (Alpha), 0.15 (Beta), and 0.05 (Seasonal).) Scroll down to find the Training and Validation Error Measures.

  Time Plot of Actual Vs Forecast (Training Data)   

  Time Plot of Actual Vs Forecast (Validation Data)

 Time Plot of Actual Vs Forecast (Validation Data)

  Validation Error Measures

While the MSE for the training data set has decreased from 112.86 to 73.73, the MSE for the Validation Set actually increased from 1207.62 to 2926.55. We can see from the graph that the model produced from this smoothing technique is not a great fit. The forecast for the Validation Set still lags behind the actual data, and the peaks for the forecast are not nearly as high as the peaks in the actual data.

Next, create a new model using the Additive model. This technique assumes the values for the different seasons differ by a constant amount. Click back to the Data_PartitionTS worksheet, then on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt-Winters - Additive to open the Holt Winters Smoothing (Additive Model) dialog.

Month has already been selected for the Time variable. Select Passengers for Selected variable, and enter 12 for Period. Produce forecast on validation is selected by default.

Holt Winters Smoothing Additive Model Dialog 

Click OK to run the smoothing technique. Two worksheets, HoltWinterAddOutput and HoltWinterAdd_Stored, are inserted at the end of the worksheet. For more information on the HoltWinterAdd_Stored worksheet, see the Applying Your Model - Scoring New Data section.

Click the HoltWinterAddOutput worksheet to display the following results.

      Time Plot of Actual Vs Forecast (Training Data)

  Error Measures (Training)

 Time Plot of Actual Vs Forecast (Validation Data)

  Validation Error Measures

When comparing the Additive Smoothing results with the Multiplicative Smoothing Results, the Multiplicative method performed better on the Training Set, but not the Validation Set when the default settings were used (112.86 - Multiplicative vs. 214.11 - Additive in the Training Sets, and 1207.62 - Additive vs. 2314.92 - Multiplicative in the Validation sets).

Again, use the Additive model example with the Optimize feature. Click back to the Data_PartitionTS worksheet, and on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt-Winters - Additive to display the Holt Winters Smoothing (Additive Model) dialog.

Select Passengers for Selected variable, and enter 12 for Period. Produce forecast on validation is selected by default. Under Parameters - Weights, select Optimize to run the Optimize algorithm, which chooses the best values for the three parameters, Alpha, Beta, and Gamma.

Holt Winters Smoothing (Additive Model) Dialog 

Click OK, then click the HoltWinterAddOutput1 worksheet to view the Parameters/Options table.

  Parameters/Options 

Notice the parameter values chosen by the Optimize algorithm were 0.858 for Alpha, .015 for Beta, and 0.05 for Gamma. Scroll down to view the results of the model fitting.

  Parameters/Options Table   

  Time Plot of Actual Vs Forecast (Validation Data)  Time Plot of Actual Vs Forecast (Validation Data)

  Validation Error Measures

When comparing the Additive Smoothing results found with the Optimize feature with the results from Additive Smoothing using the default parameter settings, we see that the Optimize method performed better on the Training Set but not the Validation Set (214.11 - Defaults vs. 105.30 - Optimize in the Training Sets, and 2314.92 - Defaults vs. 4444.13 - Optimize in the Validation Sets).

The last Holt-Winters model should be used with time series that contain seasonality, but no trends. Click back to the Data_PartitionTS worksheet and on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt-Winters - No Trend to open the Holt Winters Smoothing (No Trend Model) dialog.

Month has already been selected as the Time variable. Select Passengers as the Selected variable, and enter 12 for Period. Produce Forecast on validation is selected by default. Notice that the trend parameter is missing. Values for Alpha and Gamma can range from 0 to 1. A value of 1 for each parameter will assign higher weights to the most recent observations, and lower weights to the earlier observations. This example will accept the default values.

Holt Winters Smoothing (No Trend Model) Dialog 

Click OK to run the smoothing technique. The worksheet HoltWinterNoTrendOutput is inserted. Click this worksheet to view the following results.

  Time Plot of Actual Vs Forecast (Training Data)   

  Time Plot of Actual Vs Forecast (Training)

  Time Plot of Actual Vs Forecast (Validation Data)

  Error Measures (Validation)

When comparing the No Trend Smoothing results with the Additive and Multiplicative Smoothing results, the Additive method performed worse on both data sets (326.78 in the Training Set compared to 214.109 (Additive), and 112.86 (Multiplicative), and 16,504.36 on the Validation Set compared to 2,314.92 (Additive), and 2926.55 (Multiplicative)), when the default parameter settings were used.

Again, use the No Trend model with the Optimize feature. Click back to the Data_PartitionTS worksheet, and on the XLMiner ribbon, from the Time Series tab, select Smoothing - Holt-Winters - No Trend to open the Holt Winters Smoothing (No Trend Model) dialog.

Select Passengers for Selected variable, and enter 12 for Period. Produce forecast on validation is selected by default. Under Parameters - Weights, select Optimize to run the Optimize algorithm, which chooses the best values for the two parameters, Alpha and Gamma.

Holt Winters Smoothing (Multiplicative Model) Dialog 

Click OK, then click the HoltWinterNoTrendOutput1 worksheet to view the Parameters/Options table.

  Parameters/Options Table 

Notice the parameter values chosen by the Optimize algorithm were 0.9839 for Alpha and 0.2334 for Gamma. Scroll down to view the results of the model fitting.

  Time Plot of Actual Vs Forecast (Training Data)   

  Training Error Measures

  Time Plot of Actual Vs Forecast (Validation Data)

  Validation Error Measures

When comparing the No Trend Smoothing results for the Training Set, and the Optimize feature (140.23) with the results from Additive (105.30) and Multiplicative (73.73), we see that the Multiplicative model performed best.

When comparing the No Trend Smoothing results for the Validation Set, and the Optimize feature (14184.35) with the results from Additive (4444.13) and Multiplicative (2926.55), we see that the No Trend model performed best.