Real Statistics Data Analysis Tool: Excel doesn’t provide data analysis tools for Holt’s Linear Trend or the Holt-Winters methods. Instead, you can use the Real Statistics data analysis tool to perform these (as well as the analysis for the Single Exponential Smoothing method).
To use this tool for Example 1 and 2 of Holt-Winters Method, press Ctr-m and choose the Time Series option. Click on the Basic forecasting methods option on the dialog box that appears. Now, fill in the dialog box that appears as shown in Figure 1, selecting the Holt-Winter’s option, unclicking the Optimize MSE checkbox, setting the appropriate values for Alpha, Beta and Gamma, and choosing 4 quarters in the # of Seasons and 4 in the # of Forecasts fields.
Figure 1 – Forecasting dialog box for Holt-Winters
After clicking on the OK button the output in Figure 2 is displayed.
Figure 2 – Holt-Winter’s data analysis
Note that the formula in I21 is =I17 and the formula in cell J21 is
=($G$19+$H$19*(ROW(G21)-ROW($G$19)))*I21.
Observation: Note the following about the Forecasting data analysis tool (see Figure 1). The Input Range contains one or two columns (if two, then the first column contains the time labels). The # of Lags parameter is only used with the Simple Moving Averages option, the # of Seasons and Gamma fields are only used with the Holt-Winter’s option, the # of Forecasts and Beta fields are only used with the Holt’s Linear Trend and Holt-Winter’s options (the others methods default to one forecast value) and the Alpha parameter is used with the Single Exponential, Holt’s Linear Trend and Holt-Winter’s options.
The Weights Input Range is only used with the Weighted Moving Averages option. This range should contain values that add up to one and should not include a column heading.
Observation: The Forecasting data analysis tool can also be used to find optimal values of the Alpha, Beta and Gamma parameters when using the Simple Exp Smoothing, Holt’s Linear Trend or Holt-Winter’s Method option. This is done by clicking on the Optimize MSE checkbox (the default). The data analysis tool uses Solver to find the parameter values that minimize the MSE value.
For example, if we fill in the dialog box as shown in Figure 1, except that we click on the Optimize MSE checkbox (and use the default values of zero for Alpha, Beta and Gamma and one for # of Forecasts, we get the output shown in Figure 3.
Figure 3 – Optimized Holt-Winter’s Forecast
Actually, the output originally displays the symbol “>” for the forecasted times on the x-axis of the Forecast chart. To get the time values shown in Figure 3, you simply need to replace the symbol “>” in range E20:E23 by the values shown in Figure 3.
