Level 2Level 2 ProjectsProject, 10 min
A sales pipeline tracker
The task
Eight open and recently closed deals. Turn the list into a tracker sales leadership can forecast from: a probability for each stage, a weighted value, a close quarter, and the two numbers everyone asks about.
- Probability by stageIn F2, give every deal a win probability from its stage with
IFS: Closed Won 100%, Negotiation 70%, Proposal 40%, Discovery 10%, anything else 0%. Fill the column:=ARRAYFORMULA(IFS(C2:C9="Closed Won", 1, C2:C9="Negotiation", 0.7, C2:C9="Proposal", 0.4, C2:C9="Discovery", 0.1, TRUE, 0)) - Weighted valueIn G2, multiply each deal's Amount by its Probability, for all eight rows.
- Close quarterIn H2, label every deal with its close quarter, like 2026-Q3, from the Close date in column E.
- Weighted forecastIn K2, add up the weighted value of every deal closing in the quarter named in K1, with
SUMIFS. - Count open dealsIn K3, count the deals that are still open: the stage is not Closed Won and not Closed Lost.
"<>"means not equal to.
The data
The starting sheet. Cells outlined in green are where your formulas go.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Deal | Owner | Stage | Amount | Close date | Probability | Weighted | Quarter | Forecast quarter | 2026-Q3 | |
| 2 | Northwind expansion | Maya | Negotiation | $24,000 | Aug 14, 2026 | Weighted forecast | |||||
| 3 | Bean There Coffee POS | Theo | Proposal | $8,500 | Sep 30, 2026 | Open deals | |||||
| 4 | Cobalt Labs renewal | Priya | Closed Won | $31,000 | Jul 22, 2026 | ||||||
| 5 | Knead to Know Bakery | Jonah | Discovery | $6,000 | Oct 15, 2026 | ||||||
| 6 | Fieldstone pilot | Lena | Closed Lost | $12,000 | Jul 9, 2026 | ||||||
| 7 | Sir Loin Steakhouse | Maya | Proposal | $15,500 | Nov 20, 2026 | ||||||
| 8 | Harbor & Co upsell | Theo | Negotiation | $18,750 | Sep 3, 2026 | ||||||
| 9 | Quillworks rollout | Priya | Discovery | $9,200 | Dec 1, 2026 |