FormulasAdvanced9 min read

FORECAST.ETS vs a Manual Seasonal Index: Which Gives Better Forecasts?

Excel's built-in FORECAST.ETS handles seasonality automatically, but a manual seasonal index still has its place. Here's how to decide and how to check your forecast's accuracy either way.

Forecasting monthly or quarterly sales with clear seasonal swings, two approaches come up constantly: let Excel's FORECAST.ETS function handle it automatically, or build a manual seasonal index by hand. Both can work well — the right choice depends on how much control you need and how clean your history is.

FORECAST.ETS: the built-in approach

=FORECAST.ETS(target_date, history_values, history_dates, seasonality)

seasonality: 1 = auto-detect (Excel finds the cycle length itself), or a fixed number of periods.

  • Uses triple exponential smoothing (accounts for level, trend, and seasonality together)
  • Needs at least two full seasonal cycles of clean, evenly-spaced historical data to detect seasonality reliably
  • FORECAST.ETS.CONFINT gives a confidence interval around the forecast — useful for showing a range rather than a single number
  • FORECAST.ETS.STAT can return the detected seasonality length, so you can sanity-check what Excel found before trusting the output

The manual seasonal index approach

  1. Calculate each period's seasonal index: that period's average value ÷ the overall average across all periods
  2. Deseasonalize the history by dividing actuals by their seasonal index
  3. Fit a simple trend line (or even just a straight average) to the deseasonalized values
  4. Re-apply the seasonal index to the trend forecast to get the final seasonally-adjusted number
The manual method takes more setup, but it's transparent — anyone can audit exactly how a specific month's seasonal factor was calculated. FORECAST.ETS is a black box by comparison: faster, but harder to explain in a review meeting.

Try it yourself

Compare the two smoothing methods on the same data

The 3-month average lags behind sharp swings; exponential smoothing reacts faster because recent months carry more weight. Neither is "correct" — pick the one whose lag matches how fast your business actually changes.

When to prefer which

  • Choose FORECAST.ETS when you have at least 2–3 years of clean, regularly-spaced history and want a quick, statistically-grounded forecast with a confidence interval
  • Choose a manual seasonal index when the business needs to see and adjust individual seasonal factors (e.g. "we know December will be unusually high this year because of a new product launch") — those adjustments are easy to bolt onto a manual index and nearly impossible to influence inside FORECAST.ETS
  • Choose the manual method when history is short, irregular, or has gaps FORECAST.ETS can't smooth over cleanly

Validate either forecast the same way

Whichever method you use, hold out the last 3–6 known periods, forecast them as if they were unknown, and compare the forecast to what actually happened:

=AVERAGE(ABS((actual-forecast)/actual))

Mean Absolute Percentage Error (MAPE) — lower is better; under 10% is generally considered strong for monthly sales.

A forecast method that looks sophisticated but scores worse on this holdout test than a naive "same as last year" baseline isn't actually adding value — always check against that baseline before presenting either approach as an improvement.

Want the full worked example?

Automate Your Reports: Power Query for Beginners

A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.

Get the eBook — ₹199