Resolving Circular Reference Calculation

One of the main reasons why TM1/Planning Analytics has been so successful over the years is its calculation engine. TM1 can resolves very complex calculations over millions of cells in an instant. However, its only weakness is that it will need a little bit of help to resolve an equation with a circular reference. A circular reference is when there is a formula in a cell that directly or indirectly refers to its own cell:

In the equation above, to calculate the Dealer Margin value, the equation needs the value of the Dealer Margin:

Even though the equation is correct, you will have to resolve this circular reference first before being able to calculate the Dealer Margin value in TM1.

This article will explain you how to resolve this circular reference by using a bit of mathematics:

TM1 does not do circular references, the following formulae in a TM1 rule will result with #N/A value:

['Dealer Margin'] = (['MSRP']-['VAT']-['Consumption Tax']) * ['DM%'];['Consumption Tax'] = ['W/S Price'] * ['Consumption Tax %'];['W/S Price'] = ['MSRP'] - ['Dealer Margin'] - ['Panda Fund'] ;

#N/A in TM1 could mean either there is a division by zero or there is a circular reference in the equation. To solve the circular reference, we will have to manipulate the equations.

Our objective is to transform the circular reference system into a linear system:

Okay so let’s do it!

We have a sytem with three equations with a circular reference: 

1. Simplify the system by removing the parentheses so it will be easier to manipulate the equations.
2. Break the circular reference: We need to gather the 3 equations into one main equation.

First to simplify the system we need first to get rid of the parentheses in the Dealer Margin equation:

Our new system is now as below:

Now we need to choose one equation and add the other two equations into this new main equation. You can choose any of the three equations to be the main one, in this example we choose the W/S Price equation:

To break the circular reference, we are going first to replace the Dealer Margin definition into the W/S Price and then we are going to do the same for Comsumption Tax into the Dealer Margin:

After a few steps, the W/S Price equation will not be linked to the Dealer Margin value.

Step 1: Substitute Dealer Margin to the W/S Price formulae

It’s almost done, now we just need to simplify this main equation.

Step 4: Factor the W/S Price

Step 5:  Divide both sides with (1 – Consumption Tax %*DM%)

That is it! Our equation is now:

If we now update our TM1 rule:

['Dealer Margin'] = (['MSRP']-['VAT']-['Consumption Tax']) * ['DM%'];['Consumption Tax'] = ['W/S Price'] * ['Consumption Tax %'];['W/S Price'] = (['MSRP'] - ['MSRP']*['DM%'] + ['VAT']*['DM%'] - ['Panda Fund']) / (1 - ['Consumption Tax %']*['DM%']) ;

Refresh the cube and the #N/A will have disappeared:

Remember this when kids say, “I’ll never use this math stuff in real life”…

• Upload data into TM1 from an Excel file.
• Understand relationships between your TM1 objects.
• What if Heimdall was part of your IBM TM1 team?