Something weird happened with one of our foreign currency Bank Recs. Base currency - USD, foreign currency Bank - CAD
Jan 16 - Fx rate 0.86
Information on the Bank Rec:
GL amount (USD) $351,551.28 and the G/L Balance in CAD $408,780.56 (351,551.28/0.86)
Everything is correct, and the Bank Rec is posted
Jan 20 - Fx rate 0.84
No transaction happened between Jan 16 and Jan 20
Information on the Bank Rec:
GL amount (USD) $351,551.28 and the G/L Balance in CAD $424,396.45 ($424,396.45/351,551.28 = 0.8283 ??)
Not sure why the CAD amount changed on the Bank Rec.
Now, the most weird part is after we run “Adjust Exchange Rate” batch (with blank start date), NAV posted debit to GL of the bank account, making the GL amount 356,493.06 . Now, if we $424,396.45/356,493.06, we get the rate for Jan 20, which was 0.84
This whole thing looks puzzling. I don’t understand why NAV:
Changed the CAD amount on the Jan 20 Bank Rec if no transaction happened between Jan 16 and 20
How come initial CAD amount was according to non-existent rate of 0.8283 and only after the currency adjustment the rate is 0.84
Why NAV posted Debit adjustment to GL when the base currency become dearer? On Jan 20 CAD worth less, so NAV should have posted Credit amount
Please note that Direct Posting is disabled. Thank you.
a list of bank ledger entries might be helpful to explain, but I think we can try without. At what rate an entry is posted is defined by the “Amount” and "Amount (LCY) " fields in the gen. journal line. This way you can enter the postings as they happened. There are limitations to this, though. For a rate <>1, a currency code must be given. And depending on the settings in the currency exchange rate, there are limitations which field can be modified. And you have no way of entering and posting a FCY only (no LCY amount) posting.
FCY amount changes with no posting
Well, that’s strange. Any FCY amount change requires additional entries. A list of the bank ledger entries would help for further analysis.
Adjust Exchange Rate is posting in the opposite direction than expected
For bank accounts, the adjustment at date is a LCY only posting by adjusting the exchange rate of the balance. As you explained, the resulting rate at Jan 20 was 0.8283, not 0.86. To get to 0.84,the system posts a credit in LCY. That’s like it should be.
For me the only question remains if there are really no additional entries between Jan 16 and Jan 20. And if that’s the case, if the amounts in the backup from the entry date (if available) are the same in the entries.
I haven’t enclosed the list of Bank Ledger Entries as there is only one entry between Jan 16 and Jan 20 - adjustment posted after running the “Adjust Exchange Rate” batch, posted on Jan 20.
Understood, so system increased the GL to match the FCY amount. But the problem is that FCY amount should have stayed the same, why it system changed it I can’t understand. Now there is a difference on the Bank Rec and we can’t post it.
Jens, what would you suggest to do to locate the cause of the issue? Thank you.
First thing to say is that understanding FOREX adjustment entries is a little bit difficult due to the method NAV uses. EVERY still open entry is processed every time the batch job is run, and the amount posted is in fact an ongoing delta from previous - if any - adjustments.
I understood that nevertheless you’re in Canada, the system’s LCY = USD, but CAD is set up as (one of) foreign currencies, is it true?
How do you get FOREX rates into NAV and at what frequency? With the above-mentioned setup you can’t use simple fetch from Bank of Canada - in the process reversion is required as BoC gives rates against CAD, but you need the reverse value for USD rate and recalc thru USD for all other currencies.
If done manually, it’s a nightmare and highly possible source of errors, have you checked the rates entered in Currency Rates?
PS Especially funny it becomes with CAD-USD pair - I recall I was designing an autoimport of FOREX rates for a Canadian company at the time when CAD-USD fluctuated around 1:1, that is, one day CAD was “heavier” than USD, the other - vice versa…
PPS Again from my previous experience with Canadian NAV users - does your system have LCY symbol USD in ONE & ONLY ONE place, that is, GL Setup / LCY field and nowhere else, especially in Currencies table? Do you leave BLANK Currency field everywhere in system, when LCY amount is entered?
If not, then NAV goes completely nuts with multicurrency transactions…
Thanks for your detailed response, Modris. It gave me lots if insight into NAV multi-currency workings. You know all the “behind-the-scenes” intricacies
Actually, Jens kindly contacted me on this issue and we discovered that the problem was a variance between GL and Bank Sub-ledger coming from opening balance entries. Although issue didn’t show up from the start, possibly because of little or no currency fluctuations before, for the Jan 20 Bank Rec it suddenly showed up.
After the Bank Sub-ledger has been matched to the GL, the difference disappeared!
