Executive KPI Scorecards, Rolling Windows & Cohort Retention

Who this is for

Stage 6: Operational & Financial Reporting Packs — Designed for learners mastering selector-driven kpi cards & dynamic rolling 3m/6m windows, nrr bridges, saas magic number, utilization & clamped sla indexes, cohort retention triangles, scenario switchboards & multi-currency fx, payroll ramps, top-5 concentration, audit banners & multi-tab rollups through interactive spreadsheet examples.

What you will practise

• Pull selected-period Revenue in F3, Gross Margin % in F4, and first-month-guarded MoM Revenue Growth % in F5.
• Compute guarded Rolling 3-Month Revenue in E3 (0 if pos < 3) and guarded Rolling 6-Month Revenue in E4 (0 if pos < 6) anchored to E2.
• Compute Ending Cohort ARR in F2:F4, Segment NRR % in G2:G4, and ARR-weighted Portfolio NRR % in G5.
• Compute SaaS Magic Number in E2:E4 and Gross-Margin-Adjusted Payback Months (guarded for zero denominator) in F2:F4.
• Calculate Utilization % in F2:F5 and Fee Realization % in G2:G5, both rounded to 4 decimals.
• Compute clamped 0–100 normalized SLA scores in F2:F5 and the weighted composite SLA index rounded to 2 decimals in F6.
• Populate the retention % triangle in G2:I4 (blank when source cell is "") and compute cohort-weighted Month-2 retention in H5.
• Pull the active scenario assumptions into E5:E7 locking $B$2, then compute Projected EBITDA in E2.

Your learning path

1. Cell Equals, Count, or Sum One of Many Values (Allowlist COUNTIF + SUMPRODUCT)

   Replace brittle 10-way OR chains with a range-driven COUNTIF allowlist flag to test, count, and sum rows matching any value in an editable lookup list.

2. Lookup Hourly Rates & Sum Total Invoice Billing (XLOOKUP & SUMPRODUCT)

   Pull role-based hourly billing rates from a master rate card with XLOOKUP and roll up total project invoice value via SUMPRODUCT(Hours, Looked_Up_Rates).

3. Lookup the date or name of a MAX or MIN value: first and last matching rows

   Return the label associated with a maximum or minimum, and choose the first or last matching row when values tie.

4. Position & Exact Cell Address of Max or Min Value (XMATCH & ADDRESS)

   Find both the relative list position (1..N) and exact worksheet cell address (such as "B4") of the maximum or minimum value in a column using XMATCH(MAX(range), range, 0) and ADDRESS.

5. SUMIF & COUNTIF when cells contain a literal asterisk (*) or question mark (?)

   Escape wildcard characters with a tilde ("*~**") so SUMIF and COUNTIF match literal asterisks, question marks, or tildes instead of every text row.

6. Check If a Value Exists in a Range (COUNTIF & XMATCH Membership Test)

   Check a nonblank ID against an approved list, distinguish literal matching from COUNTIF criteria, and test whether either of two watch-list IDs is present.

7. COUNTIF & SUMIF Across Non-Contiguous Ranges (Additive & VSTACK)

   Count matching rows and sum quantities from two separate blocks, with literal criteria and a dynamic-array alternative.

8. Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)

   Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.