When a stores spreadsheet stops working (and why it is not the spreadsheet)

Nearly every controlled store begins the same way. Someone builds a spreadsheet, seeds it with a stocktake, and for a while it is genuinely excellent — searchable, sortable, free, and shaped exactly like the way that person thinks about the stores.

Then, somewhere between three months and two years later, people stop trusting it. Not dramatically. Someone checks a figure against the shelf, finds it wrong, and quietly starts walking to the shelf instead. Within a month everyone is doing that, and the file is now a historical document that takes an afternoon a week to maintain.

The failure is structural, not technical

It is tempting to blame the tool, and that leads to the wrong fix: a better template, more formulas, a shared workbook, a database. It rarely helps for long, because the actual problem is a gap between two events.

Stock changes when someone takes something. The record changes when someone remembers to write it down. Every failure of a stores spreadsheet lives in the distance between those two moments.

A spreadsheet cannot close that gap, because updating it is a separate act performed by a different person at a different time — usually a person who is holding a part, wearing gloves, halfway to a job, and not in front of a computer.

This is why the size of the catalogue is a poor predictor. A 4,000-line spreadsheet maintained by one storekeeper who personally hands out everything can stay accurate for years. A 200-line one in an unmanned store with 30 people and a door code will be wrong by Friday.

Four signs you have crossed the line

1. People check the shelf before they check the file. This is the clearest signal, and once it starts it does not reverse. The file has become advisory.
2. The stocktake is an event. If reconciling requires closing the store for a day, or a weekend of overtime, the record has drifted far enough that only a full recount can fix it.
3. Nobody can answer "who had the last one". You know you are out. You do not know whether it went into a machine, a van, or a toolbox at home — and the difference matters when you are ordering a replacement.
4. There is a second, unofficial system. A notebook by the door, a WhatsApp group, a box of scribbled labels. That is your staff routing around a process that costs them too much.

What actually fixes it

Not accuracy features. The fix is making the recording step cost the person taking the stock almost nothing, so it happens every time without anyone being chased.

In practice that means the record has to be updated at the moment and place the stock moves — in the store room, by the person taking it, in a few seconds, without signing into anything. If it takes longer than walking out with the part, it will not survive a busy week.

What you get back

Once recording is automatic rather than remembered, several things stop being projects and become side effects. Reorder points work, because the balance is real. Consumption by cost centre or work order becomes a report rather than an investigation. And the question that started most of these projects — where did it all go — has an answer, per item, per person, per day.

It is also worth being clear about what does not change. You still need someone who owns the stores. You still need a sensible catalogue, and someone to decide what a minimum level should be. Software removes the transcription work; it does not remove the judgement.

If you are not ready to move

There are two things worth doing to a spreadsheet that genuinely extend its life. First, cycle count — a handful of lines every week rather than everything once a year, so drift is caught while it is still small enough to explain. Second, record who took something, not just that it went. A column with a name in it turns "we are short" into a conversation, and conversations are what stop the shortfall repeating.

Both are habits rather than features, which is the point: they work because they close the gap between the stock moving and the record moving. Any tool that does the same will beat any tool that does not.