Financial Consolidations for Microsoft Dynamics and Other ERPs

A financial consolidations tool is essential for any parent company that manages subsidiaries and wants unified reports to analyze overall company health.  This article will explore the key features and functions of today’s financial consolidation software.

There are plenty of Enterprise Resource Planning (ERP) system users that are managing multiple companies or subsidiaries under a parent company.  Making sense of data from different entities, divisions, and sometimes, with different currencies can be a logistical nightmare without a professional financial consolidation and reporting tool.  As data becomes a bigger and bigger part of corporate decision-making, CEOs and CFOs of corporations that own multiple companies are looking for a Business Intelligence (BI) solution that includes a robust and business user friendly consolidation module.  For professionals in this boat, it can be frustrating, but specifically for the typical ERP users, there are not too many choices that combine power and ease of use.

In terms of accounting, financial consolidation can be defined as the aggregation of financial data from disparate entities into consolidated financial statements.  These statements connect subsidiary data into a clear, straightforward summary with conversions for currency differences and eliminations for any inter-company transactions, among other adjustments that, without consolidation functionality, requires manual corroboration to make sense of the health of the parent company and its entities.  In addition to wanting to avoid manual spreadsheet consolidations, there are several reasons finance teams are looking for a tool to combine more than one company into a single financial statement.

Depending on the company’s specific needs, accountants are looking for a modern, automated consolidation solution for reasons that run the gamut.   Some might be wanting to get away from older reporting and consolidation tools, such as FRx or Enterprise Reporting, that are lacking features and functionalities that speak to modern business demands.  Others are looking to get away from the elderly tools that are too complex for the financial end user, like Hyperion or Cognos TM1, and require full-time IT personnel management.

One CFO I spoke to had companies in multiple countries, so there are more specific, diverse national requirements and currency conversions (a prime example would be entering International Financial Reporting Standards to Generally Accepted Accounting Principles adjustments for international accounting compliance – or IFRS to GAAP).  Additionally, there are tools that are more versatile than traditional consolidation options, that can go beyond the general ledger – and are positioned within a comprehensive BI suite that includes budgeting, forecasting, modeling, ad-hoc reporting, dashboards, and data warehousing.  This article will zoom in on the intricacies of the consolidation functionality for Microsoft Dynamics and other ERP solutions – and will discuss what you need to know when moving your BI analytics into the 21^st century.

As a company, you will want to think about whether you plan to run reports live from the ERP, or if you plan on integrating from a data warehouse.  I’ve written about this topic before, but the decision comes down to real-time data with potential sluggishness, depending on the number of users and size of query on the ERP server versus high performance data integration that is not up-to-the-minute because it requires replication of the data from the ERP to the warehouse.

Live reporting and consolidations within the ERP is usually preferable for companies with less complex entity combinations.  Live consolidations are better for those companies that require no (or very simple) currency conversion beyond what the ERP is already doing, that have fewer companies to consolidate, and that are not concerned about the ERP’s server performance when heavy reports are queried.  Furthermore, if you have no interest in bringing in data from other sources and no need to post elimination entries or other consolidation adjustments beyond what can be performed by the ERP, consolidating within the ERP is ideal.  On the other hand, data warehouse or online analytics processing (OLAP) cubes can also host the consolidation process, meeting other companies’ needs more adequately.

Replicating data to a data storage source like a data warehouse or an OLAP cube can be a simple manual push of information or a scheduled, routine refresh.  Data from the ERP will duplicate, usually through Microsoft SQL Server Integration Services (SSIS), to the storage space where consolidations can be configured at a higher performance because of its stability.  If you have moderate to advanced currency conversions, a higher number of companies to consolidate, and concerns about how slow the ERP will run based on the reports you are generating, data warehouse or OLAP is the preferable to perform consolidations.  In addition to integration options, you will want to evaluate which consolidation features are most important to meet your company’s needs.

Some other factors to consider when assessing what you need from a consolidation function include intercompany eliminations, currency conversion, and consolidation adjustments.  If you are consolidating company data from two subsidiaries that have bought and sold goods from each other, the respective expenses and revenue cancel each other out.  Other elimination areas are found on the balance sheet.  Eliminations solve these statement “falsehoods” that are driven by intercompany interactions, either within the ERP, within a data warehouse or OLAP cube through the BI tool you select to use.  Some BI tools offer fully customizable Excel input forms that can be used for manual elimination and adjustment entries.  Currency conversion is pretty straightforward – multinational companies combine into one report with one parent company reporting currency through this feature.  As for adjustments, whether it is IFRS to GAAP (or vice versa) adjustments to comply with domestic and international accounting rules, inventory updates, or temporary correction of incomplete already submitted subsidiary data, you have options to perform these features, depending on which way you are required to submit statutory reports.  And that’s not all – when consolidating multi-company data, there are still more functions that will enhance your processes.

Reconciliation, allocations, and modeling organizational changes are additional characteristics of the consolidation process.  If going the data storage integration option, once the data has been loaded to the source, many organizations prefer to have either the parent or the subsidiary staff reconcile the imported data to ensure everything is correctly input, which can be done within the ERP, the BI front-end tool, or in the BI data storage source itself.  Some companies have to associate all expenses or revenue to divisions, departments, and/or subsidiaries.  These allocations can be performed within the ERP system, as a part of loading the data to the BI tool, or some products will allow you to design allocation reports of varied complexity as part of the data warehouse or OLAP cube.  In terms of modeling organizational changes, if you need to see the impact of acquisitions, divestitures, or internal reorganization, some data storage sources will allow you to copy and change an unlimited number of corporate hierarchies or trees to help model the to-be roll-ups.  This modeling functionality allows you to decide what projected outcomes might be for changes within the subsidiary portfolio.  So much to consider in terms of features, but all are relevant to ensuring you get the most out of your investment.

Solver, Inc. is happy to answer questions and generally review BI360’s easy-to-use, Excel-powered consolidation tool with both real-time or data warehouse integrated analysis, comprehensive reporting and collaboration as a way to accelerate company performance management.