PaulsenConsult.com, Microsoft Dynamics NAV Consulting
PaulsenConsult.com
 PaulsenConsult.com

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:

 

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
Microsoft Dynamics NAV Norway edition, Chart of Accounts Microsoft Dynamics NAV Norway edition, Chart of Accounts

Setup: Currency

 

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

Dynamics NAV - Currencies Dynamics NAV- Currencies

The next step is to enter daily exchange rates:

Dynamics NAV - Norway Edition - USD Exchange Rates Dynamics NAV - Norway Edition - USD 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.

Dynamics NAV - Norway Edition - G/L Account Card Dynamics NAV - Norway Edition - 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.

Dynamics NAV - Norway Edition - G/L Setup VAT Adjustment Dynamics NAV - Norway Edition - G/L Setup VAT Adjustment

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.

Dynamics NAV - Set Work Date 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:

Dynamics NAV - Norway Edition - G/L Setup VAT Adjustment Dynamics NAV - Norway Edition - G/L Setup VAT Adjustment

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

Dynamics NAV - Adjust Reporting Currency Dynamics NAV - Adjust 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.

Dynamics NAV - Change Additional Reporting Currency Dynamics NAV - Change 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:

Dynamics NAV - Norway Edition - General Ledger Entries Dynamics NAV - Norway Edition - General Ledger Entries

There is not 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:

Dynamics NAV - General Leder Entries in Excel Dynamics NAV - General Leder Entries in Excel

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:

Dynamics NAV - Norway Edition - Chart of Accounts with ACY Dynamics NAV - Norway Edition - Chart of Accounts with ACY

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:

Dynamics NAV - Norway Edition - Posted Sales Invoice Dynamics NAV - Norway Edition - Posted Sales Invoice

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

Dynamics NAV - Norway Edition - Posted Sales Invoice Statistics Dynamics NAV - Norway Edition - 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:

Dynamics NAV - Customer Ledger Entry 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:

Dynamics NAV - G/L Entries for Posted Sales Invoice Dynamics NAV - G/L Entries for Posted Sales Invoice

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.

Dynamics NAV - Adjust Exchange Rates 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:

Dynamics NAV - Norway Edition - Currency Exchange Rates EUR Dynamics NAV - Norway Edition - Currency Exchange Rates 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.

Dynamics NAV - Detailed Customer Ledger Entries 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:

Dynamics NAV - G/L Entries for Customer Currency Adjustment Dynamics NAV - G/L Entries for Customer Currency 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.

Dynamics NAV - Norway Edition - Currency Exchange Rates USD Dynamics NAV - Norway Edition - Currency Exchange Rates USD

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

Dynamics NAV - Norway Edition - Chart of Accounts, Adjusted Dynamics NAV - Norway Edition - Chart of Accounts, Adjusted

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:

Dynamics NAV - G/L Entries for Currency Adjustment Dynamics NAV - G/L Entries for Currency Adjustment

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

Contact

PaulsenConsult.com
4300 Jog Road #541824

Green Acres, FL 33454


Phone: +1 561 275-9519+1 561 275-9519

E-mail: Thomas@PaulsenConsult.com

Print Print | Sitemap
©PaulsenConsult.com Microsoft Dynamics NAV and Microsoft Navision are registered trade marks of Microsoft Corporation