Blog · Territory, capacity and quota planning · Construction
Build a cost-code workbook that compares approved budget, actual costs, open commitments and the remaining forecast. The layout includes formulas, a worked construction example and checks that prevent double counting.
A construction cost control Excel sheet should compare the approved cost-code budget with actual costs, open commitments and the remaining forecast. Its main output is forecast final cost, with a clear bridge back to the budget. Build it from the budget register, job-cost export and commitment register, using one reporting date.
Start with a control sheet naming the project, reporting cutoff, currency, budget version and owner. For a US contractor reporting in USD, establish whether each source includes sales tax, freight and allocated overhead. Those treatments must agree before the figures can be compared.
Keep the source exports on separate sheets. The summary should calculate from those records rather than replace them. Preserve transaction IDs, purchase-order IDs and source dates so a reviewer can follow a number back to the file that supplied it.
Use this summary layout. The example puts headers in row 1 and cost codes in rows 2 through 4.
| Column | Heading | Input or formula in row 2 |
|---|---|---|
| A | Cost code | Stable code from the approved budget |
| B | Scope | Description of the work |
| C | Original budget (USD) | Approved starting amount |
| D | Approved changes (USD) | Signed budget changes through the cutoff |
| E | Revised budget (USD) | =C2+D2 |
| F | Actual cost (USD) | Incurred cost, including required accruals |
| G | Open commitments (USD) | Ordered cost not yet included in actuals |
| H | Uncommitted ETC (USD) | Remaining work outside column G |
| I | Forecast final cost (USD) | =SUM(F2:H2) |
| J | Budget headroom (USD) | =E2-I2 |
ETC means estimate to complete. This layout deliberately splits it into committed and uncommitted portions. Positive headroom means the forecast is below budget; negative headroom means a forecast overrun.
You can format each source range as a table using Microsoft's table instructions. Keep the code itself consistent: text codes with leading zeros should stay text in every file.
Suppose the job-cost sheet has project in column A, cost code in B, posting date in C and signed incurred cost in D. Put the selected project in Control!B1 and the cutoff date in Control!B2. For the summary's first cost code, enter:
=SUMIFS(JobCost!$D$2:$D$10000,JobCost!$A$2:$A$10000,Control!$B$1,JobCost!$B$2:$B$10000,$A2,JobCost!$C$2:$C$10000,"<="&Control!$B$2)
This assumes the export contains the project's complete inception-to-date history through the cutoff. If it is a monthly export, append the prior periods before calculating cumulative actuals. Include credits as negative amounts and make sure separately supplied accruals have not already entered the ledger extract.
Microsoft documents SUMIFS as summing amounts that meet multiple criteria. Here the criteria are project, cost code and date; they keep costs from a neighboring job or later reporting period out of the result.
For approved changes, use an equivalent summary of the approved change register. Pending changes need a separate review list. They should not silently increase the approved budget.
Consider a school renovation at September 30, 2026. These are illustrative costs supplied by the contractor; they are not benchmark allowances. All amounts below are whole USD, with no rounding needed.
| Cost code and scope | Original budget (USD) | Approved changes (USD) | Revised budget (USD) | Actual cost (USD) | Open commitments (USD) | Uncommitted ETC (USD) | Forecast final cost (USD) | Headroom (USD) |
|---|---|---|---|---|---|---|---|---|
| 030 Concrete | 300,000 | 20,000 | 320,000 | 180,000 | 110,000 | 40,000 | 330,000 | -10,000 |
| 050 Steel | 400,000 | 0 | 400,000 | 250,000 | 120,000 | 20,000 | 390,000 | 10,000 |
| 260 Electrical | 250,000 | 30,000 | 280,000 | 90,000 | 140,000 | 70,000 | 300,000 | -20,000 |
| Total | 950,000 | 50,000 | 1,000,000 | 520,000 | 370,000 | 130,000 | 1,020,000 | -20,000 |
The project has spent $520,000 against a revised budget of $1,000,000. That alone does not show its expected outcome. Remaining commitments of $370,000 and uncommitted work of $130,000 produce a remaining forecast of $500,000, making forecast final cost $1,020,000.
The electrical package deserves a delivery review because its forecast exceeds its approved budget by $20,000. Steel's $10,000 headroom partly offsets the other packages, but a favorable project total would not remove the need to investigate each code. Use variance analysis for the explanation of what changed between reporting periods.
Open commitments must be a remaining balance, not the face value of every order. In the same example, the commitment register is:
| Cost code | Approved order value (USD) | Cost already incurred against orders (USD) | Open balance (USD) |
|---|---|---|---|
| 030 | 240,000 | 130,000 | 110,000 |
| 050 | 330,000 | 210,000 | 120,000 |
| 260 | 190,000 | 50,000 | 140,000 |
| Total | 760,000 | 390,000 | 370,000 |
The $390,000 already incurred belongs within actual costs. The remaining $130,000 of actual costs is non-order cost: $520,000 minus $390,000. Together they reconcile to the actual-cost total.
For each purchase order, subtract incurred cost assigned to that order from its approved value. Include applicable accruals in that subtraction when they are already in actual costs. Flag negative balances for review instead of hiding them with a formula that forces them to zero.
Run these controls before circulating the sheet:
Also reconcile the job-cost total to finance's project ledger, and list any unmapped cost codes. A zero difference on totals cannot prove that individual codes are correct, so inspect mapping exceptions separately.
The sheet answers what the project is expected to cost. It does not establish how much work has been accepted or when cash will move. Link the same cost codes to the earned value definitions when progress against budget matters, and use the earned value management spreadsheet for that workbook layout.
For payments and collections, maintain a construction cash flow forecast. An invoice can enter actual costs before it is paid, so the cost forecast and bank schedule should share references while retaining their separate meanings.
Covirage is not affiliated with Microsoft.
Bring your budget, job-cost and remaining-commitment figures 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 wider project review.
Add only the portion of purchase orders that remains unincurred. The full order usually includes work already recorded in actual costs. Subtract incurred amounts against each order first, and reconcile the remainder to the outstanding commitment register.
No. Unused budget is approved budget less actual costs. Cost to complete is a forecast of all work still required, including outstanding commitments and work not yet ordered. The forecast can exceed the budget still available.
Use the reporting cycle your project and finance teams can close consistently. Each update needs the same cutoff for ledger costs, accruals, commitments and progress. A more frequent sheet is less useful if its inputs describe different dates.