Circular Reference in LBO Model
Undergraduate student here working on replicating the illustrative LBO Model in Rosenbaum and Pearl's Investment Banking book as practice. I'm having issues with the circular references - the specific section laid out below (I'm sure most of you know what I'm referring to, but just want to be clear)
Net Income on Income Statement --> first line of Operating Activities on Cash Flow Statement Operating & Investing Activities on Cash Flow Statement --> Cash Available for Debt Repayment on Debt Schedule Cash Available for Debt Repayment on Debt Schedule --> Affects Beginning and Ending Balance of Term Loan B on Debt Schedule Beginning and Ending Balance of TLB on Debt Schedule --> Interest Expense (Interest Rate * Average Balance of Term Loan During Period) on Debt Schedule Interest Expense on Debt Schedule --> Interest Expense on Income Statement
I've gotten to calculating the interest expenses back to the income statement, but whenever I complete the circular reference, I'm getting the circular reference error that stops Excel from calculating. (Debt repayment schedule is extremely accelerated as of right now due to inflated cash available for debt repayment - because of the inflated Net Income that does not reflect interest expenses yet).
Is there a certain order of steps to build out the model in order to have the formulas in the model calculated correctly? Or do I need to avoid the circular reference some way?
Thanks, thought it'd be smart to get familiar with issues like this during a college winter break instead of at 2:30 am at some point next year.
You're doing it right if you get circularity issues due to interest expenses. This is exactly what should happen.
First, check that circularities are enabled in your Excel (Excel 2007>Office Button>Options> Formulas Tab> Set Iterations to 100). You can then create a circularity toggle in your model to flush out errors. Multiply the interest expense line by a fixed input cell which can take values 0 or 1. "Break" the circularity by inputting 0 and then 1.
Another circularity issue you might come across someday is when using an unlevered/relevered beta in DCFs. While most textbooks just use the market cap as a proxy for Equity to delever/relever the beta, a more proper way of solving it should use the Equity Value which results from the DCF, which itself depends on the WACC and thus the beta.
Autem molestiae nam officia officiis iste assumenda in. Rerum ducimus soluta et earum dolorem. Dolor rem commodi ducimus impedit reprehenderit tempora dolorem. Sapiente excepturi placeat animi adipisci.
Est aut velit quidem voluptatem fugit et ipsa. Voluptas libero repudiandae sit aperiam ullam possimus porro officia. Eveniet voluptatibus sunt earum illo dolorem omnis placeat.
Id quod aperiam sapiente fugit nulla culpa qui esse. Sequi sint architecto eum in culpa sit ut et. Consequatur provident quaerat qui sint facilis.
Accusantium consequuntur et ipsum expedita nisi temporibus autem. Eligendi voluptatem voluptatem suscipit. Qui nihil quis culpa nemo nihil quidem debitis reiciendis. Sint sed ipsam fugiat at amet sit ab fugiat. Error sequi omnis minus laborum ducimus amet. Sunt aut amet earum vero non vel sunt reprehenderit.
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...