If you forecast anything with both a trend and a repeating seasonal pattern - monthly sales, web traffic, energy use - a plain moving average won't cut it. Holt-Winters (triple exponential smoothing) is the classic method that handles both, and you can run it in Excel with no add-ins.
The three pieces
Holt-Winters tracks three things and updates each as new data arrives:
- Level - where the series is right now.
- Trend - how fast it's climbing or falling.
- Seasonality - the repeating pattern within a cycle (e.g. 12 months).
Each gets its own smoothing weight (alpha, beta, gamma) between 0 and 1. A higher weight reacts faster to recent data; a lower one is smoother and more stable.
The update equations (additive)
Level: l_t = alpha*(y_t - s_{t-m}) + (1-alpha)*(l_{t-1} + b_{t-1})
Trend: b_t = beta*(l_t - l_{t-1}) + (1-beta)*b_{t-1}
Season: s_t = gamma*(y_t - l_t) + (1-gamma)*s_{t-m}
Forecast: y_hat = l_t + h*b_t + s_{t-m+h}
where m is the season length (12 for monthly data with a yearly cycle).
Doing it in Excel
1. The one-function way. Excel 2016+ has FORECAST.ETS, which is essentially auto-tuned Holt-Winters:
=FORECAST.ETS(target_date, values, timeline, seasonality)
Set seasonality to 12 for monthly data, and pair it with FORECAST.ETS.CONFINT for a confidence band.
2. The manual way. Build the level/trend/season columns straight from the equations so you can audit every step - the only way to really answer "why does it predict that?". I wrote up the full manual build with initialisation and a worked example here: Holt-Winters in Excel.
Pitfalls worth knowing
- Too few cycles. You need at least two full seasonal cycles (24 months for monthly data) before the seasonal component is trustworthy.
- Additive vs multiplicative. If seasonal swings grow as the series grows, use the multiplicative form.
- Over-reacting. Large weights chase noise; auto-tuning by minimising one-step error usually beats eyeballing them.
A quick sanity-check
If you just want a fast trend forecast from a column of numbers without building the whole sheet, I made a free browser tool that auto-tunes the weights and charts the result: free forecast calculator. No signup, runs locally in your browser.
Forecasting won't make the future certain - but Holt-Winters gives you a defensible, transparent baseline, which is usually what the conversation actually needs.
