Excel for inventory management

The key facts

• The structure that works: two tables — products and movements — with quantity as a formula, never typed in place.
• The formulas that matter: SUMIF running quantities, low-stock flags against reorder levels, data validation on entries.
• The three ceilings: concurrent editors, live deduction from orders, and the accounting handoff — none is a formula problem.
• BSimple's relationship: import, not rivalry — your sheet starts the record; the trial runs it live.

The structure that makes Excel work

The templates that fail share one flaw: a quantity column edited in place. Every change is an overwrite, nothing carries evidence, and the sheet degrades into opinion. The structure that works splits into two tables: a product list (code, description, cost, reorder level) and a movements ledger (date, product code, quantity in or out, reason, operator). Quantity becomes a formula — SUMIF of movements per product — and the sheet turns into a ledger: every number traceable to the rows that justify it, discrepancies visible instead of invisible.

On top of that spine, three formulas pay rent: the running quantity; a low-stock conditional flag when quantity dips under the reorder level; and data validation on the movement rows so codes must exist and quantities must be sane. Add a barcode column and a keyboard-mode scanner, and the sheet becomes scan-entry capable — still free, still a spreadsheet, markedly harder to fat-finger.

The three ceilings — and why no formula fixes them

Concurrency. The moment two people edit, the file becomes a negotiation: shared copies, stale overwrites, merged versions nobody trusts. Excel's co-authoring helps documents, not ledgers that move money. Live deduction. Orders that commit stock should deduct it in the same second — a sheet updated at end-of-day is a record of the past, and overselling lives in the gap. The accounting handoff. Every sale and purchase eventually needs to be a bookkeeping record; re-keying it manually is the hidden monthly cost of the free option. Each ceiling is structural: they are the reasons database-backed inventory systems exist, not features you were missing.

Until those ceilings arrive, Excel is the correct price — and the free-in-Excel-and-PDF guide plus Excel-based inventory systems cover the template craft in depth.

From sheet to record, keeping your work

We build BSimple, so weigh that: it is built to work alongside Excel, not against it. Your product table imports — no re-typing — and the movements ledger becomes the system's native shape: every movement attached to its evidence, quantities live across locations, orders deducting on approval, purchase orders raised when reorder levels are touched, and invoices handed to Xero or MYOB with payment status mirroring back. The views you built by printing the sheet become system outputs. Plans run $180/$250/$399 per month AUD with everything in the core plans; the trial imports your real sheet so the comparison runs on your own products — the inventory management pillar shows what the record becomes.