How to Get One Inventory Number Everyone Trusts

A planner walked me through how she works out inventory coverage. Warehouses in three states. A separate export from each. A spreadsheet where she puts them together, another one for weights and dimensions, and a seasonality adjustment she had built herself and maintained by hand.

The arithmetic was straightforward. Five thousand eight hundred units on hand at the end of December against an average of twelve hundred a month gives you about four point eight months of coverage, against a target of three and a half.

She could produce that number. It took her the better part of a day, it was correct, and almost nobody else in the company could have produced it or would have got the same answer if they tried.

The request and the actual problem

What she asked for was a database and a dashboard. Reasonable, and eventually right.

What was in the way was that nobody had agreed what any of it meant.

Three warehouses had three ideas of when a unit stops being in transit and starts being on hand. The seasonality curve existed in one person's spreadsheet and one person's head. "Average monthly demand" meant trailing three months to one team and trailing twelve to another, and both were defensible, and they gave different answers about whether the company was over target.

Build the dashboard first and you get a screen that is confidently wrong, which is worse than the spreadsheet. The spreadsheet at least came with a person attached who knew where the bodies were.

Settle the words before the screen

Write the formula in words. "Months of coverage, by SKU, by warehouse: units on hand divided by average monthly demand." Not "inventory visibility." Then settle each term in it. When does a unit in transit become on hand? Which months make up the average? Where does the seasonality adjustment come in? None of them had one agreed answer here, which is why each has to be settled out loud.

Find every copy. Every export, every spreadsheet, every screen where somebody could get a version of the figure. Expect more than anyone predicted. At this company it was five places, and two of them were spreadsheets only their owners knew about.

Give the definitions to the person who knows the product. Here that was the planner, not a committee. The definitions need judgment calls, and a committee produces a document instead of a decision.

Walk one SKU through every warehouse. One product, one month, all three sites, every source, until you can explain every difference. This is boring and it is where the project is actually won. Each difference is either a definition problem or a data problem, and you need to know which before you write code.

Automate the assembly before the screen. Build the pipeline before the visualization, because a beautiful dashboard on unreconciled data is a faster way to make a bad decision.

(For the same idea applied to any figure, not just inventory, see How to Fix Your Data Before You Automate.)

Why the spreadsheet was never the villain

There is a temptation to walk into a company like this and treat the spreadsheets as the problem. They are not. They are the workaround, and the workaround is evidence of a competent person compensating for something the systems do not do.

The planner's spreadsheet contained real institutional knowledge. The seasonality curve was the accumulated judgment of someone who had watched this product line for years. If we had replaced it with a generic model, we would have thrown away the most valuable thing in the room and called it modernization.

So the first build was not a replacement. It was taking the assembly off her: the three exports, the joining, the formatting. Her judgment stayed hers. The typing stopped being hers.

The dashboard came later, on top of a pipeline everybody had agreed to, with her name on the definitions.

The tell

If you want to know whether your company has this problem, ask two people in different departments for the same number, separately, on the same afternoon.

If the answers differ, you do not have a reporting problem. You have a definition problem wearing a reporting problem as a costume, and no amount of tooling fixes it. That conversation costs you an afternoon. Finding out after the build costs you a quarter.