Project Profitability From a Timesheet Export

A project can look profitable at your rate card and lose money in reality. That gap has a name, it is calculable from two exports you already have, and fixed-fee work is where it hides.

Most firms have a project profitability report that subtracts nothing. It shows fees by project, sorted descending, and the largest projects appear at the top whether they made money or not.

Profitability needs a cost side, and for a services firm the cost side is hours. Two exports get you there: a timesheet with hours by person and project, and an invoice export with amounts by project.

Start with the cost rate, because everything else depends on it

A cost rate is what an hour of someone's time costs the firm. Fully loaded:

Cost rate = (salary + employer taxes + benefits + overhead share) / capacity hours

Both parts get understated in practice. A 90,000 salary with roughly 25% on-costs is about 112,500. Capacity is not 2,080 hours — take out holiday, public holidays and a realistic allowance for sickness and you are nearer 1,700.

112,500 / 1,700 = 66 per hour

Dividing salary straight by 2,080 gives 43, which is 35% low, and every project margin in the firm inherits that error. If you want a single decision to improve the report, it is this one.

Keep the rates in a small table with one row per person, and store the resulting cost as a column on the timesheet rows rather than recalculating it whenever you open the workbook. Rates change. If the cost of last March is recomputed at this year's rates, last March gets cheaper every time someone gets a raise, and history stops being comparable.

Two margins, and why one is not enough

Standard value  = billable hours × standard rate
Cost            = hours × cost rate
Delivery margin = (standard value - cost) / standard value
Project margin  = (invoiced - cost) / invoiced

They answer different questions.

• Delivery margin — at our own rate card, how efficiently did we deliver this? It ignores what the client paid, so it is a statement about the team and the mix of people on the job.
• Project margin — what did this engagement actually make? It includes every discount, write-off and fixed-fee overrun.

The gap between them is the interesting part:

• Delivery healthy, project poor. You delivered efficiently and did not get paid for it. A pricing or scoping problem — see realization rate, which measures the same leak from the invoice side.
• Delivery poor, project acceptable. The engagement was priced well enough to absorb an inefficient delivery. Fine once; a habit is a staffing problem.
• Both poor. Underpriced and overserved. This is where fixed-fee work goes when nobody is watching the hours.

Building it in Excel

Timesheet export as a Table named Time, invoice lines as Lines, cost rates as Rates. Add one column to the timesheet:

Cost   =[@Hours]*XLOOKUP([@Person],Rates[Person],Rates[CostRate])

Then with projects down column A:

Hours     =SUMIFS(Time[Hours],Time[Project],$A2)
Cost      =SUMIFS(Time[Cost],Time[Project],$A2)
Standard  =SUMIFS(Lines[Standard],Lines[Project],$A2)
Invoiced  =SUMIFS(Lines[Invoiced],Lines[Project],$A2)
Delivery  =IFERROR((D2-C2)/D2,"")
Margin    =IFERROR((E2-C2)/E2,"")

Sort by the currency figure (invoiced minus cost) rather than the percentage. A 60% margin on a 4,000 project is worth less attention than a 22% margin on a 300,000 one, and a percentage-sorted list puts the small jobs on top every time.

Fixed fee is where this earns its keep

On time and materials the margin is roughly decided the day you set the rates. On fixed fee it is decided by consumption, and it moves every week the work continues.

So watch it during the engagement, not after. Cost to date against fee, as a percentage of the fee, with hours to date beside it:

Burn   =SUMIFS(Time[Cost],Time[Project],$A2)/XLOOKUP($A2,Fees[Project],Fees[Fee])

At 70% burn on 40% of the deliverables, you still have choices. At 100% burn on the final draft you have a lesson.

Two things to leave out

Work in progress. WIP is a balance — unbilled time sitting at a point in time — and summing balances across months gives a number that means nothing. It belongs in its own report with its own as-of date, not in a monthly margin table.

Month-on-month margin change per project. Tempting, and unreliable on a sparse table: a project with no hours in August is simply absent, so a naive comparison lines September up against July and reports a change that did not happen. Show the series and let people read it.

Asking rather than rebuilding

The formulas are not the work. The work is doing the join every month, keeping the rate table current, and explaining to whoever inherits the file which column is the real margin.

Quiriz for professional services holds those definitions once. Upload the timesheet and invoice exports and ask which service line has the highest delivery margin or project margin by client this quarter and the answer is computed the same way each time, by whoever asks. In Excel, =QUIRIZ.ASK("delivery margin by service line", "table").

One requirement worth stating plainly: the cost has to be a column in the file. We will not derive it from hours and a rate we cannot see, because a cost rate that is guessed is worse than a margin that is missing.

Frequently asked questions