Industry: Technology / Software
Users/Teams: Financial Planning & Analysis (FP&A), Department Contributors
Technology Stack:
- Microsoft Excel: Standardized and protected calculation engines with data validations and named ranges.
- Power Query: Automated master data consolidation with auto-refresh, variance checks, and drill-down pivot reports.
- SharePoint: Governance platform managing permissions, access reviews, and audit logging.
- VBA (digitally signed macros): Enabled secure automation within Excel workbooks to reduce manual tasks.
Challenge:
A high-growth technology company managing its Annual Investment Management (AIM) process across 13 separate Excel workbooks faced significant challenges. Manual copy-pasting and broken links resulted in over 40 versions per quarter, leading to fragility and audit risks. The planning cycle spanned 10 business days with more than 8 hours spent on manual consolidation and 4 to 6 hours dedicated to executive report preparation. Inconsistent permissions and the absence of audit trails fostered shadow IT practices and governance gaps, causing delayed decision making and productivity losses.
Solution:
eSoftware Associates delivered a comprehensive solution to standardize and automate the AIM planning process within 8 weeks—from discovery and design through build, user acceptance testing, rollout, training, and hypercare.
Core solution benefits:
- Centralized, standardized Excel templates with protected calculation engines, validations, and named ranges to improve data integrity.
- Power Query workflows for master data consolidation with automated refresh, built-in variance checks, and interactive drill-down pivot tables.
- Governance enforced through SharePoint permission groups, periodic access reviews, and audit logging to ensure compliance and reduce shadow IT risks.
- Digitally signed VBA macros embedded securely within Excel to automate repeatable tasks, ensuring consistency and reducing manual errors.
- Training and support to promote user adoption and empower power-users.
Impact:
The AIM automation project generated significant improvements across multiple dimensions:
- Planning cycle duration cut from 10 to 4 days, a 60% reduction.
- Manual consolidation time reduced from over 8 hours to 1.5 hours per cycle, an 81% decrease.
- Executive preparation time decreased from 4 hours to 45 minutes, also an 81% improvement.
- Error incidents lowered from 15–20 per cycle to just 3–5, a 75% reduction.
- Versions per quarter dropped from over 40 to 8–10, achieving a 78% decline.
- Achieved payback on investment in approximately 1.7 months.
- Enabled faster scenario modeling and improved forecasting accuracy by 25%.
- Clear ownership, audit trail, and reduced overtime contributed to a better collaboration and cross-team experience.
Summary:
By standardizing planning templates, automating data consolidation through Power Query, instituting governance with SharePoint, and embedding secure VBA automation, eSoftware Associates helped the client radically streamline the AIM process. This modernization shortened the planning cycle, enhanced data accuracy, reduced manual efforts, and fostered a stronger control environment. The solution significantly boosted productivity and executive insight, enabling faster and more confident financial planning decisions.
Ready to standardize planning and speed up executive insight? We can build and govern it, fast.