Fix Your Receivables Sheet Before You Automate It

Every field-service business that has run on spreadsheets for a few years has one of these somewhere. It started as a clean list of who owes what. Then somebody copied a row that needed updating, a payment got logged twice, and somebody filtered the view, hid a block of rows they didn’t need that week and never unhid them. Nobody was careless. That’s what happens to any spreadsheet several people touch, under time pressure, for years.

Automating that sheet as it stands is the mistake. Bolt a new invoicing or scheduling system onto receivables nobody has verified and you’ve moved the mess somewhere harder to inspect by hand. This guide covers the audit that comes first. The working example is a real fire-safety inspection contractor’s receivables book, which shows what rotted looks like once you open it up.

What a rotted receivables sheet holds

Receivables lived in two hand-maintained Excel workbooks across roughly 490 rows, with no ledger system anywhere. Before anyone could trust that data enough to migrate it, an independent audit re-derived every figure from the raw files, from scratch, more than once, to see whether the numbers held up on a second and third look. They didn’t. Here is what the audit found:

• Duplicate rows. Confirmed duplicates were padding the nominal total at the top of the sheet. Strip them out and the real outstanding figure came in measurably below what the sheet claimed.
• Invoice numbers that repeat. Ten numbers turned up on separate, unrelated invoices in the current year’s sheet alone. Two full historical sheets shared over a hundred numbers between them, and almost none of those shared numbers pointed at the same document. Everyone in the office treated the invoice number as a reliable identifier. Working out which real invoice a row meant took the number, the site and the amount together, never the number on its own.
• Hidden rows, counted in every total. A meaningful share of the book, worth a substantial sum, sat hidden in the spreadsheet view. It went into every total while staying invisible to anyone who opened the file and scrolled through it. The sheet’s autofilter range had gone stale against the data underneath it, which is a small technical fault with a large bill attached.
• Invoices marked both paid and outstanding at once. Some of them flatly contradicted each other. One invoice carried a partial-payment note for one amount while the separate payments sheet recorded two different cheques adding up to a larger amount. The client’s own staff had flagged a few of these to get back to and never resolved them.
• No reliable bill-to entity on most rows. A large majority of rows carried the job site address and nothing about who gets billed. In fire-inspection work those are frequently two different parties, since a property manager rather than the tenant is often the one paying.
• Real money sitting overdue. A meaningful chunk of the book had passed 90 days at the time of the audit, and one client relationship carried a disproportionate share of the whole outstanding total, roughly two-fifths of it.

None of that is unusual once a spreadsheet has served as the de facto ledger for years. Excel was built for one person’s quick tally, and this one was carrying permanent financial records for a whole office.

Why importing it as-is fails

Any new system, purpose-built or off the shelf, takes whatever you import as true. It sums outstanding balances, flags overdue accounts, and sends collection chasers off that data. Wrong rows stay wrong. They come out looking more official, because a number sitting inside a system reads as more trustworthy than the same number in a spreadsheet.

A bad import also compounds. Put contradictory rows into a system that treats the invoice number as a real key and everything built on that key inherits the ambiguity: payment matching, aging reports, collections. Now the confusion runs automatically.

The rule that governs the cleanup: never guess

The one call that matters most in a receivables cleanup is what you do when the data contradicts itself. Letting software pick something reasonable is tempting. Take the higher of two conflicting amounts, let the more recent row win, treat a hidden row as hidden on purpose and drop it. Each of those is a decision about somebody’s money, made quietly, by a script that has never met your customers.

Here, the rule ran the other way. Anything ambiguous became an exception for a person to look at. The duplicate rows, the reused numbers, the paid-and-outstanding contradictions, all of it went back to the client for an explicit answer, and the migration tooling inferred nothing. That takes longer. It’s also the only way to run this that doesn’t quietly cost you money, or burn a customer who gets billed twice or never billed at all.

In practice:

1. Find every defect type before you touch anything. Duplicates, hidden rows, reused numbers and paid/outstanding contradictions are each their own pattern, and a machine can spot all four. Get the full list before you decide what to do about any of it.
2. Identify a row from more than one field. When the invoice number won’t hold up on its own, match on number plus site plus amount to work out which real document a row refers to. Slower than trusting a single column, and the column has already proven itself untrustworthy once.
3. Put contradictions in front of whoever can settle them. A hidden row worth real money, or an invoice marked both paid and outstanding, is a business question about what happened with that customer. Send it to the office and leave it alone until an answer comes back.
4. Then import it 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. Old invoices and payments should behave like real transactions you can chase, apply a payment to, and report on.

What this looks like for your own spreadsheet

You don’t need an outside audit to get going. Run these four checks on your own receivables sheet before any new system arrives.

• Unhide everything. Select the whole sheet, unhide rows and columns, and watch the totals. If they move, you have your own version of the same problem.
• Check the invoice-number column for repeats. Sort by number rather than by date and read down it. If your business ever restarted its numbering, you’ll find some.
• Test “paid” against your bank or payment records on a random sample of rows, including the ones that look fine. A sheet with one contradiction usually has several.
• Count the rows with no clear bill-to entity, only a job site or a generic customer name. If the invoice goes to a property manager, a head office, or some entity other than the site contact, an address on its own won’t get you paid.

When you find real problems, don’t fix them by deleting rows or picking whichever number looks likelier. Flag each one and decide it on purpose, the way this engagement’s cleanup did. Deciding is where the real cost of automating receivables sits, and the software part comes cheap after that.