Financial consolidation is the combining of data from different business entities in the consolidated financial statements. Data from these business entities may differ even due to different structures of the chart of accounts, accounting policies and currencies used. Business entities the data of which is to be consolidated (consolidated entities) enter into transactions which should be eliminated from their consolidated financial statement (intercompany eliminations). This shows how complex financial statements consolidation is if the data comes from disparate systems. One of the most accurate definitions of financial statements consolidation comes from Accounting Research Bulletin (ARB) 51. It explains the process’s objective which is “to present, primarily for the benefit of the shareholders and creditors of the parent company, the results of operations, and the financial position of a parent company and its subsidiaries essentially as if the group were a single company with one or more branches or divisions”.
Financial Statements Consolidation in Microsoft Dynamics NAV
Approaches to the preparation of consolidated financial statements
Global companies apply different methods for the preparation of consolidated financial statements. The following diagram shows four approaches. Spreadsheets are the most common and elementary method. The most advanced approach involves Global Template/core-based implementation of an ERP system in all company subsidiaries (consolidated entities).
IT. integro offers Microsoft Dynamics NAV implementation based on its Global Template which as the top-level methodology ensures high-quality implementation and long-term benefits for its international customers. As the process requires comprehensive investment, it is worth thorough planning. With the Global Template, each implementation project is delivered in a structured way, allowing you to achieve the best consolidation results across your organisation and stimulate benefits in all operational areas.
Level 1 – Manual processing with spreadsheets
MS Excel worksheets prepared by the head office are provided to consolidation entities as a form into which data is entered and sent back periodically for financial statements consolidation. This solution is cheap to implement, but it results in difficulties in controlling the consolidation process and data accuracy. This solution should be considered temporary. For example, it can be applied in a newly purchased company where it is impossible to implement consolidation software fast, not to mention, a standardised ERP system. Practically, many multi-site companies use such temporary solutions which cause quality problems and hinder the timely preparation of consolidated financial statements.
Level 2 – G/L as a consolidation platform
This level involves creating the so called “consolidation companies” within the existing ERP solution and setting up its general ledger as a consolidation platform. The launch of the solution requires small modifications within the standard functionality of multiple ERP systems used locally including Microsoft Dynamics NAV. Transactional systems, such as ERP systems typically include the functionality, which, to some extent, supports and automates the process of financial statements consolidation. In this field Microsoft Dynamics NAV is quite competitive. Regardless of this fact, ERP solutions as transactional systems are usually not equipped to support sophisticated or complex processes related to financial consolidation. That is why G/L as a consolidation platform is the solution worth considering for a non-complex consolidation process. In the case of excessive demands, consolidation software has to be designed and developed for this purpose.
Level 3 – Consolidation Software
Consolidation Software is the most widely used solution in companies where complex organisational structure translates into the complicated preparation of consolidated financial statements. If Microsoft Dynamics NAV is used as a main transactional system within the group, the need to use a dedicated Consolidation Software is bound to arise especially if the volume of consolidated entities and intercompany transactions is large.
Level 4 – ERP with a Global Template
ERP system implementation or upgrade based on the Global Template standardizes operations in each subsidiary, including accounting functions. This approach ensures that the preparation of consolidated financial statements becomes easier. Obviously, in the case of Level 4, a consolidation tool has to be used to process financial data for financial statements consolidation purposes. Both the standard functionality of Microsoft Dynamics NAV (Level 2) or Consolidated Software (Level 3) can be used as consolidation tools. The added value of Level 4 (ERP with the Global Template) consists in:
- standardization of financial data transferred from subsidiaries (consolidated entities), which simplifies the financial consolidation process,
- the high quality of financial data which improves the accuracy of consolidated financial statements and decisions made based on this data;
- enhanced drill-down capabilities and easier control of local companies – all local companies work based on the standardized data and financial setup, which facilitates internal audits.
IT.integro recommends Level 4, which involves standardization of data, setup and functionality of the Microsoft Dynamics NAV system used in subsidiaries (consolidated entities).
The standardization of Microsoft Dynamics NAV in subsidiaries which is implemented based on the Global Template is one of multiple project benefits. These benefits include: facilitated management of the entire global organization, faster change implementation and improved flow of information. To learn more about the benefits, please read: Benefits of global Microsoft Dynamics NAV standardization.
Costs and benefits
The levels described briefly above differ substantially both in terms of implementation costs, as well as in terms of benefits. It should be taken into account that in the case of group reporting, the preparation of consolidated financial statements and month-end close efficiency are critical to the future of the whole organization. The company’s capability to streamline the month-end close process and prepare reliable consolidated financial statements is the prerequisite to ensure:
- The accuracy of consolidated financial statements which affects the assessment of the current company condition;
- The efficiency of consolidated statements preparation which translates into an amount of time available for taking responsive actions by managers;
- The process of financial statements consolidation which affects the assessment of business market value in investor relations; it also corresponds to the quality of management;
- The degree of automating the consolidation process which determines work priorities for financial departments. Automation allows departments to concentrate on the analysis of financial data, i.e. the area that generates the highest added value. A lack of automation makes these departments to focus on tasks related to the preparation of summaries and control.
Research surveys carried out among financial executives in international companies confirms that the quality of IT tools is the greatest difficulty in financial statements consolidation. Both when it comes to consolidation efficiency and data reliability. In this regard, IT tools are considered a bigger challenge than staff shortages in financial departments. A study by Ernst & Young deals with a relevant example: “Closing Excellence-An analysis of the financial reporting process of 60 large and medium-sized Danish companies”
Level 2, 3 and 4 solutions have been described below. These solutions should be interesting for companies using Microsoft Dynamics NAV as a primary system within the group.
To what extent does the standard Microsoft Dynamics NAV financial consolidation functionality meet the requirements of international companies (Level 2)?
Consolidation in Microsoft Dynamics NAV means combining general ledger entries of two or more separate companies (subsidiaries) into a consolidated company. Within the system, each individual company involved in the consolidation process is called a business unit. A combined company is called a consolidated company. This company is usually set up only for consolidation purposes, and does not process any typical business transactions. The following are the most important capabilities of the consolidation tool within Microsoft Dynamics NAV:
Data exchange
Submitting consolidated financial statements within the same database and between different Microsoft Dynamics NAV databases.
Shares in subsidiaries (consolidated entities)
Possibility to define share percentages for the consolidated entities from which financial data is to be retrieved. The share percentage is used to calculate values for consolidated financial statements;
Foreign Currency Translation
Currency conversion based on the FAS 52 standard. The Foreign Currency Translation functionality enables currency conversion. Currency conversion can be run only once at the time of data transfer. To a limited extent, it is possible to select an exchange rate to be applied for selected accounts. The user can select either an average or closing exchange rate. The average rate is used to calculate income statement, whereas the closing rate is applied when calculating the balance sheet. The consolidated income statement is generated for a specific period, and for this reasons the average exchange rate is used. To the contrary, the preparation of the consolidated balance sheet is performed as of the day selected, which necessitates the use of a daily exchange rate.
Manual consolidation adjustment
The standard Microsoft Dynamics NAV functionality enables the financial statements consolidation from one or more Microsoft Dynamics NAV databases or other files — with the ability to import and export financial information. This is represented as option 1 in the following figure. In the case of more complex requirements, it is possible to create additional consolidation companies within the system. In this case each of the so called sub-companies is created to enable the manual entering of postings (the so called consolidation adjustments). As a result, the data cleaned in sub-companies is ready for consolidation within the consolidation company. This way, it is not necessary to require local companies to post adjustments.
Manual intercompany eliminations
Microsoft Dynamics NAV does not incorporate any extended functionality for intercompany eliminations. The process can be however managed by means of consolidation accounts mapping. With mapping, liabilities and receivables of a company are recorded at consolidation accounts with account numbers defined in consolidation companies, which in theory should lead to their automatic elimination. In practice, the entries on such an account require validation and write-offs. In addition, the more consolidated entities are managed, the more consolidation accounts are needed. Therefore, it can be assumed that, for intercompany eliminations, the Microsoft Dynamics NAV consolidation functionality is sufficient if a limited number of companies and a small number of intercompany transactions are managed.
Which requirements exceed the capabilities of the Microsoft Dynamics NAV consolidation tool?
The consolidation tool in Microsoft Dynamics NAV proves effective both for a medium and complex financial consolidation process. However, it is should be taken into account that this tool cannot meet the requirements of a more structured financial consolidation process. For the scenarios described below, a more advanced tool is required. These scenarios include:
- The necessity to manage situations when a company the data of which is consolidated and the completed consolidated financial statement of which was received has posted additional operations for the period already reported.
- Eliminating accounting errors e.g. adding expense reserves – if, contrary to the subsidiary’s principles, headquarters reserve some funds for expenses registered in the trial balance, such a reserve must be posted additionally. As a result, in the next period when a respective expense is incurred and invoiced, it is necessary to reverse the elimination of the posting error. What is more, both eliminating and reversing should be processed in the currency of the accounts, which is often different from the consolidation currency.
- The necessity to manage a large volume of posting corrective adjustments to the financial data (the so-called consolidation adjustments) when local accounting standards differ from those adopted within the group. Such adjustments cause problems when standardising accounting settings;
- Automated intercompany eliminations, consolidation adjustments, elimination of dividends or other eliminations the results of which should be presented in a standardized and custom form.
What optional external tools can a large multi-national and multi-site company use to manage financial statements consolidation (Level 3)?
It all depends on consolidation requirements. When it comes to ERP systems for middle sized companies, the consolidation functionality within Microsoft Dynamics NAV is quite extensive. In the case of more advanced requirements, external consolidation tools are typically used.
In such a case, two options are available. The first involves using Business Intelligence such as SQL Server Business Intelligence or BI360; the other – using tools designed for consolidation purposes with Hyperion Financial Management and IBM Cognos Controller as leading solutions.
Business Intelligence tools
IT.integro opts for using SQL Server Business Intelligence. This solution ensures that the consolidation process is completed seamlessly, especially within intercompany elimination. To include intercompany eliminations within group reporting, SQL Server Business Intelligence is used to design OLAP cubes.
With OLAP cubes created, all setup such as additional general ledger account types can be defined within Microsoft Dynamics NAV. After all relevant setting are defined, transactions are filtered within the BI tool. This means that the tool checks combinations of NAV settings for each transaction, e.g. based on an account number, contractor number or G/L account groups. This tool ensures flexibility, because if any changes are need no modification is required within the BI code. All changes can be entered by a Microsoft Dynamics NAV user at the system setup level.
BI360 – integrated with Microsoft Dynamics NAV
Using a BI tool with a consolidation functionality is the other option. BI360 is one of such solutions. It supports integration with Microsoft Dynamics NAV and other Microsoft Dynamics family products. The functionality available within BI360 is much broader than the one available within a standard NAV version. It also offers much more than what is available within the framework of consolidation in Jet Reports. The list below includes the most important consolidation functions:
- Mapping different Chart of Accounts,
- Financial consolidation process (Workflow)
- Date of loading,
- Reconciliation,
- Currency conversions,
- IFRS to GAAP adjustments,
- Other consolidation adjustments,
- Eliminations of intercompany transactions,
- Minority calculations,
- Allocations,
- Financial statements consolidation,
- Consolidate sub-ledger or statistical data,
- Sarbanes Oxley (SOX) compliance.
In turn, the elimination of accounting errors (consolidation adjustment) is managed within this consolidation tool based on the following rules: Depending on the company and its requirements, a corporate controller may need to post consolidation-related adjustment entries for a variety of reasons, including currency-related adjustments, the temporary correction of erroneous data from a consolidation entity, etc. The posting process can be managed by using BI360 Planning to design a user-friendly input form for entering and storing transactions in the BI360 Data Warehouse. By default, all entered transactions will be tracked based on user id and time/date stamps. It is also possible to enter comments to explain the reason for the adjustment entries.
Let us note that IT.integro has not implemented BI360 for any of its customers. Therefore, the section above is meant only for information purposes. As we are not able to evaluate the solution’s quality and ensure its effectiveness, it is not a recommendation of IT.integro.
Jet Reports or Targit – for consolidation purposes?
With their extensive consolidation capabilities, Jet Reports and Targit are the most popular and proven BI solutions for Microsoft Dynamics NAV. However, they only support the merge of financial data and currency conversion. With these BI tools, it is possible to create charts and visualize shared data. These tools, however, do not take into account issues such as for example intercompany transactions. Therefore, although data consolidated with BI tools ensures insight into the financial condition within the entire group, it can be encumbered with errors. Therefore, according to accounting rules (statutory consolidation), BI tools are not sufficient for financial statements consolidation. For this purpose, a consolidation tools should match the three models described in the previous sections.
Dedicated consolidation tools
Level 3 is represented by dedicated consolidation tools, the most comprehensive of which is HFM Software (Hyperion Financial Management – and IBM Cognos Controller. These consolidation tools are easy to integrate with Microsoft Dynamics NAV as they can read and process text files displayed with Microsoft Dynamics NAV.
Consolidate data in Microsoft Dynamics NAV or seek an external tool? (Level 2 or 3)?
In practice, it turns out that customers relatively rarely use the consolidation functionality within Microsoft Dynamics NAV. The other two scenarios are much more common. In the first scenario, financial statements consolidation is carried out manually. In the other one, before the global Microsoft Dynamics NAV solution is deployed, a company can implement a specialized consolidation tool to meet legal requirements for financial statements consolidation. Therefore, appropriate software needs to be purchased as a tool to manage the consolidation process. When initiating a global roll-out of standardized Microsoft Dynamics NAV, it often turns out that our customers have tools which support financial statements consolidation, for better or worse.
It should be also assumed that the consolidation functionality in Microsoft Dynamics NAV can be sufficient for a company with an averagely developed international structure. If the organization has a very complex structure and financial processes, consolidation in Microsoft Dynamics NAV is not be sufficient. In such a situation, the system should be used as a typical transactional tool which provides data input. Obviously, with standardized posting setup, the quality of such data and data consolidation process will improve substantially.
This resembles the differentiation within typical reporting (i.e. reporting for non-consolidation purposes). ERP systems are transactional systems. They prove very useful for data registration tasks. However, in terms of reporting, their usability has to be evaluated based on the customer’s needs. If the customer’s needs come down to generating uniform periodic reports, Microsoft Dynamics NAV tools are sufficient for this purpose. However, if the customer’s requirements for analytics are more advanced, they can be met by implementing an add-on tool that offers more sophisticated capabilities. Likewise, advanced consolidation requires dedicated tools. If consolidation-related processes are simple, the standard Microsoft Dynamics NAV functionality can be used. However, if requirements are high in this scope, an external consolidation tool designed for this purpose should be used.
The impact of standardized setup and data (Level 4)
Despite the fact that specialized consolidation tools such as HFM Software (Hyperion Financial Management) or Cognos Controller offer a wide range of consolidation capabilities, the quality of input data is of critical importance. Data standardization is easier before importing it into a consolidation tool. Local companies know which transactions are posted, and it is easier for them to adjust them if needed. However, if a consolidation adjustment is to be managed by a consolidation accountant, who might not know the context for transactions posted locally, the process becomes more complicated and as a result more expensive.
Mapping local charts of accounts into a chart of accounts used for consolidation purposes is another issue to be discussed. Aligning definitions for data items is very important at this point. Firstly, in the case of some local companies data registers may be not detailed enough for the preparation of consolidated financial statements. Secondly, it happens that two identical accounts are used by local companies in a slightly different way, due to different structures of their charts of the accounts, or different accounting setup within their local ERP systems.
Finally, going back to the chart with the four organisational levels presented above, it should be highlighted that data standardisation and setup within the whole group at the ERP level, i.e. the data source level requires the use of any of the three methods (levels 1- 3) as a consolidation tool. Let us mention once again that data standardisation and data source setup have a fundamental impact on the quality and efficiency of the consolidation process regardless of the tool that is used.
Financial statements consolidation, management reports and analytics in ERP system
