BOARD Replicated [R] Entities - How to use them

Document created by ggallo Employee on Jul 25, 2017Last modified by ggallo Employee on Sep 20, 2017
Version 5Show Document
  • View in full screen mode

The Adoption of the Replicate Entity ([R]) can simplify the calculation of all the processes that require multi-level allocations among different members of the same Entity.

Classic Samples are the Explosion of the Bill of Material or Cost Allocation from intermediate Cost Centers to Final Cost Centers (or from Pure Cost Centers to Revenue Cost Centers) 

 

Let's pretend the following:

You need to allocate some amounts from the elements (A01-A05) of an entity (Entity A) to the elements A08-A10 of the same entity. You can imagine this entity to be anything (Account, Cost Center..). The elements from which the amounts are sourced are also identified as "LEVEL1" with an aggregating entity, while the elements which should be "receiving" those amounts are aggregated to "LEVEL 2" and  "LEVEL 3" of the same aggregating entity, representing corresponding allocation steps.

In simpler words, the amounts associated to the elements belonging to LEVEL1 should be allocated first to the elements of LEVEL2 and then, from these, be allocated to the elements of LEVEL3.

 

As a first step, you should create a replicated entity for the Entity A. This should generate another entity, labelled as "Entity A [R]" in your database.

The main first benefit you'd get from creating a replicated entity, is that any members are added/uploaded into the original entity, will be automatically inherited and added also to the replicated entity.

As a consequence, extending the concept to a hierarchy, if you replicate a whole hierarchy, any change in relationship happening between elements of the original hierarchy, will be replicated and automatically inherited by the replicated hierarchy. Technically, the two entities and/or relationships share the same physical file.

As soon as the replicated entity is created, you must then define something to determine the "allocation" logic. This can be done by creating an Infocube structured by the 2 entities ("Entity A", and "Entity A [R]"): in this way, you can represent the allocation logic with a table showing the 2 entities by row and column as in a matrix (see below image)

 

In the example below the "destination" elements are identified with "1", integer values:

You can also display cells as check boxes.

Another option would be to define weights within each cell so that every row (every element of the source entity) sums up to 100 (or 1).

 

In order to perform a first allocation step, you need:

  1. - the Source Amounts Infocube (the infocube that contains the amounts to be allocated)
  2. - the Allocation Criteria Infocube (the matrix above defining the combinations of source-destination elements)
  3. - the Results infocube (structured by both "Entity A" and "Entity A [R]")

The first allocation logic/algorithm is quite simple: applying a selection on the Level 1 members you can easily calculate ("c") from the example above, multiplying "a" times "b"

See the results in the light-blue area below.

 

You have now "allocated" correctly your amounts on LEVEL2 elements of the replicated entity. This means that you can see the amount according to the "original" amounts on the "source" elements, displaying the "Entity A" (original entity) by row, or you can display the "destination" amounts displaying the "Entity A [R]" by row.

In order to proceed in the allocation process and allocate the amounts from LEVEL2 to LEVEL3 (the green Area of the matrix in the first picture), the data must be pivoted from "Entity A [R]" members back to the "Entity A" members.

 

You can copy the result of the LEVEL1 calculation infocube into a cube structured only by "Entity A [R]". (see STEP1 below)

This can be achieved using a simple dataflow where block b (the infocube structured only by Entity A [R] = block a (the infocube result of the first allocation).

Then you can copy this cube structured by "Entity A [R]" into an infocube structured only by "Entity A". (see STEP2 below)

BOARD will automatically recognize the elements using the codes and move amounts accordingly, and this is an additional benefit of the [R] Entity.

The "pivoting" process would have required a dataflow using a Diagonal Matrix or an Extract and re-Load process, on earlier versions of BOARD. 

 

 

 

This last cube (the result of STEP3) will become the Infocube used to start the LEVEL2 allocation process.

As done earlier with the LEVEL1, using a BOARD procedure, you can select only LEVEL2 elements and multiplying this infocube by the matrix again, you should be able to perform the same allocation process.

.

Please download the attached example and familiarize yourself with this technique

18 people found this helpful

Outcomes