Tuesday, January 21, 2014

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!

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Really Good post .

    I was expecting this type of explanation from long time ,

    Could you explain with an example of Balance account for consolidation differences and Profit and loss account for consolidation differences in accounts for automatic transactions?

    I have searched all the manuals , but did not find any example .

    Share the accounts explanation with an examples.

    Thanks in advance.

    ReplyDelete
  4. Hi Lally,
    The setup of the accounts for automatic transactions posting for the consolidation is mainly used for currency conversion differences.
    AX offers the ability to setup 2 different accounts, one for currency differences when balance sheet accounts get converted and one for currency differences when profit and loss accounts get converted.
    Since the consolidation company trial balance must balance in the reporting currency, AX must post any currency rounding or differences to the trial balance to ensure balancing of the accounts.
    You can use the same account for both setup which we do, we have a balance sheet "Currency Conversion" account that we use.
    Let me know if you need more details.
    Thanks,
    Caroline.

    ReplyDelete
  5. Share the accounts explanation with an examples.

    Thanks in advance.

    ReplyDelete
  6. Hi Caroline ,

    I have onequestion about the consolidation of Fixed Asset accounts , hope fully you can answer me.

    I have 3 companies - USA , AU, FR , parent company is USD.

    When it comes to Consolidation process we need to consider the different exchange rates for the Balance sheet , P&L and Fixed Asset accounts.

    Closing rate for Balance sheet accounts.

    Avearage rare for Profit & loss accounts.

    Transaction date for Fixed Asset accounts.

    During consolidation process in the Management Reporter , we will define the main account codes then system would consider the General ledger entries only for consolidation . It does not consider the subledger entries of Customer , Vendor and Fixed Asset.

    I have a doubt about the Transaction date for the Fixed Assets. Fixed Assets has the two dates Actual Acquistion date and Transaction date ( Opening balance date in Ax )

    Assume that client is going on Go-live 04/01/2014 , So generally we will upload the opening balances of G/L(main account) , Sub ledger( Customer , vendor , bank , Item , FA ) on 03/31/2014.

    But the question is on which date i need to upload the FA opening balances because some assets had purchased in 2010, 2011 , 2012 also.

    Exchange Rate - 5/15/2010 - 1USD - 1.25 AUD.

    Fixed Asset purchased on 5/15/2010 - 8000 AUD (Company Currency)

    Fixed Asset value was on 5/15/2010 - 6400 USD (Reporting currency)

    If i upload the FA opening balances on 3/31/2014 , then during consolidation system would take the exchange rate on 3/31/2014, but actual asset acquistion date ( transaction date ) was on 5/15 /2010.

    Exchange Rate - 3/31/2014 1 USD - 1.75 AUD.

    Assume that i uploaded the FA opening balances on 3/31/2014 , but General ledger level ( Voucher entries ) there is no field to update the Actual asset acquistion date .

    If i upload the FA opening balances of Acquistion amount and Accumulate depreciation amount on 3/31/2014 , during consolidation system would take the exchange rate of 3/31/2014 , then After consoidation system would show the wrong amount , but it suppose to take exchange rate on 5/15/2010.

    So my question is do I need to upload the FA entries based on Actual Asset acquisition date (05/15 /2010) or prior to Go-Live date ( 3/31/2014). ?

    How to overcome this problem?

    Thanks in advance

    Lally

    ReplyDelete