Total share in a company

[Vaclav Laga]

Hi, I am trying to calculate a total share in a companies for several interconnected companies. The simplified model is as follows

Company/Company	A	B	C
A	-	10%	10%
B	10%	-	10%
C	15%	20%	-

So the company A holds directly 10% in company B which holds 20% in company A. That makes further indirect holding for company A in Company C of 2% (10% x 20%). Total share of A in C is then 12%.

This is what I need to calculate for every company in a table (and there is more companies than in this model of course).

I am struggling to find a formula or google applicable VBA code. Do you know of any solution? Thanks, Vaclav.

 

[Yongle]

Your explanation contains an error
So the company A holds directly 10% in company B which holds 20% in company A. That makes further indirect holding for company A in Company C of 2% (10% x 20%). Total share of A in C is then 12%.
Should be ...
So the company A holds directly 10% in company B which holds 20% in company C. That makes further indirect holding for company A in Company C of 2% (10% x 20%). Total share of A in C is then 12%.

I am also confused by the numbers in your table. Am I reading this correctly?
Company A owns 10% of B and 10% of C
Company B owns 10% of A and 10% of C
Company C owns 15% of A and 20% of B


Are reciprocal share ownerships ignored ....
- company B owns 11% of C (10% directly and 10% X 10% of A's direct ownership indirectly )
- ignore the fact that C owns shares in A & B

 

[Vaclav Laga]

Hi, thank you for prompt response. You are correct, it should be: "So the company A holds directly 10% in company B which holds 20% in company C. That makes further indirect holding for company A in Company C of 2% (10% x 20%). Total share of A in C is then 12%" as you suggest.

The table should be read like this: The entities in columns hold shares in companies in rows and not the other way around. I.e. company A holds directly 15% in company C and not 10% as a row 1 would suggest.

Sorry for the mistake and ambiguity.

 

[Yongle]

Away today\tomorrow will update thread when back at PC later this week

 

[Peter_SSs]

You are correct, it should be: "So the company A holds directly 10% in company B which holds 20% in company C. That makes further indirect holding for company A in Company C of 2% (10% x 20%). Total share of A in C is then 12%"

Isn't that still incorrect?

If this is true ..

company A holds directly 15% in company C

.. then shouldn't A's total share of C be 15% + 2% = 17%?

 

[Vaclav Laga]

Isn't that still incorrect?

If this is true ..
.. then shouldn't A's total share of C be 15% + 2% = 17%?

ye, correct. Thx

 

[Yongle]

Here is a VBA solution for you which calculates the indirect holdings based on Sheet1
A new sheet is created for each company (each one almost identical to Sheet1 )
Formulas are include in the generated sheets so that it is possible to see how everything is calculated
Cell A1 contains the sheet name which is used in those formulas
Totals sheet needs to be added later - but I thought it better that you confirm the output is correct before we go any further

1. Example of results - sheet KLP

Book1
	A	B	C	D	E
1	KLP	ABC	DEF	GHI	KLP
2	ABC	0.00%	1.00%	1.00%	0.00%
3	DEF	1.50%	0.00%	1.00%	0.00%
4	GHI	2.25%	2.00%	0.00%	0.00%
5	KLP	0.00%	0.00%	0.00%	0.00%
KLP

Cell Formulas
Range	Formula
B2:E5	B2	=INDEX(Sheet1!$A$1:$E$5,MATCH(B$1,Sheet1!$A$1:$A$5,0),MATCH($A$1,Sheet1!$A$1:$E$1,0))*AND($A2<>$A$1,B$1<>$A$1)*Sheet1!B2

2. Explanation of results
In Sheet1 (below)
KLP owns 15% of ABC directly
ABC owns 10% of DEF directly
ABC owns 15% of GHI directly
Sheet KLP
cell B3 - KLP indirectly owns 15% X 10% of DEF = 1.5% via ABC
cell B4 - KLP indirectly owns 15% X 15% of GHI = 2.25% via ABC
etc

3. Data used

Book1
	A	B	C	D	E
1	Holding|Owner ->	ABC	DEF	GHI	KLP
2	ABC		10%	10%	15%
3	DEF	10%		10%	10%
4	GHI	15%	20%		10%
5	KLP	100%	20%	20%
Sheet1

4. Code
- put in a module like module 1

Option Explicit

Private Data As Range, Owner As Range, Holding As Range, DataValues As Range
Private Const aFormula = "=INDEX(Sheet1!DATA,MATCH(B$1,Sheet1!HOLDING,0),MATCH($A$1,Sheet1!OWNER,0))*AND($A2<>$A$1,B$1<>$A$1)*Sheet1!B2"
Private sh1 As Worksheet, c As Long, cel As Range


Sub Indirect()
'set variables
    Set Data = Sheets("Sheet1").Range("A1").CurrentRegion
    Set Owner = Data.Resize(1)
    Set Holding = Data.Resize(, 1)
    Set DataValues = Data.Offset(1, 1).Resize(Data.Rows.Count - 1, Data.Columns.Count - 1)
    Application.ScreenUpdating = False
'delete old sheets
    For c = 2 To Owner.Cells.Count
        DeleteSheet Owner.Cells(c).Text
    Next c
'create first sheet
    Set sh1 = Worksheets.Add(after:=Sheets(Sheets.Count))
    sh1.Name = Owner.Cells(2).Text
    Set cel = sh1.Cells(1, 1)
    Owner.Copy cel
    Holding.Copy cel
    cel = sh1.Name
    cel.Font.Color = vbRed
    With sh1.Range(DataValues.Address)
        .Formula = Replace(Replace(Replace(aFormula, "DATA", Data.Address), "OWNER", Owner.Address), "HOLDING", Holding.Address)
        .NumberFormat = "0.00%"
    End With
'create other sheets
    For c = 3 To Owner.Cells.Count
        sh1.Copy after:=Sheets(Sheets.Count)
        With Sheets(Sheets.Count)
            .Name = Owner.Cells(c).Text
            .Cells(1, 1) = .Name
        End With
    Next c
End Sub

Private Sub DeleteSheet(sh As String)
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(sh).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub

5. Testing
Test EXACTLY like this for first test and it should work first time for you
1 Create a NEW workbook
2 Copy sample data (see 3 above) into cells A1 to E5 in Sheet1
3 Place code (see 4 above) into a module
4 Run macro Indirect
5 Output is 4 new sheets named ABC, DEF , GHI, KLP

6. Feedback
Are the numbers what you expect?
Any other comments?

 

[Vaclav Laga]

Thank you, this is exactly what I needed.

 

[Yongle]

Thanks for the feedback
Let me know if you require further help