Weekly Cost Reporting Hours Saved
12 hours per week of manual Excel consolidation, copying actuals from site files, and reformatting monthly reports eliminated from the commercial team's workload.
Case study
No More End-of-Month Surprises: Job Costing System Built in MS Access for a Construction Firm
A mid-size construction company was discovering budget overruns weeks after the money had already been spent. Separate Excel files per project, no consolidated view, and no early warning system. We built a centralised Access job costing database that flags variances before they become write-offs.
Budget Overrun Detection
Variance alerts fire when any cost code reaches 80% of budget - weeks before month-end reconciliation would have surfaced the problem.
Active Projects in One System
52 simultaneous projects visible in a single database with budget, actuals, committed costs, and forecast-to-complete on one screen per job.
Misposted Invoices After Go-Live
PO matching and cost code validation rules reduced invoice misposting to zero - previously averaging 8-12 per month with manual correction time.
ROI in Year One
280% - driven by commercial team time savings, early variance intervention that avoided three projected overruns, and eliminated rework from misposted costs.
Months to Full Deployment
Data import, parallel running, and full staff training completed in 6 weeks. All active projects live in the system before the next month-end cycle.
The Operation Before We Stepped In
This 80-person construction firm ran between 40 and 60 active projects at any time - residential developments, commercial fit-outs, and infrastructure maintenance contracts. Each project had its own Excel job cost file created from a template at contract award.
Project managers updated their own files as invoices arrived and costs were allocated. Finance ran a monthly consolidation exercise - opening each file, extracting the numbers into a master summary sheet, and manually checking for posting errors. The commercial director then reviewed this summary at month-end to understand where the business stood.
By the time a budget overrun appeared in the monthly summary, the money was already spent. The firm had no mechanism to flag a developing variance before it became a write-off.
Three Problems That Were Costing Real Money
1. Budget Overruns Discovered After the Fact
Monthly reporting meant cost overruns on individual cost codes - groundworks, structural, M&E - were only visible at month-end. By then, subcontractor invoices had already been approved and posted. Three of the previous year's twelve major projects finished over budget on items that had been trending over for weeks before anyone saw the numbers.
2. Invoice Misposting to Wrong Jobs
Subcontractor invoices processed by finance were matched to project codes manually by looking up job numbers in email approvals. With 50+ active jobs, invoices regularly landed on the wrong project or the wrong cost code. Finding and correcting them took hours and created reconciliation issues at audit.
3. 12 Hours a Week of Manual Consolidation
The commercial manager spent half of every Thursday extracting numbers from 50+ project files into the monthly summary. Any late invoice postings by project managers invalidated the summary. She ran it twice most months - once mid-month as a forecast and once at close - consuming an entire working week per month in file-wrangling.
What We Built
We designed a centralised MS Access job costing system with four modules: Jobs, Cost Codes, Purchase Orders, and Invoices. All project cost data lives in one database - no more separate files per site.
The Jobs module holds contract details, budget by cost code, and forecast-to-complete for each active project. Project managers update their forecast as site conditions change; the system instantly recalculates the expected margin and flags jobs where completion cost now exceeds the original contract value.
Purchase orders are raised in Access before subcontractors start work. When an invoice arrives, finance matches it to the open PO - confirming job number, cost code, and amount within a tolerance band. Unmatched invoices are held in a review queue, not posted. This single change eliminated the misposting problem entirely.
A VBA alert routine runs every evening, checks actual-to-budget ratios across all cost codes, and emails the relevant project manager when any code hits 80% of its approved budget. The commercial director receives a weekly exception summary listing every job with a code above threshold and the current forecast variance.
The SQL Server backend handles 8 concurrent users entering cost data during peak periods - site managers logging daily plant hire, finance posting subcontractor invoices, and the commercial team updating forecasts - without locking conflicts.
System Capabilities
Cost Code Budget vs Actual
Every job has a budget broken down to cost code level. Actuals post against the same structure as invoices are matched. Variance visible in real time - not at month-end.
Purchase Order Matching
Invoices matched to approved POs before posting. Over-tolerance and unmatched invoices held in review queue - not auto-approved. Misposting rate dropped to zero.
Forecast-to-Complete
Project managers update their completion forecast directly in the job record. The system recalculates projected margin and flags contracts where completion cost now exceeds contract value.
Automated Variance Alerts
VBA alert engine flags any cost code at 80% of budget via direct email to the project manager - before the budget is broken, not after.
Monthly Commercial Reports
One-click report generation for the monthly commercial review - all active jobs, current margin, forecast at completion, and cost code variances - generated from live data in under two minutes.
Subcontractor and Supplier Register
Approved supplier list linked to the PO module. New POs can only be raised against suppliers on the approved register, with commercial manager override for exceptions.
After Go-Live: What Changed
The commercial manager's Thursday consolidation exercise no longer exists. The monthly commercial report is generated in two minutes from live data. Finance runs the same report mid-month for the forecast without any additional work.
In the six months after deployment, three projects flagged via the 80% variance alert allowed the commercial team to raise variation orders before costs were incurred. One of those projects would have been a £40,000 overrun under the old system. The alert fired six weeks before month-end - enough time to adjust scope and protect the margin.
Invoice mispostings went from a recurring audit issue to a non-event. The PO matching module stopped invoices landing on the wrong job before they could be posted. Year-end audit preparation time for cost reconciliation dropped by two days.
“We used to find out about overruns when we were doing the month-end. By then there was nothing to do except write it off. Now we get an alert when a cost code is trending over and we still have time to raise a variation or re-scope. We recovered more than the build cost in the first three months just from catching those early warnings.
Tech Stack
Microsoft Access - job cost forms, PO module, invoice matching, and management reports
VBA automation - variance alert engine, PO tolerance checks, and monthly report generation
SQL Server backend - multi-user concurrent access for 8 simultaneous users across finance and site teams
Outlook integration - automated variance alert emails and weekly commercial exception digests
Excel export module - formatted monthly reports for board pack distribution
Is This the Right Fit?
A custom Access job costing system works best when your business runs multiple simultaneous projects with distinct cost structures, when per-project Excel files have grown unmanageable, and when your team needs real-time variance visibility rather than month-end surprises. Typical fits:
Construction and civil engineering firms with 10+ concurrent projects
Where cost codes, PO matching, and subcontractor invoice management are the core problem - not a simple summary spreadsheet.
Fit-out and refurbishment contractors
Short-cycle projects with high invoice volumes make accurate PO matching and cost code allocation especially valuable.
Infrastructure maintenance businesses
Rolling work orders and framework contracts benefit from the same cost code discipline applied to each work instruction.
Frequently Asked Questions
Can MS Access handle job costing for a construction firm with 40+ active projects?
Yes - for a firm running 40-60 simultaneous projects, Access is well within its operational range. Each job gets its own cost code hierarchy, budget entries, purchase order log, and actual cost records. Reports pulling across all jobs run in seconds. This client manages 52 active sites with 8 users entering cost data concurrently on a SQL Server backend. Access has not been the bottleneck at any point.
How does the system handle subcontractor invoices and purchase orders?
The PO module lets project managers raise a purchase order against a specific cost code before the work starts. When the subcontractor invoice arrives, the finance clerk matches it to the open PO - the system confirms the amount is within tolerance and marks the PO as received. Unmatched invoices or over-tolerance variances are flagged and held for manager approval before they can be posted to job costs. This stopped the problem of invoices being booked to the wrong project or against a cost code that had already hit budget.
What does a job cost variance alert look like in practice?
When cumulative actual costs on any cost code reach 80% of the approved budget, the project manager receives an Outlook email with the job number, cost code, budget amount, actual to date, and projected completion cost. At 100% of budget, the alert escalates to the commercial director. Managers use this to either raise a variation order before the budget is broken or reforecast the completion cost - rather than discovering the overrun at month-end.
How long did it take to migrate from spreadsheets to the Access system?
The full migration - data import, staff training, and parallel running - took six weeks. We imported the last three years of completed job cost data in the first week so historical reporting was available from day one. Two weeks of parallel running on live projects confirmed the numbers matched. Training was four hours per role: two hours for project managers entering costs and raising POs, two hours for finance reconciling invoices. By week six, all active projects were live in the new system and the spreadsheets were retired.
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
