Microsoft Excel · Advanced modeling and platform-dependent features · Intermediate
Create a forecast with uncertainty checks
Create a Windows Forecast Sheet from 36 synthetic monthly observations and test assumptions with a holdout and confidence intervals.
Official documentation reviewed; synthetic fixtures checked independently; not executed in Excel · Editorial standards
Quick answer: Select a consistent timeline and its numeric observations, choose Data > Forecast > Forecast Sheet, and inspect Options before creating the output. Keep confidence bounds visible and use a historical holdout to compare predictions with known values. A forecast is a model output, not a guaranteed outcome.
Scope and reproducible data
Use English Excel for Microsoft 365 desktop on Windows. Microsoft’s Forecast Sheet documentation covers Windows; do not infer this command exists in Excel 2013, Mac, or the web. This is synthetic demand arithmetic, not financial guidance or a real demand prediction.
In A1 enter Month, and in B1 enter Units. Put =DATE(2023,1,1) in A2 and =DATE(YEAR(A2),MONTH(A2)+1,1) in A3. Fill A3 down through A37, producing January 2023 through December 2025. Format A2:A37 as dates.
In B2 enter =100+2*(ROW()-2)+10*MOD(ROW()-2,12) and fill down to B37. This produces all 36 observations, not an incomplete illustrative series. The first year is 100,112,124,136,148,160,172,184,196,208,220,232. The second year adds 24 to each corresponding month, and the third adds another 24.
Verify B14=124, B26=148, and B37=280. The total is 6,840 and the mean is 190. Each timestamp appears once and each month has a value. Keep the source sheet named History.
Create the forecast
- Select A1:B37.
- Choose Data > Forecast > Forecast Sheet.
- Choose a line-chart presentation and set Forecast End to March 1, 2026, giving three future monthly points.
- Open Options, retain the default Forecast Start for this new future forecast, and check the Timeline Range and Values Range refer to corresponding observations. Note that default start so you can distinguish this full-history case from the later hindcast.
- Keep Confidence Interval selected at 95%. Set seasonality manually to 12 for this deliberately annual monthly pattern. The 36 points supply three complete cycles, exceeding the documented two-cycle minimum for manually specified seasonality.
- Choose Create. Excel produces a new worksheet containing historical and predicted values, confidence-bound columns, and a chart.
Check the output’s structure: historical data through December 2025, forecasts for January–March 2026, and lower/upper bounds for predicted values. Do not expect numbers supplied here as a claimed native result; Excel’s ETS model must actually be run to obtain its predictions.
Independently judge the output
The fixture’s known construction continues with 172,184,196 for January–March 2026. These are generator benchmarks, not promised Forecast Sheet results. Compare the actual forecast against them and inspect any differences, rather than changing the chart to hide a mismatch.
Create a separate hindcast from the original History data. In Forecast Options, set Forecast Start to October 1, 2025 and Forecast End to December 1, 2025. Excel uses observations before the start date to predict the held-out period. The known actual values for October–December are 256,268,280. Compare each prediction and record its absolute error; an average of the three absolute errors is a simple holdout error measure.
The confidence band relies on the forecasting method and its assumptions. A 95% setting does not guarantee that all future observations will fall inside the displayed bounds. A narrow interval is not protection against a structural change in how the data is generated.
Stress the assumptions and save
Make a copy of History, change only B37 from 280 to 340, and create a new future forecast. Start a fresh forecast from the copied History sheet and retain its default Forecast Start, not the October 2025 hindcast start. Match the original future settings: Forecast End March 1, 2026, seasonality 12, and a 95% confidence interval. The source total becomes 6,900 and the final observation increases by 60. Compare the new model output and bounds with the original; do not assume a particular forecast delta without calculating it in Excel.
Missing months and duplicate timestamps need deliberate treatment. Options can interpolate missing points or treat them as zeros, and can aggregate duplicates; those choices answer different questions. For this complete fixture, neither intervention is necessary.
Save as XLSX and reopen to check source ranges, dated outputs, and model settings. Keep the original, holdout, and changed-input outputs separately labeled. Use SUM to audit the historical input totals before debating forecast accuracy.