MO-211 Expert Prep
Module 3 · Lesson 3.10

Forecast Sheet and Forecast Functions

Edit on GitHub

Predicting next quarter's sales used to require manual exponential-smoothing models, custom seasonal indices, and a lot of trust in your spreadsheet math. Forecast Sheet is Excel's built-in time-series forecasting tool: you point it at a date column and a value column, and it produces a confidence-bounded forecast chart in two clicks—seasonality detection included. Behind the scenes it calls the same FORECAST.ETS family of functions you can use directly for more control.

Part 1: Why Forecast Sheet Exists

Manually forecasting a time series correctly involves several steps: detecting the seasonal period, decomposing trend and seasonality, fitting smoothing parameters, and computing prediction intervals. Forecast Sheet automates all of that with the ETS (Error-Trend-Seasonal, the AAA model) algorithm and gives you:

  • A pre-built chart (line or column) with historical values, forecast values, and upper/lower confidence bounds.
  • A linked data table that you can edit, restyle, or feed into a PivotTable.
  • Automatic seasonality detection (e.g., it will recognize a 12-month cycle in monthly sales without being told).

The result lands on a brand new worksheet, so your source data is untouched.

Part 2: Workflow — Creating a Forecast Sheet

🛠 Steps

  1. Arrange your data in two adjacent columns: a column of dates (or numeric time periods) and a column of historical values. Dates should be evenly spaced (daily, weekly, monthly, quarterly, yearly).
  2. Select both columns, including headers.
  3. On the Data tab, in the Forecast group, click Forecast Sheet.
  4. The Create Forecast Worksheet dialog opens.
  5. Choose the chart type: * Line chart (default) — best for continuous trends. * Column chart — best for discrete period-over-period comparisons.
  6. Set the Forecast End date (the last period you want predicted).
  7. (Optional) Expand Options to fine-tune (see Part 3).
  8. Click Create. Excel inserts a new sheet with the historical data, forecast values, confidence bounds, and the chart.
TIP
Excel needs at least a handful of historical points to fit the model. As a rule of thumb, supply **at least two full seasonal cycles** of data—24 months for a yearly seasonality, 14 days for a weekly cycle—or the seasonality detection will silently fall back to a non-seasonal model.

Part 3: The Options Panel — What Each Setting Does

Click Options at the bottom of the Create Forecast Worksheet dialog to expose:

Option What it controls
Forecast Start The first date the model begins predicting. Set this earlier than the last historical point to "back-test" the model against known values.
Confidence Interval The shaded band around the forecast. Default 95%. Lower it for a tighter (less conservative) band; uncheck to hide bounds entirely.
Seasonality Detect Automatically (default) or Set Manually. Manual values: 12 for monthly data with yearly seasonality, 7 for daily data with a weekly pattern, 4 for quarterly data, 1 (or unchecked) to force no seasonality.
Include forecast statistics Adds a small table of accuracy metrics (smoothing coefficients α/β/γ, MASE, SMAPE, MAE, RMSE) to the new sheet.
Timeline Range The cell range holding the dates. Pre-filled from your selection.
Values Range The cell range holding the historical values. Pre-filled from your selection.
Fill Missing Points Using Interpolation (linear estimate from neighbours, recommended) or Zeros (treat gaps as 0, which usually distorts the model).
Aggregate Duplicates Using If your timeline contains duplicate dates, pick the rollup: Average (default), Sum, Count, Min, Max, Median.
IMPORTANT
The **Confidence Interval** is *not* the same as a 95% guarantee that future values will land in the band. It is a statistical prediction interval based on the residuals of the fitted model—if the underlying process changes (a price hike, a pandemic), the band can be badly wrong.

Part 4: The Underlying Functions

Forecast Sheet is a UI wrapper. If you need more control, embed the same functions directly in your worksheet.

FORECAST.ETS

Predicts a future value using the ETS algorithm.

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])
  • target_date — the date you want to predict.
  • values — the historical values range.
  • timeline — the historical date range (must be evenly spaced once gaps are handled).
  • seasonality (optional) — 1 (auto-detect, default), 0 (no seasonality), or a positive integer to force a period.
  • data_completion (optional) — 1 for interpolation (default), 0 to treat gaps as zero.

  • aggregation (optional) — 1–7 mapping to AVERAGE, COUNT, COUNTA, MAX, MEDIAN, MIN, SUM for duplicate-date rollup.

  • Official Docs: FORECAST.ETS function

FORECAST.ETS.CONFINT

Returns the width of the confidence interval for a given target date. Add and subtract it from FORECAST.ETS to get the upper and lower bounds.

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

FORECAST.ETS.SEASONALITY

Returns the seasonal period (in number of timeline steps) that Excel detected. Useful for sanity-checking that auto-detection picked up the cycle you expected.

=FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])

FORECAST.LINEAR

Simple linear regression. Replaces the legacy FORECAST function (which still works for backward compatibility but is no longer recommended). Use this when your data has a clear linear trend and no seasonality.

=FORECAST.LINEAR(x, known_y, known_x)
  • x — the x-value (e.g., future date) at which to predict.
  • known_y — historical dependent values.

  • known_x — historical independent values (typically dates).

  • Official Docs: FORECAST.LINEAR function

Part 5: When NOT to Use Forecast Sheet

Forecast Sheet is excellent for clean, periodic, sufficiently long time series. It is the wrong tool when:

  • No clear seasonality and no trend — you are essentially forecasting noise; use a moving average or just the historical mean.
  • Less than 2 full cycles of history — the ETS algorithm cannot estimate seasonal indices reliably. For yearly seasonality, that means at least 24 months of data.
  • More than ~30% of points are missing — interpolation distorts the seasonal pattern beyond recognition.
  • Irregular timestamps that cannot be cleanly aggregated to a fixed cadence (e.g., transaction-level event data).
  • Step changes / structural breaks — a permanent shift mid-series (a launch, a regulation change) violates the model's assumption of stable trend and seasonality. Forecast the post-break segment only.
  • External drivers dominate — if your sales depend mostly on marketing spend or pricing, you need a regression model with predictors, not univariate ETS.
WARNING
Forecast Sheet will happily produce a confident-looking forecast on a tiny dataset. Always inspect the **forecast statistics** table (SMAPE, MASE) and back-test by setting **Forecast Start** earlier than the last historical date before trusting the numbers in a presentation.

💡 Cheat Sheet

Need Tool
One-click forecast with chart Data > Forecast Sheet
Custom forecast in-cell, with seasonality FORECAST.ETS
Upper/lower bound around a forecast FORECAST.ETS.CONFINT
Verify detected seasonal period FORECAST.ETS.SEASONALITY
Simple linear extrapolation, no seasonality FORECAST.LINEAR
Optimization with constraints (not forecasting) Solver (see 3.4)

Links