One of the main reasons why TM1/Planning Analytics has been so successful over the years is its calculation engine. TM1 can resolve 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.
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.
Circular Reference in TM1/Planning Analytics
TM1 does not do circular references. The following formulae in a TM1 rule will result with #N/A value:
#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.
Resolving Circular Reference
Our objective is to transform the circular reference system into a linear system:
Starting Point
We have a system with three equations with a circular reference:
How to resolve this system
Simplify the system by removing the parentheses so it will be easier to manipulate the equations
Break the circular reference — We need to gather the 3 equations into one main equation
Simplify the system
First, to simplify the system we need to get rid of the parentheses in the Dealer Margin equation. Our new system is now:
Break the circular reference
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 Consumption Tax into the Dealer Margin.
Step 1: Substitute Dealer Margin to the W/S Price formulae
Step 2: Substitute the Consumption Tax with its definition to W/S Price
It's almost done — now we just need to simplify this main equation.
Step 3: Move to the left side W/S Price * Consumption Tax %*DM%
Step 4: Factor the W/S Price
Step 5: Divide both sides with (1 – Consumption Tax %*DM%)
That is it! Our equation is now resolved. If we now update our TM1 rule:
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"…