In ten years of commercial finance work — first inside Volkswagen, Mercedes and VW Financial Services, now with UK SMEs through C8 — I have never once done a customer-level margin analysis that didn’t change at least one opinion the founder held about the business.
Usually it’s the same pattern. The biggest customer, the one they’d lose sleep over losing, turns out to be the worst-margin customer in the book. All the volume. Almost none of the profit. Nobody had broken the numbers apart to see it, because at the P&L level, the margin looks fine.
This post walks through how to do the analysis yourself — in one weekend, with one spreadsheet, without any specialist software.
Why your “gross margin” is a lie
When your accountant hands you the annual accounts, they’ll show you one gross margin. Revenue minus cost of goods sold (COGS) divided by revenue, across the whole business.
That single number is an average. It hides enormous variation. In a typical UK services SME I might see:
- Headline gross margin: 42%
- Client A (biggest client, 30% of revenue): 18% margin
- Client B: 51% margin
- Client C (smallest of the top 5): 64% margin
If you treat them all as equally valuable because you’re averaging, you’ll make bad decisions. You’ll keep discounting Client A to retain them. You’ll under-resource Client C. And you’ll never understand why your profit isn’t growing in line with your revenue.
What you need to run the analysis
Three things:
- A list of every invoice you’ve raised in the last 12 months, with customer name, date, and amount. Most accounting software (Xero, QuickBooks, Sage) exports this in two clicks.
- A list of your direct costs that scale with delivery — typically: delivery staff time, subcontractor fees, materials, per-client licences, shipping. NOT rent, insurance, marketing, or director salaries. Those are fixed overhead and get allocated differently.
- A spreadsheet. Excel or Google Sheets. That’s it.
The method
I’ll write this for a services business because that’s where I see this blind spot most often. Product businesses follow the same logic with stock movement data.
Step 1: In your invoice export, add a column for the person (or people) who delivered the work. If you track timesheets, great. If you don’t, your team’s reasonable estimate for the last 3 months is good enough for an initial view — we’re not writing audited accounts.
Step 2: Work out a loaded hourly cost for each delivery person. For an employee, this is their salary + employer’s NI + pension + a modest allocation of benefits, divided by the working hours they actually deliver in a year (realistically around 1,500 hours for a UK employee, not 2,000). For a subcontractor, it’s just their invoice rate.
Step 3: For each client, tot up the hours delivered × loaded cost. That’s your direct cost of serving that client over the period.
Step 4: Revenue minus direct cost = contribution margin for that client. Divide by revenue to get contribution margin %.
Step 5: Sort the list by contribution margin % descending. Stand back from the spreadsheet. Take a breath.
What you’re looking for
Three questions the numbers will answer:
- Which clients have a contribution margin below your headline gross margin? These are the ones you’re subsidising. Often your biggest.
- Which clients are above? These are the ones to clone.
- Which services or product lines have you priced without looking? If the 18% margin client is on the same price list as the 64% client, something is off in the pricing.
What to do with it
You don’t need to fire low-margin clients. You do need to decide, consciously, how you’re going to handle them:
- Raise the price at renewal — and be specific about what you’re raising it to, not just by a percentage. Aim for a contribution margin in line with the rest of the book.
- Change the scope — deliver less of the time-consuming stuff for the same fee.
- Let them go — gracefully, at renewal, if they won’t move.
- Use the insight for new business — stop winning work at the low end by default.
The single mistake I see founders make after their first customer-margin analysis is doing nothing with it. Knowing which clients are unprofitable and carrying on regardless is worse than not knowing. The numbers are useful only when they change a decision.
If you want the template
I’ve built this analysis hundreds of times. If you’d like the Excel template I use with C8 clients — pre-structured, with contribution margin formulas built in — you can grab it for free. Takes about 20 minutes to drop your data in and see the results.