Child to Parent Ratio based on Planned Input Number

PrevContributor

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

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