Menu

Looking for the latest industry news and events?  Want to learn more about Microsoft Dynamics?  You've come to the right place!

Handling Foreign Currencies in Microsoft Dynamics AX

Concepts

If you are accepting foreign currencies in Microsoft Dynamics AX, you have to deal with currency conversion rates. There are two scenarios you’ll run across – realized and unrealized.

Unrealized gain or loss – If you post transactions in a foreign currency, you most likely need to run a foreign currency revaluation at the month end to recognize the unrealized gain or loss. Standard Microsoft Dynamics AX provides foreign currency revaluation on open AR, open AP and main accounts.

Realized gain or loss – If you post and pay an invoice (AR or AP) in a foreign currency and the exchange rate on the invoice date is different from the one on the payment date, the value difference in your accounting currency posts to the realized gain or loss accounts when the payment is posted.

Scenarios

Step One (08/03/15) – GBSI invoices a customer €1,000.

Ledger currency setup – Legal entity GBSI: accounting currency – GBP £, reporting currency – USD $.

Ledger Currency Setup

Exchange rates setup – Currency pairs of EUR-GBP and USD-GBP are defined

Exchange Rate Setup

Defining Exchange Rates

Free text invoice – A customer invoice of €1,000 is posted on 08/03/15.

Free Text Invoice

Customer transaction – Sub-ledger captures the invoice transaction in both the transaction currency € and accounting currency £. The £ value is calculated using the spot rate that is on 08/03/15 or the last available spot rate, in this case €1 = £0.7 on 08/01/15.

Customer Sub-Ledger

Voucher transaction – Ledger capturers the invoice transaction in three currencies: transaction currency, accounting currency and reporting currency.  The last available spot rates, in this case €1 = £0.7 and $1 = £0.55 on 08/01/15 are used to calculate the sterling and dollar amounts.

Voucher transaction

Step Two (08/31/15) – GBSI runs foreign currency revaluation on open ARs (unrealized).

Accounting currency unrealized accounts setup – The unrealized gain or loss accounts that are set up on a particular foreign currency, in this case €, overrides the setup on ledger form when system revaluates accounting currency of open ARs.

Accounting currency unrealized accounts setup

Reporting currency unrealized accounts setup – The unrealized gain or loss accounts that are set up on a particular foreign currency, in this case $, overrides the setup on ledger form when system revaluates reporting currency of open ARs.

Unrealized gain or loss setup

AR foreign currency revaluation – Typically you will run this at the month end. Be aware the simulation is optionally available to foresee the revaluation effects without posting anything.

Foreign currency revaluation

Customer transaction – Sub-ledger captures the revaluation adjustments in accounting currency, £100 in this case. The £100 is the result of €1,000 * 0.8 (on 08/31/15) – €1,000 * 0.7.

Customer sub-ledger

Voucher transaction – Ledger posts the revaluation adjustments in both the accounting currency £ and reporting currency $. Be aware the AR revaluation updates both the sub-ledger and ledger from the perspective of accounting currency.

Voucher transaction

Step Three (09/01/15) – GBSI posts a payment of €1,000 to fully settle the invoice (realized).

Accounting currency realized accounts setup – The realized gain or loss accounts that are set up on a particular foreign currency, in this case €, overrides the setup on ledger form when system revaluates accounting currency.

Realized accounts setup

Reporting currency realized accounts setup – The realized gain or loss accounts that are set up on a particular foreign currency, in this case $, overrides the setup on ledger form when system revaluates reporting currency.

Realized gain or loss account setup

Customer payment journal – A payment of €1,000 is posted to fully settle the invoice on 09/01/15.

Customer payment journal

Customer transaction – Sub-ledger records the payment of €1,000 (or £750 in accounting currency) given the spot rate on 09/01/15 is €1 = £0.75. A realized adjustment of £50 = €1,000 * 0.75 (on settlement day) – €1,000 * 0.7 (on invoice date) is recorded. At the same time, the unrealized adjustment of £100 from AR revaluation gets reversed because the revaluation is really nothing but a provision to accommodate the fluctuating exhange rate.

Customer transaction

Voucher transaction – Ledger capturers the realized gain of £50 from the invoice settlement in accounting currency. Meanwhile ledger reserves the unrealized gain of £100 from the previous AR revaluation. The same logic applies to reporting currency. Also, be aware that a foreign transaction of €1,000 was posted to the 110130 euro bank main account as this fact is going to be considered for the next step.

Voucher transaction

Step Four (09/30/15) – GBSI runs foreign currency revaluation on main accounts (unrealized).

Accounting currency unrealized accounts setup – The unrealized gain or loss accounts that are set up on a particular foreign currency, in this case €, overrides the setup on ledger form when system revaluates accounting currency of main accounts.

Reevaluation of currency

Reporting currency unrealized accounts setup – The unrealized gain or loss accounts that are set up on a particular foreign currency, in this case $, overrides the setup on ledger form when system revaluates reporting currency of main accounts.

Reporting Accounts

Revaluation main account setup – For any main account you want to do a revaluation on, make sure it has one of the main account types: Profit and loss, Revenue, Expense, Balance sheet, Asset and Liability. Also, make sure to select the foreign currency revaluation checkbox.

Selecting foreign currency

Main account revaluation – Microsoft Dynamics AX provides filters for main account, date, currency and account type to run a main account revaluation.

Main account revaluation

Bank transaction – Unlike the AR revaluation, a main account revaluation does not update sub-ledger accounts.

Bank sub-ledger

Voucher transactions – An unrealized gain of £20 = €1,000 * 0.77 (on revaluation date) – €1,000 * 0.75 (on original posting date) is posted for accounting currency. An unrealized loss of $27.78 is posted for reporting currency in the same vein.

Ledger

Conclusions

  • A realized gain or loss is recognized at invoice settlement in a foreign currency.
  • A unrealized gain or loss is recoginzed at foreign currency revaluation of open ARs, open APs or main accounts.
  • A company trading in foreign currency must use realized gain or loss. However, it is not mandatory to use unrealized gain or loss because the foreign currency revaluation is nothing but a provision to accommodate the fluctuating exchange rate. The revaluation makes the balance sheet more accurate, as you have made a provision for the potential profit and loss that may or may not occur in future due to change in exchange rate.
  • AX provides simulation option to foresee the effect of foreign currency revaluation on ARs or APs without posting anything.
  • AR or AP main accounts should be revaluated through the sub-ledger revaluation. Do not include the AR or AP main accounts while revaluating your ledger accounts.
  • Foreign currency revaluation uses spot rates defined. To use a different rate, for instance, an average rate to revaluate your P&L accounts, you will need maintain multiple exchange rate types and change the default exchange rate type on the ledger form every time before you run the revaluation on P&L accounts.
  • The realized and unrealized gain or loss accounts that are set up on a particular currency overrides the setup on the ledger.

See why CFOs choose mcaConnect and Microsoft Dynamics AX


Eric FuWritten by Eric Fu
Dynamics AX Application Consultant, mcaConnect