This presentation discusses the introduction of a second financial reporting currency to your existing Dynamics NAV installation. Assume the following scenario: Your business has been acquired by a foreign company and is now a subsidiary that must report its financials to the parent. The parent company asks that you present monthly G/L balances in the parent’s currency.
In the following presentation we are looking at Microsoft’s Norwegian demo company CRONUS Norge AS who reports in Norwegian Kroner (NOK) and is now being asked to export monthly G/L balances to the American parent company in Dollars (USD).
Microsoft Dynamics NAV records all transaction in Local Currency (LCY) and offers a functionality called Additional Reporting Currency (ACY) which allows to record all G/L entries in a second currency at the transactional exchange rate. Exchange rate swings will cause differences between amounts posted in LCY and ACY, which must be adjusted at the end of every Month.
Setup: Chart of Accounts
The Local Currency (LCY) in CRONUS Norge AS is NOK. The Additional Reporting Currency (ACY) is currently not being used. We will set up USD as Additional Reporting Currency (ACY) in CRONUS Norge AS.
The first step is to add four G/L Accounts to which Dynamics NAV will post currency adjustments:
| 9350 | Kto. for real. agio – t.val. | Realized G/L Gains Account |
| 9360 | Kto. for real. disagio – t.val. | Realized G/L Losses Account |
| 9370 | Konto for restagio | Residual Gains Account |
| 9380 | Konto for restdisagio | Residual Losses Account |
Setup: Currency
The four new G/L Accounts must then be set up in the Currencies window for USD:
The next step is to enter daily exchange rates:
The first line in the above picture had already been entered by Microsoft as part of the CRONUS Norge AS demo company and means that the system will convert USD 100.00 to NOK 639.1319 on January 1st, 2013.
The next lines, one for every business day starting with December 30th, 2013, had been entered by me for this demonstration. Microsoft published this test database in May 2013 containing test data for the years 2014 and 2015. At the time of this writing, in July 2013, we of course do not have any exchange rates for 2014. I simply used two year old exchange rates and imported them into the system. So if you are reading this presentation in January 2014 or later, please forgive my phantasy exchange rates.
The second line in the above picture means the system will convert USD 1.00 to 5.9682 NOK on December 30th, 2013.
Setup: Direction of Adjustment
You must decide for which currency the system will post adjustments. The field Exchange Rate Adjustment in the Reporting tab of the G/L Account Card offers three choices. For this presentation we chose the option Adjust Amount, which will have the system post adjustments in NOK.
Setup: VAT Exchange Rate Adjustment
You must also decide for which currency the system will post VAT adjustments. The field VAT Exchange Rate Adjustment in the Reporting tab of the G/L Setup Card offers again three choices. For this presentation we chose the option Adjust Amount, which will have the system post adjustments in NOK.
Setup: Set Work Date
The Work Date determines at which exchange rate past transactions will be converted to the Additional Reporting Currency. All G/L Entries posted at or before the Work Date will be converted with the exchange rate valid on the Work Date. All G/L Entries posted after the Work Date will be converted at the exchange rate valid on the posting date.
The CRONUS Norge AS demo company starts with opening balances posted to the closing date C12/31/2013. I set the Work Date to the same day. Opening balances will be converted at the exchange rate valid on December 31st.
Back-Filling the Additional Reporting Currency
Now that all setup decisions have been made, it is time to actually enter the Additional Reporting Currency and back-fill past transactions with it.
Enter USD into the Additional Reporting Currency field in the Reporting tab of the General Ledger Setup window:
When you click OK the Adjust Add. Reporting Currency window opens:
Enter a Document No. and select the G/L Account you post Retained Earnings to, and click OK.
The system asks you to confirm the following message. It may be a good idea to save a database backup before performing this step.
Once you confirmed the message, the system back-fills all General Ledger Entries, Analysis View Entries, VAT Entries, Job Ledger Entries, and Production Order Line Entries with the Additional Reporting Currency.
Result of the Back-Filling
Every General Ledger Entry now contains an Additional-Currency Amount in USD:
There is no field in the G/L Entry table that would record the exchange rate used for the conversion. To verify which exchange rate had been used for the conversion, you can copy and paste the entries into Excel and re-calculate the exchange rate:
In the above picture Column I is a division of Column G by Column H. You can see the system used the exchange rate valid on the posting date of the original transaction to calculate the Additional-Currency Amount.
In the Chart of Accounts you can make the Additional-Currency columns visible and view net changes and balances in both currencies:
In the above picture a Date Filter has been set for June 2014.
The Net Change columns show the sum of all June entries in both NOK and USD.
The Balance at Date columns show the account balances as of June 30th, 2013.
The Balance columns ignore the Date Filter and show the total balance on the accounts.
Transactions in Additional Reporting Currency
CRONUS Norge AS won the business of French sports retailer Francematic and agreed to bear the exchange rate risk, invoicing the customer in EUR.
On January 5th, 2014, CRONUS Norge AS sold 10 bicycles to Francematic for EUR 4,393.85:
The Sales Invoice Statistics show Amounts in EUR and Amounts (LCY) in NOK.
(The profit margin for a bicycle in the Microsoft demo data is unnaturally high. I did not change the item costing or pricing for this presentation.)
The Customer Ledger Entry the system posted with the sales invoice also shows the Amount in EUR and the Amount (LCY) in NOK:
The General Ledger Entries the system posted with the sales invoice show the Amount in NOK, and the Additional-Currency Amount in USD:
Adjusting the Additional Reporting Currency
If you have foreign currency vendors or customers you adjust exchange rates at the end of every Month. The same Adjust Exchange Rates function will now also adjust G/L Entries. All you have to do is to set the Adjust G/L Accounts for Add.-Reporting Currency.
When you click OK the system posts the adjustments for Customers, Vendors, Banks, and G/L Accounts.
Result of the Currency Adjustment: Customers
To understand the results of the currency adjustment for the 10 bicycle sales order to France, let us first take a look at our EUR exchange rates:
We could have maintained daily exchange rates for EUR as we do for USD, but CRONUS Norge AS decided to maintain only monthly exchange rates for EUR.
Note that field Relational Exch. Rate Amount contains the same rate 7.7381 for the first and last day of January.
Only the Relational Adjmt Exch Rate Amt drops to 7.6633, meaning the Norwegian Kroner lost value against the EUR over the course of the Month.
The next picture shows the Detailed Cust. Ledg. Entries for the sales order to France and the adjustment we just posted.
When the invoice was posted at the beginning of the Month, EUR 1.00 was worth NOK 7.7381, and the EUR 4,393.85 order was valued at NOK 34,000.05.
Now at the end of the Month EUR 1.00 is worth NOK 7.6633, and the order must be valued at NOK 33,671.39. The adjustment posted an unrelized loss of NOK -328.66 for the receivable against the French customer.
The next picture shows the General Ledger Entries to Account 2320 A/R International for the same adjustment:
The Amount field shows the same NOK -328.66 adjustment we already saw in the Detailed Customer Ledger Entries. This adjustment translates to a USD -55.96 adjustment in the Additional-Currency Amount field, which reduces the valuation of the receivable against the French customer from USD 5,642.32 to USD 5,586.36 according to the loss the Norwegian Kroner suffered against the EUR.
Result of the Currency Adjustment: General Ledger
The next picture shows the daily USD exchange rates towards the end of January. The Month ends with an exchange rate of USD 1.00 to NOK 5.873, and this is the rate the system adjusted all January entries to.
The next picture shows a loss of NOK 4,043.75 posted to G/L Account 9360 Realized G/L Losses Account:
This means the Norwegian Kroner may have risen and falled against the Dollar during the Month, but the weighted averarage of all transactions posted in January resulted in a loss of NOK against USD.
The last picture of this presentation shows three of the 77 G/L Entries the adjustment posted:
The first entry shown is again the NOK -328.66 adjustment for the EUR exchange rate swing against the French customer. By the sequence in field Entry No. you can tell this customer adjustment was posted first.
The second entry for NOK -20.371.55 is the adjustment for all January entries for G/L Account 2320 A/R International.
The last entry for NOK 4,043.75 is total adjustment against all January entries in all adjusted G/L Accounts.
Author: Thomas Paulsen
Published: 16-Nov-2014