Customer Concentration Tracker Spreadsheet
This spreadsheet ranks every customer by what is left after their own costs come off, rather than by what they invoice.
Everybody in the building knows which account is the biggest, and almost nobody knows which one is worth the most, because the three things that separate them — the terms, the unbilled hours and the days they take to pay — are never written down in the same place. This file writes them down and reorders the ledger.
What is in it
- Sales — one row per order: the date, who it was for, what you invoiced, what the goods cost, the extra hours it took, what those hours went on and how long they took to pay.
- By Customer — every account side by side, from what it invoices down to what is really left once the hours and the waiting come off.
- Cost To Serve — the hours nobody invoices, ranked by hours per thousand dollars invoiced, which is the only fair way to compare a big account with a small one.
- How Concentrated — the whole ledger scored on one number, then scored again on what each account leaves rather than what it sends.
- If They Left — for a loss of any size: the money that goes, the new accounts needed to replace it, what winning them costs and how long it takes at the rate you actually win accounts.
- Setup and Overview — seven numbers, two short lists you name yourself, and the whole year on one screen.
Who it is for
Anywhere a handful of customers make up most of the money: an agency, a haulier, a printer, a contractor, a consultancy, a wholesaler, a food producer. With four hundred customers, run it on the twenty that matter and put the rest in one row called everybody else — concentration is a question about the top of the list.
The worked example
In the worked year, fifteen accounts invoiced $1,886,476. The biggest of them was 35.2% of the money coming in but only 20.7% of the money left, and it owned 51.8% of the rework.
The second biggest account invoiced $244,385 and the third $172,689 — 41% less — yet once the unbilled hours and the waiting came off they were worth $49,091 and $47,678, three per cent apart.
How it works
Compatible with Microsoft Excel 2016 or newer on Windows or Mac, Google Sheets, and LibreOffice. No macros, no add-ons, no account to create, nothing to install.
The download has two workbooks: one with the example above already filled in, so you can see what every column expects, and the same file completely blank for your own figures. A plain-English guide comes as a PDF. Buy it on Etsy — $9
Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the buyer protection are all handled by Etsy.
A look inside the file
Questions people actually ask
How do I work out what my biggest customer is really worth?
You take its gross profit and then take off the hours nobody invoices and the cost of the days it pays late. In the worked year the biggest account is 35.2% of the money in but only 20.7% of the money left, keeping 12.8 cents in every dollar against 34.4 cents for the smallest account on the list. The If They Left tab then works out what replacing it would take: losing a third of the sales needs fourteen new accounts, or the next three years.
We have four hundred customers, not fifteen. Does that break it?
No, run it on the twenty that matter and put the rest in one row called Everybody else, because concentration is a question about the top of the list, not the bottom. It works anywhere a handful of customers make up most of the money: an agency, a haulier, a printer, a contractor, a consultancy, a food producer.
We do not record the extra hours anywhere, and our margin varies by product.
An honest half-hour estimate per order, put in as you go, beats a precise number that does not exist, and it only has to be consistent between accounts to do its job. A mixed margin is fine because the file takes the cost of the goods per order rather than one margin for everything. Everything else comes off your sales ledger: the date, who it was for, what you invoiced, what the goods cost and how long they took to pay.
Do I need Excel, or does it work in Google Sheets?
Both, and LibreOffice too. It is built as .xlsx for Excel 2016 or newer on Windows or Mac, and imports into Google Sheets through File then Import then Upload. No macros, no add-ons, no account to create, nothing to install and no subscription.
Does it hold personal data?
No. It holds accounts, not people, and there are no names of individuals anywhere in it. It is not a CRM, a sales ledger or an accounts package, it connects to nothing, and it does not know your contracts, your credit insurance or your customers' payment history. It will not stop your biggest account phoning; it tells you beforehand what they were actually worth and what filling the hole would take.
Need it built around your business?
This file assumes a ledger of accounts that order repeatedly through the year, with hours you can put a rate against. If yours looks different — a consultancy on a handful of annual retainers, a haulier whose concentration is a customer’s depot rather than the customer, a subcontractor whose largest account is also the one that holds retention — I can build the same arithmetic around how your work actually arrives.
All other templates are listed on the Excel & Google Sheets Templates page.