Stage percentages are labels. Fit them to history, add timing, set coverage from your own conversion and report a range.
A weighted pipeline forecast multiplies each open deal's value by the probability that it closes in the period, and sums. The probability must be the stage's historical win rate times the share of deals that close inside the quarter, not the percentage the CRM attaches to the stage. On an illustrative pipeline of 39.9 million against a 12.0 million quarterly target, the CRM weighting says 15.47 million, a comfortable beat; history and timing say 8.15 million, 67.9 per cent of target.
Worked in full in Financial Planning and Analysis by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
Nothing in the CRM figure is fabricated. Each stage percentage is a reasonable-looking number someone typed in years ago. The forecast fails because those percentages answer a different question: what share of deals at this stage are ever won, if anyone ever measured it, and with no view on when.
| Stage | Deals | Average size | Value | CRM probability | Historical win rate | Closes this quarter |
|---|---|---|---|---|---|---|
| Prospecting | 40 | 0.30 | 12.0 | 10% | 5% | 20% |
| Qualified | 30 | 0.35 | 10.5 | 25% | 14% | 40% |
| Proposal | 20 | 0.40 | 8.0 | 50% | 32% | 65% |
| Negotiation | 12 | 0.45 | 5.4 | 75% | 58% | 85% |
| Verbal commit | 8 | 0.50 | 4.0 | 90% | 82% | 95% |
| Total | 110 | 39.9 |
The historical win rate is measured, not assumed: of the value that entered each stage over the last six to eight quarters, how much was eventually won. The timing share is measured the same way: of the deals at that stage that were won, how many closed within the following quarter. Both come out of the CRM's own stage history, which is the data the stage percentages should have been fitted to in the first place.
Effective probability = historical win rate × share closing this quarter
Prospecting 5% × 20% = 1.0%; Qualified 14% × 40% = 5.6%; Proposal 32% × 65% = 20.8%; Negotiation 58% × 85% = 49.3%; Verbal commit 82% × 95% = 77.9%
Forecast = Σ value × effective probability
= 12.0 × 1.0% + 10.5 × 5.6% + 8.0 × 20.8% + 5.4 × 49.3% + 4.0 × 77.9%
= 0.120 + 0.588 + 1.664 + 2.662 + 3.116 = 8.15
Excel: =SUMPRODUCT(Value,WinRate,InQuarter)
| Method | Forecast | Against target |
|---|---|---|
| CRM stage probabilities | 15.47 | 3.47 |
| Historical win rates, no timing | 11.04 | −0.96 |
| Historical win rates and timing | 8.15 | −3.85 |
The bridge from the CRM figure to the honest one has two parts: 4.43 million because the stage percentages overstate the win rate, and 2.89 million because a deal that will be won is not necessarily won this quarter. The CRM figure is 89.9 per cent above the timed forecast. Early stages are the worst calibrated (prospecting wins half as often as the CRM assumes), which is why 70.9 per cent of the honest forecast, 5.78 million, sits in the last two stages.
The pipeline is 3.3 times target, which passes the common three-times coverage rule. That rule assumes roughly a third of the pipeline converts in the quarter. This pipeline converts 20.4 per cent, so hitting 12.0 would need 58.7 million of pipeline, coverage of 4.9 times. The coverage ratio a business should use is the reciprocal of its own in-quarter conversion, not a figure from a sales blog.
Put the other way, the 3.85 million gap would need 18.51 million of new value at proposal stage, where 20.8 per cent converts in the quarter. That is more than double the proposal stage today, which is a conversation about next quarter, not this one.
A weighted pipeline is an expected value. Treating each deal as an independent win-or-lose event, the standard deviation of the outcome is the square root of Σ deals × p × (1 − p) × size², here 1.31 million. That gives a P10 to P90 range of 6.47 to 9.83 million, using z = 1.2816. On those figures the probability of reaching 12.0 is about 0.2 per cent. Real deals are not independent (one lost budget cycle can delay several at once), so the true range is wider; the point is that the target sits far outside it.
| Win rates | Timing 80% | Timing 100% | Timing 120% |
|---|---|---|---|
| 90% of history | 5.87 (48.9%) | 7.34 (61.1%) | 8.33 (69.4%) |
| History | 6.52 (54.3%) | 8.15 (67.9%) | 9.26 (77.2%) |
| 110% of history | 7.17 (59.8%) | 8.97 (74.7%) | 10.18 (84.9%) |
Even a quarter in which deals win a tenth more often than history, with a fifth more of them closing inside the quarter, reaches 10.18 million. No plausible combination closes the gap, which is the useful message for the forecast meeting.
Weight by what happened, not by what the stage is called. Fit stage probabilities to the CRM's own history, multiply by the in-quarter share, and recalibrate every quarter.
Weight the pipeline by measured win rates times in-quarter closing shares, set coverage as the reciprocal of conversion, and report a range. This pipeline looks like 129.0 per cent of target on CRM weights and is 67.9 per cent on its own history. Bias in a forecast of this kind is measured after the event in how to calculate forecast bias and MAPE, and the wider driver logic is in how to build a driver-based revenue forecast. The book's own forecast-accuracy and comparison models are in the free workbooks for Financial Planning and Analysis.
The reciprocal of your in-quarter conversion rate. If 20.4 per cent of open pipeline value converts within the quarter, as in the worked case, coverage must be 4.9 times target, so a 12.0 million target needs 58.7 million of pipeline. The common three-times rule only works when about a third of the pipeline converts.
For each stage, take the value of deals that entered it over the last six to eight quarters and divide the value eventually won by the value that entered. Measure separately the share of won deals that closed within one quarter. In the case, proposal stage wins 32 per cent and 65 per cent of those wins close in the quarter, an effective 20.8 per cent.
It is an expected value, so it is unbiased only if the weights are calibrated, and it says nothing about the spread. Treating deals as independent, the case has a standard deviation of 1.31 million around 8.15 million, a P10 to P90 range of 6.47 to 9.83 million. Correlated deal slippage makes the real range wider.
This article is one calculation from Financial Planning and Analysis. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: corporate finance, valuation and markets → · All 1614 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.