Costing & Margin Analysis
Product margin analysis for a 200-SKU portfolio
Problem
Product company unable to identify which SKUs were actually profitable due to inconsistent cost allocation.
Solution
Standard costing model in Excel with proper cost driver allocation, BOM-based cost build-up, and management dashboard.
Result
Identified 23% of SKUs as loss-making. Pricing review led to 8% margin improvement on the portfolio.
The situation
The company had been growing its product range steadily for three years. By the time we started working together, they had over 200 active SKUs across four product lines — but no reliable answer to the question: which products are actually making us money?
The problem wasn’t missing data. It was inconsistent methodology. Some SKUs had costs calculated from BOMs. Others had costs estimated at launch and never updated. A few were absorbing overhead at flat per-unit rates that hadn’t been reviewed in two years. The result was a P&L that looked healthy at the top level, but masked significant losses at the product level.
What I built
A standard costing model in Excel structured in three layers:
Layer 1 — Direct cost build-up Each SKU’s cost was rebuilt from its Bill of Materials: raw materials at current purchase prices, plus direct labour hours from the production routing. This immediately exposed SKUs where material costs had increased without a corresponding price review.
Layer 2 — Overhead allocation Manufacturing overhead was allocated using activity-based cost drivers (machine hours, setup frequency, batch size) rather than a single blanket rate. This is the part that matters most for accuracy — and the part that most companies get wrong.
Layer 3 — Contribution margin dashboard A one-page summary showing gross margin per SKU, sorted from highest to lowest, with traffic-light formatting. Filter by product line, channel, or customer segment.
What the analysis revealed
23% of SKUs were loss-making on a fully-loaded basis — not because they were priced wrong at launch, but because input costs had changed and the pricing hadn’t followed. Another 15% were technically profitable but contributing margins below the company’s cost of capital.
The commercial team used the output to run a structured pricing review. SKUs below threshold were either repriced, reformulated, or discontinued.
Key takeaways
- Standard costing isn’t glamorous, but it’s the foundation everything else depends on
- Overhead allocation methodology matters more than most people think
- A margin model is only useful if the commercial team can actually read it — design for the audience, not for accountants