A weekly stock summary for the owner: what to include

On Monday morning an owner wants to know three things: how much stock do I have, is anything wrong, and what needs my attention this week. He will not scroll through 800 rows to find out. A one-page summary with five or six items answers the questions and sends him to the detail only when something looks off.

The six items

1. Total stock value at book cost, with last week's figure and the change.
2. Stock by warehouse: value and share of the total.
3. Biggest gaps: the five SKUs with the largest gap value since the last count or spot check.
4. Slow movers: the number and value of SKUs with no movement for 90 days.
5. Shortage alerts: SKUs with less than two weeks of cover.
6. Open items: transfers in transit, returns waiting to be inspected and documents not yet posted.

Where each item comes from

All six come from one long table of stock by SKU and warehouse, with quantity, unit cost, the date of the last movement and average weekly shipments. A pivot table and a few formulas do the rest:

• Total and by warehouse: a pivot table summing value by warehouse.
• Biggest gaps: taken from the latest count table, sorted by the absolute gap value. If no count has been run recently, use the week's spot checks and say so in the header.
• Slow movers: filter rows where the last movement is older than today minus 90 days, then total their value.
• Shortages: quantity divided by weekly shipments, below 2.
• Open items: counted from the transfer and return registers.

Keep the formulas on a separate sheet. The summary sheet only shows results, so nobody can type over them by accident.

Build the workbook once and reuse it every week: paste the new stock export into the data sheet, refresh the pivot tables and the summary updates itself. The first build takes an afternoon; every later week takes about ten minutes, which is the difference between a report that survives and one that is dropped after a month.

Layout

Keep it to one page. Put the numbers at the top and the lists of at most five lines below. Use the same positions every week, so the eye learns where to look. Use red only for what needs action. Put the date and hour of the data in the header, and add one line, "what changed since last week".

An example with invented numbers

Total stock is 1.84 billion so'm, against 1.79 billion last week, an increase of 2.8 per cent that comes mostly from one delivery. By warehouse: main 62 per cent, market unit 21 per cent, marketplace warehouse 17 per cent. The biggest gap is SKU B-0412 at -14 pieces. Slow movers: 14 SKUs worth 96 million so'm. Shortages: 6 SKUs under two weeks of cover. Open items: 2 transfers in transit and 11 returns waiting. The owner reads this in two minutes and knows to ask about the 6 SKUs and about the 14 that are not selling. All the figures here are made up to show the layout.

Keep the numbers honest

Stock value is only as reliable as the last count. Add a small line under each warehouse showing the date it was last counted, so the owner can see which figures are fresh and which are old. Add the number of SKUs with a missing cost as well, because those rows count as zero and make the total look smaller than it is. A summary that admits what it does not know is more useful than one that looks perfect.

What to leave out

The temptation is to add a chart for everything. Resist it. A table of five lines is quicker to read than a chart of fifty bars, and the second page is where reports go to die. A trend line earns its place only if it answers a question; one small line showing total value over the last eight weeks is usually enough. If the owner asks for more detail, it can live in an attachment that the summary points to. Revisit the list every quarter and drop any item nobody has asked about.

InventoryHub's one-page summary shows the main gaps, the warehouses and products where they cluster, and suggested next steps, and the count tables come arranged so that you can feed a weekly summary like this from your own data.