📌 Stage 3 · Pipeline & Forecast · Sales Pipeline CRM · Opportunity Register · 35–45 min

Every live deal on one grid

Learning goals

  • Structure an opportunity register: one row per deal, typed facts left and computed facts right
  • Fill the Company column with a VLOOKUP on the company code
  • Tell open deals from closed ones and keep the stage census honest

Concepts

From lead to deal

An opportunity is a deal with a dollar value and a stage - the moment a lead stops being a conversation and becomes a possible contract. The register holds twelve of them, OPP-101 through OPP-112, created between Jan 9 and Jun 24, 2026. Typed columns carry the facts (code, deal name, created date, stage, value, expected close); formula columns carry the derivations (company, weight, weighted value, days open).

One rule keeps the register honest: a deal gets exactly one row for its whole life. You never add a row because a deal moved forward - you edit the Stage cell. The stage column is the single switch that drives weights, forecast, win rate, and every dashboard number.

=VLOOKUP($C7,Companies!$B$5:$C$104,2,FALSE)

The register stores only the company CODE in C7 (CO-05); this formula borrows the company NAME from the master sheet. Exact match (FALSE) so a typo'd code shows #N/A instead of a plausible wrong company.

Open versus closed

Four of the six stages are open (Prospecting, Qualified, Proposal, Negotiation); two are terminal (Won, Lost). Terminal rows stop aging - their Close Date is filled and their Days Open freezes at close minus created. The census for this case: 1 Prospecting, 2 Qualified, 2 Proposal, 1 Negotiation, 5 Won, 1 Lost - six open deals carrying $74,200, five closed-won worth $70,200, one closed-lost worth $9,800.

Expected Close (what you promised yourself) versus Close Date (what happened) is a quiet little honesty machine: OPP-106 was expected Jul 10 and closed Jun 19 - three weeks early after a tasting demo; OPP-104 was expected Apr 24 and died Apr 30 after a price fight.

💡 Tips:

  • Lost deals stay in the register forever. Deleting them corrupts your win rate - the denominator is part of the truth.

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. Read the thirteen columns — From B: Opp #, Co Code, Company, Deal Name, Created, Stage, Value, Weight, Weighted Value, Expected Close, Close Date, Days Open, Notes. Only some are typed - spot the pale green.
  2. Type the company lookup — D7 is blank. Click it and type =VLOOKUP($C7,Companies!$B$5:$C$104,2,FALSE) then Enter. It returns Cedar Hollow Hotel Group - the account behind OPP-103, the hotel lobby build-out.
  3. Take the stage census — Scan column G and tally: OPP-112 Prospecting; OPP-110 and OPP-111 Qualified; OPP-108 and OPP-109 Proposal; OPP-107 Negotiation; OPP-101, 102, 103, 105, 106 Won; OPP-104 Lost. That is 6 open, 5 won, 1 lost.
  4. Verify the open value by hand — Add the Value column for the six open rows: 18500 + 9600 + 7300 + 11400 + 22000 + 5400 = 74,200. That is the open pipeline value the Dashboard will compute in Lesson 19.

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:

  • ☐ D7 returned Cedar Hollow Hotel Group from code CO-05
  • ☐ Stage census confirmed: 6 open deals, 5 Won, 1 Lost across 12 rows
  • ☐ Hand-added the open pipeline value to $74,200 and can define what makes a deal open