Build an inventory system that reconciles itself

Almost every home-grown inventory system starts with a Stock level column that people edit.

It is wrong within a fortnight. Worse, when it is wrong there is no way to find out why — no history, no cause, just a number that disagrees with the shelf.

The fix is a single structural decision.

Never store the stock level

Stock on hand is not a fact you record. It is the sum of everything that has happened to that product.

So you record the events, and derive the number.

Stock movements — one record per event:

Then on the product, Stock on hand is a sum rollup of movement quantities.

That number cannot be wrong, because it is not stored. And every unit is traceable: click through and see exactly which receipts, sales and adjustments produced it.

The signed-quantity choice matters. One Quantity column that goes negative is simpler to sum than separate In and Out columns, and it makes a filtered rollup by type trivial when you want "total received this year".

The tables

Products — SKU, name, category, unit of measure, cost, price, reorder point, reorder quantity, supplier relation, active flag.

Locations — warehouses, shelves, vans, shops. Even a single-site business benefits: "Main" and "Damaged" and "In transit" are locations, and modelling them stops write-offs polluting sellable stock.

Stock movements — as above. The heart of the system.

Suppliers — name, contact, lead time in days, minimum order.

Purchase orders and PO lines — the same order/line-item pattern, in the opposite direction.

The numbers that fall out

Once movements exist, the useful figures are rollups rather than reports:

• Stock on hand — sum of all movements, per product.
• Stock by location — same rollup, filtered by location.
• On order — sum of PO lines where the PO status is Sent and not yet received.
• Available — formula: On hand − Allocated, where allocated is a rollup of unshipped sales lines.
• Below reorder point — formula flag: Available < Reorder point. This is your buying list, always current.
• Days of cover — Available ÷ average daily sales, from a rollup of the last 90 days of sales movements. Far more useful than a raw count, because it accounts for how fast things actually move.

Available, not on-hand, is the number that should drive decisions. Stock that is physically present but promised to a customer is not stock you can sell.

Stock counts, done properly

A physical count is not an edit to the stock level. It is a movement of type Adjustment, sized to the difference.

The flow:

1. Take a count. Record Counted quantity on a stock-count record.
2. The system computes the variance: Counted − Expected on hand.
3. Approving the count creates an Adjustment movement for exactly that variance, linked back to the count.

Now shrinkage is measurable. You can total adjustments by product, by location, by month, by reason — and find out whether you have a theft problem, a receiving problem or a data-entry problem. If counts overwrite the stock level directly, all of that information is destroyed at the moment of the count.

Cycle counting — counting a slice of the catalogue continuously rather than everything once a year — falls out naturally: a view of products sorted by last-counted date ascending, with high-value items counted more often.

Where people go wrong

Editing stock levels. Covered above, but it is the big one. If anyone can type into on-hand, the number means nothing.

No location dimension. Then damaged goods, goods in transit and sellable goods all count as available, and you promise stock you cannot ship.

Deleting movements to fix mistakes. Never delete. Post a reversing movement. The audit trail is the entire value of the design, and deleting punches holes in it.

Reorder points set once and never revisited. A reorder point from two years ago is a guess about a demand pattern that has changed. Compute suggestions from actual velocity: average daily sales × lead time × safety factor.

Units of measure left implicit. You buy in cases, sell in units. If the model does not record which is which, you will order twelve times too much exactly once, and it will be memorable.

The views

• Reorder list — products where available < reorder point, sorted by days of cover ascending. Includes on-order so you do not double-buy.
• Stock by location — grouped grid, for pickers.
• Movement log — everything, newest first, filterable by product. Your answer to "why is this number what it is?"
• Adjustments this month — the shrinkage view. Review it monthly.
• Dead stock — products with no sale movement in 180 days and on-hand > 0. Cash sitting on a shelf.
• Cycle count queue — sorted by last-counted ascending, weighted by value.

Automations

1. Available drops below reorder point → notify purchasing. Not a weekly report; a nudge when it happens.
2. PO marked Received → create receipt movements for each PO line. This is where most manual error enters, so automating it pays immediately.
3. Sales order line marked Shipped → create the negative movement. Keeps stock and fulfilment in lockstep with no double entry.
4. Count approved → create the adjustment movement for the variance.

Together these mean nobody types a stock number, ever.

Start here

Three tables — Products, Locations, Stock movements — plus one rollup. That is a working system in an afternoon, and it is already better than any spreadsheet, because it can explain itself.

Add purchase orders when manual receipts become annoying. Add cycle counting when the annual count becomes painful. Add allocation when you start promising stock you have not shipped.

The one thing to get right on day one is the movements table, because it is the thing you cannot retrofit. Every other feature can be added later on top of an accurate history — but you cannot invent a history you never recorded.

If you are weighing building this against buying something packaged, the best inventory tools for small teams covers where each option genuinely fits. And if the things you are tracking are equipment rather than stock — issued to people, serviced, returned — the same event-based approach applies in track equipment and assets.