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.

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.

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.

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.

Click **OK** to proceed with the smoothing technique.

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

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

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.

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.

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.

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

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.

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.

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

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.

Click **OK**, then click the** HoltWinterNoTrendOutput1 **worksheet to view the

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

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.