Weighted Sales Pipeline Forecast: How to Calculate It in a Spreadsheet

You have a pipeline number. Someone asked how the quarter looks and you added up everything still open and said it out loud.

The problem with that number is that it treats a deal you are signing on Thursday and a form fill from a directory listing as the same asset. A weighted forecast fixes exactly that and nothing else, and it takes one formula.

This is the method, worked end to end on a real-shaped pipeline of 22 open deals. It sits under the client CRM and pipeline tracker guide, which covers the row structure and the follow-up side.

The Formula

For every open deal:

weighted value = deal value × probability of its current stage

Sum that column and you have the forecast. In a spreadsheet, with stages in column G and values in column F, and a two-column stage table on a settings tab:

=SUMPRODUCT(F2:F200, IFERROR(VLOOKUP(G2:G200, Settings!$A$2:$B$7, 2, FALSE), 0))

Three details do most of the work here:

• The probability lives in the settings table, not on the deal row. The moment a rep can type 90% next to their own deal, the forecast becomes a negotiation.
• IFERROR(..., 0) means a typo’d or blank stage contributes zero rather than an error, so one bad row does not take the whole forecast down. Pair it with a row check that counts blank stages, or zero quietly becomes a hiding place.
• It runs over the whole range, blanks included, so the sheet keeps working as rows are added.

Setting the Probabilities

This is the only input requiring thought. Ideally you derive it: of every deal that ever reached “Quoted,” what share did you eventually win? That fraction is your Quoted probability. Two dozen closed deals is enough to start.

Without history, this is a defensible starting set for a five-stage pipeline:

Nothing sits at 0% or 100% — those are “lost” and “won,” and they belong out of the open pipeline entirely. And the biggest single jump should be from Quoted to Negotiating, because that is where buyers self-select: they do not argue about terms for something they were never going to buy.

Fix these at the start of a quarter and leave them alone until it ends. A probability set that moves with your mood measures your mood.

A Worked Pipeline

Twenty-two open deals from one sample business, run through the formula:

$392,150 becomes $169,392 — a 57% haircut. That is not pessimism, it is the same arithmetic your win rate has been performing on you all along, moved forward to where it can be useful.

Now read the right-hand column on its own, because this is the part the total hides. Four deals in Negotiating carry $88,538 — over half the entire forecast. The eight deals at the top of the funnel, worth $92,900 between them and looking substantial on any unweighted report, contribute $9,265, about a tenth as much.

Which tells you where Monday goes. If you had one hour this week to move a number, it goes on the four negotiations, not the eight new leads. The unweighted pipeline actively argues the opposite, because the top of the funnel is where the row count is.

Adding Dates: The 30/60/90 Window

The weighted total answers how much. It cannot answer when, because nothing in it refers to a calendar. Add an expected close date to each row and re-sum by window:

30-day weighted = SUMIFS of weighted value where close date falls in the next 30 days

In the worked pipeline, $133,438 of the $169,392 sits inside 30 days. Four-fifths of the forecast lands in the first third of the quarter.

That is a real finding with an immediate consequence. This business is not short of closing work — it is about to be short of pipeline, because in five weeks most of what it is carrying will have resolved one way or the other. The right move is prospecting now, while the quarter still looks healthy. The unweighted total, which will stay large right up until the deals close, gives no warning at all.

The same split run six months forward by expected close month is where hiring decisions live. A month with $4,000 of weighted pipeline in it is not a slow month you can push through; it is a month that was decided ten weeks ago.

Deals With No Close Date

Some rows will have no expected close date. The rule:

Include them in the weighted total. Exclude them from every dated window. Report the difference.

If the weighted total is $169,392 and the 30/60/90 windows only account for $140,000, the missing $29,392 is not a rounding issue — it is a set of deals nobody has asked a timing question about. That is a useful list, not an inconvenience.

The temptation is to drop a date in to make the windows reconcile. Resist it. A guessed close date is indistinguishable from a real one three weeks later, and the forecast you build on it will be wrong in a way you cannot audit.

Checking the Forecast Against Reality

A forecast nobody scores never improves. Two checks, both cheap:

Coverage. Divide the weighted figure by the target for the same period. Because the weighting already carries your win rate, you are looking for roughly 1.0 or better, not the 3x rule of thumb people quote for unweighted pipeline (that 3x is really just a restatement of a 33% win rate). $169,392 against a $150,000 quarter is fine. The same pipeline against $250,000 is a problem you can see in September rather than December.

Last quarter, scored. Write down the weighted forecast on day one of a quarter. At the end, compare it to what actually closed. If the forecast ran consistently high, your stage probabilities are generous — most often at Quoted, where 50% flatters a business whose real quoted-to-won rate is 35%. Adjust the table once and the whole model improves. That is the advantage of holding the probabilities in one place.

In the worked business, closed deals ran a 58.3% win rate on decided deals with an average of 50.9 days to close. Both numbers are the forecast’s report card: a win rate well above the Quoted probability means the probabilities are too conservative, and a time-to-close well past the window you forecast in means the dates are optimistic.

The Short Version

1. Fix five or six stages and one probability each, in a settings table.
2. Weighted value = value × stage probability, summed with SUMPRODUCT.
3. Add expected close dates and re-sum into 30/60/90 windows.
4. Keep dateless deals in the total, out of the windows, and on a visible list.
5. Score last quarter’s forecast against what closed, and adjust the stage table — not the individual rows.

Five steps and you are forecasting from the same data you already had, just counted honestly.

Next, the other half of the sheet: the follow-up tracker that surfaces overdue, stalled and unowned deals — and the full pipeline guide that both of these sit under.

Featured on ReadySheetGo

Client CRM, Sales Pipeline & Lead Tracker — $16.99

Thirteen linked tabs and 7,217 formulas, with the 22-deal pipeline above already loaded. The Settings tab holds your stages and their probabilities in one place, so a change there moves every forecast on every other tab. Pipeline returns open and weighted value by stage, by owner and by service line. Forecast runs rolling 30, 60 and 90-day windows, projects six months ahead by expected close month, holds dateless deals out of the windows and reports them separately, and sets the whole thing against the twelve months that actually closed.

Also inside: a Follow-Ups tab building overdue and stalled lists from your next-action dates, Win-Loss ranking loss reasons by revenue, Source ROI returning a true cost per win by channel, plus Leads, Clients, Quotes, Activity, Dashboard and Lists. Works in Excel and Google Sheets, no macros.

Get the Client CRM, Sales Pipeline & Lead Tracker →