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!

Consolidation in AX 2012 - Introduction and Pre-Requisites

I have been debating between doing consolidation in Dynamics AX or in Management Reporter. I decided to do it in AX since the setup is very basic. Below are some ideas and suggestions on where to start.

• Consolidation vs Elimination companies. You need to remember that you CANNOT post any manual entries in a consolidation company. So if you do intent to use your consolidation company as your elimination company as well, keep in mind that you won't be able to manually post eliminations. Also, a company cannot be deleted from AX 2012 (although it can in previous versions of AX), make sure to think about your consolidation structure before creating those entities in AX. 

Note: A consolidation or elimination entity is created the same way as any legal entity. I always use the data export/import function to get the basic setup in from an existing entity. The only two thing that will need to be done are number sequences wizard and ledger setup in General Ledger.

Once the entity is created, you will need to flag the company as consolidation entity or elimination entity or both. For that go to Organization administration > Setup > Organization > Legal Entities. Under the General tab you will find two flags "Use for financial consolidation process" and "Use for financial elimination process".

• Consolidation and open balances. If you have been using AX for several years but you just start to execute consolidation in AX, you will need to bring all your activity over in the consolidation company in order to get your open balances. The Online consolidation doesn't have an option to bring opening balances. Opening balances will be created once you execute the year-end close process. So if you do have data like me since 1994, you will need to close almost 20 years before getting your open balances for the consolidation numbers to be correct

• Rebuild balances during consolidation process. I suggest to un-check this option as AX will schedule a job to rebuild all dimension sets you have setup. However, if you don't consolidate at the dimension level, you don't need to rebuild all dimension sets. Also, you might have to run the consolidation several times before verifying the numbers and it's faster to execute the rebuild manually in that case

• Elimination during consolidation process. If you created a different entity for the elimination then this option will not be valid for you since the proposal should be created in the elimination entity and not in the consolidation entity. Also, it's always better to verify the elimination proposal before posting it. Finally, you might need to calculate fixed amount and modify your elimination rules based on the consolidation result before being able to execute the elimination rules.

This is some food for thoughts, I will soon post more details about the consolidation process and the currency conversion. Looking forward for feedback and reviews. Enjoy!