For Example 9.14, experiment with the Holt-Winters Additive Model for Seasonality spreadsheet to find the best combination of a and g to minimize MAD

Data from Example 9.14

We will use the data for gas usage in the Excel file Gas & Electric. Looking at the chart in the Excel file (Figure 9.2), we see that the time series appears to have stable seasonality; thus the additive model would be most appropriate. We arbitrarily select α = 0.4 and γ = 0.9. 

First, initialize the values of a_t and S_t for t = 1 to 12 using equations (9.13) and (9.14) as a_t = (244 + 228 + ... + 88 + 199)/12 = 105.75, for t = 1 to 12. 

Then, 

S₁ = A₁ - a₁ = 244 - 105.75 = 138.25 

S₂ = A₂ - a₂ = 228 - 105.75 = 122.25 

and so on, up to S₁₂. These are shown in the Excel file Holt Winters Additive Model for Seasonality (Figure 9.19). 

Using the seasonal factor for the first period, the forecast for period 13 is  

F₁₃ = a₁₂ + S₁ = 105.75 + 138.25 = 244 

We may now update the parameters based on the observed value for period 13, A₁₃ = 230: 

a₁₃ = α(A₁₃ - S₁) + (1 - α)a₁₂ 

      = 0.4(230 - 138.25) + (1 - 0.4)105.75 

      = 100.15

S₁₃ = γ(A₁₃ - a₁₃) + (1 - γ)S₁ 

      = 0.9(230 - 100.15) + (1 - 0.9)138.25 

      = 130.69 

The forecast for period 14 is then 

F₁₄ = a₁₃ + S₂ = 100.15 + 122.25 = 222.40 

Other forecasted values are calculated in a similar manner. 

To forecast beyond the range of the last observed value, use formula (9.10) with t = T and k ≥ 1: 

F_{T + k} = a_T + S_{T - s} + k

The forecast for the period 25 (T = 24 and k = 1) is

F₂₅ = a₂₄ + S₁₃ = 105.80 + 130.69 = 236.49 

To find F₂₆, use T = 24 and k = 2: 

F₂₆ = a₂₄ + S₁₄ = 105.80 + 134.45 = 240.25 

We can easily experiment with different values of the smoothing constants by changing the values of a and g in cells B3 and B4, and compare error measures such as MAD.