How to calculate intercompany ownership% calculation for a group companies?

Aswinraj
Aswinraj
in Academy Discussions

Problem Statement:

I have a requirement where I have to calculate the indirect ownership% of the entities based on the input of direct ownership%

Example:

The direct ownership% of the entities

  • 'Entity 1' is the ultimate parent
  • 'Entity 2' is held by 'Entity 1' by 50%
  • 'Entity 3' is held by 'Entity 1' by 100%
  • 'Entity 4' is held by 'Entity 2' by 50% and 'Entity 3' by 50%
  • 'Entity 5' is held by 'Entity 3' by 100%

The outcome of the Indirect Ownership% should be

  • 'Entity 2' is held indirectly by 'Entity 1' by 50% through 'Entity 3'
  • 'Entity 4' is held indirectly by 'Entity A' by 100% through 3 possible ways
    • Entity 1 to Entity 2 to Entity 4 - 25%
    • Entity 1 to Entity 3 to Entity 4 - 50%
    • Entity 1 to Entity 3 to Entity 2 to Entity 4 - 25%
  • 'Entity 5' is held by 'Entity 1' indirectly by 100% through 'Entity 3'

Also, attaching sample hierarchy flow for your reference and share your thoughts/approaches to this requirement.

image.png

Regards,

Aswinraj Ramesh

Answers

  • TristanS
    TristanS

    @Aswinraj I have a theoretical solution for this. The logic is pretty much how a bill of materials explosion in Supply Chain would work

    Step 1: Define the direct ownership of each of the companies.

    image.png

    Step 2. Explode the defined relationship in Step 1 down to its most granular level (as per screenshot below)

    image.png

    Step 3. Multiply the ownership % in each ownership branch excluding where Entity 1 was is a direct owner in step 1 (as per screenshot below)

    image.png

    Step 4. Add the totals of branches against the entity you want to determine indirect ownership for (last column in screenshot below)

    image.png

    So that's the theoretical approach. How you implement it is another matter. Some people I know implemented it outside Anaplan and some have built specifically tailored (non generic) logic. I managed to build it in Anaplan implementing a kind of for loop for which you'd have to hire me lol if you wanted me to tell you how it works.

Categories