Consolidation for Dummies

The ease of communication and speed of logistic flows has smoothened the distance barriers for the modern business. Businesses control offices and divisions that can be scattered across the regions, countries and, in so many cases, across the globe. Growing and operating in the new markets involves necessity to control these divisions, along with other acquired companies and investments into other businesses. The divisions and companies, that are part of parent company, prepare their own financial statements to reflect the business activity results. In order to evaluate the success of the company in whole or for tax reporting purposes, the parent companies collect the summary of the business activity results throughout all divisions and child companies. The summary of financial information is referred to as the consolidated financial statements. In this article, we will review the fundamentals of financial statements consolidation process in Microsoft Dynamics NAV.

Why do businesses consolidate? 

• Acquiring or merging with competing companies or supplier/distributors in order to reach the synergy of cooperation effect.
• Consolidating to decrease back office operations costs. For instance two taxi service providers can merge into one company and save on the accounting, operators departments costs.
• Consolidating tax (feasible in US, New Zealand, not common in EU). Tax consolidation allows offsetting the profits of one company with the losses of the other company if they belong to the same parent company (corporation). In this case, the resulting tax amount will be decrease.

Financial consolidations in most cases will be done between the parent company and its:

• Subsidiaries
• Affiliates
• Joint ventures
• Divisions (discrete part of the company that can operate as either same or separate legal entity and name).

There are regulations for cases in which consolidations are mandatory and in which not. We will not concentrate on the legal and accounting aspects, but rather try to review the Microsoft Dynamics NAV role in this process.

Consolidation in Microsoft Dynamics NAV

In Microsoft Dynamics NAV, all kinds of legal entities that need to be consolidated to a parent company are called business units (BU).

Let us look at several cases in which business units appear. We will then discuss these examples from the perspective of Microsoft Dynamics NAV.

Business Case 1:

Let us assume that CRONUS International Inc. specializes in bicycle production and operates in USA. As part of the downward integration strategy, the company bought 45% shares of the supplier of the bicycle chains - CHAIN Co. for $500,000 in order to secure supply costs and quality. After the purchase, both companies, CRONUS International Inc. and CHAIN Co., continued to exist as separate entities and to prepare separate financial reports. However, CRONUS International Inc. needs to reflect the financial state of its investment into 45% shares of its affiliate on its balance sheet.

Business Case 2:

CRONUS International Inc. that operates in USA, but wants to enter the market of Belgium. CRONUS International Inc. establishes a subsidiary company CRONUS Belgium Ltd. of which it owns 100%. CRONUS Belgium Ltd. is a separate entity and prepares its own financial reports. However, CRONUS International Inc. needs to reflect the financial state of its investment into 100% of its subsidiary.  

Each of the three companies – the parent company and its two business units – perform an independent activity, but at the end of the year the financial results are consolidated into CRONUS International Inc.

Let us look at the key profile details of the parent company and its business units that will have an impact on the consolidation process in Microsoft Dynamics NAV:

CRONUS International Inc.Currency – USD

CRONUS Belgium Ltd.

• Currency - EUR
• Uses different general ledger account numbers
• Owned 100%

CHAIN Co.

• Currency – USD
• Uses a different set of dimension values
• Owned 45%

NOTE: For simplification purposes, the W1 localization will be used in all company examples.

In Microsoft Dynamics NAV, the consolidation process is quick and performed by one of the consolidation batch jobs. Depending on whether the business unit company is located within the same database or externally, the accountant would choose Import Consolidation from DB or Import Consolidation from File correspondingly. In our example, both companies are located within the same database.

In order to consolidate the companies, we create a list of business units that will be included into the consolidation process. The Import Consolidation from DB batch job will consolidate all business units altogether, so if you do not want a particular business unit to be consolidated or consolidated later, just leave the Consolidate check box cleared on the business unit card of that particular company or apply a specific filter in the batch job request page.

Consolidating CHAIN Co. 

Let us start with an easier case - the CHAIN Co. company.

Above are the dimension values used in the consolidated company for one of its global dimensions (Department). The management wants the business unit’s dimension values to be transferred as well. However, three of the dimensions used in CHIAN Co. differ from the dimensions used in the parent company. In order for these dimensions to be transferred during consolidation, the Consolidation Code must be specified for dimension values codes that differ in the business unit company.

Once the dimension values mapping has been done, we can perform the consolidation.

Before we run the Import Consolidation from DB batch job, let us look at the general ledger entries to GL account number 2910 in CHAIN Co. and the related dimension values to make sure they will be transferred with changed dimension value codes according to the mapping we’ve set up. 

Let us now run the Import Consolidation from DB batch job. We assume that in the course of consolidation the consolidation company wants only information, related to the Department dimension to be transferred.

The Import Consolidation from DB batch job is always run from the consolidated company. 

Let us look at the general ledger entries that are created after the Import Consolidation from DB batch job has been run in the consolidation company.

Since CRONUS International Inc. owns only 45% of CHAIN Co., we intended only 45% of transaction amounts to be consolidated to the general ledger of Cronus International Inc. The quantity of entries transferred is less in terms of lines, since in the process of transferring the entries, the entries were grouped by dimension set ID. Notice that the dimension values were changed according to the mapping we performed earlier.

Consolidating CRONUS Belgium Ltd.

Consolidating a company that operates in different currency is a little bit more complicated.

It is no secret that the currency exchange rates fluctuate every day and can differ significantly in the course of year. So, how do we consolidate transactions in different currencies? 

In fact, accountants are not free to decide which currency rate to be taken. Here is how IAS (International Accounting Standards) regulates currency aspect.

IAS 21— The Effects of Changes in Foreign Exchange Rates requires that all revenue and expense transactions are translated at the exchange rate on the date of the transaction. The transactions related to assets and liabilities accounts are translated at the exchange rate on the closing date (last exchange rate in the consolidation period). The equity part of the balance sheet is translated at the historical exchange rate of the transaction. Additionally, the foreign currency monetary amounts are reported at the closing rate and the non-monetary items are carried at the exchange rate at the moment of the transaction.

As a result of this requirement, accountants need to perform consolidation adjustments to the consolidated balance sheet. The consolidation functionality in Microsoft Dynamics NAV provides the possibility to specify a currency translation method that will be used during the consolidation process on each GL card and calculate currency exchange rate gains and losses resulting from consolidating different parts of financial statements at different exchange rates.

Before running the batch job, we must first of all go to the business unit’s chart of accounts and specify the correct consolidation translation method for each GL account with respect to its type.

Additionally, CRONUS Belgium Ltd. uses GL accounts numbers that differ from the GL account numbers in the chart of accounts of the CRONUS International Ltd. With respect to this issue, we need to specify the consolidation debit and consolidation credit account numbers to map the business unit account to the consolidation company GL account.

We fill in both the Consol. Debit Acc. (consolidation debit account) and Consol. Credit Acc. (consolidation credit account) fields to let Microsoft Dynamics NAV know where we want the transactions mapped to in cases of negative or positive amount. Generally, it should be the same account. Since account 2910.1 Cash is a Balance Sheet account, we choose the Closing Rate consolidation translation method.

Let’s see the transactions related to this account that will be consolidated.

Next, let us take a quick glance at one of the Income Statement GL accounts in order to check how transactions for this GL account will be transferred after the consolidation is run later. For this type of accounts and generally all Income Statement GL accounts, we will need to choose the Historical Rate consolidation translation method. GL account 8430 Travel represents expenses related to business trips travelling costs in Cronus Belgium Ltd.

Now, let us check the general ledger transactions that were posted to this account in the business unit company.

Let us assume that we have specified the consolidation translation method for each GL account and can now move on to checking the CRONUS Belgium ltd. business unit card before we run the batch job.

Since CRONUS Belgium Ltd. operates in the currency other than the consolidated company, we need to specify it in the Currency Code field on its business unit card. We will use Local (consolidation company) currency exchange rate table.

Note: Make sure that you have correct currency exchange rates throughout the consolidation period.

Additionally, since different types of the GL accounts will be consolidated using different consolidation translation methods, the exchange rate gains and losses will appear. These types of consolidation process-related gains and losses are usually consolidated to the income account or other comprehensive income account in the consolidation company.

The batch job will then calculate the gains and losses based on the difference between exchange rate at the moment of transaction and the closing exchange rate. Gains and losses recognition is needed to balance the income statement transactions that are consolidated at historical date with the balance sheet transactions that are consolidated at the closing exchange rate.

The batch job will look for exchange rates either in the currency exchange rates or, alternatively, in the currency rate setup that can be accessed from the business unit list.

Now we run the Import Consolidation from DB batch job. 

We expect GL account 2910.1 Cash to be mapped to GL account 2910 and the transactions translated at the closing rate currency rate. Also, we expect GL account 8430 Travel transactions to be translated at the exchange rates that existed on the days of the transactions. Let us see whether the consolidation results meet our expectations.

The general ledger in the consolidated company now contains GL entries on account 2910 from GL account 2910.1 from the business unit, as we specified in the Consolidation Code field on the GL account card in the business unit. Additionally, the entries were grouped by dimension set ID and translated at the currency rate on 31 December 2018, which is the closing date.

Let us check how income statement related entries were consolidated.

The consolidated general ledger now contains business unit transaction amounts related to account 8430 that were translated at the currency exchange rates at the days of transactions, exactly as we indicated in the consolidation translation method.

Let us check whether there were any gains and losses resulting from the consolidation process. We specified consolidated company account 9410 to accumulate consolidation gains and losses on the business unit card of CRONUS Belgium Ltd.

Indeed, the consolidation losses and gains were posted to this account. Depending on the accounting standards under which the company is reporting, this account may be different. For now, it is important for us to understand that the Microsoft Dynamics NAV consolidation functionality minimizes the need in manual calculations and additional posting after the Import Consolidation from DB batch job has been run.

In case transferred transaction amounts are not correct or incomplete, you can perform the corresponding corrections and run batch job again. Each run of the Import Consolidation from DB batch job will clear the previous consolidated transaction amounts from the consolidated company general ledger.

Let us sum up the consolidation process steps in Microsoft Dynamic NAV we have covered:

• Setting up consolidation mapping:
  • Mapping general ledgers to a single chart of accounts.
  • Mapping dimensions and dimension values to the consolidated company.
  • Specifying a consolidation translation method on each GL account card in case of BU functional currency differs from the functional currency of the consolidated company.
  • Specifying a consolidation currency and gain and loss accounts on the business unit card.
• Merging financial transactions by running the Import Consolidation from DB batch job.
• Making sure the consolidation results meet our expectations.