Blog · Forecast and pipeline · Sales teams
How to build a sales projection for next year from three parts: existing customers at their net revenue retention, new business from qualified pipeline and win rates, and a top-down check against the growth trend. Worked on three segments, with the Excel formulas and the gap that has to be explained.
A sales projection for next year adds two parts and checks the result against a third. Existing customers bring this year's revenue times their net revenue retention; new business brings qualified pipeline times win rate times the share of the year it will bill; and the total is compared with the historical growth trend. On $8,400,000 of revenue this year, that gives a projection of $9,147,500, and a $263,274 gap to trend that must be explained deal by deal.
Projection = Σ segments [ (this year's revenue × NRR) + (qualified pipeline × win rate × billing share) ]
Trend check = this year's revenue × (1 + CAGR)
NRR, net revenue retention, is revenue this year from customers who existed last year divided by their revenue last year. CAGR is (end / start)^(1 / years) − 1. This is the annual, assumption-based method used for a budget. For the quarter in progress, the four sales forecasting methods compared are the better tools, and they reconcile to this one.
Compute NRR per segment from the ledger: take the customers who bought in the prior year, and divide what they bought this year by what they bought then. Expansion, price increases, downgrades and churn are all inside it. The net revenue retention worked example computes it customer by customer.
Use segment NRR, not a company figure. Enterprise customers that expand and small customers that churn average out to a number that describes neither, and the mix of next year's revenue will not match this year's.
New revenue = qualified pipeline × win rate × billing share. The win rate is the segment's own history; what is a good win rate helps sense-check it. The billing share is an assumption: a deal won in the middle of the year bills for about half of it. Here it is 50%, which assumes deals close evenly through the year. If the pipeline closes mostly in the first quarter, the share is higher; if it is weighted to the fourth, lower. State the assumption in the projection.
FY2026 actual revenue $8,400,000. In Excel, with this year's revenue in B, NRR in C, pipeline in E and win rate in F, the segment projection in H2 is:
=B2*C2+E2*F2*0.5
| Segment | FY26 revenue (USD) | NRR | Retained FY27 (USD) | Qualified pipeline (USD) | Win rate | New FY27 (USD) | FY27 projection (USD) |
|---|---|---|---|---|---|---|---|
| Enterprise | 5,200,000 | 106% | 5,512,000 | 1,800,000 | 25% | 225,000 | 5,737,000 |
| Mid-market | 2,600,000 | 101% | 2,626,000 | 1,200,000 | 30% | 180,000 | 2,806,000 |
| Small | 600,000 | 92% | 552,000 | 300,000 | 35% | 52,500 | 604,500 |
| Total | 8,400,000 | 8,690,000 | 3,300,000 | 457,500 | 9,147,500 |
Enterprise retained: 5,200,000 × 1.06 = 5,512,000. Enterprise new: 1,800,000 × 0.25 × 0.5 = 225,000. Totals: 8,690,000 + 457,500 = 9,147,500, growth of 747,500 / 8,400,000 = 8.9%.
The split matters. Existing customers supply $290,000 of the growth and new business $457,500, while the small segment shrinks by $48,000 before new deals. A single growth rate on the total would have hidden that.
FY2023 revenue was $7,100,000 and FY2026 $8,400,000. With FY2023 in B1 and FY2026 in B4:
=(B4/B1)^(1/3)-1 5.76%
=B4*(1+(B4/B1)^(1/3)-1) 8,884,226
CAGR in Excel covers the formula in detail. The trend says $8,884,226; the bottom-up projection says $9,147,500, which is $263,274, or 3.0%, above it.
That gap is not an error, but it has to be explained. A gap backed by named deals, a new product or a stated price rise is a projection; a gap with no explanation means the retention or win rates are optimistic. Write the explanation as lines: deal names and values, or the price change times the base it applies to, until the lines sum to the gap.
Retained plus new must equal the projection in every segment, and the segments must sum to the total: 5,737,000 + 2,806,000 + 604,500 = 9,147,500. Then phase the annual figure into months using last year's pattern; seasonality in sales measures shows how. The monthly phasing feeds the cash flow forecast, where receipts follow invoices by the customers' payment days.
No one method is right for every use. Chambers, Mullick and Smith made the point in How to Choose the Right Forecasting Technique (Harvard Business Review, 1971): "care must be taken to select the correct technique for a particular application." For an annual budget, the bottom-up build with a trend check is the one that can be explained line by line.
Upload the invoice ledger and pipeline export and Covirage's tools compute NRR and win rates by segment, build the bottom-up projection and the trend check side by side; the external AI model explains the gap, never computes it. See Covirage for sales teams. For a workbook, see the sales forecast template; for the trend check in Excel, the Excel FORECAST function and moving average in Excel.
An estimate of future sales for a period, usually the next year, built from existing customers, expected new business and known changes such as price rises. It differs from a target, which is what you want, and from a forecast, which is usually shorter-term and updated often.
Take current revenue from existing customers and apply retention by segment, add new business as qualified pipeline times win rate times the share of the year it will bill, then compare the total with the historical growth trend and explain the difference.
The terms overlap. A projection usually looks further ahead, such as next year's budget, and rests on stated assumptions. A forecast is usually near-term, such as this quarter, and is updated as deals progress. Both should be checked against actuals.
Measure it rather than assume it: compare each year's projection with the actual, in total and by segment, and adjust the method. A projection that is always high is biased, not unlucky, and segment errors are usually larger than the total's.