I was in banking for a year and one of the things they stressed when teaching me how to model was how I should never use circular references in my model. They said they make models slower and harder to follow. However, in my new job, I've seen multiple models from other banks and they all use circular references, particularly when modeling debt.
So, are circular references necessary to simplify your work or are they just a result of being lazy?
| +70 | Any tips for sleeping well? | 37 | 3h |
| +59 | Wealthy Parents / Jaded | 21 | 4h |
| +44 | Venezuela Debt Restructuring - LAZ vs CVP | 16 | 12h |
| +40 | Background check issue IB Full Time | 22 | 19h |
| +39 | How are hours rn for SA (interns) | 23 | 7h |
| +27 | Investment Banking in Mexico | 7 | 17h |
| +26 | HL vs WB | 19 | 1d |
| +24 | MM bottom bucket bonus | 9 | 1d |
| +24 | UBS GIG, LevFin, FSG Interns working Sundays and 85 hour weeks their first week? | 11 | 1d |
| +21 | Excel macros for QOL formatting | 0 | 1d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
It's not possible to have a proper operating model with debt/interest/cash and not have a circularity.
Think about revolvers: your revolver balance is a "plug" based on if you have negative cash. Cash is on your CFS, CFS begins with net income, which factors in interest expense, and adding to your revolver adds to your interest expense. If you add a circularity breaker (named cell, put an IF statement in interest expense/income to where if named cell = 1, it uses circularities, if it = 0, interest expense is 0), you should be able to avoid most problems with circulating.
Calculate interest expense off BoP debt balance. Breaks the circularity plus you're being more conservative in your case.
Circularities suck balls when you're running tons of sensitivities / data tables as you do on the buy-side. If any banker we hire sends us a model with circularities we ask them to take it out.
True, my reply is on the assumption that you’re using avg balance for the interest calc. At any rate I feel like a circ switch and changing the second half of the if statement to base it off of the BoP balance would also solve the issue no? Might marginally slow you down but should work.
Circularities slow the model down immensely when you have data tables. Just do it off BoP Balance.
You can also calculate from the last year in your model back to today to see how much cash you'll need in your revolver. That way you calculate interest expense with future years' values (not the present year) and you will not have any circularities.
I just think it's very annoying every time you press F9 or open another excel you get the same message from Excel telling you your model is shit.
I have no idea what you are talking about OP. They're commonplace in IB (every firm I've been at uses a model with circular references) and none of us ever seem to have problems with them.
Or just don’t make operating models and build down to Ebitda. Problem solved
The best way is to use algebraic solutions (for such as committed debt with a target equity/ capital ratio + idc/ commitment fees, upfront fees; DSRA, Sculpting, etc)
For Revolver the best way to get rid of circularity issues is to build models on the monthly basis, where interest could be recorded as payables at the month of accrurals (calculated on the average debt balance) and further paid next month (actually it is quite similar to what happens in reality).
Such a logic is also applicable to taxes (that are dependent on interest). Taxes are also recorded as payables and are actually paid in the future with no impact of other cashflows that occur during the period of payment
Although algebraic solution is also possible, you have to write down all potential scenarios for revolver movements (like revolver is repaid, partially repaid, repaid in full, revolver is not required, new drawdown is required, tax shield is utilised, etc.). For each of this scenarios a certain equation has to be solved. And multiplied by the binary flag of the current scenario.
Just a brief example. After all operating cash flows are calculated (CAPEX, Working Capital, Ebit), it is possible to calculate cash surplus / requirement (deficit) before Revolver. It is worth mentioning that in order not to be trapped into circularity issue connect with income tax tax rate should be multiplied by Ebit (not by EBT, as the majority of modellers does), but in the following case let’s just don’t use taxes.
So, for this very example let’s say that opening debt (revolver) balance is $100, then opening excess cash is obviously 0 (as financial modeling algorithms - if revolver is built correctly - assumes that all excess cash and cash flows are used to repay revolver and it is not possible that closing revolver balance and closing cash balances are simultaneously greater than 0).
Let’s assume that our operating cash flow is negative and cash requirement before debt equals to $30
In this particular scenario it is obvious that we have to attract more debt and thus other scenarios are not active (debt attraction binary flag = 1, other flags = 0)
So, Debt Opening Balance = $100
Cash Requirement before Debt = $30
Drawdown = (Cash Requirement (Deficit) before Debt + Interest Paid)
Interest Rate = 8%
Closing excess Cash Target = $0 (as we assume that it we do not attract more debt than we require
2 * ((Debt Opening Balance + Debt Opening Balance + DrawDown) * Interest Rate ) / 2 = Interest Paid
Debt Closing Balance = Debt Opening Balance + DrawDown
Let’s say Drawdown = X, then
(100 + 100 + X)/2 * 8% + 30 = X
And X = $39,58
I actually published free template on eloquence 7 years ago. So you can download and use it if you would like.
(as I am not able to post links here, please find my page on eloquence (dot) com and download it from there)
It contains two minor mistakes although, that were corrected but I did not upload the correct version.
I will hopefully upload it soon if I have time)
Ut aut eligendi nihil omnis illo deserunt. Inventore eum ut in ut minima. Optio quasi labore quaerat voluptates autem.
Ratione ullam assumenda consequatur explicabo. Veniam consectetur voluptatem molestiae qui autem. Sapiente facere expedita eum sed omnis placeat. Qui sit natus officiis sint commodi soluta ducimus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...