Construction Subcontract Buyout Log in Excel: Catch the Margin Loss at 30 Percent Bought Out

A general contractor in Nashville signed a $3,150,000 tilt-up shell with an interior fit-out last spring. The estimate carried $2,677,000 in subcontracts and material buyouts, $268,000 in general conditions, and a fee of $204,750. Six weeks after the notice to proceed, five trades were executed and the project manager reported that buyout was "going fine." It was not. Those five subcontracts landed $36,900 worse than the numbers carried on bid day, which is 18 percent of the fee, burned before a single yard of slab concrete was placed. A construction subcontract buyout log Excel workbook exists to put that number on the table in week six, instead of at the 60 percent cost review in month seven when the only lever left is arguing with the owner.

Most buyout logs are award registers. Trade, subcontractor name, contract value, date signed. That is a filing system. It records what already happened and asks you to feel good about it. The log that earns its place answers a different question: given what the bought trades actually cost, can the trades I have not bought yet still absorb the damage?

The Buyout Gap Is Where a Job's Margin Actually Moves

By the time the last subcontract is executed, 85 to 90 percent of your cost is fixed. Everything after that is production risk and change order defense. The window where a general contractor can still move the number is the eight to twelve weeks of buyout, and most of the movement happens in the ten days between opening bids on a package and signing it.

Here is what the Nashville job looked like at week six.

Look at roofing. The raw comparison says the GC saved $13,000. The subcontract came in at $151,000 against a carried number of $164,000, and on a normal buyout log that row is a win. It is not a win. The roofer excluded the walkway pads at the rooftop unit curbs, which is $2,800 of work somebody has to buy, and bid a ten-year material-only warranty when the specification calls for a twenty-year NDL, a $3,700 difference. The real gain is $6,500, exactly half of what the headline number claimed.

That pattern repeats on nearly every job. A bid that comes in under budget is usually under because it excludes something the drawings require. Price the exclusion before you celebrate the gain, and put the price in its own column so it cannot be forgotten.

Build the Log Around Scope Gaps, Not Around Price

One row per bid package. Package list goes in rows 4 through 28, with a summary block in rows 1 through 3 where the numbers that matter live.

Column C is the one people get wrong. The estimator's working budget and the number you actually carried in the bid are frequently different, because on bid day somebody dropped a late quote in, shaved a trade to make the number, or moved allowance dollars around. Log the bid day figure. Compare against the pre-bid budget and your variance is fiction, and the PM will spend the whole job defending a baseline nobody submitted.

Column J is the only variance number that means anything. Subtracting the subcontract value from the budget and stopping there tells you what the subcontractor charged. Adding column F tells you what the trade costs you, which is a different figure and the one your fee is exposed to.

Two flags belong in the log from day one. Coverage:

=IF(AND($C4/SUM($C$4:$C$28)>0.05,$D4<3),"THIN COVERAGE","")

On any trade worth more than 5 percent of the subcontract budget, two bids is not a market. It is a coin flip with your fee on the table. The flag does not tell you the price is wrong. It tells you that you have no way of knowing, which on a $400,000 package is the same thing.

And status discipline:

=SUMIFS($C$4:$C$28,$K$4:$K$28,"Executed")/SUM($C$4:$C$28)

Only "Executed" counts toward percent bought out. An award letter is not a subcontract. Price moves between the letter and the signature more often than anyone admits, usually when the sub reads the flow-down terms and reprices the schedule risk. On the Nashville job that formula returned 38.9 percent, and the executed variance in column J summed to negative $36,900.

The Leveling Tab Feeds Column F

Build a second tab with one row per exclusion, per bidder: package number, bidder, item, who carries it, dollar value. Column F then pulls itself:

=SUMIFS(Leveling!$E:$E,Leveling!$A:$A,$A4,Leveling!$B:$B,$H$4,Leveling!$D:$D,"GC carries")

Masonry on this job shows why it matters. Bidder A quoted $208,500 and excluded cold weather protection ($2,900) and final cleaning ($1,300). Bidder B quoted $214,000 with nothing excluded. The headline says A is $5,500 cheaper. Leveled, A is $1,300 cheaper, and the actual decision is whether $1,300 is worth self-managing two extra scopes in January. Stated that way, most PMs take bidder B. Stated as a raw price comparison, everyone takes A and finds out in February.

The One Formula That Should Drive Your Monday Meeting

Percent bought out and variance to date are backward looking. The number that changes behavior is the projected final buyout position:

=SUMIFS($J$4:$J$28,$K$4:$K$28,"Executed")+SUMIFS($C$4:$C$28,$K$4:$K$28,"<>Executed")*$B$6

Cell B6 holds your company's historical buyout rate, which you get by dividing total buyout variance by total subcontract budget across your last ten or twelve closed jobs. For this contractor it is positive 0.6 percent. Not a guess, a measured average.

The math at week six: $1,635,000 of budget is still unbought. At 0.6 percent that recovers $9,810. Against negative $36,900 already banked, the projected final position is negative $27,090, or 13.2 percent of the $204,750 fee. Then the break-even question, which is one cell:

=-SUMIFS($J$4:$J$28,$K$4:$K$28,"Executed")/SUMIFS($C$4:$C$28,$K$4:$K$28,"<>Executed")

That returns 2.26 percent. Every remaining trade has to come in an average of 2.26 percent under the carried number just to finish flat. This company's best full-job buyout in five years was 1.4 percent. Say that out loud in week six and the conversation stops being about whether the concrete sub was fair and starts being about which of the remaining packages can realistically be attacked.

The spread between the second and third rows is $49,050 of fee on the same job, same drawings, same field crew. Nothing in the dirt changes it. It is decided in a conference room over about ten weeks, by whether anybody is watching.

Wrap the projection in a flag so it reads itself:

=IF($B$8/$B$9<-0.05,"RECOVERY PLAN REQUIRED",IF($B$8<0,"WATCH","ON PLAN"))

B8 is the projected position, B9 is the fee. Five percent of fee is the right trigger because below that you can usually claw it back with normal buyout discipline. Above it you need to change something structural, and the earlier you know, the more of the five levers below are still open.

Buyout Dates Are Set by Lead Times, Not by Your Calendar

The second way buyout kills a job has nothing to do with price. A trade bought on budget but bought late costs schedule, and schedule costs general conditions at a rate you already know. Column L, must award by, works backward from the long lead item in each package:

=E4-F4-G4

Where E is the week the material has to be on site, F is fabrication lead time in weeks after approved submittals, and G is submittal preparation plus review. Expressed in project weeks it stays readable when the schedule shifts.

Electrical is the package almost every GC buys last, because it is the biggest, the hardest to level, and the one where the drawings are least complete. On this job it has to be executed by week 6, ahead of drywall, ahead of finishes, ahead of trades that will not set foot on site for a year. Miss that date by four weeks and you pay expedite freight on switchgear, which runs $18,000 to $40,000 on a package this size, or you extend the job a month at $6,200 a week in general conditions, which is $24,800. Either way it is larger than most of the buyout gains you were chasing.

Make the log say it out loud:

=IF($K4="Executed","",IF($L4<$B$1,"LATE",IF($L4<=$B$1+2,"AWARD NOW","")))

B1 holds the current project week. Sort the log by column L, not by division number. Division order is how the estimate is organized. Lead time order is how the job actually needs to be bought.

Do Not Spend the Gains, and Know Your Levers When You Are Down

Buyout savings are not profit until the trade is finished. The $6,500 roofing gain turns into a $2,900 loss the first time the GC pays for temporary protection the subcontract quietly left out. Hold gains in a reserve row and release them on a rule, not on optimism:

=IF(AND($N4>=0.5,$O4=0),$J4,0)

Column N is percent complete for that trade, column O is open change order exposure in dollars. A gain moves to the projected profit line only when the trade is half built and carries no open exposure. Everything else sits in reserve where the PM cannot spend it on a contingency item.

When the projection says you are underwater, there are five levers and they are worth very different amounts.

1. Rebid the package. Available only before award, and only worth the two to three weeks of float it costs when you have fewer than three real bids. Typical recovery when it works: 4 to 9 percent of the trade value.
2. Value engineer with the awarded sub. Recovery of 3 to 8 percent, but only if you give up something real, a finish, an assembly, a warranty term. VE that changes nothing on the drawings recovers nothing on the invoice.
3. Bill the owner for scope the documents did not include. The highest recovery lever, and it depends entirely on column F. A scope gap documented at buyout with a date, a bidder, and a drawing reference is a change order. The same gap remembered in month five is an argument you lose.
4. Self perform. Worth 15 to 25 percent when the trade is labor heavy, your crew is otherwise idle, and your burdened rate beats the sub's. On masonry and mechanical, rarely. On rough carpentry, specialties, and site cleanup, often.
5. Accept it and defend what is left. Freeze contingency spending, tighten the change order process, hold the rest of the buyout to its dates. A $27,000 buyout loss against a $204,750 fee is survivable. That loss plus a contingency nobody is guarding is how a job finishes at zero.

Four of those five close the moment the subcontract is executed. That is the whole argument for keeping the log current. It is not a reporting tool for the monthly owner package. It is the only instrument that tells you a trade is in trouble while you can still do something about it.

Set It Up the Week You Get the Award Letter

Build the log before the first bid package goes out, and seed column C directly from the bid tab while the numbers are still cold. Once buyout starts, people remember the budget as whatever makes the current award look acceptable, and a baseline you set after the fact is worthless.

Update it twice a week, Tuesday and Friday, and report exactly two numbers at the internal job review: percent bought out by budget dollar, and projected final buyout position in dollars and as a percent of fee. Every other column in the workbook exists to make those two cells honest. If your PM can only tell you the first number, the log is still a register and the job is still flying blind.

If you would rather not rebuild the leveling tab, the variance math, and the must-award-by logic on every project, the SheetCraft Construction Budget Tracker already carries them: a budget structured by cost code that reconciles back to the bid, a buyout column set that keeps awarded value and scope gaps separate so a fake gain cannot hide, and change order and draw schedule modules that pick up exactly where the buyout log stops. The buyout log tells you what the job is going to cost. The tracker tells you what it is costing while you build it.