Translation reserve - balance sheet

Kresimir

New Member
Joined
Aug 9, 2017
Messages
4
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,661
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

New Member
Joined
Aug 9, 2017
Messages
4
Maybe this will clearup my question:

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

CompanyAccount descriptionBalanceCurrencyFX rateBalance in EUR
DomesticProperty plant equipment100,00EUR1100,00
DomesticCash150,00EUR1150,00
DomesticShare capital(125,00)EUR1(125,00)
DomesticRevenues(50,00)EUR1(50,00)
DomesticCosts30,00EUR130,00
DomesticShort term liabilities(105,00)EUR1(105,00)
ForeignProperty plant equipment100,00USD0,990,00
ForeignCash150,00USD0,9135,00
ForeignShare capital(125,00)USD0,9(112,50)
ForeignRevenues(50,00)USD0,85(42,50)
ForeignCosts30,00USD0,8525,50
ForeignShort term liabilities(105,00)USD0,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 LabelsDomesticForeignGrand Total
Property plant equipment100,0090,00190,00
Cash150,00135,00285,00
Share capital-125,00-112,50-237,50
Revenues-50,00-42,50-92,50
Costs30,0025,5055,50
Short term liabilities-105,00-94,50-199,50
BALANCE0,001,001,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).
 

Kresimir

New Member
Joined
Aug 9, 2017
Messages
4
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 LabelsDomesticForeignGrand Total
Property plant equipment10090190
Cash150135285
Share capital-125-112,5-237,5
Translation reserve011
Revenues-50-42,5-92,5
Costs3025,555,5
Short term liabilities-105-94,5-199,5
Grand Total000

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

Kresimir

New Member
Joined
Aug 9, 2017
Messages
4
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,661
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).
 

Forum statistics

Threads
1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top