What if we cannot measure completion objectively?

Then fix that before using earned value at all. Agree a rule such as nothing until a package is finished and then its whole budget, or a fixed split on start and completion. Percentage complete estimated by whoever is doing the work is the judgement earned value exists to replace.

Why does schedule variance stop being useful near the end?

Because it is measured in money rather than time. Earned value has to reach planned value once all the work is done, so schedule variance converges on zero and the index on one, and a project delivered six months late still finishes at exactly one.

Can this be used on adaptive delivery?

Yes. A release scope and budget supply the baseline and accepted sized items supply the measure of completion. The measure is arguably stronger, since an item is accepted against a definition of done rather than judged as a percentage.

Project delivery

Earned value tracker

Planned value, earned value and actual cost per period, with both variances, both indices, the estimate at completion and the to complete index as live formulas. Type three numbers a period and the sheet forecasts the rest.

What it is

Earned value answers a question that a spend report cannot. Spend against budget tells you how much money has gone. It does not tell you how much work you got for it, and a project that is under budget because it is behind schedule looks identical to one that is under budget because it is efficient.

The move that makes it work is measuring completed work in money. A work package worth forty thousand that is finished has earned forty thousand, whether it cost twenty or ninety. Once you have that number alongside what was planned and what was spent, both variances and both indices fall out, and the forecast stops depending on anybody's optimism.

The reason to build it as a sheet is that almost nobody has seen the arithmetic laid out where changing one input moves everything downstream. Watching the estimate at completion move as you edit a single period is what makes the relationships stick, and it is the difference between recalling the formulas and reading a scenario correctly.

When to use it

Spend is on budget and you suspect the project is behind, or the other way round.
A sponsor wants a forecast of the final cost that is not somebody's opinion.
You need to show that a recovery plan is arithmetically implausible.
You are preparing for an exam and the formulas have not connected to anything real yet.

The columns

Period
Planned value
Earned value
Actual cost
Cost variance
Schedule variance
CPI
SPI
EAC
TCPI

The template

Period	Planned value	Earned value	Actual cost	Cost variance	Schedule variance	CPI	SPI	EAC	TCPI
Month 1	30000	28000	32000	-4000	-2000	0.88	0.93	685714	1.01
Month 2	75000	68000	78000	-10000	-7000	0.87	0.91	688235	1.02
Month 3	130000	118000	136000	-18000	-12000	0.87	0.91	691525	1.04
Month 4	195000	176000	205000	-29000	-19000	0.86	0.90	698864	1.07
Month 5	265000	238000	278000	-40000	-27000	0.86	0.90	700840	1.12
Month 6	335000	300000	352000	-52000	-35000	0.85	0.90	704000	1.21
Month 7	405000	360000	424000	-64000	-45000	0.85	0.89	706667	1.36
Month 8	465000
Month 9	515000
Month 10	555000
Month 11	583000
Month 12	600000

Everything from Cost variance rightwards is a formula. You type three numbers a period and the sheet does the rest.

The three numbers you type

Planned value. The budget for the work that should have been finished by the end of this period, cumulative from the start. Fill the whole column in once, at the beginning, from the baseline. It is the plan, so it does not move unless the baseline is formally rebaselined.
Earned value. The budget for the work that actually has been finished, cumulative. Note what this is not. It is not what you spent and it is not a percentage somebody feels good about. A work package worth 40000 that is finished earns 40000 whether it cost 20000 or 90000.
Actual cost. What has actually been spent on the work finished so far, cumulative, from the finance system rather than from memory.

The budget at completion is the last figure in the planned value column, which is why the whole column is filled in from the start. The EAC and TCPI formulas read it from there.

What each calculated column says

Column	Formula	What the answer means
Cost variance	Earned value minus actual cost	Negative means the work finished so far cost more than it was budgeted to
Schedule variance	Earned value minus planned value	Negative means less work has been finished than the plan expected by now
CPI	Earned value divided by actual cost	Below 1 means you are getting less than a currency unit of work per unit spent
SPI	Earned value divided by planned value	Below 1 means work is being completed more slowly than planned
EAC	Budget at completion divided by CPI	What the whole project will cost if the rest of it runs at the efficiency of the part already done
TCPI	Budget remaining divided by money remaining	The efficiency the rest of the work has to run at to finish inside the original budget

Reading the example

The project above is both late and over cost, and the two indices say so within the first month. By month seven, CPI has settled around 0.85 and SPI around 0.89, and neither has recovered in six periods. That flatness is the finding. A single bad month is noise, and an index that holds a level for four periods is the rate the project actually runs at.

EAC is the column that changes conversations. A budget of 600000 running at a CPI of 0.85 forecasts around 707000, and it forecast roughly that from month one. Nobody has to argue about whether the team will make it up later, because the number already assumes they work at the rate they have been working at.

TCPI is the reality check next to it. At month seven the project needs to run at 1.36 for the rest of its life to finish inside 600000, having never once exceeded 0.88. A required efficiency well above anything achieved so far is the clearest signal a sheet like this produces, and the honest response is to change the budget, the scope or the date rather than the forecast.

Schedule variance goes quiet at the end

Schedule variance and SPI are denominated in money rather than time, and they both converge on zero and one as the project finishes, because earned value has to reach planned value once all the work is done. A project delivered six months late still ends with an SPI of exactly 1.

That makes both figures unreliable near the end of a project and useful in the middle. For lateness at the end, read the schedule.

Where the numbers come from

Earned value only works when completion is measured the same way every period. Agree the rule before the first entry and write it on this tab.

The rules worth considering are nothing until a work package is finished and then its whole budget, a fixed split such as a quarter on start and the rest on completion, or physical measurement where the work has a countable unit such as metres laid or tests passed. The first is the most conservative and the hardest to argue with. Percentage complete estimated by whoever is doing the work is the one to avoid, because it is the judgement earned value exists to replace.

Questions people ask

What if we cannot measure completion objectively?: Then fix that before using earned value at all. Agree a rule such as nothing until a package is finished and then its whole budget, or a fixed split on start and completion. Percentage complete estimated by whoever is doing the work is the judgement earned value exists to replace.
Why does schedule variance stop being useful near the end?: Because it is measured in money rather than time. Earned value has to reach planned value once all the work is done, so schedule variance converges on zero and the index on one, and a project delivered six months late still finishes at exactly one.
Can this be used on adaptive delivery?: Yes. A release scope and budget supply the baseline and accepted sized items supply the measure of completion. The measure is arguably stronger, since an item is accepted against a definition of done rather than judged as a percentage.