Dynamics Community Forum Thread Details

YTD and Calculated Data in the same column? Is it possible?

Hello,

I am trying to create a balance sheet.

I am consolidating 9 companies with 4 different currencies.

When we prepare our P&L, we sum each monthly column, so that each column will convert to GBP at the monthly fx rate. Simply using YTD for "periods covered" does not give us each month at the monthly rate, it converts all months at the current monthly rate.

When I create the balance sheet, I am using the YTD Periods Covered.

This works great until I get to Current Year Net Income.

When the system calculates Current Year Net Income in the same column as all other balance sheet numbers, it uses the YTD number which would be the current month fx rate for all activity and thus the Current Year Net Income does not tie to the PL YTD Net Income.

I want to have 12 columns, one for each month, so that Current Net Income is converted at each monthly rate, and then sum the months together to get YTD numbers, but I also want the YTD balance sheet accounts all in the same column.

I tried using column restrictions on the row format, however, it seems that the figures are restricted from printing to the page, however, they are still getting included in the formulas.

Any help would be appreciated.

Maybe our situation is unique and it has more to do with the set up of how we are calculating fx on YTD PL. If we used a different method, we would probably not have this issue on the balance sheet, but this is what we got.

Thanks

Hi achamberlain,

I would expect that your P&L net income does not tie to the one that you see in your BS report if foreign currencies are involved.

That is because P&L data is usually translated into GBP using average or monthly rates in your case while BS accounts are translated using historical rates (for equity) or the closing rate (for everything else).

Because you use different rates to convert your numbers into GBP, you will always get a translation gain/loss that you can e.g. show in your equity section.

The financial reporter has actually a function that allows you to balance assets and liabilities/equity and this functionality could be used here because your profit/loss does not consist only of the economic profit or loss that your entities created but also of the 'unrealized' translation gain/loss, which is the result of your GBP translation.

Does that make sense to you?

Best regards,

Ludwig

Quick Links