Input into different hierarchy level and display balances in one module with Hierarchy and COA dim

michael_park
michael_park
in Modeling

Hi,

I've been digging around all the resources but could not find any solution to my problem.

We are trying to build a legal hierarchy tree (ragged) with inputs into each entity in varying levels and then presenting those inputs in one module with 3 dimensions: legal entity hierarchy tree, chart of accounts, and months.

Thought I've got it working by using Formula as a summary method in the module, FINDITEM & PARENT functions but once the parent level is beyond 2 levels from the Child level, this won't work.

 

This is important when you have entities that don't roll up to a parent by summing 100% of a child and that parent entity has transactions on its own as well.

e.g 

Child 1 balance $100

Parent 1 owns 80% of Child 1 and has $10 transaction on its own.

Therefore Parent 1 should have a balance of $90 (80% of $100 and $10).

When you are using just normal roll-up sum this is impossible and when you change the summary method in a module to FORMULA it won't work with SUM or LOOKUP function.

 

Amounts entered in the Bottom Level 4 Child entities.

michael_park_0-1581676295719.png

 

Amounts entered in the Level 3 Parent entities.

 

michael_park_1-1581676305711.png

 

 

 

Amounts entered in the Level 2 Parent entities.

michael_park_2-1581676312661.png

 

 

Hope this is clear.

Thanks for your help in advance.

 

Tagged:

Answers

  • KBeltz
    KBeltz

    Hey Michael,

     

    Seems like a difficult case -- especially with LOOKUPS and SUMS not having the ability to have a formula.  I would suggest creating dummy list members at your L4 level that are for L3 and L2 inputs.  That way, the user is inputting everything on the "L4" level, even though some are inputs on L3 and L2.  

     

    You should be able to turn the summary method on "SUM" on the input line to see the aggregation up to L3 and L2. 

  • JaredDolich
    JaredDolich

    @michael_park 

    This is a common challenge and @KBeltz suggestion is right on.

    I've seen two approaches to this. The first is to read through @DavidSmith post on productionalizing lists. While the topic in general doesn't fit your use-case 100%, the important part of that post is when you create the parent only list. Also read @ChrisMullen answer to @emilydunn's question about ragged hierarchy calculations.

     

    The other approach is to structuralize your ragged list (yes, I made up that word - I think the correct term is balancing your hierarchy). You can watch this course 305 contributed by @emilydunn, @Bob-Bachynsky, and @rob_marshall. I think @ChrisMullen was involved with this too. Absolutely brilliant video and may give you some tips/tricks on how to work with that ragged list.

     

    Let us know what you decide to do or if you need more clarification. I'd be happy to work out an example for you if it helps.

     

     

  • michael_park
    michael_park

    Thanks, @JaredDolich

    Yes, I've gone through both articles and the course 305 before. Indeed helped me create balancing of ragged hierarchy.

     

    I've now worked it out my problem by producing a dummy child entity in the same tree as the parent and calculating the difference between the sum rolled up value against the imported actual balance for that entity. Then feed that difference into the dummy child.

     

    Thanks for your help though.

Categories