Monthly Reporting Time Saved
3 days of manual compilation per month reduced to a 20-minute automated run. Finance staff spend those days on analysis, not file-wrangling.
Case study
3 Days to 20 Minutes: Automated Monthly Board Pack for a Property Group
A property investment group's finance team spent the first three days of every month building a 40-tab board report from eight source files. We automated it with Excel VBA and Power Query. The report now runs itself - same quality, same layout, zero manual work.
Source Files Consolidated
8 separate source workbooks - rent rolls, valuation updates, loan schedules, cash flow models, and capex trackers - pulled automatically into one master report.
Board Pack Tabs Automated
40-tab board report - P&L, cash flow, occupancy, debt covenants, portfolio metrics, and executive summary - rebuilt from live data with every run.
Manual Errors Since Deployment
Zero. Automated data extraction and formula-protected output cells eliminated the transcription errors and version mismatches that previously required spot-checks.
Report Release Day
Board pack now delivered on the 2nd working day of the month instead of the 4th - giving the board two extra days of visibility before monthly decisions.
First-Year ROI
400% - three senior finance staff days per month recovered, multiplied by annual billing rate, exceeds the build cost by the third month of deployment.
The Problem: Three Days Vanished Every Month
This property investment group holds 12 assets across residential and commercial sectors. Every month, their three-person finance team spent the first three working days building the board pack - a 40-tab Excel workbook used by the board, lenders, and asset managers to review performance.
The pack drew from eight source files maintained by different teams: the property management software export, three asset-level cash flow models, the group debt schedule, a capital expenditure tracker, the valuation summary, and the monthly rent roll. Each file had a different structure. Each had to be opened, navigated to the right cells, values copied, and pasted into the master template.
Errors crept in when source files had been updated since the last reconciliation, when formulas in the master sheet referenced old cell addresses, or when staff copied values instead of formulas from source files with changed layouts. The finance manager ran a manual check pass after every build. It still took three days.
Two Failure Modes That Kept Repeating
Version Mismatch Between Source and Report
Source files were updated by different teams throughout the month. If a property manager updated their cash flow model on the 3rd and finance pulled data on the 2nd, the board saw stale numbers. Version control was managed by email - unreliable, invisible, and not auditable.
Late Delivery to the Board
The pack consistently arrived on the 4th or 5th working day - after the board's first monthly review meeting had already taken place. Decisions made in that meeting were based on last month's report, not the current one. A two-day delay was embedded in the process by design.
What We Built
We built an Excel VBA automation suite with three components: a data extraction layer using Power Query, a report assembly macro, and a distribution routine.
Power Query connections pull from each of the 8 source files and land the structured data into staging sheets in the master workbook. Staging sheets are named and structured consistently - finance staff never see them; they are the engine room behind the formatted report tabs.
The VBA assembly macro triggers on demand (or via a scheduled Task Scheduler call) and refreshes all Power Query connections, rebuilds the 40 formatted report tabs from the staging data, applies conditional formatting rules for variance highlighting, updates chart series, inserts the current month label across all headers, and generates a PDF copy with the correct filename and date stamp.
A separate distribution routine attaches the PDF to a templated Outlook email addressed to the board distribution list and sends it - the finance manager reviews the attached PDF before confirming the send with one click.
System Capabilities
Power Query Data Extraction
Connects to all 8 source files and loads structured data into staging sheets. Each connection updates in seconds when the macro runs - no manual copy-paste.
Automated Report Assembly
VBA macro rebuilds all 40 tabs from live staging data, preserving exact template layout, formatting, and chart series. Output is identical to a manually built pack.
Conditional Variance Highlighting
Cells exceeding budget or covenant thresholds are highlighted automatically based on rules defined in a configuration sheet - no manual formatting pass needed.
Automated PDF Export
Report saved as a dated PDF with consistent filename convention. Board receives the same format every month - no PDF printer settings, no manual save-as.
Configuration Sheet for Maintenance
File paths, recipient lists, threshold values, and month labels stored in a configuration sheet. Finance staff update settings without touching VBA code.
Error Handling and Missing File Alerts
Macro halts with a named-file error message if any source file is missing or inaccessible - prevents silent data gaps in the distributed report.
After Go-Live: What Changed
The board pack runs on the 1st working day of the month. Finance triggers the macro after confirming all source files have been updated - a 10-minute review process, not a three-day build.
The board now receives the pack before their first monthly meeting. Monthly decisions are made on current data. The finance manager uses the recovered three days for covenant compliance modelling and investor reporting - work that had been deferred or abbreviated under the old process.
The distribution email goes out with one click after the PDF review. The board knows to expect it on the 2nd working day every month without exception.
“We used to dread the first week of every month. Three days building the report, then catching errors, then doing it again. Now I click a button on the morning of the 2nd, check the PDF, and send it. The board gets better numbers faster and my team gets three days back every month.
Tech Stack
Excel VBA - report assembly macro, conditional formatting, PDF export, and Outlook distribution routine
Power Query - structured data extraction from 8 source workbooks into staging sheets
Windows Task Scheduler (optional) - scheduled monthly trigger for fully unattended report generation
Outlook object model - automated PDF attachment and distribution email generation
Configuration sheet architecture - file paths, thresholds, and recipient lists manageable without VBA edits
Frequently Asked Questions
How does VBA automation pull data from multiple source files into one report?
The macro opens each source workbook silently in the background, navigates to the named range or specific cell addresses that hold the data, copies the values, and writes them into the master report template. It does this for each of the 8 source files in sequence, then closes each source file without saving. The whole process - 8 files, 40 tabs, 200+ data points - completes in under 20 minutes because VBA reads the files directly without rendering the full Excel UI for each one.
What happens if one of the source files has been renamed or moved?
The macro checks each file path before opening it. If a file is missing or has been moved, the routine pauses and displays a clear error message naming the missing file - it does not silently skip it or produce a report with gaps. The finance team gets a specific file name to chase rather than discovering incomplete data after the report has been distributed. File path mappings are stored in a configuration sheet so the team can update paths without editing VBA code.
Can the automated report include charts and formatted visualisations?
Yes - charts in the master template are linked to the refreshed data ranges, so they update automatically when the macro runs. We used this for revenue trend charts, portfolio mix pie charts, and occupancy rate bar charts in this client's report. The chart series and formatting stay intact across each monthly run; only the underlying data changes. The output is indistinguishable from a manually formatted report - same fonts, same colours, same layout every time.
Could this be done with Power Query instead of VBA?
Power Query is the better choice when source file structures are consistent and the main task is transforming and combining data. VBA is the better choice when the output needs precise formatting control, when charts and narrative sections need to be preserved in the exact template layout, or when the report has complex conditional logic - like flagging variances over a threshold or inserting commentary sections. This client's board pack required both: Power Query handled the data pulls and transformations; VBA handled the conditional formatting, chart refreshes, and the PDF export at the end.
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
