A hub for the Excel guides on this site: how to calculate customer concentration, dormant customers, account coverage, win rate and pipeline coverage, forecast accuracy and bias, net revenue retention, renewal rate, quote conversion, OTIF and cost to serve in a spreadsheet, from exports a business already has. Each guide gives the columns needed, the exact formulas for Microsoft 365 and older Excel, the one check that proves the result, the usual spreadsheet mistakes, and the point at which the spreadsheet stops being enough.
Ten measures, each computable in a spreadsheet from an export the business already has. Every guide follows the same order: the columns you need, the formulas step by step, the pivot alternative, the check, the usual mistakes, and where the spreadsheet stops.
| Measure | Export needed | Key functions | Guide |
|---|---|---|---|
| Customer concentration | Invoice ledger | SUMIFS, LARGE, MAX | Customer concentration in Excel |
| Dormant customers | Order history | MAXIFS, MINIFS, SUMIFS | Dormant customers in Excel |
| Account coverage | CRM activities, account list | MAXIFS, VLOOKUP, COUNTIFS | Account coverage in Excel |
| Win rate and pipeline coverage | CRM opportunities | COUNTIFS, SUMIFS | Win rate and pipeline coverage in Excel |
| Forecast accuracy and bias | Weekly forecast snapshots, actuals | MAXIFS, AVERAGEIFS, ABS | Forecast accuracy and bias in Excel |
| Net revenue retention | Billing by customer | SUMIFS, MIN, IF | Net revenue retention in Excel |
| Renewal rate | Contract terms | MINIFS, COUNTIFS, SUMIFS | Renewal rate in Excel |
| Quote conversion | Quote log, order file | COUNTIFS with date windows | Quote conversion in Excel |
| OTIF | Order and delivery lines | AND, COUNTIFS | OTIF in Excel |
| Cost to serve | Deliveries, order lines, visits, returns, ledger | COUNTIFS, SUMPRODUCT | Cost to serve in Excel |
| Analysis | Export needed | Guide |
|---|---|---|
| RFM scoring, adjusted for B2B | Order history | RFM analysis for B2B customers |
| ABC analysis and Pareto tiers | Invoice ledger | ABC analysis of customers in Excel |
| Cross-sell matrix | Invoices with category; customer segments | Cross-sell matrix in Excel |
| Cohort retention table | Invoice ledger | Cohort retention table in Excel |
| Seasonality index | Two or more years of sales | Seasonality index in Excel |
Share of wallet needs an estimate of the customer's total spend as well as your own ledger, so it has its own page: how to calculate share of wallet, with a worked example.
Parameters in cells, never in formulas. Period dates, tolerances, thresholds and multiples sit in labelled cells. Changing one changes the whole workbook, and a reader can see what was assumed.
A fixed as-of date. TODAY() makes every list unreproducible. Type the date.
Identifiers, not names. Joins between a CRM export and a ledger fail on "Acme Ltd" against "ACME Limited". Use the account number in both, or build the mapping sheet first.
Count and value. Every rate is computed both ways. Where they differ is usually the finding.
One check that must be zero. Parts against the whole: customer totals against the ledger, states against the row count, allocated cost against the pools.
| Measure | The check |
|---|---|
| Concentration | Sum of customer totals − ledger total for the period = 0 |
| Dormancy | Dormant + active + too few orders − customers = 0 |
| Coverage | Covered + overdue + never touched − assigned accounts = 0 |
| Pipeline | Won + lost + open − deals = 0 |
| Forecast | Every actual finds a snapshot; no missing weeks |
| NRR | Prior + expansion − contraction − churn − current from prior customers = 0 |
| Renewal | Due − renewed − late − lost − won back − pending = 0 |
| Quote conversion | Exact + probable + open + lost − quotes = 0 |
| OTIF | OTIF + late only + short only + both + not due − lines = 0 |
| Cost to serve | Allocated cost − sum of pools = 0 |
The five checks before trusting a spreadsheet total cover the general version.
Dates stored as text. SUMIFS and MAXIFS skip them without complaint. Test with ISNUMBER.
Ranges that stop short. The export grew; the formula did not.
Whole-column formulas on large files. A COUNTIFS across a million rows, filled down fifty thousand times, calculates for minutes. Limit the ranges or merge in Power Query.
The file that only one person understands. The measure stops when they are on holiday.
For what each number should be once computed, the benchmark pages give the ranges and what they assume. For the same arithmetic on ten rows by hand, see the worked examples. For when to stop, the four signs a spreadsheet is no longer enough. Covirage computes all ten from the same exports, with the checks built in and the definitions stated on every table; the Excel analysis page covers how uploaded workbooks are read.
Any version from 2019 has SUMIFS, COUNTIFS, MAXIFS and MINIFS, which cover nine tenths of these guides. Microsoft 365 adds UNIQUE, FILTER, SORT and XLOOKUP, which make the customer lists and ranked tables dynamic. Each guide gives the older alternative: Remove Duplicates, a pivot table, or an array formula.
Because a spreadsheet fails silently. A date stored as text, a customer missing from the list, a range that stops at row 5,000: none of them produce an error, and all of them produce a wrong number. One formula that compares the parts to the whole, and must be zero, catches most of them.
When the same workbook is rebuilt every week by one person, when two people get different answers from the same export, when the measure is wanted per rep and per customer as well as in total, or when nobody can say which version of the file the board number came from. The four-signs page covers each.