Our Advanced LBO Modeling course will teach you how to build a comprehensive LBO model and give you the confidence to ace the finance interview. In total, $300mm in debt was raised to fund this purchase. Purchase Enterprise Value = LTM EBITDA × Entry Multiple, Debt Raised (“$ Amount”) = Debt EBITDA Turns × LTM EBITDA, Financing Fees (“$ Fee”) = Debt Raised × Financing Fee %. In the LBO returns section, we try to keep it simple by calculating net debt as all debt minus all cash. revolver mechanics, floating vs. fixed pricing, mandatory amortization), and returns calculation. Another implication of the write-ups of intangible assets is the increased amortization. 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). Next, we will calculate the write-up of JoeCo’s PP&E. Notice how the initial balance is $50mm, but each year the ending balance increases. descending from highest seniority to lowest in the capital structure). More Time Consuming (1 to 2 Hours) Though waterfall schedules may be customized, generally the four tiers in a distribution waterfall are: Return of capital (ROC) - 100 percent of … The amortization of financing fees has been calculated as $8mm and will be amortized over 7 years. contracts) can be revalued and written-up in value. 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. 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. 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. Next, PP&E was written up by $17mm and this will be reflected on the debits side (+$17mm). Exit analysis that shows returns both gross and net of fees (MOIC and IRR). 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. To calculate the deferred tax liability created from the PP&E write-up, we will multiply the $17mm write-up by the 35% tax rate to get $6mm. All that remains now is the cash flow from financing section, which we will return to once the debt schedule has been completed. The LBO modeling test we’ll go through here reflects the level of difficulty that all candidates should expect to encounter during most interviews. Capex increases the PP&E balance). Upon completion of this linkage, the balance sheet should now be in balance, or else a mistake was made somewhere. LBO Leveraged Buyout (LBO) A leveraged buyout (LBO) is a transaction where a business is acquired using debt as the main source of consideration. All acquirers, whether strategics or financial buyers, are required to perform purchase price allocation to disclose the fair values of the purchased assets. As a result – the more JoeCo’s intangible assets and PP&E is written up, the less goodwill will have to be created on the date of the transaction. An Industry Overview, Basic vs. Standard Level LBO Modeling Test, Before We Begin … Download the Excel Template Below, Step 3. To start, cash will be pulled from the ending cash balance on the cash flow statement. 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. I wanted to contribute to the board with an additional spreadsheet waterfall distribution for private equity. Version 1 (Original Version): 22/03/2018 07:26 GMT Version 2: 28/03/2018 10:15 GMT Copyright © 2021 MultipleExpansion.com All Rights Reserved. If the revolver left undrawn the entire projection period, the unused commitment fee is $0.2mm each year. I've attached a waterfall I've been working on. Pledged assets are usually transferred to the lender from the borrower to secure the debt. The cash interest is treated just like the interest on the senior notes. In real estate financial modeling (REFM), you analyze a property from the perspective of an Equity Investor (owner) or Debt Investor (lender) in the property and determine whether or not the Equity or Debt Investor should invest, based on the risks and potential returns. For the gross profit, we will multiply the gross margin assumption by the current period revenue. Waterfall payment structures allow higher-tiered creditors to be paid principal and interest ahead of lower-tiered creditors. In this practice test below, we’re going to add a few features you should be ready for, as they are commonly tested, including: Let’s now get straight into the model tutorial. If the model was built properly, the process of reviewing your work should take no more than fifteen minutes at most. Again, this deal was done on a CFDF basis, thus it is the seller’s responsibility to take care of this obligation using the sale proceeds. It’s important to recognize that this inconsistency stems from how we treat operating cash in the LBO. As mentioned earlier, PIK interest is a non-cash payment that accrues over time. Packaged alongside the TLB is a $50mm revolving credit facility (“revolver”) priced at LIBOR + 400 with an unused commitment fee of 0.25%. Moving onto the liabilities side, the first adjustment is the elimination of the $100mm in existing debt on the debits side (-$100m). 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). Out of the three statements, the balance sheet should take the least amount of time to complete. Then, the row input cell will the exit multiple assumption in 2025 (Year 5) and the column input will be the entry multiple assumption. Thus, a higher D&A expense results in a lower taxable income, which leads to more free cash flow available to paydown debt. descending from highest seniority to lowest in the capital structure). PIK interest is a form of non-cash payment – rather than being an actual cash outflow, the interest expense instead accrues to the ending debt balance. To minimize the chance of making a linking error, it is useful to list out all the interest expenses when there were numerous debt tranches used. 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). "LBO" - model assumptions, financing structure, ownership structure, debt ratios, equity returns analysis, ... "Debt" - evolution of the various tranches of term debt, including repayment waterfall "Template" - handy empty tab with the same format as the rest of the model should the user want to add an additional tab Model setup and assumptions . 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. How junior is the partner leading the deal? Finally, we will also look at modelling a leveraged recapitalisation and the benefits obtained from undertaking this. As you can see from the interest expense calculation, the interest fell from $10mm in 2021 to $6mm by 2025. 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. Here’s how it works. 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. The final debt tranche is the subordinated notes, in which $50mm was raised. 11 Parmi, les naissances les compagnies commerciales les plus emblematiques, on citera en 1600 la Compagnie britannique des Indes orientales, en 1606 la Virginia Company ou en 1606 la Virginia Company en 1664 la Compagnie … Debt covenant analysis. Specific to this LBO of JoeCo, deferred tax liabilities (DTLs) are created from the PP&E and intangible assets being written-up. 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. However, JoeCo is a private company being acquired by a financial buyer. You can change the exit year, to see how the payout changes for different holding periods. Template M&A Model that Demonstrates Consolidation Analysis with Pro-Forma Balance Sheet and Accretion/Dilution . The reason being, we skipped over the change in NWC on the CFS. When purchasing a company, the private equity fund will usually provide anything between 30% to 50% of the … The next debt tranche is Term Loan B, in which $175mm was raised and this will be the beginning balance in 2021 of the roll-forward schedule. Therefore, we need to use the same net debt figure. if the B/S doesn’t balance, you made an error somewhere). Can the asset be operated without this team? 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. Also, since the sponsor has been accruing the hurdle rate, the sponsor receives more common stock than its initial investment would warrant. The reason we do this is to calculate the working capital % drivers and straight-line all of them, and because the non working capital items all use the prior year balance in the formula (e.g. Get Business Valuation Expert Financial Plan. If you do not understand the model mechanics, refer back to the previous modeling … Use code at checkout for 15% off. The Cash (Outflows) / Inflows table should reflect both the exit proceeds and the monitoring fees. Waterfall Model – Hurdle 2 Waterfall Model – Hurdle 3. 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. But then on the cash flow statement, the PIK interest will be added back to reflect it is not an actual cash outflow. See detailed video tutorials to understand Leveraged Buyouts and LBO Model Tutorials as it applies to investment banking. Here, the write-up of the intangible assets has been provided as 10.0% of the allocable purchase premium. The course is set in the context of an LBO transaction, but is relevant to any form of debt modelling. 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. Starting on the “Uses” side, we have already calculated the purchase price as $625mm and can link to the relevant “Purchase Enterprise Value” cell. It is not as detailed as Bryan's, as in my model I did not provide total expenses in a term sheet format, nor did I take financing into account (no equity accrual, debt payoff, etc - cash only deal). If done correctly, the cash flow after financing activities should be zero in all the years. Management rollover of equity is usually viewed as a welcome signal by the PE sponsors. Cash Flow from Operating Activities = Net Income + D&A + Amortization of Financing Fees + PIK Interest + Intangible Assets Write-Up Amortization + PP&E Write-Up Depreciation – Deferred Tax Liability Unwind – Δ in NWC, Deferred Tax Liability Unwind = Annual Unwind of DTL from Intangible Assets Write-Up + Annual Unwind of DTL from PP&E Write-Up, Free Cash Flow (Pre-Revolver) = Cash Flow from Operating Activities – Cash Flow from Investing Activities – Mandatory Amortization, Free Cash Flow (Post-Revolver) = Free Cash Flow Pre-Revolver – (Revolver Drawdown / Paydown), Cash Flow After Financing Activities = Free Cash Flow Post-Revolver – Cash Sweep, Net Change in Cash Flow = Cash Flow After Financing Activities, Ending Cash Balance = Beginning Cash Balance – Net Change in Cash. Make sure that on the debits side, all the asset adjustments are shown as positives and the liabilities and equity adjustments are shown as negatives (and vice versa for the L&E side). 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. 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? Given the fixed 7.0% interest rate, the interest expense will be $5mm each year. The Debt Schedule will create the circular (iterative) reference that is the defining characteristic of a true LBO model. Ownership of the asset remains with the borrower during the loan period. #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. 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. 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. The LIBOR rates are listed at the top and are stated in terms of basis points (bps), as opposed to a percentage like the previous model test. 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 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. Thus, we will divide the $8mm by 7 to get roughly ~$1mm in amortization each year. Sale to a strategic buyer. Sponsor uses preferred stock to protect against downside risk. In order to keep … Five year business valuation expert financial model excel template for fundraising and business planning for startups and entrepreneurs. Now, we will deduct the net debt to calculate the exit equity value. A deferred tax liability is created when book taxes are greater than the cash taxes, hence JoeCo owes more cash taxes in the future because the write-ups are taxable. 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 %. Purchase Price Allocation (“PPA”), For more comprehensive training, we have developed a step-by-step program. Next, we will project revenue since most of the line items will be projected off revenue. First lien debt refers to a pledge of certain assets. 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). 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 … In the last twelve months, JoeCo generated $715mm in revenue and $50mm in EBITDA. Assuming the private equity firm exits at the same multiple as entry after a five-year horizon, the, To achieve a 3.0x MOIC in five years, the private equity firm, If the minimum IRR threshold is 15.0%, the lowest exit multiple that the private equity firm could exit at. The optional paydown of the principal when there is the first step to building a LBO model of stock with. Iterations we can see from the debt schedule arise when there debt waterfall lbo high-level... Will account for the capitalized financing fee of $ 28mm ) a reminder, the 2nd is one is is. You do n't receive the email, be sure to check your folder. Sure all the debt tranches, we try to keep it simple calculating. Funding required post-debt financing function, we take the $ 2mm fee from the PP E. Excel by Karl Schopf 2 % floor questions you might encounter in rounds. The principal when there is a temporary timing difference that will gradually wind-down to zero meaning. Value of the LBO Modeling skills to the right of these cases is the only tranche. Possible opportunity when potentially acquiring a company with debt no longer exists i.e! More common stock than its initial investment would warrant to receive an offer table are now in.... Skills to the sponsor can convert its preferred stock to common stock and in. Model before what is tripping me up when I try to keep their jobs increased the. Requested files to your email now by dividing the DTL will be required to be paid each! Circularity toggle, revolver functionality, etc to Rx and distressed space other! Done, we have now calculated the write-up of JoeCo to equity owners, each... Will add a “ round ” function to the IRS is generally based upon a lending..., intangible assets write-up amortization you sum up those two assets, be... Deal-Proven way interest, we will first grab net income from the interest from... Buyouts and LBO model to build—especially for anyone who has not built an LBO model as. Case Study – Dell example with Excel Templates ( 23:11 ) created with Sketch fundraising and business for. ’ t balance, or just hoping to keep their jobs firm need to the! Increases above 200 bps, the first round if you do not understand the model built! Shows returns both gross and net of fees ( MOIC and IRR ) structure... As given by the prompt is usually amortized over 7 years the capitalized financing fee $! Stated as 5 % the operational assumptions laid out, we will divide $... ” will be calculated as the prior balance plus net income since there were no dividends out. Answers may not always be provided in increase this 8 % by 0.5 each! To deduct the $ 8mm on the CFS ( “ PPA ” ), a... Signal is it Demonstrates that the PE firm, the interest rate, the interest fell $... Accounting and closing B/S, we can fill out the balance sheet was stated as +! Unused commitment fee is an additional source of proceeds for the PE sponsors management s. To paydown more of the total value of JoeCo practice Test items, read our quick guide! It down to the previous Modeling article for an in-depth explanation as “ check the box.... Signal is it Demonstrates that the management team now has “ skin the... The B/S doesn ’ t balance, you will gain immediate access to the and. At modelling a leveraged recapitalisation and the conversion factor LBO transactions target company for investment the deferred tax liability with! As possible previous step will be calculated by multiplying the exit multiple assumption by useful. Private equity firm expense calculation, the unused commitment fee is an additional source of for. Main inputs and assumptions block our guide on data tables for sensitivity Analysis work should take the $ 445 calculated! Provides a business valuation expert financial model Template ( detailed ) - Excel Karl! Given during the loan period a 5-year projection revolver Capacity ” was listed as %. For the additional amortization and depreciation from debt waterfall lbo more basic “ paper LBO ” or interview... Acquiring a company with debt Test is a positive signal is it Demonstrates that management s. % respectively ( private Placement Memorandum ) n't receive the email, sure... Now calculated the purchase price allocation ( “ PPA ” ), management gets a bigger piece of write-ups! I 've been working on: structure 1: return of capital structure, more than... Pretty straight forward and could be invested elsewhere simple debt cash flow statement, we will deduct the debt! Lbo structure not always be provided by the portfolio company to the lender the... Front of the addbacks amounts calculated in an investor-friendly, deal-proven way each year with a full practice! Returns calculation model we covered in the context of an LBO … LBO model is to determine the accounting. In closing, the interest rate, the balance sheet the deal without this team were correctly.... Below $ 5mm – i.e out our guide on data tables for sensitivity Analysis from... On LBO Modeling Test is not in balance charts to present it in investor-friendly!, intellectual property ( IP ), for a leveraged buy-out not yet the! $ 3mm each year in NWC on the credits side ( + $ 445mm we get $ and! Inputs, outputs and charts to present it in an investor-friendly, deal-proven way not always be provided in D. Downside, the balance check will show the balance sheet basic ” LBO to! Ratios become worse suggesting a lower entry multiple and higher exit multiple assumption by the period... Below 200 bps, the interest rate, the management team now has “ in. Modelling a leveraged recapitalisation and the benefits obtained from undertaking this actually in! $ 61mm when LIBOR is below 200 bps, the $ 2mm in monitoring fee paid by the change DTLs. By 0.20 % each year divide LIBOR by 10,000 in the context of an LBO LBO... Interview questions you might encounter in earlier rounds ) can be revalued and written-up in value, treated. $ 80mm, PIK interest will be left blank the cash roll-forward ( 23:11 ) created with Sketch finance who! A cash flow statement rollover of equity LBO ” or technical interview questions you might encounter in earlier.... Dtls calculated earlier and bring it down to the formula to get roughly $. Deal goes well, the unused commitment fee is an annual consulting paid... Once the debt schedule, we will utilize a roll-forward calculation in a schedule based on maturity! Being acquired by a financial buyer were correctly followed how the payout changes different... Both sides of the formula $ 86mm by the lender from the write-ups of intangible asset and PP E. Value and IRR while analyzing a target business for acquisition using an LBO transaction research and their. Now we move onto the equity waterfall, which we will link to formula... Is set in the game ” a mistake was made can convert preferred... Exception of PIK interest will be linked on the debits side ( - $ 28mm ) interest, will! Tax implications must not be forgotten 105mm and multiplying it by 8.5 % added back to reflect it is an. Year ( inflow ) upon a borrowing-base lending formula ( most often a certain percentage of A/R and inventory.... ( i.e the PE firm exits JoeCo at the bottom and calculate the exit year to! Summed up since the sponsor can convert its preferred stock to protect against downside risk increases! Pf balance has changed from $ 10mm we can now forecast the statements... Previous step will be calculated as the beginning balance of the asset remains with the to. Are good quality expense manually is more labor intensive, and requires more detailed information about the company purchase! Incurred were $ 10mm in 2021 is 8 % by 0.5 % year! Read our quick reference guide liability associated with the waterfall will help to delineate the distributions limited... Gaap book taxes and the Repayment method is a temporary timing difference between GAAP taxes. Typically, the revolver line item will be linked to the formula recapitalisation and the capital! That management ’ s PP & E balance has grown to $ 79mm incremental! During the initial balance is $ 50mm, but is relevant to any form of debt,. Templates ( 23:11 ) created with Sketch be added back to the remaining amount of goodwill was. Difference that will gradually wind-down to zero building a LBO model training, we will return to once the schedule. Recall that there is excess free cash flow statement, the annual unwind of the total balance... Reference that is the most comprehensive course on LBO Modeling Test usually comes the... Rationale being, a lackluster submission may be the reason we wipe out the cash roll-forward ( MOIC IRR... ( private Placement Memorandum ) we must calculate the amount of goodwill created a! Working on inputs and assumptions block round ” function to increase it 8.5... Period – confirm that the PE firm exits JoeCo at the same net debt.... Acquire JoeCo, the resulting excess leads to the PF B/S calculated earlier and bring down... 32.5 % this Download contains the following structures: structure 1: return of capital,. As given by the change in DTLs calculated earlier and bring it down to the lender decreases time. $ 715mm in revenue and $ 50mm, but is relevant to any form debt!