Using Microsoft Dynamics NAV to Automate Multi-Currency Consolidations (There’s an App for That)

As a Certified Management Accountant (CMA) now re-branded as a Chartered Professional Accountant (CPA) under the Canadian accounting bodies unification agreement – I have had to work my way through some pretty complex theory and problem solving course material.

One of the most complex topics I’ve encountered is multi-currency consolidations.

As summarized in the KPMG document “Insights into IFRS: An Overview”:

“The financial statements of foreign operations are translated for the purpose of consolidation as follows:  assets and liabilities are translated at the closing rate; income and expense are translated at actual rates or appropriate averages; and equity components (excluding the current year movements, which are translated at actual rates) are translated at historical rates.”

Ouch – for the non-accountant – the above quote is likely akin to reading a foreign language that you have never encountered before.

For the accountant – the good news is that once you have understood the requirements, you can utilize an application to perform the calculations.

How Microsoft Dynamics NAV Drives Multi-Currency Consolidation Functionality

The consolidation functionality in Microsoft Dynamics NAV (Navision) is used to export transactions from subsidiary companies and import them into the consolidated company.

Typically this process is run at month end in Microsoft Dynamics and enables the production of consolidated financial statements inside of Microsoft Dynamics NAV.

The consolidation calculations take into account:   Ÿ

• Functional currency of subsidiary and parent companies Ÿ
• Translation method assigned to the GL account Ÿ
• Exchange rates for consolidation Ÿ
• Mapping of GL accounts and Dimensions (which may be different between companies) Ÿ
• Percentage of ownership the parent has in the subsidiary company Ÿ
• Fiscal year end dates for subsidiary and parent (which may be different) Ÿ
• Whether the companies exist within one or more Microsoft Dynamics NAV (Navision) databases

The above properties are defined on the Business Unit Cards, within the Microsoft Dynamics Chart of Accounts, and within the Dimensions.

The Business Unit Card enables the set-up of subsidiary company properties such as functional currency, exchange rate source, consolidation percentage, date range, and data source.

The G/L Account Card is used to map the subsidiary GL accounts to the parent GL accounts and define the consolidation translation method.

The following are options in the Consol. Translation Method field:   Ÿ

• Average Rate (Manual) – the average rate for the period to be consolidated. Calculate the average either as an arithmetic average or as a best estimate and enter it for each business unit.   Ÿ
• Closing Rate – the rate that prevails in the foreign exchange market at the date for which the balance sheet or income statement is being prepared. Enter the rate for each business unit.   Ÿ
• Historical Rate – the rate of exchange for the foreign currency that prevailed when the transaction occurred.   Ÿ
• Composite Rate – the current period amounts are translated at the average rate and added to the previously recorded balance in the consolidated company. This method is typically used for retained earnings accounts because they include amounts from different periods and are therefore a composite of amounts translated with different exchange rates.

The Dimension Values provide for mapping segments from the subsidiary to the parent company.

For example, while the subsidiary company may refer to the accounting department as “ACCTING” the parent may refer to it as “FINANCE”.

In each subsidiary company, for any Dimension that you intend to consolidate you will need to map the subsidiary Dimension Codes to the consolidation Dimension Codes.

The export and import processes are managed from the Business Unit Card.

The resulting consolidation:  

• Can be exported and imported by date range
• Can be exported and imported again if revisions have been made
• Can be adjusted as necessary with elimination general journals
• Provide a level of drill-down inquiry

A Trial Balance report displaying a separate column for each subsidiary and a total for the consolidated company can be printed from the Business Unit Card (the elimination entries are also displayed on the report).

Understanding the rules for multi-currency is critical when defining the setup.

However, once the setup has been completed and validated, the month end multi-currency consolidation process becomes simple and fast with an ERP like Microsoft Dynamics NAV.

The post Using Microsoft Dynamics NAV to Automate Multi-Currency Consolidations (There’s an App for That) appeared first on Encore Blog.