Clean Up Your Receivables Before You Automate Them

Every field-service business that has run on spreadsheets for a few years has one of these somewhere: an accounts-receivable workbook that started as a clean list of who owes what, and slowly turned into something nobody fully trusts. Rows get copied instead of updated. A payment gets logged twice. Someone filters a view, hides a block of rows they didn’t need that week, and forgets to unhide them. None of this is negligence. It’s what happens to any spreadsheet that gets touched by different people, under time pressure, for years.

The mistake is trying to automate that spreadsheet as it stands. Wiring a new invoicing or scheduling system on top of receivables data you haven’t actually verified just moves the mess into a system that’s harder to inspect by hand. This guide is about the audit that has to happen first, using a real fire-safety inspection contractor’s receivables book as the working example of what “rotted” actually looks like in practice.

What a rotted receivables sheet actually contains

On the fire-safety engagement, receivables lived in two hand-maintained Excel workbooks, not a ledger system, spread across roughly 490 rows. Before any of that data could be trusted enough to migrate, an independent audit re-derived every figure from the raw files, from scratch, more than once, to see if the numbers held up under a second and third look. They didn’t, not cleanly. Here is what the audit actually found:

• Duplicate rows. Confirmed duplicate rows were inflating the nominal total shown at the top of the sheet. Once removed, the real outstanding total was measurably lower than what the sheet claimed.
• An invoice-number key that was not unique. Ten invoice numbers were reused across genuinely different invoices in the current year’s sheet alone. Two full historical sheets shared over a hundred numbers between them, with essentially none of those shared numbers actually pointing at the same document. The invoice number, which everyone assumed was a reliable identifier, was not one. Reconstructing which real invoice a row referred to required cross-checking the number against the site and the amount together, never the number by itself.
• Hidden rows, counted in every total. A meaningful share of the book, worth a substantial sum, was hidden in the spreadsheet view and silently included in every total while being invisible to anyone opening the file and scrolling through it. The sheet’s autofilter range had gone stale relative to the actual data underneath it, a small technical mismatch with a large practical consequence.
• Invoices marked both paid and outstanding at the same time. Some of these directly contradicted each other: one invoice showed a partial-payment note for one amount, while the separate payments sheet recorded two different cheques totalling a different, larger amount. The client’s own staff had flagged some of these as “get back to this” and never resolved them.
• No reliable bill-to entity on most rows. A large majority of rows recorded only the job site address, not who actually gets billed for it, which are frequently not the same thing in fire-inspection work where a property manager, not the tenant, is often the paying party.
• Real money genuinely overdue. A meaningful chunk of the book was more than 90 days overdue at the time of the audit, and a single client relationship accounted for a disproportionate share of the entire outstanding total, roughly two-fifths of it.

None of this is unusual for a spreadsheet that has been the de facto ledger for a small business for years. It’s the predictable outcome of a tool designed for one person’s quick tally being pressed into service as permanent financial record-keeping for a whole office.

Why you cannot just import this as-is

A new system, whether it’s a purpose-built application or an off-the-shelf field-service product, will treat whatever you import as true. It will sum outstanding balances, flag overdue accounts, and drive collection follow-ups off that data. If the underlying rows are wrong, the new system doesn’t fix that. It just makes the wrong numbers look more official, because now they’re in a system instead of a spreadsheet, and people trust systems more than spreadsheets by default.

Worse, a bad import compounds. Once contradictory rows sit inside a system that treats invoice numbers as a real key, every process built on top of that key, payment matching, aging reports, collections, inherits the ambiguity. You end up automating the confusion instead of resolving it.

The rule that has to govern the cleanup: never guess

The single most important decision in a receivables cleanup is what happens when the data is ambiguous. It is tempting to let software make a reasonable-looking guess: assume the higher of two conflicting amounts is right, assume the more recent row wins, assume a hidden row was hidden on purpose and can be dropped. Every one of those guesses is a decision about someone’s money, made silently, by a script that doesn’t know the business.

On the fire-safety engagement, the working rule was the opposite: ambiguity creates an exception for human review, never a silent assumption. Every defect the audit found, the duplicate rows, the reused numbers, the contradictory paid/outstanding rows, was resolved by an explicit decision from the client, not inferred by the migration tooling. That’s slower. It’s also the only version of this process that doesn’t quietly cost someone money or credibility with a customer who gets billed twice, or not billed at all.

In practice this looked like:

1. Find every defect class first, before touching anything. Duplicates, hidden rows, reused numbers, and paid/outstanding contradictions are each a distinct, mechanically detectable pattern. Find all of them before deciding what to do about any of them.
2. Reconstruct real identity from more than one field. If the invoice number alone isn’t reliable, use number plus site plus amount together to work out which row refers to which real document. This is slower than trusting a single column, and it’s the only way to get a correct answer when the single column has already proven itself untrustworthy.
3. Surface contradictions to the person who can actually resolve them. A hidden row worth real money, or an invoice marked both paid and outstanding, is not a technical problem. It’s a business question about what actually happened with that customer. Route it to the office, don’t auto-resolve it.
4. Only then import as a real, ledger-affecting record. Once every flagged row has an explicit answer, the cleaned data goes into the new system as genuine financial history, not as reference data sitting off to the side. Historical invoices and payments should behave like real transactions in the new system, not like an imported PDF nobody can act on.

What this looks like for your own spreadsheet

You don’t need an outside audit to start. Before bringing in any new system, run these checks on your own receivables sheet:

• Check for hidden rows and columns. Select the entire sheet and unhide everything. Look at whether the totals change. If they do, you have a version of the same problem.
• Check whether your invoice-number column actually has zero duplicates. Sort by number, not by date, and look for repeats. If your business has restarted numbering at any point, expect to find some.
• Cross-check “paid” against your bank or payment records for a random sample of rows, not just the ones that look suspicious. Sheets that have one contradiction usually have more than one.
• Look at how many rows have no clear bill-to entity, only a job site or a generic customer name. If billing goes to a property manager, a head office, or a different entity than the site contact, a site address alone isn’t enough to invoice correctly.

If you find real problems, resist the urge to fix them by deleting rows or picking whichever number looks more plausible. Flag them, and decide them deliberately, the same way this engagement’s cleanup did. That decision work is the actual cost of automating receivables. The software part is comparatively easy.