Margin leakage: how small cost changes eat your profit, and how to catch it early
Ask most owners whether their margins are healthy and they will quote a number from the last time they checked properly. Ask when that was, and the answer is often "a while ago".
Margins rarely collapse. They leak. A supplier raises a cost price by a few cents and nobody updates the shelf price. A promotion ends but the discount stays in the system. A popular product is replaced by a similar one that costs more. None of these causes a bad day you would notice. Together they can quietly take a few points off your gross margin over a season.
This post explains where margin leaks come from and how to catch them while they are still small.
Why margin leaks are hard to see
No single day looks wrong. If the margin on a product line slides from 46% to 43% over eight weeks, each week is only a fraction of a point lower than the week before. Day-to-day noise from discounts, product mix and promotions is much larger than that, so the slide is invisible if you only look at recent days.
Totals hide the detail. Your overall margin can look stable while one category is sliding and another is improving. The leak only shows when you look at the right level: product, category or supplier.
Costs change in a different place from prices. Cost prices usually change when goods are received. Selling prices change when someone updates the POS. If those two jobs are done by different people, or not at all, the gap opens without anyone deciding it should.
The usual causes
- Supplier cost increases that were never passed on. The most common one. Often it is only a few cents per unit, applied by the supplier without a letter or a call.
- Promotions that did not end. A discount set up for a festive period and left running.
- Product substitutions. A product is out of stock, a similar one is sold instead at the old price, and the replacement costs more.
- Mix shifting to lower-margin items. Not a leak in prices, but the same effect on profit. Customers move towards cheaper or more heavily discounted lines.
- Manual price overrides at the till. Small overrides to close a sale, repeated often enough, add up.
- Wrong cost prices in the system. Sometimes the margin has not changed at all. The cost price was simply entered wrongly. That matters too, because every decision based on it is wrong.
How to look for it
The idea is simple: compare the margin per product line, week by week, over a long enough period to see a trend.
Here is an example for MySQL, assuming a sales_lines table with quantity, selling price and unit cost at the time of sale, and a products table with a category:
SELECT pr.category,
YEARWEEK(s.sale_date, 3) AS week,
ROUND(SUM(s.qty * s.price), 2) AS revenue,
ROUND(100 * (SUM(s.qty * s.price) - SUM(s.qty * s.unit_cost))
/ NULLIF(SUM(s.qty * s.price), 0), 1) AS margin_pct
FROM sales_lines s
JOIN products pr ON pr.id = s.product_id
WHERE s.sale_date >= CURDATE() - INTERVAL 16 WEEK
GROUP BY pr.category, YEARWEEK(s.sale_date, 3)
ORDER BY pr.category, week;
For SQL Server, group by the start of each week instead:
SELECT pr.category,
DATEADD(week, DATEDIFF(week, 0, s.sale_date), 0) AS week_start,
ROUND(SUM(s.qty * s.price), 2) AS revenue,
ROUND(100 * (SUM(s.qty * s.price) - SUM(s.qty * s.unit_cost))
/ NULLIF(SUM(s.qty * s.price), 0), 1) AS margin_pct
FROM sales_lines s
JOIN products pr ON pr.id = s.product_id
WHERE s.sale_date >= DATEADD(week, -16, CAST(GETDATE() AS date))
GROUP BY pr.category, DATEADD(week, DATEDIFF(week, 0, s.sale_date), 0)
ORDER BY pr.category, week_start;
Put the result into a chart per category. A leak looks like a staircase going down slowly, often starting on the week a delivery with a new cost price was received.
One important detail: use the cost at the time of sale, not today's cost price. Many systems store the cost on each sales line for exactly this reason. If yours only keeps the current cost, every past week will be recalculated with today's cost and the trend disappears.
Telling a leak from noise
Weekly margins jump around. A few ways to tell a real slide from ordinary noise:
- Look for direction, not size. Eight weeks in a row slightly lower is more meaningful than one week much lower.
- Check whether revenue changed too. A margin drop during a planned promotion is expected. A drop with steady prices and volume is not.
- Find the starting point. If the slide starts on a specific date, look at what was received from suppliers that week. That is usually the cause.
- Check the same weeks last year. Some categories always have lower margins at certain times of year.
Fixing it
Once you know where the leak is, the fixes are usually simple:
- Update selling prices for the products whose cost went up, or talk to the supplier about the increase.
- Turn off forgotten promotions and set an end date on every new one.
- Correct wrong cost prices so that future reports are right.
- Make cost changes visible. The single most effective habit is a short weekly list of every product whose cost price changed, put in front of whoever sets selling prices.
Catching it automatically
Slow changes are exactly what people are bad at noticing and computers are good at. In Cuedis, slow trends are watched separately from day-to-day surprises. A margin that loses a little every week is flagged as a trend even though no single day looks wrong, for example "The margin on Apex Cables slid from 46.6% to 43.6% over eight weeks." It then appears in the morning briefing with the records behind it, so you can see where it started.
Whether you use a tool or a weekly chart, the point is the same: margins do not usually fall, they leak. Looking at the trend per product line, every week, is how you catch the leak while it is still a few cents.