Free template
Agency Profitability Tracker Template
One row per engagement, and one honest answer to which of your clients is actually worth having. Covers agreed value, both margins, and cash position, because those are three different questions.
Sort by exposure margin, ascending. The engagement at the top is the one to look at, and it is rarely the one you expected.
What is in it
| Column | What it holds |
|---|---|
| Agreed value | What the client committed to, including any adjustment you have agreed since. Not the original proposal figure. |
| Approved cost | Team cost that has been accepted and is genuinely owed. |
| Pending cost | Work done or underway that has not been approved yet. |
| Invoiced | What you have actually billed. Usually less than agreed value. |
| Received | What has actually arrived. |
| Paid out | What you have actually paid the team. |
| Confirmed margin | Calculated. Agreed value less approved cost. |
| Exposure margin | Calculated. Also counting the work in flight. |
| Cash position | Calculated. Received less paid out. Can be negative on a profitable engagement. |
| Uninvoiced | Calculated. Agreed value you have not billed. The most commonly forgotten number here. |
| Outstanding | Calculated. Agreed value less received. |
The arithmetic
Already in the file. Here so you can check it rather than take it on trust.
Confirmed margin
Agreed value - Approved cost
Exposure margin
Agreed value - Approved cost - Pending cost
Margin percent
Exposure margin / Agreed value
Cash position
Received - Paid out
Uninvoiced
Agreed value - Invoiced
How to read it
The gap between the two margins is the warning
Confirmed margin uses cost that has settled. Exposure margin also counts work that has been done but not approved. On a healthy engagement they are close together. When they separate, work is piling up faster than it is being accepted, and that is worth knowing before it becomes cost.
Cash position and margin disagree, and both are right
An engagement can show a healthy margin and negative cash. You paid the team last month; the client pays on 45-day terms. That is a funding fact, not a profitability problem, and averaging them into one number destroys both.
Uninvoiced is where money quietly sits
Agreed value you have delivered against and not billed. It is nobody's job by default, it does not appear on any list of things to chase, and on a busy month it is frequently the largest number on this sheet.
Look at the whole client, not the engagement
An agency with three engagements for one client can have two profitable and one badly underwater, and the relationship still net positive. Or the reverse. Group the rows by client before deciding anything about the relationship.
Where a spreadsheet stops
- The inputs go stale. Every figure is a snapshot of when somebody last looked, and they will not all have been looked at on the same day.
- Currencies break it. Subtracting cost in one currency from revenue in another produces arithmetic that runs happily and means nothing.
- Nobody can see it but you. The moment a second person needs the numbers, either they get a stale copy or you get a second version of the truth.
- It cannot tell you why. A margin that dropped is a number. Which work caused it is a different question, and this sheet does not hold the answer.
How Bikabo does this
The same six numbers, without the monthly assembly.
The hard part of this sheet is not the arithmetic, it is sourcing the inputs. Approved cost comes from one place, invoiced from another, paid out from somebody’s memory. Most agencies fill it in once a quarter and it takes an afternoon.
Bikabo holds all nine inputs as one record, so the six calculated columns are simply true at any moment. The question stops being what the numbers are and becomes what to do about them.