When doing a financial consolidation, financial data from different companies need to be combined in a single “consolidation” report. As the data that need to be consolidated are often based on different Chart of Accounts (COA), the question arises how ledger accounts from different COA’s can be combined into a single consolidation report. This question will be answered in the following based on a simplified example.
The financial data of company DEMF and BRMF need to be consolidated. While the first company (DEMF) uses a “shared” COA, the second company (BRMF) uses a local Brazilian COA. What makes things even more complex is that company DEMF records all transactions in EUR-currency, while company BRMF keeps all its books in Brazilian reals. The next screenshot shows you the COA’s and currencies setup for the two companies.
In order keep the example as simple as possible, only the bank accounts of both companies are considered for the following illustrations.
In company DEMF only one single transaction with a total amount of 10000 EUR has been recorded on bank account (main account) “110200”.
In company BRMF two local currency transactions have been recorded on the bank account (main account) “184.108.40.206.02”.
The exchange rate used for consolidation purposes is 300 BRL / 100 EUR.
Against the background of those data, a financial consolidation report should show a total balance of 50000 EUR or 150000 BRL in the bank accounts line.
To realize this, the following setup has been done in Management Reporter.
Step 1: Setup of the row definition
The first step in setting up the consolidation report is mapping the different main accounts in the row definition of the report. This mapping in done by specifying separate row links that hold the main account information of both companies. Please see the following screenshot for an example.
Step 2: Setup of the reporting tree Definition
The next step is setting up a reporting tree. When doing this setup, you have to make sure that a reference is made between the different companies and the row definition / row links established in the first step.
Step 3: Setup of the column Definition
The third step is setting up a column definition as illustrated in the next screenshot.
Please note that you need to link the different columns with the reporting tree elements if you want to report on the different legal entities.
Step 4: Setup of the report Definition
The last step in the report generation is the combination of all previous elements (row definition, reporting tree and column definition) into a report. Please note that you need to select the reporting currency check box before running the report if you want to do a currency conversion once the report that gets generated.
Once the report is generated you can have a look at the data in the various currencies setup in Dynamics AX. The next two screenshots show you the EUR-currency and BRL-currency report.
Vinoth Venugopal said:
i have a requirment to change the company currency of a company where transactions exists for the past 2 years. System has option to change curency but only from current fiscal year . Is there any option to reverse and change the transactions base currency value ?
or posting manual journals to reverse in existing transaction for the old periods and post new journals with new currency is the only option ?
Ludwig Reinhard said:
Hi Vinoth, I just replied to a similar question on the AX community forum (Please see: https://community.dynamics.com/ax/f/33/t/239194). If you want to have your vouchers posted in a different currency, then the only way to do that seem to be to reverse the existing transactions and post them new. That is because the currency conversion tool – that allows changing your company currency – does not affect your historical vouchers. Hope this answers your question. Best regards, Ludwig
Ronel Geldenhuys said:
Hi Ludwig, I have a requirement for a Dynamics AX Customer for the following:
2 Chart of accounts for 1 legal entity:
a) Transactional (standard COA for group – shared)
b) Reporting for statutory purposes to report to Government.
What is the best way to configure?
Ludwig Reinhard said:
Hi, AX only allows assigning a single COA per legal entity. This is usually the one that you need for statutory (external) reports. If you need a second one – for example – for group reporting purposes you can consider using a financial dimension for that purposes. Other alternatives are making use of the consolidation account feature or doing a mapping through Management Reporter. There are really a lot of options available and without knowing details of the solution, it is difficult giving a recommendation. Hint: Please also take a look at the AX community forum where similar questions have been discussed. Best regards, Ludwig