How to allocate with a dynamic driver

Center of Excellence · October 2023

1. Abstract

In a complex application, we often face the need to perform allocations based on a different driver based on the situation, in most cases it is about cost allocation.

We will see in this article a step-by-step example of how to implement such a process.

2. Context

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

Since there are many types of costs (fixed, variable, direct, indirect), the allocation driver can be different according to the perimeter.

Many points must be clearly defined during both the analysis and configuration phases, we will try to cover in this article two main pillars:

Best practices in data modeling for allocation.
Best practices in writing the allocation procedure.

3. Content

Let’s consider the following example:

For an international company with many legal entities all around the world, the need is to perform allocation for Group financial closing.

The starting point is the P&L cube, structured as follows:

The need is to allocate costs based on the combination of one group account and one legal entity to another legal entity.

Note the presence of the “Layer” dimension, composed of these elements:

Basis (the basis value that could be loaded, entered, or calculated)
Local Adjustments
Group Adjustments
Group Elimination
Allocation

The logic of this dimension is to maintain an audit trail of all data processes, where each element contains the delta from the base element, e.g.:

To view loaded data → select layer = Basis
To view loaded data after local adjustment → select layer = Basis + Local Adjustments

Following this logic, we will inject the allocated data into the “Allocation” layer of the same cube at the end of the allocation process.

3.1 Source Perimeter

The first step is to determine the source perimeter and identify the data to be allocated for which group account and legal entity (combination of sources).

In our example, the definition of the source scope is simply through a structured cube for Legal Entity and Group Account, and the user can define the scope through data entry.

The application must allow the user to choose the scope of origin according to the functional requirements, through a flexible interface like a dataview.

For ACME Corp. America, several group accounts were included in the source definition.

It is always preferable to use alerts to help the user with data entry and make sure to check all necessary combinations.

3.2 Target perimeter

As mentioned earlier, the need is to allocate data from one legal entity to another, so we need to define the mapping between the source legal entity and the target legal entity.

To define this mapping, it is necessary to have a cube structured by the Legal Entity (source) and the replicated entity of the Legal Entity (target).

The user performs data entry as follows:

source legal entity by columns
target legal Entity by rows

ACME Corp. AMERICA has been entered as the source legal entity for ACME Corp. CANADA, which is the destination legal entity.

It is always preferable to use alerts to help the user check for each source whether it has a target or not.

3.3 Allocation driver

Now we need to define the allocation method for each source combination (Legal Entity and Group Account).

The available allocation methods are:

Headcount
Revenue
Projects
Assets

We need to create a dimension named “Allocation Keys” filled with the above elements and create a cube structured with the source dimensions (Legal Entity and Group Account) plus the Corp. Allocation Key dimension.

Note: the allocation driver can be loaded from a file or entered by users.

Note: the combination of visible sources is guided by section 3.1 Source perimeter.

3.4 Writing the procedure

Before allocation, it is necessary to collect the allocation driver data in a cube structured by {Month, Legal Entity, Allocation Keys}.

For example, the ‘Headcount’ data is retrieved from the HR module, while the ‘Revenue’ data is retrieved from the Sales module (Alternatively, this information can be uploaded to the Board application by users from an external layer).

Once the driver data has been successfully stored, we need to transfer it to the legal entity [R], which is the destination (target) legal entity.

Therefore, the allocation driver data cube structured by {Month, Legal Entity [R], Allocation Keys}.

Now we can perform the allocation by following these steps:

Step 1:

Select the correct source Layer (for example Layer = Basis, Local Adj, Group Adj)

Clean source data and keep only the combinations to allocate.

→ c = a*b

Step 2:

Populate the allocation methods with the driver values of each key method.

This is done in 2 dataflows:

First add the Legal Entity [R] to the allocation methods

→ c = a*b

where block a is the result of the mapping of section 3.2 Target perimeter, and block b is the result of the mapping of section 3.3 Allocation driver.

2. Then populate the methods with the key values

→ c = a*b

Step 3:

Adding the target dimension to the source data

→ c = a*(b/b)

NB: in this dataflow, it is possible to replace the block b with the cube Corporate Allocations Targets, but it is more performant to use the temporary cube Source & Targets with Method Keys since its structure is more similar to the target cube.

Step 4:

Performing the allocation.

This is done in 2 dataflows:

First we need to aggregate the allocation key values according to the source data structure in order to calculate the ratios.

→ b = a

2. Now that we have the allocation drivers at the detailed level (numerator) and also at the aggregate level (denominator), we can easily perform allocation as follows:

→ d = a*(b/c)

Step 5:

Now we have the “Results” cube that has the allocated data, note that the allocated data is on the Legal Entity [R] dimension, so we need to put it back by Legal Entity to be able to inject this result in the main cube “Actual P&L”.

This is done in 2 dataflows :

The first one is just to get rid of the Legal Entity and allocate key dimensions

→ b = a

2. Secondly to move data from Legal Entity [R] to Legal Entity

→ b = a

Since we are working with the replicated entity Legal Entity [R], it is possible to simply copy cube a into cube b, Board will automatically do the mapping between the 2 entities, otherwise, if Legal Entity [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 Legal Entity and Legal Entity [R].
Create a temporary cube that has the same structure as cube “a” + Legal Entity
Temp cube = cube * mapping
Target cube (without the Legal Entity [R]) = temp cube

Step 6:

Now that the data has been allocated with the right structure (Legal Entity), the last step is simply to inject the data back into the main cube, but on a different layer:

Select Layer = Corporate Allocation

In that Layer we must cancel the initial value and add the new allocated value, so we do the following dataflow:

→ c = -a + b

3.5 Conclusion

This method does not work for multi-level allocation, but only for one level, which means that a legal entity can allocate costs to other legal Entities, but a target legal entity cannot reallocate to other legal entities.

The goal is to achieve this result by using the Layer Dimension.

Allocation results by accounts: