Inventory Reporting Rebuild

A family-owned distributor supplying hardware and garden products to independent retailers ran its buying decisions on a spreadsheet ritual: at each month's end, two staff spent three days merging nine separate workbooks — one per product category, several maintained by the sales reps themselves — into a stock and sales summary for the owner. The numbers were always late, sometimes wrong, and had quietly stopped driving decisions. The owner described the situation as "flying on instruments we don't believe."

The problem

The damage was specific and growing. Buying decisions were made on instinct ahead of the report, so seasonal stock was ordered late and clearance decisions came after the season. Two category workbooks contained conflicting on-hand figures for the same SKUs, and nobody knew which was right. The three-day merge also depended entirely on one staff member's private knowledge of which columns to trust — knowledge that left with her during a family leave period and took six weeks to reconstruct.

The obstacles

The data itself was the first obstacle: SKU codes inconsistent between systems, historical records holding three different naming conventions for the same supplier, and rep-maintained sheets with formulas that broke silently. The second obstacle was cultural — the sales reps had real knowledge embedded in their sheets, and an abrupt centralisation felt like distrust. The third was cash: the owner had been quoted an enterprise inventory platform that cost more than the problem justified.

What we did

We began with a data audit that reconciled the nine workbooks against the accounting system and the warehouse counts, established a single SKU register, and documented the reconciliation rules. Rather than a new platform, we consolidated reporting into a structured data model fed by exports the existing systems already produced, with automated category-level intake replacing the manual merge. Each sales rep's category view was rebuilt from the same source — so their knowledge was preserved in the system, not in private files. Old and new reporting ran side by side for one full cycle, discrepancies chased and explained, before the old workbooks were retired.

The result

The monthly report cycle fell from three days of staff time to roughly twenty minutes of review, and the pack is now available weekly rather than monthly — which changed what it is used for: buying decisions moved onto the numbers. Stock cover improved visibly within two quarters as over-ordering and late seasonal buying were corrected against real sales rates. The knowledge that used to leave the building with one staff member now leaves the building with the business.

Engagement type: Defined Project (data audit, consolidation, reporting build), retained monthly for reporting maintenance.

The lessons we carry from this engagement

This project reshaped how we approach reporting rebuilds everywhere. Lesson one: reconcile before you consolidate. The two conflicting workbooks were the real story — until they were reconciled against warehouse counts, every downstream plan rested on unknown numbers, and the reconciliation itself took only four days. Lesson two: preserve the rep's view, not the rep's spreadsheet. What made the centralisation acceptable was rebuilding each sales category view from the shared source — the knowledge survived, the fragility did not. Lesson three: run old and new side by side for a full cycle. One complete trading cycle, with every discrepancy chased and explained, is what converted the owner from hopeful to confident; cutting that step to save a fortnight would have been the falsest of economies.

The ongoing result also deserves mention: the maintenance retainer we proposed was deliberately light — a monthly reconciliation check and an annual definition review — because the design goal was a system the client's own team could keep honest. Reporting you cannot maintain yourself is just a dependency you have purchased.