Weekly Consolidation Hours Eliminated
4 hours of Monday morning file collection, validation, and manual consolidation replaced by an overnight automated run. Team arrives Monday to a ready master report.
Case study
Half-Day Monday Exercise to Overnight Automation: 28 Branch Reports Consolidated by VBA
A retail group with 28 branches received weekly Excel performance reports from each site by email. HQ spent every Monday morning - 4 hours - opening files, checking structure, and pasting data into the master. We automated the entire process to run overnight Sunday, so the consolidated master is ready before the team arrives on Monday.
Branch Reports Consolidated
28 branch Excel reports picked up from a shared Outlook mailbox, validated against structure rules, and consolidated into the master in a single overnight run.
Exceptions Flagged Automatically
Missing submissions, structural validation failures, and outlier data values flagged in an exceptions log - HQ team sees exactly which branches need follow-up without manual file review.
Data Quality Issues Identified
Outlier detection flags branches with values more than 2 standard deviations from the period average - catching keying errors and genuine performance anomalies for the same morning review.
Missing Submissions Tracked
Branches that have not submitted by the deadline appear in the missing log by name, region, and last submission date - replacing the manual count that previously involved opening the email inbox and scrolling.
First-Year ROI
290% - 4 hours per week recovered at HQ analyst rates, data quality errors caught earlier, and one stockout avoided by a Monday morning outlier flag that triggered a same-day replenishment order.
The Problem: A Half-Day That Had No Business Reason to Exist
This retail group operates 28 stores across two regions. Each branch manager submits a weekly performance report every Sunday evening - sales, footfall, stock-on-hand, promotional compliance, and waste figures - as an Excel file attached to an email to the HQ trading team.
Every Monday morning, the trading analyst opened each of those 28 emails, downloaded the attachment, opened the file, checked the structure had not changed, copied the data range, and pasted it into the corresponding section of the master consolidation sheet. For 28 branches, this took approximately 4 hours. When branches submitted files with incorrect column layouts or missing sheets - which happened 6-8 times per week - additional time was spent chasing resubmissions.
The consolidated master was rarely ready before midday Monday. The weekly trading review meeting, scheduled at 11am, routinely started before the data was complete.
What We Built
We built a VBA automation suite with four components: an email collection routine, a file validation engine, a consolidation processor, and an exceptions and outlier reporting module.
All branches submit their weekly report to a dedicated shared Outlook mailbox. The VBA routine connects to this mailbox on Sunday night via Task Scheduler, identifies emails with the expected subject line pattern, downloads attachments to a staging folder, and marks them as processed. Branches that have not submitted appear in the missing log with their region and last submission date.
Each downloaded file passes through a validation engine before data is extracted. The engine checks for the correct sheet name, expected column headers in the right positions, and data types in key columns. Files that pass are queued for consolidation; files that fail are logged with the specific failure reason - the HQ team contacts those branches directly Monday morning rather than discovering the problem mid-consolidation.
The consolidation processor reads the validated data from each branch file and writes it into the master consolidation sheet - one row per branch per week, all 28 branches in one pass. After consolidation, an outlier detection routine flags any branch metric more than 2 standard deviations from the period average for review.
The completed master with exceptions and outlier flags is saved and a summary email sent to the trading analyst: X of 28 submissions received, Y exceptions, Z outlier flags. The analyst arrives Monday morning to a complete report and a targeted action list.
Automation Capabilities
Automated Email File Collection
VBA connects to shared Outlook mailbox on schedule, downloads attachments matching the submission pattern, and marks emails as processed - no manual file management.
Structural Validation Before Consolidation
Each file validated against expected column headers, sheet names, and data types. Invalid files logged with specific failure reason rather than causing consolidation errors.
Master Report Consolidation
All 28 validated branch files read and written to the master in a single pass - one row per branch, all metrics, ready for the 11am trading meeting.
Missing Submission Tracking
Branches that have not submitted appear in the missing log by name, region, and last submission date - replacing manual inbox scroll with a targeted follow-up list.
Outlier Detection and Flagging
Automated statistical check flags branches with metric values more than 2 standard deviations from the period average - catching both keying errors and genuine performance anomalies.
Summary Notification Email
Automated Monday morning email to trading analyst: submission count, exceptions list, outlier flags, and master report location. Complete picture in one message before the day starts.
After Go-Live: What Changed
The trading analyst arrives Monday morning to a complete consolidation, an exceptions list, and an outlier report. The time she previously spent on manual consolidation is spent reviewing and acting on the flags - a fundamentally different and more valuable use of the same Monday morning slot.
The 11am trading meeting now starts with complete data every week. The change was noted by the trading director in the first week - the conversation shifted from "we're still waiting for three branches" to "we have all 28, and here are the outliers worth discussing."
Two months after deployment, the outlier flag system identified a branch with anomalously low stock-on-hand on a promotional item. The replenishment order was placed the same morning. Under the old process, that metric would not have been reviewed until the afternoon - by which point the item would have been out of stock for half the trading day.
“Mondays used to be four hours of copy and paste before I could do anything useful. Now I come in, check the exceptions, review the outliers, and I'm ready for the trading meeting with the full picture. The team can't believe we did it manually for so long.
Tech Stack
Excel VBA - consolidation engine, validation logic, outlier detection, and master report assembly
Outlook object model - shared mailbox connection for automated attachment collection and submission tracking
Windows Task Scheduler - Sunday night trigger for fully unattended collection and consolidation run
Structural validation engine - column header, sheet name, and data type checks before data extraction
Statistical outlier detection - 2-standard-deviation flag on all branch metrics for data quality and performance review
Frequently Asked Questions
How does the macro pick up emailed Excel files from 28 branches automatically?
Branch managers email their weekly reports to a dedicated shared Outlook mailbox - not to a person's inbox. The VBA routine connects to this mailbox via the Outlook object model, filters for emails with subject lines matching the expected weekly report pattern, downloads the Excel attachments to a staging folder, and marks each email as processed. The macro runs on Sunday night via Windows Task Scheduler so Monday morning the staging folder has all received files ready for consolidation. Branches that have not submitted by Sunday night appear in the missing submissions log.
What happens when a branch submits a file with a different column structure?
The macro validates each file against the expected column headers before attempting to read data from it. Files that pass validation are consolidated; files that fail appear in the exceptions log with the branch name and the specific validation failure - missing column, wrong sheet name, unexpected format. The consolidation runs for valid files; the HQ team contacts the exceptions branches directly for resubmission. Validation failures dropped from 6-8 per week in the first month to 0-1 per week by month three, as branches corrected their templates.
How does the system handle branches in different time zones?
Submission deadline is set as Sunday midnight in the branch's local time zone. The shared mailbox consolidation run is configured to accept submissions up to Monday 6am UK time, which covers all time zones in scope. The missing submissions log flags branches by local time zone so the HQ team can assess whether a late submission is genuinely missing or still in transit. For international operations, this time window has been sufficient - no legitimate submission has been marked as missing.
Could this be done with Power BI instead of VBA?
Power BI is a better choice when the source data structure is consistent and the primary goal is interactive visualisation. This solution needed three things Power BI does not handle well: picking up files from an email inbox (not a folder or database), applying data quality validation rules with targeted exception feedback, and writing the consolidated output back into a specific, historically formatted Excel master that other parts of the business read from. VBA was the right tool here. For the reporting layer on top of the consolidated data, Power BI is used - it connects to the master Excel file that VBA produces.
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
