Construction Overhead Allocation by Job in Excel: Which Jobs Actually Pay for the Office

Ridgeline Construction finished the year at $6,400,000 in revenue, $5,509,000 in direct cost, and $848,000 in overhead. Net profit: $43,000. The owner knows those three numbers cold. Ask him which of his five jobs produced the $43,000 and he will point at the medical office fit-out, because that is the one that felt good. He is wrong, and the reason he is wrong is that his spreadsheet charges every job 13.25 percent of its revenue for overhead. That single number is doing all the lying. Construction overhead allocation by job in Excel is not an accounting chore, it is the difference between bidding the work that pays for your office and bidding the work that quietly eats it.

Overhead allocation is not the same thing as overhead markup, and mixing them up is why most contractors never fix this. Markup is the number you add at bid time. Allocation is the question of which slice of last year's office cost actually belongs to which job. You cannot set a defensible overhead markup until allocation tells you what different kinds of work really consume. Get the allocation wrong and every markup decision downstream is wrong in the same direction, on every bid, all year.

Percent of revenue is the default and it is backwards

Almost every contractor spreadsheet allocates overhead as a flat percentage of job revenue. It is easy, it always foots to the total, and it produces a per-job number that looks authoritative. Ridgeline's is $848,000 divided by $6,400,000, or 13.25 percent, applied to all five jobs.

Read that table the way the owner read it. The big retail shell lost money. The small works division, 31 little jobs run out of the service truck, threw off $14,400 on $480,000 of revenue, the best net margin on the board at 3.0 percent. The obvious conclusion is to stop chasing big shell work and feed the small works crew.

That conclusion is going to cost Ridgeline about $200,000 next year, because the allocation that produced it assumes a dollar of revenue consumes the office at the same rate no matter where it comes from. It does not, and the gap is not subtle.

What a revenue dollar actually costs the office

The retail shell was 88 percent subcontracted. Twelve subcontracts, twelve monthly pay applications, one owner, one architect, nine change orders, one set of lien waivers per month. A project manager touched it perhaps six hours a week.

The small works division was 31 separate contracts, 31 invoicing cycles, 31 certificates of insurance, 47 change orders under $2,000 each, and 116 site visits by a superintendent who spent most of the year driving. The bookkeeper opened a job file 31 times. Estimating priced 84 small proposals to win those 31.

Revenue allocation charged the shell job $344,500 for that six hours a week and charged the small works division $63,600 for consuming the entire office. Nobody would defend that if you stated it out loud. It survives because it is never stated out loud, it is just a column.

Pick a base that moves with the work, not with the invoice

The accounting fix is to allocate on direct labor hours instead of revenue. Self-performed field hours are the closest single proxy for how much supervision, tools, trucks, and management a job absorbs, and every contractor already has the data sitting in timecards coded to job numbers. Ridgeline ran 18,500 self-perform field hours across the five jobs, so the rate is $848,000 divided by 18,500, or $45.84 per hour.

Same company, same $43,000 of profit, completely different story about where it came from. But do not adopt this one either, because it overcorrects. Pure labor-hour allocation hands the retail shell a free ride on $2,262,000 of subcontracted cost that generated real office work: twelve subcontract buyouts, bonding and general liability that price off contract value, twelve pay applications to assemble and reconcile, and a certificate-of-insurance chase that ran all year. Those costs scale with contract dollars, not with your crew's hours.

Any single allocation base is a blunt instrument, because overhead is not one thing. It is at least two things, driven by two different meters.

Build two pools in Excel, not one rate

Split the $848,000 into a field-support pool and a general and administrative pool, then give each pool the driver that actually moves it. The test for which pool a cost belongs in takes one question per line item on your P&L:

• Does it go up when you put another crew in the field, and nothing else changes? Field support. Superintendent salaries not charged to jobs, trucks and fuel, small tools, field phones, safety program, workers comp administration.
• Does it go up when you sign another contract, regardless of who performs the work? General and administrative. Contract administration, accounting, general liability and bonding, estimating, project management time on pay apps and submittals.
• Does it sit flat either way? Office rent, owner compensation, software, legal. Park it in the G&A pool and remember that this is the piece that makes a slow year dangerous.

Ridgeline's split came out at $392,000 field support and $456,000 G&A. Two tabs run the whole thing.

The Pools tab

The Jobs tab

One row per job. Columns A through F are what you already track. G through P are the ones that change what you bid. Example values are job E, the small works division.

Column G is the one people get wrong. It has to be self-performed field hours only. If your timecard export includes a working owner, a project manager, or anything you also put in the field support pool, you are allocating a cost using a driver that contains the cost, and the rate will drift every time your office headcount changes. Pull hours from the payroll job-cost export filtered to field classifications, and reconcile the column total to the payroll register once a quarter.

Columns M through P are the payoff. M is not a target margin or a rule of thumb, it is the arithmetic answer to what this job had to add on top of direct cost just to break even after the office. N is what you actually charged. O is the distance between the two, in percentage points, per job.

What the two-pool numbers tell you to bid

Two jobs made $155,300. Three jobs lost $112,500. The company netted $43,000 and the owner thought it came from the medical office.

The big job was priced out of the market by its own spreadsheet

Under revenue allocation, the retail shell needed $2,262,000 of direct cost plus $344,500 of overhead, so break-even looked like $2,606,500. Ridgeline won it at $2,600,000, which is why the spreadsheet showed a $6,500 loss and why the owner decided that shell work is a trap. Under two-pool allocation, real break-even on that job was $2,489,600. There was $110,400 of room in the number, and the estimator did not know it. Every shell job he bid after that one, he bid with $116,900 of phantom office cost baked in, and lost the ones that mattered by two or three points to a competitor who knew what his own overhead did.

The small works division needs a 36 percent markup or it needs to close

Job E carried $143,500 of overhead on $402,000 of direct cost. To break even, small works has to price at direct cost plus 35.7 percent. To clear a 10 percent net margin, price at =(D4+J4)/(1-0.10), or $606,100 on $402,000 of cost, a 50.8 percent markup. Ridgeline was charging 19.4 percent.

That does not automatically mean kill the division. Small works feeds relationships, keeps crews busy between big jobs, and generates callbacks that turn into fit-outs. But those are strategic reasons to accept a loss, and you can only accept a loss on purpose if you know it is $65,500 and not a $14,400 gain. Two other moves are available before closing anything: raise small works pricing to a minimum 35 percent markup with a $2,500 minimum charge, or cut the hours that drive the allocation by batching site visits and stopping the 47 no-charge change orders under $2,000.

A rate is a bet on next year's volume

Here is the failure mode nobody warns you about. Your allocation rate has a denominator, and the denominator is a forecast. Ridgeline's $21.19 per hour assumes 18,500 self-perform hours. Run 15,200 hours instead and every single job still gets charged $21.19, every job still looks like it covered its overhead, and $69,900 of field support cost was never charged to anything.

Put the check in cell B12 of the Pools tab and read it every quarter, not at year end:

=B3+B7-SUM(Jobs!J4:J40)

A positive number is overhead you spent and never recovered in any job's price. Ridgeline at 15,200 hours and $4,900,000 of direct cost would show $69,900 unabsorbed on the field pool and $50,400 on the G&A pool, $120,300 total, against $43,000 of reported profit. That is the year where every job report is green and the bank account is red. Track it with a simple driver variance too: =SUM(Jobs!G4:G40)/Pools!$B$4-1 tells you how far off your hours forecast is, and if it drops below negative 10 percent by the end of Q2, reprice mid-year rather than discovering it in February.

Do not build a third pool yet

Once the two-pool model runs, someone will suggest a transaction-count driver for project management: pay apps, RFIs, submittals, change orders, owner meetings. They are right that it is more accurate. Job A had 21 of those events and job E had 194. But a third pool needs a count that somebody has to maintain, and drivers that depend on a new form get abandoned by the second quarter. Run two pools for four quarters against real closed jobs first. If the flags in column P keep matching what your PMs already suspected, the model is good enough to price off.

Do this before your next bid goes out

1. Pull last year's P&L and split every overhead line into the field pool or the G&A pool using the three questions above. It takes about ninety minutes and it only has to be done once.
2. Export self-perform field hours by job from payroll for the same period. Filter out office and management classifications, then reconcile the total to the payroll register.
3. Build the Pools tab, compute the two rates, and apply them to every job you closed last year. Do not model it on open jobs first, closed jobs are the only ones with a true final cost.
4. Sort by column O. The most negative row is the kind of work you have been buying at a discount all year, and it is almost never the kind you expected.
5. Reprice that work category before the next proposal goes out, and set the minimum markup at column M plus your target net margin.
6. Add the absorption check in B12 to your quarterly review. A rate that assumed volume you did not get is a loss that no job report will ever show you.

The reason standalone overhead allocation spreadsheets die by the third quarter is that the model needs data it does not own. Column D needs final direct cost including approved change orders, column G needs job-coded labor hours, and the whole thing needs a job list that stays current as work closes out. Rebuild those by hand and you will do it twice before you stop doing it at all. The SheetCraft Construction Budget Tracker already carries the job register, committed and actual direct cost by cost code, change orders, and labor hours by job, which is columns A through G of the allocation tab. Drop the Pools tab in beside it and columns H through P populate themselves from the job cost you are already maintaining, so the question of which job pays for the office gets answered every month instead of once a year, after the bidding decisions have already been made.