Forecasting in Excel step by step: five methods, a backtest, and a sales forecast template
Forecasting in Excel predicts the next months of sales from past months, with Forecast Sheet or FORECAST.ETS.
How forecasting in Excel works
Every Excel forecast starts from two columns: a timeline, such as months, and the values for each period, such as units sold. The timeline needs an even step. Microsoft’s FORECAST.ETS page says the dates must have a consistent step between them, and that step cannot be zero.
The methods fall into three groups:
- A straight line. FORECAST.LINEAR and TREND fit a line through the history and extend it.
- Exponential smoothing. Forecast Sheet and FORECAST.ETS weigh recent months more heavily and can follow a seasonal pattern.
- Simple benchmarks. A moving average, or the same month last year, written with ordinary formulas.
Keep the benchmarks even if you use a more advanced method. The textbook Forecasting: Principles and Practice notes that a simple method is sometimes the best one available. More often, it serves as the benchmark that a better method has to beat.
Forecasting in Excel step by step
Whichever method you use, the work runs in the same order:
- Export at least 24 months of sales for the item, by month. Two years is the minimum for a seasonal pattern.
- Clean the history. Take out one-time orders, and mark the months when the item was out of stock, which otherwise count as zero demand.
- Number the months 1, 2, 3 and so on beside the sales. That gives the even timeline the functions need.
- Forecast the last 12 months from the months before them with each method, and compare the errors.
- Forecast the next 12 months with the method that scored best.
- Each month, add the new actual beside last month’s forecast and check the error.
Forecast Sheet: a forecast without formulas
Forecast Sheet builds the forecast for you. Microsoft’s guide to creating a forecast gives these steps:
- Enter the dates in one column and the values in the next.
- Select both columns.
- On the Data tab, in the Forecast group, select Forecast Sheet.
- Pick a line or a column chart, choose an end date in the Forecast End box, and select Create.
Excel then adds a new worksheet with a table of past and predicted values and a chart. The table uses FORECAST.ETS for the forecast and FORECAST.ETS.CONFINT for the confidence interval, which defaults to 95%. The options also let you set the seasonality, fill missing points, and add a table of forecast statistics.
Two of those options matter most. Seasonality needs at least two full cycles of history, so a yearly pattern in monthly data needs two years. With less, Microsoft says Excel cannot identify the seasonal components. The Forecast Start option can begin the forecast before your last actual month, which Microsoft calls hindcasting. Comparing those forecasts with the actual months shows how accurate the method is on your data.
The FORECAST.ETS function and its siblings
FORECAST.ETS is the function behind Forecast Sheet. It uses the AAA version of the exponential smoothing (ETS) algorithm, and its syntax is FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation]).
| Argument | What it does | Default |
|---|---|---|
| target_date | The period to forecast. A date before the end of the timeline returns an error. | Required |
| values | The past values, such as monthly units sold | Required |
| timeline | The dates or period numbers, with an even step | Required |
| seasonality | 1 detects the season length, 0 turns seasonality off, and a number sets it, up to 8,760 | 1 |
| data_completion | 1 fills missing points with the average of their neighbours, 0 counts them as zeros. Up to 30% may be missing. | 1 |
| aggregation | Combines values that share a date: average, or sum, count, counta, min, max, or median | Average |
Three sibling functions report on the same model:
FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])returns the confidence interval around a forecast, at 95% by default.FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])returns the length of the repeating pattern Excel detected.FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])returns the smoothing parameters and error measures such as MAE and RMSE.
Check your version first. Microsoft states that FORECAST.ETS is not available in Excel for the web, iOS, or Android.
Forecast every item, not one column at a time
Tell Derik which items you forecast in Excel today and which system holds their order history. He will tell you whether that history is enough to forecast them.
Start a conversationFORECAST.LINEAR and TREND: a straight-line forecast
The FORECAST.LINEAR function predicts a value by linear regression. Its syntax is FORECAST.LINEAR(x, known_y’s, known_x’s), and the line is a + bx. In Excel 2016, it replaced the older FORECAST function, which still works with the same arguments.
The TREND function fits the same least-squares line: TREND(known_y’s, [known_x’s], [new_x’s], [const]). In Microsoft 365, one formula fills every future month. Older versions need the output range selected and Ctrl+Shift+Enter.
A straight line suits items that grow or shrink steadily with no season. On a seasonal item it lands near the yearly average every month, so it misses both the peaks and the troughs.
Moving average and same month last year
Two benchmarks need no forecasting functions at all:
- A moving average repeats the average of the last few months:
=AVERAGE(C38:C40)for the last three. - Same month last year repeats the value from twelve months earlier. The textbook calls this the seasonal naïve method.
A common spreadsheet version adds growth to the second one. Multiply each month of last year by this year’s total divided by last year’s total. The guide to AI demand forecasting lists this as one of the usual ways a forecast is done by hand.
Worked example: which method wins on three years of sales
A distributor sells one HVAC filter by the case. Sales peak in spring and fall. The numbers below are made up to show the method, and a script computes every result.
| Month | 2023 | 2024 | 2025 |
|---|---|---|---|
| January | 682 | 695 | 743 |
| February | 710 | 759 | 815 |
| March | 901 | 912 | 996 |
| April | 1,101 | 1,192 | 1,252 |
| May | 1,050 | 1,078 | 1,160 |
| June | 775 | 835 | 879 |
| July | 742 | 739 | 839 |
| August | 791 | 784 | 887 |
| September | 947 | 994 | 1,023 |
| October | 1,105 | 1,134 | 1,178 |
| November | 832 | 864 | 941 |
| December | 645 | 719 | 708 |
| Total | 10,281 | 10,705 | 11,421 |
To test the methods fairly, each one forecasts 2025 from 2023 and 2024 only. Its forecasts are then compared with what 2025 sold.
| Method | 2025 forecast | MAE | MAPE | Bias |
|---|---|---|---|---|
| Linear trend | 11,096 | 138.5 | 14.4% | −2.8% |
| 3-month moving average | 10,868 | 139.9 | 14.3% | −4.8% |
| Same month last year | 10,705 | 61.5 | 6.5% | −6.3% |
| Last year plus growth | 11,146 | 32.1 | 3.6% | −2.4% |
Actual sales in 2025 were 11,421 cases. MAE is the average miss in cases, MAPE the average miss as a percentage, and bias the total forecast against the total actual. The template’s Backtest sheet runs each method on the first 24 months only, the same way.
On the template’s Forecast sheet, with the history in rows 5 to 40, each method’s first forecast month reads:
| Method | Formula in row 41 |
|---|---|
| Linear trend | =FORECAST.LINEAR(A41,$C$5:$C$40,$A$5:$A$40) |
| 3-month moving average | =AVERAGE($C$38:$C$40) |
| Same month last year | =C29 |
| Last year plus growth | =F41*$M$6, where M6 holds =SUM(C29:C40)/SUM(C17:C28) |
| FORECAST.ETS | =FORECAST.ETS(A41,$C$5:$C$40,$A$5:$A$40) |
The straight line and the moving average miss by about 14% a month, because they flatten the spring and fall peaks. Same month last year keeps the season but misses the growth. Adding last year’s 4.1% growth cuts the average miss to 3.6%.
Forecasting 2026 from all three years shows the same gap. The line puts April at 980 cases, while last year plus growth puts it at 1,336. The two methods agree on the year within 3%: 11,867 cases against 12,185. FORECAST.ETS is in the template beside them. Excel fits its parameters from your data, so run the backtest sheet to see how it scores.
The sales forecast template
One .xlsx file of about 10 KB, with three sheets and working formulas. It holds the example above, ready to replace with your own 36 months.
The workbook has three sheets:
- Forecast. Paste 36 months for one item into column C. Rows 41 to 52 forecast the next 12 months five ways, with a 95% range around the FORECAST.ETS line.
- Backtest. The same five methods forecast your last 12 months from the 24 before, with MAE, MAPE, and bias for each.
- How to use. The steps, and the syntax of FORECAST.LINEAR, TREND, and FORECAST.ETS.
The timeline is a period number from 1 to 48, which gives the ETS functions the even step they need. Microsoft lists the FORECAST.ETS functions for Excel 2016 and later and for Microsoft 365, but not for Excel for the web, iOS, or Android. LibreOffice also calculates every column, but its version of FORECAST.ETS may return different numbers from Excel’s. To see which cells a formula reads, open the file in trace precedents.
Where forecasting in Excel stops
A workbook like this works well for a handful of items. The limits show up as the item count grows:
- One series at a time. Each formula reads one item’s history. A slow or new item cannot borrow the pattern of similar items.
- No drivers. FORECAST.ETS predicts from past values alone, so a price change, a promotion, or a large one-time order distorts the history.
- Manual refresh. Someone pastes the new month, extends the ranges, and checks that nothing broke.
- No record of accuracy. Unless you keep each month’s forecast and compare it with what sold, nobody knows whether the forecast is getting better. The forecast accuracy calculator scores one against actuals.
The textbook warns that percentage errors such as MAPE are infinite or undefined when actual demand is zero. Slow movers need a different measure.
AI forecasting takes over at that point. A model learns across thousands of items at once and can use prices, promotions and other signals. The AI demand forecasting guide covers how that works and the public evidence behind it. Safety stock and reorder points then follow from the forecast, as inventory optimization explains.
How ThriveAI forecasts every item from your order history
ThriveAI builds the step after the spreadsheet. It reads the order history in your ERP and cleans it, so each item means the same thing in every year. It then drafts a forecast for every item and tests it against the method you use today, on months it has not seen. A planner reviews and approves each forecast before anything changes in the ERP. The connections only read data, and the system is designed to keep your data on its own server in Canada. Pipeline forecasting from open quotes can feed the same forecast, as the guide to pipeline forecasting shows.