Circular reference in a financial model

Post Reply
lynnsong986
Posts: 76
Joined: Wed Feb 28, 2018 2:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Circular reference in a financial model

Post by lynnsong986 » Mon Nov 18, 2019 2:39 pm

Hello, I need some help with a circular reference in TM1 (knowing it is not allowed). Attached is a simplified model where a circular reference exists in excel:

interest expense (to simply it I only calculate on short term debt) = STD * interest rate; interest is part of net income that goes into retained earnings on balance sheet
Short term debt - this is basically a plug, short term debt is not allowed to go negative (once it becomes negative cash will increase)

the issue here is that I need to circular reference STD in order to calculate interest expense. In the Tm1 model, this is done through rules (not TI), I can't make it to work (basically crushed the server because of the circular reference), can someone please help me with this?

thanks so much!
Attachments
Circular Ref.xlsx
(10.57 KiB) Downloaded 7 times

ascheevel
Posts: 91
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: Circular reference in a financial model

Post by ascheevel » Mon Nov 18, 2019 3:51 pm

To be sure, you don't really have a TM1 problem, you have a math problem. Solve the circular ref issue in excel (without iterative calc turned on) and you would solve your problem implementing in TM1. I wasn't a math major, so the attached solution might not be the most elegant, but one way to solve these is to calc individual parts that are not part of the dependent calculation and reference those instead. I've done this in the attached in rows 19 through 22 in column D. Column B has your original formulas. By calcing retained earnings & net income before interest is applied, you can calc short-term debt before interest and then apply the interest and sum the result for your final short-term debt amount. I consolidated the formula in cell E23 if you don't like creating measures for the different formula steps.
Attachments
Circular Ref.xlsx
(11.72 KiB) Downloaded 10 times

lynnsong986
Posts: 76
Joined: Wed Feb 28, 2018 2:20 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Circular reference in a financial model

Post by lynnsong986 » Mon Nov 18, 2019 4:02 pm

oh wow I never thought of circumventing formulas like that, you are a genius! thanks a ton!!

ascheevel
Posts: 91
Joined: Fri Feb 15, 2013 5:49 pm
OLAP Product: TM1
Version: 11.2
Excel Version: 2016

Re: Circular reference in a financial model

Post by ascheevel » Mon Nov 18, 2019 5:25 pm

Cubewise had a good blog post on working through circular ref in TM1, provides good examples & visuals.


https://code.cubewise.com/blog/resolvin ... alculation

Post Reply