Bring line item value from parent to child, distributed equally, from one module to another.

ddamerji
ddamerji
in Modeling

Hello, 

I have a list hierarchy that looks something like this: 

  • Department A (Parent)
    • Office 1 (Child)
    • Office 2
    • Office 3
  • Department B
    • Office 4
    • Office 5
  • Department C
    • Office 6

I have now created two modules, one where Department list is a dimension (Module Department) and one where Office list is a dimension (Module Office). 

In the Department module I have a line item called "Other revenues", which is basically an input from the end-user on that level. What I now would like to do is to bring that line item value from Module Department into a line item in the Office module by either:

1. Distributing it equally, or

2. Distributing it based on another line item in the Office Module (for example Head counts). 

 

If we go for option 1 (equally distributed), then it should look something like this: 

 

  • Department A (Input: 9Millions)
    • Office 1 (3 Millions)
    • Office 2 (3 Millions)
    • Office 3 (3 Millions)
  • Department B (Input: 10 Millions)
    • Office 4 (5 Millions)
    • Office 5 (5 Millions)
  • Department C (Input: 3 Millions)
    • Office 6 (3 Millions)

Thanks in advance! 

 

Best regards,

Daniel

Tagged:

Best Answer

  • M.Kierepka
    M.Kierepka
    Answer ✓

    Hi @ddamerji,

    I assume you have at Office level a line tiem with parent of office (so department) in some SYS module ("SYS Office.Department").
    Firstly, create your driver line item (dimensioned as Office), let's name it "Base". In option 1. pin in to the right source line item (like Headcount), for option 1. just put formula as "1". In both cases set the summary to Sum. Then your result (again dimensioned by Office) line item needs formula as "Base * Department.Other revenues[LOOKUP:SYS Office.Department] / Base[LOOKUP:SYS Office.Department]".

    Example:

    MKierepka_0-1648129961700.png

     

Answers

  • ddamerji
    ddamerji

    Hi @M.Kierepka,

     

    Thanks for your solution, it worked very well! 

     

    One other note: I just stumpled upon the breakback functionality in Anaplan. In the case of option 1, where we would just simply distribute it equally, would enabling breakback be a valid solution, or could that lead to other problems?

     

    The only difference I see, is that now the input to the line item "other revenues" would be done in the Office module, however on the department level (since breakback is enabled), and in the Department module the line item "Other revenues" would just be a reference to the line item in the Office module. 

     

    Best regards,

    Daniel 

     

  • M.Kierepka
    M.Kierepka

    Hi,

    Yes, breakback might be a valid solution. But the right choice greatly depends on some edge cases:

    • What do you want to happen if a new Office gets added under some Department? In breakback, it will have value equal to 0, and other items won't reallocate. If user want to split evenly again, then need to erase value at Dep level and put it back (there is a risk that they forget about erasing and just update new value because they want to raise it - then you have a problem as it will be split only to old ones). In non-breakback solution I proposed, when a new Office gets added, it's automatically included in split. So the latter is good if you want to have always live split, not so good to hold past data that should remain unchanged.
    • Flexibility: Breakback technically allows for quick and easy adjustment at lower level, however the initial split is always equal. On the other hand, the solution I described allows you to change the split to not equal, and even apply different criteria to different departments. But if you want to overwrite values at lower level with some exceptions, you need to build some custom solution, adding extra few line items (just like in the Academy lessons).
    • Importing the data: I am not sure, but I think breakbacks don't work well with importing data at higher level. That means imported value that needs to be split would need to be split using my proposed solution, only then imported to your input line item.

     

    P.S. The module in which input would be done doesn't matter - you can place breakbacked, input line item in Depertments, use subsidiary view (because this line item would have Offices dimension), and later just reference it in your Offices module.

  • Brfonseca
    Brfonseca

    Hi,

    How can I do it without breakback?

    Thank you,

    Bruna

Categories