Sales forecast templates: pipeline, 12-month and Excel versions

This page is for sales managers, founders, revenue operations people and finance teams who need a number for next month, quarter or year. It covers the two kinds of forecast people usually mean: a pipeline forecast built from open deals, common in B2B, and a projection built from past sales, common for product businesses and business plans. Pick the method before you pick a spreadsheet.

Which forecasting method fits

Most B2B teams should run the first two side by side and investigate the gap between them.

How to build stage probabilities from your history

Default CRM probabilities (10%, 25%, 50% and so on) are placeholders. Replace them with your own: for each stage, divide the deals that reached it and were won by all the deals that reached it, over the last four quarters.

1. Export closed deals. Every opportunity closed won or lost in the last 12 months, with the furthest stage each reached.
2. Count, per stage, the deals that reached it. A deal that got to proposal also counts for every stage before.
3. Count, per stage, the wins among them.
4. Divide wins by deals reached. That's the stage probability.
5. Recalculate each quarter. If a stage's probability swings by more than ten points, check whether reps are using the stage the same way.

Fewer than about 30 closed deals a stage makes the figures noisy. Pool a longer period, or use fewer stages.

Sales forecast templates

A: Opportunity   B: Owner   C: Close date   D: Value   E: Stage   F: Stage probability   G: Weighted value (= D × F)   H: Category (commit / best case / pipeline)   I: Next step and date

Weighted forecast for the quarter, in Excel or Google Sheets (example for Q1 2027):
=SUMIFS(G:G, C:C, ">="&DATE(2027,1,1), C:C, "<"&DATE(2027,4,1))

Weighted total for a whole list:
=SUMPRODUCT(D2:D200, F2:F200)

Period: last 4 quarters of closed deals (won and lost)

Stage | Deals that reached stage | Of those, won | Stage probability (won ÷ reached)
1. [stage] |  |  |
2. [stage] |  |  |
3. [stage] |  |  |
4. [stage] |  |  |

Average cycle from each stage to signature (days):

Rep | Closed so far | Commit (will close) | Best case (could close) | Pipeline (unlikely this period) | Weighted pipeline
1.
2.
Team total |  |  |  |  |

Forecast call: [amount], between commit [amount] and best case [amount]
Gap between weighted pipeline and best case, and why:

Product or service | Month 1 | Month 2 | ... | Month 12 | Total
Units:
Price per unit:
Revenue (units × price):
Cost per unit:
Gross profit:

Assumptions (write them down): [where each unit estimate came from, seasonality, price changes]

Column A: month (first day of each month, consistent steps)
Column B: sales for that month (at least 24 months)

Seasonal forecast for the month in A38, from 36 months of history in rows 2 to 37:
=FORECAST.ETS(A38, $B$2:$B$37, $A$2:$A$37)

Straight-line forecast for the same month:
=FORECAST.LINEAR(A38, $B$2:$B$37, $A$2:$A$37)

Starting annual recurring revenue (ARR): [amount]
+ New customer ARR (from the weighted pipeline): [amount]
+ Expansion ARR (upsells, more seats or employees): [amount]
- Churned ARR (customers leaving): [amount]
- Contraction ARR (downgrades): [amount]
= Ending ARR: [amount]

Reps (or founders selling) by month: [number]
Months to ramp: [number]; productivity while ramping: [percent]
Ramped productivity per rep per month: [amount], from [source: your own pilot, not a benchmark]
Projected sales per month = productive reps × productivity
Assumptions to test in the first 90 days: [list]

Period | Forecast (submitted at week [number]) | Actual | Error (|actual - forecast| ÷ actual) | Main cause of the miss
1.
2.
3.
Average error over the last 4 periods:

Microsoft documents FORECAST.ETS as using the AAA version of exponential smoothing and requires the timeline's dates to have a consistent step; it's in Excel 2016 and later on desktop, but not Excel for the web. FORECAST.LINEAR replaced the older FORECAST function in Excel 2016 with the same syntax.

Worked example: a payroll provider's quarterly forecast

Illustrative example. The company and figures are invented to show the method.

The team: six account executives at Ledgerline Payroll (ledgerline.example), selling payroll services to businesses with 50 to 500 employees. The forecast: new annual contract value closing in Q1 2027, prepared in the third week of January.

Step 1: stage probabilities from the last four quarters. 32 deals were won, and every won deal passed through all four stages.

Step 2: the weighted pipeline for deals with Q1 close dates.

Add $40,000 already closed this quarter, and the arithmetic says $311,280.

Step 3: the reps' calls. Rolled up, the team's commit is $150,000 (including the $40,000 closed) and its best case is $260,000.

Step 4: explain the gap. The weighted figure is $51,280 above best case. That's a signal, not a forecast. The team's average cycle from discovery to signature is 75 days. Twelve of the 20 discovery-stage deals ($204,000) only reached discovery this month, which puts their likely close in April at the earliest, whatever the close dates say. Removing them takes $40,800 (20% of $204,000) off the weighted total, leaving $230,480 in pipeline plus $40,000 closed: $270,480, close to the reps' best case.

Step 5: the call. The manager submits $200,000, with $150,000 commit as the floor and $260,000 as the ceiling, and moves the 12 discovery deals' close dates into Q2 so the next forecast starts clean.

Step 6: score it. Last quarter, the week-three forecast was $230,000 and the team closed $198,000. The error was |198,000 - 230,000| ÷ 198,000 = 16%. Logging that each quarter shows whether the new method narrows the error.

If the forecast keeps falling short because there isn't enough early-stage pipeline, the fix is upstream: more qualified opportunities, not better spreadsheets. Bebop finds companies that match a plain-language description of your best customers, with contacts and the reason each fits, which is one way to refill the top of the funnel. The sales pipeline entry explains coverage and stage definitions.

Bookings, billings and revenue

A sales forecast usually predicts bookings: contracts signed. Finance reports revenue, which under IFRS 15 and the matching US standard, Topic 606, is recognized as the promised goods or services are transferred to the customer. A $17,280 annual payroll contract signed in March is a March booking, but only about a month of it ($1,440) is March revenue. Label which one your forecast predicts so sales and finance don't argue about a gap that's only timing.

Sales forecast checklist

• Stage probabilities come from your own closed deals in the last year.
• Every open deal has a value, a stage, an owner, a close date and a next step.
• Deals that can't close in the period, given your cycle, are excluded.
• Weighted pipeline and reps' calls are both shown, with the gap explained.
• The forecast says whether it predicts bookings or revenue.
• Forecast and actual are logged every period, with the cause of any miss.

Mistakes to avoid

• Treating the target as the forecast. If the forecast always equals the quota, it isn't a forecast.
• Default probabilities. A CRM's 50% at proposal may be 30% for your team.
• Stale close dates. Deals that slip every month inflate every forecast. Push them out or close them as lost.
• One method only. Arithmetic without judgment misses deal-specific news; judgment without arithmetic drifts toward optimism.
• Never scoring it. Without forecast-versus-actual records, you can't tell whether any change helped.

Next step: check the forecast against the year's target and capacity in the sales plan template.