How to Build a Sales Forecast in Excel: A 12-Month Method with Seasonality
A practical guide to forecasting monthly sales from your own history — seasonal index, growth assumption, step-by-step formulas, a worked example and forecast-vs-actual checks.
A sales forecast estimates how much a business will sell in each future month. This guide shows how to build a 12-month forecast in Excel from your own sales history: measure the seasonal pattern, set a growth assumption you can explain, combine the two with simple formulas, and then check the forecast against actual sales as each month closes. Every figure in the worked example can be checked line by line.
What is a sales forecast?
A sales forecast is a month-by-month estimate of future sales, usually in currency and sometimes also in units. It feeds the rest of the plan: the budget, staffing, purchasing and the cash flow forecast. A useful forecast is not a target and not a guess. It is a calculation from stated assumptions that someone else can read, question and update.
Most small and mid-sized businesses forecast in one of two ways:
- From history (time-series) — start from recent sales, add a growth assumption and repeat last year’s seasonal pattern. This is the method in this guide. It suits a business with at least two years of reasonably stable monthly sales.
- From drivers (bottom-up) — build sales from the things that cause them: units × price, customers × average order value, or a sales pipeline × win rate. This suits a new business with little history, or a year with a known change such as a new product, a lost customer or a price rise.
The two are often combined: the history-based forecast gives the baseline, and driver-based adjustments are layered on top for changes the history cannot know about.
Choosing a forecasting method in Excel
| Approach | Best for | Watch out for |
|---|---|---|
| Growth % on last year | A quick first draft | Repeats last year’s one-offs; hides the growth logic |
| Moving average | Smoothing noisy, non-seasonal sales | Lags behind a trend; ignores seasonality |
| FORECAST.LINEAR / TREND | A steady straight-line trend | Assumes a straight line; no seasonality |
| Forecast Sheet / FORECAST.ETS | A fast statistical forecast (Excel 2016 or later) | Harder to explain; assumptions are inside the function |
| Seasonal index × trend (this guide) | Seasonal businesses that need to explain every number | Needs two years of monthly history |
The seasonal-index method is used here because every step is visible: you can show a lender, a manager or yourself exactly why March is forecast higher than February. Excel’s built-in Forecast Sheet is a reasonable cross-check once your own forecast is built.
Information you need
- 24 months of monthly sales (36 is better), net of returns and discounts, in one currency.
- Notes on unusual months — a one-off large order, a stock-out, a closure or a price change. These distort the pattern and may need adjusting before you start.
- A growth assumption — an annual rate you can justify from recent trend, pricing plans, capacity or market conditions.
- Known changes for the forecast year — new products, lost customers, price changes — to add as separate adjustments.
How to build a sales forecast in Excel, step by step
Put month-start dates in column A and monthly sales in column B, oldest first, starting in row 2. With 24 months, January of year 1 is in row 2 and December of year 2 is in row 25.
Step 1 — Measure the underlying level with a 12-month centred moving average
A 12-month average removes the seasonal pattern because every month of the year is included once. Because 12 is an even number, average two neighbouring 12-month windows so the result sits on a single month. For July of year 1 (row 8): =(AVERAGE(B2:B13)+AVERAGE(B3:B14))/2. Copy down to June of year 2 (row 19). The first six and last six months have no centred average — that is expected.
Step 2 — Calculate each month’s seasonal ratio
Divide actual sales by the centred average: =B8/C8 in column D. A ratio of 1.15 means that month sold 15% above the underlying level. Add the month number in column E with =MONTH(A8).
Step 3 — Turn the ratios into a seasonal index
In a small table with month numbers 1–12 in G2:G13, average the ratios for each month and scale them so the 12 indices average exactly 1: =AVERAGEIFS($D$8:$D$19,$E$8:$E$19,G2)/AVERAGE($D$8:$D$19). With more years of history, AVERAGEIFS averages several ratios per month, which makes the index more reliable.
Step 4 — Remove seasonality from the latest months
Divide each of the last three months by its index to see the seasonally adjusted level, for example =B25/INDEX($H$2:$H$13,MONTH(A25)). Average the three results to get the base: the underlying monthly sales level at the end of your history.
Step 5 — Apply a growth assumption
Enter the annual growth rate in a labelled cell (say K2) and convert it to a compounding monthly rate: =(1+K2)^(1/12)-1. The average of the last three months sits at the middle month (November, if your history ends in December), so January is two months after it. The underlying level for forecast month n (January = 1) is =Base*(1+g)^(n+1).
Step 6 — Put the seasonality back
Multiply each month’s underlying level by that month’s seasonal index: =Trend*INDEX($H$2:$H$13,n). That is the forecast. Add a separate adjustment column for known changes (a new contract, a price rise) rather than overwriting the formula, so the reason for every difference stays visible.
Step 7 — Sanity-check the total
Compare the forecast year with the last actual year and with the deseasonalised trend. If the implied growth is very different from what the business has achieved, revisit the growth assumption or look for a distorted month in the history.
Worked example: a 12-month sales forecast with seasonality
A small homeware wholesaler has two years of monthly sales and wants a forecast for the next year. Sales rise every autumn into a December peak. Total sales were 534,100 in year 1 and 577,200 in year 2, an increase of 8.1%. The owner chooses a more cautious growth assumption of 6% a year for the forecast. All figures are fictional.
| Month | Year 1 | Year 2 |
|---|---|---|
| Jan | 38,200 | 41,500 |
| Feb | 36,500 | 39,400 |
| Mar | 41,800 | 44,900 |
| Apr | 42,600 | 46,300 |
| May | 44,100 | 47,200 |
| Jun | 41,900 | 45,600 |
| Jul | 40,300 | 43,500 |
| Aug | 41,700 | 45,100 |
| Sep | 45,200 | 48,300 |
| Oct | 47,800 | 51,900 |
| Nov | 52,600 | 56,700 |
| Dec | 61,400 | 66,800 |
| Total | 534,100 | 577,200 |
Steps 1–3 give one seasonal ratio for each calendar month, from July of year 1 to June of year 2. Scaling the ratios so they average 1 gives the seasonal index.
| Month | Sales | Centred avg | Ratio | Index |
|---|---|---|---|---|
| Jul Y1 | 40,300 | 44,646 | 0.903 | 0.903 |
| Aug Y1 | 41,700 | 44,904 | 0.929 | 0.929 |
| Sep Y1 | 45,200 | 45,154 | 1.001 | 1.001 |
| Oct Y1 | 47,800 | 45,438 | 1.052 | 1.052 |
| Nov Y1 | 52,600 | 45,721 | 1.150 | 1.151 |
| Dec Y1 | 61,400 | 46,004 | 1.335 | 1.335 |
| Jan Y2 | 41,500 | 46,292 | 0.896 | 0.897 |
| Feb Y2 | 39,400 | 46,567 | 0.846 | 0.846 |
| Mar Y2 | 44,900 | 46,838 | 0.959 | 0.959 |
| Apr Y2 | 46,300 | 47,138 | 0.982 | 0.982 |
| May Y2 | 47,200 | 47,479 | 0.994 | 0.994 |
| Jun Y2 | 45,600 | 47,875 | 0.952 | 0.953 |
December’s index of 1.335 means December normally sells about 33.5% above the underlying level; February’s 0.846 means about 15.4% below it. The 12 indices add up to 12 (shown rounded to three decimals).
Step 4 removes seasonality from the last three months of year 2: October 51,900 ÷ 1.052 = 49,335; November 56,700 ÷ 1.151 = 49,262; December 66,800 ÷ 1.335 = 50,037. Their average, the base, is 49,545.
Step 5 converts 6% a year to a monthly rate: (1.06)^(1/12) − 1 = 0.487% a month. January’s underlying level is 49,545 × (1 + 0.487%)² = 50,028.
| Month | Underlying level | Index | Forecast |
|---|---|---|---|
| Jan | 50,028 | 0.897 | 44,875 |
| Feb | 50,272 | 0.846 | 42,530 |
| Mar | 50,516 | 0.959 | 48,445 |
| Apr | 50,762 | 0.982 | 49,848 |
| May | 51,009 | 0.994 | 50,703 |
| Jun | 51,257 | 0.953 | 48,848 |
| Jul | 51,507 | 0.903 | 46,511 |
| Aug | 51,758 | 0.929 | 48,083 |
| Sep | 52,010 | 1.001 | 52,062 |
| Oct | 52,263 | 1.052 | 54,980 |
| Nov | 52,517 | 1.151 | 60,447 |
| Dec | 52,773 | 1.335 | 70,452 |
| Total | 617,784 |
The forecast total is 617,784, 7.0% above year 2 — higher than the 6% assumption. That is not an error: sales grew during year 2, so the business entered year 3 already running above the year-2 average. This carry-over growth is worth explaining whenever you present the forecast, because it is the first thing a reviewer will question.
Tracking forecast vs actual
A forecast becomes useful when you measure it. As each month closes, enter actual sales next to the forecast and calculate:
- Error = Actual − Forecast. Positive means sales beat the forecast. With the forecast in column B and the actual in column C, put =C2-B2 in column D.
- Percentage error = Error ÷ Actual: =D2/C2 in column E.
- Mean absolute percentage error (MAPE) — the average size of the percentage errors, ignoring their sign. Put =ABS(E2) in a helper column and average it.
- Bias = total error ÷ total actual sales. A forecast can have a small MAPE and still be consistently too high or too low; bias shows the direction.
| Month | Forecast | Actual | Error | % error |
|---|---|---|---|---|
| Jan | 44,875 | 45,900 | 1,025 | 2.2% |
| Feb | 42,530 | 41,600 | −930 | −2.2% |
| Mar | 48,445 | 50,800 | 2,355 | 4.6% |
| Total | 135,850 | 138,300 | 2,450 |
The MAPE for the quarter is 3.0% and the bias is +1.8%: the monthly errors were small, and sales ran slightly ahead of the forecast overall. One quarter is too short to change the growth assumption, but if the bias stays positive for several more months, the assumption is probably too cautious.
Leave a month’s actual blank until it is known. Recording zero for a month that has not closed would look like a 100% miss and distort MAPE and bias.
Review the assumptions when the bias stays in the same direction for three months or more, or when a known change (a lost customer, a new channel) makes the history less representative. Update the forecast; do not quietly edit the history.
Common mistakes and practical checks
- Too little history. One year shows the pattern once, so any unusual month becomes part of the “season”. Use at least two full years.
- Distorted months left in. A one-off bulk order or a month-long stock-out will flow straight into the index. Adjust it to a normal level (and note why) before calculating ratios.
- Growth applied to raw seasonal figures. Growing December’s sales by a monthly rate to forecast January carries the December peak into January. Always grow the deseasonalised level, then reapply the index.
- Annual growth used as a monthly rate. 6% a year compounds to about 0.487% a month. Using 6% a month is a large error; dividing by 12 (0.5%) slightly overstates growth.
- Hard-coded assumptions. Keep growth, adjustments and the index in labelled cells so a reviewer can change one number and see the effect.
- No accuracy tracking. Without forecast-vs-actual and bias, the same optimistic assumption can be repeated every year.
Use the Sales Forecast Template
If you would rather not build the workbook from scratch, the BabaDigit Sales Forecast Template for Excel forecasts up to five product lines for twelve months from your own sales history, with a growth rate and uplift per product, twelve monthly seasonal factors, compounding growth, and a forecast-vs-actual sheet that reports how accurate the forecast has been. Any forecast cell can be overridden when you know something the history does not.
View the Sales Forecast TemplateExcel 2016 or later, or Microsoft 365 · no macros · includes clearly labelled sample data.
Frequently asked questions
How many months of history do I need?
At least 24 months, so each calendar month appears twice and the seasonal pattern is not based on a single year. With less history, use a driver-based forecast (units × price or customers × order value) and add seasonality later.
Should I use FORECAST.ETS or the Forecast Sheet instead?
They are useful cross-checks and quick to run in Excel 2016 or later. The seasonal-index method takes longer to set up, but every assumption is visible, which matters when you have to explain or defend the forecast.
What growth rate should I use?
Start from what the business has actually achieved (the deseasonalised trend), then adjust for things you know: price changes, capacity, new or lost customers. Record the reason next to the assumption.
How do I forecast a new product with no sales history?
Build it from drivers — expected units × price, or customers × average order value — and borrow the seasonal index from a similar existing product if the pattern is likely to be the same.
How often should I update the forecast?
Enter actuals every month. Review the assumptions quarterly, or sooner if the bias stays in one direction for three months or something material changes.
Is this the same as a cash flow forecast?
No. A sales forecast shows when sales are made; a cash flow forecast shows when customers actually pay. Use the sales forecast as an input, then apply payment timing in a 13-week cash flow forecast.
Related resources
- Revenue Growth Calculator — measure historical growth between two periods before choosing a forecast growth rate.
- CAGR Calculator — find the compound annual growth rate over several years.
- 13-Week Cash Flow Forecast guide — turn forecast sales into expected customer receipts week by week.
This guide is general information, not financial, accounting or legal advice. A forecast is an estimate based on stated assumptions, not a prediction of results. The worked example uses fictional figures. Written and reviewed by Ghayas Uddin, MS (Finance), financial analyst. Last reviewed 18 October 2026. Found an error? Email support@babadigit.com.