How to build a sales pipeline that forecasts well

It’s the start-of-month sales meeting. Each rep says how much they’ll close, and the total is a number everyone knows won’t happen. By month-end half of it closes, and nobody can explain whether it was bad luck, bad opportunities or a pipeline padded with deals that had been dead for months. The problem is almost never the team: it’s that the pipeline has no rules.

This guide explains, step by step, how to build a pipeline you can forecast from. It works the same in a spreadsheet as in a CRM.

1. Define stages with exit criteria

A stage doesn’t describe what the rep is doing, but what has already happened with the customer. That’s why each stage needs a verifiable exit criterion: a fact anyone can check.

Stage	Exit criterion (to move to the next one)
Prospect	There was contact and the customer agreed to a meeting
Qualified	Need, rough budget, decision-maker and estimated date confirmed
Proposal sent	The customer received the quote and reviewed it with you
Negotiation	There are concrete objections about price, timing or terms, no longer about the what
Commitment	The customer confirmed they will buy and it’s in their internal approval process
Won / Lost	Signature or purchase order, or loss reason recorded

Practical rules:

1. Five to seven stages. More stages don’t add precision, only more debate.
2. An opportunity doesn’t skip stages without meeting the criteria in between.
3. Losing always requires a reason (price, competitor, no budget, no response). Without reasons you can’t improve.

2. Assign a probability per stage based on your history

A stage’s probability isn’t made up: it’s calculated from your closed opportunities.

Stage probability = opportunities that reached that stage and were won ÷ closed opportunities (won + lost) that reached that stage.

If you have no history, start with reasonable values and recalculate them every quarter. For the example we’ll use: Prospect 10%, Qualified 20%, Proposal 40%, Negotiation 60%, Commitment 80%.

3. Calculate the weighted forecast

Weighted forecast = Σ (opportunity amount × probability of its stage).

Suppose this open pipeline, in bolivianos:

Opportunity	Amount	Stage	Probability	Weighted	Expected close
A	40,000	Negotiation	60%	24,000	This month
B	25,000	Proposal	40%	10,000	Next month
C	80,000	Qualified	20%	16,000	In two months
D	15,000	Commitment	80%	12,000	This month
E	50,000	Proposal	40%	20,000	This month
F	30,000	Prospect	10%	3,000	In three months
Total	240,000			85,000

Three readings:

• The total pipeline (Bs 240,000) is not a forecast. Adding it up is where the meeting’s inflated number comes from.
• The weighted forecast for the whole pipeline is Bs 85,000, but spread over several months.
• For this month only A, D and E count: 24,000 + 12,000 + 20,000 = Bs 56,000. That’s the number worth committing to.

Weighting works well with many opportunities; with a few large ones, also review the high-probability deals one by one.

4. Measure the conversion rate

Win rate = won opportunities ÷ closed opportunities (won + lost). Don’t include open ones.

Suppose last quarter you closed 60 opportunities: 18 won and 42 lost. Your rate is 18 ÷ 60 = 30%.

Also look at it stage by stage (how many move from Proposal to Negotiation, for example): that shows where the funnel leaks. If most die after the proposal, the problem may be price or how the offer is presented, not prospecting.

With the rate you can calculate the coverage you need: if your monthly target is Bs 100,000 and you win 30%, you need about 100,000 ÷ 0.30 ≈ Bs 333,000 in opportunities closing that month.

5. Track time in stage and the sales cycle

• Time in stage: days since the opportunity entered its current stage. Calculate the average of won deals for each stage.
• Sales cycle: days from when the opportunity was created until it was won.

A useful rule: if an opportunity has been in its stage for more than twice the average of won deals, review it. Most likely it’s stalled and its real probability is lower than its stage’s.

With this data you can calculate pipeline velocity:

Velocity = (open opportunities × average amount × win rate) ÷ sales cycle in days.

Suppose 30 opportunities, an average amount of Bs 20,000, a 30% rate and a 45-day cycle: 30 × 20,000 × 0.30 ÷ 45 = Bs 4,000 per day. If you want to sell more, you can move any of the four variables, and this calculation shows you the effect of each.

6. Pipeline hygiene: the weekly review

A forecast is only as good as its data. Review every week:

• Opportunities with a past close date: update the date or mark them lost.
• Opportunities with no next activity scheduled.
• Amounts not updated since the latest version of the quote.
• Stalled opportunities according to the time-in-stage rule.
• Losses with no reason recorded.

And once a month, compare: forecast accuracy = actual sales ÷ committed forecast. If you committed Bs 56,000 and closed Bs 50,000, accuracy is 89%. If you consistently fall short, your probabilities are inflated: recalculate them.

Common mistakes

• Stages that describe activities (“called”, “sent email”) instead of customer facts.
• Fixed probabilities nobody reviews, or that each rep adjusts by gut feel.
• Committing the total pipeline instead of the period’s weighted figure.
• Not closing dead opportunities, which inflate the pipeline and sink the real rate.
• Calculating the rate with open opportunities, which distorts it.
• Quotes and pipeline disconnected, with amounts that don’t match.

Quick checklist

1. Five to seven stages with a written exit criterion.
2. Probability per stage calculated from your own data and reviewed every quarter.
3. Close date and next activity required on every opportunity.
4. Reason required when losing.
5. Weekly hygiene review and monthly accuracy comparison.

Taking it into a system

All of this can be kept in a spreadsheet with discipline. If you’d rather have the math done for you, a CRM like the one in MKA keeps the pipeline on a Kanban board where each stage has its probability, calculates the weighted forecast and includes conversion funnel and time-in-stage reports (see CRM).