Skip to Content
Technical Articles
Author's profile photo Thomas Haeusel

Intercompany Elimination with SAP Analytics Cloud

 

In this article, I would like to describe a typical business challenge in the context of integrated financial- and cost planning and how it can be handled within SAP Analytics Cloud.

 

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/e88b9a87633c4e19a0dbdfa8ce3909f3.html?q=ELIMMEMBER#loioe88b9a87633c4e19a0dbdfa8ce3909f3__IC_Elim

https://help.sap.com/doc/a2c49c40bbe549ef9c291537aa561430/2021.20/en-US/index.html#ELIMMEMBER

ELIMMEMBER%20Example

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%20Planning%20Data%20Model

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%20Dimension

Account Dimension

And the resulting hierarchy.Account%20hierarchy

ACCOUNT Dimension Hierarchy

To track the changes before and after elimination, the AUDIT dimension with the following structure is used. This allows to have a clear overview over the changes before and after the elimination process.Audit%20Dimension

AUDIT Dimension Hierarchy

In the following the ENTITY dimension structure with the organization entities can be seen.Entity%20Dimension%20Hierarchy

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%20Dimension%20Hierarchy

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%20before%20elimination

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.

After the execution of the intercompany elimination process the result looks like this: Story%20after%20elimination

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%20/%20Advances%20Formular%20Step%20/%20Visual

DataAction / Advances Formular Step / Visual

DataAction%20/%20Advances%20Formular%20Step%20/%20Script

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.

 

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alexey Dugarov
      Alexey Dugarov

      Thanks for your blog post! Haven't seen an example for this Function before, except help docs.

      Author's profile photo Karsten Oehler
      Karsten Oehler

      well done, Thomas, an excellent example.