Financial reporter YTD Currency translation

(0) Share

Report

Posted on by Frank Hamelly | MVP...

46,633 Moderator

I'm trying to confirm that this is how FR (MR) calculates a YTD, translated rollup of periodic results - I read an article that stated MR calculates the periodic balances month-by-month and then sums them for the YTD balance, but not sure if that's how it actually works. Does anyone here know if that's how YTD translated balances are derived? Thanks in advance.

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Frank,

This docs site specifies the formulas used for foreign currency translations.

docs.microsoft.com/.../financial-reporting-currency-capability

Best regards,

Ludwig

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,633 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Ludwig. I had read that article but it doesn't state explicitly how a YTD column is calculated. Say I'm 6 months through my fiscal year and I'm publishing a Profit & Loss statement that's been translated from GBP to USD. I believe the way the YTD column is calculated, MR takes Month1Results x Month1 rate + Month2Results x Month2 rate + Month3Results x Month 3 rate, etc. . . . to arrive at the 6 months to date total. The reason I'm trying to find something definitive on this is to convince a client that they don't need to configure a YTD average exchange rate every month because MR 'calculates' this for them. We may have to prove it to them via testing.

Was this reply helpful? Yes No
Verified answer

EDJJR Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello Frank,

A YTD column should translate at the current rate, if that account is set to "Current" (GL | Chart of Accounts | Main Account | Financial Reporting).

A YTD column will also include any balances from the prior year, if it is a Balance Sheet account.

Now if you you using AVG then a YTD column is going to take the AVG translated amount, from each of the prior periods and then translate them and sum them together.

Example:

Period 1 Period 2 Period 3 Period 4 Period 4 YTD

50.00 35.00 40.00 65.00 $190.00

If you still have questions/issues it might be better to open a support ticket as we can then stream into your data and take a look.

Thank you,

Erik Johnson

Microsoft

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,633 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Erik! This is exactly what I was looking for. No need for a ticket - I just needed confirmation of my understanding/assumption as to how the YTD column worked.

Was this reply helpful? Yes No
Suggested answer

saurabh bharti 15,059 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Frank,

If you need your YTD column to be calculated same as you mentioned YTD month -1 * rate of month -1 + YTD month -2 * rate of month -2 .... because every month rate can be different

in that case you need to create separate columns for each month and create final calculated column for YTD and keep individual month column as non printing

This is another alternative if you want exact rate of each month to be considered for YTD

Was this reply helpful? Yes No
Frank Hamelly | MVP... 46,633 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

And for those who are interested, it works as Erik said - I set this up in DEMF in Contoso, exported to Excel and ran some proof calculations

Was this reply helpful? Yes No