guide · 25 Sep 2026
Why your project margin is wrong in Excel
Margin is the one figure a partner reads first, and it is assembled from 5 files that were never meant to be added together. Below is the calculation, a project that reads 31.4% and is really at −22.7%, and the 6 reasons the number comes back too high.
The 5 places the number comes from
A margin is 2 figures, revenue and cost, and neither one lives in a single file. Before any arithmetic, name the source of every line.
Revenue recognised
budget_Q3_v2.xlsx
The sold amount, and how much of it this cycle has earned. On a fixed price these are 2 different numbers and only one belongs in the margin.
Days delivered
project tracker mars.xlsx
Who worked how much on what. Stored in days here, in percentages in the staffing file, and in neither for the partner who stepped in for a week.
Cost rates
rates_2026.xlsx
Cost per day per grade — salary plus charges divided by billable days. Not the sell rate, and usually maintained by whoever does payroll.
Bought-in cost
subcontractors_PO.xlsx
Freelancers and partner firms, invoiced at their own rate. Lives with purchasing, never in the tracker, and is the single most common reason margin reads high.
Expenses
notes_de_frais.xlsx
Travel, licences, one client dinner. Some rebilled, some absorbed. Only the absorbed ones are cost against the project.
The cost side has 3 sources and the tracker is only one of them. That asymmetry is the whole reason margin drifts high: revenue is complete because somebody invoices it, and cost is partial because nobody owns all of it.
One project, worked all the way through
A security review, sold fixed price, 7 of 8 workstreams accepted at this cycle. Read the lines in order and watch where the number goes.
Sold, fixed price
$96,000
Completion at this cycle
87.5%
Revenue recognised
$84,000
72 internal days at $800
$57,600
18 subcontract days at $1,150
$20,700
8 partner days of rework at $1,400
$11,200
Travel not rebilled
$13,600
Total delivery cost
$103,100
margin from the tracker alone
31.4%
$84,000 revenue against 72 internal days of cost. Every figure is real and the answer is wrong.
margin with all 4 cost lines
−22.7%
$84,000 revenue against $103,100 of delivery cost. A decision for the committee, not a rounding difference.
54.2 points of margin sat in 3 files nobody joined. That is the ordinary case, not the horror story: the subcontractor, the rework and the travel are each defensible to leave out, and together they invert the sign.
The 6 reasons it comes back too high
Margin errors are almost always one-directional. Cost gets forgotten; revenue rarely does.
1The cost rate is the sell rate
The rate card has one column and it is the one the client pays. Costing 72 days at $1,400 instead of $800 does not flatter the margin, it destroys it — and costing at the sell rate makes every project read as exactly 0.0%, which is the tell.
2Subcontractors are in a different file
18 days bought from a partner firm at $1,150 a day is $20,700 of cost that the tracker never saw, because the freelancer does not fill in the timesheet. Margin reads 31.4% until purchasing sends the invoices over.
3Revenue is invoiced, cost is delivered
The budget file holds what was invoiced; the tracker holds what was done. On a milestone contract those drift by a month in both directions, so the margin swings 20 points cycle to cycle while nothing real changes.
4Days and percentages summed as-is
The tracker stores 11 days, the staffing file stores 0.5, and someone stored "50%" as text so SUM skips it. Cost per project is only as right as the unit conversion under it.
5A SUM range that stopped growing
Rows were added below the range, or a #REF! from a deleted tab poisons one cell and the total goes quiet rather than wrong-looking. A margin computed from a broken total is confidently exact.
6Nobody costs the rework
The 8 days a partner spent rescuing the deliverable were not sold, not tracked and not billed, so they cost nothing anywhere. They are the difference between a project that missed and a project that nobody learned from.
The calculation, in order
7 steps. Step 05 is the one that gets skipped when the committee is tomorrow, and it is the one that moved the project above from 31.4% to −22.7%.
- 01Fix the margin definition before touching a cell. Delivery margin is revenue recognised minus delivery cost, over revenue recognised. Write it at the top of the sheet so the committee argues with the definition and not with you.
- 02Recognise revenue by completion, not by invoice. For a fixed price, sold amount times percentage complete. For time and materials, days delivered times sell rate. State which of the 2 each project is.
- 03Build a cost rate per grade, from payroll, not from the rate card. Fully loaded annual cost divided by billable days per year. One number per grade per year is enough; precision beyond that is theatre.
- 04Convert every allocation to days before costing anything. Percentages become days using that month's working days, and text rows get retyped as numbers.
- 05Add the bought-in cost and the absorbed expenses. Pull subcontractor purchase orders and the expense file, keep the rebilled lines out, and tag each line with its source file.
- 06Compute margin per project, then reconcile the total against the firm's revenue. If the sum of project revenue does not tie to the month, the gap is a project you are not reporting on.
- 07Recompute at completion, not just to date. A project at 31.4% to date whose remaining 12.5% is already committed to a subcontractor is not a healthy project, it is a loss that has not landed yet.
cost of internal days
=SUMPRODUCT(Days,Cost_rate)
bought-in cost for one project
=SUMIFS(Amount,Project,$A2,Kind,"subcontract")
margin, guarded against no revenue
=IF(Rev=0,"no revenue",(Rev-Cost)/Rev)
Guard the division. A project with cost and no recognised revenue is not at −100%, it is a project whose contract has not been read into the budget file, and printing −100% in a pack costs more credibility than printing no revenue.
Before it goes in the pack
- ✓Every project states whether it is fixed price or time and materials, and revenue follows that choice.
- ✓Cost rates come from payroll and are dated. The sell rate appears nowhere in the cost column.
- ✓Subcontractor and absorbed-expense lines are present, each tagged with the file it came from.
- ✓Negative margins are written with a minus sign and one decimal: −22.7%, not (22.7) and not red shading alone.
- ✓Anything you could not resolve is listed — a missing rate, a project with cost and no revenue, an expense you could not tell was rebilled.
One more habit, borrowed from the allocation check in our guide on a consultant booked over 100%: write the merges and exclusions down. A margin that names the 4 cost lines behind it survives the meeting. A margin that is just a percentage does not.
Or drop the files and read the margin
This is the calculation Operations Cell runs. You drop the budget file, the tracker and the staffing file exactly as they are, a model reads them, and the pack comes back with margin per project, the cost lines behind each one, and a list of what it refused to guess. Operations Cell is built and run end to end by AI agents on NanoCorp, which is why this guide describes exactly what our own engine does rather than a simplified version of it.
Read a real pack before you trust it with client numbers.
The example is a real run of the engine on an invented firm whose 3 files are as broken as yours: 2 dead tabs, a #REF! in the budget total, and one consultant spelled 2 ways. It includes the margin table and the findings behind it.
The firm, the project and every figure on this page are illustrative, chosen because they are the shape these numbers take in real files.