Nearly every business has a spreadsheet that started off simple, did its job, then quietly grew into something nobody quite trusted any more but everybody still relied on. This is the story of one of those, and what happened when we finally sat down and sorted it out - with a fair bit of help from AI, used the way we think it ought to be.
The job it was doing
The business is a hospitality operation that shares its card tips - the tronc - between its staff. The pot gets divided by the total hours worked and paid out proportionally. It's simple enough in principle.
In practice it had run for two years - twenty-six periods - on a single workbook with a fresh tab added by hand each time. The key question was whether it was still working properly - did everybody actually get what they were owed? Working it out by hand ate hours every period and still left a nagging doubt the figures weren't quite right.
How we went at it
Here's what makes this an AI story rather than a bookkeeping one. Rather than squinting at the spreadsheet in isolation, we pointed an AI coding assistant at the business's own systems to look at the payslips for each pay period, so that it wasn't guessing from one source - it could read every tab, match each row back to the actual payroll records, and show us where the two disagreed.
One rule sat above everything: the original workbook was never touched. Everything the assistant produced came out as a new file, so there was always a clean original to check against. Each replacement tab showed the payroll hours, an adjustment that reconciled back to the old sheet, the entitlement it calculated, what was actually paid, and the gap. You could see the working, rather than being asked to trust a number.
What fell out when we looked properly
This is where it got interesting, because a hand-maintained spreadsheet hides a remarkable amount over two years. None of it was carelessness, it's just stuff that happens over time.
A paid Christmas holiday had been counted as worked hours. A formula copied down a column was quietly doubling one fortnight's hours for several people. A correction for an overpaid vendor had been entered in pounds straight into a column counting hours, which throws everything downstream out without leaving a trace. One tab was simply broken, handing the whole pot to hourly staff on paper while the real money had gone out on a different split.
Then there were the names. Of around 150 people, roughly half were spelled more than one way somewhere - nicknames one period and legal names the next, the odd entry in block capitals, full names typed into the surname column, honest typos, that sort of thing. On its own each is trivial. Across twenty-six tabs, they make totting anyone up by hand close to impossible.
Where the automation earned its keep
The clever bit wasn't the arithmetic - a spreadsheet can add up. It was the matching and the pattern-spotting.
For the names, the assistant used surname similarity, known nicknames, or alternates (think "Liz", "Lizzie" and "Elizabeth"), and people's employment dates to work out who was who - and, crucially, when it couldn't be sure it didn't guess, it flagged the row for a human. The patterns did the detective work too: multiples of eight pointed at holiday, an exact doubling at a copied formula, an underpayment in one period matched by an identical overpayment in the next at tips simply being paid a period late rather than money going astray.
Once every spelling mapped back to one proper payroll name, ordinary formulae worked across the whole thing again. The end product was a single reconciliation - one line per person, showing what they were owed, what they got, and where any gap first appeared - with a check row confirming each period still tied back to its original tab.
The bit that matters most
Take one thing from this. The machine did the grinding - the matching, the arithmetic, the relentless consistency no human keeps up across fifty-odd tabs. But a person stayed firmly in charge of every judgement call - which spelling to settle on, what to do with the broken period, whether two payroll records were the same person. If you hand off the judgement to the machine, all that happens is that you end up producing wrong answers faster.
Work that had been swallowing hours every period was done, for more than twenty periods, in a single session. Instead of a vague worry, the owner came away with one clear reconciliation showing exactly who was owed what, and when it arose - plus a tidy list of genuine questions only they could answer. The balance in play across the two years came to a tidy sum, which is rather a lot to rest on a copied formula and a few misspelled names.
What it means for your business
Spreadsheets don't fail with a sudden bang, they fail quietly - a formula copied one cell too far, a figure in the wrong units, the same person spelled three ways over a couple of years - and the longer one runs, the more it buries.
AI is at its most useful here not when it's staring at the spreadsheet alone, but when it can see the real source data behind it: your payroll, timesheets, the original record of what actually happened. Keep a person on the decisions, let the machine handle the matching and the sums, and never let automation write over your original - generate, compare, then adopt.
If you've got a spreadsheet that's quietly become mission-critical and you no longer quite trust it, that's exactly the sort of knot we enjoy untangling. Tell us about it and we'll give you a straight answer on whether it's worth sorting.