📌 Stage 3 · Pipeline & Forecast · Sales Pipeline CRM · Stage Weights · 30–40 min

One VLOOKUP prices the deal's odds

Learning goals

  • Explain the weighted pipeline idea: value alone overstates, weights discount
  • Pull each deal's weight from the Settings stage table with VLOOKUP
  • Tune weights from your own win history instead of copying someone else's

Concepts

Why weight a pipeline at all

The open pipeline says $74,200. Nobody should book that number. Of the six open deals, the $22,000 school pilot has cleared exactly one meeting, while the $18,500 hotel renewal is in pricing negotiations - those two dollars are not equally real. Weighting multiplies each deal's value by its stage's likelihood-to-close, producing a forecast you can defend in front of a bank or a board: $31,215, not $74,200.

The mechanism is deliberately boring: the stage name in column G is looked up in the Settings stage table, and the weight (10%, 25%, 50%, 75%) comes back. Nothing else in the workbook encodes odds - change one cell in Settings and the whole forecast re-prices.

=VLOOKUP($G5,Settings!$B$10:$C$15,2,FALSE)

Search for the stage name from G5 (here: Won) in Settings B10:B15 and return the matching weight from column C: 1.00, displayed as 100%. FALSE = exact match, so a stage not in the table fails with #N/A instead of guessing.

💡 Tips:

  • The stage dropdown on this sheet lists exactly the six names in Settings B10:B15 - the lookup can never miss while the dropdown is respected.

Weights are history, not hope

Northgate's weights (10 / 25 / 50 / 75) look like folklore until you check them against results, which is exactly what a small business should do quarterly: of the deals that ever reached Proposal, how many eventually closed? If it is 3 in 10, Proposal deserves 30%, and the forecast was lying to you by 20 points per deal.

Won at 100% and Lost at 0% anchor the scale: a closed deal is fully real, a dead deal is fully gone. The middle four are estimates, and the only wrong way to set them is to never revisit them. Keep them in Settings rows 10-15, revisit quarterly, and let every weighted number downstream follow.

💡 Tips:

  • Weighted forecasts err low by design - they count a deal at 75% even the day before signature. Bankers call that conservatism; owners call it sleep.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Opportunities.

  1. See the weights column — Column I shows each deal's stage weight as a percentage. I5 is blank - you will type it. Column G on each row is the lookup key.
  2. Type the weight lookup — Click I5 and type =VLOOKUP($G5,Settings!$B$10:$C$15,2,FALSE) then Enter. OPP-101 is Won, so I5 returns 100%.
  3. Check both ends of the table — I11 (OPP-107, Negotiation) shows 75% - the deal closest to signing. I16 (OPP-112, Prospecting) shows 10% - a cold crew-coffee route idea. Same formula, different odds.
  4. Read the Settings table once more — Flip to Settings rows 10-15 and confirm the six weights: 10, 25, 50, 75, 100, 0 percent. Every weighted dollar in the workbook flows from these six cells.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ I5 returned 100% (Won), I11 75% (Negotiation), I16 10% (Prospecting)
  • ☐ Located the source table at Settings!B10:C15 and can explain the FALSE argument
  • ☐ Can explain why the $74,200 raw pipeline overstates and what the weights will do about it