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):

Company	Account description	Balance	Currency	FX rate	Balance in EUR
Domestic	Property plant equipment	100,00	EUR	1	100,00
Domestic	Cash	150,00	EUR	1	150,00
Domestic	Share capital	(125,00)	EUR	1	(125,00)
Domestic	Revenues	(50,00)	EUR	1	(50,00)
Domestic	Costs	30,00	EUR	1	30,00
Domestic	Short term liabilities	(105,00)	EUR	1	(105,00)
Foreign	Property plant equipment	100,00	USD	0,9	90,00
Foreign	Cash	150,00	USD	0,9	135,00
Foreign	Share capital	(125,00)	USD	0,9	(112,50)
Foreign	Revenues	(50,00)	USD	0,85	(42,50)
Foreign	Costs	30,00	USD	0,85	25,50
Foreign	Short term liabilities	(105,00)	USD	0,9	(94,50)

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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:

Row Labels	Domestic	Foreign	Grand Total
Property plant equipment	100,00	90,00	190,00
Cash	150,00	135,00	285,00
Share capital	-125,00	-112,50	-237,50
Revenues	-50,00	-42,50	-92,50
Costs	30,00	25,50	55,50
Short term liabilities	-105,00	-94,50	-199,50
BALANCE	0,00	1,00	1,00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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

Row Labels	Domestic	Foreign	Grand Total
Property plant equipment	100	90	190
Cash	150	135	285
Share capital	-125	-112,5	-237,5
Translation reserve	0	1	1
Revenues	-50	-42,5	-92,5
Costs	30	25,5	55,5
Short term liabilities	-105	-94,5	-199,5
Grand Total	0	0	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[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).