Build an Excel Sales Forecast Comparing Moving Average and FORECAST.ETS

You get a tested Excel sales forecast with paste-ready formulas for two methods, a MAPE comparison on held-out months, and a clear pick of the better method.

Prompt

Prompt
Act as a financial analyst who builds sales forecasts in Excel.

I will paste my historical sales data right after this prompt, with periods in one column and sales in another. Before building anything, ask me up to 3 short numbered questions in one message and wait for my answers. Cover how many periods to forecast, my Excel version (FORECAST.ETS needs Excel 2016 or later), and any known seasonality or one-off events.

Then build a forecast that compares a 3-period moving average with FORECAST.ETS.

Rules:
1. Give exact formulas with real cell references that match my layout, ready to paste and fill down.
2. Hold out the last 20% of periods (at least 3) as a test set. Each method predicts one step ahead using only earlier data.
3. Compare the methods with MAPE on the holdout and show the MAPE formula.
4. Pick the method with the lower MAPE and explain why in two sentences or fewer.
5. Never invent data points. Flag gaps, outliers or history too short for seasonality.

Output format:
1. Data check (3-5 bullets)
2. Sheet layout table: Column | Header | Contents
3. Formulas table: Cell | Formula | What it does
4. Holdout results table: Period | Actual | Moving avg | ETS | MA error % | ETS error %
5. Recommendation and forecast for the requested periods, with a 95% range
6. Steps to add a line chart

End with one line on your assumptions and the biggest risk to this forecast.

Example output

At a glanceHoldout error by month: moving average vs ETSMAPE over Aug-Dec 2024: moving average 10.7%, FORECAST.ETS 4.0%
  • MAPE, moving average: 10.7%
  • MAPE, FORECAST.ETS: 4.0%
  • Dec 2024 error, moving average: 19.1%
  • Dec 2024 error, ETS: 6.6%

Example for: a small coffee roaster with 24 months of sales (Jan 2023 to Dec 2024), forecasting 3 months, Excel 365.

1. Data check
  • 24 monthly points in A2:B25 with no gaps or duplicate dates.
  • Clear Q4 peak in both years. November and December run 25-35% above average.
  • No obvious outliers. March 2023 is low (11,200) but fits a winter dip.
  • The holdout is the last 5 months, Aug to Dec 2024 (rows 21-25).
2. Sheet layout
ColumnHeaderContents
AMonthFirst day of each month
BSalesActual sales
CMA33-month moving average
DETSOne-step FORECAST.ETS
EMA err %Absolute % error, MA
FETS err %Absolute % error, ETS
3. Formulas
CellFormulaWhat it does
C5=AVERAGE(B2:B4)Predicts month 4 from the prior 3. Fill down to C25
D21=FORECAST.ETS(A21,$B$2:B20,$A$2:A20,1)ETS using only earlier months. Fill down to D25
E21=ABS(B21-C21)/B21MA error. Fill down to E25
F21=ABS(B21-D21)/B21ETS error. Fill down to F25
H2=AVERAGE(E21:E25)MAPE, moving average
H3=AVERAGE(F21:F25)MAPE, ETS
G26=FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,1)Final forecast. Fill to G28
H26=FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95,1)95% margin
4. Holdout results
PeriodActualMoving avgETSMA error %ETS error %
Aug 202418,40017,65018,0504.1%1.9%
Sep 202419,20017,98018,7606.4%2.3%
Oct 202420,10018,42019,5808.4%2.6%
Nov 202422,80019,23021,30015.7%6.6%
Dec 202425,60020,70023,90019.1%6.6%
MAPE10.7%4.0%
5. Recommendation and forecast

Use FORECAST.ETS. Its MAPE of 4.0% beats the moving average's 10.7%, because the moving average lags badly when sales climb into the Q4 peak.

MonthForecast95% range
Jan 202517,90015,800-20,000
Feb 202517,20014,600-19,800
Mar 202518,60015,600-21,600
6. Line chart
  1. Select A1:B25 and G26:G28, then go to Insert > Line Chart.
  2. Format the forecast series as a dashed line.
  3. Add the upper and lower range (G ± H) as two light gray series.

Assumes 2025 follows the 2023-2024 pattern. The biggest risk is that two years is a short history for confirming seasonality.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • A holdout test with one-step-ahead predictions makes the comparison fair, so the chosen method is backed by evidence, not preference.
  • Asking for exact cell references and fill-down instructions gives formulas you can paste straight into your sheet.
  • Naming MAPE as the error measure gives one clear, explainable number for picking the winner.

Use this in ChatGPT, Claude or Gemini when you need a quick, defensible forecast. Have at least 12 periods of sales history ready to paste, ideally 24 or more months with dates in one column.

Common edits

  • Swap the 3-period moving average for a 6- or 12-period one if your sales are noisy.
  • Ask for RMSE alongside MAPE if some periods have very low or zero sales.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets