Inventory & margin reporting
Two years of monthly and quarterly reporting for a commercial team working a 10,000-SKU catalogue through 20+ field agents, and what happened once we stopped ranking products by revenue alone.
The problem
When I joined, the commercial team already had reports. The issue was that they had many of them, each built by a different person, each pulling from 1C at a slightly different moment, and none of them agreeing. A category manager preparing for a supplier meeting would open two files and find two different figures for the same month. The usual resolution was to ask whoever built the file, which meant the reporting cycle depended on people being available rather than on the data being right.
The second problem was slower and more expensive. Purchasing decisions leaned on last month's sales volume. That reads reasonably until you notice it treats a fast-moving low-margin item and a slow high-margin one as roughly equivalent, and it says nothing at all about how long capital sits on a shelf before it converts. Stock kept accumulating in categories nobody had flagged, because nothing in the reporting was designed to flag them.
What we had to work with
Everything sat in 1C: sales documents, stock movements, supplier invoices, and a master data table that had been edited by hand for years. Roughly 10,000 active SKUs, with maybe 2,000 more that were technically alive but hadn't moved in eighteen months. Twenty-odd sales agents filing their own documents, with regional naming conventions that had never been reconciled.
Before any analysis was possible, master data had to be cleaned:
- Duplicate SKUs created when an agent typed a product name instead of selecting it
- Category assignments that contradicted each other between the sales and purchasing sides
- Unit-of-measure mismatches — the same product invoiced by piece in one region and by pack in another
- Supplier records split across two or three entries with slightly different legal names
This took about six weeks and produced no visible output, which is worth saying plainly: it is the part of the work that nobody sees and everything else depends on.
Approach
I settled on one extraction, one model, one pack. A single set of SQL queries pulled sales, stock and purchasing on a fixed schedule. Power Query shaped it into a small star model — a fact table for movements, dimensions for product, supplier, region and date. Every report the team used was then built off that model, so two people asking the same question got the same number by construction rather than by coincidence.
On top of that sat the ABC analysis. Products were ranked on contribution margin rather than revenue, then cross-tabulated against turnover speed. That second axis is what made it useful — it separated the slow items that earn their shelf space from the slow items that are simply capital sitting still.
The A/B/C split was not the interesting output. The interesting output was the C-class items with healthy margins that purchasing had been quietly deprioritising for two years because their revenue numbers looked small.
What the data showed
About 8% of the catalogue accounted for a little over half of total contribution margin, which is roughly what you would expect. The unexpected part was the tail. Close to a fifth of stock value was tied up in items that had turned over less than twice in a year, and most of it was concentrated in three categories that had never appeared in a management conversation, because each individual SKU was too small to notice. Only in aggregate did they add up to a real number.
We also found a reconciliation gap on the supplier side. Rebates agreed at the start of the year were being calculated against volumes the suppliers reported, not volumes we could verify. Once purchase documents were matched line by line against agreed terms, a meaningful share of claims turned out to be under-recovered.
Outcome
Excess stock came down by 12% over three quarters, mostly through markdowns and order stops on the slow tail rather than anything clever. The monthly pack went from four days of assembly to about half a day, and the questions in the review meeting changed — less time spent arguing about whose figure was correct, more spent on what to do about the categories the quadrant view kept surfacing.
The part I would do differently: I built the model to answer the questions the team was already asking. It took another six months before I understood which questions they had stopped asking because the old reporting could never answer them.