Pipeline-Based Sales Forecasting: How to Do It Well

Pipeline sales forecasting predicts revenue from the deals currently moving through your sales process, by weighting each deal or stage by the chance that it closes and the timing of when it will. Done well, it gives finance an early, auditable view of the next two to four quarters. Done badly, it is a stack of optimistic estimates that inflates the plan. The difference is whether the probabilities come from your own conversion history and whether the data in the CRM are clean. This guide shows the method with a worked example, explains coverage ratios and timing, sets out controls a CFO can apply and lists common failures. It is part of budgeting and forecasting, builds on sales forecasting methods and relates to forecast bias in financial planning.

When pipeline forecasting fits

For recurring revenue and seasonal products, combine the pipeline forecast with a baseline from history.

The method

1. Define the stages clearly, with entry criteria that a deal must meet (for example, "Proposal: written proposal sent to a named decision-maker").
2. Measure historical conversion from each stage to closed-won, using at least 8 to 12 quarters of data where available.
3. Weight the pipeline by those conversion rates.
4. Model timing: when will each stage's deals close?
5. Compare with coverage and targets, and investigate gaps.
6. Review regularly, and measure accuracy.

A worked example

A hypothetical company has the following open pipeline at the start of a quarter.

Check the history-weighted column: 2,000,000 × 10% = 200,000; 1,500,000 × 25% = 375,000; 1,000,000 × 45% = 450,000; 600,000 × 70% = 420,000; sum = 1,445,000. The rep-weighted column: 400,000 + 600,000 + 600,000 + 480,000 = 2,080,000.

The rep-assigned probabilities produce a forecast of $2,080,000, which is $635,000 or 44 percent higher than the forecast from historical conversion ($635,000 / $1,445,000 = 43.9%). If the historical rates are right, using the reps' numbers would overstate revenue by that amount. The raw, unweighted pipeline of $5,100,000 would be worse still. This is the main reason pipeline-based forecasts are biased high and why stage conversion must come from data.

Note that the weighted figure is an expected value over the whole pipeline. It will not match any single outcome, since deals are won or lost whole. For a few large deals, the spread around the expected value is wide; consider presenting a range, for instance by listing the large deals separately and weighting only the smaller ones.

Timing: when will it close?

Weighted value tells you how much, not when. Use cohort timing from history. Suppose a cohort of $1,000,000 of newly created opportunities historically converts as follows:

Expected won revenue from the cohort: $80,000 in the first month, $120,000 in the second, $100,000 in the third and $50,000 in the fourth, a total of $350,000, which is a 35 percent win rate on creation value. Apply the same pattern to each month's new opportunities and add the cohorts to get a month-by-month forecast, including deals that have not yet been created. That is how a pipeline forecast reaches beyond the current open deals.

Pipeline coverage

Coverage is the ratio of pipeline to target, and a rough guide to whether a target is realistic.

Coverage needed = 1 / Win rate

If your win rate from creation is 25 percent, you need about 1 / 0.25 = 4 times the target in pipeline. If the target for the quarter is $1,000,000 and you have $2,500,000 of pipeline (2.5 times) when 4 times is needed, the target is at risk unless the pipeline grows or conversion is better than history. Use your own win rates, not a rule of thumb copied from elsewhere, and measure by stage, since late-stage pipeline needs less coverage.

Adjustments that improve the forecast

Start with stage conversion and add adjustments only where the data show real differences and the sample is large enough.

Recurring revenue and templates

If part of your revenue recurs, forecast that portion from the installed base and churn, and use the pipeline only for new business. See SaaS revenue forecasting for the cohort approach. If you need a place to start for the base-business forecast, the structure in the sales forecast template can sit beside the pipeline sheet.

Forecast categories

Many teams ask reps to classify deals as commit, best case or pipeline. Treat these as inputs and check them against history: what share of "commit" deals actually closed in the period, and what share of "best case"? If commit deals close 85 percent of the time, a commit total of $1,000,000 means expected revenue of $850,000, not $1,000,000. Publish the category accuracy so that reps calibrate.

Controls for the CFO

From bookings to revenue and cash

The pipeline forecast gives bookings, which must be converted to revenue and cash.

• Revenue timing depends on delivery or recognition: a subscription booked in March starts producing revenue when the service starts. Check accounting policy with your accountants.
• Cash timing depends on billing terms and customer payment behavior.
• Cancellations and returns reduce net bookings.

Build the conversion explicitly, so the revenue forecast and cash forecast follow from the bookings forecast. A company that sells to larger customers on 60-day terms should expect the cash to arrive about two months after invoice.

Building it in a spreadsheet

Layout:

1. A table of open opportunities with amount, stage, expected close date, source, created date and owner.
2. A conversion table with stage, historical win rate and average days to close.
3. A calculation column: amount × stage conversion.
4. A summary by month or quarter, using a pivot table or SUMIFS on expected close date.
5. A history sheet with weekly snapshots to evaluate accuracy.

Weighted value = Amount×VLOOKUP(Stage, ConversionTable, 2, FALSE)
Quarter total  = SUMIFS(WeightedValue, CloseQuarter, "Q3")

Reviewing accuracy

Compare the forecast made at the start of each quarter with the outcome. Break the variance into: deals that closed that were not in the forecast, deals that slipped, deals lost, and difference in deal value. Over time, you learn where the model errs and adjust. For the cost of bias in profit terms, link to unit economics.

Common mistakes

• Using rep-assigned probabilities without checking them.
• Counting unweighted pipeline as a forecast.
• Ignoring deal age and slippage.
• No distinction between bookings, revenue and cash.
• Mixing new and expansion business with different win rates.
• Not saving snapshots.
• Using too little history for conversion rates.
• Reporting a single number with no range when a few large deals dominate.

FAQs

What is pipeline sales forecasting?

Predicting revenue from open opportunities by weighting them with historical conversion rates and timing.

How do you calculate a weighted pipeline forecast?

Multiply each deal's value, or each stage's total, by the historical probability of winning from that stage and add the results.

What is pipeline coverage?

The ratio of pipeline to target. The coverage needed is one divided by your win rate.

Why are pipeline forecasts often too high?

Stage probabilities are set by judgment, not history, and stale or slipping deals stay in the pipeline.

How often should conversion rates be updated?

At least quarterly, using a rolling window of recent history.