How to do Stage Consolidation by BPC
As many customers know, the logic of BPC consolidation is designed according to the idea of “parallel consolidation”, which means the upper group run its consolidation based on the elimination result of all entities under this group without considering its sub-groups’ consolidation results.
But some customers have the requirement to do Stage Consolidation – Upper groups are only interested in the eliminations at its own level and lower groups’ consolidation results need to be aggregated to upper groups stage by stage.
Can we configure BPC to do that? The answer is yes.
There are two features in BPC Consolidation to support this.
– You have some data that have already been converted to group currency. You submit these data records at sub-group level and want them to roll up to upper groups.
This is achieved during the Currency Conversion process. Refer to the WIKI article below regarding this feature:
– After Consolidation execution, you want consolidation results to be aggregated to higher groups.
This can be achieved by setting an Audit member whose “DATASRC_TYPE” is set to be L. Refer to the WIKI article below regarding how to do that:
Here, I would like to give an example how to make the configurations to combine the two features to achieve the business requirement.
1, There are three groups. The group SEC5001 is the lowest group, and its parent group is SEB5000. SEB5000 has the parent group of SEA3000. Shown as below:
2, Ownership data is maintained and submitted for entity 5100.
3, In Audit dimension , to roll up manual adjustments posted in Group Currency, we set the “DATASRC_STAGE” property of the source audit “ELIM_JRN_FORECAST” to be “ELIM_JRN_FORE_LEVEL”. As below:
The configuration is done according to the Wiki:
Now, we submit the source converted manual adjustment to the group SEC5001 as below:
Run Currency Conversion at the top group SEA300 and we get the results as below:
As you can see, data is rolled up to higher level groups SEB5000 and SEA300.
Now we want to do consolidation at lowest group SEC5001 and want to roll up the Consolidation results from lower group to higher group too.
We make the configuration according to the WIKI article
4, In SCOPE dimension, we set the property “DATASRC_LEVEL” in the higher groups to be Y, as shown below. It doesn’t matter for the lowest group SEC5001.
5, In the Audit dimension, set the property “DATASRC_TYPE” of the member “ELIM_SYS_LEVEL” to be L, which will be used as the roll up audit at higher groups level for consolidation result.
6, Add the source Audit ELIM_JRN_ADJ and the roll up Audit “ELIM_JRN_FORE_LEVEL” to the DIMLIST “DATASRC_ELIM” so that we can use the DIMLIST to assign multiple Audits as the source audit in the Elimination business rule later. We have the target Audit ELIM_SYS_ARAP , whose “IS_CONSOL” property is set to be “Y”.
Please Note : It is a common mistake that, customers don’t add the roll up Audit “ELIM_JRN_FORE_LEVEL” for manual adjustment as the source audit in the Elimination rule, which will cause Unbalance at higher groups.
7, In the Elimination rule, we set the DIMLIST -DATASRC_ELIM as the source audit and the audit ELIM_SYS_ARAP as the target audit.
8, Set the rule detail:
Now we run consolidation and we get the result as below:
If we don’t add the roll up Audit “ELIM_JRN_FORE_LEVEL” for manual adjustment as the source audit in the Elimination rule as below
The consolidation result would be below:
We can see some un-balanced consolidation result at higher group SEB5000.
This is because if we add the roll up audit “ELIM_JRN_FORE_LEVEL” for manual adjustment as the source audit in elimination rule, it will also do elimination and generate a reverse value to the result marked in red in above image.