Bad Debt & Write-Off Tracker Spreadsheet | Aged Debtors Credit Control Excel Google Sheets Template | Accounts Receivable

🧾 BAD DEBT & WRITE-OFF TRACKER — EXCEL AND GOOGLE SHEETS

A bad debt is not a loss of money. It is a loss of margin.

You did not lose a dollar. You lost a dollar of margin, and a dollar of margin
takes several dollars of sales to make. So the honest number is not what you
wrote off. It is what you now have to sell, at your own margin, to be back where
you started.

---

EIGHT LINKED TABS

· Start Here — what each tab expects and where to type.
· Setup — three numbers, and two lists you name yourself.
· Write-Offs — one row per invoice you gave up on.
· By Customer Type — which kinds of account go bad, per hundred accounts.
· How Old It Got — what chasing it longer actually got you.
· The Warning Signs — how much of it you could have seen coming.
· Replacing It — the number nobody works out.
· Overview — the whole year on one screen.

---

FROM THE WORKED YEAR INSIDE THE FILE

Write-offs 380
Amount invoiced $239,885
Recovered $18,483
You got back 7.7%
WRITTEN OFF $221,402
What chasing it cost on top $26,073
WHAT IT COST YOU $247,475

Your gross margin 24.5%
SALES YOU HAVE TO WIN AGAIN $1,010,100
Which is this many days of trading 13

---

5.3 PER HUNDRED. 52.4 PER HUNDRED.

A big category is not a bad one.

Against every kind of customer in Setup you put how many accounts of that kind
you actually hold. Without it the biggest category always looks like the worst
one. With it, everything changes.

In the worked year the checked trade accounts produced more write-offs than the
public sector did — but 5.3 per hundred accounts, against 52.4 per hundred for
the accounts nobody checked.

Per account held, an unchecked one cost $446.29 a year. A checked one cost
$59.50. That is 7.5 times, from the same trade in the same year.

---

15.5% BACK AT NINETY DAYS. 0.8% AFTER A YEAR.

The file ages every debt from the day it was due to the day you gave up, and
shows what each extra month actually bought.

Under ninety days: 15.5% came back, and each one took half an hour to chase.
Over a year: 0.8% came back, and each one took two and a half hours.

Five and a half times the hours, for a twentieth of the money. That is the whole
argument for a shorter, harder chasing policy, in one row.

---

TWO QUESTIONS DO MOST OF THE WORK

Did you check them? Yes, Partly or No. A credit check, a trade reference, a
deposit, a limit somebody actually set.

Could you have seen it coming? Yes, Maybe or No, against every way a debt ends.

They are the only judgement calls in the file, and they split the whole bad-debt
bill into piles you can do something about and piles you cannot.

In the worked year the Yes pile came to $120,904 — 54.6% of everything written
off, and $493,485 of sales to replace.

---

THE ROW NOBODY EXPECTS

Seventy-nine invoices written off as too small to chase, worth $6,043 between
them. And $760 of somebody's time went into chasing them anyway before anybody
said so out loud.

Every row carries the hours somebody spent on it, which is how a file like this
finds the debts that were never worth a phone call.

---

WHAT YOU ACTUALLY TYPE

Ten things per write-off: a reference, the kind of customer, how it ended, three
dates, what was invoiced, what came back, the hours spent chasing, and any legal
or collection fees.

The rate, the margin and both lists come from Setup, so nothing is typed twice.
Age bands, the check answer and the hindsight answer all fill themselves in.

---

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 write-offs 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 do not know our gross margin exactly.
Use your best figure. The point of the sales-to-replace number is the order of
magnitude: bad debt costs three to five times what the write-off line says.

We only write off two or three a year.
Then it takes ten minutes a year and the per-account view still tells you which
kinds of customer they were. Three years in one file is better than one.

Does it track live debt?
No. It measures what you gave up on. Your ledger already tells you who owes you
today; nothing tells you what giving up has cost you over three years.

Does it hold customer names?
Only if you type them. A reference is enough, and the file never needs a name.

Is this only for builders' merchants?
No. Anywhere you supply on credit: a wholesaler, a printer, a haulier, an
agency, a garage, a landlord, a professional practice.

---

WHAT THIS IS NOT

Not a credit-checking service, a sales ledger or a collections platform. Not
accounting, bookkeeping or tax software. Not connected to anything — nothing
syncs, nothing logs in. Not legal, credit or tax advice, and it does not know
your terms of business.

It will not stop anybody going bust. It will tell you which kinds of account do
it, how long you should chase before you stop, and what the whole thing costs
you in sales rather than in write-offs.

---

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 ledger login, no bank feed. You download a file; that is the
whole transaction.

The example business, its customers and its figures are invented. The arithmetic
behind every figure is not.