Circular references in LBO models
Hi all,
I have a problem with lbo models, I don't understand how they involve circular references ?
Here is a very very basic model, where you enter a debt level and your expected FCF/EBITA (lets assume they're the same) to calculate your return (i broke the link, WSO wouldn't let me post it if not):
htt p://w ww.se ndspace.co m/file/cd1xo4
The more debt, the more interests I pay, the less excess cash I have, the less I amortize my debt. There are no circular references.
Do you know what I am missing that would introduce a circular reference in this model please ? (not that I love circular references, but it seems to be the base of any lbo model, and I can't see where they come from).
In advance, thank you for your help !
you can create an lbo model without circular refs if you calc interest based on prior ending balance. they come into play when you calc interest on average balance outstanding, i.e. (prior ending balance + current period ending balance)/2. Circular BC your interest is dependent on ending balance, ending balance is dependent on cash flow available to amort debt, and that cash flow available to amort debt is dep on interest. look at any basic lbo model template and you'll see that.
Can I set up a function about interest rate? for example, since debt principal payment is determined by EBIT and interest expense, I can make x= interest expense, and y=principal payment=a(EBIT-x) . Also make D0 debt balance at year=0 and D1 debt balance at year1, r=interest rate. So x=(D0+D1)r/2=(D0+D0-y)r/2=(D0+a(EBIT-x))*r/2. This is a equation about x. So can we solve x aka interest payment this way? if so, why is it a circular reference?
Circularity -- Basic LBO Model (Originally Posted: 07/17/2010)
Basic problem --
Net income isn't being calculated b/c interest expense is tied to the additional debt repayment, which is tied to the CF after principal repayment, which will not run without the net income number.
What would be the best way to solve this problem (other than using the previous year's ending balances)?
Iteration. Turn it on - find a setting that works for you (# of tries / precision). (Tools-Options-Calculation iirc, not at the office now and my home computer has '07)
Figured it out 10 minutes after posting aha
I've got iterations enabled... just made a rookie mistake
And my bank is still on 03 as well. Not sure if any BB's have actually made the transition to 07+
iteration would work for this scenario. also fyi you can always use solver (or Goal Seek) to solve linear/convex programming problems, where you need to find inputs that satisfy a certain known outcome.
iRX is right. You can find sample lbo models online. A good model with detailed explanations is on macabacus.com but is very in depth
excel-circularity help (Originally Posted: 07/06/2008)
I am trying to model an LBO and when I try to link my change in cash from my CF statement back to cash on my BS my 2007 version of excel comes up with a warning of circularity. It allows me to ignore the warning and link it anyway but when I do the number shows up to be zero. How do I get around this problem so I can finish my model?
Thanks
Manual calculation, F9.
didnt work, it still shows zero for some reason
Your iterations aren't on...
Tools Options Alt+i
set iterations to about 100 usually does it.
Iterations. Very basic setting requirement for modeling. Also make sure to have your basic add-ins for calculations of IRRs and such, although this may differ according to formulas.
Shouldn't a good model avoid circularity?
A LBO / Cash Flow model will always be circular. The interest that you pay affects the cash flow available to retire debt, and the cash flow available to retire debt affects the interest that you pay.
Sometimes you have to go through and delete problem cells then undo the deletes and it will work. Can be very frustrating. Have spent hours trying to make the circular reference work properly. Try saving then deleting cells like interest expense and other items in the cash flow statement that are tied to cash then crtl+z until it is back to normal.
Thanks for your answers. I've looked at some models, that's actually the case (they take the average balance instead of just the prev. ending balance). I didn't know we were supposed to do it like that.
Circ Question (Originally Posted: 05/05/2008)
Question about Circs.
I have a problem whereby I have a model with some circs in it and sometimes when I change one of the input cells it messes up the circ and the whole thing goes to #div. When I try to hit undo it stays messed up. I usually close the file without saving and redo my work. There has to be a better way to over come this. Suggestions/help.
The logic follows how you would calc DSO and DPO. You take the average of the beg and end balance because interest is being accrued over time. If you use one or the other, you are going to over or under represent actual interest.
Change iterations from automatic to manual.
This works well in lbo models, at least the ones I do...
Replace the offending cells with constants, e.g. the number 5 and hit F9 to refresh until you get numbers replacing the #div's.
Then, go in and replace them with the actual formulas, one-by-one, hitting F9 after each one. Everything should update correctly.
Sometimes with an lbo model I'll just replace the interest expense (which is the circular part obviously) with a constant, get everything looking right and then paste in the formulas and refresh to fix.
Another easy way to fix circ errors is to include a choose function (or something similar) that toggles between using the average and beginning balance of debt to calculate interest expense. Average balance is of course the correct method, but switching to beginning balance can help to strip out a circ.
And always remember to save down/version up before hitting that sexy f9 button when you are changing something big or checking stuff. woo!
I remember in training they taught you to build a circ toggle to replace interest expense cells with hard values (zero) to fix your div'ed out model. I don't know anyone that actually builds the toggle, though. It's funny, it's obviously more efficient but I always just cut the interest expense, paste it to the side, replace with zeros, F9, then paste it back in. And I know the former pre-MBAs here did it too because it's hilarious - I see 0's to the side of the interest expense lines (outside the print area) in older models every time without fail.
Use this formula, if you have fasttrack it will automatically put them in.
If(iserror(a1/a2),0,a1/a2) will allow circs without your model reffing out.
It's been a little while, but I remember discovering an easier way with the circ interest calc in an lbo model. Often times it works to simply highlight all interest calcs, delete and then ctrl-z. Should refresh correctly.
average debt balances except for PIK interest
Dolorum totam asperiores qui consequuntur velit natus quae. Totam et nulla rerum ea quia aut culpa. Delectus eum corrupti consequuntur et iste. Sit tempore explicabo quidem impedit. Recusandae sequi cupiditate dicta facilis sint velit ea.
Unde consequuntur ut et dolore quisquam odit. Tempora magni eum nostrum quos. Similique magnam officiis dolor sit ipsa provident.
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...
Non enim molestias consequatur deleniti perspiciatis ipsum. Reprehenderit molestiae expedita voluptatum tempore illo. Qui eos temporibus hic aut dolor dicta.
Perferendis iure est nihil saepe harum rerum adipisci et. Magni aut unde aut voluptatem voluptatem voluptas quo tempore. Animi voluptatibus tempora modi et delectus quis explicabo est. Veritatis et beatae quo doloremque asperiores nostrum. Possimus dolorem molestias et enim voluptatem.
Voluptatum totam dolores dignissimos enim sint temporibus. Dolorum et consequatur quo quia molestias ab. Tempore qui voluptatem sapiente quod corporis ipsam.