Invoice Reconciliation: From 6 Hours to 22 Minutes
Real case study from a FMCG finance team — the exact VBA automation built, how long it took, what it handles now, and what the ROI looked like after 3 months in production.
The problem: 6 hours every Friday
A finance team at an FMCG company spent every Friday afternoon reconciling 200+ vendor invoices across three separate Excel files: one exported from the ERP system, one received from each vendor, and one internal payment tracker. The process required opening all three files, manually finding invoice number matches, checking amounts, flagging discrepancies, and updating the tracker. With two analysts involved, the process consumed roughly 12 person-hours per week — over 600 hours annually.
Why previous attempts at automation had failed
The team had tried VLOOKUP-based matching previously. It failed repeatedly because the vendor file format changed slightly every few weeks — different column headers, different invoice number prefixes, extra blank rows. That inconsistency broke the formula logic each time. The real problem was not the matching itself but the unreliable input format. VBA is well-suited to this because it can handle dynamic format variations programmatically rather than assuming a fixed structure.
What was built
A VBA macro that: opens all three source files from a fixed folder path, normalises invoice number formats across all three (removing vendor-specific prefixes, trimming whitespace, converting to uppercase), runs three-way matching using a dictionary object for performance, categorises each exception (amount mismatch, present in ERP but missing from vendor file, present in vendor file but missing from ERP, duplicate entries), and generates a formatted exception report with a full audit trail. One button press, 22 minutes start to finish.
The validation layer — the most important part
Before running any reconciliation logic, the macro checks: all three source files exist and are accessible, invoice counts in each file are within expected range (a sudden drop signals a data export problem), and no duplicate invoice numbers appear in the internal tracker. If any check fails, the macro stops and displays a clear error message before touching any data. This prevented the automation from silently producing incorrect reconciliation output — the most dangerous failure mode in a financial process.
Results after 3 months in production
Full reconciliation now runs in 22 minutes instead of 6 hours. The error rate on matched invoices dropped from approximately 3% (manual misses) to near zero — only genuine data discrepancies get flagged. The two analysts recovered over 500 person-hours in the first 3 months and redirected that time to supplier relationship management, payment dispute resolution and cash flow analysis — work that genuinely required human judgment and relationship context.
This topic is covered in the VBA & Macros for Excel course. Sessions are live, practical and taught with real business data — a natural next step if this article matches what you are working on.