A pipeline is the list of all open deals, sorted by the stage they have reached. Sales teams use it to answer a simple question: how much will we sell this quarter? This worksheet introduces three common figures for that question. All companies, people and numbers are invented for teaching.
The three formulas
Weighted pipeline. Not every open deal will be won. Each stage gets a probability, usually based on how many deals at that stage the team won in the past. The weighted value of a deal is:
weighted value = deal value × stage probability
The weighted pipeline is the sum of all weighted values.
Pipeline coverage ratio. It compares the open pipeline with the part of the target still to be reached:
coverage ratio = open pipeline value ÷ remaining target
A ratio of 2 means the pipeline is twice as big as the gap. How much coverage is enough depends on the win rate (the share of deals that are won): if a team wins 25 percent of its pipeline, it needs about 1 ÷ 0.25 = 4 times the gap.
Sales velocity. It estimates how much revenue the pipeline produces per day:
sales velocity = (number of opportunities × win rate × average deal value) ÷ sales cycle length in days
An opportunity is a qualified deal in the pipeline. The sales cycle is the average time from a new opportunity to a won deal. Companies define these figures slightly differently, so always say which numbers you used.
The situation
Mia Hoffmann sells planning software at Proseware, a fictional software company. Customers pay for one year in advance. It is 1 November. Mia's target for the quarter (October to December) is 120,000 euros. In October she already won deals worth 52,000 euros.
Proseware uses these stage probabilities, based on its own (fictional) results from the last two years:
Stage | Probability |
|---|---|
Qualified | 10 % |
Discovery | 25 % |
Proposal | 50 % |
Negotiation | 75 % |
Commit (verbal yes, contract not yet signed) | 90 % |
Mia's open deals:
No. | Customer | Stage | Deal value (euros) | Expected close | Weighted value (euros) |
|---|---|---|---|---|---|
1 | Woodgrove Bank | Negotiation | 36,000 | November | |
2 | Coho Winery | Proposal | 8,500 | December | |
3 | Lamna Healthcare | Discovery | 24,000 | February | |
4 | Alpine Ski House | Commit | 12,000 | November | |
5 | Fourth Coffee | Qualified | 6,000 | January | |
6 | Margie's Travel | Proposal | 15,000 | December | |
7 | Wide World Importers | Discovery | 30,000 | January | |
8 | Trey Research | Negotiation | 9,600 | December | |
9 | Wingtip Toys | Qualified | 18,000 | February | |
10 | Relecloud | Proposal | 21,500 | December |
Tasks
Task 1: The unweighted pipeline
Add up the values of all ten deals. What is Mia's total open pipeline?
Solution to task 1
36,000 + 8,500 + 24,000 + 12,000 + 6,000 + 15,000 + 30,000 + 9,600 + 18,000 + 21,500 = 180,600 euros.
This is the value if every deal were won, which will not happen.
Task 2: The weighted pipeline
Fill in the last column of the table: deal value × stage probability. Then add up the weighted values. What share of the unweighted pipeline is the weighted pipeline?
Solution to task 2
No. | Customer | Calculation | Weighted value |
|---|---|---|---|
1 | Woodgrove Bank | 36,000 × 0.75 | 27,000 |
2 | Coho Winery | 8,500 × 0.50 | 4,250 |
3 | Lamna Healthcare | 24,000 × 0.25 | 6,000 |
4 | Alpine Ski House | 12,000 × 0.90 | 10,800 |
5 | Fourth Coffee | 6,000 × 0.10 | 600 |
6 | Margie's Travel | 15,000 × 0.50 | 7,500 |
7 | Wide World Importers | 30,000 × 0.25 | 7,500 |
8 | Trey Research | 9,600 × 0.75 | 7,200 |
9 | Wingtip Toys | 18,000 × 0.10 | 1,800 |
10 | Relecloud | 21,500 × 0.50 | 10,750 |
Total | 83,400 |
83,400 ÷ 180,600 = 0.4618, so the weighted pipeline is about 46.2 percent of the unweighted pipeline.
Task 3: Weighted value by stage
Add up the weighted values for each stage. Which stage holds the largest share of the weighted pipeline?
Solution to task 3
Stage | Deals | Unweighted | Weighted | Share of weighted pipeline |
|---|---|---|---|---|
Qualified | 2 | 24,000 | 2,400 | 2.9 % |
Discovery | 2 | 54,000 | 13,500 | 16.2 % |
Proposal | 3 | 45,000 | 22,500 | 27.0 % |
Negotiation | 2 | 45,600 | 34,200 | 41.0 % |
Commit | 1 | 12,000 | 10,800 | 12.9 % |
Total | 10 | 180,600 | 83,400 | 100 % |
Negotiation holds the largest share, 34,200 euros or 41.0 percent. Discovery has the largest unweighted value (54,000) but counts for much less, because those deals are still early.
Task 4: The forecast for this quarter
Only deals expected to close in November or December count for this quarter. Calculate the weighted value of those deals and add what Mia has already won in October. Will she reach her target of 120,000 euros?
Solution to task 4
Deals expected in November or December: 1, 2, 4, 6, 8 and 10.
Weighted: 27,000 + 4,250 + 10,800 + 7,500 + 7,200 + 10,750 = 67,500 euros.
Forecast for the quarter: 52,000 (won) + 67,500 (weighted) = 119,500 euros.
That is 500 euros short of the target of 120,000. On paper the target looks almost reached. Tasks 5 and 8 show why Mia should not relax.
Task 5: Coverage
The remaining target is the part of the target Mia has not reached yet. For the open pipeline, use the unweighted values of the deals expected this quarter (the same six deals as in task 4).
a) Calculate the remaining target and the unweighted value of this quarter's deals.
b) Calculate the coverage ratio.
c) Last year Mia won 30 percent of the value of her qualified opportunities. What coverage would that suggest she needs, and how large would the pipeline for this quarter have to be?
d) The weighted forecast in task 4 says "almost on target", the coverage ratio says "far too little". How can both be true?
Solution to task 5
a) Remaining target: 120,000 minus 52,000 = 68,000 euros. Unweighted value of deals 1, 2, 4, 6, 8 and 10: 36,000 + 8,500 + 12,000 + 15,000 + 9,600 + 21,500 = 102,600 euros.
b) Coverage ratio: 102,600 ÷ 68,000 = 1.51 (rounded).
c) With a 30 percent win rate she would need about 1 ÷ 0.30 = 3.33 times the gap, which is 68,000 ÷ 0.30 = about 226,667 euros of pipeline for this quarter.
d) The rule "1 ÷ win rate" assumes an average mix of deals, from early to late stages, with an average chance of 30 percent. Mia's deals for this quarter are mostly late (negotiation and commit), and late deals have a higher chance than the average. The weighted forecast takes the stages into account; the simple coverage rule does not. Both are rough estimates. A low coverage ratio is still a warning: if one or two late deals slip or are lost, there is almost nothing in the pipeline to replace them this quarter.
Task 6: Sales velocity
Proseware's whole sales team works with these figures (all fictional): 48 open opportunities, a win rate of 25 percent (by number of deals), an average deal value of 16,000 euros and an average sales cycle of 80 days.
a) Calculate the sales velocity in euros per day.
b) How much revenue does that suggest for 30 days?
Solution to task 6
a) (48 × 0.25 × 16,000) ÷ 80 = 192,000 ÷ 80 = 2,400 euros per day.
b) 2,400 × 30 = 72,000 euros in 30 days.
Sales velocity is an average. It does not say which deals will close, only how fast the pipeline turns into revenue if things continue as before.
Task 7: Which lever helps most?
The sales manager has four ideas. Calculate the new sales velocity for each one, changing only that one figure.
a) Better qualification raises the win rate from 25 to 30 percent.
b) A shorter contract and faster approvals cut the sales cycle from 80 to 60 days.
c) More prospecting brings 10 more opportunities (58 instead of 48).
d) A larger package raises the average deal value from 16,000 to 18,000 euros.
Which idea raises sales velocity the most? Name one reason why the biggest number is not automatically the best choice.
Solution to task 7
Idea | Calculation | Velocity (euros per day) | Change |
|---|---|---|---|
Today | (48 × 0.25 × 16,000) ÷ 80 | 2,400 | |
a) Win rate 30 % | (48 × 0.30 × 16,000) ÷ 80 | 2,880 | +480 (+20 %) |
b) Cycle 60 days | (48 × 0.25 × 16,000) ÷ 60 | 3,200 | +800 (+33.3 %) |
c) 58 opportunities | (58 × 0.25 × 16,000) ÷ 80 | 2,900 | +500 (+20.8 %) |
d) Deal value 18,000 | (48 × 0.25 × 18,000) ÷ 80 | 2,700 | +300 (+12.5 %) |
Idea b, the shorter sales cycle, raises velocity the most in this example.
Why it is not automatically the best: each idea has a cost and a chance of working. Cutting the cycle by a quarter may be very hard, while ten more opportunities may be easy. The ideas also affect each other: pushing for a larger deal value can make the cycle longer, and adding opportunities of poor quality can lower the win rate.
Task 8: One big deal
a) What share of Mia's total weighted pipeline (task 2) comes from the Woodgrove Bank deal?
b) Woodgrove Bank either signs the full 36,000 euros or nothing. Calculate Mia's quarter result if Woodgrove is won and if it is lost, with all other deals counted at their weighted value as in task 4.
c) What should Mia do with this information?
Solution to task 8
a) 27,000 ÷ 83,400 = 32.4 percent of the total weighted pipeline. For this quarter alone it is 27,000 ÷ 67,500 = 40 percent.
b) Forecast without Woodgrove's weighted value: 119,500 minus 27,000 = 92,500.
Woodgrove won: 92,500 + 36,000 = 128,500 euros (above target).
Woodgrove lost: 92,500 + 0 = 92,500 euros (27,500 below target).
c) A weighted value of 27,000 never actually happens for a single deal: it is either 36,000 or 0. Weighting works well for many deals of similar size, and badly for one large deal. Mia should check carefully what is still open with Woodgrove (who signs, by when, what could stop it), and build more pipeline early so the quarter does not depend on one customer.
Online self-check
This short check is optional and works only on screen.
Check your key results
Mia's unweighted pipeline is euros.
Her weighted pipeline is euros.
The forecast for the quarter, including October, is euros.
The coverage ratio for this quarter is .
The team's sales velocity is euros per day.
Cutting the sales cycle to 60 days raises it to euros per day.
For the teacher
Time. About 45 minutes: 10 minutes to read the formulas together, 30 minutes for the tasks in pairs, 5 minutes to compare task 5 d) and task 8 c), which need explanations rather than numbers.
Easier. Do tasks 1 to 4 and 6 only, and give the weighted values for the first three deals.
Harder. Ask learners to build the table in a spreadsheet with formulas, then change the stage probabilities and see how the forecast moves. Or ask them how Proseware should calculate its stage probabilities from past deals.
Common mistakes. Using percentages as whole numbers (36,000 × 75 instead of × 0.75); counting deals that close in January or February in this quarter's forecast; dividing by the full target instead of the remaining target in the coverage ratio.
Honesty point. The probabilities, win rates and targets are invented. Real teams calculate their own from their history; there is no correct probability for a stage that fits every company.