Forecasting in Excel
Respectable time-series forecasts right in the grid
Excel's FORECAST.ETS fits exponential smoothing with trend and seasonality, producing ranges and confidence bounds for time-series data without external tooling.
What you will learn
- Fit a seasonal forecast with FORECAST.ETS
- Choose seasonality and confidence settings
- Validate forecasts against actuals using MAD/MAPE
What is this?
Excel's FORECAST.ETS family (ETS, ETS.CONFINT, ETS.SEASONALITY, ETS.STAT) fits a triple-exponential-smoothing model with level, trend and seasonality to evenly spaced historical series. It outputs point forecasts and confidence bounds, and is the fastest route to a credible baseline forecast for weekly/monthly business numbers.
Why do you need it?
- Most weekly/monthly business series have both trend and seasonality — naive extrapolation fails on both.
- Forecast ranges (not points) communicate unavoidable uncertainty to stakeholders.
- It's audio-grade for baselines where a full Python/ML stack is overkill.
- Confidence intervals expose when a number is too uncertain to bet on.
Mental model
Seasonal clothing sales at a mall
Seasonal smoothing
You forecast next winter's coats as 'last winter ± trend ± seasonal pattern', blended with recent evidence. Exponential smoothing is exactly that: weighted recent data + trend + repeating seasonal component.
How it works
- FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]) fits and predicts.
- Seasonality auto-detects or is pinned (e.g., 12 for monthly, 7 for weekly).
- ETS.CONFINT returns a confidence bound; ETS.STAT exposes alpha/beta/gamma and accuracy metrics (MAD, MAPE).
- The engine requires evenly spaced data with no gaps; it imputes missing points when told to.
- Validation: hold out recent actuals, forecast over them, and compare MAD/MAPE.
Fitting and validating a seasonal forecast.
Examples
A monthly demand forecast
// Point forecast for the next 6 months=FORECAST.ETS($A$25, $B$2:$B$24, $A$2:$A$24, 12, 1, 1) // Confidence bounds at 90%=FORECAST.ETS.CONFINT($A$25, $B$2:$B$24, $A$2:$A$24, 0.9, 12) // Seasonal length + accuracy stats=FORECAST.ETS.SEASONALITY($B$2:$B$24, $A$2:$A$24)=FORECAST.ETS.STAT($B$2:$B$24, $A$2:$A$24, "Mape")Point forecast plus a 90% interval gives planners a central case and a range; the MAPE stat tells them how much past errors should be trusted.
In production
- A supply chain lead forecasts weekly SKU demand to tune reorder points.
- A finance team produces a monthly revenue baseline with a confidence range for board planning.
- An ops leader forecasts call volume per hour to schedule staff.
Best practices
- Provide at least two full seasonal cycles of history (2 years monthly, 2 weeks daily).
- Hold out the most recent 10–20% to validate before trusting the forecast going forward.
- Report the range, not just the point: stakeholders plan against intervals.
- Document the seasonality length and aggregation you chose.
Common mistakes
- Feeding sparse history and letting auto-seasonality hallucinate a pattern.
- Forecasting into structural change (new pricing, COVID-style shocks) with ETS's backward-looking fit.
- Ignoring confidence bounds and quoting the point forecast as a promise.
- Not validating — forecasting history and pretending it's the future.
Debugging tips
- Bad forecast for one SKU? Check its history's consistency of seasonality first.
- Huge confidence bounds — the series is noisy; state that as the finding.
- MAPE unrealistically low then a miss — your validation window overlapped the fit.
- Gaps in timeline — backfill or set data_completion=1 for imputation.
Interview checkpoint
Quick quiz
What does FORECAST.ETS.CONFINT return?
You need at least two full seasonal cycles of history for a reliable seasonal forecast.