learn-sales-pipeline-analytics-with-phoebe / Analyst session 4 of 6
Learn B2B Sales Pipeline Analytics with Phoebe · Analyst session 4 of 6

Three forecast methods

Every sales forecast you will meet is one of three things, or a blend of them. Ask the reps what they will close. Multiply each deal by a probability for its stage. Or ignore the stage and ask history how deals of this age and origin have behaved. Each method reads a different field, so each fails in a different way. Tonight all three meet the same 300 open deals of Kettle & Co's office-tea team (stages, amounts and reps constructed) sitting on real Olist lead timings, and we see what each would have said for one quarter.

🟡 Analyst Data analysts who build pipeline reporting and forecasts Hands-on in the browser 45 min
0-3 · Welcome 3-18 · Rep commit, stage-weighted, history-based 18-40 · Build-along: one quarter, three answers 40-45 · Q&A
Part 0

Where we start

So far you can rebuild the pipeline on any date (Analyst session 1), measure conversion by cohort (session 2) and read cycle time with open deals left in (session 3). Tonight those pieces become forecasts. Scope matters here: these are sales forecast methods, built deal by deal from the pipeline. Statistical forecasting models such as ARIMA, Prophet or machine learning on a revenue series are a different discipline, and Learn Timeseries Forecasting with Phoebe owns them. The rep's side of the forecast call lives in AI Sales, A forecast people actually believe.

🎫 Clear all three quiz questions to stamp your passport Live = presented in the 45 minutes Self-study = the full depth, after class ★ Build-along = everyone builds it

★ What you walk out with tonight. The three methods written as formulas you can explain to a sales leader, the one field each of them reads, a range for each instead of a single number, and a stage-weighted query you can run against your own snapshot table on Monday. You also leave with a healthy suspicion of one quarter: a single result is an anecdote, and Analyst session 5 is where the methods get scored properly.

Part 1 · covers rep commit

Rep commit: what the people closest to the deals say

5 min live

The oldest method asks each rep to place every deal in a forecast category. HubSpot's forecast tool (read at source, October 2026) uses Pipeline for low likelihood, Best case for moderate, Commit for high likelihood and committed to the forecast, plus Closed won and Not forecasted. Salesforce is reported to use a similar set: Pipeline, Best Case, Commit, Closed and Omitted. The forecast is the sum of Commit; the range runs from commit to commit plus best case. The categories are a convention, and the catch is that each rep decides where "high likelihood" begins.

FOUR REPS, FOUR COMMIT LINES, ONE STAGE LADDER Kettle & Co, constructed · m yen: commit / best case / actual 1 Contacted 2 Need confirmed 3 Tasting trial 4 Proposal 5 Contract out Signed Aiko, commit at 5 3.55 / 4.61 / 3.28 commit Ben, commit at 4 3.37 / 4.84 / 0.91 commit Chiara, commit at 5 0.94 / 4.29 / 0.51 commit Dev, only once signed 0 / 2.76 / 1.16 commit best case: one stage below the commit line commit: summed into the forecast Summed: commit 7.86, range 7.86 to 24.36. Really closed: 5.86. Ben's lower line carries most of the gap.
🔍 Click to zoom - the same ladder, four private definitions of "commit"; the copper line is the optimist's
LiveHow the commit number is built3 min▶

The point. Sum the amounts of every deal a rep has put in Commit. For Kettle this quarter that is 3.55 + 3.37 + 0.94 + 0 = 7.86m yen.

The range. Low end is commit; high end is commit plus best case. Best case summed is 4.61 + 4.84 + 4.29 + 2.76 = 16.50, so the range is 7.86 to 24.36. Really closed: 5.86, below the floor. The range missed.

Why it can miss low too. Dev commits nothing until a contract is signed, yet 1.16m yen of his deals closed. A sandbagger's zero is a forecast too, and an equally wrong one. Per rep, commit against actual: Aiko 3.55 vs 3.28, Ben 3.37 vs 0.91, Chiara 0.94 vs 0.51, Dev 0 vs 1.16.

Self-studyCategories are a convention, not a measurement2 min▶

HubSpot lets categories be mapped automatically from deal stage, which turns rep commit into a crude stage rule. When reps set them by hand, the category records a judgement and a mood. Neither is wrong, but write down which one your team does. The same help page also lists "Most likely" among its default categories elsewhere, so do not hard-code a fixed list beyond Pipeline, Best case and Commit: check your own configuration.

Rep commit is the only method that can see what is not in the CRM: a call last night, a budget freeze rumour. Keep it. Just never ship it alone, and measure each rep's call ratio over many quarters in Analyst session 5.

Real world

Constructed Kettle & Co example. In the Monday call, Ben says his Proposal deals are "basically done" and commits 3.37m yen. Aiko waits for a contract to go out before committing. Dev, burned once by a slipped renewal, commits nothing unsigned. All three are using the same CRM category with three private definitions. The quarter closes with 0.91m yen from Ben. The fix is not a better rep; it is a written commit rule and a record of how each person's calls land.

Part 2 · covers stage-weighted

Stage-weighted: amount times a probability per stage

5 min live

Stage-weighted replaces judgement with a table. Each open deal contributes its amount times the probability attached to its current stage, and the forecast is the sum. HubSpot calls the product the weighted amount. Everything rides on where the probabilities come from. Kettle's own come from last quarter: of the deals at each stage on 1 March, what share won inside the quarter. HubSpot's default pipeline ships with 20, 40, 60, 80 and 90%. Same deals, two ladders, two very different forecasts.

THE SAME 300 DEALS, TWO PROBABILITY LADDERS stages and amounts constructed · own rates from last quarter stage deals this quarter Kettle's own HubSpot default 1 Contacted 2 Need confirmed 3 Tasting trial 4 Proposal 5 Contract out 153 78 39 19 11 0.0% 0.6% 6.3% 18.1% 64.6% 20% 40% 60% 80% 90% sum of amount x probability 80% band, modelled 8.60m 5.48 to 11.72 63.64m 55.56 to 71.72 Really closed: 5.86m yen. The own ladder's band covers it; the default ladder misses by more than tenfold.
🔍 Click to zoom - one pile of deals, two ladders; the copper total is what a placeholder table forecasts
LiveBuilding your own stage table3 min▶

Take the snapshot on the first day of last quarter (Analyst session 1's as-of join). Group by stage. For each stage, count the deals and count how many were won before the quarter ended. Kettle's prior quarter, as of 1 March 2018:

★ Kettle's own stage table (constructed stages, real outcomes)stage deals won in quarter rate 1 Contacted 1022 0 0.0% 2 Need confirmed 507 3 0.6% 3 Tasting trial 240 15 6.3% 4 Proposal 149 27 18.1% 5 Contract out 79 51 64.6%

The rate must rise with stage; the widget's self-test refuses to run if it does not. If yours does not rise, your stages are not buyer commitments yet (Business session 1).

A constructed check of the arithmetic: a 360,000 yen deal at Proposal adds 360,000 x 0.181 = 65,160 yen on Kettle's own ladder, and 360,000 x 0.80 = 288,000 yen on HubSpot's default.

Self-studyWhere the 80% band comes from2 min▶

Each deal either closes or does not: a yes-or-no outcome worth its amount with probability p. The sum of many of those has a mean (the forecast) and a variance, the sum of amount squared x p x (1 - p). The band is the mean plus or minus 1.2816 standard deviations, which is where 80% of a normal distribution sits. That is a modelled approximation, labelled as such in the widget. It is rough with few deals or one giant deal, and it assumes deals close independently, which a recession or a budget freeze breaks.

Real world

HubSpot's own help pages, read at source. The default pipeline lists Appointment scheduled 20%, Qualified to buy 40%, Presentation scheduled 60%, Decision maker bought-in 80% and Contract sent 90%, and says each stage's probability drives the weighted amount. Mapped onto Kettle's five stages, those defaults forecast 63.64m yen for a quarter that closed 5.86m. That is not HubSpot being wrong; the defaults are placeholders for a team's own history, and a team that never replaces them is forecasting from someone else's guess.

Part 3 · covers history-based

History-based: ignore the stage, ask how deals like this behaved

5 min live

The third method never reads the stage field. In words: for each open deal, ask two questions of history. How often do leads from this origin end up won at all? And of the deals that do get won, what share close in the next 91 days, given this deal is already a certain age and has not closed yet? Multiply the answers, sum over deals, and that is the forecast. It is Analyst session 3's survival idea put to work: a deal that has already waited a long time has used up most of the fast-closing chances.

ONE DEAL'S AGE ON THE CYCLE CURVE curve shape drawn · deal age 20 days, constructed 1.0 0 age a = 20 a + 91 = 111 the 91-day quarter F(a + 91) rise in the window F(a + 91) - F(a) F(a) 0 30 60 90 126, longest days from first contact to won, observed wins of the training cohort p = c x rise / (1 - c x F(a)): the chance of winning in the window, given not won yet. A deal older than 126 days sits where F = 1, so p = 0: the method cannot see past its history.
🔍 Click to zoom - the copper bracket is all of history's say about this deal; its stage never enters
LiveThe formula, one piece at a time4 min▶
★ Per open dealp = c x (F(a + 91) - F(a)) / (1 - c x F(a)) c = share of leads from this origin that were won (training cohort) F = cycle-time CDF: share of observed wins that closed by day x a = the deal's age in days on the forecast date 91 = days in the quarter (1 June to 30 August 2018)

Numerator. c x F(a + 91) is the chance a lead is won by age a + 91; c x F(a) is the chance it was won by age a. The difference is the chance of a win inside the window.

Denominator. This deal is still open, so the cases where it already won are ruled out: divide by 1 - c x F(a).

A constructed check of the arithmetic. With c = 0.124 and made-up curve values F(a) = 0.5 and F(a + 91) = 0.9: numerator 0.124 x 0.4 = 0.0496; denominator 1 - 0.062 = 0.938; p = 0.0529, about 5.3%. Forecast = the sum of amount x p over all 300 deals; Kettle's this quarter is 5.01m yen, 80% band 2.03 to 8.00, which covers the 5.86 that closed.

Self-studyWhat it protects you from, and what it cannot see2 min▶

Protection. Because it never reads stage, an optimistic rep who drags deals up a stage cannot move it. Analyst session 6 tests exactly that.

Blind spot. For the same reason it cannot see a real late-stage signal either: a signed contract and a cold lead of the same age and origin look identical to it. It also assumes the next quarter behaves like the training period. And it cannot see past the longest observed cycle, 126 days in the training wins, the same limit Analyst session 3 found in the survival curve.

Real world

The training cohort is real. Leads Olist first contacted in January and February 2018, as seen on 1 June 2018: 2,169 leads, 268 won by then, a pooled 12.4%. Conversion c comes from those rows by origin, and any origin with fewer than 40 leads borrows the pooled 12.4% rather than trusting a handful of rows. F is the cycle-time distribution of those 268 observed wins. These are real Olist B2B funnel rows (Brazilian marketplace seller acquisition) standing in for Kettle's history; Olist has no stage history and no lost deals, which is exactly why this method never needed either.

Build-along

One quarter, three answers

★ 22 min · everyone builds

The widget draws this quarter's 300 open deals (real Olist leads first contacted March to May 2018 and not yet won on 1 June; Kettle's stages, amounts and reps constructed) and runs all three methods in your browser. One control switches the stage probabilities between Kettle's own table and HubSpot's default pipeline. Work the five steps, and copy what the widget prints.

WHAT THE WIDGET COMPUTES ON EVERY CHANGE Stage probabilities own or HubSpot default 300 open deals draw 0, this quarter three methods plus an 80% range each verdict pipeline, and what really closed range rows rep commit · stage-weighted · history-based stage table deals, own %, HubSpot % per stage Switching the control moves only the stage-weighted row. Rep commit and history-based never read it.
🔍 Click to zoom - the copper control is the one decision you make; the hatched pile is the same deals every time

Step 1 · Read the verdict. Before any method speaks, note the two facts it is judged against: the size of the pile and what came out of it.

★ Step 1 · what the widget printsPipeline 168.21m yen across 300 deals. What really closed: 5.86m yen, 7 deals.

Step 2 · Read the three range rows. With Kettle's own probabilities selected, copy each method's point, range and verdict.

★ Step 2 · three methods, one quarterRep commit 7.86m (7.86 to 24.36) · misses 5.86 Stage-weighted 8.60m (5.48 to 11.72) · covers 5.86 History-based 5.01m (2.03 to 8.00) · covers 5.86

Step 3 · Read the stage table. Half the pile sits at stage 1, where Kettle's own rate is zero. Compare the two probability columns row by row.

★ Step 3 · the stage table1 · Contacted 153 0.0% 20.0% 2 · Need confirmed 78 0.6% 40.0% 3 · Tasting trial 39 6.3% 60.0% 4 · Proposal 19 18.1% 80.0% 5 · Contract out 11 64.6% 90.0%

Step 4 · Switch to HubSpot default. Set "Stage probabilities" to the HubSpot default pipeline and read the stage-weighted row again. Notice which rows did not move.

★ Step 4 · stage-weighted on placeholdersStage-weighted 63.64m (55.56 to 71.72) · misses 5.86

Step 5 · Write stage-weighted against your own snapshot. A sketch, not a result: no output is claimed. Join the snapshot on the forecast date to your stage table and sum.

★ Step 5 · SQL sketchSELECT SUM(s.amount * p.win_rate) AS forecast, SQRT(SUM(s.amount * s.amount * p.win_rate * (1 - p.win_rate))) AS sd FROM pipeline_snapshot s JOIN stage_win_rate p ON p.stage = s.stage -- your own last quarter WHERE s.snapshot_date = DATE '2018-06-01' AND s.status = 'open'; -- 80% band: forecast +/- 1.2816 * sd (normal approximation)
★ Step 5 · the same in Python (pandas)df = snap.merge(rates, on="stage") # rates: stage, win_rate df["w"] = df["amount"] * df["win_rate"] point = df["w"].sum() sd = (df["amount"]**2 * df["win_rate"] * (1 - df["win_rate"])).sum() ** 0.5 low, high = point - 1.2816 * sd, point + 1.2816 * sd

One quarter is an anecdote. Tonight history-based came closest and rep commit missed its own range, but a single quarter has an error and no bias. Analyst session 5 replays the quarter 20 times with different 300-deal draws from the same real open pool and scores each method on bias, error and how often its range covers what closed.

Homework

Before Analyst session 5

30 min
  1. Build your own stage table. From the snapshot on the first day of last quarter, count deals per stage and how many won inside the quarter. Check that the rate rises with stage.
  2. Find your CRM's probabilities. Open your pipeline settings and note whether the stage probabilities are still the vendor's defaults. If they are, write down who would own replacing them.
  3. Run the Step 5 query against last quarter's snapshot with both ladders, and compare each to what really closed.
  4. Write each rep's commit rule in one line ("commits at Contract out", "commits when the buyer confirms budget"), and note whose rule differs from the rest.
Check yourself

Three questions

Clear all three and Analyst session 4 gets stamped in your passport.

1 · Ben commits deals at Proposal; Aiko commits at Contract out. Both use the same Commit category. What does that do to rep commit?

Forecast categories are a convention each rep applies privately. Ben's 3.37m committed against 0.91m closed shows what a lower line does.

2 · The same 300 deals give 8.60m on Kettle's own stage table and 63.64m on HubSpot's default pipeline. Why?

Stage-weighted is only as good as its probabilities. Kettle's own stage 1 rate is 0.0%; the default is 20%, on half the pile.

3 · A rep moves every deal up one stage before the forecast. Which method does not move at all?

That is its protection and its blind spot: it cannot be inflated by stage, and it cannot see a real late-stage signal either.

Sources covered

What this session teaches, and where it came from

✓Training cohort, conversion by origin and cycle-time distribution behind history-basedOlist, "Marketing Funnel by Olist", Kaggle dataset, CC BY-NC-SA 4.0. Read at source
✓HubSpot default stages and probabilities (20/40/60/80/90), weighted amount, forecast categoriesHubSpot Knowledge Base, "Set up and customize pipelines" and "Set up the forecast tool". Read at source, October 2026
◐Salesforce forecast categories Pipeline, Best Case, Commit, Closed, OmittedSalesforce Help, opportunity stages and forecast categories. Reported; no Salesforce percentages given
◐Kettle & Co's stages, reps, commit lines and amountsConstructed and labelled; every Kettle number on this page is printed by the widget or shown as checked arithmetic

Every fact on this page, and its verification tier, is recorded in the course's source map.

Analyst session 4 cheat sheet · pin this

Rep commitSum of Commit; range commit to commit + best case. Reads the rep's judgement.
Write the commit ruleOne category, many private thresholds. Publish the stage each rep commits at.
Stage-weightedSum of amount x stage probability. Reads the stage field.
Defaults are placeholdersBuild the table from your own last quarter; check it rises with stage.
History-basedp = c x (F(a+91) - F(a)) / (1 - c x F(a)). Reads origin and age, never stage.
Ranges, then replays80% band is a normal approximation. One quarter is an anecdote; score over many.