Consolidation in AX 2012 - Currency Translation

Let's review today how to setup currency translation on the consolidate [Online] process in AX 2012. I would like to share this with you because it took me some time to understand how the calculation was done especially for the reporting currency to come up correctly. I have several foreign entities setup with foreign currency and I am using USD as my reporting currency in order to pull reports from AX and from Management Reporter.

The first set of setup that needs to be done is the different rates that will be used during the consolidation. The rate can be different depending on the type of the account, balance sheet accounts usually use an average rate compare to profit and loss accounts that usually use historical rates.
The consolidate online form in AX 2012 offers the capability to either manually enter the rate to use during consolidation or to use the exchange rate type and exchange rate tables. I found very useful to actually use the tables so the users can always refer back to what rates was used for what and when. If you do manually enter the rates on the consolidation form, it will not save the rate for future reference.

The exchange rate type is setup under GL > Setup > Currency < Exchange rate types.

Then for each exchange rate type, you will need to setup the rate for the period it is valid for under GL > Setup > Currency > Currency Exchange Rates. Remember that the Start date is the effective date so if you are to calculate an end of month rate, the start date should be set as the first of the month in order to reevaluate all the transactions of that month with the end of month rate. On the picture below you can see that the rates start on 10/1/12 and 10/1/13.

Note: Up to CU6, AX 2012 doesn't offer any functionality to calculate an average rate. You will have to manually calculate it and enter it in an exchange rate type you call average in order to use it during the consolidation in AX. In CU7, AX will auto-calculate the average rate for the period but the consolidation has to happen in Management Report and not in AX, I will create a post on this new process soon.

Once all the rates are setup in the exchange rate tables, you will need to specify the accounts and tell which exchange rate type it should use in the consolidation form. The From and To account can be a range of accounts or a single account (you would specify the same account in the two fields).
Once you select the exchange rate type, the rate that the consolidation will use is displayed. This rate is selected based on the consolidation period selected on the Criteria tab of the consolidation form.

Once all the accounts are setup, you can click Ok to execute the consolidation. Refer to the previous post for the rest of the setup.
The consolidation process will first convert the amount in the consolidation currency and then sum the transactions and generate a consolidation entry based on the grouping of financial dimensions that should be brought over in the consolidation company. If no dimension are brought over then only one entry will be created per account.
The fact that the amount gets translated on every single transaction before it gets summed is creating currency evaluation errors. The sum of converted amounts doesn't equal to the translation of the sum of those amounts. I know this is hard to picture so let's review an example:

In the table above you can see that each line is using an exchange rate that is in range of a CAN/USD exchange rate but the last line which is the sum of all the transactions (and the consolidation entry that AX will create) generates a USD value that is in fact wrong.
The translation of the currency should happen on the total and not on single transaction when the consolidation happens. In order to work around this, the foreign currency revaluation function can be executed. This will reevaluate the consolidation entries and apply the correct rate on the total.

The foreign currency revaluation function is located under GL > Periodic > Foreign currency revaluation. In my example, I want to reevaluate the USD value only since the transaction in the local foreign currency is created correctly (just the sum of the original transactions in the foreign currency). Only the USD value is wrong due to the conversion happening on each transactions instead of the sum of all those transactions.

Once executed, you can see how AX created a new entry in USD only to adjust the difference created during the consolidation.

This process can also be executed for the reporting currency to be corrected evaluated in foreign entities, we will talk about this in another post if you want more information :)

I hope you enjoyed this post, please leave comments or ask any questions if you want more details! See you soon!