Why your stock level is always wrong when you need it right

The ERP says 340 units. The warehouse says 312. Your purchasing lead says she stopped trusting the ERP number six months ago and runs her own spreadsheet instead. This is the most common inventory problem we encounter, not that companies lack data, but that the data is always slightly out of date at the exact moment a decision needs to be made. Here is what causes the gap, what it costs in over-ordering and stockouts, and what a live inventory view actually requires to build.

There is a particular kind of frustration that ops and purchasing leads at manufacturing companies know well. You need to make a decision, a supplier is on the phone, a production run is scheduled, a customer is asking about availability, and you open the ERP to check the stock level. The number is there. But you do not quite trust it, because the last warehouse count was three weeks ago, because there were two shipments received this week that may or may not have been entered, and because you know from experience that the ERP number and the physical number diverge by the time anything important is riding on it.

So you call the warehouse. Or you check the spreadsheet. Or you make a judgment call and hope the number is close enough.

This is not an edge case. In the companies we work with, it is the default state of inventory management at the 20 to 200 person scale.

Why ERP stock numbers drift

An ERP records transactions. When a purchase order is received and a warehouse operative enters the goods receipt, the ERP updates. When a production order consumes components and someone closes the production booking, the ERP updates. When a customer order ships and the despatch is confirmed, the ERP updates.

The gap lives in the lag between when things happen and when they get entered. A goods receipt that came in on Tuesday afternoon might not be entered until Wednesday morning. A production booking from Friday might get closed on Monday. Returns and adjustments often get batched. Damaged stock gets recorded in the physical count but not in the system until someone raises a write-off request, which takes another two days.

None of this is negligence. It is the friction of a process where entering data is a secondary task for people whose primary job is moving physical goods. The ERP number is accurate as of the last batch of updates. Which is almost never right now.

The shadow spreadsheet

In most of the warehouses we have audited, the purchasing lead or ops manager maintains a parallel record. It is a spreadsheet, usually Google Sheets, sometimes Excel, that they update personally from a mix of incoming delivery notes, production run confirmations, and warehouse conversations. It is more accurate than the ERP for fast-moving SKUs, but only as long as that person is maintaining it. It has no history. It has no audit trail. And when that person is on holiday, the rest of the team goes back to trusting the ERP number and hoping.

The cost of the shadow spreadsheet is not the time it takes to maintain, though that is real, typically two to four hours per week. The cost is the decisions made on bad numbers. Over-ordering because the ERP showed low stock that had already been received but not entered. Stockouts because stock that was committed to a production run was not reflected in the available balance shown to the sales team. Emergency freight because a reorder point was missed by three days.

These are not dramatic failures. They are small, chronic losses, each one defensible, all of them avoidable.

What a live inventory view actually requires

The dashboard we build for inventory visibility connects directly to three sources: the ERP (purchase orders, production orders, sales orders, stock movements), the warehouse management system or goods-receipt log if that is separate, and in some cases the supplier portal for open PO confirmations.

The reconciliation layer does two things that the ERP alone cannot. First, it distinguishes between stock on hand (physically in the building), available stock (on hand minus what is committed to confirmed orders), and incoming stock (on order, by expected delivery date). Most ERPs can show all three, but the numbers are often stale for the reasons described above. The dashboard reads the same source data but timestamps every update, so you can see not just the current number but when it last moved and why.

Second, it flags anomalies. A SKU that has not moved in thirty days when its average movement is every four days. A purchase order that was due yesterday with no goods receipt posted. A production run that consumed more components than the bill of materials specifies. These are the signals that exist in the data and currently surface only when someone happens to look at the right row at the right time.

What the purchasing lead stops doing

The shadow spreadsheet does not disappear on day one. Most purchasing leads run it in parallel for the first month, checking the dashboard number against their own. Within three weeks, the pattern is consistent: the dashboard is right, and the spreadsheet has a three-day lag. By the end of the month, the spreadsheet is abandoned. Not because we asked them to give it up, but because maintaining it becomes obviously redundant.

What changes is not just the time saved. It is the decision confidence. The purchasing lead makes a call to a supplier based on a number she trusts. The ops manager commits to a production schedule without a phone call to the warehouse first. The sales team quotes availability based on the same data the warehouse is looking at, in real time, rather than a number that was right on Monday.

The ERP is still the system of record. The dashboard does not replace it. What it does is surface the ERP data, and the gaps in it, clearly enough that the people who need to act on it can do so without the lag that was costing them an hour a day and a judgment call they should never have had to make.