Child to Parent Ratio based on Planned Input Number

PrevContributor
PrevContributor
in Modeling

Hello I have a set of Actuals(FTE) by Level 3 rolling up to a Level 2 in turn rolling up to a Level 1. I need a formula that calculates the ratio to its immediate Parent first . I also am looking for a mechanism where someone enters a new value at level 2 (immediate parent) the new planned numbers for service level 3 should automatically be distributed based on the original ratio . Thanks

Tagged:

Answers

  • Jaakko
    Jaakko
    Hi Soma,

    Here is one example how you could do it. The example will use the latest FTE ratio for all forecast months. I assume you have versions and time dimension in your module. Hierarchy lists are called "Level 2" and "Level 3". 

    For this solution you will need a level 2 -formatted property in Level 3 -list for a lookup. Let's call it 'Parent at Level 2'.  This is because parent-function cannot be used directly together with lookup formula. Considering model size, it is usually better to have it in a property compared to adding a new line item to the module. Use formula like this for the property:  PARENT(ITEM('Level 3'))

    Line items used:
    Line item 1: 'FTE'
    - This line item will have actual FTE's in the actual periods and calculated FTE's in the forecast. All at Level 3.
    - Bring actual FTE's to cells in actual version with a process, by inputting them or by using a formula. If you don't use formula, use only the part after "ELSE" and change formula scope to "current" or "all except actual":
    IF ISACTUALVERSION() THEN ELSE FTE Input[LOOKUP: 'Level 3'.'Parent  at Level 2'] * LAG('% of Level 2', 1, 0)

    Line item 2: '% of Level 2'
    - Here you will calculate the ratio in actuals. Formula above will copy/lag it to all forecast periods:
    FTE / FTE[LOOKUP: 'Level 3'.'Parent at Level 2']

    Line item 3: FTE Input
    - This line item you will use as an input line item. Make sure to apply it to "level 2" list instead of level 3.
    - If you want to add actuals also for this line item, change formula scope to actual version and add a formula for actuals.

    I hope this helps you!

    Br,
    Jaakko

Categories