📌 Stage 1 · Foundations · Sales Pipeline CRM · Model Parameters · 30–40 min

One place for dates, terms, and weights

Learning goals

  • Centralize model parameters so one edit re-prices the whole workbook
  • Read the pipeline stage table (Settings rows 10-15) and explain what each weight means
  • Explain why the workbook pins a fixed aging date instead of using TODAY()

Concepts

Parameters live in one place

Rows 5 through 9 of Settings hold every assumption the rest of the workbook shares: the period start (Jan 1, 2026), the period end and aging date (Jun 30, 2026), the quote validity window (30 days), the credit terms (NET 30), and the currency note. No other sheet hard-codes these numbers - they reference Settings, so changing the quote validity to 14 days re-dates every quote expiry in one stroke.

=$C5+Settings!$C$7

The quote expiry formula on the Quotes sheet (column H): quote date in C5 plus the validity window in Settings!C7. Change 30 to 14 in Settings and every expiry follows - no hunting through formulas.

The stage table - the heart of the forecast

Rows 10 through 15 define the six pipeline stages and the weight each one carries: Prospecting 10%, Qualified 25%, Proposal 50%, Negotiation 75%, Won 100%, Lost 0%. A weight is your honest estimate of how likely a deal at that stage is to close - early lists mostly die, late negotiations mostly land. Multiply each open deal's value by its stage weight and the sum is a forecast you can defend, instead of the fantasy number a raw pipeline total gives you.

The weights are judgment calls. The principled way to set them is your own history: if 3 of 10 proposals eventually close, Proposal deserves roughly 30%, not 50%. Keep them in this table so one edit re-forecasts the whole workbook.

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

Look up the Negotiation stage in the two-column stage table (names in B, weights in C) and return the second column: 0.75, displayed as 75%. FALSE means exact match only - a mistyped stage name must fail loudly, not guess.

💡 Tips:

  • Won at 100% and Lost at 0% are not arbitrary: a won deal counts at full value in reports, a lost one contributes nothing to any forecast.

Never TODAY() in a teaching model

TODAY() and NOW() are volatile functions: they recompute every time the file opens, so every overdue flag and every age would drift day by day. This workbook instead compares against the fixed date in Settings!C6 (Jun 30, 2026). The payoff is that every number in the course - the 2 overdue follow-ups, the 77-day-old OPP-107, the $15,500 overdue receivable - is stable and checkable forever. When you adapt the workbook for real use, the cleanest compromise is still one cell: point C6 at the month-end you are reporting and leave the formulas alone.

💡 Tips:

  • Sheet references with a space in the name need quotes: 'Read Me'!B2. Names without spaces, like Settings, reference bare: Settings!$C$6.

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 Settings.

  1. Read the parameter block — Rows 5-9: period start Jan 1, 2026; period end Jun 30, 2026; quote validity 30 days; NET 30 terms; USD. Notice cell C7 is blank - you will restore it.
  2. Re-type the quote validity — Click C7 (blank) and type 30, then Enter. That single cell feeds the Expiry column on Quotes.
  3. Verify the parameter at work — Switch to the Quotes tab: H5 (QT-3001, dated Mar 18, 2026) shows expiry Apr 17, 2026 - exactly 30 days later. That is the formula =$C5+Settings!$C$7 doing its job.
  4. Memorize the stage table — Back on Settings, read rows 10-15 aloud: Prospecting 10%, Qualified 25%, Proposal 50%, Negotiation 75%, Won 100%, Lost 0%. Every weighted number in the course comes 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:

  • ☐ Typed 30 into Settings!C7 and verified QT-3001 expires Apr 17, 2026
  • ☐ Can recite the six stages and weights, including Negotiation at 75%
  • ☐ Can explain why the workbook ages against Settings!C6 (Jun 30, 2026) and never TODAY()