Dealing with multiple circular references in Excel

[DaRook1e]

Hi folks,

I have been dealing with circular references in a financial model I develop and unfortunately cannot find a solution. The problem is that I have multiple circle references that interfere on multiple levels with each other and throughout the model. I have reproduced the problem by building a simplified income statement and balance sheet (see below). My goal is to calculate the amount of borrowings the company has to take on based on the difference between total assets and (rest of liabilities + equity). The IS and BS are both interconnected through the deal flo, revenue, expenses, interest expense, cash, equity and borrowings - the mess is real!

One observation I have made is the following:

I have tied the revenues to the total deal flow that is linked to the total assets by a multiple of 15x. Excel can handle a multiple up to 17x the total assets and the result will converge to a stable solution. Once a multiple greater than 17x total assets is entered, the circle reference calculations become unstable and shoot through the roof.

I hope that there is a waay to handle this problem in one of two ways:
1) preserve but "optimize" the circle references so that a stable solution is achieved.
2) break the circle reference by introducing helping calculations

Your help is very much appreciated!

Cheers
Gena

Circle Reference Problem.xlsx
	B	C	D	E	F	G	H	I	J
2		Assumption	FY1	FY2	FY3	FY4	FY5
3	Deal Flow (x Assets)	15x	14,250	34,821	58,446	72,964	83,196		tied to total assets
4
5
6
7	Income Statement
8	Revenue (% of total deal flow)	10%	1,425	3,482	5,845	7,296	8,320		tied to deal flow (total assets)
9	Cost of service		1,100	900	700	600	550
10	SG&A	2%	285	696	1,169	1,459	1,664		tied to deal flow (total assets)
11	EBIT		40	1,886	3,976	5,237	6,106
12	Interest (10% of borrowings outstanding amount)	10%	58	197	354	450	518		tied to borrowings
13	EBIT		(18)	1,689	3,622	4,787	5,588
14	Tax	20%	8	377	795	1,047	1,221
15	Net Income		(26)	1,311	2,826	3,739	4,366		tied indirectly to total assets and borrowings
16
17
18	Balance Sheet
19	Assets
20	Cash		100	1,411	2,926	3,839	4,466
21	Cash at banks		100	100	100	100	100
22	Restricted cash (retained earnings)		-	1,311	2,826	3,739	4,366		tied to net income
23	Restricted cash (cash to cover losses in the first 2 years, funded at the beginning)		(26)	(26)	-	-	-		tied to net income
24	Receivables		50	60	70	75	80
25	Tangible assets		800	850	900	950	1,000
26	Total Assets		950	2,321	3,896	4,864	5,546		indirectly tied to net income
27
28	Liabilitites
29	Payables		45	50	55	60	65
30	Borrowings		579	1,971	3,541	4,504	5,181		tied to total assets ) and equity (directly tied to net profit/loss and indirectly to total assets through deal flow)
31	Equity		326	300	300	300	300
32	Paid-in capital		300	300	300	300	300
33	Reserves for accumulated losses		26	-	-	-	-		tied to net income
34	Total Liabilitites und Equity		950	2,321	3,896	4,864	5,546		tied to borrowings
35
36	Check A = L + E		TRUE	TRUE	TRUE	TRUE	TRUE
Nachstellung

Cell Formulas
Range	Formula
D3:H3	D3	=$C$3*D26
D8:H8,D10:H10	D8	=$C8*D$3
D11:H11	D11	=D8-D9-D10
D12:H12	D12	=$C$12*D30
D13:H13,D15:H15	D13	=D11-D12
D14:H14	D14	=$C$14*D11
D20:H20,D31:H31	D20	=D21+D22
D22:H22	D22	=IF(D15<0,0,D15)
D23	D23	=SUMIF($D$15:$E$15,"<0",$D$15:$E$15)
E23	E23	=D23
E25:H25	E25	=D25+50
D26:H26	D26	=SUM(D24:D25)+D20
D30:H30	D30	=D26-(D29+D31)
D33:H33	D33	=IF(D15<0,-D15,0)
D34:H34	D34	=D31+D30+D29
D36:H36	D36	=D34=D26

 

[bebo021999]

You have 2 kind of circular:
For every column, there are rows relation to each other:
1) 26-22-15-13-11-8-3-26
2) 12-13-15-20-22-26-30-12
Try to break these circular first

 

[DaRook1e]

Hi bebo,

thanks for your reply. It was enough to remove the link of the deal flow to the total assets (26-3) and Excel handled the remaining circular references pretty good. What I mean is they converged to a stable result.

In my model I could not remove the circular references completely and need to manually copy/paste one line to make Excel calculate correct. Do you see a way to handle circ references in the posted example using if statements?