Academy

I knew it from the start.

The moment I started with presenting general ledger data in icCube dashboards, the issue of intercompany elimination would pop-up, eventually. T I had an easy elimination solution at one of my clients, that worked for three years. During fall 2017, the business controllers of that specific client requested a full elimination solution that would eliminate intercompany deals on all levels of the organization; a functionality you see in specialized financial consolidation applications like Oracle HFM or SAP BFC.

In this post, I present you the solution for intercompany elimination when processing financial G/L data in icCube.

What is “intercompany elimination” and why would you care?

Intercompany elimination can be defined as the process for removal of transactions between companies included in a group in the preparation of consolidated accounts. The process of intercompany elimination is helpful in managing eliminations of operations among companies within a single group (see link).

Organizations do business with the outside world and as a result transactions flow into the general ledger. Suppose we have a company, ‘EasyDrone’, that sells and repairs drones and within this company an engineer doing the repairs, Monica.

Using our definition of Intercompany Elimination and for the sake of this example, EasyDrone would be the group with two companies (in our example the Sales – and Engineering department). Our interest will be to calculate the actual value of this two departments as separate entities.

Let’s start creating a few financial transactions :

Monica repairs a drone for one of EasyDrone clients. After the work is done, the hours are billed and the results are recorded in the general ledger as “revenue from repair” for the cost center “engineering agency”. The revenue rolls-up into the organizational structure as indicated in the figure below. This transaction with a real client is called a regular “external transaction” (action 1).

So far so good. Next, a second-hand drone and a training drone need to be repaired by Monica. In this case, the clients are not a “real” client, but two internal departments: training and sales. When the job is done, Monica records her hours worked and an internal invoice is sent to the training department (action 2) and the training department (action 3). The G/L will record these as “revenue from consulting” and “repair cost” transactions.

The transactions between organization structures like these are called “intercompany transactions”. The departments roll-up into the organization structure as indicated in the following figure:

Adding up the revenue and costs for these actions gives the following result:

Gross profit looks nice but what happens if we look at the ‘revenue from repair’ and ‘repair costs’ ?. Are you able to detect the issue with the intercompany transactions?

Let me give you a clue. What if the Engineering department and the Sales department were separate legal entities and you wanted to buy only the latter. What would be the “real” revenue for the “Engineering department”?

Think about it, for a moment.)

Would it be really € 190? I do not think so. As a buyer I would argue that the internal revenue to “training” is not a real revenue, therefore I would value the revenue at € 155 only, eliminating the internal transaction with training as it is not a real revenue.

This process of eliminating the internal transactions is called “intercompany elimination”.

Applied to the example above, the following additional eliminating transactions are required to give the eliminated view of the complete organization:

In the figure above, you can see the “real” revenue from repair for each level in the organization. The “engineering agency” can report € 190 revenue in the internal management reports, but as you zoom out to the higher levels the internal transactions are deducted, eventually resulting in just € 100 revenue for the Total Organization, which is the revenue of the external client.

Without having the “eliminated” view on the organization, the revenues and costs are reported too high due to intercompany transactions.

This example showed intercompany bookings for internal services (revenue & costs), but intercompany effects, also apply to internal debts and loans and internal stock ownership.

The icCube solution for intercompany elimination

In the following steps I will show you how you can build intercompany elimination into your financial icCube model. I will use the data in the example above to keep the explanation short and simple, but you can easily modify it to your needs.

To make it practical, a ready-to-use schema can be downloaded here. Unzip and then import into the icCube builder.

step 1 – model exploration

The model contains three in-memory tables:

• 1. data, the data for the revenue in the previous chapter
• 2. costcenter structure parent-child, the structure for the organization
• 2. elimination structure, the flat hierachy structure for the elimination dimension

The solution for intercompany can be found in the icCube “data views”: createPath and elimination. Both views are ETL procedures based on javascript.

dataview: createPath

This procedure generates a path string in which the parent-child hierarchy is stored. The hierarchy in this example is very simple, but in reality, organization structures tend to be very “ragged”, meaning that some parts might just contain a few levels of depth while other parts are very detailed. Typically, the organization structure ends with the cost centers. That is why I call the organization structure “Costcenter”.

Here is the Init. Code:

ctxt.map = [[], []];

Row Processing Code:

// Get current node data
var node = get("costcenter");
var parent = get("parent");

// Find the path of the parent 
var idx = ctxt.map[0].indexOf(parent);
var parent_path = ctxt.map[1][idx] || "";

// Create path of current node
var path = parent_path + "|" + node;
set("PATH", path);

// Add row to map array
ctxt.map[0].push(node);
ctxt.map[1].push(path);

fire();

Completion Code:

ctxt.map = [];

This is the result:

costcenter	parent	PATH
Organization	null	|Organization
Sales dept.	Organization	|Organization|Sales dept.
Engineering dept.	Organization	|Organization|Engineering dept.
sales second-hand machines	Sales dept.	|Organization|Sales dept.|sales second-hand machines
sales new machines	Sales dept.	|Organization|Sales dept.|sales new machines
engineering agency	Engineering dept.	|Organization|Engineering dept.|engineering agency
training	Engineering dept.	|Organization|Engineering dept.|training

dataview: elimination

This procedure processes all rows and if the row is an intercompany booking, it will “calculate” the elimination booking that will counterpart this row.

The logic is as follows:

• 1. for each normal row, set the elimination type to 1 (= orginal). If the row does not have an intercompany value, set the intercompany value to “external”.
• 2. for each row that has an intercompany value, find the lowest common parent for the “costcenter” – “intercompany” combination using the commonPath matrix.
  When found, create an elimination booking with coordinates on the common parent, elimination type 2 (= elimination booking) and -1 x amount.

Here is the Init. Code:

	//
	// get first common parent of all combinations
	//
	var df = table("createPath");
	var n = df.count();
	
	// output matrix, n rows and 2 columns: 1st element, 2nd the path
	var nrow = 2;
	var ncol = n;
	
	ctxt.m = Array(nrow);
	
	for(var i=0; i<nrow; i++) {
	ctxt.m[i] = new Array(ncol);
	}
	
	for (var i=1; i<n; i++)
	{
	var idx = i;
	var element = df.row(i).get("costcenter");
	var path = df.row(i).get("PATH");
	
	ctxt.m[0][idx] = element;
	ctxt.m[1][idx] = path;
	
	}
	
	ctxt.row_nb = 0;
	

Row Processing Code:

	var costcenter = get("costcenter");
	var intercompany = get("intercompany");
	
	if(get("intercompany") == null)
	{  intercompany = "external";
	} 
	
	// 1st, show the regular input as output
	set("costcenter", costcenter);
	set("intercompany", intercompany);
	set("type", 1);
	set("amount", get("amount"));
	fire(ctxt.row_nb++);
	
	
	// 2nd, check if the row is an intercompany booking and if so
	// prepare the row
	if(intercompany != "external")  // intercompany <> null <> ""
		{
		
		// look up the paths in the predefined matrix for both the costcenter as the intercompany
		var cc_path = "";
		var ic_path = "";
		for (var k=0; k<ctxt.m[0].length; k++)
		{
		if (ctxt.m[0][k] == costcenter)
		{
		cc_path = ctxt.m[1][k].split("|");
		if (ic_path.length > 0 )
			{
			break;
			
			}
			}
			if (ctxt.m[0][k] == intercompany)
			{
			ic_path = ctxt.m[1][k].split("|");
			if (cc_path.length > 0 )
			{
			break;
			
			}  
			}
			}
			
			// now, compare the paths
			var common_parent = "";
			for (var i = Math.min(cc_path.length, ic_path.length); i > 0; i--)
			{
			if (cc_path[i-1] == ic_path[i-1])
			{
			common_parent = cc_path[i-1];
			break;
			}
			
			}
			
			
			set("costcenter", common_parent);
			set("intercompany", common_parent);
			set("type", 2);
			set("amount", -1*get("amount"));
			fire(ctxt.row_nb++);
			
			}
			

This is the result:

costcenter	intercompany	amount	type
engineering agency	external	100.0	1
engineering agency	sales second-hand machines	55.0	1
Organization	Organization	-55.0	2
engineering agency	training	35.0	1
Engineering dept.	Engineering dept.	-35.0	2

As you can see, the procedure has generated two elimination bookings, exactly at the right level.

.

step 2 – model generation

Deploy the schema and navigate to the MDX editor. And copy the following MDX statement:

[icCubeMdx]

select {[Measures].[amount] }*descendants([Elimination].[Elimination].[Eliminated]) on 0
				, descendants([CostCenter].[CostCenter].[Total CostCenter]) on 1
			from finance

[/icCubeMdx]

This gives as result:

Exactly as we want it to be!

Feel free to explore this model into more detail. Although the Intercompany dimension is not strictly needed, I would advise you to keep it as is, since it provides very useful insights in the intercompany process for the financial controllers.

practical things to consider in real-life implementation

The code presented in the previous paragraph has been implemented at a client and is used on a cost center structure of about 1500 members and 1 mio fact rows. It has worked flawlessly and fast for the past few months and I assume it will continue to work like that for the years to come.

When implementing intercompany allocation, consider the following:

• If intercompany elimination is introduced in an existing solution, make agreements with the controllers how to proceed with the historical data. Most likely, they do not want it to be changed, so intercompany elimination should only be applied to the data starting with January 1st this year.
• Loading the elimination bookings on subtotal levels might be difficult for end-users to swallow. For example, have a look at the column “Eliminated” in Figure 4. If you only see the figures for this element you could get nervous that the figures “do not add up”. Also the € -90 does not help a lot as you can not see the eliminatin booking of € -55 seperately, but in stead you see the summarized result. A possible solution to this, is to add an additional input level for each subtotal to the Costcenter hierarchy. Obviously, this has also its drawbacks as it “pollutes” the structure.
• Regardless of what you choose to load the elimination bookings, ensure to create sufficient explanatory dashboards. Intercompany elimination is a difficult process so help your controllers and other end-users so they can review, analyse and explain the results they see. The most efficient solution is the solution that leaves no unanswered questions for your audience.

Good luck implementing this in your dashboard solutions!

By Arthur van den Berg