Client Profitability Template for Agencies and Consultancies
A margin-per-client view from two exports you already have: a Xero P&L by tracking category and a timesheet export. Shows margin per client, profit per head and over-servicing hours in under an hour, with no new software.
What the Template Shows You
Agencies know their revenue by client. Few know their margin by client, because the biggest cost, people, sits in one payroll line. The template puts it back against the clients those people served.
Margin per Client
Revenue, delivery cost, direct costs and margin for every client, in pounds and as a percentage of revenue, sorted so the loss-makers are at the bottom. Margin below zero shows in red, below your own agency average in amber.
Profit per Head
Total gross margin divided by fee-earning headcount for the period, with a monthly line so you can see whether a hire paid for itself.
Over-Servicing Hours
Hours logged against each retainer or project compared with the hours agreed, and what the extra hours cost you at the team's cost rate. Shows which accounts and which service lines quietly eat margin.
Utilisation by Person and Team
Billable hours as a share of available hours, against the target you set. No benchmark figures are supplied; you compare against your own target and your own history.
Reconciliation Checks
Revenue total ties to the Xero P&L, hours total ties to the timesheet export, and every person in the timesheet has a cost rate. All three must show OK before the summary is trusted.
One-Page Summary
A single printable tab with the five outputs above, built for the founder or MD rather than a board, so it can go in the monthly leadership meeting as it is.
Two Exports and One Typed-In Tab
No API connections and nothing to install. Paste-in exports only, so it works for every firm on day one.
Xero P&L by Tracking Category
Reports, Profit and Loss, compare by tracking category (set up as Client; a second category for Service Line is optional), last 12 months with monthly columns, exported to Excel. If you have no tracking categories yet, the template includes a one-page set-up note.
Timesheet Export
One row per time entry: person, client, service line (optional), date, hours and a billable flag. Any timesheet tool works, and so does a spreadsheet, as long as those columns exist. A mapping tab lets you rename your column headers to match.
People Tab
The only manual input. Person, role, fully loaded annual cost (salary plus employer costs), contracted hours per year and whether they are fee-earning. The template flags anyone who appears in the timesheet but not here.
Settings
Period start and end, your target utilisation, and the hours or fee agreed in each client's retainer or contract where you have them. Agreed hours are what make the over-servicing view work.
How to Use the Client Profitability Template
Three steps from two exports to a margin-per-client table you can take into the next leadership meeting.
Export From Xero and Your Timesheet Tool
Run the P&L by tracking category for the last 12 months and export it. Export the timesheet for the same period. Paste each into its own tab. The mapping tab handles different column names.
Fill in the People Tab
Add each person's fully loaded cost and contracted hours. The template works out a cost rate per hour for each person and multiplies it by the hours they logged against each client. This is the step Xero cannot do on its own.
Check the Reconciliation, Then Read the Summary
When revenue, hours and people all show OK, the summary tab is ready. Expect one surprise: a well-known account losing money, or a small one that turns out to be your most profitable. Then decide what to reprice, rescope or stop.
The Arithmetic Behind the Summary
Per client
- Cost rate per person = fully loaded annual cost divided by contracted hours
- Delivery cost = hours logged on the client multiplied by each person's cost rate
- Direct costs = freelancers, media and pass-through costs coded to the client in Xero
- Margin = revenue from the tracking category P&L less delivery cost less direct costs
- Over-servicing hours = hours logged less hours agreed, valued at the blended cost rate
Across the agency
- Profit per head = total gross margin divided by fee-earning headcount
- Utilisation = billable hours divided by available hours, pro rated to the period
- Unallocated hours shown separately so the total still ties to the timesheet
- No benchmark figures built in; your own history is the comparison
- No tax, WIP or debtor days; those sit in the cash flow work, not here
This is the same view we build in the first month of every agency engagement, before anything else. Once it exists, pricing, scope and hiring conversations change, because they stop being about revenue and start being about margin. The agency profit margin guide explains how to read the numbers and what moves them.
Want the template built around your numbers?
Free 15-minute call. Bring a timesheet export and your Xero login if you want a first read of margin per client.