Additional Reporting Currency

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:

9350Kto. for real. agio – t.val.Realized G/L Gains Account
9360Kto. for real. disagio – t.val.Realized G/L Losses Account
9370Konto for restagioResidual Gains Account
9380Konto for restdisagioResidual Losses Account
Microsoft Dynamics NAV - Chart of Accounts
Microsoft Dynamics NAV – Chart of Accounts

Setup: Currency

The four new G/L Accounts must then be set up in the Currencies window for USD:

Microsoft Dynamics NAV - Currencies
Microsoft Dynamics NAV – Currencies

The next step is to enter daily exchange rates:

Microsoft Dynamics NAV - Currency Exchange Rates
Microsoft Dynamics NAV – Currency 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.

Microsoft Dynamics NAV - G/L Account Card
Microsoft Dynamics NAV – G/L Account Card

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.

Microsoft Dynamics NAV - General Ledger Setup
Microsoft Dynamics NAV – General Ledger Setup

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.

Microsoft Dynamics NAV - Set Work Date
Microsoft Dynamics NAV – Set Work Date

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:

Microsoft Dynamics NAV - General Ledger Setup - Additional Reporting Currency
Microsoft Dynamics NAV – General Ledger Setup – Additional Reporting Currency

When you click OK the Adjust Add. Reporting Currency window opens:

Microsoft Dynamics NAV - Adjust Additional Reporting Currency
Microsoft Dynamics NAV – Adjust Additional Reporting Currency

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.

Microsoft Dynamics NAV - Confirm Additional Reporting Currency
Microsoft Dynamics NAV – Confirm Additional Reporting Currency

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:

Microsoft Dynamics NAV - General Ledger Entries with Additional Currency Amount
Microsoft Dynamics NAV – General Ledger Entries with Additional Currency Amount

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:

Microsoft Excel - General Ledger Entries with Currency Exchange Rate
Microsoft Excel – General Ledger Entries with Currency 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:

Microsoft Dynamics NAV - Chart of Accounts - Additional Currency
Microsoft Dynamics NAV – Chart of Accounts – Additional Currency

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:

Microsoft Dynamics NAV – Posted Sales Invoice

The Sales Invoice Statistics show Amounts in EUR and Amounts (LCY) in NOK.

Microsoft Dynamics NAV - Posted Sales Invoice Statistics
Microsoft Dynamics NAV – Posted Sales Invoice Statistics

(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:

Microsoft Dynamics NAV - Customer Ledger Entry
Microsoft Dynamics NAV – Customer Ledger Entry

The General Ledger Entries the system posted with the sales invoice show the Amount in NOK, and the Additional-Currency Amount in USD:

Microsoft Dynamics NAV - General Ledger Entries
Microsoft Dynamics NAV – General Ledger Entries

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.

Microsoft Dynamics NAV - Adjust Exchange Rates
Microsoft Dynamics NAV – Adjust Exchange Rates

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:

Microsoft Dynamics NAV - Currency Exchange Rates to EUR
Microsoft Dynamics NAV – Currency Exchange Rates to EUR

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.

Microsoft Dynamics NAV - Detailed Customer Ledger Entries
Microsoft Dynamics NAV – Detailed Customer Ledger Entries

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:

Microsoft Dynamics NAV - General Ledger Entries
Microsoft Dynamics NAV – General Ledger Entries

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.

Microsoft Dynamics NAV - Currency Exchange Rates to US Dollar
Microsoft Dynamics NAV – Currency Exchange Rates to US Dollar

The next picture shows a loss of NOK 4,043.75 posted to G/L Account 9360 Realized G/L Losses Account:

Microsoft Dynamics NAV - Chart of Accounts - Currency Balance
Microsoft Dynamics NAV – Chart of Accounts – Currency Balance

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:

Microsoft Dynamics NAV - General Ledger Entries
Microsoft Dynamics NAV – General Ledger Entries

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