How to spread a cost from a father to his son in the hierarchy?

Options
LucaRicci
LucaRicci
in Modeling

Hi all,

I am having a hard time spreading costs along the hierarchy. My hierarchy is composed this way:

  • BU responsible
  • BU
  • R&D project

What I am trying to do is to spread a general cost across each BU using a driver and then to spread that BU cost again across its R&D projects using a second driver. The drivers need to be input by the user, so they can change.

I managed to complete the distribution of costs for each BU, but when I try to do the same thing for the R&D projects I come across the mismatch dimension error no matter how I try to tackle the problem.

I leave some screenshots of the modules I built.

Last one is my calculation module where you can see that I tried to make the "cost per prj" line item apply to a different list from the others, but nonetheless it does not seem to solve the issue.

image.png
image.png
image.png
image.png

Thanks in advance,

Luca

Answers

  • JaredDolich
    JaredDolich
    Options

    @LucaRicci

    Love this use case. You might consider storing your costs for the BU and BU responsible in their respective system modules (DISCO). Then you can take the R&D project percentage times the cost in the parent system module. So something like this: SYS01.BU Cost * R & D Driver. When it comes to allocation you can also use the same method to generate percentages, or % to Total. It's going to be important to use conditional formatting or some validation technique to make sure those R&D driver percentages add up to 100%.

    Looking good!

Categories