Currency Translation Adjustment: Comparing P&L accounts at the average and month end rates
With the release of Management Reporter CU7 and CU8, currency translation for Dynamics AX 2012 and Dynamics GP 2013 was introduced. Customers want to be able to compare their Profit and Loss accounts at the weighted average or average rate to the month end rate. Each ERP allows one currency translation method to be applied to the account, so a report will help you accomplish this goal.
This video explains how to create a report that shows you the month end rate and apply it to the profit and loss accounts so you can compare and get a currency translation adjustment. The high level steps are:
- Pick an account that uses the Current calculation method and add it to an Income Statement. In my example, I used Cash. It will never change, its always going to use the month end rate.
- Hard code your functional currency in a column using Currency Display, so that when you flip between any currency, it will remain in the base currency. In the example, USD is specified in column B.
- A calculation column is added to divide the columns to get the spot rate. The entire column will be divided, but we only care about the first row.
- Add as much formatting as you like to hide columns and rows. But the key part is then to add a normal periodic column. This column will be translated at each accounts currency translation method.
- Create another calculated column. The formula should be the USD amount column * the spot rate row. In my case, this was B*E.130.
- Add a Currency Translation Adjustment column to subtract the two columns and show the difference.
[embed]https://www.youtube.com/watch?v=XF617rpH9LE&feature=youtu.be[/embed]
NOTE: You can import the attached .tdbx and import it to get started quickly. Just change the accounts in the row definition and the currency display in the column definition.