Translation reserve - balance sheet

[Kresimir]

Hi


my problem is Translation reserve in balance sheet and how to put it in powerpivot.

I am consolidating a group of 10 subsidiaries and some have different functional currencies. I need to translate the balance sheet accounts to FX rate at the last day of the month and the P&L accounts to average FX rate for that month and this is not a problem.

Problem is the difference which arose for using this two FX rates and my balance sheet don't balance. I can't find a way to put this difference as a Translation reserve.

Please could You help


Thanks in advance

 

[footoo]

It's not clear whether your question relates to the accounting treatment/presentation of the translation difference, or to the calculation of the difference.

If the latter, then based on :
(a) BOM P&L balance @ last month's BS rate
(b) Profit or loss for the month @ average rate for the month
(c) EOM P&L balance @ this month's BS rate

Then the translation surplus (deficit) will be : (c)-(a)-(b)

 

[Kresimir]

Maybe this will clearup my question:

Here are trial balances for two companies (Domestic and Foreign):

[TABLE="width: 504"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]Account description[/TD]
[TD]Balance[/TD]
[TD]Currency[/TD]
[TD]FX rate[/TD]
[TD]Balance in EUR[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Property plant equipment[/TD]
[TD="align: right"]100,00[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100,00[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Cash[/TD]
[TD="align: right"]150,00[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]150,00[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Share capital[/TD]
[TD="align: right"](125,00)[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"](125,00)[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Revenues[/TD]
[TD="align: right"](50,00)[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"](50,00)[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Costs[/TD]
[TD="align: right"]30,00[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30,00[/TD]
[/TR]
[TR]
[TD]Domestic[/TD]
[TD]Short term liabilities[/TD]
[TD="align: right"](105,00)[/TD]
[TD]EUR[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"](105,00)[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Property plant equipment[/TD]
[TD="align: right"]100,00[/TD]
[TD]USD[/TD]
[TD="align: right"]0,9[/TD]
[TD="align: right"]90,00[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Cash[/TD]
[TD="align: right"]150,00[/TD]
[TD]USD[/TD]
[TD="align: right"]0,9[/TD]
[TD="align: right"]135,00[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Share capital[/TD]
[TD="align: right"](125,00)[/TD]
[TD]USD[/TD]
[TD="align: right"]0,9[/TD]
[TD="align: right"](112,50)[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Revenues[/TD]
[TD="align: right"](50,00)[/TD]
[TD]USD[/TD]
[TD="align: right"]0,85[/TD]
[TD="align: right"](42,50)[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Costs[/TD]
[TD="align: right"]30,00[/TD]
[TD]USD[/TD]
[TD="align: right"]0,85[/TD]
[TD="align: right"]25,50[/TD]
[/TR]
[TR]
[TD]Foreign[/TD]
[TD]Short term liabilities[/TD]
[TD="align: right"](105,00)[/TD]
[TD]USD[/TD]
[TD="align: right"]0,9[/TD]
[TD="align: right"](94,50)[/TD]
[/TR]
</tbody>[/TABLE]

For example the FX rate for closing balance at year end is 0,9 EUR/ 1 USD and 0,85 EUR / 1 USD as average which is used for P&L. When I translate this to my domestic currency (EUR) I get a difference of 1 EUR due to using of different FX rates:

[TABLE="width: 400"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Domestic[/TD]
[TD]Foreign[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Property plant equipment[/TD]
[TD="align: right"]100,00[/TD]
[TD="align: right"]90,00[/TD]
[TD="align: right"]190,00[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD="align: right"]150,00[/TD]
[TD="align: right"]135,00[/TD]
[TD="align: right"]285,00[/TD]
[/TR]
[TR]
[TD]Share capital[/TD]
[TD="align: right"]-125,00[/TD]
[TD="align: right"]-112,50[/TD]
[TD="align: right"]-237,50[/TD]
[/TR]
[TR]
[TD]Revenues[/TD]
[TD="align: right"]-50,00[/TD]
[TD="align: right"]-42,50[/TD]
[TD="align: right"]-92,50[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD="align: right"]30,00[/TD]
[TD="align: right"]25,50[/TD]
[TD="align: right"]55,50[/TD]
[/TR]
[TR]
[TD]Short term liabilities[/TD]
[TD="align: right"]-105,00[/TD]
[TD="align: right"]-94,50[/TD]
[TD="align: right"]-199,50[/TD]
[/TR]
[TR]
[TD]BALANCE[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]1,00[/TD]
[TD="align: right"]1,00[/TD]
[/TR]
</tbody>[/TABLE]

And to balance I need to put this 1 EUR as a Translation reserve (in Equity).

 

[footoo]

And to balance I need to put this 1 EUR as a Translation reserve (in Equity).

So what is your question?

 

[Kresimir]

How to make (if it is possible) through powerpivot this line so it can automatically calculate (not to manually put the data in). Some kind of calculation (adjustment)... And then the pivot table would look like this

[TABLE="width: 400"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Domestic[/TD]
[TD]Foreign[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]Property plant equipment[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]285[/TD]
[/TR]
[TR]
[TD]Share capital[/TD]
[TD="align: right"]-125[/TD]
[TD="align: right"]-112,5[/TD]
[TD="align: right"]-237,5[/TD]
[/TR]
[TR]
[TD]Translation reserve[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Revenues[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"]-42,5[/TD]
[TD="align: right"]-92,5[/TD]
[/TR]
[TR]
[TD]Costs[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25,5[/TD]
[TD="align: right"]55,5[/TD]
[/TR]
[TR]
[TD]Short term liabilities[/TD]
[TD="align: right"]-105[/TD]
[TD="align: right"]-94,5[/TD]
[TD="align: right"]-199,5[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

Add a row to the trial balance for the "Translation reserve" with a formula that returns the translation difference.

Alternatively :
http://www.contextures.com/excel-pivot-table-calculated-field.html

 

[Kresimir]

I wrote that I don't won't to put the data manually as the FX calculation is performed in Data model through powerpivot but if it is the only solution could you tell me:

1. Do you think to put it in pivot as a calculation field - what would be the formula?
2. Or in trial balance? How?

 

[footoo]

I have never used Power Pivot, so will leave to someone else to respond.
Can you not just change the TB format to include a row for "Translation reserve"? The calculation would just be the balancing amount (debits less credits).