Financial Consolidations for Microsoft Dynamics NAV

Financial consolidation tools are a must in today’s complex climate for parent companies and their subsidiaries.  This article will zoom in on the options Microsoft Dynamics NAV users have to perform powerful, modern financial consolidations.

There are lots of Microsoft Dynamics NAV users and administrators that are overseeing more than one company as subsidiaries for a parent company.  Aggregating company data from multiple businesses, sometimes with a variety of currencies, can be a logistically messy task without a powerful financial reporting and consolidations tool.  Data continues to grow in importance, fine-tuning corporate decision-making.  Therefore, executives are seeking a business user friendly solution to the routine problem of consolidating information into a unified set of financial reports.  If you are a Dynamics NAV user who has to manage multiple companies’ data, this article is for you, and it is an important one because there aren’t a lot of options for NAV that prove to be both easy to use and powerful.

For those of you who are not 100% familiar with the term, financial consolidation refers to the aggregation of financial information from different subsidiaries into a unified set of financial statements.  These reports bring together the entities’ data that make up the parent company in a direct, standard statement that offer currency conversions when multiple countries are involved and eliminations for transactions that happen between subsidiaries, as well as other adjustments that have to be corroborated manually without a modern financial consolidation tool in order to assess the overall health of the parent company.  There are several additional reasons that finance teams are seeking a solution that simplifies the consolidation process.
 
Your accounting department might be looking for an automated, modern consolidation tool for a number of reasons.   Some are looking to avoid the mature software packages that are too difficult to operate for business users, like Hyperion or Cognos, putting the management of the product in the hands of the IT department.  Others are ready to retire the elder consolidation tools that are too simple to meet today’s business needs, like FRx or Enterprise Reporting.
I spoke to a CFO who used Dynamics NAV with multiple companies around the globe.  In her role, this CFO dealt with country-specific requirements and currency conversions (including International Financial Reporting Standards to Generally Accepted Accounting Principles adjustments, also known as IFRS to GAAP).  There are also more flexible consolidation tools that drill down to the sub-ledger level and are part of a full Business Intelligence (BI) suite, with fully integrated ad-hoc reporting, budgeting, forecasting, modeling, dashboards, and data warehousing modules.  I’m going to do a little drill down on the details of financial consolidations for Dynamics NAV, including what it is that you should know when shopping for the right solution for your company.
First of all, I’ve covered the topic of reporting live from Dynamics NAV versus integrating from a data storage source before, but your choice is between potentially slower real-time data, depending on how many users and query size on the NAV server versus a higher performance data source integration, which entails a data replication to a data warehouse or online analytical processing (OLAP) cube.
Companies with fewer companies to unify and reconcile, with simple or no currency conversions necessary, and/or are not worried about slowing down NAV’s server performance with bigger report queries are going to benefit more from live consolidations.  Additionally, if you don’t plan to grab data from other sources, don’t need to post elimination entries or other adjustments beyond what NAV has to offer, you might as well use Microsoft Dynamics.  Alternatively, many OLAP cubes and data warehouses do host consolidations, so companies with different needs could benefit more from using a data storage source that loads GL data from NAV.
You can replicate data to an OLAP cube or a fully built data warehouse with a manual push or regular, scheduled refreshes.  Information from Dynamics NAV will duplicate, typically by way of Microsoft SQL Server Integration Services (SSIS), to the data storage unit, and financial consolidations can be processed at higher performance because of the stability of an OLAP cube or data warehouse.  If your company relies on more advanced currency conversions, has concerns about how slow NAV will perform with the type and number of queries you need to run, and/or has a substantial amount of company data to consolidate, a data warehouse or OLAP cube will be your best route.  Beyond data integrations, you should figure out which consolidation functionalities are a priority for your company.
Another element to think about when deciding what you need in a financial consolidation solution should be intercompany eliminations.  Sometimes, one subsidiary will buy from or sell goods to another subsidiary, cancelling out the line items of respective expenses and revenue.  Intercompany eliminations resolve these “falsehoods” on the P&L and balance sheet that are a result of inter-company resource reallocation, which can happen within Dynamics NAV or a data storage source through whichever tool you select.  There are even some BI modules that have fully configurable Excel input forms that you can use to manually enter eliminations and adjustments in a user-friendly template.
A couple other features to consider include currency conversion and consolidation adjustments. Currency conversion is what it sounds like – international corporations can consolidate their transactional data into one parent company report with a single currency through this functionality.  When it comes to consolidation adjustments, whether it is inventory updates, temporary correction of incomplete subsidiary information, or IFRS to GAAP adjustments required for compliance with domestic and global accounting rules, there are solutions empowered to help you perform these tasks, depending how you submit statutory statements.  Beyond currency conversion and adjustments, there’s more to consider regarding your company’s specific needs.
Reconciliation, allocations, and modeling organizational changes are more important facets of financial consolidations.  Should you decide to go the data storage source integration route, after data has been replicated to the cube or warehouse, plenty of companies prefer to have either the subsidiary or parent company staff reconcile the information to make sure everything is correct, which can be done with Dynamics NAV, the BI front-end tool, or in the data storage source.  Some corporations have to allocate certain expenses or revenue sources to a division, a department, and/or a specific subsidiary.  You can perform these allocations within NAV, while loading the data to a BI tool, or in some cases, you can craft allocation reports of different complexities within a cube or warehouse.  When it comes to seeing the effect of acquisitions, internal reorganization, or divestitures, some data sources allow you to model to-be roll-ups by copying and changing an infinite number of company trees or hierarchies.  Using this functionality, you can project how certain changes could impact the subsidiary portfolio.
Financial consolidations can be complex, so the amount of things you have to consider in terms of features and functionality makes sense if you want to maximize your BI tool.  Solver, Inc. is happy to answer questions and generally review BI360’s easy-to-use, Excel-powered consolidation tool for Microsoft Dynamics NAV with both real-time or data warehouse integrated analysis, comprehensive reporting and collaboration as a way to accelerate company performance management.