Which of your projects actually made money?

Most services firms know their revenue by client and almost none know their margin by project. The reason is not laziness. It is that the cost of a project is people's time, and time is the one cost that never arrives as an invoice from someone else.

So the numbers that would answer the question sit in three different places. What you charged is in the accounting system. How long it took is in a timesheet, if anyone fills one in. What those hours cost is in payroll, at a salary figure that understates the real cost by 15% or more. Nobody joins them up, and the firm runs on a rate card that describes an intention rather than a result.

This tracker joins them up. It is a spreadsheet rather than a system, which is the right size for a firm of ten to sixty people that wants an answer this month rather than a software project.

Rates

One row per person who delivers work. Fully loaded annual cost, paid hours, utilisation and card rate go in; cost per paid hour, billable hours and cost per billable hour come out. The last of those is the number most firms have never seen, and it is always higher than the salary-based figure they were using.

Projects

One row per project or retainer, with agreed fee, estimated hours, hours delivered, what you invoiced. It returns delivery cost, margin, margin percentage, realisation against your card rate, and the variance against your own estimate. A status column flags anything thin.

Utilisation

Billable hours per person per month across an April to March year, against the paid hours from the Rates sheet. Twelve columns, so a quiet month is visible rather than averaged away.

Summary

The firm on one page: realisation, gross margin, operating margin, utilisation, what the unsold hours cost, what you never invoiced, and the card rate your margin target actually requires.

Read me

What each sheet is for, what to put in it, and what the file deliberately does not try to do.

Shaded cells are yours. Everything else is a formula. It arrives with fourteen invented example projects so you can see the shape before clearing them.

In the example, the three retainers look like the safest work in the firm. They are recurring, the clients are happy, and nobody has had an awkward conversation about them in two years.

They are also the worst work in the firm. The support retainer delivered 960 hours against an assumption of 700, and invoiced 78% of what those hours were worth at card rate. The growth retainer ran 135 hours over and realised 81%. Their delivery margins still read in the forties, which is why nothing ever looked wrong.

That is the shape of retainer drift. Scope expands a little each quarter, the fee is anchored to what was agreed at the start, and no single month is bad enough to raise. The tracker catches it because realisation and hours against estimate are on the same row as the margin, and the three together tell a story that the margin alone does not.

The single most common mistake in a home-made version of this spreadsheet is costing an hour at salary divided by working hours. It produces a number that is comfortably wrong in two directions at once.

• ·The numerator is too small. Employer provident fund, gratuity accrual, ESIC where it applies, insurance, equipment and software licences are all real costs of employing someone, and together they commonly add 12% to 18% on top of salary.
• ·The denominator is too big. Nominal annual hours include leave, public holidays and training, none of which can be sold. Around 1,850 paid hours is a more realistic base than 2,080.
• ·And the denominator should shrink again for utilisation. Only billable hours can carry the cost, so the cost per billable hour at 62% utilisation is more than half again the cost per paid hour.

In the example the three steps take a person costing ₹13.5 lakh a year from ₹730 an hour to ₹1,183 an hour. Every project margin in the file depends on getting that one number right, which is why the Rates sheet comes first.

The Summary sheet ends with an arithmetic most firms never run. Instead of asking what margin the current rate produces, it asks what rate the intended margin requires.

The effective rate has to cover the cost of a billable hour once overheads and the target margin have each taken their share of revenue. Divide the cost of a billable hour by what is left after both, and you have the effective rate. Divide that by realisation and you are back at a card rate.

The example firm needs ₹2,327 and charges a weighted ₹2,403, so it has ₹76 an hour of headroom. That is a thin cushion, and it is what absorbs an estimate that runs over or a quarter with more bench than planned. A firm reading a negative number there has a structural gap rather than a bad quarter.

The Summary draws delivery cost from the Projects sheet and hours billed from the Utilisation sheet, which means it is only correct if those two agree. In practice they very often do not, because project hours get logged and personal hours get estimated, or a month of one is missing.

So the file checks. A band near the bottom of the Summary puts the two hour counts side by side and says either that they tie or that they do not. If it says they do not, nothing above it is worth reading until the inputs are fixed.

That check is the difference between a spreadsheet you trust and one you abandon after the second month, and it is the first thing to look at each time you update the file.

1. 01Update the Utilisation sheet with last month's billable hours per person. Ten minutes if time is tracked, half an hour of asking if it is not.
2. 02Update hours delivered and invoiced to date on the Projects sheet. Add any new projects.
3. 03Look at the tie check on the Summary before anything else.
4. 04Sort the Projects sheet by margin, then by realisation. The two orders usually flag different problems, and both lists are short.
5. 05Pick one thing. A retainer to reprice at renewal, a change request process for the project giving work away, or a resourcing conversation about whoever has been on the bench.
6. 06Revisit the Rates sheet once a quarter, or whenever someone joins, leaves or is promoted.

The discipline that matters is doing it every month rather than doing it thoroughly. A rough version reviewed twelve times a year changes more decisions than a precise version built once for a board meeting.

• ·It is not a timesheet system. If you bill by the hour and need an audit trail behind an invoice, you need software, and this sits on top of whatever that software exports.
• ·It does not tie to your accounts. Invoiced value here is what you billed; recognised revenue on a fixed-price project spanning a year end will differ, and that is a conversation for your accountant rather than a fault in the file.
• ·It holds the bench inside utilisation rather than as a separate line, so one person idle all year reads the same as two people at half utilisation.
• ·It assumes overheads are a constant share of revenue, which is roughly true across a year and wrong in any single month.
• ·It says nothing about whether a client is worth keeping. Margin is one input to that decision and usually not the largest.

It answers one question properly: what did this work earn, and where did the difference between the rate card and the result go. For most firms that have never asked it, the first answer is the one that changes what they do next.