From time to time people have asked me how to implement Holt Winters (trend-seasonal exponential smoothing) in Excel. Let me start by saying that although Excel is probably the most common forecasting tool in business, it is also not a good one. It does not provide many useful options and tools and there is plenty of space for mistakes.

I have produced a small example of Holt Winters that you can download. It comes with two options, depending on how the initial values are calculated. The first option is using a simple heuristic, while the second requires finding optimal initial values with solver.

Two words of caution: i) Excel’s optimiser can easily get stuck to local minima, so try to start with reasonable starting values; ii) Do not use Holt-Winters method when you do not have trend-seasonal data. Instead prefer the simpler Holt or Single Exponential Smoothing methods.

You can download the example spreadsheet here.

My personal view is that organisations should invest in expertise in forecasting and appropriate systems. There are multiple benefits to be gained by accurate and robust forecasts. A relatively inexpensive solution is using R. Several forecasting related packages exist. Prof. Rob Hyndman maintains the excellent forecast package that includes a state-of-the art exponential smoothing implementation. Alternatively you can consider the TStools package that includes a very flexible exponential smoothing implementation by Ivan Svetunkov, amongst other useful forecasting tools and methods (like the Theta method) or try the MAPA package, which implements the Multiple Aggregation Prediction Algorithm that has demonstrated very good performance relatively to exponential smoothing.

I have used this method based on nature of our business. Good one

regards

Hello Professor Kourentzes – How would you handle the calculations if a variable is zero? You have two formulas that divide either by “Level” or by “Season” I have some data that would make these zero. FDor not I used an if statement and declared that formula’s result to be zero.

Any advise would be appreciated.

Thank you,

Anthony

Unfortunately Holt-Winters does not handle zeros well, because of the multiplicative interaction between level and season. Typically there are two ways forward, none of which is perfect.

Having said that, if your seasonality is really multiplicative then switching to the additive exponential smoothing will not be ideal.

Hope this helps!

Nikos

I have tried the first option, to estimate business sales of my company for 2016, a medium size company with strong seasonality, due to the fact that it sell products for building sector. Monthly sales usually range from 400K€ in AUgust (lowest point) to 1,2 MILLIOn €(high peaks) in other months (July, MAy)… the pattern is pretty almost the same, considering last 4 years.

Although this, forecast for 2016 is clearly overestimated… do you think that is a problem of fixing smoothing parameters for level, trend and season?

Have a good day

Greetings from Italy

Hi Mario,

I think your assessment sounds reasonable. If the forecasts looks unreasonably highthen I would seuspect it might be something with the smoothing parameters of the level and the trend (and perhaps the initial values, if these are not optimised). A good heuristic is to set a low alpha (smoothing parameter for level) first and then proceed to the rest of the parameters. Did you optimise the parameters or set them manually?

Nikos

Well to be honest I have substituted my monthly sales values and then applied all the formulas inserted in the “first option- excel sheet”. The results are not that far in the In –sample-period and follow quite well the seasonality of our business. Regarding forecast for 2016 , the seasonality is good, but monthly estimates are clearly overestimated , in average of +50%.

So I have tried to lower level parameter (from 0,51 to 0,38) keeping trend and season parameter constant and it improves. But the question is: is this scientific? I do not think so. Do you have a more scientific approach to balance the model?

Thanks for help.. never faced this model during my economics studies.

I presume that the 0.51 was the result of the optimisation? Perhaps then it is a problem with the initial values and optimising all would be better (see option 2, the recommended one). However the optimisation in Excel is rather weak, so it may not be able to get good parameters at all for some time series. A very practical approach is to just not allow some parameters to become very high (for example restrict alpha, the level smoothing parameter to < = 0.2). A final point to make is that optimisation using MSE will not always be able to give you good forecasts! There is ongoing work (see this post) to make the optimisation better and more reliable.

OOops .. I have forgotten to minimize the MSE. I have just done it with the excel solver and effectively MSE is minimized and parameters become LEVEL 0,17, Trend 1 and Season 0,30. The forecast for the In- sample period fits even better than before and the forecast for the out-sample period (2016) is now acceptable (just +5% against my personal forecast).

Now I will try the second option. Thanks so much for this model. I think is pretty important to extend quantitative forecast analysis even for small/medium size company, like mine.

Thanks for help

Hi Nikolas,

I’m working in implementing a Sales forecast tool with your Excel file (option 2). It is delivering good results. I’m preparing the Slides to present it to senior management and I have a doubt.

Is this file based on multiplicative Holt-winters or additive?. I have read the comments and seems that it is a multiplicative one, but not sure

Many thanks

It is the multiplicative version. Good luck with your presentation!

Best,

Nikos

Hello Dr. Kourentzes,

Let me thank you very much for taking the time to post this informative article.

I have simulated the Holt Winter model listed above and a few other models. (eg: Holt, Holt Winters, Linear Regression etc). One of the challenges I faced in all of these models is when trying to minimize MAPE or MAD by optimizing the smoothing parameters is that Excel returns extreme values (for eg: Alpha = 1, Beta = 1, Gamma =.45).

I would like to ask you 2 questions:

1. I believe this is due to the fact that the Excel model is solely focused exclusively on reducing MAPE & MAD, based on ex-post forecast. Therefore for example, it gives very high weightage to recent forecasts. Is this the reason? If so, how can we overcome it?

2. Does variance of the order history affect the above phenomenon?

It depends on the data that you are using. The effect of high parameters is to give very high weighting to recent periods, but it is not the reason for the optimisation results. On the other hand the variance of the order history will be driving this. Let me give you an example. Suppose let’s say that you have a very well behaved demand pattern and at some point sales jump to a new level. Without the jump the optimiser would typically prefer low parameters. The jump will cause your optimiser to shift parameters towards higher values, because this way it will be able to minimise on average errors for all past sales (the type of error squared error/absolute error/etc. will affect the exact solution, but the behaviour will be similar). In fact increasing the parameter values may not be necessary, as that jump in sales happened in the past and we still care about appropriately smoothing sales after the jump. A similar effect may be caused by promotions (depending how many there are and how much is the uplift). What does that mean in practice? Two things: (i) although optimisation is a great way to get model parameters automatically, it is not guaranteed to work best all the time; and (ii) we should override results that do not make much sense!

One more point that you may find helpful: optimising on squared errors will try to minimise any extreme errors, so it will focus on such jumps in sales or promotions. On the other hand optimising on mean absolute error will focus more on the sales baseline. Typically we use squared errors (MSE), but absolute errors/deviation (MAE/MAD) is also reasonable. MAPE on the other hand is a bad idea, as it does not weight positive and negative errors equally. The division by the actual sales at each period may cause additional problems!

I realize from the above comments that you have proposed that we restrict the values by placing bounds on them. Is there a better method? I did peruse the article you had mentioned as a response to Mario’s comments, but I was not able to understand them.

Is there a possibility that you could explain it in layman’s terms?

– Thank You

Hello, I really appreciate for your excel file!

But, I have a question about parameter, alpha, beta and gamma.

How can I find them using excel, matlab or whatever??

Thank you!

Hi Ashley,

In the example file I calculate the in-sample Mean Squared Error (MSE – cell B10 in either options). Use solver to minimise this, by changing the cells that contain the smoothing parameters (cells B4:B6 in option 1 or cells B13:B29 in option 2). In MATLAB or R you would use the same logic. You would fit the model with some initial smoothing parameters and values and calculate the in-sample MSE. Then you would minimise that by asking some optimiser to change the smoothing parameters. Finally note that other errors could be used instead of MSE, but this is one of the most common.

Nikos

Hello Mr. Nikos,

Could you please suggest me a book that it can help me to understand the meaning of the method’s results in Excel?

This online book is a god starting point to understand exponential smoothing: https://www.otexts.org/fpp/7

This is a good general book about business forecasting, which includes an easy to understand chapter on exponential smoothing: https://www.amazon.com/Principles-Business-Forecasting-J-Ord/dp/1133584403/ref=mt_paperback?_encoding=UTF8&me=

Hello Nikos,

Thank you for the excel!

If we put a value for gamma > 0, then the sum of the seasonality factor over a higher can be != 12.

Should we do somthing about it?

Thank you 🙂 Indeed, over time the seasonal indices can veer off to sums that do not make much sense. This is addressed with the so-called seasonal index normalisation (you can look it up in: Hyndman, Rob, et al. Forecasting with exponential smoothing: the state space approach. Springer Science & Business Media, 2008). The normalisation will force the indices to retain their properties over time. Nonetheless, normalising or not does not affect the final forecast at all!

The excel would become quite a bit more complicated to introduce normalisation, and since it has no impact in the forecasts I did not add it.

Hello Sir,

Your 2nd option is the good one. I need one clarification that as I have 3 years of actual data & I need to forecast next 1 or 2 years more by holt’s winter method. So in your example I have seen last 1 year forecasting trend is exactly same for all the months. Is it possible to get different level, trend & seasonality data for next 1 year by holt’s winter method? Thanks for your help.

Hi Nahed,

If I understand your question correctly, the forecasted trend, level and seasonal indices would alwasy be the same, as they reflect the best estimation of the future values, using all available data up to that point. So essentially you would have a linear trend of the same slope going forward for all months and the seasonal shape repeated every year. On the other hand, in-sample the exponential smoothing estimates will keep on updating, as they capture data as they become available.

Pingback: Principles of Business Forecasting 2e – Forecasting