Seasonal Forecasting with Forecast Sheet

Linear forecasting assumes revenue moves in a straight line. Moving averages assume the recent past repeats itself. Neither captures the most common pattern in real business data: seasonality — predictable peaks and troughs that repeat at the same points in the calendar every year. Excel's Forecast Sheet is built specifically for this, and it is the most powerful single-click forecasting tool in Excel.

What Seasonal Forecasting Does Differently

A seasonal forecast separates your data into three components and models each one independently:

• Trend — the overall direction over time, identical to what TREND and FORECAST.LINEAR calculate;
• Seasonality — the repeating calendar pattern. If December revenue is consistently 30% above the trend line and August is consistently 20% below it, the seasonal model captures those ratios and applies them to every future December and August automatically;
• Residual — what's left after trend and seasonality are removed. Ideally this is pure noise with no pattern. If it still has a pattern, your model is missing something.

The technical name for this approach is ETS — Exponential Triple Smoothing. You don't need to understand the mathematics, but knowing the name helps when you see it in Excel's dialog boxes and documentation.

Using Excel's Forecast Sheet

Forecast Sheet is accessed through the Data tab and requires just two columns: a date column and a values column. It does everything else automatically.

1. Click anywhere in your Forecast_Input_Table;
2. Select the Period_Date and Total_Revenue columns (hold Ctrl for non-adjacent);
3. Go to Data → Forecast Sheet;
4. In the dialog that opens, set the Forecast End date to 2025-12-01;
5. Click Create.

Excel generates a new sheet containing: the historical data, the forecast values, and upper and lower confidence bounds — all in a pre-built chart.

Understanding the Forecast Sheet Dialog

Before clicking Create, review every option in the dialog:

Option	What It Controls	Recommendation
Forecast End	How far forward to project	Set to December 2025 for a 12-month forecast
Confidence Interval	Width of the uncertainty band	Leave at 95% — standard for business reporting
Seasonality	How many periods in one seasonal cycle	Leave on Auto — Excel detects it from your data
Timeline Range	Which date column to use	Confirm it points to Period_Date
Values Range	Which metric to forecast	Confirm it points to Total_Revenue
Fill in Missing Points	How to handle gaps in history	Use Interpolation
Aggregate Duplicates	How to handle multiple values per period	Use Average

The most important option to understand is Seasonality. On Auto, Excel analyzes your data and identifies the repeating cycle length. For monthly business data with annual seasonality it should detect 12. If it detects something unexpected — like 3 or 6 — your data may have quarterly patterns stronger than annual ones, which is itself a useful finding.

Reading the Confidence Interval

The Forecast Sheet produces three forecast columns, not one:

Forecast — the central predicted value, equivalent to what FORECAST.LINEAR would give you for the trend component alone, plus the seasonal adjustment.

Lower Confidence Bound — the pessimistic boundary. There is a 95% probability the actual value will fall above this line.

Upper Confidence Bound — the optimistic boundary. There is a 95% probability the actual value will fall below this line.

The gap between the upper and lower bounds widens as you forecast further into the future — this is correct and intentional. A forecast that shows the same confidence interval in month 1 and month 12 is lying to you about its certainty.

Extracting Forecast Sheet Values into Your Model

The Forecast Sheet creates its own standalone sheet, which is useful for presentation but not connected to your Forecast_Input_Table. Extract the values so they live alongside your other forecast methods:

In your Forecast_2025 table, add three new columns:

• ETS_Forecast → link to the Forecast column from the generated sheet
• ETS_Lower → link to the Lower Confidence Bound column
• ETS_Upper → link to the Upper Confidence Bound column

Now your complete forecast comparison table has five columns side by side:

Month	Baseline	MA3_Forecast	Linear_Forecast	ETS_Forecast	ETS_Lower	ETS_Upper
Jan 2025	flat avg	moving avg	linear	seasonal	lower bound	upper bound

Interpreting Seasonal Patterns in Your Data

Once the Forecast Sheet is generated, look at the chart carefully before using the numbers. Three things to check:

Does the seasonal pattern make business sense? If Software revenue peaks every Q4 in the chart, that aligns with typical enterprise budget cycles — a credible pattern. If it peaks randomly, the model may be fitting noise rather than real seasonality.

Is the trend realistic? Compare the implied annual growth rate from the ETS forecast to your CAGR from the KPI_Calculations sheet. They should be broadly consistent. A large divergence means the model has detected a recent acceleration or deceleration worth investigating.

Are the confidence bounds reasonable? Bounds that are extremely wide suggest high volatility in the historical data. Bounds that are suspiciously narrow suggest the model may be overfit to history.

Task 1: Generate the Forecast Sheet

Goal: Use Excel's Forecast Sheet to produce a seasonal forecast with confidence intervals.

1. In your Forecast_Input sheet, click on the Period_Date column, hold Ctrl, and also select the Total_Revenue column.
2. Go to Data → Forecast Sheet. Review the preview chart — does it show a seasonal pattern?
3. Set Forecast End to 2025-12-01. Leave Confidence Interval at 95% and Seasonality on Auto.
4. Before clicking Create, note the Seasonality value Excel has detected — write it in a comment cell. It should be 12 for monthly annual seasonality.
5. Click Create. Excel generates a new sheet — rename it ETS_Forecast.
6. In the generated sheet, identify the three forecast columns (Forecast, Lower, Upper) and note the January 2025 central forecast value.

Task 2: Integrate ETS Results into Your Forecast Table

Goal: Bring the Forecast Sheet output into your master forecast comparison table.

1. In your Forecast_2025 area, add three columns: ETS_Forecast, ETS_Lower, and ETS_Upper.
2. Link each cell to the corresponding values in the ETS_Forecast sheet using direct cell references.
3. Add the ETS_Forecast series to your master forecast chart alongside the MA3 and Linear series. Format it as a solid line in a distinct color — it is your most sophisticated method and deserves visual prominence.
4. Add ETS_Lower and ETS_Upper as dashed lines in a lighter shade of the same color, creating a visible confidence band around the ETS forecast.
5. Compare the ETS_Forecast values against Linear_Forecast for each month — in which months does seasonality push the ETS forecast significantly above or below the linear projection? Note at least two months where the difference is largest.