How to Build a Monthly Project Cost Report in Excel With AI Support (QS Workflow Series, Part 1)
Every month a Quantity Surveyor has to answer one question for management: will we finish inside the budget? The number in the actual cost column does not answer it. It only tells you what has been spent so far, not what the project will cost when it is finished.
Many of us still rebuild the monthly cost report by hand, copy figures between spreadsheets, and spend the last evening before the deadline chasing a total that will not reconcile. This guide shows a cleaner way: a workbook where Excel does the calculating, AI helps with analysis and wording, and the QS checks every line before it is issued.
QS Workflow Series
Part 1: The Monthly Project Cost Report
Each part of this series takes one real QS task, builds the tool in Excel, and tests honestly where AI helps and where it must not be trusted.
Next: Part 2 adapts the same report for the contractor side, with contract value and margin.
📋 What You Will Learn In This Article:
- Why actual cost alone gives a false picture of the project
- The four numbers every monthly cost report needs, and the Estimate at Completion formula
- The reconciliation check that stops a mistyped cost code from hiding money
- How to read month-on-month movement without being misled
- Where Excel Copilot and ChatGPT genuinely help, with ready prompts
- A verification checklist to use before any AI-assisted report is issued
📥 Free Download: The Workbook Used In This Article
10 sheets, 112 live formulas, 5 reconciliation checks, 2 charts and 8 copy-ready prompts. Fictional data. Free, no signup.
Download the Excel Workbook⚠️ All figures in this article are fictional.
I use an invented USD 5 million office building so that no real project information leaves my desk. All amounts are in USD thousands. This is the same principle as in our article on what to check before uploading project documents to AI tools. Use fictional or approved data whenever you test AI with a cost report.
Whose view is this?
This guide uses a project-wide cost view: approved variations increase the adjusted budget. That is how an employer-side or project controls team often reports. If you work for the contractor, your cost budget and your contract value are normally kept separate. A variation changes the contract value and may or may not change the cost budget, depending on your company's policy. Adapt the approved variations column to your own procedure. Part 2 covers the contractor view in full.
Why Actual Cost Alone Misleads
In our fictional project the adjusted budget is USD 5,150k and the actual cost to date is USD 5,100k. Read in isolation, that says only USD 50k of budget remains, and a busy manager may nod and move on.
But USD 410k of orders and subcontracts are already committed and not yet in actual cost. A further USD 240k of work has not been ordered but is still expected. The project will not cost USD 5,100k. It will cost about USD 5,750k, which is USD 600k above the adjusted budget.
Actual cost answers "what have we spent?" The Estimate at Completion answers "where will we finish?" A monthly cost report exists to answer the second question.
The Four Numbers Behind Every Cost Report
Estimate at Completion (EAC), USD '000
| Number | What it means | Common mistake |
|---|---|---|
| Actual cost | Cumulative cost recognised to date | Mixing certified amounts, invoices received and cash paid |
| Remaining commitments | Orders and subcontracts placed, but not yet in actual cost | Counting the full order value instead of the unbilled balance |
| Forecast to complete | Expected cost of work not yet ordered | One lump allowance with no basis behind it |
| Estimate at Completion | Where the project is expected to finish | Double counting the same work in commitments and forecast |
How the Workbook Is Built
The workbook has five input sheets (Budget, Actuals, Commitments, Forecast and Variations), a PriorMonth sheet for last month's totals, one Analysis sheet where every calculation lives, a Dashboard, a Prompts sheet and a ReadMe. You type data only into the input sheets. Everything else is a formula.
The workbook Dashboard (fictional data, USD '000)
The one rule that makes it work: use the same cost code on every sheet. The Analysis sheet pulls each category with SUMIFS, matching on the cost code. These are the formulas for the first work category, where column A holds the cost code:
Original budget (C): =SUMIFS(Budget!$C$6:$C$50,Budget!$A$6:$A$50,$A6) Approved variations (D): =SUMIFS(Variations!$E$6:$E$50,Variations!$C$6:$C$50,$A6,Variations!$D$6:$D$50,"Approved") Adjusted budget (E): =C6+D6 EAC (I): =F6+G6+H6 Cost variance (J): =I6-E6 Variance % (K): =IFERROR(J6/E6,0)
Cost variance is EAC minus adjusted budget, so a positive figure is a forecast overrun. Notice that pending variations are not in the adjusted budget or the EAC. They sit in their own column as exposure, and that is a deliberate choice.
The Check Most Cost Reports Do Not Have
SUMIFS has one quiet weakness. If a cost code on the Actuals sheet is typed as CW001 instead of CW-001, the formula does not complain. It simply leaves that amount out, and the report still looks perfectly tidy.
So the Analysis sheet carries five reconciliation checks. Each one compares the total on a source sheet with the total picked up by Analysis. If they differ by more than USD 0.5k, the status turns to CHECK, and the Dashboard shows "CHECK ANALYSIS SHEET" in red instead of "All OK".
This is the part of the workbook I would keep even if I deleted everything else. A report that does not reconcile to its sources should never leave your desk.
What the Analysis Shows
| Work category | Adjusted budget | EAC | Variance | Variance % |
|---|---|---|---|---|
| Civil Works | 1,900 | 2,100 | 200 | 10.53% |
| Structural Works | 1,250 | 1,270 | 20 | 1.60% |
| MEP Works | 1,000 | 1,300 | 300 | 30.00% |
| Finishing Works | 1,000 | 1,080 | 80 | 8.00% |
| Total | 5,150 | 5,750 | 600 | 11.65% |
Forecast Cost Variance by Work Category (USD '000)
Colour guide used here: green below 5%, gold 5% to 10%, red above 10% of adjusted budget. Set your own thresholds to suit your project.
Reading Month-on-Month Movement
The Dashboard compares this month with last month. In our example, actual cost rose by USD 550k between September and October, yet the EAC rose by only USD 400k.
| Indicator (USD '000) | September | October | Change |
|---|---|---|---|
| Adjusted budget | 5,100 | 5,150 | +50 |
| Actual cost to date | 4,550 | 5,100 | +550 |
| Remaining commitments | 550 | 410 | -140 |
| Forecast to complete | 250 | 240 | -10 |
| Estimate at Completion | 5,350 | 5,750 | +400 |
| Forecast variance vs adjusted budget | 250 | 600 | +350 |
The reason is simple. As work is completed and invoiced, commitments convert into actual cost. The +550 in actual cost is partly offset by -140 in commitments and -10 in forecast. The check that proves the report is consistent: +550 - 140 - 10 = +400, which equals the movement in EAC.
The real news in this table is the last row. The forecast overrun has grown from USD 250k to USD 600k in one month. That is the number to explain to management, and it is the number that deserves a proper investigation before anyone writes a comment on it.
Where AI Helps and Where It Must Not Be Trusted
✅ AI is useful for
- Spotting which categories need attention
- Drafting a first version of the commentary
- Listing questions a QS should investigate
- Structuring a risk register
❌ AI must not be trusted for
- The figures themselves, which Excel calculates
- The causes of an overrun, which need records
- Entitlement, fault or recoverability
- Deciding how pending variations are treated
Excel Copilot can read the Analysis sheet directly if your Microsoft 365 licence includes it. Its features and licensing change often, so check Microsoft's current guidance. Everything in this workbook works without it. With ChatGPT, paste only the fictional or approved Analysis table as text rather than uploading the whole file.
These three prompts do most of the work. The other five are on the Prompts sheet of the workbook.
Prompt 1: Find cost overruns (Excel Copilot)
Analyse the Analysis table. Compare Adjusted Budget (column E) with Estimate at Completion (column I). Identify the work categories with forecast overruns, the largest cost variance, the variance percentage for each category and the categories that need immediate management attention. Use only the existing figures. Do not change any source data or formulas.
Prompt 2: Month-on-month comparison (ChatGPT)
Compare the previous month and current month cost positions shown below. Identify changes in adjusted budget, cumulative actual cost, remaining commitments, forecast cost to complete, Estimate at Completion and forecast variance. Explain what a Quantity Surveyor should investigate. Present a comparison table and a short commentary. Do not assume that an increase in actual cost is an additional project cost unless the data supports it.
Prompt 3: Audit the answer (any AI tool)
List every figure and every stated cause in your report that is not directly supported by the workbook. For each one, say whether it is a calculation, an assumption or an unsupported statement.
Prompt 3 is the one I would never skip. Ask it after every AI-written comment. It makes the tool show you where it has guessed.
What the MEP Overrun Does Not Tell You
MEP Works shows a 30% forecast overrun, the largest in the report. It is tempting to write "MEP is over budget due to additional work" and move on. The data does not support that sentence. These are the questions a QS should answer first:
- Is the USD 150k of remaining commitments for work already included in the USD 1,080k of actual cost? That would be double counting.
- Is the USD 70k forecast backed by a quotation, or is it an allowance?
- VO-003, additional electrical works for USD 120k, is still pending. If it is approved, the budget moves and the overrun shrinks. That is a decision about entitlement, not arithmetic, so it stays out of the EAC until someone with authority approves it.
- Was the original MEP budget too low, or has the scope grown? The answer decides whether this is a cost control problem or a recovery problem.
An overrun in a cost report is a prompt to investigate. It is not, by itself, proof of a loss, a fault or an entitlement. AI can draft the list of questions in seconds. Answering them takes records, site knowledge and judgment.
Your Verification Checklist
Before an AI-assisted cost report is issued, tick every box. These boxes are a reading aid and are not saved.
Excel Calculates. AI Assists. The QS Verifies.
That is the whole method. Let the spreadsheet do the arithmetic, use AI for the first draft and the list of questions, and keep the judgment, the records and the signature with the qualified professional.
Try It With Your Own Numbers
Download the workbook, replace the fictional rows, and let the reconciliation checks tell you if anything is missing.
Download the Workbook Free AI Prompt Generator💬 Found This Useful?
- ⭐ Share it on LinkedIn with a colleague who rebuilds the cost report by hand every month
- 💬 Comment below: what does your monthly cost report get wrong most often? Please do not include confidential project details.
Part 2 of the QS Workflow Series will look at the contractor side of this report.
Frequently Asked Questions
What is Estimate at Completion (EAC) in a construction cost report?
Estimate at Completion is the expected final cost of the project. In this workbook it equals actual cost to date, plus remaining commitments, plus forecast cost to complete. It answers where the project will finish, not just what has been spent.
Why is actual cost not enough to report?
Actual cost ignores work that is already ordered but not yet invoiced, and work that is still to be ordered. A project can show actual cost close to budget and still be heading for a large overrun.
Do I need Excel Copilot to use the workbook?
No. The workbook uses ordinary formulas (SUMIFS and IFERROR) and works in common Excel versions. Copilot and ChatGPT are optional helpers for analysis and wording.
Can I upload my real project cost report to ChatGPT?
Only if your employer and client authorise it and you have checked the account settings. When in doubt, use fictional or approved data. Our article on uploading project documents to AI tools covers the checks in detail.
Is this the contractor view or the employer view?
Part 1 uses a project-wide view in which approved variations increase the adjusted budget. Contractors who keep cost budget and contract value separate should adapt the approved variations column to their own policy. Part 2 of the series covers the contractor side.
Are the figures in this article real?
No. Every figure is fictional and was created for this tutorial. None of it comes from a real project.
About The Author
Thushara Priyanka | BSc (Hons) Quantity Surveying | MRICS | MCIOB | MQSi
Chartered Quantity Surveyor with 19 years of experience on large scale infrastructure projects under FIDIC conditions.
AI Construction Hub shares practical professional guidance for Quantity Surveyors. Read more about the author.
📚 Related Articles and Free Tools:
Editorial note: educational content only, not professional advice. The workbook and all figures are fictional examples. Verify against your contract and your project's cost reporting procedure before using any method on a live project.
Take Your QS Workflow to the Next Level
Choose the free tool that fits your current task:
Comments
Post a Comment