📌 Stage 1 · Foundations · Sales Pipeline CRM · Course Overview · 30–40 min

The whole customer lifecycle, no code required

Learning goals

  • Explain what a sales pipeline workbook must answer: what will close, what is stuck, and what is overdue
  • Name the 12 linked sheets and the lead-to-payment flow they form
  • State why this course deliberately ships zero macros or VBA

Concepts

One workbook, the whole customer lifecycle

The case is Northgate Coffee Roasters, a five-person wholesale roastery in Portland, Oregon. Between Jan 1, 2026 and Jun 30, 2026 they logged 12 inquiries, worked 12 opportunities worth $154,200 in total, quoted 8 deals, delivered 5 orders, invoiced $70,200, and collected $33,500 - and every one of those numbers lives in one linked workbook you will build lesson by lesson.

The lifecycle runs left to right: Companies and Contacts hold the master data; Leads captures every inquiry with its source; Opportunities turns qualified leads into weighted pipeline deals; Activities logs every call, email, and demo; Quotes become Orders, Orders become Invoices, and Payments settles them; the Dashboard reports win rate, sales cycle length, and a weighted forecast.

A pipeline workbook exists to answer three questions an owner actually asks: what should close next (Opportunities), what have I not touched in too long (Activities), and what does the future look like in dollars (Dashboard weighted forecast).

=COUNTIF(Opportunities!$G$5:$G$1004,"Won")

Count every row on the Opportunities sheet whose Stage column reads Won. In this case: 5 deals closed won between January and June 2026. You will write bigger versions of this on the Dashboard in Stage 6.

Why no macros - the advantage stated plainly

Plenty of Excel CRM courses teach VBA user forms and event macros. This one teaches none of it, on purpose. A formula-only workbook opens without a security prompt in desktop Excel, Excel for the web, Google Sheets, and LibreOffice Calc - nothing to enable, nothing for IT to block. Every behavior is visible in the formula bar, so you can audit (and fix) the model yourself instead of debugging code. Formulas recalculate the instant data changes, and the file can never carry a macro virus.

Everything a small-business CRM needs - dropdowns, red overdue cells, lookups, a KPI dashboard - is covered by three native features: data validation, conditional formatting, and classic formulas. If a task seems to demand a macro, there is almost always a combination of those three that does the job, and this course shows the pattern for each one.

💡 Tips:

  • This workbook intentionally contains no macros; the downloads are plain .xlsx files.
  • Classic formulas (VLOOKUP, SUMIFS, COUNTIFS, INDEX-MATCH) have worked identically in every Excel since 2010 - no version worries, no compatibility mode.

A case frozen in time - on purpose

Every date test in this course runs against a fixed aging date: Jun 30, 2026, held in Settings!C6. Nothing uses TODAY() or NOW(), so the workbook produces exactly the same numbers today, next month, or next year - which is what makes the check figures checkable. As of that date Northgate has 6 open deals worth $74,200, a weighted forecast of $31,215, an 83% win rate, and exactly 2 overdue follow-ups. Those four numbers are your tour guide for the whole course.

💡 Tips:

  • In your own workbook you may later point the aging date at a live date - but keep it in ONE cell (Settings!C6) so every formula reads the same clock.

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). This tour lesson opens on the Read Me sheet; every tab below it stays reachable through the bottom tab bar.

  1. Read the ground rules — The exercise opens on Read Me. Skim the nine topics: what the file is, the lifecycle, the workflow, entry rules, stage weights, the sales-tax note, capacity, the hand-off note, and boundaries.
  2. Walk the tabs in lifecycle order — Click through the bottom tabs in this order: Settings, Companies, Contacts, Leads, Opportunities, Activities, Quotes, Orders, Invoices, Payments, Dashboard. Say one sentence about each: what enters here, what leaves.
  3. Find the four headline numbers — On Dashboard confirm: Open deals = 6, Open pipeline value = $74,200.00, Weighted forecast = $31,215.00, Win rate = 83%. You will rebuild all of these yourself by Lesson 20.
  4. Note the color language — Pale-green cells are formulas - never type over them. White cells are typed entries. That single convention runs through every sheet and every lesson.

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:

  • ☐ Read the Read Me topics and toured all 12 tabs in lifecycle order
  • ☐ Located the four headline numbers on Dashboard: 6 open deals, $74,200.00 open pipeline, $31,215.00 weighted forecast, 83% win rate
  • ☐ Can state the no-macro advantage in one sentence: opens anywhere, auditable, nothing to enable