Modify the Excel file Holt-Winters Additive Model for Seasonality (see Example 9.14) to implement the HoltWinters multiplicative seasonality model with no trend.

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.