Why is my average exchange rate not what I expected in Dynamics GP and how did GP calculate the realized gain or loss of an account during financial revaluation?

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

1. GP subtotaled the credits and debits for both the originating and functional transactions.
2. GP determined the originating and functional balances by subtracting the debits from the credits since this is a credit balance account.
3. To determine the Average Exchange Rate: GP divides the functional balance by the originating balance to come up with -.04 (since we are a multiple rate).

Realized Gain\Loss

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.

1. GP subtotaled the credits and debits for both the originating and functional sides.
2. GP then got our originating and functional balances by subtracting our debits from our credits since we are a credit balance account.
3. GP then multiplied our originating balance by the exchange rate being used in the revaluation (since we are using a multiple rate in our example) to get our new functional balance of 150.
4. Finally, we get our gain\loss by comparing the original functional total to the new functional total.
   1. Our original functional total was -40 and our new functional total is 150 which gives us a gain of 190.

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.

• We need to know if the account we are investigating is revaluing based of net change or period balance.
  1. If we are revaluing by period balance, every transaction for the account currency combination (including the BBF!) up to the end of the period being revalued is included in the calculation.
  2. If we are revaluing by net change ONLY transactions in the period we are revaluing are considered.
  3. Go to Cards>Financials>Account Currencies.
  4. Select our account.
  5. Review and take note of if the account is being revalued by net change or period balance.
  6. It’s also important to note what “post results to” is set to as well. If we are posting to account, we will see revaluation records against our account in the results assuming a revaluation has been ran for the account. These are expected but since they only have a functional value, often they are mistaken as damaged transactions (which they are definitely not!)                                                                                                                                                                                                 

• We need to know what period is being revalued (this is from the revaluation options in Tools>Routines>Financials>Revaluation).

• Next, we need to verify our target dates for the revaluation.
  1. Go to Tools>Setup>Company>Fiscal Periods.
  2. Select the year needed.
  3. If we are checking on an account that is revaluing based off period balance, we just need the end date for the period we are revaluing.
  4. If we are checking on an account that is revaluing based off net change, we need the start and end date for the period we are revaluing.

• Now that we know our target date, we can capture the data we need for review. This can be done with smartlist or SQL. Since some accounts will have lots of transactions, the below will use the SQL method however a smartlist setup to pull the same information can work just as well if the account does not have many transactions.
  1. In the below script, replace XXXX with our account number string. This is the full account number with separators. For example, ‘000-1100-00’.
  2. In the below script, replace YYYY with the year we are reviewing.
  3. In the below script, replace ZZZZ with the currency we are reviewing. For example, ‘Z-US$’
  4. If you like, you could add in a restriction on dates as well (I just always “chop off” the transactions we don’t need in excel).
  5. Run the query.

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')
and OPENYEAR='YYYY'
and CURNCYID='ZZZZ'
order by TRXDATE

• Now that we have our data, lets get it into Excel.
  1. In the results, copy everything.
     1. In the results pane there is a the little gray box in the upper left by the column and rows , if you left click (to select everything) and then right click on the little gray box you can select “Copy with headers” to get everything.
  2. Paste these results into Excel and it should put everything into its own cells nicely for you.
  3. Next right click on the TRXDATE column and select format cells.
  4. Select a date format of your choosing.
  5. Next Delete the rows that are outside of the range we are investigating (the dates from step 3).
     1. If we are investigating an account that revalues by period balance, every transaction AFTER the final day of the period that we are revaluing should be deleted from the spreadsheet.
     2. If we are investigating an account that revalues by net change, every transaction that falls outside of the period we are revaluing should be deleted from the spreadsheet.
     3. If the revaluation we are investigating has already been posted and we are posting the revaluation to the account itself (Account currencies window), you will want to delete the one specific revaluation record as well.
     4. The transactions will be in date order so you can delete all the needed rows in one or two blocks.

• Now that our data is prepared, we can finally put in our equations that will get us our results.
  1. Scroll to the bottom of the spreadsheet and in column E, enter the equation below, replacing XXX with the last row number that has data.
     1. the calculation is =SUM(E2:EXXX)                                                                     
  2. Repeat for rows F,G,H (you can just copy the equation over from cell E and it should change the equation for you).
     1. This is our subtotals.
  3. Next, we need to get our Functional and Originating balances.
     1. If we are a debit balance account, subtract our credit subtotals from our debit subtotals. =sum(EXXX-FXXX) for functional and =SUM(GXXX-HXXX) for originating.
     2. If we are a credit balance account, subtract our debit subtotals from our credit subtotals. =sum(FXXX-EXXXX) for functional and =SUM(HXXX-GXXX) for originating.
     3. As an example, with a debit balance account.                                                  
     4. You will want to get the balance for both functional and originating.
  4. Now that we have our balances, we can get our average exchange rate.
     1. If we are a multiple rate, we will divide our functional balance by originating balance to get the average exchange rate.
     2. If we are a divide rate, we will divide our originating balance by our functional balance to get our average exchange rate.
     3. In the below example, I am using a divide rate.                                             
  5. Finally let’s see what a revaluation at a specific rate would show.
     1. To calculate what a realized gain\loss should be need to multiple or divide (depending if we are using a multiple or divide rate) our originating balance by the revaluation rate. To get our new functional balance. In this example I am revaluing with a 1.2 divide exchange rate which gives me a revalued amount of 508.33.
     2. The difference between the old functional balance and the new functional balance we just calculated is our gain and loss.
     3. In the case of the above, I wind up at a loss of 43.8067 (or 43.81 once rounded).

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!