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
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
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 like this
Calculate Year-over-Year and Month-over-Month Growth in Excel Without Misleading Percentages
You get a growth table, exact Excel formulas that handle zero and negative baselines and partial months, plain-language takeaways, and a list of percentages that would mislead.
Write SQL Queries from a Plain-English Question and Your Table Schema
Paste your schema and ask a question in plain English. You get a working SQL query, a line-by-line explanation, and a row-count check query to verify the results.
Summarize Pasted Spreadsheet Data into Key Findings and Trends
Paste rows from Excel or Google Sheets and get numbered findings tied to exact figures, a trend table, data quality flags and the gaps that limit conclusions.
Write a Weekly or Monthly KPI Report Narrative from Your Excel Numbers
Turns your pasted KPI numbers into a report narrative where each metric states the change, the likely driver and a next action, with every claim tied to a figure.
Plan a Pivot Table That Answers a Specific Business Question
Get a pivot table plan mapping your fields to rows, columns, values and filters, with aggregation choices, data shape fixes and build steps.
Build a Power Query Workflow That Combines and Cleans Monthly Excel Files
You get numbered Power Query steps with paste-ready M code, a full query, and a flag on every step that breaks when column names change.