Financial Modeling Quick Lesson: Simple LBO Model (1 of 3)

hello and welcome to another lesson of financial modelling and valuation brought to you by Wall Street prep check us out on the web at www.att.com/biz this file we're going to be working through today today we're going to talk about doing a leveraged buyout of a company and how to analyze that now you might think an LBO is for the masters of the finance universe but that's not necessarily the case if you have ever bought a house or considering buying a house buying a home is an LBO you go to the bank you take out a mortgage that's your debt you put in a little equity over time you're working presumably and paying down that debt over time and then you sell your house few years later hopefully your house is appreciated in value but when you sell your house you've paid down the debt you sell your house you take those proceeds you pay off the remaining balance of the debt if there is any and then you get to keep the rest and you can generate huge returns potentially that way so really buying a home is no different than an LBO but we're going to go ahead and build a hypothetical LBO model just kind of a quick one 30,000 foot view so you know how it could be done right so we're going to buy this fictitious company where the financial sponsor that's our perspective we're going to buy out this fictitious company XYZ and we're going to buy them on the last day of 2012 just for simplicity and you can see I've got some comments over here as well so download the file and you can follow along with the comments okay so we need some select operating a financial statement theta we have some assumptions about the lbo we have just kind of a very simple financial model a very simple debt schedule and then we're going to answer these six questions okay again we're going to buy out this firm the last day of 2012 they're trading at $30 currently 500 million shares outstanding of course share price time shares gives us market cap fifteen billion dollar company now that company currently has some debt on its books but 600 million dollars worth but they also have some cash a hundred million dollars worth so will calculate net debt and of course the rationale behind this net debt is debt - cash the rationale being did any cash sitting around on a company's books on the balance sheet can be used to pay down debt and if we buy this company we have to buy out its equity we typically have to pay off its current debt but when we acquire the company we also get that company's cash okay so it makes some logical sense that we can when we get that company's cash we can use that cash to pay down the debt and then we'll also calculate enterprise value versus enterprise value commonly defined as market cap plus net debt it's the value of a firm's operations here's our select some historical financial statement data last 12 months LTM revenues 6 billion eBay daf 2.4 and free cash flows 408 million four hundred eighty million dollars what we can do right now we can calculate our enterprise value to eBay da multiple this company is currently trading at six and a half times it's eBay dies its trading enterprise value six and a half times even though just a common commonly quoted valuation multiple now as the financial sponsor we're not going to hold on to this investment forever we're going to buy out the company and our holding period for most financial sponsors typically three to seven years into the future so we will assume that we're going to exit on the very last date of 2016 that's when we're going to sell off the business or maybe two strategic acquirer or maybe we'll take the equity public again and IPO it but we're going to exit the business at the end of 2016 now we need to know how much debt we can actually borrow and now this is based on current debt market conditions okay you know what's what's going on with with debt markets how much our lenders willing to lend to us based on the riskiness of the proposed transaction and the company we want to borrow as much debt as possible but not so much that we put the company into financial distress okay now this this debt capacity is going to change over time it's just as you know what's going on the debt markets change but we're just going to assume that we can borrow six times Ivica there's also known as a leverage ratio additionally we must maintain some kind of minimum cash balance on hand just in case of a rainy day and we're going to assume that that's fifty million dollars maybe maybe our projections don't turn out quite as rosy as we believe that they will so we want to have some kind of minimum cash balance to maintain you know our obligations pay our employees and so forth we're going to assume that that's fifty million dollars we're going to exit in 2016 like I mentioned and we're going to exit at the same multiple we aren't going to enter at okay we're we can buy the company for six and a half times Eva da we're gonna sell it for six and a half times even though okay generally speaking the assuming expansion of multiples is it's that's a little aggressive of an assumption so we'll just say okay we'll sell it for six and a half times even same that we can buy the company for and based on the riskiness of this transaction we the financial sponsor must earn twenty five percent over our holding period each year versus interest rates are calculated annually we must earn twenty five percent every year this is our internal rate of return our IRR okay and it's high because of the higher risk of this transaction when we're borrowing a bunch of dead so it's going to make the the riskiness of this transaction a little bit higher so of course the higher the risk the greater we need the greater potential returns we need its financial sponsor so we're going to say that that's 25% required rate of return and what we'll do quickly we'll build up some financial statement information we're not going to build a complete three statement financial model of course we just need a little bit just doing a high-level overview of lvu so for simplicity we're going to assume revenues will grow at 10% each year so they had six billion in revenues 2012 and we're going to grow each revenue each year's revenues by 10% again just copy that out we need a sum forecast of financial statement data to calculate our potential returns once we exit and for simplicity the company's historical even down margin was 40% for simplicity we're going to keep it constant as well of course margin being something as a percentage of sales calculated again 40 percent of revenues and then I just copied that out to the right we also need some cash flow metrics as well again we're not going to build a full cash flow statement we're just going to keep again everything consistent we're going to assume that cash flows after required debt pay down are going to come in at 8 percent of sales every year make that calculation then copy it out of course we we don't have a full-blown balance sheet we really don't need it we're primarily concerned with cash and debt for this analysis of course our the historical cash the company had on hand was 100 million from our earlier assumption and then 600 million in debt straight from our earlier assumption and then our net debt is already calculated so what we'll do next we'll build out our debt schedule and our abbreviated balance sheet but we're going to do that in the next lesson so stay tuned for part 2

Channel: Wall Street Prep

Views: 199,183

Rating: 4.9049234 out of 5

Keywords: lbo model, leveraged buyout, private equity, financial modeling, wall street prep, lbo modeling, lbo template

Id: paBl20MIXxQ

Length: 8min 55sec (535 seconds)

Published: Mon Mar 18 2013