Introduction
If your financial consolidation process involves a folder full of Excel workbooks, manual data exports from multiple company databases, and a controller who is the only person who truly understands how it all fits together, you’re not alone.
Most investment managers with complex legal entity structures have evolved their consolidation processes organically over years. What started as a simple spreadsheet to combine two entities has grown into an elaborate, fragile system that nobody wants to touch.
The problem isn’t Excel. Excel is a great tool. The problem is using Excel for something it was never designed to do: serve as your system of record for consolidated financial statements.
The Real Cost of Spreadsheet Consolidation
Let’s be honest about what spreadsheet consolidation actually costs:
Time: A multi-entity investment manager with 30+ legal entities typically spends 10-15 days on consolidation activities at month-end. That’s 10-15 days where senior finance staff are focused on mechanics rather than analysis.
Accuracy: Studies consistently show that 90% of spreadsheets contain errors. When your consolidation model has thousands of formulas, links to external workbooks, and manual data entry points, the probability of material error is significant.
Key Person Risk: Every firm has that one person who built the consolidation model and is the only one who truly understands it. When that person leaves, takes vacation, or gets sick during close, the firm is exposed.
Audit Pain: External auditors need to understand and test your consolidation process. Explaining a maze of linked spreadsheets, documenting control points that exist only in someone’s head, and demonstrating completeness of consolidation entries takes time and money.
Scalability: Every acquisition adds complexity to the model. Every new intercompany relationship requires new elimination logic. At some point, the model becomes too complex to maintain.
What the Data Says: The Value of Automation
Recent research from McKinsey and Gartner highlights a widening gap between “manual” finance teams and those that have modernized. According to a McKinsey survey, companies with highly automated finance teams spend 60% more time on strategic activities than those bogged down by manual work.
The efficiency gains are measurable:
- Automation Potential: McKinsey finds that up to 50% of work in a typical finance department is devoted to transaction processing and reconciliation—tasks that are highly susceptible to automation.
- Close Benchmarks: Gartner reports that while the average firm takes 12 days to close, top-performing teams using modern ERPs complete the process in just 6 days.
- ROI: For firms managing 35+ entities, automating financial consolidation typically delivers a full ROI within 18-24 months by reducing audit fees and labor costs.
What Modern Consolidation Looks Like
D365 Finance approaches consolidation fundamentally differently. Rather than extracting data to an external tool, consolidation happens within the system where the source data lives.
Online Consolidation: When all legal entities are in the same D365 Finance environment, consolidation can happen in real-time. No batch jobs. No data extraction. Click a button and consolidated financials reflect current source data. For a 35-entity investment manager, this alone can reduce consolidation time from days to hours.
Import Consolidation: For entities on different systems (perhaps acquired companies that haven’t been migrated yet), D365 Finance can import financial data and incorporate it into the consolidated picture. This hybrid approach lets firms consolidate their entire structure even when not all entities are on the same platform.
Account Mapping: Different entities may have different charts of accounts. A UK subsidiary might use an IFRS-aligned structure while US entities use a GAAP structure. Consolidation account mapping translates local charts to a standardized consolidated chart without requiring all entities to change their local setup.
Currency Translation: Overseas subsidiaries need currency translation for consolidation. D365 Finance handles this automatically with configurable rate types: current rates for balance sheet items, average rates for income statement items, historical rates for equity accounts. No manual calculation required.
Automated Eliminations
Intercompany eliminations are where spreadsheet consolidation really breaks down. The volume of intercompany activity in a typical multi-boutique investment manager creates elimination requirements that change month to month.
D365 Finance uses elimination rules that automatically:
- Identify intercompany balances that require elimination
- Generate elimination journal entries with proper debits and credits
- Post eliminations to a consolidation company (or apply them “softly” for reporting only)
- Provide full audit trail of what was eliminated and why
Consider the scenario: boutique A charges management fees to fund entities B, C, and D. This creates receivables in A and payables in B, C, D. Without elimination, the consolidated balance sheet would overstate both assets and liabilities.
In a spreadsheet model, someone needs to identify these balances, calculate the elimination amounts, enter the elimination entries manually, and verify the intercompany accounts net to zero after elimination. Every month.
In D365 Finance, elimination rules handle this automatically. Define the rule once, and it processes every period. Changes in intercompany activity are handled without updating elimination logic.
Minority Interest and Partial Ownership
Investment management structures often include minority interests, joint ventures, and partially owned subsidiaries. Consolidation needs to handle these scenarios correctly.
D365 Finance supports:
- Proportional consolidation where only a percentage of subsidiary results are included
- Minority interest calculation for subsidiaries where the parent owns less than 100%
- Equity method accounting for investments where consolidation isn’t appropriate.
These calculations happen within the system, with proper audit trail, rather than in manual journal entries or spreadsheet adjustments.
Real-Time Reporting vs. Batch Processing
One of the most significant differences between modern consolidation and legacy approaches is timing.
Traditional process: Source systems close, data is extracted (often the next day), consolidation runs in a batch process, results are reviewed, adjustments are made, consolidation re-runs, and eventually consolidated financials are available. This can take days.
D365 Finance process: Source ledgers and consolidated view update simultaneously. A transaction posted in any entity is immediately reflected in the consolidated position. Month-end consolidation still requires a formal process (period close, elimination posting, review and approval), but the heavy lifting happens continuously rather than in a big bang at period end.
This changes how finance teams work. Instead of waiting until after close to see consolidated results, controllers can monitor consolidated position throughout the month. Issues are identified earlier. Month-end becomes confirmation of what was already known rather than discovery of surprises.
The Audit Trail Advantage
External auditors test consolidation extensively. They want to trace from source transaction through consolidation to final financial statements. They want to understand elimination logic. They want to test controls around the consolidation process.
When consolidation happens in the ERP:
- Every consolidation entry has a document number, date, user, and timestamp
- Elimination entries are linked to the rules that generated them
- The path from source transaction to consolidated financials is system-documented
- Controls like segregation of duties apply to consolidation entries just like any other transaction.
Compare this to defending a spreadsheet model where consolidation logic lives in cell formulas, data comes from manual exports, and the audit trail is “we’ve always done it this way.”
Firms that move from spreadsheet consolidation to system-based consolidation consistently report reduced audit hours and fewer audit findings related to consolidation.
Implementation Considerations
Moving from spreadsheet consolidation to D365 Finance isn’t just a technology project. It requires thinking through:
- Consolidated Chart of Accounts: What structure makes sense for consolidated reporting? How do local charts map to the consolidated structure?
- Elimination Rules: Document current intercompany relationships and how they’re eliminated today. Design rules that handle these scenarios automatically.
- Reporting Requirements: What consolidated reports do you produce today? Who consumes them? What dimensions and breakdowns do they need?
- Historical Data: How much historical consolidated data needs to migrate? Do you need trend analysis against prior periods?
Timing: When in your fiscal year does implementation make sense? Most firms avoid go-live during their busiest close periods.
Making the Business Case
Consolidation modernization has clear ROI drivers:
- Labor savings: Reducing consolidation time from 12 days to 3 days frees senior finance staff for higher-value work.
- Error reduction: Eliminating spreadsheet errors reduces risk of restatement and improves decision-making based on financial data.
- Audit efficiency: Reduced audit hours translate directly to lower audit fees.
- Scalability: Future acquisitions integrate faster when consolidation is systematized.
- Talent retention: Finance professionals want to do analysis, not data manipulation. Better tools help retain good people.
Quantify these benefits for your specific situation. For a multi-boutique investment manager with 35+ entities, the business case typically shows payback within 18-24 months.
Conclusion
Spreadsheet consolidation served its purpose when firms were smaller and simpler. But as investment managers grow through organic expansion and acquisition, spreadsheet-based approaches become bottlenecks and risk points.
Modern consolidation in D365 Finance offers real-time consolidated visibility, automated eliminations, proper currency translation, and audit-ready documentation. It transforms consolidation from a monthly ordeal to a routine system function.
Ready to Modernize Your Finance Operations?
Don’t let legacy systems and manual spreadsheets hold back your firm’s growth. Whether you are managing 10 entities or 100, our Finance and Supply Chain Management service are designed to help investment managers unlock the full potential of Microsoft Dynamics 365.
How We Can Help:
- Custom Implementation: Transition from manual processes to an automated, multi-entity environment tailored to PE and investment boutique needs.
- Consolidation Strategy: Design a “single source of truth” for real-time reporting across all fund families and jurisdictions.
- Managed Services: Ongoing support to ensure your system evolves alongside changing regulatory requirements and new fund launches.
Next in this series: Blog 3: Multi-Currency Operations in Investment Management.
