# AnalyticBridge

A Data Science Central Community

# Holt-Winters forecasting

Does anyone know of a good Excel-based algorithm for Holt-Winters exponential smoothing? I have seen several that allow the user to enter values for alpha, beta and gamma, but don't include a method for optimizing the values based on MSE.

I have yet to find any two algorithms that generate similar results because of the various methods for setting the intial values for the level, seasonal and trend components.

Views: 5526

### Replies to This Discussion

You can do this in Excel using Solver. Set the objective function to "minimize" to the cell containing the average MSE. Then set your constaints for the alpha, beta, and gamma cells.

-Ralph Winters
Ralph-

Thanks for the response. I probably didn't explain my challenge very well: I can generate the MSE minimizing solution easy enough. However, as you know, the forecast results are very sensitive to the initial estimates for the smoothing, trend and seasonal factors.

I'm not a professional statistician (I'm a lowly IE) so I struggle with the various methodologies for calculating the initial conditions. I was hoping to find a Excel-based solution with a straightforward (or, one that I can explain) way of calculating the initial conditions.
At this point it is more of an art than a science. The major question is how quickly do you want your baseline forecasts to adapt to changes in the data? An alpha of .3 and beta of .0025 are reasonable starting points. Check out the work of Jake Brutlag who has some interesting thoughts on setting initial parameters.

http://www.usenix.org/events/lisa2000/full_papers/brutlag/brutlag_h...

-Ralph Winters
Why would you want to impose a model form onto your data? Yes the coefficients vary but the model dooes not and that's hardly an excuse. Its like fitting a square peg into a round hole. Let your "data speak" and identify the model not force it. Plus you are ignoring any outliers in your data which would negatively impact your coefficients. You can find these by plotting the residuals or better yet using autocorrelation function (acf) to verify this! Simple methods like this don't work. Plus do you know of any causal variables that drive the series like price, promotion, # of housing starts? If you can identify these then you will put yourself in a much better position to analyze your data. I'll ask you to send me your data ([email protected]) and I will analyze it and send it back to you and show you what Box-Jenkins (which is a superset of all models including exponential smoothing)can do.

Tom Reilly