In this blog, I will take you through the steps to create a simple foreign currency sales invoice transaction to demonstrate how Microsoft Dynamics 365 Business Central converts foreign currency (FCY) to local currency (LCY) amounts and how it accounts for unrealised/realised gains and losses.
I will also show how to run the adjusted exchange rates batch job for Dynamics 365 Business Central and NAV.
Business Central calculates the foreign currency gains and losses based on the fluctuation of the effective exchange rate between the posting date of a sales/purchase invoice and the date of a payment or any applying entry.
Setting up foreign currency for realised and unrealised gains and losses
On your Business Central Dashboard, go to Finance > Currencies.
You can also search for ‘Currencies’ in the search toolbar. (For NAV users you can find this under Financial Management > Periodic Activities > Currency > Currencies.)
You will be able to set the corresponding posting G/L Account No. for Realised and Unrealised Gains and Losses entries by editing the Currency Card.
I will be using USD as the foreign currency (FCY) with the below sample setup on the Currency Exchange Rates table.
On the first line of the above table, it indicates that on or after 01/08/19, USD amount transactions will be converted to LCY using the relationship of 2 USD = 1 AUD. This is effective until there is another entry with the latest starting date (i.e., 01/09/19 where 2.5 USD = 1 AUD).
How to create and post a foreign currency sales invoice
Amount: USD $10,000
Posting date: 1/08/2019
Exchange Rate: 2 USD = 1 AUD
The invoice below does not include GST and discounts, for easy review. I have also made sure the correct currency code is selected under the Invoice Details of the sales invoice.
By clicking on the ellipsis button beside the currency code, you can view the Exchange Rate amount that the transaction used to convert to LCY.
Note: If the Fix Exchange Rate Amount field on the Currency Exchange Rates Table are set to “Currency” or “Relational Currency”, you will be able to change the Exchange Rate or the Relational Exch. Rate Amount at this page. If you do not want these to be modified at transaction level, you will need to set the value to “Both”.
On the Sales Invoice Statistics, you will see that this has already converted to the Local Currency correctly using the exchange rate defined on the setup (10,000 USD / 2.0).
How to post entries for foreign currency sales invoices
Once the sales invoice is posted, it will create below G/L entries in LCY amount.
Also take note of the LCY amounts posted in the Customer Ledger Entry.
How to run the adjust exchange rate batch job
- Posting Date = 31/08/2019
- Exchange Rate: 1.25 USD = 1 AUD
This function is typically run on Month End. It will calculate differences for the individual currency balances for each open foreign currency transaction and post it to the unrealised gains/losses account. This will also adjust the sub-ledger entries (i.e., Vendor, Customer, Bank Ledger Entries). You can run this by clicking Adjust Exchange Rate on the Currencies List > Actions as shown below:
This will open a request page which will enable you to select the date range and as well as other filters to process the adjustment.
You can leave the Starting Date to blank so it will include any open entry prior to the End Date. The Posting Date is important as this will determine the effective exchange rate that will be the basis of calculating the currency fluctuations. In this example, I have used 31/08/2019 which is the same with the End Date. I have also filtered the Currency Code so it will only adjust USD transactions.
Note: Make sure Adjust Customer, Vendor and Bank Accounts option is enabled so it will also adjust the sub-ledger entries and tally with the G/L entries.
How to post entries to reflect the adjusted exchange rate
Once the batch job processing is completed, it will be posting the entries to reflect the unrealised gains/loss.
In the example above, the sales invoice posted on 1/08/19 has recorded $5,000 AUD in the receivables account. With the new exchange rate on 31/08/19, your receivables for the unpaid sales invoice must be $8,000 AUD. Business Central posts an Unrealised Foreign Currency Gain and increased the receivables account by $3,000 AUD to achieve this.
Notice that it also adjusted the Remaining LCY Amount in the Customer Ledger Balance to reflect the current LCY conversion.
Receiving foreign payment for the sales invoice
- Posting Date = 1/09/2019
- Effective exchange rate= 1 AUD = 2.5 USD
Foreign currency payment entries
In the payment entries below, the system posted a $4,000 AUD payment in the bank using the effective rate on 1/09/19. It has also reversed the Unrealised Foreign Currency Gain recorded during the Adjust Exchange rate job run. Then, as the LCY conversion of the applied payment is less than the original LCY of the invoice, Business Central automatically posts a Realised FX Loss of $1,000 AUD ($5,000 – $4,000).
The receivable was also reversed and the invoice in the customer ledger entry has been fully applied.
It is also worth mentioning that Business Central has the ability to connect to a currency service to automatically update the exchange rates.