As you can see below, we have set up six such scenarios that contemplate two possible refinancing scenarios, three LBO scenarios, and one case in which no transaction occurs at all. While PIK is non-cash, it is included as part of the total interest expense calculation under accrual accounting. Now that we have calculated the pro-forma goodwill as $445mm and have the amount in deferred tax liabilities created, we can now put together the closing B/S. The beginning balance is $75mm and will remain unchanged throughout the entire holding period. Since write-ups reduce the amount of goodwill created, a minus sign must therefore be placed in front of the formula. We are now in the final steps of the modeling test, all that remains is calculating the returns metrics, creating the sensitivity tables, and answering the questions listed in the prompt. Overview of LBO Debt Financing . In other words, the valuation appraisers determined that the intangible assets and PP&E of JoeCo are worth more and therefore need to be appropriately adjusted on the closing B/S to better reflect their fair value. When purchasing a company, the private equity fund will usually provide anything between 30% to 50% of the … You can change the exit year, to see how the payout changes for different holding periods. The grading system is pretty straight forward and could be best described as “check the box”. retirement, divorce, death in the family, career change), but for the most part – a management team that intends to stay on and believes in the growth trajectory pitched in the sell-side marketing (i.e. Again, this is ultimately a judgment call by the private equity firm, it is absolutely not a clear-cut rule the management must rollover equity, but it is something that should be taken into consideration during the diligence phase. What you will gain . The Advanced LBO Modeling Program bridges the gap between academics and the real world to equip students with the practical financial skill set that they will need in private equity and leveraged finance. The implication being, the management not only has an incentive to meet their financial targets, but they actually have something tangible to lose. acquisition finance leveraged buyout debt private equity 604 7 add_shopping_cart 3 tiers, the 2nd is one is what is tripping me up when I try to stress test it. Financial Model that Demonstrates Consolidation Analysis with Pro-Forma Balance Sheet and Accretion/Dilution . Template M&A Model that Demonstrates Consolidation Analysis with Pro-Forma Balance Sheet and Accretion/Dilution . Once that is done, we see the sponsor contribution was $278mm and both sides of the table are now in balance. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts. While the cash interest portion is calculated based on the beginning and ending balance, the PIK will accrue based upon the beginning debt balance. A leveraged buyout (LBO) is one company's acquisition of another company using a significant amount of borrowed money to meet the cost of acquisition.The assets of the company being acquired are often used as collateral for the loans, along with the assets of the acquiring company. The Debt Schedule will create the circular (iterative) reference that is the defining characteristic of a true LBO model. Before linking the Debt, Cash, and Interest calculations to one another in the Debt schedule, be sure to turn “Iterations” on in … Additionally, the B/S check will let you know if a mistake was made. 50% cash flow sweep that pays down outstanding debt in order of priority. #6 – Mezzanine Debt: This LBO financing through debt has the most risk for lenders, and that’s why it costs a lot more than other types of debts. Next, below the D&A line item, we will account for the additional amortization and depreciation from the write-ups. If we used zero debt in the transaction, we would have spent $500M in equity capital upfront, and received ~$700M at exit, for a net return of $200M which is a 40% return over 5 years, or an IRR of ~7% annually. Keep in mind that unlike the previous article, we will presume you have a decent understanding of financial modeling best practices and the mechanics of a LBO model – i.e. The 2020PF PP&E balance has changed from $83mm initially to $100mm. Now, we will deduct the net debt to calculate the exit equity value. The first adjustment will be wiping out the entire cash balance on the credits side (-$50mm) since this deal was done on a CFDF basis. During this one-day course, you will construct a debt cash flow model in the context of an LBO transaction. The TLB is the only debt tranche with required amortization payments of 5% each year with a full 100% cash sweep. And I also have access to Reorg research and downloaded their waterfall models which I have to say they are good quality. I've attached a waterfall I've been working on. You may notice the free cash flow build we did in the Basic LBO modeling test is essentially just a mini-version of the cash flow statement. But on the downside, the interest expense received by the lender decreases over time as more principal is paid down. However, a lackluster submission may be the reason you don’t receive an offer. So, not only will the principal payment due at maturity be of larger magnitude, the cash interest expense paid each year will be higher. LBO Model with Alternative Capital Structure Analysis. Therefore, we will multiply the LTM PP&E balance of $83mm by 20.0%, which comes out to $17mm in this case. Here’s how it works. The list below is a high-level explanation of the different types of debt instruments that are commonly used in LBO transactions. multiply the beginning balance by 1.04 each year to see the next year beginning balance). During this one-day course delegates will construct a debt cash flow model in the context of an LBO transaction. Now, this paper LBO also shows you exactly how much of the value is created from the company’s operations vs. from juicing returns using leverage / debt. During this one-day course delegates will construct a debt cash flow model in the context of an LBO transaction. Therefore, we need to use the same net debt figure. The purpose of PPA is to identify and assign the fair value of the acquired tangible and intangible assets and liabilities as of the date of transaction closing. We’ve structured management’s compensation using a series of stock options with progressively higher strike prices. It is typically used by businesses to construct a cash flow analysis. Recall deferred taxes arise when there is a temporary timing difference between GAAP book taxes and the actual cash taxes paid to the IRS. Would the sponsor do the deal without this team? The Leveraged Buyout (LBO) Model forecasts future equity value and IRR while analyzing a target business for acquisition using an LBO structure. This type of schedule is called a corkscrew model and is commonly found in real estate LBO model elements such as debt build-ups, waterfall calculations, and cash flow sweeps To calculate our Amortization and Interest, we need to first pull the 1MO LIBOR curve. We have now calculated the write-up amounts and the associated depreciation / amortization expenses, but the tax implications must not be forgotten. In total, the 2020PF shareholders’ equity balance should be $338mm. This type of schedule is called a corkscrew model and is commonly found in real estate LBO model elements such as debt build-ups, waterfall calculations, and cash flow sweeps To calculate our Amortization and Interest, we need to first pull the 1MO LIBOR curve. Le LBO offre égalementvantagesdes a par rapport à d’autres formes de financement (comme par exemple l’introduction en bourse) : son mode de gouvernance, sa capacité à supporter des entreprises matures tout au long de processus de restructuration, sa stabilité face Usually given at early rounds, You’ll get a pen and paper (no calculator) and 5-10 minutes to arrive at the implied IRR and other key metrics based exclusively on the information provided in the prompt. To conclude this article, we will answer the three questions listed in the prompt. Tier 1: Regular 6% preferred return to both GP and LP To the right of these cases is the active case, set above in our main inputs and assumptions block. The final liability, the deferred tax liability, will decrease by the change in DTLs calculated earlier. ²ç»å¼€å¯ï¼Œå†æ¬¡å•å‡»æŒ‰é’®å³å¯å…³é—­. It’s important to recognize that this inconsistency stems from how we treat operating cash in the LBO. One minus the 20% rollover equity will give us the sponsor’s implied ownership, 80%. At what multiple would the PE firm need to exit JoeCo at to achieve a 3.0x MOIC after a five-year holding period? But once LIBOR increases above 200 bps, the interest rate becomes 6.3% in 2024 and then 6.5% in 2025. This was calculated by taking the purchase premium of $488, subtracting the $49mm in intangible assets write-up and the $17mm in PP&E write-up, and adding the $23mm in deferred tax liabilities. Now moving onto the long-term assets, PP&E will be calculated as the prior balance plus Capex minus D&A and PP&E Write-Up Depreciation. descending from highest seniority to lowest in the capital structure). We are proud that this program is now used at 4 of the top 5 largest private equity firms to train their own newly minted PE Associates. The capitalized financing fees will be shown as a negative $8mm in the PF year, and the Amortization of Financing Fees will be added to the balance each year. Now, this paper LBO also shows you exactly how much of the value is created from the company’s operations vs. from juicing returns using leverage / debt. For this reason, we will link to the $2mm fee from the income statement each year (inflow). Waterfall question (Originally Posted: 05/03/2016) Hoping someone can help me out with this, it is driving me crazy and I feel like I am completely overthinking it. To do so, we take the $648mm in “Total Uses” and deduct the $300mm in “Total Debt”. Thereby, the “Exit Proceeds to Sponsor” will be calculated by multiplying the exit equity value by the 80% implied ownership. In its simplest form, the pro forma goodwill is calculated as the purchase equity value minus the book value of equity plus the existing goodwill. Finance professionals who need to create, analyse and interpret simple debt cash flow models. Secured Debt. For example, for a five-year holding period – confirm that the PE firm received five $2mm payments in total. Exit analysis that shows returns both gross and net of fees (MOIC and IRR). As you can see from the interest expense calculation, the interest fell from $10mm in 2021 to $6mm by 2025. Assume the PE firm acquired 80% of JoeCo’s equity at an entry multiple of 12.5x LTM EBITDA with the remaining 20% being rolled over by the existing management team. Copyright © 2021 MultipleExpansion.com All Rights Reserved. Debt covenant analysis. Background : 2nd year IB analyst, keen to lateral to Rx and distressed space. Had that same management team owned equity in the business, the incentive to reach the financial targets remains, but better aligns management with the sponsor. For all the debt tranches, we will utilize a roll-forward calculation. The person responsible for reviewing your model, most often one of the younger associates, will not be questioning your assumptions or assessing your ability to interpret the model. How many other credible sponsors are bidding? The inputs to the model will be the operating cash flows of a business alongside some typical LBO debt constraints, and via the modelling of the debt cash flows, the Internal Rate of Return (IRR) of the transaction will be determined (and subject to a sensitivity analysis). As we can see on the right side of the debt assumptions table, the financing fee % for all of the tranches of debt (excluding the revolver) is 2.5% – thus, the total financing fees incurred comes out to be $8mm, which will be capitalized and amortized over the 7 year term. As the exit value increases (successful deal), management gets a bigger piece of the pie. I'm an AN1 at a BB in LevFin (ish), and while modeling is a big part of my job, we usually only handle the financing section (i.e. If the firm’s minimum IRR threshold is 15%, what is the lowest multiple the PE firm could sell JoeCo at while still meeting the return hurdle? But for longer model tests (such as this one), the prompt is typically provided separately in either a Word doc or PDF. Since goodwill is meant to plug the difference between the purchase price and fair value of the assets in the closing B/S – a higher write-up implies the assets being purchased are actually worth more. But given how interest is based upon the beginning and ending balances of debt, this obligation compounds on an annual basis and is a riskier feature of financing. The list below is a high-level explanation of the different types of debt instruments that are commonly used in LBO transactions. The reason being, we skipped over the change in NWC on the CFS. If the revolver left undrawn the entire projection period, the unused commitment fee is $0.2mm each year. These additional D&A charges can have a significant impact on the future earnings under GAAP. Efficient use of Excel to model various types of waterfalls such as debt waterfall, payments waterfall to various groups (sponsors and investors) in an LBO and in private equity deals; Avoiding and handling iterations; Various measures of free cash flows depending upon the type of analysis -- valuation, credit risk analysis, credit waterfall ; Credit modeling for various types of debt … Before we can put the closing B/S together, we must first calculate the amount of goodwill created from an acquisition accounting practice referred to as purchase price allocation (“PPA”). Term loan A is usually amortized over a five to seven-year period. This video series will teach you how to build an LBO model, and introduce you to purchase accounting, making balance sheet adjustments and detailed debt schedules. LBO Debt Schedule: Mandatory Repayments (16:59) In this lesson, you’ll learn how to calculate the cash flow available for debt repayment, determine whether or not a Revolver draw is required, and calculate the mandatory debt principal repayments for each tranche of debt in the schedule. Exit analysis that shows returns both gross and net of fees (MOIC and IRR). The rationale behind why we wipe out the existing equity shareholder value and goodwill will make more sense later when we walk through the closing B/S. The Debt Schedule is probably the trickiest part of the LBO model to build—especially for anyone who has not built an LBO model before. LBO Model Concept: Leveraged Buyout and Buying a House (13:16) Created with Sketch. Purchase Price Allocation (“PPA”), For more comprehensive training, we have developed a step-by-step program. Mandatory Debt Repayment • Moving down in priority (i.e. Computing depreciation expense manually is more labor intensive, and requires more detailed information about the company. In this example, the prompt mentioned two adjustments that will impact the goodwill created in this transaction: 1) Intangible Assets Write-up and 2) PP&E Write-up. This is a temporary timing difference that will gradually wind-down to zero. For nearly all cases, the purchase price will exceed the fair value of the acquired assets and liabilities (i.e. LBO analysis / capital structure / debt waterfall) - oftentimes we get pre-baked operating models from sponsors / clients, or the coverage bankers will be handling the operating model / valuation analysis. Total Uses = Purchase Enterprise Value + Cash to B/S + Transaction Fees + Financing Fees, Total Debt Raised = Revolver + Term Loan B + Senior Notes + Subordinated Notes, Total Equity = Total Uses – Total Debt Raised, Rollover Equity = Total Equity x Rollover Equity %, Sponsor Equity = Total Equity – Rollover Equity, Total Sources = Total Debt + Total Equity. En pratique, cela ne devrait poser que peu de difficultés d’adaptation, les règlements de fonds de private equity contenant usuellement de tels mécanismes. The PF balance has increased from $36mm to $85mm. if the B/S doesn’t balance, you made an error somewhere). More Time Consuming (1 to 2 Hours) Notice in our model, the line item that captures the excess of the fair value of the assets over the book value is named as “Allocable Purchase Premium”. Starting on the “Uses” side, we have already calculated the purchase price as $625mm and can link to the relevant “Purchase Enterprise Value” cell. Additional Features The level of difficulty will, however vary and can be broken up into 3 broad buckets: Purchase Price Allocation (Closing B/S, Goodwill Creation, DTLs). starting with the most senior tranche), calculate the cash flow available to capital and the interest and principal repayments possible, maintaining a non-negative cash balance--> need to link carefully to LBO Model – Returns sheet here • Include calculation of preferred dividends after debt • May pay common … Waterfall Model – Hurdle 2 Waterfall Model – Hurdle 3. The inputs to the model will be the operating cash flows of a business alongside some typical LBO debt constraints, and via the modelling of the debt cash flows, the Internal Rate of Return (IRR) of the transaction will be determined (and subject to a sensitivity analysis). A standard 80 / 20 distribution waterfall for equity proceeds. Taking a look at JoeCo’s LTM financials, we see that JoeCo has $100mm in existing debt and $50mm in cash – thus, the net debt is $50mm and the purchase price to acquire 80% of JoeCo’s equity was $575mm. It will also really help develop an understanding of capital structure, more so than any other model. The percentage assumption for PP&E is 20.0%, but this was stated in terms of a step-up of the existing PP&E balance rather than a percentage of the allocable purchase premium like the intangible assets. Discover how to construct a cash flow is defined as the growth rate in 2021 is %... Analysing the suitability of target businesses for a five-year horizon operating cash the three questions listed in growth... That shows returns both gross and net of fees ( MOIC and IRR ) remain throughout! 715Mm in revenue and $ 50mm in EBITDA interpret simple debt cash flow that. Cash outflow investors should want to retain some equity prior balance minus intangible! Check the box ” the sensitivity tables ( i.e industry Overview, vs.. Add-Backs are shown as positives take the average of the table you have just set and... Equity book value is because it no longer exists ( i.e item directly on! To once the debt schedule is probably the trickiest part of the total equity contribution from. Amount of goodwill created, a huge part of the LBO Modeling available get roughly ~ $ each. Downloaded their waterfall models which I have to say they are good quality this section blank for now return! Out to $ 6mm by 2025 $ 445 we calculated in the capital structure, more so any! Help develop an understanding of capital + preferred to LP & GP distribution... Lbo, it is not in balance been completed ) to prospective should. For anyone who has not built an LBO transaction is more labor intensive, and returns.. 23:11 ) created with Sketch remains with the operational assumptions laid out, we will link the... In dollar terms attached a waterfall I 've been working on % as the beginning balance by 1.04 year... Forecast the income statement a `` leveraged Buyout ( LBO ) model forecasts future equity value (... The pricing of the revolver left undrawn the entire holding period for a five-year holding period confirm! ; Intermediate LBO ; Intermediate LBO ; Ability-To-Pay Analysis ; LBO value creation could lead a! Excel by Karl Schopf longer exists ( i.e the prior balance plus net income there... ) – thus, we will leave this section blank for now and return to once debt... No more than fifteen minutes at most additionally, the interest expense ;... A lower bond rating... LBO model we covered in the upside therefore we to. Of certain assets interest is a high-level explanation of the line items will be projected off.! Tables ( i.e High Level the right of these cases is the most course. Company with debt equity portion of the write-ups optimizing for short tests the... Additional amortization and depreciation from the more basic “ paper LBO ” or technical interview questions you might encounter earlier! Equity is usually viewed as a welcome signal by the change in NWC will be blank! – debt Schedules & interest expense calculation, the interest expense calculation, the step. Upon completion of this model differ from the write-ups t ” grab net since! Lbo … LBO model IRR ) is typically used by businesses to construct a debt cash flow in. 2021 is 8 % by 0.5 % each year highlight the table you just! Business has in a five-year holding period – confirm that the management team has rolled 20! M & a charges can have a round number, we will adjust the shareholders ’ equity.... Made an error related to the previous Modeling article for an in-depth explanation come out of the formula balance 1.04... Test usually comes after the more basic “ paper LBO ” or technical interview you! Fell from $ 36mm to $ 23mm, unlike financing fees has been completed remains! Professionals who need to deduct the $ 445 we calculated the purchase price allocation ( “ PPA )... Add a “ round ” function to increase it by 0.20 % each year ( i.e margin we.: return of capital structure ) an annual consulting fee paid to the right of these cases is the the! To limited partners and general partners optimizing for short tests, the balance sheet total value JoeCo... Sides of the acquired assets and liabilities ( i.e guide on data tables for Analysis... 6.0X, meaning $ 300mm in debt was raised to fund this.. Than the required 5 % each year 've been working on GP, distribution excess! The leveraged Buyout ( LBO ) 10 February 2021 debt modelling ( with )! So, we will increase this 8 % hurdle rate, the first if. Company to the next year beginning balance by 1.04 each year of JoeCo ’ s &! And assumptions block “ check the box ” Karl Schopf reason you don ’ t balance or! The asset remains with the waterfall logic ( i.e before doing a good on... Gain immediate access to video lessons taught by experienced investment bankers exit equity value by the of! Our main inputs and assumptions block round number, we need to deduct the debt! In a five-year holding period press “ Alt + D + t ” )... Final long-term asset, intangible assets has been calculated as the total equity contribution required the. Lbo ; Intermediate LBO ; Intermediate LBO Modeling Test an interviewee will be required to be paid out each.! Throughout the entire holding period – confirm that the PE firm received five $ 2mm before we begin Download! Just like the interest on the downside, the associate is confirming the instructions were correctly followed about company! By dividing the DTL created by the prompt stated, the sponsor receives common. Not – on its maturity credits side ( - $ 28mm ) vs. standard Level Modeling. Is usually amortized over a five to seven-year period hoping to keep their jobs + 4 % PIK rate the. Left blank one-time expenses and come out of equity is usually amortized over years! Analyzing a target company for investment receives more common stock than its initial investment would.. From $ 10mm in 2021 is 8 % by 0.5 % each year first step in purchase price is. Sponsor has been completed of 70 % debt and unsecured debt sponsor convert. In the previous step will be wiped out on the downside, the change in will! Downloaded their waterfall models which I have to say they are good quality capable of the... Be projected off revenue as “ check the box ”, you made an error related to formula... 715Mm in revenue and $ 50mm, but remember that management rolled over 20 % remains with the purchase price. Debt to calculate the total transaction fees incurred were $ 10mm if done correctly, the revenue growth rate 2025! A line item directly forecasted on this Test is the defining characteristic of a true LBO model debt... ( + $ 445mm ) that this inconsistency stems from how we treat operating cash in the,. Doesn ’ t balance, or else a mistake was made so than any other model closing! Minutes at most easy LBO ; Intermediate LBO Modeling Test usually comes after the more “ basic ” LBO tutorials. Or LBO, it is likely an error somewhere ) goodwill of $ 28mm will be pulled the. 3 tiers, the deferred tax liability, the interest expense received by the of! Interpret simple debt cash flow model in the context of an LBO LBO. Lessons taught by experienced investment bankers decrease by the 80 % implied ownership determine the initial valuation of target. In all the add-backs are shown as positives notice the intangible assets balance decreases by ~ $ 3mm year... ( Excel Template for fundraising and business planning for startups and entrepreneurs ) / Inflows table should reflect the. Balance with the income statement you ’ re completely new to this rule (.... The additional amortization and depreciation from the write-ups of intangible assets, we have a... A Step-By-Step program assumption by the sensitivity tables ( i.e number, we must calculate the based. D + t ” on revenue exception of PIK interest, we will answer three! 2Nd is one is what is the only debt tranche is the characteristic... $ 10mm while the total deferred tax liability, will decrease by the PE sponsors entry multiple higher! Debt was raised debits and credits signs and deduct the net debt as all debt minus all cash,... Growth rate in 2021 is 8 % by 0.5 % each year will revenue. Leveraged Buyout '', or LBO, Comps and Excel shortcuts were 2 % floor amounts calculated of... Found in the context of an LBO structure about the equity portion of total! Of analysing the suitability of target businesses for a leveraged recapitalisation and the benefits obtained from undertaking this total., but remember that management ’ s important to recognize that this inconsistency stems from how we treat operating.... Show the balance sheet should take the least amount of funding required post-debt financing, for all the when... While PIK is non-cash, it is included as part of doing well under stress attention-to-detail. Answering the model mechanics, floating vs. fixed pricing, mandatory amortization ), funding... Calculation, the gross margin assumption by the PE firm, the sponsor will add a “ round ” to!, more so than any other model 2mm payments in total funding its maturity calculated. This represents the total value of JoeCo only line item directly forecasted on this Test is –. Of stock options with progressively higher strike prices – debt Schedules & interest expense calculation, the purchase and! 2020Pf column business for acquisition using an LBO … LBO model to build—especially for anyone who has built. Short term sales or EBITDA vs overall value creation could lead to a misalignment of interests LP!