GL foreign currency revaluation types

26 Sunday Apr 2015

Posted by Ludwig Reinhard in General Ledger

≈ Comments Off on GL foreign currency revaluation types

Tags

Dynamics AX 2012, Foreign currency revaluation, Vendor payments

If you record foreign currency transactions you might experience a situation in which Dynamics AX records foreign currency gains/losses on the wrong ledger account. This blog post illustrates the circumstances under which you might run into this situation and provides you a possibility how to fix this issue.

Scenario
For this example, the following (fictional) currency exchange rates between the EUR and USD are used. (Please note that the local company currency is EUR). The first transaction recorded in Dynamics AX on 1 January 2015 is a vendor invoice for 1000 USD. At the time the vendor invoice is recorded the EUR/USD exchange rate stands at 80 EUR/100 USD, resulting in an amount equivalent to 800 EUR. As the invoice is not settled until the end of January 2015, a foreign currency revaluation is run in the Accounts Payable module. This foreign currency revaluation process results in an unrealized exchange rate gain of 50 EUR (1000 USD * [80/100 EUR/USD – 75/100 EUR/USD]), which is recorded on the ledger account for unrealized exchange rate profits. The next screenshot shows the corresponding voucher.
On 5 February 2015 the vendor invoice is paid by using a wire transfer. This transfer is – as usual – recorded in a vendor payment journal in the Accounts Payable module. Because the USD appreciated to 87 EUR/100 USD at the time the payment is made, an exchange rate loss of 70 EUR is realized. This loss is recorded on the ledger account for realized exchange rate losses. At the same time, Dynamics AX cancels the previously recorded unrealized exchange rate gain of 50 EUR. The next screens show the payment made and the corresponding vouchers created. Please note that the payment is made via a transfer account – in my example account “109053 Transfer Bank Charlie”. This transfer account is cleared on 8 February when the bank transaction is posted (see the next screenshot). As the EUR/USD exchange rate changed from 87 EUR/USD (at the time the payment was made) to 83 EUR/USD (at the time the bank transaction was posted), a balance of 40 EUR remains on the payment transfer account. This balance can be identified in the following screenshot.
To clear this balance on the transfer account the foreign currency revaluation process is run in the General Ledger module for the respective account. The outcome of this revaluation process is a balance of 0 EUR on the transfer account. EN_11 If one takes a close look at the foreign currency posting that is made to clear the balance on the transfer account, one can identify that the balance was posted on the ledger account that is used for recording unrealized exchange rate losses (see the next screenshot). Posting the remaining balance of 40 EUR against the unrealized exchange rate loss account is wrong because the transaction represents a realized loss. The next figure summarizes all transactions that have been recorded for this example and highlights the remaining realized and unrealized exchange rate gains and losses recorded in Dynamics AX.Even though the total exchange loss of 30 EUR is correct from an overall perspective, the elements that make up this total amount are wrong. That is, if your company reports realized and unrealized currency exchange gains/losses separately, your profit and loss statement shows incorrect figures.

Possible solution
In this section I will show a possible solution to the problem illustrated. This solution requires some minor system modifications that are described next.

Step 1
The first modification required is extending the Base Enum values “FinancialGain” and “FinancialLoss” of the “CurrencyGainLossAccountType” Base Enum. In my example I simply added the country codes for Germany (DE) and the USA (US). Please see the next screenshot for details.

Step 2
The next (optional) step is to change the following two labels: @GLS109008 and @GLS109005.

Step 3
Step three requires extending the LedgerExchAdj class by the same country codes that have been used in step 1. Please note that those codes are entered slightly different (#isoDE, #isoUS).

Step 4
A similar adjustment is required in the ledger form as illustrated in the following screenshot.

The result of those modifications is that two new exchange rate transaction types are available in the ledger form (see the next screenshot).

With this setup I repeat the previously recorded transactions on another (“fresh”) bank account and its corresponding transfer account. All steps and vouchers are identical except the voucher that is generated when running the foreign currency revaluation in GL for the transfer account.

As you can identify from the next screenshot, after having done the previously described system modifications, Dynamics AX does now record the exchange rate adjustment of 40 EUR on the newly setup account that represents a realized exchange rate adjustment. This change allows you reporting currency exchange rate differences correctly in your profit and loss statement.
Please note that the foreign currency revaluation process in the Bank module is also affected by the changes described here. For details, please see my previous post.

Foreign currency revaluation cash and bank management module

12 Sunday Apr 2015

Posted by Ludwig Reinhard in Bank Management

≈ Comments Off on Foreign currency revaluation cash and bank management module

Tags

Bank, Dynamics AX 2012, Foreign currency revaluation

For most users, Dynamics AX does not provide a foreign currency revaluation functionality in the Bank Module. As a result, differences between the General Ledger Module and the Bank Module can arise. The following example illustrates how those differences might arise and what users can do to avoid them. (Note: All EUR/USD exchange rates used in the following are purely fictional).

Scenario
A company located in the EURO-zone has some idle foreign currency reserves (3 mio. USD) and deposits them in its bank account („Bank 13“) on 15 December 2014. At the time the money is deposited, the exchange rate between the EUR and the USD stands at 0.73 EUR/USD resulting in an amount equivalent to 2.19 mio. EUR. On 31 December 2014 the EUR/USD exchange rate is 0.70 EUR/USD. At this exchange rate the foreign currency deposit value expressed in EUR-currency is 2.1 mio. To track the exchange rate loss in Dynamics AX, the periodic General Ledger foreign currency revaluation process is run. The result of this periodic process is that a foreign currency loss of 90000 EUR is recorded on the main account “Bank 13” in the General Ledger Module.As this transaction is recorded in the General Ledger Module only, a difference between the General Ledger and Bank Module balance for Bank Account 13 arises as illustrated in the following screenshots.

Solution
To fix this issue the (country-specific) foreign currency revaluation functionality in the Bank Module can be made available for all Dynamics AX users. This can be realized by adding country codes into the “BankExchAdj_RU” menu item.Similar changes are required in the “BankExchAdj_RU” class and for the extended data type „BankExchAdjVoucher_RU“. The next screenshots illustrate the changes required. After doing all those adjustments a new foreign currency revaluation functionality can be identified in the periodic section of the cash and bank management module. The only thing missing to use this functionality is setting up a number sequence in the Bank Module parameters to record the exchange adjustment vouchers.

Finally, all the transactions that have been recorded on “Bank 13” are re-entered for “Bank 14”. The only difference is that the periodic foreign currency revaluation process is now run from the Bank Module rather than from the General Ledger Module. The outcome of those transactions can be viewed in the next screenshots. As one might expect, using the new foreign currency revaluation functionality that is available in the Bank Module results in identical balances in General Ledger Module and the Bank Module.

Use document templates to inform your vendors about payments made

03 Friday Apr 2015

Posted by Ludwig Reinhard in Accounts Payable

≈ 2 Comments

Tags

Document templates, Dynamics AX 2012, Vendor payment advice

Occasionally you might face a situation where you need to inform an external party – such as a vendor or customer – on something out of Dynamics AX. Thereby you might notice that no standard Dynamics AX report is available that might help you with your problem. In such situations you basically have two options: (a) try to find the data you need in different Dynamics AX forms, export them to Excel, gussy up your data and copy them into an Email that you send to your customer/vendor, or (b) ask somebody from IT to create a new Dynamics AX report for you. If (a) takes up much of your time and if (b) is not possible due to time and budget constraints, you can use document templates to create your own reports.

If you search the internet on how to setup document templates in Dynamics AX you will quickly notice that there are already several blog posts and websites available that give you an idea on how they can be implemented in Dynamics AX. Yet, I found that information rather abstract and difficult to implement in practice. For that reason, I will use an example that I experienced many times in customer projects and show you the different steps that are required to setup and use document templates.

The example that I am going to use in the following is based on a situation where a vendor calls and asks you to send some information on what invoices have been paid with the last payment that you made. To finalize this request you can reprint a payment advice statement and sent it to your vendor. (Please note that this requires that the payment advice document has previously been saved in Dynamics AX as reprinting payment advice statements is not supported in the standard application). Another option would be to send your vendor a screenshot of the payment history form that is available from the vendor list page. If none of those options suits you, take a look at the steps below that show you how to setup your own ‘payment report’.

Please note that document templates are not made for mass communication but rather for individual communication with a limited number of stakeholders. This is mainly because document templates do not ship with a multi-select functionality in Dynamics AX. As a result, you have to select the different parties you want to communicate with individually. Another remark that I would like to make here is that document templates are not limited to the example that I illustrate below. They can rather be used for numerous other purposes such as informing customers/vendors on their account transactions, requesting information from customers / vendors and other stakeholders, etc.

Now let’s have a look at the steps required for setting up a document template to inform your vendors about the payments you made.

Step 1: Setup document management parameters
The first step is to ensure that you have setup Microsoft Word Templates as a file type in the document management parameters form that can be found in the organization administration module.

Step 2: Setup queries
To include Dynamics AX data in your documents you need queries. For the purpose of this example, I copied the default query ‘Vend’ twice to ensure that I do not destroy the original query. Thereafter, I simply renamed the first copy into ‘Vend_VendTrans’ and extended the second copy by the VendSettlement table as illustrated in the following screenshot.
(Please note that the queries have to be in line with the tables to which you plan to link your documents to. For that reason all queries that I use start with the ‘VendTable’).

Step 3: Setup document data sources
After setting up your queries in the AOT you have to make them available in the document data sources form that can be found in the organization administration module. When doing that, please ensure that you use the data source type ‘Query reference’.

Step 4: Setup your MS Word Template
After setting up your document data sources you can link them to your MS Word template by selecting ‘Add data’ from the Dynamics AX menu.
To setup your template, simply add the data you want to have included in your document by dragging and dropping the fields from the Microsoft Dynamics AX pane on the left.
Please note the following when setting up your template:

In the upper part of the template, I selected all fields from the Vend_VendTrans query and setup a filter to ensure that only payment data are included in this section. To avoid that Dynamics AX shows all payment data for the selected vendor, I created a second document version where I added a second filter that limits the payment data to those that have been executed within the last seven days.All of the data that are included in the lower part of my template are from the Vend_VendTrans_VendSettlement query. Also here I used two filters to ensure that only invoice data for the last seven days are illustrated in the lower part of the document.
After finishing the setup of my document I saved it as a MS Word Template.

Step 5: Save template in template library
The next step is to save your template in a SharePoint template library.

Step 6: Setup document types
After you saved your document in the template library you can link it back to Dynamics AX via the ‘document types’ form that is available in the organization administration module. When doing that please make sure that you point to the correct Sharepoint document library site and that you activate your templates.

Step 7: Use the template
After having done all of this setup you can use your document template in the vendors list page in the accounts payable module.
Once you select the document template that has been setup, MS Word automatically opens and illustrates your data from your query. In my example all payments made to the selected vendor can be identified in the table that can be found in the upper part of my document and the corresponding invoices are illustrated in the lower part. The link between the payments and the invoices is made via the voucher and offset voucher fields.
Please note that I created two document versions for this example; one that shows me all vendor payments made (illustrated on the left side of the following screenshot) and one that shows me all vendor payments made within the last seven days (illustrated on the right side of the following screenshot).After generating the document report you can directly Email it to your vendor via the default MS Office Email functionalities.

Also note that Dynamics AX automatically saves a copy of the documents that you generated as an attachment to the record for which you created the document for, which allows you to track the information that has been sent out to your stakeholders.

In summary, using document templates provides you a good instrument when it comes to communicating with external stakeholders. Even though the document template solution illustrated here is probably not the best and most elaborate solution, it can be helpful especially in situations where you do not have the time and resources available for making adjustments to Dynamics AX.