Sales Forecast Template in Excel: Trend, Seasonality and Weighted Pipeline

A sales forecast template in Excel estimates revenue for the coming months from what you already know: past sales, their seasonal pattern, deals in progress and planned changes such as price increases or new products. For a small business, three methods cover almost every case and can be combined: trend with seasonal factors, Excel’s built-in forecasting function, and a weighted sales pipeline. This guide shows each one with formulas, then how to track forecast against actual.

Prepare the data

Start with one row per month: the month (first day), revenue, and if possible a split by product group or channel. Two years of history is the minimum to see seasonality. Adjust one-off months (a closure, an unusual bulk order) in a separate “adjusted revenue” column so they do not distort the forecast. Monthly totals come straight from the sales tracker spreadsheet.

Method 1: trend with seasonal factors

1. Seasonal factor for each month: the average sales of that month across the years, divided by the average month. A factor of 1.45 in May means May sells 45% more than an average month.
2. Annual forecast: last year’s revenue × (1 + growth rate), with the rate justified by recent trend and your plans.
3. Monthly forecast: annual forecast ÷ 12 × the month’s factor.

• Factor for May: =AVERAGEIFS(B:B,C:C,5)/AVERAGE(B:B), where column C holds =MONTH(A2).
• Monthly forecast: =AnnualForecast/12*XLOOKUP(MONTH(A30),Factors[Month],Factors[Factor])

Check that the twelve factors add up to 12; if not, divide each by their average.

Example: a bike shop

Last year’s revenue was $420,000 and the owner plans 6% growth, so $445,200 this year, or $37,100 in an average month. With a factor of 1.45 for May and 0.55 for January, the forecast is about $53,800 for May and $20,400 for January. Those swings are exactly what the 13-week cash flow forecast needs to plan stock buying before spring and to cover the quiet months.

Method 2: the FORECAST.ETS function

Since Excel 2016, FORECAST.ETS (exponential smoothing) detects trend and seasonality automatically:

=FORECAST.ETS(A30,B$2:B$29,A$2:A$29,12)

The last argument sets a 12-month season. FORECAST.ETS.CONFINT gives a confidence range, useful for building low and high scenarios. Data > Forecast Sheet produces the same calculation with a chart in a few clicks. It is quick, but it does not know about your plans: a price change or a new product line has to be added by hand.

Method 3: a weighted sales pipeline

For B2B or quote-based work, history says little about next quarter; open deals matter more. List each opportunity with client, value, stage and expected close date, and apply a probability by stage:

Weighted revenue for a month: =SUMPRODUCT((MONTH(Pipe[CloseDate])=MONTH(A2))*(YEAR(Pipe[CloseDate])=YEAR(A2))*Pipe[Value]*Pipe[Probability]). Replace the typical probabilities with your own win rates after a few months. The CRM spreadsheet template can hold the pipeline.

Combine methods and add scenarios

Many small businesses add a recurring base (method 1 or 2) and new deals from the pipeline (method 3). Keep three versions of the key assumptions (conservative, base, optimistic) and switch with =CHOOSE(Scenario,Conservative,Base,Optimistic). The conservative case is the one to test against fixed costs with the break-even analysis.

Track forecast versus actual

• Variance: =Actual-Forecast
• Variance %: =IFERROR(Actual/Forecast-1,"")
• Average error over 12 months: the average of the absolute percentage variances, in a helper column with =ABS(…).

Write one sentence explaining every variance over 10%. After a few months the notes show whether the method is biased, for instance always too optimistic, and how to correct it.

From forecast to budget and plan

The sales forecast is the first line of the business budget template, and for a new business it drives the financial side of the business plan template. More guides are in Small Business Finance.

Frequently asked questions

Which forecasting method should a small business use?

With two years of history and walk-in customers, trend with seasonal factors. For quote-based sales, a weighted pipeline. Often a combination of the two.

How far ahead should I forecast?

A rolling 12 months, updated monthly, gives enough visibility for cash flow and budgeting.

How do I forecast sales for a new business?

Without history, start from capacity (customers served per day, billable hours), your local market and your sales plan, then replace assumptions with actual numbers as soon as you have them.

Does FORECAST.ETS work in Google Sheets?

Google Sheets has no exact equivalent; use the seasonal factor method or FORECAST for a straight-line trend.