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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
You can download the example spreadsheet here<\/a>.<\/p>\n My personal view is that organisations should invest in expertise in forecasting and appropriate systems. There are multiple benefits<\/a> 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<\/a> package that includes a state-of-the art exponential smoothing implementation. Alternatively you can consider the TStools<\/a> 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<\/a> package, which implements the Multiple Aggregation Prediction Algorithm that has demonstrated very good performance relatively to exponential smoothing.<\/p>\n