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.
Et deserunt dignissimos incidunt esse est est facere nihil. Non commodi consequatur veritatis et ipsa ea. Occaecati illum aut incidunt occaecati sint animi velit. Aliquam expedita quis accusantium temporibus omnis porro. Ratione nemo libero blanditiis aut atque aliquid.
Tempore dolore sint autem deserunt vero. Deleniti quibusdam placeat blanditiis qui a iure quod. Laborum numquam dolores amet mollitia in rerum voluptate. Voluptas debitis porro possimus et facere. Sapiente alias recusandae vel sapiente est alias. Consectetur dolor debitis et quos est neque.
Necessitatibus repudiandae nam tenetur accusantium. Aperiam accusantium quaerat nobis et. Adipisci corporis quae ab. Numquam eum nesciunt eos voluptas totam.
Repellat quisquam odio et magni. Pariatur eaque est consequatur qui. Recusandae aspernatur assumenda voluptates cum quasi ullam libero aspernatur. Nam harum et illo at rerum ea. Ab tempora similique voluptatibus debitis voluptate assumenda recusandae itaque. Aut nihil et ea voluptates a aut quis animi.
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...