📌 Stage 6 · Invoicing & Dashboard · Sales Pipeline CRM · KPI Dashboard · 40–50 min

The last four formulas, and the workbook is yours

Learning goals

  • Compute win rate from the dashboard's own cells, guarded by IFERROR
  • Average the won sales cycle with AVERAGEIFS
  • Assemble the complete KPI picture and take delivery of the workbook

Concepts

Win rate: won over decided

Win rate answers 'when we compete, how often do we win?' - so the denominator is decided deals (Won + Lost), never all deals. Open deals are ungraded exams; including them would flatter nobody. Northgate won 5 of 6 decided deals for an 83% rate, with the single loss documented to the dollar on the register.

=IFERROR($C$8/($C$8+$C$10),0)

Divide won deals (C8 = 5) by won plus lost (C8 + C10 = 6): 0.8333, displayed as 83%. The dashboard reuses its own rows instead of re-counting the register. IFERROR guards the brand-new-workbook case where zero deals exist and the division would return #DIV/0!.

💡 Tips:

  • 83% on six decided deals is a strong quarter AND a small sample - quote it with the denominator, always. 5 of 6, not '83%'.

Sales cycle with AVERAGEIFS

The average won cycle turns Lesson 10's Days Open column into a promise you can make prospects: Northgate wins deals in about 55 days (55.2 exactly - 276 days across five deals). AVERAGEIFS averages one column where a criterion holds elsewhere: average Days Open where Stage is Won. Lost deals are excluded on purpose - a loss can drag on for months and says nothing about your delivery speed once a customer says yes.

=IFERROR(AVERAGEIFS(Opportunities!$M$5:$M$1004,Opportunities!$G$5:$G$1004,"Won"),0)

Average column M (Days Open) over rows where column G reads Won: (70 + 37 + 98 + 47 + 24) / 5 = 55.2. IFERROR again guards the empty workbook - no won deals means no average, and #DIV/0! would look like a broken model instead of an honest zero.

💡 Tips:

  • Both guard formulas return 0 in the blank template - open the downloadable template and the dashboard reads cleanly from day one.

The cash trio, and where you go next

The dashboard closes with the money rows you verified in Lesson 18: $70,200 invoiced (equal to won value - every won deal was invoiced in full), $33,500 collected, $36,700 open, $15,500 overdue - plus the 2 overdue follow-ups from Lesson 12. That is the complete customer lifecycle on one screen: 6 open deals worth $74,200, a defensible $31,215 forecast, an 83% win rate, a 55-day cycle, and cash tracked to the dollar.

Two next steps. First, take the downloads below: the case workbook to study and the blank template with formula columns pre-filled to row 204, dropdowns, and conditional formatting - replace the sample data with your own companies and go. Second, when open invoices need serious collections work (aging buckets, statements, dunning), that is the companion accounts-receivable course, which starts exactly where this ledger ends. Everything you built here - twelve sheets, zero macros - opens anywhere, audits clean, and never asks anyone to enable anything.

💡 Tips:

  • Modern Excel callout: =XLOOKUP and =FILTER would shorten some lookups in Excel 365 - this course kept every formula classic (2010-safe) so the workbook runs on any machine a customer or accountant hands you.

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 Dashboard - the final exercise of the course.

  1. Type the win rate — C11 is blank. Click it and type =IFERROR($C$8/($C$8+$C$10),0) then Enter. It returns 83% - five won of six decided.
  2. Read the cycle and the cash — C12 shows 55.2 days; C13-C16 show $70,200 invoiced, $33,500 collected, $36,700 open, $15,500 overdue; C17 counts the 2 overdue follow-ups. Every row now traces to a lesson you completed.
  3. Reconcile the two big equals — C9 (won value $70,200) equals C13 (invoiced $70,200): every won deal was invoiced in full. When those diverge in your own workbook, investigate immediately.
  4. Take delivery — Use the download card below: the case workbook to study every live formula, and the blank template for your own pipeline. Both are plain .xlsx - no macros, no security prompt, nothing to enable.

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.

表格加载中…

Practice files: sales-pipeline-crm-case.xlsx, sales-pipeline-crm-template.xlsx (see the attachments section at the bottom of this page).

Checklist

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

  • ☐ C11 returned 83% (5 won of 6 decided) and C12 shows the 55.2-day average won cycle
  • ☐ Confirmed the final figures: $70,200 invoiced, $33,500 collected, $36,700 open, $15,500 overdue, 2 overdue follow-ups
  • ☐ Downloaded the macro-free case and template workbooks and can name where the AR course picks up