Industry: National law enforcement supplier
Users/Teams: Financial Planning & Analysis (FP&A), department contributors
Technology Stack:
- Microsoft Excel: standardized, protected calculation engines with data validation and named ranges.
- Power Query: master data consolidation with auto refresh, variance checks and drill down pivot reports.
- SharePoint: governance with permission groups, access reviews and audit logging.
- VBA: digitally signed macros that automate repeatable tasks inside the workbooks.
Challenge:
A national law enforcement supplier ran its Annual Investment Management (AIM) process across 13 separate Excel workbooks. Manual copy and paste and broken links produced more than 40 versions per quarter, which made the files fragile and created audit risk.
Each planning cycle took 10 business days. The team spent more than 8 hours on manual consolidation and 4 to 6 hours preparing executive reports. Permissions were inconsistent and there was no audit trail, so shadow IT grew and decisions were delayed.
Solution:
eSoftware Associates standardized and automated the AIM planning process in 8 weeks, from discovery and design through build, user acceptance testing, rollout, training and hypercare. Department contributors kept working in Excel, and FP&A got one consolidated, governed view of every submission.
Key components of the solution included:
- Centralized Excel templates with protected calculation engines, validations and named ranges to keep data clean.
- Power Query workflows that consolidate master data with automated refresh, built in variance checks and interactive drill down pivot tables.
- SharePoint permission groups, periodic access reviews and audit logging to enforce governance and reduce shadow IT.
- Digitally signed VBA macros that automate repeatable steps and cut manual errors.
- Training and support so contributors adopted the new process and power users could own it.
Impact:
The new AIM process is faster, more accurate and easier to control:
- Planning cycle cut from 10 to 4 days, a 60% reduction.
- Manual consolidation down from more than 8 hours to 1.5 hours per cycle, an 81% decrease.
- Executive report preparation down from 4 hours to 45 minutes, also an 81% improvement.
- Errors per cycle down from 15 to 20 to just 3 to 5, a 75% reduction.
- Versions per quarter down from more than 40 to 8 to 10, a 78% decline.
- Payback on the investment in about 1.7 months.
- Faster scenario modeling and 25% better forecast accuracy.
- Clear ownership, a full audit trail and less overtime improved collaboration across teams.
Frequently Asked Questions:
Can Power Query consolidate many Excel workbooks automatically?
Yes. Power Query can pull data from many workbooks into one master model and refresh it on demand, so nobody has to copy and paste between files.
Do VBA macros still make sense with Microsoft 365?
For repeatable tasks inside Excel, yes. Digitally signing the macros lets IT trust them, so they run without users lowering their security settings.
Why keep annual planning in Excel instead of a new FP&A system?
Your finance team already knows Excel. Standardizing and automating it gets most of the speed and control without a long software rollout or retraining.
Need to speed up planning in Excel? Book a call with our Excel team.