A free monthly sales forecast template for Excel, by region or product. It forecasts the existing business from last year's same months times the recent growth rate, adds new-business pipeline weighted by your own stage win rates, and back-tests the method on the last completed quarter.
Free Excel workbook, no sign-up. The formulas are live, and sample rows show how it fills in: replace them with your own.
Download sales-forecast-template.xlsx
| Region | Same quarter last year | Trailing growth | Baseline | New-business pipeline | Weighted probability | Weighted pipeline | Forecast |
|---|---|---|---|---|---|---|---|
| Northeast | $2,400,000 | 8.0% | $2,592,000 | $310,000 | 40.0% | $124,000 | $2,716,000 |
| Southeast | $1,850,000 | 3.0% | $1,905,500 | $180,000 | 35.0% | $63,000 | $1,968,500 |
| Midwest | $1,300,000 | 12.0% | $1,456,000 | $260,000 | 30.0% | $78,000 | $1,534,000 |
| West | $2,050,000 | -2.0% | $2,009,000 | $150,000 | 45.0% | $67,500 | $2,076,500 |
| Southwest | $900,000 | 5.0% | $945,000 | $90,000 | 50.0% | $45,000 | $990,000 |
| Total | $8,500,000 | 4.8% | $8,907,500 | $990,000 | 38.1% | $377,500 | $9,285,000 |
| Forecast vs same quarter last year | 9.2% |
This is a monthly sales forecast that a sales or finance manager can fill this week: by region or product, in two parts kept apart, with a back-test that shows how the method would have done last quarter. It is built for company sales, not household budgets.
Forecast = same period last year × (1 + trailing growth) + weighted new-business pipeline
The first part is the baseline: the business you already have, carried forward with its seasonality and its recent trend. The second is new business: open deals with new customers, each counted at the probability that a deal at its stage is won. They are kept separate because they fail differently. A baseline goes wrong when a large customer leaves; a pipeline goes wrong when deals slip. Four sales forecasting methods compared sets these methods side by side on one dataset.
The forecast for Q1 2027, by region, as the Forecast sheet shows it:
| Region | Q1 last year (USD) | Trailing growth | Baseline (USD) | New-business pipeline (USD) | Weighted probability | Weighted pipeline (USD) | Forecast (USD) |
|---|---|---|---|---|---|---|---|
| Northeast | 2,400,000 | 8.0% | 2,592,000 | 310,000 | 40.0% | 124,000 | 2,716,000 |
| Southeast | 1,850,000 | 3.0% | 1,905,500 | 180,000 | 35.0% | 63,000 | 1,968,500 |
| Midwest | 1,300,000 | 12.0% | 1,456,000 | 260,000 | 30.0% | 78,000 | 1,534,000 |
| West | 2,050,000 | −2.0% | 2,009,000 | 150,000 | 45.0% | 67,500 | 2,076,500 |
| Southwest | 900,000 | 5.0% | 945,000 | 90,000 | 50.0% | 45,000 | 990,000 |
| Total | 8,500,000 | 4.8% | 8,907,500 | 990,000 | 38.1% | 377,500 | 9,285,000 |
The forecast is 9.2% above last year's Q1: 4.8 points of that from the baseline and the rest from new business. The weighted probability is a blended figure for each region, its weighted pipeline divided by its pipeline value, so it reflects that region's mix of stages. The Northeast's 40% comes from a $150,000 deal at Negotiate (60%), a $120,000 deal at Discover (25%) and a $40,000 deal at Qualify (10%).
Underneath, the sheet carries each region month by month. Northeast January: last January was $768,000, times 1.08 is a baseline of $829,440, plus $90,000 of weighted pipeline closing that month, makes a forecast of $919,440.
The trailing growth is the last three months against the same three months a year earlier: October to December 2026 against October to December 2025, for a forecast starting in January. On the Forecast sheet:
=IFERROR(SUMIFS(History!$C$5:$C$5000,History!$B$5:$B$5000,$A6,History!$A$5:$A$5000,">="&DATE(YEAR($B$2),MONTH($B$2)-$B$3,1),History!$A$5:$A$5000,"<"&$B$2)/SUMIFS(History!$C$5:$C$5000,History!$B$5:$B$5000,$A6,History!$A$5:$A$5000,">="&DATE(YEAR($B$2)-1,MONTH($B$2)-$B$3,1),History!$A$5:$A$5000,"<"&DATE(YEAR($B$2)-1,MONTH($B$2),1))-1,0)
Cell B3 sets the window. Three months follows a turn quickly; twelve months is steadier and slower. Comparing with the same months a year earlier keeps seasonality out of the growth rate, which is the point made in seasonality in sales measures. Do not take the growth rate from the annual target: that turns the forecast into the plan.
The baseline for each month is then last year's same month times one plus that rate:
=C15*(1+D15)
Excel's FORECAST.ETS is an alternative for the baseline, fitting exponential triple smoothing to the history. The template uses the ratio because a reader can check it by hand.
Use stage probabilities from your own win history, not the CRM defaults. The Stages sheet holds, for each stage, how many opportunities reached it and how many of those were won:
| Stage | Reached | Won | Probability |
|---|---|---|---|
| Qualify | 200 | 20 | 10.0% |
| Discover | 120 | 30 | 25.0% |
| Propose | 75 | 30 | 40.0% |
| Negotiate | 50 | 30 | 60.0% |
| Commit | 35 | 28 | 80.0% |
Each deal's probability is an INDEX/MATCH on its stage, and its weighted value is value times probability. The forecast adds only deals marked New and closing in the month, with one SUMIFS:
=SUMIFS(Pipeline!$I$5:$I$2000,Pipeline!$C$5:$C$2000,$A15,Pipeline!$D$5:$D$2000,"New",Pipeline!$G$5:$G$2000,">="&B15,Pipeline!$G$5:$G$2000,"<"&DATE(YEAR(B15),MONTH(B15)+1,1))
Renewals and repeat orders from existing customers are already in the baseline, so they stay out. The sample pipeline holds two Existing deals and one New deal closing in May to show both filters working. How weighted pipeline differs from coverage is in pipeline coverage vs weighted pipeline.
The workbook uses SUMIFS and INDEX/MATCH only, so it works in Excel 2016 as well as Microsoft 365. It has no chart sheet: select the monthly forecast and history and insert a line chart if you want one.
The Back-test sheet runs the baseline method as if it had been run the day before the last completed quarter, Q4 2026, using July to September growth, and compares it with what happened. Testing a method on data it was not fitted to is the standard way to judge it; Forecasting: Principles and Practice explains why accuracy on the fitting data says little. Error here is forecast minus actual, so positive means over-forecast; that textbook defines it the other way round.
| Region | Back-test forecast (USD) | Actual (USD) | Error (USD) | Error % |
|---|---|---|---|---|
| Northeast | 2,650,000 | 2,700,000 | (50,000) | −1.9% |
| Southeast | 1,976,000 | 1,957,000 | 19,000 | 1.0% |
| Midwest | 1,485,000 | 1,512,000 | (27,000) | −1.8% |
| West | 2,121,000 | 2,058,000 | 63,000 | 3.1% |
| Southwest | 997,500 | 997,500 | 0 | 0.0% |
| Total | 9,229,500 | 9,224,500 | 5,000 | 0.1% |
The total is almost exact, yet the weighted absolute error is 1.7%: the regions' misses offset. That is why error is tracked by region and why bias and absolute error are reported side by side, as in how to measure forecast accuracy and bias in Excel. The back-test covers the baseline only, because the pipeline as it stood before that quarter is not in History. Save the Forecast sheet as values each month, a forecast snapshot, to measure the whole forecast later.
The template needs the history and pipeline pasted in by hand every month. Covirage's tools compute baselines and weighted pipeline from your sales and CRM exports and are built to snapshot each forecast so its accuracy can be measured later; the external AI model explains the forecast, and the figures come from statistical models fitted on your data. See how sales teams get the baseline and the pipeline computed from their own files, with accuracy tracked every month. For the annual method, see sales projection; for the baseline formulas, the Excel FORECAST function and moving average in Excel.
Put two years of monthly sales by region or product on one sheet and the open pipeline on another. Compute a baseline from last year's same month times recent growth, add new-business pipeline weighted by stage probability, and total by month. Save a copy each month to measure accuracy.
It depends on the business. Repeat-purchase businesses forecast well from history and seasonality; project or new-logo sales depend on pipeline. Most B2B companies need both, kept separate. Compare methods on your own data by measuring past forecast error.
The words are often used interchangeably. Where a distinction is made, a forecast is the expected outcome from current data, while a projection extends a trend or assumption forward, often further out and with less evidence behind each period.
The right benchmark is your own history. Track error and bias by region each month; consistent bias in one direction matters more than an occasional miss. Back-test the method on past quarters to see the error it would have had.