Child to Parent Ratio based on Planned Input Number

Previous Contributor

Child to Parent Ratio based on Planned Input Number

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

Frequent Contributor

RE: Child to Parent Ratio based on Planned Input Number

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