Management Reporter currency translation using the Average translation type

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.

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.

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