Projects in One Consolidated View
15 simultaneous construction projects visible in a single master dashboard - budget, actuals, committed costs, and forecast-to-complete per project and per cost code.
Case study
15 Projects, One View: Multi-Project Budget vs Actual Tracker Built in Excel
A construction group ran 15 concurrent projects with no consolidated cost view. Each project lived in a separate Excel file, overspend was discovered weeks after the fact, and the commercial team rebuilt the summary manually every week. We built a master Excel tracker that auto-refreshes from all project files.
Weekly Hours Saved on Consolidation
10 hours of manual weekly consolidation work eliminated. Commercial manager's Monday morning summary now generated in under 5 minutes via automated refresh.
Overspend Detection Lead Time
Weekly automated alerts fire when any cost code hits 85% of budget - giving the project team weeks of lead time rather than a month-end surprise.
Reporting Frequency Increased
Summary reporting moved from monthly (when the manual process allowed) to weekly - driven by the same source files with no additional work from the commercial team.
First-Year ROI
240% - time savings across commercial and project management teams, plus variance intervention on two projects that would have overrun without early alerts.
Minutes to Add a New Project
Adding a new project to the master tracker takes 10 minutes - add the file path to the configuration sheet, run a refresh, and the project appears in all dashboards.
The Problem: 15 Files, No Portfolio View
This regional construction group ran between 12 and 18 concurrent projects across residential developments and commercial refurbishment contracts. Each project had its own Excel cost file - created from a template at contract award and maintained by the project manager throughout the build.
The commercial director had no consolidated view of the portfolio. To understand where the business stood, the commercial manager manually opened each project file, extracted the key cost lines, and pasted them into a summary sheet. This took half a day every week and was often slightly out of date before it was finished.
When a project cost code started trending over budget, nobody saw it until the monthly commercial review - by which point the money was spent and the only response was writing off the variance or raising a retrospective variation order.
What We Built
We designed a master Excel budget tracker with three layers: project cost file templates, a Power Query extraction layer, and a VBA-driven summary dashboard.
Each project manager uses a standardised cost file template with defined cost code tables, budget entry, purchase order log, and actual cost columns. The template enforces a consistent data structure that the Power Query connections can read reliably.
Power Query connections in the master tracker link to each project cost file. A single refresh loads all 15 projects' current cost data into staging tables in under 5 minutes. The VBA macro then rebuilds the dashboard - project-level summary, cost code drill-down, variance flags, and forecast-at-completion - from the refreshed staging data.
The weekly alert routine runs on Monday mornings via Task Scheduler. It checks every cost code against its approved budget and sends targeted alerts to the relevant project manager and the commercial director for any code above the 85% threshold.
Dashboard Capabilities
Portfolio Summary View
One-row-per-project summary showing contract value, total budget, actual to date, committed costs, forecast at completion, and expected margin. Colour-coded for at-risk projects.
Cost Code Drill-Down
Click any project row to expand the full cost code breakdown - groundworks, structural, M&E, finishes - with budget vs actual vs forecast at each level.
Committed Cost Tracking
Purchase orders raised but not yet invoiced appear as committed costs - preventing the false comfort of a project that looks on budget but has outstanding POs that will bring it over.
Weekly Variance Alerts
Automated Monday morning emails to project managers and commercial director listing every cost code above the 85% threshold with current actuals and projected overspend.
Forecast at Completion
Project managers enter their completion forecast in the project file. The master dashboard aggregates these to show portfolio-level forecast margin alongside contract value.
Period Trend Charts
Monthly actual spend charts per project generated automatically - same chart template, same formatting, different data each period. No manual chart updates needed.
After Go-Live: What Changed
The Monday morning commercial summary went from a half-day manual exercise to a 5-minute automated refresh. The commercial director now has a live portfolio view every week rather than a manually assembled snapshot once a month.
Two projects triggered variance alerts in the first three months after deployment. Both allowed the commercial team to raise variation orders before the budget was broken. One of those projects would have finished £55,000 over budget under the old process - the alert fired with six weeks remaining on the programme.
Project managers spend less time preparing monthly reports because their cost file doubles as the input to the master tracker. Update the project file and the consolidated dashboard updates with it.
“I used to spend Monday mornings opening files and copying numbers. Now I click refresh, check the alerts, and I'm done in five minutes. The overspend alerts alone have paid for this twice over - we caught two jobs going wrong while we could still do something about it.
Tech Stack
Excel VBA - dashboard rebuild macro, variance alert engine, and chart refresh automation
Power Query - structured connections to all 15 project cost files with transformation mapping per template type
Standardised project cost file template - consistent data structure across all project types enabling reliable extraction
Windows Task Scheduler - Monday morning alert trigger running the variance check without manual intervention
Outlook integration - targeted alert emails with project name, cost code, budget, actual, and variance data
Frequently Asked Questions
How does the master tracker pull data from individual project files automatically?
Power Query connections link the master tracker to each project's cost file. When the refresh runs, Power Query opens each source file, reads the defined cost tables, and loads them into the master staging area. A VBA macro then rebuilds the summary dashboard from the refreshed data. The process runs in under 5 minutes for 15 projects. Project managers update their own files on their own schedule - the master refresh picks up whatever is current at the time it runs.
What happens when a new project is added to the portfolio?
Adding a new project takes about 10 minutes. The project manager creates their cost file from the standard template, saves it to the designated folder, and notifies the commercial team. A configuration sheet in the master tracker holds the list of project file paths - the commercial manager adds the new file path and project code to that list. On the next refresh, the new project appears automatically in the master dashboard without any VBA edits.
Can the tracker handle different cost structures across project types?
Yes - the staging layer normalises data from different project templates into a consistent structure before it reaches the summary dashboard. This client runs residential, commercial, and infrastructure projects, each with slightly different cost code hierarchies. Power Query transformation steps map each source structure to the master cost code taxonomy. New project types require adding a transformation mapping - about 30 minutes of configuration work, not a rebuild.
How does the weekly overspend alert work?
A VBA routine checks actuals against approved budgets for every cost code across all projects. Any cost code where actuals exceed 85% of budget, or where the forecast-to-complete exceeds the approved budget, triggers an email to the relevant project manager and the commercial director. The email includes the project name, cost code description, budget amount, current actual, and variance percentage. Alerts fire on Monday morning from a Windows Task Scheduler trigger - managers arrive to a weekly exception list rather than discovering problems mid-project-review.
Next step
Want similar results in your operations?
Book an Automation Opportunity Assessment. We map workflows and scope a practical build plan.
- No Onsite Visit Required
- No Technical Specification Required
- Assessment Before Commitment
- Clear Scope Before Work Begins
Questions before you book? Speak with our team at +1 385 386 3860
