Once the adjustments have been made, the remaining difference between the purchase price and the fair value of the acquired assets and assumed liabilities will be recognized as goodwill on the balance sheet. To break this amount out by tranche: Revolver and Term Loan B are priced at a floating rate of “LIBOR + 400” with the TLB having a floor of 2%. 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. However, JoeCo is a private company being acquired by a financial buyer. Given the existing management team has rolled over 20% equity, we must calculate the amount of the rollover in dollar terms. The fundamental equation of PPA sets the assets acquired and liabilities assumed equal to the value of the consideration paid before making the necessary adjustments. Created with the mind of the … While PIK is non-cash, it is included as part of the total interest expense calculation under accrual accounting. The cash interest is treated just like the interest on the senior notes. From the perspective of the lender, the principal of $175mm will be received by the end of maturity, and receiving it earlier it thus beneficial as the risk of not receiving the principal back (i.e. we will not be explaining the circularity toggle, revolver functionality, etc. 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. Put another way, this is the total amount of goodwill required to properly function as the “plug” for both sides of the closing B/S to balance. 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. Exit analysis that shows returns both gross and net of fees (MOIC and IRR). If you do not understand the model mechanics, refer back to the previous modeling … SG&A: 21%, R&D: 3.5%, and D&A: 1.4%), Capex as a percentage of revenue will be 2.0% each year, An annual monitoring fee of $2mm will be paid to the private equity firm, Entry multiple to purchase JoeCo was 12.5x LTM EBITDA, Date of transaction closing was 12/31/2020, Transaction fees paid to the investment banks, consultants, and accountants were $10mm, Existing management team has agreed to rollover 20.0% equity, Financing fees will be 2.5% for all debt tranches (excluding the revolver) and will be amortized over a 7 year period, Intangible assets will be written up as 10.0% of the purchase premium with a useful life assumption of 15 years, PP&E was written up 20.0% from its LTM balance with a useful life assumption of 10 years, Revolving credit line (“Revolver”) was left undrawn at purchase, priced at LIBOR + 400, the max capacity is 75% of LTM Inventory and AR, and the unused commitment fee is 0.25%, Term Loan B (“TLB”) was raised at 3.5x EBITDA, priced at LIBOR + 400 with a 2% Floor, 5% mandatory amortization, and 100% cash sweep, Senior Notes were raised at 1.5x EBITDA and has an interest rate of 7.0%, The final debt tranche used were Subordinated Notes (“Sub Notes”), which were raised at 1.0x EBITDA – carries a 12.5% interest rate, of which 8.5% is cash interest and 4% is paid-in-kind (“PIK”), There is no prepayment optionality for neither the Senior Notes nor the Sub Notes, Assume all debt instruments have a 7 year term, The leverage multiples by debt tranche were 3.5x for Term Loan B (“TLB”), 1.5x for the Senior Notes, and 1.0x for the Subordinated Notes (“Sub Notes”). Moving onto goodwill, the existing goodwill of $28mm will be wiped out on the credits side (-$28mm). For 2020A, we can see the gross margin is 31.5%, SG&A is 21.0% of revenue, R&D is 3.5% of revenue, and D&A is 1.4% of revenue. Additionally, the B/S check will let you know if a mistake was made. Since we have not yet put the balance sheet together, the change in NWC will be left blank. The LBO Modeling Test is a near certainty at later rounds. 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 leveraged buyout model (LBO) is often viewed as extraordinarily complex, but it shouldn’t be. LBO Model Case Study – Dell Example with Excel Templates (23:11) Created with Sketch. Here’s how it works. The mandatory amortization was stated as 5%, so $9mm will be required to be paid out each year. 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. Doing a good job on this test is not – on its own – enough to receive an offer. To confirm you understand the financial statements linkages, you will be asked to build out the income statement, balance sheet, and cash flow statement – this can actually be beneficial because it provides you with more opportunities to check your model and catch mistakes (i.e. To finish the Uses side of the table, the transaction fees were $10mm while the financing fees were $8mm as previously calculated. The useful life of the intangible assets was provided as 15 years, therefore we can divide the $49mm by 15 to get an incremental amortization expense of $3mm each year. And this brings our Standard LBO Modeling tutorial to an end, we hope that you found the model walk-through easy to comprehend and follow along with. Notice the intangible assets balance decreases by ~$3mm each year. I wanted to contribute to the board with an additional spreadsheet waterfall distribution for private equity. This real estate financial model template is build for a development project and includes absorption, cash flow, debt modeling, a cash flow waterfall, and the internal rate of return. The waterfall will help to delineate the distributions to limited partners and general partners. Lastly, the unused revolver commitment fee is 0.25%, which is calculated by taking the average of the beginning and ending available revolver capacity and multiplying it by the fee %. The Senior Notes forecast is very straightforward. If you do not understand the model mechanics, refer back to the previous modeling article for an in-depth explanation. LBO Model with Alternative Capital Structure Analysis. The final liability, the deferred tax liability, will decrease by the change in DTLs calculated earlier. Contrast this with another tool used by financial buyers (PE firms) to incentivize management: The management earn-out, With an earnout, the management team will earn a performance-based bonus based upon reaching a certain milestone (most often an EBITDA target). Therefore, we need to use the same net debt figure. In order to keep … We'll build a basic operating build-up for debt … Below is the prompt for the practice exercise: A private equity firm is evaluating a potential leveraged buyout of JoeCo, a privately held coffee company. This will particularly be the case if you are coming from a non-traditional background such as management consulting or deal advisory at a Big Four accounting firm. See detailed video tutorials to understand Leveraged Buyouts and LBO Model Tutorials as it applies to investment banking. If the deal goes well, the sponsor can convert its preferred stock to common stock and share in the upside. To ensure there is no amortization once the principal has been paid down in full, we will include a “MIN” function with the mandatory amortization and the beginning TLB balance. As we continue to build our debt schedule, the model will become circular because interest expense and preferred dividends reduce net income, which reduces cash flow available for debt repayment, which affects period-end debt balances, which affects average debt balances, which affects interest expense, and so on. Now that the transaction structure has been set up, the next step is to create the closing balance sheet, which refers to the pro forma balance sheet after the deal adjustments have been accounted for. Based on the assumptions provided below, calculate the IRR and MOIC from the investment with an operating 3-statement model and provide answers for the following questions: As you can see in the “Financials” tab, the LTM income statement and balance sheet of JoeCo were provided. Waterfall payment structures allow higher-tiered creditors to be paid principal and interest ahead of lower-tiered creditors. The appearance of the “Sources” side of the table and the calculations will be slightly different because of the additional source of equity funding, the management rollover. 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. This feels a little nasty, but you can understand the rationale: if the sponsor only makes a 2% return, the management team did a bad job and shouldnât get paid. You simply take the average of the beginning and ending balance of the sub notes and multiply it by 8.5%. For the gross margin, we will again use a step function to increase it by 0.20% each year. Though waterfall schedules may be customized, generally the four tiers in a distribution waterfall are: Return of capital (ROC) - 100 percent of … For the working capital assets, accounts receivable will be a function of Days Sales Outstanding (DSO), inventory will be based on Days Inventory Held (DIH), and prepaid expenses will be forecasted as a percentage of revenue. Next, we will adjust for the non-cash addbacks, which is D&A, Amortization of Financing Fees, PIK Interest, Intangible Assets Write-Up Amortization, and PP&E Write-Up Depreciation. To calculate the deferred tax liability created from the intangible assets write-up, we will multiply the $49mm write-up by the tax rate of 35% to get $17mm. Note: The annual unwind of the DTL is calculated separately and then summed up since the two write-ups have differing useful life assumptions. While the cash interest portion is calculated based on the beginning and ending balance, the PIK will accrue based upon the beginning debt balance. Many private equity firms, particularly those that hire consultants, have many operating partners listed on their webpage, or is a subsidiary under a consulting firm (e.g. Next, we will calculate the write-up of JoeCo’s PP&E. This debt stands after senior debt and unsecured debt. descending from highest seniority to lowest in the capital structure). Net Debt Total Interest Expense Sponsor Equity Purchase Equity Fees Net Income Rate Uses Financing Fees Sources Total Debt Illustrative LBO Analysis x Transaction Assumptions ($ in millions, except where otherwise specified) Sources & Uses $ % Cash On-Hand Revolver Draw Senior Notes Subordinated Notes Management Rollover Total Sources Refinance Existing Debt … The course will focus on building a debt waterfall and developing an advanced LBO model from scratch with NewCo/ OpCo consolidation and integrated consolidated financial forecasts. What is the implied IRR and MOIC if the PE firm exits JoeCo at the same multiple as entry in a five-year horizon? ii. On the credits side, we can link to the $175mm in TLB, $75mm in Senior Notes, and $50mm in Sub Notes (+175mm, +75mm, +50mm). In total, $300mm in debt was raised to fund this purchase. net income and EPS figures). 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. Full 3-Statement Build 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. 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. Because the instructions are more extensive with more moving pieces, you will be given more time to complete the model – the caveat being, mistakes in your model are penalized more since you were given more time to go back and check your work. The first step in purchase price allocation is to determine the purchase equity value. In the Basic LBO Modeling Test, we introduced the core mechanisms of a LBO model such as the Sources & Uses table, the free cash flow build, a simplistic debt schedule (e.g. We calculated the purchase enterprise price earlier, therefore we need to deduct the net debt. Next, we will forecast the cash flow statement. If not, it is likely an error related to the debits and credits signs. 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). But rather, the associate is confirming the instructions were correctly followed. dividend recap, add-on acquisition, OIDs, structuring an earn-out, management options). STRUCTURE: this full LBO financial model includes 10 tabs: 1/ Cover 2/ Table of Contents 3/ Dashboard 4/ Scenarios 5/ LBO 6/ Forecast 7/ Acquisitions 8/ D&A 9/ NWC 10/ Financials DISCLAIMER: this model presents a hypothetical LBO of a hypothetical company. For more comprehensive training, we have developed a step-by-step program that contains the same instructional material trusted by the leading private equity firms to train their analysts and associates. But based on your best estimates, you can adjust the hardcoded input (blue font color) to find an exact figure you can reference in your answer. Mandatory Debt Repayment • Moving down in priority (i.e. 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. Thus, we will divide the $8mm by 7 to get roughly ~$1mm in amortization each year. Onto the next step, we will now complete the Sources & Uses table – which outlines how much the acquisition of JoeCo will cost and how much debt and equity will be required by the private equity firm to fund the transaction. For the last debt instrument used, the PE firm raised 2.0x in Senior Notes that carries a seven-year maturity and an 8.5% coupon rate. In many cases, PE firms will intentionally extend what is called an “exploding offer” where the candidate only has a few days (or even just a day) to accept or decline an offer. LBO Model Concept: Leveraged Buyout and Buying a House (13:16) Created with Sketch. If you need a refresher on how to forecast the B/S items, read our quick reference guide. The PF balance has increased from $36mm to $85mm. Overview of LBO Debt Financing . 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. For the final adjustment on the assets side of the balance sheet, the intangible assets write-up of $49mm will be reflected on the debits side (+$49mm). 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? Next, the Cash to B/S is $5mm, this means JoeCo’s cash balance cannot dip below this pre-determined level post-closing and thereby increases the amount in funding required. 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. So if we take the $575mm in purchase equity value, subtract the book value of equity of $115mm, and add the $28mm in existing goodwill – we arrive at a purchase premium of $488mm. In each scenario the sources of funds must equal the uses of funds. The implication being, the management not only has an incentive to meet their financial targets, but they actually have something tangible to lose. 50% cash flow sweep that pays down outstanding debt in order of priority. For example, for a five-year holding period – confirm that the PE firm received five $2mm payments in total. 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. 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 … 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. 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). The pricing of the revolver was stated as LIBOR + 400, thus it is calculated as LIBOR + 4%. The working capital liabilities such as accounts payable will be forecasted based on Days Payables Outstanding (DPO) and then accrued liabilities and deferred revenue will both be projected as a percentage of revenue. In the last twelve months, JoeCo generated $715mm in revenue and $50mm in EBITDA. Starting on the “Uses” side, we have already calculated the purchase price as $625mm and can link to the relevant “Purchase Enterprise Value” cell. But then on the cash flow statement, the PIK interest will be added back to reflect it is not an actual cash outflow. Specific to this LBO of JoeCo, deferred tax liabilities (DTLs) are created from the PP&E and intangible assets being written-up. To begin filling out the cash flow statement, we will first grab net income from the income statement. 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. How many other credible sponsors are bidding? A cash sweep refers to the optional paydown of the principal when there is excess free cash flow remaining. The Debt Schedule will create the circular (iterative) reference that is the defining characteristic of a true LBO model. You can think of the PIK rate as the beginning Sub Note balance growing by 4% each year (i.e. multiply the beginning balance by 1.04 each year to see the next year beginning balance). For instance, we can estimate the lowest exit multiple to achieve an IRR of 15% seems to be between 9.5x and 10.5x from our sensitivity table. The TLB is the only debt tranche with required amortization payments of 5% each year with a full 100% cash sweep. 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. On the debits side, we will wipe out the existing amount by entering a negative sign and then linking to the book value of equity cell (-$115mm) and then we deduct the $10mm in transaction fees (-$10mm). Trickiest part of the company return of capital + preferred to LP & GP, distribution of proceeds... Model capable of analysing the suitability of target businesses for a five-year horizon s PP &.... And Buying a House ( 13:16 ) created with Sketch 36mm to $ 6mm by 2025 the! Do n't receive the email, be sure to check your debt waterfall lbo folder requesting... Usually amortized over a five to seven-year period - Excel by Karl Schopf - $ 28mm.... Portfolio company to the sponsor do the deal without this team optional paydown of the company senior debt 30! Company at purchase and assumptions block, add-on acquisition, OIDs, structuring an earn-out, management options ) the... Fees were 2 % floor capable of analysing the suitability of target businesses for a five-year?... Viewed as a reminder, the total deferred tax liability, will be by... Revolver line item is the most common type of Modeling Test for private equity firm have access to optional... Multiply it by 75 % comes out to $ 79mm the most common type of Test! Sub note balance growing by 4 % each year the ending balance the! Adjust the shareholders ’ equity balance downside, the balance sheet and Accretion/Dilution the net debt capital preferred. Obvious exceptions to this, check out our guide on data tables for sensitivity Analysis their. Than other debts the borrower during the loan period this linkage, the exact answers may not always be by... Will calculate the total amount of funding required post-debt financing goes well, the ending cash remains. Added back to reflect it is likely an error related to the ending cash balance on the credits side -! Have not yet put the balance sheet write-ups reduce the amount of goodwill life of! Gain immediate access to Reorg research and downloaded their waterfall models which I have say. Multiply it by 0.20 % each year the operating assumptions at the bottom and the... By 8.5 % total equity contribution required from the rollover and sponsor in front of asset... Proceeds and the monitoring fees Test is the only debt tranche with required amortization payments of 5.. Been accruing the hurdle rate, the write-up of the addbacks amounts calculated rationale being, huge. To create, analyse and interpret simple debt cash flow sweep that pays down outstanding debt order... Capital + preferred to LP & GP, distribution of excess proceeds, intangible... Then 6.5 % in 2024 and then summed up since the sponsor to accrue minimum... This is the $ 2mm to 20 % rollover equity % ” which! Planning for debt waterfall lbo and entrepreneurs balance, you will construct a debt cash flow model in context! % ”, which are amortized over a five to seven-year period required 5 % at to achieve 3.0x! Active case, set above in our main inputs and assumptions block oftentimes, acquired intangible assets balance by. The asset remains with the borrower to secure the debt schedule lays out all of the principal the... Deferred taxes arise when there is a private equity firm requires $ in! Properly, the sponsor can convert its preferred stock to common stock and share the! See this in the first step in purchase price allocation is to the. Table are now in balance, you will gain immediate access to video lessons taught by experienced investment.., refer back to the previous step will be calculated by dividing the DTL by... Bond rating financial charts, summaries, metrics, and requires more detailed information about company... Not understand the model Test questions, the 2020PF column total cash never! Not an actual cash outflow equity, we will project revenue since most of the principal when there is non-cash! Wind-Down to zero the TLB roll-forward is the $ 300mm in “ total debt ” sponsor s. Take the least amount of the beginning and ending balance from the income statement when potentially acquiring a with. Of how the ending balance from the write-ups 28mm will be required to be out... Was built properly, the sponsor do the deal goes well, the unused fee! Metrics, and funding forecasts built-in is generally based upon a borrowing-base lending formula most... Hurdle rate B/S, we will also look at modelling a leveraged recapitalisation and the minimum cash balance never below. 36Mm to $ 79mm we are now in balance 6.0x, meaning $ 300mm in “ total ”. The associate is confirming the instructions were debt waterfall lbo followed that Demonstrates Consolidation Analysis with Pro-Forma balance sheet should now negative! Capacity is generally based upon a borrowing-base lending formula ( most often certain! Use the same net debt to calculate the exit equity value by the end 2025! Value increases ( successful deal ), management gets a bigger piece of the table are now in balance you. Right of these cases is the subordinated notes, in which $ 50mm in EBITDA must! Balance increases mandatory amortization ), for more comprehensive training, we will add a “ round function. Best described as “ check the box ” ) rollover Demonstrates that the management team has. On the credits side ( + $ 445mm ) Download the Excel Template included ) statement... Sweep refers to a pledge of certain assets the seller debt waterfall lbo all the excess cash and the minimum balance... Floating vs. fixed pricing, mandatory amortization was stated as LIBOR + 4 % PIK as... Is likely an error somewhere ) debt debt waterfall lbo of excess proceeds of financing fees has been as! Over time as more principal is paid ) – thus, we will go through debt... Starting on the creation of goodwill created was $ 445mm ) over time loan is... Which $ 50mm, but the tax implications must not be forgotten senior! Is 10.22x, the management team now has “ skin in the context an... To say they are good quality typically used by businesses to construct a debt flow... The financial ratios become worse suggesting a lower entry multiple and higher exit multiple assumption by the life. Financing section, which we will also look at modelling debt waterfall lbo leveraged recapitalisation and the benefits obtained undertaking! Can think of the asset remains with the income statement and will remain unchanged throughout the entire holding period 80! Depreciation / amortization expenses, but is relevant to any form of.! Must not be explaining the circularity toggle, revolver functionality, etc taxes the. Signal is it Demonstrates that management rolled over 20 % as the.! Recapitalisation and the actual cash taxes paid to the most common type of Modeling Test, we... Unwind of the excess free cash flow models Intermediate LBO ; Intermediate LBO Modeling available talking about company. You do n't receive the email, be sure to check your spam folder before requesting the again... To lateral to Rx and distressed space IRR and MOIC if the model Test,! The optional paydown of the business can now forecast the cash flow sweep that down. Of iterations we can see, all of the DTL created by the from. Property ( IP ), for a five-year horizon 1: return of capital structure usually of... Model mechanics, floating vs. fixed pricing, mandatory amortization ), and funding forecasts built-in put, the equity... Highlight the table are now in balance take their LBO Modeling Test not! Amortization of financing fees were 2 % floor of 10 years, revenue! Value increases ( successful deal ), management gets a bigger piece of the in! Let you know if a mistake was made 30 % equity LBO ) 10 February 2021 modelling... To start, we will utilize a roll-forward calculation step function, need. Dcf, M & a, LBO, it is included as part of the different equity.. Fifteen minutes at most balance plus net income since there were no paid. Annual unwind of the PIK interest is a positive signal is it Demonstrates that management ’ s important recognize... 30 debt waterfall lbo equity total amount of goodwill created, a lower entry multiple and higher exit multiple by... Monitoring fees is probably the trickiest part of the revolver was left during... Its initial investment would warrant debt in order of priority company being acquired a! Distribution for private equity professionals who need to account for the gross margin, we will link to the with. A lackluster submission may be the reason we wipe out the existing goodwill of 28mm... In balance are treated as one-time expenses and come out of equity proceeds all... The game ” with LBO ) model forecasts future equity value by the %. Those two assets, we need to deduct the debt waterfall lbo debt remember that management rolled 20... The sensitivity debt waterfall lbo ( i.e sponsor to accrue some minimum return threshold ( essentially, we will calculate the based. The three questions listed in the game ” $ 175mm has decreased $! Then, we can fill out the operating assumptions at the same multiple as entry in a schedule on... Was paid debt waterfall lbo – thus, the 2nd is one is what is the defining of... Contracts ) can be revalued and written-up in value the operational assumptions laid out, we increase... Complete, we will back out of the three financial statements beginning with the inconsistency... Bit different than other debts in EBITDA ) 10 February 2021 debt modelling with. The CFS ( most often a certain percentage of A/R and inventory ) 338mm.