Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1 Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
The most common questions we receive related to financial multicurrency revaluations are associated with unexpected average exchange rates during the financal revaluation process and how the system calculates the realized gain or loss.
Average Exchange Rate
The first thing to know is that the average exchange rate is a simple calculation that is comparing the functional and originating balance for any given account and currency combination over a specific period of time. Because this is just a quick calculation, differences in exchange rate between transaction can cause the average exchange rate to look “off” even though it is correct.
The average exchange rate as seen on the revaluation report and the multicurrency summary inquiry window. is INFORMATIONAL only. It can be an indication that a revaluation is needed or there is a potential issue with a transaction, but the value calculated is almost always correct based off the data in the tables.
The example below shows how a negative exchange rate may be encountered. It also illustrates how the originating balance can be positive while the functional balance is negative or vice versa. Please note normally there isn’t this much variance in exchange rates, we are just using a big difference in exchange rate to “speed up” what can happen across several transactions with smaller differences in exchange rates.
There are two transactions in this hypothetical account, multiple exchange rates are used, and the accounts typical balance is credit. In this example, one of the transactions is a debit transaction and the other is a credit transaction. We are using a multiply rate calculation method.
In the above example there is an average exchange rate of -0.4 however the exchange rates on the transactions are not even close to that. Here is how GP came up with the (seemingly erroneous) average exchange rate (which is accurate):
The realized gain\ loss during revaluation uses much the same logic that is used to get to the average exchange rate. We get our originating balance and from there we multiply this by the rate being used for the revaluation to get our “new” functional balance and the difference between the old and new functional balance is our gain\loss on the revaluation.
Let’s use the same set of transactions as before to see what the gain or loss would be if we revalue the account at a rate of 1.5.
What GP did to get to our gain\loss is the below.
How to calculate yourself
So now that we covered how GP gets to the average exchange rate and gain\loss during a revaluation, how can this help you? With a little setup, you can back into the calculations used by GP yourself to verify data.
select JRNENTRY,TRXDATE,CURNCYID,XCHGRATE,DEBITAMT,CRDTAMNT,ORDBTAMT,ORCRDAMT, REFRENCE,DSCRIPTN,SOURCDOC,ORCTRNUM,ORDOCNUM
from GL20000 where
ACTINDX = (select ACTINDX from GL00105 where ACTNUMST='XXXX')
order by TRXDATE
And there we have it, you have just backed into the average exchange rate as well as the gain\loss calculation during a revaluation. You will notice there are a few columns that I included in our script which did not get used at all during this process. What are those for? Well if we have a bad record, we want to be able to know what it was so the additional columns (journal entry, reference , description, source doc, original control number (if it comes form a submodule this should be populated) and original document number (again should be populated if it came from a sub module) can be used to dig in further.
There is also one important note on something you may see in the results. Revaluation transactions will ONLY have a functional value but will show as our currency, this is to be expected (since a revaluation is only adjusting our functional values and not our originating values).
As always, if you come across any issues during revaluations and need assistance, all of us at Microsoft GP support are more than happy to assist. Thank you all very much for your time!
Business Applications communities