Customer Concentration Tracker Spreadsheet | Key Account Profitability Excel Google Sheets Template | Cost to Serve Analysis

📊 CUSTOMER CONCENTRATION TRACKER — EXCEL & GOOGLE SHEETS

Everybody in the building knows which account is the biggest.

Almost nobody knows which account is worth the most, because the three things
that separate the two are never written down anywhere.

This file writes them down, and then puts every account in the order that
actually matters.

---

EIGHT LINKED TABS

· Start Here — what each tab expects and where to type.
· Setup — seven numbers, and two short lists you name yourself.
· Sales — one row per order. Seven things typed, the rest worked out.
· By Customer — every account side by side, down to what is really left.
· How Concentrated — how much of the business sits on how few accounts.
· Cost To Serve — the hours nobody invoices, and who they went on.
· If They Left — the tab this file exists for.
· Overview — the whole year on one screen.

---

FROM THE WORKED YEAR INSIDE THE FILE

Fifteen accounts. 897 orders.

Everything invoiced $1,886,476
Gross profit $478,729
The hours nobody invoiced $41,843
What the waiting cost $26,321
LEFT AFTER EVERYTHING $410,565
Which is this much of every dollar 21.8%

The biggest account, share of the money in 35.2%
The biggest account, share of the money left 20.7%

---

35.2%. AND 20.7%.

The same account. The first number is what it sends you. The second is what it
leaves behind once its own costs have come off.

It keeps 12.8 cents in every dollar. The smallest account on the list keeps
34.4. Same trade, same year, same building.

---

THREE THINGS SEPARATE THEM, AND THE FILE PRICES ALL THREE

Big accounts negotiate. The biggest one bought its size with your margin and
sits on the worst terms in the building, and nobody wants to be the person who
reopens them.

Big accounts ask for more. Short-notice runs, artwork changes, split
deliveries, chasing the payment, rework. In the worked year that came to 1,008
hours and $41,843, none of it on an invoice and all of it on a wage slip. One
account owned 51.8% of the rework and 48.8% of the chasing while being 35.2% of
the sales.

Big accounts pay when they like. 45 days agreed, 77 days taken. Priced at what
money costs you, that gap alone is worth more than four of the fifteen accounts
leave in total.

---

THE TAB THIS FILE EXISTS FOR

Losing an account is not the hole. Filling it is the hole.

If They Left works out, for a loss of any size, the money that leaves, the new
accounts it would take to replace it, what winning them costs, what they make
in their first year on the thinner margin new accounts arrive on, and how long
that takes at the rate you actually win accounts.

Losing a third of the sales in the worked year takes fourteen new accounts, or
the next three years.

And it does all of that before you have typed a single row, because it runs on
the seven numbers in Setup.

---

14.4 TO REPLACE THE REVENUE. 7.8 TO REPLACE THE MONEY.

Underneath, the same arithmetic runs for every account you have, in two ways.

Where the second number is smaller than the first, that account was carrying
less than its size suggested. For the good accounts it runs the other way — the
profit is harder to replace than the revenue — and that contrast is the whole
argument of the file in one column.

---

ONE NUMBER FOR THE WHOLE LEDGER

Square every account's share of the sales, add them up, multiply by ten
thousand. Competition regulators use it to decide whether a market is too
concentrated. It works just as well on a sales ledger.

Over 2,500 and one account is the business. The worked year scores 1,698.

Then the file scores the same ledger again on what each account leaves rather
than what it sends. That one came back 1,058, which is the one genuinely good
piece of news in the whole workbook.

---

THE SECOND AND THIRD BIGGEST ACCOUNTS

One invoices $244,385. The other invoices $172,689 — 41% less.

After the unpaid hours and sixty-one days of waiting, they are worth $49,091
and $47,678. Three per cent apart.

Nobody in that business knew, because nobody had ever put the two columns next
to each other.

---

SMALL DOES NOT MEAN CHEAP

Cost To Serve ranks every account by hours per thousand dollars invoiced, which
is the only fair way to compare a big account with a small one.

In the worked year the most demanding account on the whole ledger is 1.5% of
the sales. It is almost never the account people would have named.

---

WHAT YOU ACTUALLY TYPE

Seven things per row: 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.

Every rate and every payment term comes from Setup, so changing one reprices
the whole year. Every row works out the gross profit, the cost of the hours,
the days it went over the terms you agreed and what that waiting cost — on the
row, where you can see it.

---

WHAT YOU GET

Two workbooks and a guide. One workbook has the year above already filled in,
so you can see what every column expects before you touch anything. The second
is the same file completely blank — same eight tabs, same formulas — ready for
your own ledger from the first row. The guide is a plain-English PDF.

---

EXCEL AND GOOGLE SHEETS

Built as .xlsx. Excel 2016 or newer on Windows or Mac, and Google Sheets
through File → Import → Upload. LibreOffice too. No macros, no add-ons, no
account to create, nothing to install, no subscription. You download a file and
it is yours.

---

QUESTIONS PEOPLE ACTUALLY ASK

We have four hundred customers, not fifteen.
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, not the bottom.

We do not record the extra hours anywhere.
Nobody does. An honest half-hour estimate per order, put in as you go, beats a
precise number that does not exist. It only has to be consistent between
accounts to do its job.

Our margin varies a lot by product.
That is fine — the file takes the cost of the goods per order rather than one
margin for everything, so a mixed ledger works properly.

Is this only for wholesale or manufacturing?
No. 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.

Does it hold personal data?
No. Accounts, not people. There are no names of individuals anywhere in it.

---

WHAT THIS IS NOT

Not a CRM, a sales ledger or an accounts package. Not invoicing or credit
control software. Not connected to anything — nothing syncs, nothing logs in.
It does not know your contracts, your credit insurance or your customers'
payment history. Not legal, tax or commercial advice.

It will not stop your biggest account phoning. It will tell you, before they
do, what they were actually worth, and what filling the hole would take.

---

Sold through my Etsy shop, ThePlainLedger, so the download, the payment and the
buyer protection are all handled by Etsy. I never ask for access to anything —
no password, no accounts login, no bank feed. You download a file; that is the
whole transaction.

The example business, its accounts and its figures are invented. The arithmetic
behind every number is not.