Problem definition:
In the area of Integrated Financial Planning for an international acting company it is required to have the ability to do elimination of intercompany transaction. For the actual booking it is often done within the ERP system or a dedicated legal consolidation solution to create the legal group consolidation and the consolidated financial statements over all legal entities. We as SAP offer the solution SAP GROUP REPORTING for this purpose. In planning, forecasting and simulation scenarios this can be done in a planning solution like SAP ANALYTICS CLOUD. This area of use should be described in the following.
Technical Solution:
The SAP Analytics Cloud already includes standard functionalities for the elimination of intercompany transactions like the Advanced Formular ELIMMEMBER. This Advanced Formula which can be used for Data Action calculation returns the names of the elimination members below the first common parent of two companies that did inter-company trading.
https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/e88b9a87633c4e19a0dbdfa8c...
https://help.sap.com/doc/a2c49c40bbe549ef9c291537aa561430/2021.20/en-US/index.html#ELIMMEMBER
ELIMMEMBER Example
In the case above, the inter-company transaction data between Korea and China will be eliminated after copying to Elimination Asia, which is the elimination member under their common parent, Asia. For the inter-company transaction data between Korea and France, the value will be copied to Elimination World under their first common parent, World. Using ELIMMEMBER, you can get Elimination Asia as the elimination member of Korea and China and Elimination World as the elimination member of Korea and France. In this example of elimination of intercompany transactions will be done on aggregated parent level of the trading entities.
Customization:
If the elimination of intercompany transaction should be done directly between the acting entities, another in the following described approach can be used. The example SAP Analytics Cloud planning model for this purpose consists of the following dimensions: VERSION, ACCOUNT, AUDIT, ENTITY and ENTITY_INTERCO.
SAC Planning Data Model
The ACCOUNT dimension includes elements for “Account Payable” and “Account Receivable” for internal partner entities and elements like “3rd party A/P” and “3rd party A/R” for external entities like customer or suppliers. For the intercompany elimination only the internal partner entities are considered.
Account Dimension
ACCOUNT Dimension Hierarchy
AUDIT Dimension Hierarchy
ENTITY Dimension Hierarchy
And in the following the ENTITY_INTERCO Dimension with the internal trading entities is seen. Additionally, the element "External" was created to handle all the external trading partners like customer and supplier in this example with related hierarchy:
ENTITY_INTERCO Dimension Hierarchy
The elimination data entry report includes two tables. The first one with an entry table in combination with “Before Elimination”, “IC A/P” + “IC AR” and the entities and the partner entities.
For example, now Germany receives goods from the partner entity France with a value of 300. Therefore, France receives goods from the partner entity Germany with a value of 200.
The booking for France would be 300 on “IC A/R” (Intercompany Account Receivable) in combination with the partner entity Germany and the audit element “Before Elimination”. After the elimination process, the 300 should be eliminated by the booking of -300 on the account “IC A/P” (Intercompany Account Payable) for Germany and the audit element “Elimination”.
The resulting booking is that Germany books 200 on “IC A/R” (Intercompany Account Receivable) in combination with the partner entity France and the audit element “Before Elimination”. After the elimination process, the 200 should be eliminated by booking of -200 on the account “IC A/P” (Intercompany Account Payable) for France and the audit element “Elimination”.
Story before elimination
To start the elimination, process an SAP Analytics Cloud Planning Trigger can be used, which executes a DataAction with Advanced Formular Step calculation.
Story after elimination
It is very clearly visible that by switching the sign and booking to the corresponding accounts, a balance on consolidated level Europe and Global will be achieved. In addition, all changes can be traced very well using the AUDIT dimension. Booking in combination with external trading partners is not considered in the elimination and would simply be added up and consolidated.
The intercompany elimination will be executed by a Planning Trigger inside the reporting story and the related data action and an advanced formular script is show in the following.
DataAction / Advances Formular Step / Visual
DataAction / Advances Formular Step / Script
//DE+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "FR_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "DE_P") * -1
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "US_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "US", [d/ENTITY_INTERCO] = "DE_P") * -1
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "CA_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "CA", [d/ENTITY_INTERCO] = "DE_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "FR_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "DE_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "US_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "US", [d/ENTITY_INTERCO] = "DE_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "CA_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "CA", [d/ENTITY_INTERCO] = "DE_P") * -1
//FR+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "DE_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "FR_P") * -1
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "US_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "US", [d/ENTITY_INTERCO] = "FR_P") * -1
DATA([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "CA_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "BE_EL", [d/ENTITY] = "CA", [d/ENTITY_INTERCO] = "FR_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "DE_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "DE", [d/ENTITY_INTERCO] = "FR_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "US_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "US", [d/ENTITY_INTERCO] = "FR_P") * -1
DATA([d/ACCOUNT] = "IC_AP", [d/AUDIT] = "EL", [d/ENTITY] = "FR", [d/ENTITY_INTERCO] = "CA_P") =
RESULTLOOKUP([d/ACCOUNT] = "IC_AR", [d/AUDIT] = "BE_EL", [d/ENTITY] = "CA", [d/ENTITY_INTERCO] = "FR_P") * -1
//+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Outlook:
Through further parameterization and the use of attributes or properties, the used Data Action Advanced Formular Step script could be parameterized even more and made leaner.
- SAP Managed Tags:
2 Comments
