In the **Holt Winters Method** (aka **Triple Exponential Smoothing**), we add a seasonal component to the Holt’s Linear Trend model.

Let *c* be the length of a seasonal cycle. Thus *c* = 12 for months in a year, *c* = 7 for days in a week and *c* = 4 for quarters in a year. The model takes the following form for all *i > c*

where 0 < *α* ≤ 1, 0 ≤* β* ≤ 1 and 0 ≤ *γ* ≤ 1–*α*. The initial values, i.e. where 1 ≤* i * ≤ *c*, are given by

An alternative form of these equations is given by

where

Note that if *γ* = 0, then the Holt-Winters model is equivalent to the Holt model and if *β* = 0 and* γ* = 0, then the Holt-Winters model is equivalent to the Single Exponential Smoothing model.

**Example 1**: Calculate the forecasted values of the time series shown in range C4:C19 of Figure 1 using the Holt-Winter method with *α* = .5, *β* = .5 and *γ* = .5.

The result is shown in Figure 1. First we calculate *s*_{1}, *s*_{2}, *s*_{3}, *s*_{4}, where *c* = 4, as shown in range F4:F7. We do this by inserting the formula =C4/AVERAGE(C$4:C$7) in cell F4, highlighting the range F4:F7 and pressing **Ctrl-D**.

Next, we calculate *u _{c}* and

*v*by placing the formula =C7/F7 in cell D7 and the value 0 in cell E7.

_{c}We now insert the formula =C$22*C8/F4+(1-C$22)*(D7+E7) in cell D8, the formula =D$22*(D8-D7)+(1-D$22)*E7 in cell E8, =E$22*(C8/D8)+(1-E$22)*F4 in cell F8 and the formula =(D7+E7)*F4 in cell G8, and then highlight the range D8:F19 and press **Ctrl-D**.

**Figure 1 – Holt-Winters Method**

**Observation**: For any value of *i*, the forecast at time *i+h* is given by the formula

**Example 2**: Forecast the y values for 2014 from Example 1 (i.e. the next 4 quarters).

The result is shown in Figure 2. The values through 2013 are copied from Figure 1. The forecasted value for Q1 of 2014 is 36.87209 (cell N20), as calculated by the following formula with reference to cells in Figure 1.

=(D$19+(L20-L$19)*E$19)*F16

The other three forecasted values are calculated by highlighting the range N20:N23 and pressing **Ctrl-D**.

**Figure 2 – Holt-Winters’ Forecast**

**Observation**: As we have done in Example 2 of Holt’s Linear Trend, we can use Solver to determine which values of alpha, beta and gamma yield the best Holt-Winters’ fit for the data in Example 1. In this case you need to assign the value *α+γ* to some cell and add a constraint to Solver making this value less than 1 (as well as constraining *α* and *β* to be less than 1). This turns out to be *α* = .184124, *β* = .439756 and *γ* = .417974, which yields a value for *MSE* of 19.9562.

Awesome work! Crystal clear.

where can I download the excel file?

Thanks!

http://www.real-statistics.com/free-download/

Charles

Hi Charles,

Thanks for the help.

If the time series data has y=0 as some entry the calculation for S and u, v start giving DIV#0 error. If i replace 0 with a very small number like 0.001 the value for initial S goes very low and value of u,v gets multiplied by same factor and the forecast has huge errors.

Sample Data : YEAR Month mm

2012 Jan 16.7

2012 Feb 0

2012 Mar 0

2012 Apr 8.2

2012 May 0

2012 Jun 2.1

2012 Jul 438.4

2012 Aug 367.6

2012 Sep 71.5

2012 Oct 0

2012 Nov 0

2012 Dec 0

What do you suggest can be done?

One approach is to add a constant to all the values (not just the zero values). E.g. add 1.

Charles

Thanks for your quick reply Charles!

Really appreciate the effort and time you spend helping others. Thanks again!

is there will be any different in the result between adding 1 and adding 2 to all the values?

Gabriel,

I don’t completely understand your question, but you should be able to add any fixed constant to all your data values. The result should then be incremented by that constant, but I haven’t actually checked to make sure this is so.

Charles

very nice, just need a clarification, for prediction the formula is =(D$18+(L20-L$19)*E$18)*F16, why D$18 and E$18 NOT D$19 E$19 as, it predicts the value of 20.

Debadrita Panda,

You are correct. Thank you very much for identifying this error. I have now corrected the error on the referenced webpage.

I really appreciate your help in improving the Real Statistics website.

Charles

Great explanation but is it possible to use Winter’s model to forecast farther out than c observations (4 in this case)? What ways are their to forecast say for 2015 or 2016 in this example?

Mike,

Yes, you can forecast further out than c. Keep in mind, the further out you go the less accurate the forecast will be. See the following webpage regarding how to specify longer forecasting periods using Real Statistics:

Forecasting Tools

Charles

The link you have mentioned does not explain how to forecast further out than c. Kindly show how can we forecast for 2015 or 2016 using excel formula only.

Namita,

If you have Excel 2016 and want to use only pure Excel formulas, then see the following webpage:

http://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/excel-2016-forecasting-functions/

Charles

Dear Charles,

I replicated your model, then applied “=FORECAST.ETS” function (as well as “Forecast Sheet” in the Data Tab, which uses the same “ETS AAA” algorithm). ETS AAA Excel algorithm is stated to implement Holt-Winters method.

The first issue is that ETS AAA choses alpha, beta and gamma, which are 0.251 / 0.001 / 0.001 respectively for your example data. These Excel algorythm values of α, β, γ are even worse than your initial 0.5/ 0.5 / 0.5 in terms of MSE (Excel figures worsened the error by around 8 points).

The second issue is that even if we accept 0.251 / 0.001 / 0.001 for α, β, γ, the predictions are different:

* in your model the predictions are as follows (decimals are shortened but not rounded):

24,7 for period #17

34,6 for period #18

19,8 for period #19

61,9 for period #20

* Excel’s ETS AAA (claimed to be Holt-Winters) algorithm predicts:

38,5 for period #17

47,3 for period #18

41,2 for period #19

60,3 for period #20

To sum up, my ultimate concerns are rather simple: “Can we securely use Excel’s “=FORECAST.ETS” function, believing it is backed by established Holt-Winters method? Otherwise, should we avoid using Excel’s ETS AAA, for we don’t understand the calculations behind this ‘black box’ algorythm?”

Thank you

Dmitry,

I honestly don’t understand the Excel calculations either. I would imagine that they make sense, but I need to find some reference that explains the calculations. I know that they don’t agree with the values produced by the Real Statistics software. I will try to find something that explains what is going on and get back to you.

Charles

Dmitry,

Apparently, others share your concern about Excel’s FORECAST.ETS function. See the following:

https://social.technet.microsoft.com/Forums/ie/en-US/b29d4216-3882-4883-86de-4b16157f491f/calculation-transparency-behind-forecastets-function?forum=excel

Charles

Charles,

Thank you for your opinion and the link. I haven’t seen the later.

Dmitry

Awesome, thank you!

However I do have a question, and I cannot find the answer in any of the tutorials.

It’s a simple question probably everyone will encounter:

How do I forecast for more than one season?

Assume that I’m using the Additive model, my daily data shows a strong weekly recurrence, usually when we predict for the next season(week) we fix the level and trend components of the last day of previous week and use the seasonal component of the same weekday of last week. But what shall I do if I want to forecast more than 1 week?

Thank you!

Markus,

You can forecast as many seasons as you like using Holt-Winters. If you are using the Real Statistics data analysis tool just make the # of Forecasts value as large as you need (e.g. enter 14 for 2 weeks).

Charles

Hi Charles thanks for the reply!

Hi Charles,

Great explanation. .. as I followed your example, I noticed the moving sum of the new calculated s over C periods is no longer equal to C or averaging to 1. How important is it to normalize C as we use Holt Winter model?

I reworked the example using normalized s figures, and the MSE improved to 34.53; however, MAPE deteriorated from 17.91% to 18.22%.

I appreciate your view on this.

Thanks.

Ayman,

This true for the first year since that is how the values are initiated. After the first year it is not necessary for any consecutive 4 quarter period to average to 1.0. In fact it would be impossible unless all the quarter 1 values are the same, and similarly for Q2, Q3 and Q4.

Charles