Earned Value Management in Excel for Construction: Catch Cost Overruns at Month Three

A general contractor is five months into a ten-month, $2.4 million commercial fit-out. The owner sees $1.2 million billed and assumes the job is half done. The PM walks the site and tells himself 50 percent is roughly right. The accounting team confirms costs are tracking to budget month over month. Everyone feels fine. Four weeks before substantial completion, the PM runs the numbers seriously for the first time and finds out the job is $410,000 underwater. Two weeks of margin evaporated overnight. A working earned value management Excel construction setup would have flagged this in month three, when there was still time to do something about it.

This is the gap progress billing hides. Billed dollars are not earned dollars. Spent dollars are not finished work. The only reliable way to know whether a construction project will land at budget is to compare physical completion against planned completion and against actual cost, on the same date, in the same units. That is what earned value management does. The reason most contractors do not run it is not that the math is hard. The math is fifth grade. The reason is they confuse the four numbers that matter and end up with a dashboard that lies to them with more confidence than no dashboard at all.

The Four Numbers, and Why Three Are Not Enough

Every EVM setup runs on three measurements pulled on the same cutoff date. The fourth is the contract value itself. Mix any of them up and the rest of the analysis collapses.

The single most common mistake is using billed percentage as earned value. Billing is a function of what your contract lets you draw, when you sent the invoice, and how generous you got with stored materials. Earned value is a function of what is physically installed. A drywall sub at 85 percent complete who already billed 95 percent has earned $85k of an $100k subcontract, not $95k. If you let billing stand in for earned value, your CPI looks healthy until the moment you run out of work to bill against, and then it crashes through the floor.

How to Measure Physical Percent Complete Without Lying to Yourself

Three methods work for construction, ranked by how hard they are to fudge.

1. Units installed. For trades with countable output: linear feet of pipe, square feet of drywall hung, cubic yards poured. Divide installed by total. Hardest to game.
2. Milestone weighting. Break a scope into stages with predefined percent values. Concrete forming 25 percent, rebar placement 20 percent, pour 30 percent, strip and cure 15 percent, patch and finish 10 percent. You only earn the next bucket when the milestone is signed off.
3. Supervisor estimate. The superintendent eyeballs it. Use this only for soft scopes where units do not work, and require a second sign-off above 80 percent. This is the method that produces optimistic numbers if nobody checks it.

CPI and SPI: The Two Ratios That Diagnose the Job

Once you have BAC, PV, EV, and AC for the cutoff date, two ratios tell you what is happening. Cost Performance Index measures whether you are getting a dollar of work for each dollar spent. Schedule Performance Index measures whether you are completing work as fast as the baseline said you would.

CPI = EV / AC SPI = EV / PV

CPI above 1.0 means you are under budget. CPI below 1.0 means you are over. A CPI of 0.85 means every dollar you spent only bought 85 cents of finished work. SPI above 1.0 means you are ahead of schedule, below 1.0 means behind. The reason these are ratios and not differences is that they let you compare a job at month two against the same job at month six against a different job entirely. Variance dollars do not normalize. Indexes do.

Construction-realistic thresholds, before you panic:

For SPI the bands are similar but the action is different. SPI of 0.92 with CPI of 1.05 means you are slow but efficient, often a labor shortage problem. SPI of 0.92 with CPI of 0.89 means the work is taking longer and costing more, almost always rework or scope creep.

The Forecast Numbers That Decide Whether You Can Recover

CPI tells you where you are. The next three numbers tell you where you are going.

TCPI is the underrated one. If your TCPI says you need to run the remaining scope at 1.18 efficiency to finish at budget, and you have been running at 0.92 for five months, that is a quantitative way of saying the job will not come back. Anything above 1.10 is a flag that you should either negotiate a change order, pursue a constructive change claim, or accept the loss and stop pretending. Senior PMs talk about TCPI before they talk about anything else.

The Excel Layout That Does Not Break

Here is the column structure for a project-level EVM tracker. Each row is one cost code or WBS element. Headers in row 3, first data row at row 4.

1. WBS / Cost Code (Column A)
2. Description (Column B)
3. BAC (Column C). Budget for this line.
4. Planned % Complete (Column D). From the baseline schedule, as of cutoff date.
5. Earned % Complete (Column E). Physical, as of cutoff date.
6. PV (Column F). =C4D4
7. EV (Column G). =C4E4
8. AC (Column H). Pulled from the G/L for this cost code.
9. CV (Column I). =G4-H4
10. SV (Column J). =G4-F4
11. CPI (Column K). =IF(H4=0,"",G4/H4)
12. SPI (Column L). =IF(F4=0,"",G4/F4)
13. EAC (Column M). =IF(K4="",C4,C4/K4)
14. VAC (Column N). =C4-M4
15. Flag (Column O). The diagnostic.

The flag formula nests three thresholds.

=IF(K4="","", IF(K4<0.90,"FIRE", IF(AND(K4<0.95,L4<0.95),"REVIEW", IF(K4<1,"WATCH","OK"))))

FIRE means cost performance has fallen below the threshold a recovery plan can realistically address. REVIEW means both cost and schedule are slipping together, classic productivity loss. WATCH means cost alone is sliding, often a leading indicator before SPI follows. Apply conditional formatting that turns the cell red for FIRE, orange for REVIEW, yellow for WATCH, green for OK.

Project-Level Rollup

Add a summary block above row 3 that aggregates across all rows. Assuming the data range goes from row 4 to row 100:

Total BAC:  =SUM(C4:C100) Total PV:   =SUM(F4:F100) Total EV:   =SUM(G4:G100) Total AC:   =SUM(H4:H100) Project CPI: =SUM(G4:G100)/SUM(H4:H100) Project SPI: =SUM(G4:G100)/SUM(F4:F100) Project EAC: =SUM(C4:C100)/(SUM(G4:G100)/SUM(H4:H100)) Project TCPI: =(SUM(C4:C100)-SUM(G4:G100))/(SUM(C4:C100)-SUM(H4:H100))

The TCPI formula at the project level is the number you walk into every weekly job review with. If it is 1.04 or below, you have a path. If it is 1.15 or above, you are explaining a loss to a principal sooner or later.

What the Numbers Look Like on a Real Job

Same $2.4 million fit-out, month five of ten. Baseline schedule has the job at 52 percent planned complete by the cutoff date. Physical walk plus milestone signoffs put earned at 41 percent. The cost report from accounting shows $1.32 million spent.

The owner is still seeing 55 percent billed and thinks everything is fine. The PM, looking at this, has three honest choices. File a change order claim for the documented scope additions causing the productivity loss. Negotiate a revised completion date that recovers indirect cost. Or take the loss and protect the schedule so it does not get worse. EVM does not solve the problem. It makes it impossible to pretend the problem is not there.

Trend Matters More Than the Snapshot

A CPI of 0.93 in month three of an eight-month job is recoverable. A CPI of 0.93 that dropped from 1.02 in month one, 0.98 in month two, and 0.93 in month three is a job in free fall. The direction tells you more than the value. Track the indexes monthly and chart them. If the line is heading down for two consecutive periods, the underlying problem is structural and will not fix itself. Productivity does not spontaneously recover. Estimating gaps do not close on their own. Scope creep does not reverse. Whatever is causing the trend is still active.

A simple sparkline next to each cost code in the Excel sheet, charting CPI over the last six periods, gives you the trend at the same glance as the current value. INDEX and OFFSET formulas can pull rolling periods. Or just add a small CPI history table on a second tab and reference it.

Start Tracking Before You Need It, Not After

EVM is worthless on a job that is already lost. Its only function is to spot the loss while there is still runway. That means setting up the tracker before kickoff, populating BAC from the contract value, freezing the planned percent complete curve from the baseline schedule, and updating EV and AC on the same cutoff date every month. Skip a month and the trend breaks. Pull numbers from different dates and the indexes lie. The discipline is consistency, not complexity.

If you would rather skip the column setup, formula entry, and conditional formatting work, the Construction Budget Tracker ships with a built-in EVM tab that pulls BAC from your line-item budget, accepts physical percent complete entries by cost code, calculates CPI, SPI, EAC, VAC, and TCPI per line and at the project level, and flags rows above or below threshold automatically. Use it on the next job. Catch the slip in month three when you can still do something about it.