Weighted Pipeline Forecast Template
A ready-built spreadsheet template that weights every deal by its stage's historical win-rate, so the forecast number is math derived from your own data, not a gut feel.
How to use it
Build this as a live spreadsheet, plug your own trailing-12-month win-rates into Step 1, then keep every open deal in the Step 2 table, the Weighted Forecast in Step 3 is the number to bring to leadership instead of a gut-feel total.
What's inside
- Default stage win-rate table (Qualified through Verbal Commit) with instructions to replace with your own trailing-12-month data
- Deal-level template columns: owner, stage, raw value, stage win-rate, weighted value, close date, days-in-stage, forecast category
- Weighted Value formula (Raw Value × Stage Win-Rate) applied per row
- Rollup formulas for total raw pipeline, total weighted pipeline, and weighted coverage ratio
- Aging-flag rule for deals sitting 1.5x+ longer than normal time-in-stage
- Monthly reconciliation instructions against actual closed-won to recalibrate win-rates
- Governance rule against manually overriding the weighted-value formula
Step 1: Set your stage win-rates (calibrate from your own history, defaults below only as a starting point)
| Stage | Default Win-Rate (use until you have your own data) | Your Actual Win-Rate (last 12mo) |
|---|---|---|
| 1. Qualified | 10% | ___ |
| 2. Discovery / Needs Analysis | 20% | ___ |
| 3. Solution / Demo Delivered | 35% | ___ |
| 4. Proposal / Quote Sent | 50% | ___ |
| 5. Negotiation | 70% | ___ |
| 6. Verbal Commit | 90% | ___ |
To calculate your own win-rate by stage: for each stage, take (deals that reached that stage and later closed-won) ÷ (all deals that ever reached that stage) over the trailing 12 months. Recalculate quarterly, win-rates drift as your process and ICP change.
Step 2: The deal-level template
| Deal / Account | Owner | Stage | Raw Value ($) | Stage Win-Rate | Weighted Value ($) | Close Date | Days in Current Stage | Forecast Category |
|---|---|---|---|---|---|---|---|---|
| =Raw×Win-Rate | Commit / Best Case / Pipeline | |||||||
Weighted Value formula (every row): Raw Value × Stage Win-Rate = Weighted Value
Step 3: Roll it up
| Metric | Formula | Value |
|---|---|---|
| Total Raw Pipeline | Sum of Raw Value column | |
| Total Weighted Pipeline | Sum of Weighted Value column | |
| Weighted Forecast (the number to give leadership) | Total Weighted Pipeline | |
| Commit-only Total | Sum of Raw Value where Category = Commit | |
| Commit + Best Case Total | Sum of Raw Value where Category ∈ {Commit, Best Case} | |
| Quota | ||
| Weighted Coverage Ratio | Total Weighted Pipeline ÷ Quota (target ≈1.0–1.2x) |
Step 4: Aging flag (add this column, it catches the deals that quietly rot)
Add a column: Days in Current Stage. Flag any deal where days-in-stage exceeds 1.5× the median time-in-stage for deals that eventually won at that stage. A deal sitting 3× the normal time in "Proposal Sent" is not a Commit regardless of what the raw stage suggests, down-weight it manually or move it to Pipeline category.
Step 5: Reconcile monthly
At month-end, compare Total Weighted Pipeline (start of month) against Actual Closed-Won (end of month) using the companion Forecast Accuracy Calculator. If weighted-pipeline consistently overshoots actuals, your stage win-rates are set too high, recalibrate Step 1 downward. If it consistently undershoots, they're set too low.
Notes on use
- Recalculate stage win-rates every quarter, not once and forget it: a process change (new qualification bar, new competitor) shifts these numbers.
- Never let a rep hand-edit the Weighted Value cell directly: it should always be the formula output, never an override. If a rep believes a deal is worth more than its stage-weighted value, that's a deal-review conversation, not a spreadsheet edit.
- Segment the rollup by rep and by segment before trusting the blended number, see the companion Pipeline Coverage Ratio Calculator for why blended numbers hide risk.