What a head-office stock table needs to contain

When head office asks for "the stock", it usually gets six files: one from each warehouse, each with its own columns, its own units and its own idea of what "available" means. Somebody then spends two evenings pasting them together. A better approach is to agree, once, on the shape of a single table that every warehouse fills in the same way. This article lists what that table needs.

One row per SKU per warehouse

Avoid a wide table with one column per warehouse. Use a long table: one row for each SKU in each warehouse. If you have three warehouses and 500 SKUs, the table has up to 1,500 rows.

This layout looks less friendly, but it is the one that works. You can filter by any column, build a pivot table in a few clicks, and add a fourth warehouse without changing the structure at all.

What head office wants to answer

Design the table backwards from the questions. Head office usually asks three things: how much stock do we hold, in total and in each warehouse, in units and in money; where is stock sitting without moving; and how much of it can actually be sold. Every field you ask for should help answer one of these. A column that answers none of them is a column the warehouses will fill in badly, because nobody can see why it matters.

Fields the table must have

• Snapshot date and hour, the same for every row of one report.
• Warehouse code.
• SKU, written exactly as in the item master.
• Product name.
• Unit of measure.
• Quantity on hand.
• Quantity reserved or in transit.
• Unit cost, and the currency in the next column.
• Stock value, calculated by a formula and never typed.

Nine columns are enough for the first version. Resist the temptation to ask for forty. Name fields consistently too: one name for one meaning, not "Stock" in one file and "On hand" in another.

Fields worth adding later

Once the basic table runs smoothly, these are the most useful extras:

• category or product group
• supplier
• batch or arrival date
• bin location
• barcode
• date of the last movement
• status, such as good, damaged, returned or quarantined

The status and last-movement columns are particularly valuable for head office, because they show stock that exists on paper but is not really sellable.

Traps when merging warehouse data

Most problems appear only after the files are combined:

1. Different units. One site counts in pieces, another in cartons.
2. Different snapshot times, so one warehouse reports Monday's stock and another Wednesday's.
3. SKUs spelled differently, so the same product appears as two lines.
4. so'm and dollars mixed in the same value column.
5. Empty cells in one file and zeros in another.
6. Numbers stored as text, which a pivot table ignores.
7. Totals typed over formulas by someone in a hurry.

Here is how the first one bites. SKU B-0360 has 120 pieces in the main warehouse, 40 pieces in the market unit and none in the third-party store. The market unit, however, reports in cartons of 10, so its file says 4. A pivot table adds 120 and 4 and reports 124 instead of 160. Nobody notices, because the total looks plausible. The fix costs nothing: one column for the unit, one for the number of pieces per unit, and a formula that converts everything into pieces before totals are made.

Who owns the final file

Name one person at the centre who merges the files, checks totals against each warehouse's own figure and sends the result to head office. Each warehouse is responsible for its own part only. Fix a rhythm too: the same weekday and hour each week, with every week's file saved under its date. After a few weeks you can compare snapshots and see which SKUs are growing, shrinking or standing still.

InventoryHub delivers count results in this spirit: a gap table by SKU, a per-warehouse summary and bin detail, arranged by SKU, warehouse and bin with a field guide, so head office can filter and total them without re-keying anything.