The Challenge
A Galway-based professional services firm providing HR consulting to clients across Connacht had grown to a team of 22 over seven years. Their financial processes, however, were still running on the same spreadsheet model built during their first year of operation.
Month-end close required two senior staff members to dedicate three full working days to manually copying data between 12 linked Excel files, updating pivot tables, reconciling figures across departments, and assembling the final P&L and project margin report. The whole process was undocumented — it lived in the heads of the two people who had always done it.
When one of those staff members announced she was leaving, the business owner realised how exposed they were. Rebuilding this knowledge from scratch would take months and carry significant risk.
Key risk: The entire month-end process depended on institutional knowledge held by one person. There was no documentation, no backup, and no way to onboard a replacement quickly.
The Audit Findings
Data Sensum's free productivity audit documented the full close process for the first time. Key findings included:
- 12 separate workbooks with manual copy-paste dependencies between them — a single error cascaded across all downstream files
- No input validation — anyone could type anything into any cell, silently breaking formulas
- Pivot tables required manual refresh in a specific order — skipping a step caused calculation errors
- The process had grown organically and contained redundant steps that no longer served any purpose
- No audit trail — impossible to know who changed what or when
The Solution
We consolidated the 12 workbooks into a single macro-enabled master file with a clean, structured architecture. The solution included:
- A protected data entry sheet with dropdown validation, preventing invalid inputs at source
- VBA macros that import data from source exports, apply transformations, and refresh all calculations in the correct order — triggered by a single button
- Automated reconciliation checks that flag any discrepancies before the report is finalised
- A one-click report generation macro that assembles and formats the final P&L and margin report, ready for management review
- Full documentation embedded in the workbook, so any member of staff can run the process
We also ran a half-day training session with the full finance team so that the process was no longer dependent on a single person.
Bonus outcome: Because the new system is documented and requires no specialist knowledge, the firm successfully onboarded a replacement staff member who ran month-end independently within their first week.
The Results
The first month-end close using the new system took 4 hours — down from 3 full days. The two senior staff members reclaimed the equivalent of 2 full working days per month, which have since been redirected to client-facing work.
Formula errors dropped to zero. Management now receive their month-end report on day 1 of the new month rather than day 10–12, significantly improving their ability to respond to business performance in real time.
"I used to dread the first week of every month. Now I press a button, go for a coffee, and the report is done when I get back. I genuinely can't believe we waited so long to sort this out."
— Director, Galway Professional Services Firm