In my last series of posts, I wrote about NAV currency setup, entering foreign currency sales invoices and how adjust currency amounts can be run at period end to create gain loss entries.
In that last post, I promised to show you a sample report that would allow you to see the gain loss before you actually post it.
And here it is.
Aged Accounts Receivable in NAV
The Aged Accounts Receivable report in NAV has the option to print results in the customer’s currency:
If we run this report for Beef House (49525252), and select Print Detail, we see the following:
We can see the transaction in euros as well as a report total in USD. Which is all good.
However, it’s important to note: The USD total reflects the current rates as of the cut off date for the report whether or not we’ve actually posted gains and losses to the ledger.
How do I know this? Let’s start by looking at the rates. (I had to add a rate to the system to create the example.) Checking the exchange rates, we can see that the system should choose the 1.4 rate because that’s the latest rate before the cut off date.
(If you do the math, you’ll see that indeed 30315.02 /21,653.58 = 1.4.)
However, when we look at the customer ledger entries, we something else.
For example, let’s take the first invoice (103005) and look it up in the Customer Ledger. If we multiply 1,245.92 x 1.4 we’d expect to see 1744.33.
However, if we look at the local currency amount (marked misleadingly with a $ sign) we see that it’s 1541.20.
Which, if you do the math, is a rate of 1.237, which is the rate as of 1/1/2016.
If we choose Detail Customer Ledger Entries, we would find there’s no revaluation for the transaction. (For more on this, see our post Understanding NAV Currency: Part 3 Month End Revaluation.)
Red Three Open AR Unrealized Gain/Loss Report
As part of my forthcoming set of free SSRS reports for NAV, I’ve created a report that shows both the current value (which should tie to the ledger), the prospective revaluation, and the prospective gain and loss.
For example, here’s a small screenshot for Beef House:
You can see the current calculation (1.237) and the new at 1.4 (which ties to the NAV report).
I’ve built this report using my shared database view.
I’m in the process of putting all my code together, but I’ve also included it below.
Let me know if you’d like to have the stored procedures and/or the SSRS report when they’re ready. I’m hoping to have them put together by mid-December.
