Retail Consolidations for Microsoft Dynamics NAV
This article lays out your options for modern financial consolidations solutions to take Microsoft Dynamics NAV processes to the next level for retail organizations.
There are plenty of Microsoft Dynamics NAV retail users who are managing a parent company’s financial portfolio with one or more subsidiaries. Combining transactional and operational information from a number of entities, at times with multiple currencies, can be a challenge without a modern financial consolidations tool. As the size and significance of data continues to grow, organizational decision-makers nearly require a software that can assist in consolidating data into a unified set of financial statements. If you’re relying on Dynamics NAV for your retail business, this article will aim to lay out your options for a business user friendly, powerful consolidations solution.
Defining it. “Financial consolidations” is a term that refers to aggregation of company financials from disparate subsidiaries into a singular set of financial reports. These statements consolidate data by reconciling various currencies, eliminating any inter-company transactions between entities, and making any other adjustments, whether manually or with one of today’s dynamics BI tool – to easily understand the comprehensive health of a parent company.
Why do we need a software solution? There is a myriad of reasons that executives are seeking an automated financial consolidations tool, but the top reasons seem to have to do with product age. Some executive teams are looking to move away from older software that is too complex, like Cognos/TM1 or Hyperion, which require IT department involvement or a technical product expert for management of the tool. Others are hoping to upgrade from older tools that are too simple for modern business needs, like Excel or FRx/Management Reporter.
Recently, I was speaking with a retail executive who utilizes Dynamics NAV and a third party consolidations software to oversee multiple subsidiaries for a multi-national parent company. His role involves following rules and regulations that are established by each country in addition to converting currencies, like International Financial Reporting Standards to Generally Accepted Accounting Principles (IFRS TO GAAP) adjustments, with the powerful flexibility of a consolidations solution. He produces reports that go beyond the General Ledger (GL) with software that is positioned within a fully integrated, complete suite of Business Intelligence (BI) tools, like ad-hoc reporting, financial planning, data visualizations, and data warehousing. This article will zoom in on financial consolidations as one of your Dynamics NAV month end tasks, with a focus on how you can find the right tool for your retail organization.
You can pull data from Dynamics NAV for real-time retail analyses and/or integrate your data from an (OLAP) cube or a data warehouse for your aggregations. If you’re consolidating and reconciling fewer subsidiaries, with easier or no currency conversions needed, or if your Dynamics NAV server isn’t sluggish because of bigger and sometimes simultaneous data queries, then consolidating directly from the NAV database will provide real-time roll-ups. If you don’t intend on including data from other sources, don’t require adjustments or eliminations beyond what NAV can functionally perform, Dynamics will probably suffice. However, most BI data stores inherently consolidate your financials, so your retail organization might do better by deploying a data store that pulls GL data from NAV.
Replication of company data from Dynamics NAV to a data warehouse or an OLAP cube is a simple manual push or a configurable automated routine process, if needed. Your information replicates to a data store usually via Microsoft SQL Server Integration Services (SSIS), and aggregations are done at a higher performance because of the intuitive and efficient organization that a data store provides when compared to a complex ERP database like NAV. If you regularly have more complicated currency conversions, are anxious about a sluggish NAV server, or have a large amount of data to consolidate, your users will likely need a data store of some kind. You’ll want to evaluate which consolidation software features and functionalities are necessary in order to achieve your specific goals.
Intercompany eliminations. Every once in a while (or more regularly) one of your entities might buy or sell goods from another, which should cancel out the line item in regard to any expense or revenue for either subsidiary. Intercompany eliminations do exactly that – eliminate transactions that shouldn’t show up on the balance sheet and/or P&L as false line items, but rather as intercompany reallocations, which can be done within Dynamics NAV or a BI data store. Some BI tools offer fully configurable forms that can be used to easily, manually input eliminations and adjustments.
Currency conversions and consolidation adjustments. Similar to intercompany eliminations, the terminology explains it: currency conversion refers to the process of translating data from international retail entities into one currency for a unified report. Consolidation adjustments correct subsidiary data, make adjustments to a consolidation, and/or perform the required IFRS to GAAP adjustments to be in compliance with domestic and global accounting regulations via today’s software functionality to submit statutory statements.
Reconciliation, allocations, and modeling organizational changes. If you’re going the data store route, plenty of retail organizations prefer to have either the subsidiary or parent company staff reconcile the input data to make sure that all the details are correct, which can be accomplished within Dynamics NAV, in a data store, or with a BI front-end solution. Perhaps your retail company allocates revenue sources or expenses to a department, division, and/or a specific entity. The allocations can be set up within NAV, during the replication process, or you can even sometimes produce allocation reports of various complexities within a data store. Finally, if you want to evaluate the effect of acquisitions, divestments, international reorganization(s), or new store openings, some data warehouses enable you to model potential roll-ups by cloning and altering any number of hierarchies or company trees. This function allows you to forecast how specific variables can impact the portfolio aggregation.
Financial consolidations can be tedious and complex, so while the list of features and functionalities might seem lengthy to wrap your head around, it’s vital that you do your research, so you can invest the best way for your retail organization’s particular analysis goals. 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 for retail companies.
