Stock inventory management in Excel, audited

The key facts

• The structural fix: two tables — products and movements — with quantity as a formula, never typed in place.
• The habits that matter: a movement row per event, codes that match reality, a count that reconciles instead of overwriting.
• The three ceilings: concurrent editors, live deduction from orders, the accounting handoff — no formula fixes them.
• BSimple's role: import the sheet, run the record live — the trial on your own products.
• The full picture: how the inventory record works is the hub page for everything above.

Six ways a stock sheet lies — and the fixes

1. Quantities typed in place. Editing a quantity cell overwrites history; the sheet cannot tell error from adjustment. Fix: a movements table with quantity as a SUMIF formula per product. 2. No reason column. Movements without reasons cannot be audited — every row needs in/out, why, and who. 3. Codes that drift. "Widget A", "widget-a" and "Widget A (old)" are three products to a formula and one product to you. Fix: data validation against the product table. 4. Negative quantities hidden by formatting. A minus sign is information, not an error; let it show and investigate it. 5. Counts that overwrite. A stocktake that types new quantities deletes the evidence of the variance. Fix: record counted vs expected, then adjust as a movement. 6. No version discipline. "Stock-FINAL-v3.xlsx" is a lie with a filename. Fix: one shared copy or, at volume, a database-backed record.

Each fix is cheap; together they turn a list into a ledger — the template structure shows it built out, and scan entry in Excel removes the typing errors too.

The handover point — where Excel stops being enough

A well-built sheet still carries three ceilings, and they arrive on a schedule rather than all at once. Concurrency: the second person editing — shared files negotiate by overwrite, and the negotiation is lost. Live deduction: orders that commit stock should drain it that second; a sheet updated tonight is a record of yesterday, and overselling lives in the gap. The accounting handoff: re-entering sales and purchases into the books is a monthly tax paid in hours, and the errors it introduces are the month-end surprise factory.

We build BSimple, so weigh that: it is the record layer for wholesale, manufacturing, distribution and trade businesses — live quantities across locations, orders deducting on approval, purchase orders from demand, batch tracking, invoices handed to Xero or MYOB with payment status mirroring back, from $180/month AUD with everything in the core plans. Your sheet imports rather than retires: the product list moves across, the movements history maps to the record's native shape, and the printed views become system outputs. Excel for inventory, the full picture covers the craft, the stock management category the record layer, and the trial the handover test on your own file.