Laying out a gap report so finance can read it at a glance

The same set of count differences can be read in three minutes or left a reader lost after half an hour, depending only on layout. When finance or the owner opens the file, they are usually asking the same questions: how much money is the gap, which products does it sit on, why, and what happens next? Laying out a gap report means answering those questions in that order.

Put a summary on the first sheet

Do not make the reader face thousands of detail rows straight away. The first sheet holds one small block:

• total book value and total counted value
• net gap: all differences added together
• gross gap: all differences added together after removing the signs
• the number of lines with a gap, and their share of all lines
• the ten SKUs with the largest gap value

Show the net gap and the gross gap together. Suppose one group of goods is 4,000 dollars over and another is 4,000 dollars short. The net gap is zero, which looks reassuring, yet two things need an explanation. A net figure alone hides this kind of offsetting.

Column order on the detail sheet

Arrange the columns in reading order, from "what is it" to "what do we do":

1. Identification: SKU, product name, unit, warehouse, bin
2. Quantity: book quantity, counted quantity
3. Difference: gap quantity, gap percentage
4. Value: unit cost, gap value
5. Explanation: cause category, owner, status

Agree the sign convention beforehand. The usual choice is counted minus book, so a negative number is a shortage and a positive one a surplus, and it should be a formula, never a typed number.

Make the cause column a drop-down list rather than free text. Five or six categories are enough:

• receipt or return not booked
• wrong bin or bleed between bins
• duplicate SKU or inconsistent code
• counting or keying error
• confirmed shortage or confirmed surplus
• to be investigated

Fixed categories make the later summary obvious: of the gaps in each warehouse, how many are data problems and how many are real stock problems.

Sort by value, not by SKU

Many reports run in SKU order. It looks tidy, but it buries the lines that matter in the middle of thousands. Sort by the absolute gap value, largest first, so the biggest problem is at the top.

You can add a "cumulative share" column showing how much of the total gap has been reached by each row. Suppose row 25 reaches 70 percent: then those 25 rows are where to start. That share differs for every table, so look at your own data. Lines with tiny gaps can sit at the bottom with a status such as "within tolerance". Tolerance need not be the same everywhere: set it tight for high-value goods and looser for cheap bulk items, and state the rule on the summary sheet.

Use colour sparingly

More colour means less emphasis:

• At most two colours, one for shortage and one for surplus.
• Colour only the lines beyond tolerance, and leave the rest on a plain background.
• Keep the plus and minus signs, so the report still reads in black and white and does not depend on colour alone.
• Put a small legend beside the header. People's habits with red and green differ, and a note is more reliable than a guess.

Small details that affect reading

• Freeze the header row and turn on filters, so column names stay visible while thousands of rows scroll.
• Use thousands separators in value columns and state the currency in the header.
• Format code columns such as barcode and bin as text, so leading zeros survive and long numbers do not turn into scientific notation.
• Hide helper columns the reader does not need, rather than deleting them.
• Set the sheet to landscape and fit it one page wide before printing.
• Put the date and version in the file name, for example gap-report_2025-10-15_v2.xlsx.

A test from the reader's side

When you finish, give the file to a colleague who took no part in the count and no explanation. Ask three questions: what is the total gap, which warehouse holds the biggest problem, and what is the first thing to do? If the answers do not come within a minute, the layout still needs work.

InventoryHub's inventory data reports are summarised by warehouse, category and bin, with a list of the SKUs with the biggest gaps and a warehouse-by-warehouse comparison, ready for head office or the owner to read without further tidying.