Bad Debt & Write-Off Tracker Spreadsheet
This spreadsheet turns what you wrote off into the sales you now have to win again at your own gross margin.
A bad debt is not a loss of money, it is a loss 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 to be back where you started, plus the hours somebody spent chasing it first.
What is in it
- Write-Offs — one row per invoice you gave up on: 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.
- By Customer Type — write-offs per hundred accounts you actually hold, so the biggest category stops automatically looking like the worst one.
- How Old It Got — every debt aged from the day it was due to the day you gave up, with what each extra month of chasing actually bought.
- The Warning Signs — two questions on every row: did you check them, and could you have seen it coming?
- Replacing It — the write-off converted at your own gross margin into sales you have to win again, and days of trading.
- Setup and Overview — three numbers, two lists you name yourself, and the whole year on one screen.
Who it is for
Anywhere you supply on credit: a wholesaler, a merchant, a printer, a haulier, an agency, a garage, a landlord, a professional practice. If you only write off two or three a year it takes ten minutes a year, and three years in one file is far more useful than one.
The worked example
In the worked year, 380 write-offs left $221,402 unpaid and cost $26,073 more to chase. At a gross margin of 24.5%, that is $1,010,100 of sales to win again — thirteen days of trading.
Accounts nobody had checked went bad at 52.4 per hundred against 5.3 per hundred for checked ones. Chasing past a year returned 0.8% for two and a half hours a debt, against 15.5% for half an hour a debt under ninety days.
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 a bad debt really costs?
Not in money written off but in sales you have to win again at your own margin. In the worked year $221,402 was written off with $26,073 of chasing on top, and at a 24.5% gross margin that is $1,010,100 of sales to replace, or thirteen days of trading. Bad debt costs three to five times what the write-off line in the accounts says.
We only write off two or three a year, and we are not a builders' merchant.
Then it takes ten minutes a year, and the per-account view still tells you which kinds of customer they were, so three years in one file is better than one. It works anywhere you supply on credit: a wholesaler, a printer, a haulier, an agency, a garage, a landlord, a professional practice.
We do not know our gross margin exactly. Does that matter?
Use your best figure, because the point of the sales-to-replace number is the order of magnitude, not the decimal place. The only judgement calls in the file are two questions per row: did you check them, answered Yes, Partly or No, and could you have seen it coming, answered Yes, Maybe or No. Everything else is already on paperwork you have: three dates, what was invoiced, what came back, the hours spent chasing and any legal or collection fees.
Do I need Excel, or will Google Sheets open it?
Either will, 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 customer names, and does it track live debt?
It only holds names if you type them, because a reference is enough and the file never needs a name. It does not track live debt: your ledger already tells you who owes you today, while this measures what giving up has cost you over three years. It is not a credit-checking service, a sales ledger or a collections platform, and it is not legal, credit or tax advice.
Need it built around your business?
This file assumes credit accounts, invoices with due dates and somebody chasing them. If your exposure looks different — a landlord whose bad debt is arrears and a vacant unit rather than an invoice, a practice writing off work in progress that was never billed, a haulier whose customer goes into administration owing on forty consignments at once — I can build the same arithmetic around how you actually lose money.
All other templates are listed on the Excel & Google Sheets Templates page.