Track stock levels, manage reorder points, and optimize inventory control
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
The mistake that breaks inventory spreadsheets is typing the stock level. This template records movements and calculates the balance, so the file can always be reconciled against a physical count.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Date | SKU | Type | Qty | Reason | Balance |
| 2 | 2026-07-02 | TSH-BLK-M | Receipt | +120 | PO-2211 | 184 |
| 3 | 2026-07-03 | TSH-BLK-M | Issue | -32 | Order #10241 | 152 |
| 4 | 2026-07-04 | TSH-BLK-M | Adjust | -3 | Damaged | 149 |
| 5 | 2026-07-05 | TSH-BLK-M | Issue | -18 | Order #10266 | 131 |
Why each one is written the way it is
=SUMIFS(Movements[Qty], Movements[SKU], [@SKU])
Stock on hand as a calculation over the ledger. Because nothing is overtyped, any discrepancy against a physical count is a real event you can investigate.
=ROUNDUP(AVERAGE(Usage[Daily])*[@[Lead time]]*1.2, 0)
Reorder point: average daily usage over the lead time, plus a 20% safety buffer. Derived rather than guessed, and it updates as demand changes.
=IF([@[On hand]]<=[@[Reorder point]], "REORDER", "OK")
Reorder status as a formula, not a manual flag. A flag someone has to remember to set is a flag that will be wrong.
=SUMIFS(Movements[Qty],Movements[SKU],[@SKU],Movements[Type],"Issue")*-1/90
Average daily usage over the last quarter, the input to the reorder point.
A worked example with real numbers
A workshop counted 131 units of a SKU where the sheet said 149. Because every movement was recorded, the gap was traceable: eighteen units issued against an order that had been cancelled and never reversed. The fix was a single adjustment row with a reason, and the process change was to reverse issues on cancellation — neither of which is possible when the stock level is a typed number.
Tab by tab
| Tab | Contents |
|---|---|
| Movements | Append-only ledger: receipts, issues and adjustments. |
| SKUs | Part master with reorder point, lead time and supplier. |
| Stock | Calculated on-hand, reorder status and days of cover. |
| Counts | Physical count results and the resulting adjustments. |
| README | The rule: never overtype a balance, always add a movement. |
What is included, and what to look at next
Specific to this workbook, not generic
Comfortably a few thousand in Google Sheets and tens of thousands in Excel with Power Query. Past that, or with multiple locations and transfers, see our comparison — the answer is usually inventory software with a spreadsheet reporting layer.
Because the moment someone overtypes it during a count, the audit trail is gone and you cannot tell a counting error from a theft from a data-entry slip.
In Google Sheets, yes — that is its main advantage here. In Excel, use one file per location and consolidate, or accept a single owner for the file.
"This inventory management system has revolutionized our stock control. We've reduced stockouts by 95% and cut excess inventory costs by 30%."