The spreadsheet had been quietly wrong for a year

The spreadsheet had been quietly wrong for a year



Tópico: The spreadsheet had been quietly wrong for a year
Categoria: Tutoriais | Programação & Tecnologia
Idioma Principal: Português (Conteúdo de Tecnologia)

Descrição do Conteúdo / Informações:
-------------------------------------------------------------------------
Originally published on the NuWay Biz Solutions blog.

✦ Cover image: Made with ChatGPT — we're transparent about AI. See the exact prompt on the original post.

The first thing they showed us was a file.

A landscaping company, about a dozen crew, two office staff. We'd been brought in to look at whether any of their admin work could be handed to a machine, and the owner opened a laptop, clicked through three folders, and turned the screen around. Column after column of green. Somebody had clearly cared about this thing for a long time.

It was the monthly numbers. Every job, what it brought in, what the crew hours cost, what the materials cost, and at the bottom of the third tab, the number the whole company steered by: what each crew actually made last month.

The office manager built it fresh every month. First working day, coffee, door shut. Export from the accounting system, export from the scheduling app, paste both in, fix the job names that came through spelled differently, drag the formulas down, check the pivots didn't break. About three hours if nothing went wrong. Longer in January.

The owner told us he trusted that sheet more than anything else in the business.

Then we asked the office manager to walk us through one column, and she got about halfway and stopped. She didn't build that part. A bookkeeper had set the sheet up four years earlier and left, and she'd inherited it the way you inherit a house with a boiler you don't understand: it works, you don't touch the pipes, and you learn which noises are normal.

So we spent an afternoon rebuilding it from the raw exports, on our own, to see if we landed on the same answer.

Mostly we did. One line was off. A kind of subcontractor cost was landing just outside the block of cells the crew-profit formula added up, so every crew came out looking a little better than it was. Small on any one job. Not small across a year, which is roughly how long it had been that way. Long enough that the owner had already made a real call on the back of it, about which kind of work to chase more of.

Nobody had done anything careless. The sheet was good work.

It had just never been checked by anyone, including the two people who trusted it most.



The obvious advice, and why he was right to resist it


Walk into that room as a consultant and the pitch writes itself. Three hours a month. A fragile file. One person who understands it. Automate the whole thing, buy a dashboard, get the humans out of the loop.

The owner pushed back before we'd finished saying it, and his version was better than the pitch. That hour isn't data entry, he said. It's the only hour all month I actually look at my business. Touching every number is how I notice the job that went sideways, the supplier who raised prices without telling anyone, the crew whose hours stopped matching their output. Automate the typing and you automate away the noticing.

He was right, and it's the part the pitch never has an answer for.

Those are two different jobs wearing the same hour. Gathering the numbers is mechanical, and a machine could do it at 6am on the first. Reading them is judgment, and judgment is the part that catches things. One of those two jobs is worth defending.

Automate the gathering. Keep the reading. They only look like the same task because the same person does them back to back.

That would be the end of a shorter article. The complication is that reading only works if the thing you're reading is true, and this is where the manual sheet starts losing the argument it was winning.



A spreadsheet gets believed because it's a spreadsheet


There's a group of researchers in Europe who have spent years collecting the ways spreadsheets go wrong in real organisations. One of their case studies follows a small non-profit run by a volunteer committee, about the level of formality a lot of the businesses we work with have. Two volunteers built a cash-flow model in Excel to plan the next 35 years. It said the organisation was heading for trouble and couldn't afford to replace its aging equipment, so the board froze the spending.

The model had understated future cash flow by more than a factor of two. It also ran its whole calculation at a negative discount rate, which is a bit like doing your budget with a calculator that adds when you press minus.

When someone wrote up a step-by-step test script showing exactly how to reproduce the errors, and sent it to the chairman and the treasurer, nothing happened. No response. No evidence anyone ever ran it.

The researcher's line for this is the sharpest description of the problem I've read: once information is loaded into a spreadsheet, it acquires properties it may not deserve. He lists them. Believability. Correctness. Appropriateness. Objectivity. Authority.

The green cells do that. The grid looks like arithmetic, so it reads as fact.

And notice the failure is identical to the one everybody warns you about with dashboards. A dashboard keeps rendering a beautiful chart of a wrong number and you find out in March. A trusted spreadsheet does exactly the same thing, with the same confidence, in a font you happen to like better. The landscaping company's sheet was wrong for a year in a file the owner trusted more than anything else he owned.

A big company would have someone whose actual job is to check that file. A company this size has the person who built it, and she has four other jobs.



The two things worth fixing


So the sheet stays, and automating it is only half a job. What's left is smaller than either, and duller, and it's most of what we do when we walk into one of these.

One. A second person has to be able to rebuild it. That means rebuilding it from the raw exports, not following along while somebody talks them through it. Where does each number come from, which export, which app, what does this column mean, and what is the sheet for. Written down in the file itself, because a document about the sheet lives in a folder nobody opens. The test is blunt: if the only person who can reproduce your monthly numbers goes on holiday for three weeks, does the business still know what it made?

The rule of thumb we use: if a spreadsheet would take more than about two days of work to reproduce from scratch, it has stopped being a document and become a piece of your business. Treat it accordingly.

Two. One number gets checked by hand, every month, before you trust the rest. Cash in the bank. Total deposits. Something you can verify at the source in two minutes without the sheet's help. If it doesn't tie out, you stop and find out why before you read anything else.

This is the easiest step to skip, and it's the one that would have caught the landscaping company a year earlier. It matters more, not less, once you automate anything, because the two failure modes are opposites. A manual sheet fails by being late. An automated one fails by being confidently wrong and on time.



Then automate the boring half, with your eyes open


Only now is it worth touching the tooling, and the honest version is that every path charges a toll.

The most common advice is to use the tools built into Excel to pull and join the data for you. It genuinely works, and it will join almost anything you point it at. What people don't mention is that it often won't refresh on its own in a browser, and on a Mac it can't reach most of the apps you'd want. So the monthly job doesn't disappear. It moves onto one particular person's Windows laptop, which is the same dependency you started with wearing a better outfit.

The free dashboard tools will blend a handful of sources and are often the right call, up to the point where they stop letting you add more. The services that wire apps together are built for things that happen constantly, which makes them a poor fit for a job that runs once a month: something that fires twelve times a year can break in February and sit broken until May.

We put these in for people constantly. The point is to know which one you've picked, who owns it, and how you'd find out it stopped.

Start with the thing you already pay for. A surprising number of accounting and scheduling systems will email a scheduled report on the first of the month, and nobody ever turned it on. That plus a folder where the exports always land with the same filenames kills most of the three hours, costs nothing, and doesn't create a new machine only one person understands.



Why the sheet is there in the first place


It's easy to read all this as an argument against spreadsheets, and it isn't. When Intuit surveyed 1,500 US firms under 100 employees in 2022, reported in a National Bureau of Economic Research working paper, 46% said they use spreadsheets to manage or record their money.

46% of small US firms use spreadsheets to record their money

They could pick more than one answer, and more of them named accounting software than named spreadsheets. So in plenty of these businesses the sheet isn't standing in for the accounting system. It's sitting next to it, doing a job the accounting system won't do, which is usually joining money to something the money doesn't know about: a job, a crew, a channel, a customer. That joining is most of what "ready" data actually means in practice.

That's a gap, left where two systems wouldn't talk to each other, and a person stepped into it. The spreadsheet is the shape of that person's work.

Which is why the answer is usually to treat the sheet as a piece of the business rather than a document: somebody else can rebuild it, something checks it, and the machine does the parts that don't need a brain.

If it grows past what a sheet can carry, and for some businesses it does, that's when the conversation about connecting the tools properly earns its place. Plenty of businesses aren't there yet, and don't need to be.

The three-week test

If there's a file your business steers by, open it. Pick one number on it that matters, and try to answer two questions without asking the person who maintains it: where did this come from, and how would I know if it were wrong? If both answers are missing, that's a morning's work, not a project.

We finished up with the landscaping company in about a week. Two scheduled exports, a folder, a page of notes at the front of the workbook explaining every column, and one figure the office manager ties back to the bank before she sends anything to the owner. Still three tabs of green. Still hers.

The rebuild takes her about forty minutes now, and she still does it on the first working day of the month, with the door shut, because that's when she reads it.

If there's a file like that holding up your business and nobody's completely sure how it works, tell us what it does and we'll tell you whether it needs automating or just needs an owner. It's usually the second one.

Practical AI. Clear process. Real business value.

— Brian, NuWay Biz Solutions

P.S. If you went looking for that file and found four versions of it with different dates in the filename, pick the one everybody actually opens and delete the argument.