Monitor real-time inventory movement and optimize warehouse operations
✓ Free to download · ✓ Excel 2016+ and Google Sheets · ✓ Custom version in 24h
A stock list tells you what you have. A warehouse sheet has to tell you where it is, which means location has to be first-class data rather than a note in a comment.
The actual columns, with sample rows
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | SKU | Zone | Bin | Qty | Last count | Variance |
| 2 | TSH-BLK-M | A | A-04-2 | 88 | 2026-06-30 | 0 |
| 3 | TSH-BLK-M | C | C-11-1 | 43 | 2026-06-30 | -2 |
| 4 | MUG-CER | A | A-01-3 | 210 | 2026-06-30 | +4 |
| 5 | HDY-GRY-S | B | B-07-4 | 36 | 2026-06-30 | 0 |
Why each one is written the way it is
=SUMIFS(Locations[Qty],Locations[SKU],[@SKU])
Total on hand across bins. Stock split across two zones is the normal case, and a single-location sheet cannot represent it.
=[@[Counted]]-[@Qty]
Count variance per bin. Recording it per bin rather than per SKU is what tells you whether you have a counting problem or a put-away problem.
=TEXTJOIN(", ",TRUE,IF(Locations[SKU]=[@SKU],Locations[Bin],""))
Every bin holding a SKU, as one readable string for the pick list.
=COUNTIFS(Locations[Zone],[@Zone],Locations[Qty],">0")/COUNTIFS(Locations[Zone],[@Zone])
Bin occupancy by zone. Below about 60% usually means the layout, not the space, is the constraint.
A worked example with real numbers
A warehouse recorded stock by SKU only. A fast-moving item showed 131 units available and pickers reported it empty — 88 units sat in zone A and 43 in an overflow bin in zone C that no pick list referenced. Adding bin-level records surfaced eleven similar splits in the first week and removed a recurring class of false stockout.
Tab by tab
| Tab | Contents |
|---|---|
| Locations | SKU, zone, bin and quantity — one row per bin. |
| Movements | Put-aways, picks and transfers between bins. |
| Counts | Cycle-count results with per-bin variance. |
| Layout | Zone and bin reference with pick sequence. |
| README | The bin naming convention and how to add a zone. |
What is included, and what to look at next
Specific to this workbook, not generic
Zone-aisle-level, fixed width, so they sort in pick order. "A-04-2" sorts correctly; "A4-2" does not once you reach aisle 10.
Continuously, weighted by value and movement: A items monthly, C items annually. A full annual count finds errors up to twelve months after they happened, which is too late to explain them.
Yes — sort the required lines by the bin sequence on the Layout sheet. Sequenced picking is usually worth 15–25% of walking time in a warehouse of any size.
"This warehouse control system has revolutionized our operations. We've improved picking efficiency by 45% and virtually eliminated location errors."