How to do multi level allocation

Center of Excellence · October 2023

1. Abstract

A Multi-Level cost allocation is an important process of any business, it plays a huge role in the decision-making process, and that’s why we must design it in the best possible way.

The Multi-Level cost allocation consists of applying a classic allocation but with the possibility that an element can be at the same time a target and a source of the allocation process.

2. Context

In a cost allocation module, sometimes we face the need to perform allocations at multiple levels. Let’s see in this article what are the best practices in implementing such a process.

3. Content

The Multi-Level allocation procedure consists of applying the allocation in several iterations.

Let’s take the following use case: cost allocation between different departments (see schema here below).

We notice that some departments are both targets and sources of the allocation process.

For example :

Step 1: Management department allocates total or partial costs to the Finance and Production Planning departments.
Step 2: Finance will also allocate to Production Planning.
Step 3: Production Planning will do the same operation as Manufacturing.

3.1 Use case

For this use case, the focus is only the logic of the allocation procedure, all that concerns the source perimeter to allocate, the allocation driver, reporting …etc. has already been treated in the article How to allocate with a dynamic driver.

3.1.1 Entities and Relationships

3.1.2 Cubes

We have the “Costs” cube (before allocation) that is structured as follows :

The “Allocated Costs” cube shares the same structure.

The “Allocation driver” cube is structured as follows:

3.1.3 Source data

3.1.4 Allocation driver

By rows: Source Cost Center
By column: Target Cost Center (CC [R])

As you can see, each source CC can allocate its costs totally or partially to one or many target CCs, for example :

CC 1: keeps 50% of its costs and allocates 10% to CC 5 and 40% to CC 21
CC 5: allocates 100% of (its original costs + costs coming from CC 1) to CC 10
CC 10: allocates 100% of (its original costs + costs coming from CC 5) to CC 11
CC 11: keeps 50% of its costs and allocates the remaining 50% to CC 12
CC 12: keeps 50% of its costs and allocates the remaining 50% to CC 19, note that CC 12 is also receiving costs from CC 9
CC 19 will also allocate to others and so on … Etc.

So, we can see that a cost center can have multiple sources, and it also can allocate to many targets.

3.2 Complexity

The complexity of this use case lies in defining the right sequence of allocations, which means that for a Cost Center “X” to allocate its costs to “Y” and “Z”, it must first receive all costs coming from other CCs towards “X”.

Let’s take the previous example:

To allocate from CC 12 to CC 19, CC 12 must first receive the costs coming from CC 11 and CC 9.

3.3 Solution

The solution is to apply the allocation following multiple iterations.

Iteration 1: apply the allocation on one shot for all the CCs, as if it is a single-level allocation.

Iteration 2: Take only the CCs that were targets of iteration 1, and calculate their new costs as follows:

Cost before Iteration 2 = Original cost + Results of iteration 1

Apply allocation on one shot for those CCs

Iteration 3: Take only the CCs that were targets of iteration 2, and calculate their new costs as follows:

Cost before Iteration 3 = Costs before Iteration 2 + Results of iteration 2

Apply allocation on one shot for those CCs

Iteration 4: …

Iteration n: the last iteration is when the result of applying the allocation is NULL, which means that the remaining CCs only receive costs, they do not reallocate.

The final value allocated is calculated as follows:

Suppose we have 5 iterations, for each CC we apply this formula:

Final allocated value = if iteration 5 is NOT NULL then iteration 5, else, if iteration 4 is NOT NULL then Iteration 4, else, if iteration 3 is NOT NULL then Iteration 3 …etc.

The result of the previous example is:

3.4 Procedure

3.4.1 Loop

Before getting to the core of the procedure, we must first define the logic of the iterations. Since the number of iterations cannot be known and is not fixed, we must build a procedure that is dynamic and works on all configurations on the allocation driver.

The solution is to use a LOOP, the logic of which is as follows:

Prerequisites:

Entities:

Allocation step: which has the following elements {Step 1, Step 2, … Step n}.
Dummy: which has only one element.

Cubes:

LOOP Steps, structured by Allocation Step. This cube is filled by the step position as follows:

LOOP Next Step, structured by Allocation Step.
LOOP Count, structured by Dummy.

The logic is:

Initialize cube LOOP Count with value 1.
Condition to enter the LOOP:

Treatments: Allocation
Incrementation

Go To Condition

3.4.2 Calculation algorithm

Now let’s focus on the core of the procedure.

As mentioned earlier, for each iteration, we must define the correct value to be allocated; to get this value, we must include another dimension “Cost Center Origin”, identical to the Cost Center so that we can keep track of the origin of the allocation; let us explain this with the following example:

CC1 allocates 10% to CC5.
CC5 allocates 100% to CC10.
CC10 allocates 100% to CC11.
CC11 keeps 50% and allocates 50% to CC12.

By drilling to Cost Center Origin on the CC11

Iteration 1: CC11 is receiving 900k which is the original value of CC10.
Iteration 2: CC11 receives 1700k that is the sum of 900k (original value of CC10) + 800k which CC10 received from CC5 in iteration 1, knowing that 800k is the original vale of CC5.
Iteration 3: CC11 is receiving 1800k which is the sum of 900k + 900k, i.e.:
- 900k original value of CC10
- 900k received by CC10 from CC5, which is:
  - 800k original value of CC5
  - + 100k that CC5 received from CC1.

At this point, the final value of CC11 to be used in iteration 4 is the sum of 650k which is its original value + 1800K which was received from all the others, which gives 2450k.

Now, drilling down to CC12:

We can see that in Iteration 4, CC12 received 1224k from CC11, which is 50% of the value of CC11, and CC11 retained the other 50% which became its final value.

Attention:

The final value is NOT the original value + the results of the last iteration.
The final allocated value is the sum of the original value + the result of the last iteration for each source CC.

Let us continue with the same example to understand.

In this step, for Iteration 5, CC12 will keep 50% and allocates the other 50% to CC19.

The final value of CC12 is calculated as follows:

1200k original value.
+ 1641k coming from CC9 (results of iteration 3).
+ 1225k coming from CC11 (results of iteration 4).
The sum of the 3 above gives 4066k.
Taking 50% of 4066k gives 2033k.

Please see the screenshot here below.

Let us take CC21 as a last example for better understanding.

CC21 retains 100% of its costs, as we see in the allocation driver, by drilling to the “Cost Center Origin” we get:

The final value of 7,322,250 is calculated as follows:

2,800,000+400,000+273,500+738,800+980,250+524,100+738,800+294,250+572,550 = 7,322,250

Let us reiterate this once again:

The final allocated value is the sum of the original value + the result of the last iteration for each source CC.

3.4.3 Procedure steps

Before entering the loop, we need to set 2 things:

Add the Cost Center Origin dimension to the source cube:

→ C=a*b

Remove the value of the CC allocation driver, which is on itself:

→ C = if(b=0,a,0)

Then, the procedure steps are as follows:

1. Select Iteration step

2. Calculate the allocation using the Allocation Driver → d=(a+b)*(c/100)

3. Eliminate the CC → b=a

4. Move the CC[R] in CC → b=a

Since we are working with the replicated entity Cost Center [R], it is possible to simply copy cube a into cube b, Board will automatically do the mapping between the 2 entities, otherwise, if Cost Center [R] is not a replicated entity, you must use a mapping cube to pass from cube ‘a’ to cube ‘b’ as follows :

Create mapping cube structured by Cost Center and Cost Center [R].
Create a temporary cube that has the same structure as cube “a” + Cost Center
Temp cube = cube * mapping

5. Copy the result of the Step in Allocated Costs (Step) → b=a

6. Condition to Exit the Loop

7. Calculate the last allocated value → C= if(a<>0,a,b)

8. Step 1 Calculate the new origin CC for each step → b=a

8. Step 12Calculate the new origin CC for each step → c=a*b

9. Final allocated value (with origin) → c=a+b

After quitting the loop (when all iterations have been treated), the last thing to do is to populate the target cube “Allocated Costs” → c=a-(a*(b/100))