Set up products and opening stock
An inventory spreadsheet needs a reliable transaction process as well as a stock list. Begin with one stable SKU per product and one quantity unit per SKU. A carton of 24 pieces and one piece are different units; convert them before posting movements to the same stock balance. Keep identifiers such as 000123 as text.
Choose the workbook’s country display preset before entering costs. Its ten presets change date and currency display; they do not convert prices, set tax rules or change Excel’s interpretation of a typed date. Enter opening stock once at a clear starting date, rather than recording the same receipt both as opening stock and as a movement.
Convert cartons and pieces before recording stock · Check identifiers without losing leading zeros
Record receipts and dispatches
Record each real receipt, dispatch or adjustment once, with its date, SKU, quantity and reference. In the v3 kit, use the receiving workflow for purchase-linked receipts and review its suggested movement before recording it. A preparation sheet does not post the receipt for you. Keep the same purchase reference and line association so the open balance is reduced by the intended receipt.
| Event | Change | On-hand balance |
|---|---|---|
| Opening stock | 100 | 100 |
| Purchase receipt | +24 | 124 |
| Customer dispatch | −18 | 106 |
The result is 100 + 24 − 18 = 106 pieces. A purchase order for another 60 pieces is on-order stock, not an additional receipt. If the entered history is invalid or incomplete, review it before relying on the derived stock balance.
Review incoming stock and reorders
Review on-hand stock together with open purchase quantities and backorders. The workbook’s arrival warnings help identify a shortage before an expected receipt, while the reorder sheet applies its demand, lead-time, review-period, pack and MOQ assumptions. An order suggestion is not purchase approval.
Work through the reorder formulas and pack rounding · Check several SKUs with the free planner
Prepare a count and reconcile
Prepare the book quantity at a specific time, control or separately record movements during the count, and enter the physical count in the same unit. If the book snapshot is 106 pieces and the count is 104, the variance is −2 pieces. Recount and document the reason before approving an adjustment. Never alter an opening balance just to hide a later difference.
The kit keeps count preparation, the snapshot and a proposed adjustment separate. Its stock history and warnings help you review the records; you still decide whether and how to record an adjustment.
Compare a stock snapshot with a physical count · Plan a repeatable cycle count workflow
Choose a list, sample or full kit
| Option | Use it for |
|---|---|
| Free inventory CSV | A plain stock list with 10 columns, without formulas. |
| Free working Excel sample | Try formulas with synthetic data: 10 SKUs, 10 purchase lines, 100 movements and 20 count rows. |
| AUD29 full Excel kit | Blank v3 template with overview: 200 SKUs, 200 purchase lines, 10,000 movements and 2,000 count rows. |
The full price includes applicable taxes. Prepared ranges do not expand automatically. This macro-free workbook uses whole-number stock quantities and one stock pool per SKU; it is not a multi-user, bin/lot/serial, FIFO, tax or ERP synchronization system. Keep a dated backup before replacing business data or adopting an upgraded template.
Continue with the right tool
Browse all warehouse guides · Compare the Excel kit and try its free sample