Stock inventory management system in Excel

The key facts

• The one design rule: log every stock change as a row in a movements table and derive on-hand quantities with SUMIFS — never type a stock total by hand.
• The minimum build: Products, Movements, and (optionally) ReorderLevels sheets, one formula for stock on hand, conditional formatting for low stock.
• The four walls: multiple simultaneous users, no audit trail without discipline, manual stocktake reconciliation, and no accounting handoff.
• The honest exit: BSimple works alongside Excel — import your product list — and keeps the record live once the spreadsheet stage breaks.
• The full picture: how the inventory record works is the hub page for everything above.

The build that stays accurate

Sheet 1 — Products. One row per SKU: code, description, unit, cost. Resist extra columns; the movements carry the history.

Sheet 2 — Movements. One row per stock event: date, product code, location, signed quantity (positive in, negative out), reference and reason. Purchases, sales, adjustments, wastage — all the same shape. This table is the system; treat it as append-only.

The derived column. Stock on hand lives in a SUMIFS over the movements, never in a typed cell: =SUMIFS(Movements!D:D, Movements!B:B, A2) where A2 is the code and D the signed quantity. The moment someone edits a derived number to "fix" it, the system has become a diary.

Low-stock formatting. A ReorderLevels sheet joined by lookup, and conditional formatting to highlight derived stock beneath the floor. That is the whole control system — the free-in-Excel template route and the stocktake patterns extend it.

What the spreadsheet does well, honestly

For one location, one careful person, and a weekly habit of reconciliation, the movement-based sheet is a genuine system: transparent, adaptable in seconds, and free. It teaches the discipline that later software enforces — because the owner can see the movements, drift gets noticed, and the derived figure earns trust. Businesses that skip this stage usually also skip learning why their numbers went wrong; the spreadsheet at least makes the mistake visible in a filterable row.

The failure arrives with the second person. Two editors in one workbook means file locks, overwritten rows and versions named "final_v3" — and no amount of formula skill fixes a concurrency problem.

The four walls, in the order you will hit them

Concurrency — a second editor breaks the single-file model; the fixes (shared workbooks, online sheets) reintroduce it as slow, fragile or conflict-prone.

Audit — Excel rows can be edited silently; tracking changes is optional and trivially defeated. A business that needs to explain a stock figure to an auditor, an insurer or a tax office needs a record that cannot be quietly rewritten.

The count — stocktakes mean exporting, counting, and hand-reconciling discrepancies back into movements, every time. What a real inventory system does with that same event is record it as a correction with a reason.

The handoff — invoices and costs still get re-keyed into accounting, because a spreadsheet is not a system the accounting platform can talk to. That re-keying is the monthly tax of the free route; the worked comparison totals it honestly.

Stepping up without losing the work

The movement sheet is a specification, not a dead end: the product list imports directly, the movement history explains how the business actually works, and the reorder levels become configuration. BSimple imports from Excel and keeps the model — products, locations, movements, derived stock — as a live multi-user record with purchasing, customer ordering portals and invoices handed to the accounting system. The trial runs alongside your sheet long enough to verify the counts agree before anything is switched off.