In GP, you can set up the average rate to either do a simple average or a weighted average.  The examples that follow explains how the two different methods for calculating an average rate works.

 

 

Currency translation using Rate Average

Account is set up using the Average currency translation type.

 

In the Multicurrency Setup window, the average calculation method is set to Rate Average. This method will calculate the average as the sum of the exchange rates, divided by the number of rates.


 

The rates entered for the average exchange table. Using the Rate Average method, the average should be:

Sum of rates/number of rates

P11 – 1.2 + 1.3 = 2.5/2 = 1.25

P12 – 1.4 + 1.5 = 2.9/2 = 1.45

P1 – 1.45 + 1.55 + 1.6 = 4.6/3 = 1.53

P2 – 1.35 + 1.45 + 1.5 = 4.3/3 = 1.43

P3 – 1.55 + 1.6 = 3.15/2 = 1.575

  

In MR, you have a column definition with a column for functional and translated currencies, for each period. There is also a beginning balance (Columns F and G) and YTD (Columns O and P).

 

 

 

The balance of each period is converted at the average rate for that period.

Period / Functional amount

Average rate

Translated amount

P11

10.00

1.25

12.50

P12

20.00

1.45

29.00

P1

200.00

1.53

306.67

P2

100.00

1.43

143.33

P3

300.00

1.575

472.50

 

The Beginning Balance is the total of the previous period, translated at their average rate.

P11 + P12 = BB

12.50 + 29.00 = 41.50

 

 

The YTD amount is the total of the periods, translated at the average rate for that period, plus the Beginning Balance.

BB + P1 + P2 +P3

41.50 + 306.67 + 143.33 + 472.50 = 964.00

 

 

 

 

 

Currency translation using Rate Days Average

 

In the Multicurrency Setup window, the average calculation method is set to Rate Days Average. This is a weighted average. The average for this is calculated as (Exchange rate * Days in effect) / Days in period.

 

Using the same rate table, the averages would be calculated like this:

(Rate*days in effect)/days in period

P11 – (1.2 * 15) + (1.3 * 15) / 30 = 1.25

P12 – (1.4 * 14) + (1.5 * 17) / 31 = 1.4548

P1 – (1.45 * 10) + (1.55 * 6) + (1.6 * 15) = 1.5419

P2 – (1.35 * 11) + (1.45 * 9) + (1.5 * 8) = 1.425

P3 – (1.55 * 15) + (1.6 * 16) = 1.575

 

 

  

 

 

The balance of each period is converted at the weighted average rate for that period.

Period/Functional amount

Weighted Average

Translated amount

P11

10.00

1.25

12.50

P12

20.00

1.4548

29.10

P1

200.00

1.5419

308.39

P2

100.00

1.425

142.50

P3

300.00

1.575

472.74

 

The Beginning Balance is the total of the previous period, translated at their weighted average rate.

P11 + P12 = BB

12.50 + 29.10 = 41.60

 

 

The YTD amount is the total of the periods, translated at the average rate for that period, plus the Beginning Balance.

BB + P1 + P2 +P3

41.60 + 308.39 + 142.50 + 472.74 = 965.23

 

Thanks,

Greg Byer

Microsoft Dynamics