Blog · Territory, capacity and quota planning · Construction
Build a workbook for planned value, earned value, actual costs and completion estimates. The construction example includes package-level formulas, reconciliation checks and a cumulative earned-value chart layout.
An earned value management spreadsheet compares the budgeted work scheduled, the budgeted work accepted and the actual cost incurred at one reporting date. Build one row per work package, calculate the project totals from dollars, and keep the remaining forecast beside the performance measures. The spreadsheet needs progress evidence as well as financial records.
Create a control sheet with project, reporting date, baseline version, cost basis and currency. Keep the approved package budgets separate from later changes so a reviewer can identify which baseline produced the comparison. Record who accepted the progress and who approved the remaining forecast.
The Department of Energy describes earned value management as integrating cost, schedule and technical scope accomplishments. That integration is the reason the workbook cannot be built from the ledger alone. A paid invoice can support actual cost without demonstrating that the related milestone was completed.
Use the existing earned value formula guide for the definitions and choice of completion-estimate assumptions. Here the deliverable is a repeatable workbook with its inputs, outputs and checks in view.
Keep these source sheets:
Put headers in row 1 and packages in rows 2 through 4. Percentage inputs are numeric shares formatted as percentages. Keep full precision in calculations and round only the displayed dollar amounts and indices.
| Column | Field | Input or formula in row 2 |
|---|---|---|
| A | Work package | Stable package ID and label |
| B | BAC (USD) | Approved budget at completion |
| C | Planned share | Baseline progress at the reporting date |
| D | Earned share | Accepted progress under the agreed rule |
| E | AC (USD) | Actual cost through the same cutoff |
| F | Bottom-up ETC (USD) | Owner's supported remaining-cost forecast |
| G | PV (USD) | =B2*C2 |
| H | EV (USD) | =B2*D2 |
| I | CV (USD) | =H2-E2 |
| J | SV (USD) | =H2-G2 |
| K | CPI | =IF(E2=0,"",H2/E2) |
| L | SPI | =IF(G2=0,"",H2/G2) |
| M | Bottom-up EAC (USD) | =E2+F2 |
| N | VAC (USD) | =B2-M2 |
PV is planned value, EV is earned value and AC is actual cost. CV and SV are cost and schedule variances; CPI and SPI are their respective performance indices. EAC is estimate at completion, and VAC is budget less that estimate.
For actuals, suppose the source sheet has package ID in A, posting date in B and cost in C. With the reporting cutoff in Control!B2, calculate E2 as:
=SUMIFS(Actuals!$C$2:$C$10000,Actuals!$A$2:$A$10000,$A2,Actuals!$B$2:$B$10000,"<="&Control!$B$2)
This assumes the export contains only the selected project's complete history. If it contains multiple projects, add the project as another criterion. Microsoft documents SUMIFS for exactly this type of multiple-criteria total.
A US contractor reviews three packages at September 30, 2026. The progress register supplies these accepted-unit measures. In this illustration, units within each package carry equal budget weight; real packages need the weighting rule agreed before reporting.
| Package | Total units | Planned units | Accepted units | Planned share | Earned share |
|---|---|---|---|---|---|
| Design acceptance | 1 | 1 | 1 | 100% | 100% |
| Structure units | 1,000 | 800 | 700 | 80% | 70% |
| Services units | 100 | 40 | 25 | 40% | 25% |
Calculate each share as its planned or accepted units divided by total units. The packages have different units, so do not add the unit columns together or average their completion percentages.
| Package | BAC (USD) | Planned share | Earned share | PV (USD) | EV (USD) | AC (USD) | Bottom-up ETC (USD) |
|---|---|---|---|---|---|---|---|
| Design | 100,000 | 100% | 100% | 100,000 | 100,000 | 110,000 | 0 |
| Structure | 500,000 | 80% | 70% | 400,000 | 350,000 | 390,000 | 145,000 |
| Services | 400,000 | 40% | 25% | 160,000 | 100,000 | 130,000 | 310,000 |
| Project | 1,000,000 | 660,000 | 550,000 | 630,000 | 455,000 |
The project has earned $550,000 of budgeted work against $660,000 planned. That work cost $630,000. The summary calculations are:
| Measure | Calculation | Result |
|---|---|---|
| Cost variance (USD) | 550,000 - 630,000 | -80,000 |
| Schedule variance (USD) | 550,000 - 660,000 | -110,000 |
| CPI | 550,000 / 630,000 | 0.8730 |
| SPI | 550,000 / 660,000 | 0.8333 |
| Bottom-up EAC (USD) | 630,000 + 455,000 | 1,085,000 |
| VAC (USD) | 1,000,000 - 1,085,000 | -85,000 |
Indices are displayed to four decimal places. In row 5, calculate project CPI as =SUM(H2:H4)/SUM(E2:E4) and SPI as =SUM(H2:H4)/SUM(G2:G4). Never sum or average the package indices to produce the project result.
Show the package forecast as well as the project result. Design's EAC is $110,000; structure's is $535,000; services' is $440,000. They add to $1,085,000, matching actual cost plus the summed remaining forecast.
As a reasonableness check, calculate a separate estimate assuming cost efficiency to date continues:
=SUM(B2:B4)/(SUM(H2:H4)/SUM(E2:E4))
That returns $1,145,454.55, using the unrounded CPI. It is $60,454.55 above the bottom-up EAC. The gap is a question for the forecast owners: what remaining work, rates or one-time costs explain the expected improvement? The formula is an alternative assumption, not another cost to add.
Reconcile the bottom-up ETC to open commitments and work not yet ordered. The construction cost control sheet supplies that bridge. An order already included in ETC must not enter the completion estimate a second time.
Save the cumulative project totals at each reporting date. Do not rebuild historical EV using today's progress percentage. These earlier snapshots lead to the same September totals:
| Reporting date | Cumulative PV (USD) | Cumulative EV (USD) | Cumulative AC (USD) |
|---|---|---|---|
| July 31, 2026 | 180,000 | 160,000 | 175,000 |
| August 31, 2026 | 400,000 | 350,000 | 390,000 |
| September 30, 2026 | 660,000 | 550,000 | 630,000 |
Select the date and three value columns, then insert a line chart. Microsoft's chart instructions explain selecting data and creating the chart. Label the vertical axis USD and keep all three series cumulative; mixing monthly actuals with cumulative earned value gives a misleading comparison.
Use variance analysis to explain the causes behind the chart. Keep the construction cash forecast alongside it when timing of collections and payments affects delivery decisions.
Covirage is not affiliated with Microsoft.
Bring the package budgets, dated progress, actual costs and remaining estimates in a workbook or Google Sheet. Covirage maps the columns, its tools compute and check the comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for construction for the broader project review.
Spending is a cost measure, not evidence that work was accepted. Use the progress rule agreed for each package, such as accepted units or weighted milestones. Record its source and reporting date before calculating earned value.
Calculate project CPI by dividing total earned value by total actual cost. A simple average gives a small package the same weight as a large package. Keep package indices for investigation, while deriving the project result from summed dollars.
No. Schedule variance measures budgeted work ahead of or behind the baseline, rather than days of delay. Keep the delivery schedule and its dependencies beside the workbook when deciding whether the completion date remains achievable.