In this tutorial, part 1 of a 2-part series on LBO case studies, we’ll look at what you might expect in a case study modeling test given at an assessment center or at the end of your internship at a bank or PE firm. You have to use an LBO model for valuation purposes in this case study, and you’ll start by setting up the model in this segment. By http://breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers" Table of Contents: 1:59 Case Study Overview 5:00 Strategy for Tackling the Case Study 9:39 Step 1: Assumptions and Sources & Uses 12:22 Step 2: Income Statement 13:12 Step 3: Cash Flow Statement and Debt Repayments 16:42 Step 4: Interest Expense and Linking the Statements 21:22 Step 5: IRR and Cash-on-Cash Multiple Lesson Outline: Download the documents linked to under this video to see the case study instructions and understand this lesson (and the follow-up lesson after this one). Key Points: With a time-pressured case study like this, you have to SIMPLIFY and cut corners where needed. Here, for example, we can simplify by only including 3 years of projections, skipping a complex debt schedule and Balance Sheet Projections, and consolidating the assumptions and Sources & Uses schedules. However, there are also a few tricky parts to be aware of – first off, despite the instructions, you do need to assume a purchase EBITDA multiple so that the model works initially. Also, you need to include more of a “real” investor returns calculations area, and you have to go a bit out of order because of the specific assumptions here. Step 1: Assumptions and Sources & Uses Assume a baseline purchase multiple just to get a price to use in the model, and then base the Uses side on this purchase price and the associated fees. Then, fill in Sources using the x EBITDA assumptions given for each tranche of debt, and back into Investor Equity by taking Total Uses and subtracting the debt sources so far. Step 2: Income Statement On the Income Statement, project revenue, EBITDA, and D&A; according to the given assumptions, and calculate Pre-Tax Income and Net Income based on the tax rate assumption. Step 3: Cash Flow Statement and Debt Repayments Then, start the CFS with Net Income, add back the non-cash charges (just D&A; for now), and factor in the changes in Inventory, AR, and AP. Since you don’t have a Balance Sheet, take a shortcut and just use the change in revenue times the relevant percentages to get these. Inventory and AR increasing will decrease cash flow, and AP increasing will increase cash flow. Calculate FCF by taking all of that and subtracting CapEx, and then include the amortization of Term Loans A, B, and C to determine the Net Change in Cash. At the bottom, the Cash Balance each year equals the Cash Balance from the prior year plus the Net Change in Cash. Step 4: Interest Expense and Linking the Statements First, track how Term Loans A through C change each year by subtracting the annual amortization. Hold the Second Lien constant. Keep the PIK Loan blank for now. Calculate interest expense using the BEGINNING balance (i.e. the one from the end of the previous year), not the average, to avoid circular references. Do this for all the debt tranches, as well as the interest earned on cash. Use LIBOR and the LIBOR spread, or the fixed interest rates for the Second Lien and the PIK Loan, to do this. Then, go back and make the PIK Loan increase by the PIK Interest each year, and then go back and link the Net Interest Expense on the Income Statement (it should be a negative there). Finally, make sure you include the PIK Interest as a non-cash add-back on the Cash Flow Statement now. Step 5: IRR and Cash-on-Cash Multiple Calculate the IRR and cash-on-cash multiple the normal way at the bottom; Investor Equity should always be a negative in Year 0, and you calculate the Exit Enterprise Value in Year 3 using the EBITDA in Year 3 and the EBITDA exit multiple we were instructed to use. Subtract Net Debt to calculate the Exit Equity Value. For now, Investor Equity = Exit Equity Value. Sum up all positives in the Investor Equity line and divide by the sum of all negatives there to calculate the cash-on-cash multiple; use the built-in IRR function in Excel to calculate that. Up Next In part 2 of this series, we’ll complete the case study by building sensitivity tables and using Goal Seek to turn the LBO model into a valuation methodology. We will also answer the case study questions. RESOURCES: http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case.pdf http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-Before.xlsx http://youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-After.xlsx

• published: 17 Feb 2015
• views: 41