Analysing count gaps with an Excel pivot table

Once the count table exists, it is the analysis that takes the time. With thousands of rows of differences, where do you look first? Many people scroll row by row or filter again and again, and still cannot see where the problems cluster. An Excel pivot table can total the gaps by warehouse, category and bin in a few steps, and you can then click through to the exact rows behind any figure. Here is the sequence.

Tidy the source data first

A pivot table is strict about its source. Tidying first saves most of the trouble:

• One header row only, with data rows beneath it: no merged cells, no blank rows and no subtotal rows.
• One row per SKU in one warehouse and one bin.
• Code columns such as SKU, barcode and bin set to text; quantity and value columns must be real numbers, not text that merely looks like numbers.
• A few helper columns: gap quantity (counted minus book), gap value, the absolute gap value (using the ABS function) and category.

Then select the whole data range and press Ctrl+T to convert it to an Excel table. New rows will then be picked up automatically when the pivot table is refreshed.

Build the pivot table

1. Click any cell in the data, choose Insert, then PivotTable.
2. Place it in a new worksheet.
3. Drag "Warehouse" to Rows and put "Category" beneath it.
4. Drag "Gap value" and "Absolute gap value" to Values, both set to Sum.
5. Drag any other column to Values as a Count, to see how many lines each group has.

Always put the gap value and its absolute value side by side: the first is the net gap, the second the gross gap. Surpluses and shortages cancel, so the net alone can hide problems.

A worked example

Suppose three warehouses and a pivot table that shows:

• Main warehouse: absolute gap 2,100 dollars, net gap minus 900.
• Market unit: absolute gap 5,600 dollars, net gap minus 4,900.
• Third-party store: absolute gap 1,800 dollars, net gap plus 1,500.

The absolute gaps total 9,500 dollars and the net gap is minus 4,300. The market unit holds 5,600 of 9,500, about 59 percent, so it is the first to investigate. Expanding its categories shows phone accessories alone at 3,900 dollars, nearly 70 percent. Double-click that figure and Excel lists, in a new sheet, every row that makes it up. Often a few SKUs carry most of the value.

Once you have found the rows, follow up in three steps: recount the bins holding those SKUs, check for data causes such as a duplicate SKU, bleed between bins or an unbooked return, and after correcting, refresh the pivot table to see how far the gross gap has fallen. Each round leaves figures you can compare.

Four views worth keeping

1. Warehouse by category: shows at once which warehouse and which product group has the biggest gap.
2. Bin ranking: put bins in Rows, open the filter, choose Value Filters and then Top 10, to keep only the ten bins with the largest absolute gap.
3. Cause category: if there is a cause column, total by cause to see how much is a data problem and how much is a real stock problem.
4. SKU ranking: sort by absolute gap value, largest first, which gives the SKU recount list mentioned above.

Handy operations

• In Value Field Settings, show values as a percentage of the grand total to see shares at a glance.
• Insert slicers for warehouse and category and switch with one click, which suits a live meeting.
• Double-click any figure to see the detail rows behind it.
• After the data changes, right-click and choose Refresh. A pivot table does not update itself, and this is the most common omission.

Common traps

• The values area shows a count instead of a sum: some cells in the number column are text, so convert the column first.
• A blank header cell, or two columns with the same name, makes the pivot table complain.
• The source is a plain range, so new rows are left out and refreshing changes nothing.
• Only looking at the net gap, so surpluses and shortages cancel and everything seems to be zero.
• Mixed units, pieces in one warehouse and cartons in another, make the totals meaningless.

Most of the skill is in preparing the source: one header, codes as text, numbers as numbers, and net and gross gaps side by side. InventoryHub delivers a gap table arranged by SKU, warehouse and bin, with a field guide, ready to filter or to turn into a pivot table.