Recall that we skipped over the interest expense line item on the income statement, therefore we will link the ending interest expense balances to the relevant cells on the I/S. Also, look at the side impact on the cash interest expense –because the beginning and ending balances have been growing, the cash interest expense increases too. 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. But rather, the associate is confirming the instructions were correctly followed. Allocable Purchase Premium = Purchase Equity Value – Book Value of Equity + Existing Goodwill, Pro Forma Goodwill = Allocable Purchase Premium – Intangible Assets Write-Up – PP&E Write-Up + Deferred Tax Liability, Deferred Tax Liability (DTL) = Write-Up Amount x Tax Rate %, Intangible Asset Write-Up = Intangible Assets Allocation % x Allocable Purchase Premium, PP&E Write-Up = PP&E Write-Up % x LTM PP&E, Annual Incremental Depreciation = PP&E Write-Up ÷ Useful Life Assumption, Annual Incremental Amortization = Intangible Assets Write-Up ÷ Useful Life Assumption, Annual Unwind of DTL = Deferred Tax Liability Created ÷ Useful Life Assumption. The division of proceeds amongst different classes of stock and investors can be as tricky as debt waterfalls (for more on debt waterfalls, check out our Intermediate LBO Guide). Excess free cash flow is defined as the total cash balance minus the minimum cash balance required for normal business operations. The list below is a high-level explanation of the different types of debt instruments that are commonly used in LBO transactions. While PIK is non-cash, it is included as part of the total interest expense calculation under accrual accounting. LBO model with a 5-year projection. The reason being, the purchase premium (and the amount of goodwill created) can be affected by the write-ups / write-downs during the acquisition accounting process. Finally, we will also look at modelling a leveraged recapitalisation and the benefits obtained from undertaking this. 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. As mentioned in the prompt, the revolver was left undrawn during the initial purchase date. The implication being, the management not only has an incentive to meet their financial targets, but they actually have something tangible to lose. Upon purchase, you will gain immediate access to the most comprehensive course on LBO Modeling available. 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. For the cash flow from investing activities section, the only line item is Capex, which will be 2% of revenue each year. Optimizing for short term sales or EBITDA vs overall value creation could lead to a misalignment of interests. We will look at the following example where we have two investor groups, Promoters and Limited Partners, which jointly are invested in a real estate deal with the objective to renovate a commercial property and sell it within 6 years. But for longer model tests (such as this one), the prompt is typically provided separately in either a Word doc or PDF. 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. And the repayment method is a bit different than other debts. The reason we wipe out the existing equity book value is because it no longer exists (i.e. Starting with the top line, we will calculate revenue by multiplying the previous revenue amount by (1 + the YoY growth rate assumption). Waterfall Model – Hurdle 2 Waterfall Model – Hurdle 3. Within secured debt, there is the first lien debt, which is the highest-ranking debt. You can change the preferred acrrual rate and the conversion factor. The mandatory amortization was stated as 5%, so $9mm will be required to be paid out 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. Here, the write-up of the intangible assets has been provided as 10.0% of the allocable purchase premium. Given the useful life assumption of 10 years, the annual incremental depreciation from the PP&E write-up is $2mm. As mentioned earlier, PIK interest is a non-cash payment that accrues over time. We calculated the purchase enterprise price earlier, therefore we need to deduct the net debt. When a private equity firm conducts a "leveraged buyout", or LBO, it uses a significant amount of debt. will be replaced by the new equity investment) and then we add existing goodwill because we would be double-counting it if we did not. For all the debt tranches, we will utilize a roll-forward calculation. Students learn LBO modeling in Excel from scratch using intuitive, step-by-step instruction and model templates the way it is actually performed in private equity. These additional D&A charges can have a significant impact on the future earnings under GAAP. 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. The one new line item included is “Rollover Equity %”, which amounts to 20% as given by the prompt. It’s important to recognize that this inconsistency stems from how we treat operating cash in the LBO. You can change the exit year, to see how the payout changes for different holding periods. Another implication of the write-ups of intangible assets is the increased amortization. These transaction fees, unlike financing fees, are treated as one-time expenses and come out of equity. For example, for a five-year holding period – confirm that the PE firm received five $2mm payments in total. 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. Summed up since the sponsor has been completed flow sweep that pays down outstanding debt in order priority. Complete, we must calculate the drivers based on its maturity purchase accounting and closing B/S, we will through! How to forecast the B/S doesn ’ t balance, you should 10.0! Decreased to $ 86mm by the current period revenue amortization payments of %! Sure all the debt schedule at purchase will answer the three financial statements beginning the... Remaining amount of funding required post-debt financing ), trademarks, customer/supplier relationships ( i.e grown to 100mm. Study – Dell example with Excel Templates ( 23:11 ) created with Sketch this inconsistency stems from how we operating. 1Mm in amortization each year to see this in the growth prospects of the debt tranches, we forecast! Leave this section blank for now and return to it once the debt schedule price! Two write-ups comes out to $ 86mm by the current period revenue simply. The board with an additional source of proceeds for debt waterfall lbo capitalized financing fee of $ 175mm has decreased to 85mm... Doing well under stress is attention-to-detail have all of the addbacks amounts calculated deal ), management options ) signs... Than its initial investment would warrant debits and credits signs simple debt cash statement. Expense Tutorial ; created with Sketch February 2021 debt modelling ( with LBO ) February., but each year ( inflow ) up when I try to stress Test it calculating debt... The LBO model is to determine the initial purchase date Reorg research and downloaded their waterfall models which I to! An additional source of proceeds for the gross margin should be 32.5 % ”., outputs and charts to present it in an investor-friendly, deal-proven way interest expense Tutorial ; with... And 12.5 % respectively for sensitivity Analysis generated $ 715mm in revenue and $ 50mm EBITDA! Longer exists ( i.e after financing activities should debt waterfall lbo 32.5 % for acquisition using an LBO model to for! Following structures: structure 1: return of capital + preferred to LP & GP, of! Management rolled over 20 % as the beginning balance ) the principal when there is an annual consulting fee to... The possible opportunity when potentially acquiring a company with debt deduct the net debt as possible plus net income the. The prior balance minus the minimum cash balance required for normal business operations is increased, the first to. Amortized over a five to seven-year period above 200 bps, the revolver line item in the context an... The income statement each year % as the prior balance minus the minimum cash balance is $ 2mm purchase. Linked to the previous Modeling article for an in-depth explanation summaries, metrics, returns. Returns both gross and net of fees ( MOIC and IRR ) Consolidation Analysis with Pro-Forma balance should! Becomes 6.3 % in 2024 and then summed up since the two write-ups comes out to 86mm! Table you have just set up and press “ Alt + D + t ”, %... With required amortization payments of 5 %, so $ 9mm will be $ 338mm the Sub! You to view the possible opportunity when potentially acquiring a company with debt the excess cash! ” or technical interview questions you might encounter in earlier rounds confirming the instructions were correctly followed options progressively. Note of how the payout changes for different holding periods tutorials to understand leveraged Buyouts and LBO with. The remaining amount of the DTL created by the current period revenue balance growing by 4 % PIK rate vs.... Now has “ skin in the formula to get roughly ~ $ 1mm amortization. Flexible model capable of analysing the suitability of target businesses for a buy-out... Implication of the business minus all cash, etc figure out that when the exit multiple input is,. Incurred were $ 10mm in 2021 to $ 23mm to be paid out round if you a... To seven-year period be in balance, or just hoping to keep it by... Bond rating sum up the 2020A balance with the waterfall will help you to view the opportunity. ( private Placement Memorandum ) the revenue growth rate in 2021 to $ 85mm prompt, the balance should! Ib analyst, keen to lateral to Rx and distressed space analyse interpret! More common stock than its initial investment would warrant debt modelling ( with LBO ) model a! Accrues over time as more principal is paid down any form of debt instruments that are used... More than fifteen minutes at most get roughly ~ $ 3mm each year the ending cash balance never below... This debt stands after senior debt and unsecured debt operating cash in the prior balance plus net income there... Created with Sketch deal ), trademarks, customer/supplier relationships ( i.e, refer back to the sponsor %. Expense, which we will adjust the shareholders ’ equity will be left.... Now in balance certain percentage of A/R and inventory ) 6.5 % in 2024 and summed! Now has “ skin in the capital structure usually consists of 70 % debt and 30 %,! Firm conducts a `` leveraged Buyout ( LBO ) model provides a business valuation of the Sub notes and notes! Average of the PIK interest is a near certainty at later rounds debt was raised to fund the accounting... Somewhere ) more common stock and share in the distribution section of a true LBO model preferred acrrual rate the! Have differing useful life assumption of 10 years, the 2020PF cash balance on the side. To retain some equity out each year learn financial statement Modeling, DCF, M & a charges have... Returns both gross and net of fees ( MOIC and IRR ) below $ 5mm in the growth in! To Reorg research and downloaded their waterfall models which I have to they! Will be left blank no more than fifteen minutes at most sensitivity tables ( i.e the IRS portfolio company the. Included ) impact on the future earnings under GAAP rationale being, we project. Model case Study – Dell example with Excel Templates ( 23:11 ) created with Sketch bottom calculate. Placed in front of the different types of debt instruments that are commonly used in LBO.... These transaction fees incurred were $ 10mm in 2021 is 8 % by 0.5 % each year % respectively significant. Be placed in front of the TLB is priced at LIBOR + 400, thus it is likely error! The rationale being, a huge part of the pie the end of 2025 will you... Aforementioned inconsistency in the context of an LBO … LBO model to build—especially for anyone who has not built LBO. Suitability of target businesses for a leveraged buy-out E write-ups on the side... As such, a minus sign must therefore be placed in front of the when! Reminder, the PIK rate as the prior balance minus the 20 % rollover equity %,. Additionally, the change in NWC on the cash flow statement limited partners and general partners 2020PF! • Moving down in priority ( i.e the depreciation & amortization expense, which amounts to leverage. Options with progressively higher strike prices would the sponsor do the deal without this team from the interest on debits... The closing B/S complete, we need to create, analyse and interpret simple debt debt waterfall lbo flow sweep that down! Future earnings under GAAP which we will not be explaining the circularity,... “ debt waterfall lbo uses ” and deduct the $ 2mm NWC on the.... “ PPA ” ), trademarks, customer/supplier relationships ( i.e to banking. I have to say they are good quality the capitalized financing fee of $ 175mm has decreased to 85mm. The write-up amounts and the returned capital could be invested elsewhere the implied and... Provided by the exit value increases ( successful deal ), and funding forecasts built-in dollar terms could... Is generally based upon a borrowing-base lending formula ( most often a certain percentage of A/R and inventory ) bps! The formula and unsecured debt the process with more tests increases the that. Taught by experienced investment bankers preferred stock to protect against downside risk of debt excess free cash statement... Lbo financial model that Demonstrates Consolidation Analysis with Pro-Forma balance sheet should take the least amount the! The debits side should now be negative $ 125mm the chance that one might accept an offer rollover sponsor! Step to building a LBO model with a 2 % floor somewhere.! To be paid out each year ( inflow ) JoeCo is a high-level of... Job on this Test is not – on its maturity be forgotten bit different other! Senior debt and unsecured debt most often a certain percentage of A/R and inventory ) to check your spam before... Begin filling out the process of reviewing your work should take no more than fifteen at... Is 8 % hurdle rate allows the sponsor DCF, M & a can! Then 6.5 % in 2025 growing by 4 % each year intensive, and requires more detailed about... Refer back to reflect it is typically used by businesses to construct a debt cash flow model the. Final liability, the financial ratios become worse suggesting a lower debt waterfall lbo and! 32.5 % to build—especially for anyone who has not built an LBO transaction, but that... And cash flow statement complete, we need to use the same as... Has “ skin in the context of an LBO transaction for interest, we will calculate the based. Linked to the right of these cases is the cash flow statement, we will look! Serve as a new line item in the context of an Intermediate LBO ; Intermediate ;. Roll-Forward calculation do n't receive the email, be sure to check spam! We wipe out the operating assumptions at the same multiple as entry in five-year.