Wall Street Prep

Advanced LBO Modeling Test: 4 Hour Example

Complete Step-By-Step Tutorial of an Advanced LBO Modeling Test for Private Equity Recruiting (Excel Template Included)

Learn LBO Modeling Online

Designed for investment banking and private equity professionals who want to take their LBO modeling skills to the next level.

Enroll Today

In this post, we will go through an advanced, 4-hour LBO modeling test, step-by-step. If you are interviewing with the leading upper-middle market firms and mega-funds, you should prepare to encounter an Advanced LBO Modeling Test in the final rounds of the private equity recruiting process.

This post is the final in our 5-part series on LBO modeling tests:

  1. Top 25 Private Equity Interview Questions – Baseline knowledge around the technical aspects you'll be expected to know in PE interviews.
  2. Paper LBO Test – Given at earlier rounds, you'll get a pen and paper (no calculator) and 5-10 minutes
  3. Basic LBO Modeling Test – You're given a laptop, simple instructions and ~30 minutes – this serves as a slightly more robust early-round screen than the Paper LBO
  4. Standard LBO Modeling Test – You're given a laptop and 1-2 hours. This is the most common LBO Modeling Test given at lower-middle market and middle-market PE firms.
  5. Advanced LBO Modeling Test (*This Post*) – You're given a Laptop, a 5-15 page packet of financial data, and 3-4 hours. You're most likely to see this from upper-middle market firms or mega-funds in the later rounds of the interview process.

If you are capable of completing the four levels of difficulties in this LBO Model Test Series without reliance on the templates, you should rest assured knowing that you have the necessary foundation to complete the majority of LBO tests.

The remaining time for your preparation should be allocated towards practicing under similar conditions and to gain more sector-specific knowledge based on the firm you are interviewing with (i.e. reading recent news articles, initiating coverage equity research reports, industry primers).

Advanced LBO Modeling Test Overview

In this guide, we will go over the concepts that are likely to be tested in an Advanced LBO Modeling Test. We tried our best to balance quantity and depth, and cover every concept to the point that you should be able to understand it well enough to deal with it correctly during the interview. Below is a summary of what you'll see and how it compares to the Standard LBO Modeling Test:

Standard LBO Modeling Test Advanced LBO Modeling Test
Test Format
  • A laptop with Excel and instructions ("Prompt")
  • Financials in Excel format
  • A laptop with Excel and instructions ("Prompt")
  • Data presented in a packet that can range from 5 to 15 pages that you will have to manually input into Excel, calculate the relevant margins, growth rates, and other relevant metrics
Time Allotted
  • 1 hour
  • You'll likely be under a time crunch
  • 3 to 4 hours
  • Unlike the earlier round tests that see how you do under time pressure, this will be ample time - at this stage firms are more concerned with whether you can thoughtfully structure the model and less about seeing how you perform under time pressure
Scoring Criteria
  • Was the candidate able to get the right answer (i.e. what's the IRR, etc.)
  • Model structure is usually not part of the scoring.
  • Scoring is based on getting the right result plus how well the model is structured and how easily the reviewer can flex the cases and contingencies
  • You may also encounter a few questions about your opinion on the investment itself
Key Concepts Tested The standard LBO Model Test will usually include the following elements - this is the "bread & butter" LBO Model Test.

  • Debt schedule with cash sweep
  • Sources & Uses including rollover equity
  • Purchase Price Allocation (Closing B/S, Goodwill Creation, DTLs)
  • Debt schedule with “Cash Sweep” and PIK interest
  • Sponsor Monitoring Fees
  • Returns Sensitivity Analysis
Standard LBO Modeling Test + the Following Additional Features:

  • Revenue Build
  • Case Functionality
  • Original Issue Discounts (“OIDs”)
  • Convertible Preferred Equity Investments
  • Add-on Acquisitions
  • Dividend Recaps
  • Management Option Pool
  • Management Compensation Earn-Outs

If any of these concepts are unfamiliar to you, check out our Private Equity Q&A Article before proceeding

Note that you will likely not be asked to model all these features in a single LBO model test.

The model we will build together should take you less than 3 hours, not 4 hours. That's because we will provide you with all the relevant assumptions in a convenient format (Excel) rather than the PDF or Word document you'd get in the test, forcing you to manually input the historical data, which usually amounts to 30-40 extra minutes of work.

The assumptions used for the different cases are rarely provided in the format we will. In most modeling tests, you will be given the CIM, which will contain the management projections. It is up to your discretion whether to use this as your "Base Case" or to adjust it as you feel necessary. Sometimes, you might even find a supplementary industry report (just a few pages) inside the packet to help you determine your assumptions for all the cases. Otherwise, the firm will expect you to have an understanding of the unit economics of the industry and be able to adjust your assumptions accordingly without any external resources.

You might also be surprised to hear that unlike the Standard LBO Modeling where you have to build a full 3-statement model, you will not be asked to do so here. Some of the concepts tested in advanced LBOs make the balance sheet modeling too complex given the time allotment. So for example, you might be asked to model an add-on's impact on IRR, but not to create another closing B/S for that add-on transaction and then integrate it into the platform's B/S forecast.

Before We Begin...Download the LBO Modeling Files

File 1:  Modeling Test Instructions & Case Overview (PDF)

File 2: Excel File (Below)

Submitting ...

Step 1. Model Assumptions

Entry Valuation

For this model, the entry valuation will be based on the $15.00 offer price per share rather than an EV/EBITDA multiple given that JoeCo is a publicly-traded company. We can arrive at the pre-deal share price of $12.00 as $15.00 / (1+25%).

In the next step, we will get to equity value, which will require us to determine the total share count.

To calculate the fully diluted shares outstanding, we will use the Treasury Stock Method ("TSM"), which includes all dilutive securities (restricted stock and in-the-money options) in the share count, but assumes the option proceeds are used by the company to buy back shares to mitigate the dilutive impact.

Below is the calculation of the net dilution for each options tranche:

So while the Prompt tell us there are 5.5 million options, the treasury stock method calculates the option proceeds for each tranche of options and calculates how many shares can be bought back at the offer price. Therefore, we see the net dilutive impact is only 3.0 million shares, not 5.5 million.

Treasury Stock Method (TSM) in Excel

As the screenshot above shows, to calculate the dilutive impact under the TSM, we will first insert an "IF" function that says if the strike price is below the offer price per share, then all the options in the tranche will be exercised by the holders, which are often employees and management (i.e. stock-based compensation). But the option holders' end of the bargain is to pay JoeCo for each share price at the strike price as part of the contract.

Under the TSM, we assume JoeCo will use those proceeds received to repurchase shares to minimize the dilution. The number of shares repurchased will be calculated by multiplying the number of new shares by the strike price and then dividing that by the offer share price of $15.00.

After doing the TSM calculation for each tranche, we can sum up the three tranches and see the net dilution impact was 3.0mm shares. The fully diluted shares outstanding of JoeCo is thus 318mm:

Now that we have the diluted share count, the entry equity value can then be calculated by multiplying the offer price per share of $15.00 by the fully diluted share count we just calculated, which gets us to $4,770mm.

  • Net Debt: JoeCo's pre-deal balance sheet has $450mm in gross debt and $200mm in cash, for net debt of $250mm.
  • Implied Entry Multiple (EV/EBITDA): To back into our implied purchase entry multiple, we will add the net debt of $250mm to get to an entry enterprise value of $5,020mm and then divide that by JoeCo’s LTM multiple of $345mm to arrive at 14.5x as the implied entry multiple (EV/EBITDA) paid to take JoeCo private.

Entry Offer Price vs. Entry Multiple

In this Prompt, we were given an offer price as the initial valuation input, which led us to equity value directly, from which we add net debt to get to enterprise value. If this was a private company, the explicit entry valuation assumption would be the EV/EBITDA multiple, which would arrive at enterprise value directly, from which we would subtract net debt to get to equity value.

Transaction Assumptions

With the initial valuation done, we will now lay out some of the key transaction and financing assumptions provided in the Prompt – beginning with the "Transaction Assumptions":

  • Transaction Fees: The transaction fee assumption was 2.5% of the offer equity value - later in the Sources & Uses section we will multiply this amount by the entry equity value of $4,770mm to get $119mm in transaction fees.
  • Cash to Balance Sheet: We assume JoeCo will require $25mm on the B/S at close.
  • Monitoring Fees: An annual monitoring fee of $5mm will be paid exclusively to WSPCP. This sponsor consulting fee will not be divided amongst all the equity owners as it is compensation for the services provided by the consulting arm of WSPCP.
  • Tax Rate: The final assumption for this section is the 25% tax to be used throughout the entirety of the model.

Financing Assumptions

Moving onto the financing assumptions:

  • Maximum Revolver Capacity: The maximum revolver capacity was stated as $1,000mm. We can infer WSPCP and the 2nd Sponsor were able to negotiate and obtain a revolver of this magnitude due to JoeCo’s past profitability and credit rating.

The next three financing assumptions are straight-forward and simple hard-codes:

  1. Unused revolver commitment fee is 0.25%
  2. Financing fees amortization period is 8 years
  3. Financing fee is 2.0% for all the debt tranches raised excluding the revolver
Asset Based vs. Cash Flow Revolver

Unlike a traditional asset-backed revolver, which is tied to a borrowing formula based on a percentage of the borrowers' inventory and A/R balance, this revolver is unsecured by collateral and based on the future cash flows of JoeCo (i.e. cash-flow based revolver).

The institutional lender that provided the revolver and unitranche term loan was made aware of the sponsors' plan to make an add-on acquisition and perform a dividend recap, and therefore provided this line of credit to ensure JoeCo has sufficient liquidity available (i.e. "cushion").

Debt Assumptions

We will now turn to the debt assumptions.

Note: If you are unfamiliar with the basics of leveraged finance, read this guide before proceeding.

  • Revolver: Left undrawn at purchase and is priced at a floating rate of "LIBOR + 400" with no floor.
  • Unitranche Term Loan: Rather than having senior and junior tranches of debt in the capital structure, an increasingly popular financing structure is Unitranche debt (see more color below). Total Unitranche debt raised was 6.0x LTM EBITDA, which comes out to $2,070mm.
  • Recap Bonds: Let's ignore Recap Bonds for now and return to this later. Just note the terms: fixed interest rate of 8.5% with no prepayment optionality.
Unitranche Term Loans (TLs)

Unitranche TLs are a very popular form of financing historically in the lower middle market deals but increasingly popular in larger middle market deals.

Prior to the proliferation of Unitranche Debt, there were two traditional ways to structure debt where you had senior and junior debt:

  1. First and second lien debt
  2. Senior and subordinated debt

In both frameworks, the higher priority debt would have a lower interest rate and the lower priority debt would have a higher interest rate.

The key characteristic of Unitranche Debt is that it is – as the name suggests – just one tranche of debt instead of the two and is priced at a blended interest rate.

Unitranche debt comes from direct lenders – as opposed to debt that is syndicated to many institutional lenders.

The key advantages to borrowers over traditional credit facilities is that it enables borrowers to have a "one stop shop" financing. Borrowers have only one set of loan documents, one set of covenants, and a much simpler and faster process to close.

Lastly, syndicated deals always carry the risk that original pricing indications provided to the borrower are different from what happens during syndication. Since Unitranche financing comes from direct lenders, it eliminates risk that pricing terms will change ("flex") in syndication.

Read More:

Original Issue Discount

You may have noticed two new columns referring to OIDs. An OID stands for “Original Issue Discount."

Primer on Original Issue Discount (OID)

Imagine you ask a friend to lend you $10,000 at 10% over 3 years.  Your friend refuses, so as a way to make it more attractive, you offer the following "how about instead, you just give me $9,800 but when I pay you back, you'll get $10,000, plus the 10% interest each year on the $10,000.

That's basically OID.

So when debt is issued at a discount to par value (98 in this example) , the borrower gets $0.98 for every dollar owed.

For OID accounting, the difference between the principal ($10,000) and the issuance amount ($9,800) is $200.  This $200 must be amortized over the term of the debt (3 years) and treated as non-cash interest.  This is identical to financing fee accounting (i.e. increases amortization, reduces earnings before taxes, and is a non-cash add-back on the cash flow statement).

Modeling the OID

We calculate the $ OID in our example by taking the Unitranche Term Loan issued at (98 OID / 100) x $2,070mm = $41mm.

Since financing fees and OID will be treated identically in our model, we can simply lump this $41mm to the total financing fees column on the far right. The financing fees are calculated as the 2% Financing Fee Assumption x the $2,070mm Unitranche Debt Principal. Together the OID and fees amount to $83mm.

Dividend Recap Assumptions

To finish up the model assumptions section, we will list out the dividend recap and add-on related assumptions.

Master LBO Modeling 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.
Learn More

Dividend Recapitalization Introduction

Dividend recaps refer to when a sponsor raises debt to finance a dividend (kind of like a home equity line). This is a common strategy utilized to monetize an investment before a complete exit and has the benefit of increasing the fund’s IRR from the earlier receival of proceeds.

Completing a dividend recap is considered to be a risky action that should only be undertaken once the LBO has been, for lack of a better term, “validated” (i.e. proceeding better than originally anticipated) and the company has shown it can handle the additional lien.

For this reason, a dividend recap is usually done a few years after the initial acquisition – but there are some exceptions. For example, Bain's Dividend Recap of BMC that was covered in detail in our LBO course is a great example of a private equity firm rapidly recovering some of their initial investment back.

Modeling the Dividend Recap

First, we create a toggle for the dividend recap as we need the flexibility in our model to not only choose the year but to switch it off.

Assume the lender provided two maintenance covenants and one additional restriction that JoeCo must abide by:

  • Leverage Multiple: (Total Debt / EBITDA) must remain below 6.0x in all years and a dividend recap cannot be completed if the additional debt raises the leverage multiple above this threshold.
  • Interest Coverage Ratio: (EBITDA / Interest Expense) must stay above 2.0x
  • Timing Restrictions: The dividend recap cannot be done until two years have passed, therefore 2023 is the earliest the recap can be undertaken. we will create a toggle for the years the dividend recap can be done. Due to the date-related covenants, we will create a drop-down list where the only options are restricted to N/A, 2023, 2024, and 2025. We're going to assume the recap debt will be raised at the beginning of the "Recap Year," so the available recap debt capacity is based on the prior year LTM EBITDA and amount of debt outstanding, not the current period. Let's also assume that the recap is completed right at the beginning of the "Dividend Recap Year", meaning the full interest amount and mandatory amortization will be due in the year it was raised.

As long as these two maintenance covenants and the timing restriction is followed, the sponsors are free to raise additional debt to pay themselves a dividend without having to refinance any debt.

Add-On Assumptions

Just as we did for the dividend recap, we will first list a toggle to have the option to switch it off.

The "LTM EBITDA Acquisition Multiple" is assumed to be 9.5x and the lender has provided a $1,000mm revolver. The acquisition of TeaCo, if completed, will be funded entirely by this revolver.

Lastly, we will include a drop-down list for the year the add-on is completed. Unlike the dividend recap, the add-on can be completed at any given year.

Step 2. Sources & Uses Table

We will now complete the Sources & Uses table. If you've gone through the Standard LBO Modeling Case, this will be familiar, with some key distinctions:

  1. This transaction is NOT completed on a CFDF basis
  2. There are two financial sponsors involved this time
  3. There is no equity rollover.

Below is the completed Sources & Uses schedule for this take-private:

Uses Side

We start on the Uses side with the "Purchase Equity Value", which will be the $4,770mm entry equity value and NOT the entry enterprise value. The reason being, the deal is a take-private and not done on a cash-free, debt-free basis.

Next, we have a new line item named “Refinancing of Net Debt”, which is equal to all the existing debt on JoeCo's B/S minus its entire cash balance. The rationale being, the acquired cash can be used to help with refinancing JoeCo’s debt at any given moment. This comes out to $250mm in debt that needs to be refinanced.

Right below this line item is the "Cash to B/S", which will ensure the minimum cash balance does not dip below $25mm since right above we used the entire cash balance in the formula for the net debt refinancing.

Note: An alternative could have been to model "Excess Cash on B/S" on the "Sources" side, which is calculated by subtracting the Cash to B/S from the total cash balance. Then on the "Uses" side, there would be a line item for "Refinancing of Existing Debt" that just links to the total existing debt.

The final two "Uses" are the "Transaction Fees" and "Financing Fees". The financing fees of $83mm can just be linked to the calculation in the debt assumptions table. And the transaction fees are calculated as 2.5% of the offer value, which comes out to $119mm in advisory fees paid to the investment banks, consultants, accountants, etc. that helped complete this deal.

Sources Side

Moving onto the "Sources" side, we will list the debt sources first since the equity required is a function of the amount of debt raised.

  • Revolver: Left undrawn at purchase
  • Unitranche: $2,070mm was raised via the unitranche term loan
  • Equity: WSPCP invested $475mm of preferred equity, and the remaining equity required was plugged by the Lead Sponsor, which amounts to $2,702mm entirely in the form of common equity.

Recall, the equity invested by WSPCP is of convertible preferred – which means the preferred shares include the option for the WSPCP to convert the shares into a fixed number of common shares.

This means that WSPCP will receive either:

  • Option 1: The "Accrued Value" of the investment at the 8.5% PIK rate – which has the benefit of downside protection via the PIK debt-like feature
  • Option 2: The "Conversion Value" to common shares at the strike price of $1.25 – this provides WSPCP the option to participate in the potential upside of the common equity

This is a great place to mention the disconnect between equity and debt investors in the context of a dividend recap. The lender does not have the option to participate in the upside regardless of how well JoeCo performs. For the revolver and unitranche term loan, the most the institutional lender could earn is the interest payments and being repaid the principal amount.

If the sponsors decide to proceed with a dividend recap, they are de-risking their investment since they are receiving some of their initial investment back before the exit. From the perspective of the lender, the bankruptcy risk of the borrower has just increased from the additional lien on the company.

Hence, in real life, debt covenants are significantly more restrictive and would typically include provisions that some (or all) the existing debt must be refinanced before a dividend recap could be completed.

Note: This is the reason we intentionally had only one lender involved in the financing of this LBO – this lender pre-approved of these plans and the new debt raised is coming from them.

Step 3. Purchase Price Allocation

Next, we will go through the process of purchase price allocation:

Purchase Price Allocation (“PPA”)

As we noted earlier, we are not going through this step to forecast the 3-statements as we did in the Standard LBO Model Test. Instead, we are completing the PPA to calculate the values of the PP&E and Intangible Asset Write-Ups, the Incremental Amortization/Depreciation, and the Un-Wind of Deferred Tax Liabilities (“DTLs”).

All of these items must be calculated because they are reflected in the I/S and impact the free cash flow build that determines the amount of cash available to paydown debt.

To get started, we will first link the purchase equity value from the transaction assumptions and subtract the book value of equity and existing goodwill to arrive at the "Allocable Purchase Premium" of $3,870mm – this is the excess paid that will flow towards goodwill once adjusted for write-ups/write-downs and DTLs.

As the prompt mentioned, intangible assets were written up 10% with a useful life assumption of 15 years. This amounts to an "Intangible Assets Write-Up" of $387mm and an "Incremental Annual Amortization" of $26mm. We must not forget the DTL created, which will be $97mm and will be un-winded by $6mm each year.

Now for the PP&E write-up, the write-up was 10% of the LTM PP&E balance of $1,000mm to $1,100mm. As you can see, the "PP&E Write-Up" amounts to $100mm and the "Incremental Annual Depreciation" will be $13mm. The associated DTL created is $25mm and the annual un-wind will be $3mm each year.

With the intangible assets and PP&E write-up sections completed, we can now calculate how much goodwill was created in this LBO. As we can see below, the total goodwill created was $3,504mm.

Step 4. Closing B/S Adjustments

With the PPA complete, we will now put together the Closing B/S – which is not necessary for this particular LBO modeling test but should still serve as good practice. It takes no more than a few minutes to complete and it is an opportunity to check your work.

Unlike our Standard LBO where we build out a full 3-statement model – you do not get many chances to check your work so you may find this beneficial even if it is not necessary.

For example, if you double-counted the Cash to B/S in the Sources & Uses table, the ending balance sheet should not balance and you will be able to fix your mistake immediately. While we do have a "Check" for the Sources & Uses table, it is not that useful because there is an equity “plug” that makes the two sides balance. You would have to make an absurd mistake in the S&U schedule for it to not balance.

Assets Side

As we can see, the calculated pro forma goodwill post-adjustments comes out to $3,504mm and flows into the Closing B/S and the existing goodwill is wiped out to prevent double-counting.

For the rest of the assets side, Cash to B/S is a positive debit of $25mm and the existing cash balance is wiped out. But rather than being wiped out to go to the seller as it would in a CFDF deal, the amount was used for the refinancing of the existing pre-LBO debt.

Next, we see the $100mm write-up of PP&E and the closing intangible assets balance of $387mm as we calculated in the PPA.

Liabilities & Equity Side

On the liabilities side, we see the existing capital structure was completely wiped out and the "Sources" side was brought into the closing B/S.

The pre-LBO debt of $450mm was wiped out, while the new debt tranches raised are inputted as credits.

The capitalized financing fee, which includes the OID, is shown as a debit of $83mm and the "Deferred Tax Liability" of $122mm is shown on the credits side.

Finally, the existing book value of equity of $1,100mm is wiped out alongside the extra deduction of the transaction fees on the debits side, and then the new equity investment of $3,177mm will flow into the credits side.

Step 5. Add-On Financial Build

Our end goal for this step is to get the following pro-forma financials for TeaCo:

Notice the case toggle right above. We want to be able to easily toggle between three possible cases for the Add-On's revenue and EBITDA forecasts:

1) Base Case

The base case represents the conservative forecast that is most probable to occur. The assumptions used should be reasonable and in-line with past historical performance.

As a general proxy, if you were to average the past growth rates and margins of the company, the outputs should be close to what is being projected. The exception would be if this were a turnaround deal, in which the past performance would not reflect future expectations.

2) Upside Case

The upside case tends to be close to management guidance. Management projections usually contain some level of optimism (and they rightfully should). But one factor to consider when assessing management assumptions is the potential for an earn-out as part of the transaction. An earn-out is used to bridge the gap between valuation differences between the buyer and seller, and will be a contingency payment based on reaching an EBITDA (or Revenue) target.

Earn-Outs Commentary

In nearly all cases, a seller and sell-side representative has the incentive of selling at the highest valuation. However, if a private equity firm has a history (i.e. reputation) of structuring deals with earn-outs, the sell-side advisor may suggest to the seller to be more conservative.

Earn-Outs are based on hitting target figures, and if the management pitches unrealistic numbers – they have effectively shot themselves in the foot because the targets used are the ones that came out of their mouth. In these scenarios, the deal would either not close or the management would accept a lower valuation and upfront payment.

3) Downside Case

The downside case reflects how a business should perform during an economic downturn.

The severity of the impact and how much the performance deviates from the Base Case will be dependent on the industry the company operates in. Hence, PE investors pursue investment opportunities in non-cyclical industries. If you switch our "Case Toggle" to "3", the Downside Case will be shown and you will see how the revenue declines in the forecast and the margin contraction.

In this case, we expect the downside case is actually not too bad because of the non-cyclicality of this add-on.

Note: Often, the downside case in a LBO model will reflect "disappointing" financial results, rather than the company's performance under a recession. In these situations, there will be another case specifically named the "Recessionary Case".

The TeaCo Revenue Build

So now that we know where we're trying to go, how do we get there?

We need to create a revenue build for TeaCo that reflects the unit economics provided in the Prompt.

To forecast the revenue of TeaCo, we have first broken the business down to some of its most basic unit economics:

Revenue Drivers:

  • The basic idea is that TeaCo's Revenue will be a function of: "Average Revenue per Store ($)" × "# of Stores"
  • Whereas, "Average Revenue Per Store" is a function of: "Average Order Price ($)" × "# of Orders per Store"

Using this logic, our "Average Revenue Per Store" comes out to approximately ~$400k in 2020.

Quick Tip

Be careful of the units used – as you can see in our model, we explicitly wrote out the units used for each to ensure the reviewer is aware of what units we are using.

The "Average Order Price" was the unit economic we were working our way down to. The order price or average selling price (i.e. "ASP") is usually the minimum level of specificity a LBO Model Test will need you to have.

The purpose of doing this is: you want specific figures to reference (i.e. the average order price increased $X amount and the number of new stores opened was X, rather than revenue will grow X% just because it seems reasonable).

Now based on these figures, we can build out our case functionalities. The three assumptions that will serve as the revenue drivers are the:

  1. # of New Store Openings
  2. Δ in Annual Orders Per Store
  3. Δ in Average Order Price

We will make assumptions for how these three variables will differ under the three cases, and this will ultimately drive our revenue for the year.

The three revenue drivers we will forecast across the three case assumptions are shown below:

The "OFFSET" function will pick the case selected based on the toggle. For example, this above picture is reflecting the "Upside Case" of TeaCo.

The next step is to now work our way up to total revenue each year forecast. We will begin with the "Average Revenue Per Store", which will be a function of our "Average Annual Order Count" and "Average Order Price" assumptions – just be sure to divide the store count by 1,000 before multiplying the two.

As you can see the "Δ in Annual Orders Per Store" and "Δ in Average Order Price" were pulled from the assumptions table, and will impact the unit driver it is connected to. For example, the $0.50 increase in average order price and the 25 new stores opened to bring the number of stores in 2021 to 75.

This ultimately drives the "Average Revenue Per Store", which is not individually forecasted as we mentioned earlier. We want to identify the unit economics that drive the average revenue per store, not just forecast it without having referenceable figures. For example, on the job you would want to be able to say "TeaCo needs to open X amount of stores and increase its average order price by X".

Then, we will multiply the "Average Revenue Per Store" by the "TeaCo Store Count", which is being driven by our assumption on the number of new stores opened each year. Doing so will give the total revenue of TeaCo for each year.

To finish up the forecast, we created three case functionalities for the Gross Margin %, SG&A % of Revenue, and R&D % of Revenue, and integrated them into our forecast.

For most LBO Model Tests, this is perfectly fine to do and usually you will actually not have enough details to do a full expense build. At the most, you might do some type of simple forecast based on employee count and then back into the annual salaries if the figures are provided, or do what we just did above for individual segments.

This model tutorial is intentionally keeping these forecasts simple to focus more on the LBO concepts, enroll in our FSM Course if you want to learn more about in-depth, segment forecasts.

The completed forecast of TeaCo is shown below. As you can see, TeaCo is currently in the growth stage of its maturity cycle and therefore it might be best for JoeCo to complete the add-on as early as possible before the purchase price would exceed $500mm.

Lastly, we tracked our add-on acquisition and the implied purchase price in the schedule below:

Step 6. Pro Forma Financial Forecast

Now that TeaCo's financial build is complete, we can forecast JoeCo’s financials in a similar fashion.

Pro Forma Income Statement

Our goal in this section is to forecast the income statement:

And then the free cash flow build:

JoeCo Standalone Operating Forecast

Below the financial forecast, we placed the assumptions driving JoeCo's forecast. Per usual, we will have these assumptions placed below the summary financial forecast.

Note that the operating case toggle should be separate from the one used for TeaCo as we want the flexibility to view the two companies under separate assumptions.

For example, if JoeCo is performing in-line with its upside side, it may lead the sponsors to question whether to just keep doing what is working and reinvest into JoeCo, particularly if TeaCo has been under-performing.

This is also the reason we have separate line items for the pro forma consolidated financials. We can view the impact of TeaCo by switching the toggle on / off and viewing the changes in the pro forma growth rates and margin profile.

Since you've already gone through how to do a revenue buildup for TeacCo, for JoeCo, we use a simple, % revenue growth approach for JoeCo in the interest of time. In addition we also include base line, upside and downside assumptions for JoeCo's operating expenses (as well as changes in NWC (which will be used later to forecast cash flows):

TeaCo Add-On Integration

Integrating TeaCo’s financials into the model will use the toggle switch we created earlier. All of TeaCo’s line items will use an “IF (AND(“ function to confirm:

  1. The "Add-On Toggle" is switched to "Yes"
  2. The “TeaCo Acquired?” line item states “Yes”

If both are "Yes", then TeaCo outputs will be added to JoeCo's financials:

We follow the same approach for COGS, SG&A and R&D to arrive at EBITDA.

Management Earn-Out

Leave this line blank as we will return to it shortly. We calculate this separately (even though this is an SG&A expense), to avoid a circularity since the earn-out itself is based on EBITDA.

EBITDA → EBIT

Now that we have the calculations for EBITDA set, we will subtract "D&A", the "Intangible Assets Write-Up Amortization", "PP&E Write-Up Depreciation", and the "WSPCP Monitoring Fees".

This should be very straight forward – the D&A is projected as a percentage of revenue while the incremental amortization and depreciation write-ups are pulled straight from the PPA schedule, and the WSPCP monitoring fees were listed in the assumptions section as $5mm each year.

Interest Expense and Amortization of Financing Fees & OID

While the interest expense cannot yet be calculated since we have not built the debt schedule, the amortization of the financing fees and OID can be calculated by dividing the $83mm in total financing fees by the financing fees amortization period of 8 years to get a value of $10mm.

We will also need to return to this section later to reflect interest and fees from recap debt

Taxes

Once we subtract taxes based on the 25% tax rate assumption, we have arrived at net income, the “bottom line.”

Free Cash Flow Forecast

We can now construct the our free cash flow build:

Although we don't break up the cash flow statement formally into the 3 sections (operations, investing and financing), you can see that they're all there:

Cash from Operations

  1. Add back all the non-cash expenses such as "D&A", "Amortization of Financing Fees & OID", "PIK Interest", "Intangible Assets Write-Up Amortization", and "PP&E Write-Up Depreciation"
  2. Deduct the "Deferred Tax Liability Unwind" and "Δ in NWC."

Cash from Investing

  1. The usual cash outflow, Capex, will first be accounted for
  2. Reflect the cash consideration paid for the TeaCo add-on using a similar "IF(AND(" function used earlier:

Cash from Financing

  1. While we haven't built the debt schedule yet, in this area we will eventually subtract the "Mandatory Unitranche Term Loan Amortization"
  2. There are two new line items below, the "Recap Debt Draw", and "Recap Dividend Issuance" that we will also leave blank for now
  3. The remaining line items such as the "Revolver Drawdown / (Paydown)", "Free Cash Flow Available (Optional Paydown)", "Cash Sweep", "Cash Flow After Financing Activities", and the Cash Roll-Forward cannot be filled out at this point since the debt schedule has not been completed.

The result is Free Cash Flow (Pre-Revolver):

Step 7. Management Performance

Before we turn to the Debt Schedule, let's calculate the earn-out that we left off the income statement earlier.

Each year, the management team has the potential to receive up to $10mm in additional compensation if all three threshold levels are met.

If the "Minimum EBITDA" hurdle is met, $2mm is earned, meeting the "Midpoint EBITDA" is an additional $3mm, and "Outperformance EBITDA" results in $5mm to total $10mm as the maximum earn-out in a given year.  We can set this up as follows:

We then calculate the earnouts based on the LTM forecast for EBITDA:

Next, we will calculate the pro-rata adjustments – which refer to how management may have missed a certain target, but the excess EBITDA over the threshold will be compensated in proportion to how close to reaching the next target the EBITDA was.

The "Excess EBITDA" calculation will use an “IF(AND(“ function with two logics.

  1. Makes sure the LTM EBITDA was greater than the previous target hurdle, meaning the next target was not met but there is some excess EBITDA
  2. Then, it will confirm the target in question was indeed missed (i.e. equal to "0")

If both logics are true, the actual LTM EBITDA will be deducted from the missed target EBITDA.

The "Excess Over Minimum" is shown:

Notice how for the examples shown above, the "Excess Over Minimum" is zero because the "Midpoint EBITDA" target was met each period under this case selection. This is what the 2nd logic check is confirming (i.e. the "F282=0" in the picture above).

Next, the "Excess Over Midpoint" is shown:

Thus, the only relevant pro-rata adjustment for 2021 is for the "Outperformance EBITDA", in which the "Excess Over Midpoint" is $38mm. From a glance, we can see this is just ~$1mm short of meeting the target, so the pro-rata adjustment should bring the total bonus paid close to $10mm.

The formula for the "Midpoint EBITDA" pro-rata adjustment is shown below:

The "Excess Over Minimum" is zero each year because the "Midpoint EBITDA" was met, so the only pro rata adjustments under this case selection will be for the "Outperformance EBITDA".

The pro rata adjustment for the "Outperformance EBITDA" is shown below. As you can see, the formula takes the "Excess Over Midpoint" and divides it by the difference between the "Outperformance EBITDA" and "Midpoint EBITDA". Then, this percentage will be multiplied by the "Outperformance EBITDA" proceed of $5.0mm.

For 2021, the "Outperformance EBITDA" pro-rata adjustment will be $4.87mm.

Below is the completed "Management Contingency Payments" table:

As we mentioned earlier, the "Outperformance EBITDA" target was just ~$1mm off, so as a sanity check this bonus amount in 2021 (i.e. the $9.87mm payout) appears to be right.

The amount in bonuses paid will vary significantly by the assumptions used. For example, if you switch the JoeCo case selection to the "Downside Case", not a single penny is paid out to the management team in extra compensation because not even the minimum was met.

There are no pro rata adjustments for almost meeting the "Minimum EBITDA". For the first target, it is an "all or nothing" type arrangement.

This is the reason behind why there are only two pro rata adjustments for almost hitting the "Midpoint EBITDA" and nearly reaching the "Outperformance EBITDA".

On the flip side, any excess above the "Outperformance EBITDA" will NOT result in any extra compensation either. The maximum that can be earned is $10mm each year.

If the target is met every single year, this is in no way a negative for the sponsors as it means that JoeCo is performing very well.

In a sense, this type of compensation earn-out is an attempt to replicate management rollover equity, but there are still some limitations (i.e. rollover equity is preferable, given there is more for the management to lose than missing out on some bonus payments).

But to wrap this section up, the "Total Bonuses Paid" will be the sum of the "Total Compensation Earn-Outs" and the "Pro-Rata Adjustments". This line item will flow into the financial build right before the "Pro-Forma EBITDA".

Step 8. Debt Schedule

In order to finish the income statement and cash flow statement we have to complete the debt schedule.

The first step will be to determine if there's enough cash to pay down the revolver:

...Then see is there's any excess cash remaining to sweep into the Unitranche Debt:

...And finally we can determine if there's excess debt capacity to fund a dividend recap:

“Excess Cash Available for Revolver”

To start the debt schedule, we reference “Free Cash Flow (Pre-Revolver)” on the CFS into the Debt Schedule and rename the line item "Excess Cash Available for Revolver."  We want to see this number without having to constantly scroll back up to the CFS.

Keep in mind that this line is post-mandatory amortization for all the debt tranches, thus the "Excess" in the name. The only places this excess cash can go towards are:

  1. Paydown of Revolver
  2. Additional Prepayment of Unitranche

Since the Prompt said no prepayment is allowed for Recap Debt, we do not have to model a cash sweep for that.

Revolver

The mechanics of the revolver is no different from the previous Standard LBO Modeling Test, the only notable difference is the sponsors are given a much larger and fixed maximum capacity of $1,000 million:

Interpreting what's happening:

JoeCo paid $409mm to acquire TeaCo in 2021 and did not have sufficient cash to fund this acquisition using the free cash flows it generates alone. Thus, $337mm was drawn from the revolving credit line as intended.

While there is still significant flexibility for JoeCo to operate following the add-on acquisition, we included a "Revolver Compliance Check" that ensures the "Ending Available Capacity" never dips below zero.

Modeling Test vs. On-the-Job

This usage of the revolver as a funding source of an acquisition contradicts many of our past articles where we said the revolver is meant to meet short-term working capital requirements and act as a corporate credit card for emergencies.

But when it comes to modeling tests, it is actually quite common for this type of assumption to be made where an add-on or a significant capital expenditure is funded using a revolver.

After 2021, free cash flows turn positive and all go toward paying down the revolver given its seniority in the capital structure. By 2024, the revolver balance has been fully paid down and then we see the first cash sweep for the unitranche term loan.

In terms of the revolver's pricing, it is the floating rate of "LIBOR + 400" as we are used to seeing and the unused commitment fee will be $2.5mm in the years the revolver is not used.

Unitranche Term Loan

Next in our debt waterfall, we have the unitranche term loan.

Recall the unitranche term loan has a mandatory amortization of 5% off the original principal amount each year – as we can see the annual mandatory amortization of $104mm paid out.

Now the question is whether there's any excess cash to pay down the principal even further. Because the revolver gets paid down first, there's only excess cash available in the last two years: Cash available to sweep into the Unitranche is calculated as the “Excess Cash Available for Revolver” plus the “Revolver Drawdown / (Paydown)” from the revolver roll-forward right above.

Since we don't want to have the principal balance turn negative, we also add a MIN function  such that the cash sweep formula is the minimum of

  1. "SUM" of the Beginning Balance and Mandatory Amortization
  2. The “Excess Cash Available for Unitranche Term Loan"

Unitranche Interest Expense

In terms of pricing, the unitranche term loan is priced at LIBOR + 400 with a 1% floor. Since LIBOR is above 100 basis points in all the forecasted years, the floor actually has no impact on the interest rate.

And as usual, the interest expense will be calculated off the beginning and ending balance of the term loan – which you can see does not actually reduce until the revolver is paid down as the cash sweep is delayed since the revolver comes first in the waterfall. But once the revolver balance returns to zero, we see the full cash sweep provision of the unitranche term loan in full effect.

Dividend Recap Term Loan

We will now cover the mechanics of the dividend recap, starting with the total available capacity that could be drawn to payout the one-time special dividend to equity shareholders. By the end, we'll see that in 2024 that Sponsors can dividend recap $1.4 billion out of the company:

“Maximum Recap Debt Capacity” for the current year will be calculated by multiplying the EBITDA of the previous year by the “Maximum Leverage Multiple” of 6.0x. It is very important to recall that the dividend recap is done right at the beginning of the year, so this is the reason we use the prior year figures.

So for the example above, the $748mm is in the column for 2022. However, if a dividend recap were to be done in 2023, this would be the amount that could be drawn, as opposed to the $1,427mm.

So the proceeding step is to deduct the total outstanding debt of the previous year to arrive at the “Available Recap Debt Capacity”.

For the “Available Recap Debt Capacity”, we include two additional logical functions to the formula:

  1. "MAX" function is used to ensure the output is never below zero
  2. Confirms the "Recap Toggle" is equal to 1 (i.e. switched to "Yes")
Debt Capacity Commentary

As EBITDA continues to grow and more debt is paid down via amortization and the cash sweep, the amount the sponsors can pay themselves increases.

The reason the available capacity is so low in 2023 relative to the other years is due to the acquisition of TeaCo, which was funded almost entirely by the revolver. Recall, 6.0x is the initial leverage used during the transaction and we have a covenant in the lender agreement that we will not raise any additional debt to brings our leverage multiple above this level.

This is likely the reason why the Lead Sponsor said the most likely scenario would be a dividend recap in 2024, when the available capacity would be $1,427mm. The Lead Sponsor here is attempting to balance de-risking their investment by receiving a portion of their initial capital back and paying themselves as large a dividend as possible. In addition, an earlier dividend will have a more positive impact on IRR – which is the main marketing metric used when a firm is raising capital for its next fund.

Next, we will have a compliance check for the two maintenance covenants.

But to do this, we need to calculate the credit metrics the covenants are based on:

  1. Total Leverage Multiple = Total Debt / EBITDA
  2. Interest Coverage Ratio = EBITDA / Interest

When calculating the leverage multiple and coverage, use the figures for the current period.

The reason being, our "Available Recap Debt Capacity" already ensures the leverage multiple does not exceed 6.0x.

So if the sponsors completed a dividend recap in 2023, the amount that can be raised will be based on 2022, but the credit metrics we use for the compliance tests are the 2023 EOY numbers.

Because maintenance checks are completed on an annual basis typically at the end of the year, it would not make sense to look at the previous year's credit metrics. It is the year the recap was completed and then the forward-looking projections that matters to JoeCo and the lender.

Now moving onto the roll-forward for the dividend recap term loan. The "Plus: Dividend Recap" will use a simple "IF" function that confirms if the "Recap Year" selected is equal to the current year, then the "Available Recap Debt Capacity" from the previous year will be output. This represents the amount in recap debt that could be raised in the current year.

Below is the formula for the dividend recap draw for 2024:

Note that both of the maintenance covenant tests are both in compliance, not just in 2024 but 2025.

Note: While we were not asked to build out the B/S, be aware that the dividend issued comes out of the shareholders’ equity account. This would be an example of a scenario when shareholders’ equity could turn potentially negative.

Note that if a dividend recap is completed, there should only be one outflow in the "Plus: Dividend Recap" line item, as shown below.

We cannot include the draw in the beginning balance line item, or else the roll-forward would not work properly. As a sanity check, confirm the beginning balance is empty in the year of the recap, and that there is only one draw of the recap debt (assuming a recap was done).

For the interest expense calculation, the minor difference mentioned above will make the calculation a bit less convenient.

First, we will start with a circuit breaker as with all interest expense calculations. Then, we will take the "AVERAGE" between the:

  1. "MAX" between the "Beginning Balance" of the Recap Bonds and "Plus: Dividend Recap"
  2. The "Ending Balance" of the Recap Bonds

And then we will multiply by the interest rate of 8.5% as shown below:

In the last step, we will calculate the "Recap Bonds Financing Fees Amortization", which is just calculated as "MAX" between the "Beginning Balance" and "Plus: Dividend Recap" multiplied by the 2% Financing Fee assumption and then divided by the Financing Fee Amortization Period of 8 years.

The reason we keep using the "MAX" function for the recap debt is because we do not know when the recap is done, therefore we have no other option but to do this.

As we mentioned earlier that we would have to return to this, the "Recap Bonds Financing Fees Amortization" will be added to the "Amortization of Financing Fees & OID" in the financial build.

Finalize Financial Forecast

Now that the debt schedule has been completed, we can return the financial forecast to account for portions skipped over (e.g. interest, mandatory amortizations, cash sweep, and the dividend recap / draw).

The interest, mandatory amortization, and the cash sweep should be very straight-forward as you are just linking or summing up the relevant cells directly from the debt schedule.

For the "Plus: Recap Senior Notes Draw", we will link to the recap debt schedule and pull the new debt amount raised for the dividend. Then, right below it will be reflected as an outflow of the same amount as a one-time special dividend to the shareholders in the “Less: Recap Dividend Issuance”.

Thus, the net impact on cash should be zero since the two will off-set each other in effect.

Note: This is unrealistic but usually how it is done in model tests. In real life, you would have lender agreements that restrict a dividend without refinancing (or some other type of compensation/adjustment to the debt terms, etc.). But in our hypothetical scenario, there is only one lender and the sponsors have received approval of this dividend.

Step 9. Capitalization Table

Before we can begin to calculate the returns on WSPCP's investment, we must first determine the initial ownership structure and how the percentages change on a diluted basis.

In this section, we're working toward the following:

Equity Ownership Structure

Pre-Dilution Equity: Because there was no rollover equity and the existing management was replaced, the sponsor ownership is 100%. This includes both the equity contributions of the Lead Sponsor and WSPCP.

Since there's a 5% option pool, divide the pre-dilution ownership by 105% to arrive at a post-dilution ownership of 95.2% for the sponsors.

Option Pool Side Note

There are two approaches to calculating the value to management from options.

Approach #1: Percentage of Full Exit Value

Most LBO Model Tests will assume the option pool is fully executed, so to determine how much value goes to management you would just multiply the entire exit equity value by the post-dilution management ownership for each year in the returns calculation.

Let's say there is only one sponsor and the post-dilution sponsor ownership percentage is 90.9% (i.e. the management rollover and options pool equates to 10%). The exit equity value attributable to the sponsor would be calculated by multiplying the total exit equity value by 90.9%, and the amount that goes toward the management team is calculating by multiplying the total exit equity value by 9.1%.  This is the simplest method, and also the common type seen in Basic and Standard LBO Model Tests.

Approach #2: Percentage of Exit Value in Excess of Original Investment

Alternatively, the management pool may just be based on the excess equity value creation over the initial equity investment (from the Sources & Uses schedule), thus you would multiply the excess by the agreed upon percentage (e.g. 10%) each exit year. So you first deduct the initial sponsor investment amount from the exit equity value (i.e. the excess) and then multiply this amount by 10%. The fixed 10% payment for the excess is used and not adjusted for dilution (although the sponsor ownership dilution has changed).

Other Approaches

While the two approaches described above are the most common, there are other variations.  For example, management equity can be structured such that management is to receive 10% of equity exceeding 2.0x sponsor equity. Then, the amount to management would be calculated as 10% of the exit equity value less the 2.0x initial sponsor investment.

Option pools for management teams are highly negotiated forms of compensation and there are many variations out there, so you need to identify and be sure you understand how it was structured to correctly model the returns. As a broad generalization, option pools based on the excess of the initial investment (e.g. 1.0x, 2.0x) tend to be for LBOs where existing management stayed on and rolled over some equity, while structures based on actual ownership tend to be more common when a new team is brought on since they quite literally own zero equity on paper.

For our JoeCo example, a strike price was actually provided so we cannot just assume execution of the options without actual value creation (i.e. > $1.00 implied share price). And since there was no mention of the proceed amount being based on the excess equity value over the sponsors' initial investment, we can assume this will be based on the full exit equity value. This is why we calculated the post-dilution percentage of 4.8% as this is a new reservation of ownership, and thus the +5% to the 100%. The intuition being: as long as there is value creation, the management will own 4.8% of the entire exit equity value of JoeCo.

Investment Assumptions

How many shares does the Lead Sponsor own then? We divide the initial investment of $2,702mm by the "Post-Closing Share Price" of $1.00 (per the Prompt).

Post-Closing Share Price: The $1.00 "Newco" share price is arbitrary and at the discretion of the new ownership (e.g. Lead Sponsor, WSPCP). What actually matters is the equity value of JoeCo amount and the ownership structure percentages, which will remain completely unchanged.

Armed with the closing share price, we can outlines the initial number of common shares outstanding:

There was no rollover equity and 100% of WSPCP's co-investment was in the form of preferred stock, NOT common stock so the Lead Sponsor owns 100% of the common shares outstanding - at least initially.

Getting back to the investment assumptions...

Preferred Convertible Shares: Represents the number of common shares WSPCP would own if it chooses to convert to common rather than receiving the accrued value of the preferred stock.

Just how many common shares can WSPCP own? The amount will be calculated by dividing the "WSPCP Initial Investment" by the "Convertible Equity Strike Price" of $1.25, which yields 380mm and the formula is shown below.

WSPCP Preferred Investment % of Initial Equity: Calculated by dividing the WSPCP initial equity investment by the total equity amount. The percentage of total equity (common + preferred) at purchase owned by WSPCP is 15%, while the other 85% was contributed by the Lead Sponsor. This will come into play when calculating the amount of the recap dividend that will be distributed to WSPCP, as this is the assumption provided by the prompt (i.e. WSPCP will receive 15% of the recap dividend).

Management Assumptions: First, the "Management Pool Amount" will be calculated by multiplying the management's 4.8% diluted ownership management by the entry valuation.

Then similar to how we did for WSPCP, we will determine how many exercisable shares are owned by management by dividing the "Management Pool Amount" by the "Management Strike Price" of $1.00.

Assuming management's options are in-the-money at exit, they would own 227mm shares.

Mini-Capitalization Table

We can now bring this all together to create a mini-capitalization table (and to eventually calculate the "Implied Share Price" at the bottom).

"Lead Sponsor Common Shares" - is simply what we calculated at the beginning of this step and will just be straight-lined since they are ordinary, basic shares.

Moving on, we will calculate the next two lines, the "WSPCP Converted Preferred Shares" and the "Management Exercised Shares", which we already have all the necessary inputs for the calculations used in the table.

You should recognize where the 380mm and 227mm are coming from. They represent the number of common shares created if WSPCP converts its preferred equity and if management's options are ITM.

The formula for WSPCP's converted share is shown below:

Based on our prior explanations of the convertible feature of WSPCP's preferred equity, you should be able to infer what the "IF" function is referring to. The formula is saying that if the value of the convertible shares is greater than that of the accrued value, then the option to convert will be selected and 380mm shares will be issued to WSPCP. The two values mentioned that will be compared have not yet been calculated and will be determined in the final step, but we did want to define them.

And then the formula for management options is provided below:

This formula is essentially the same as above. It is stating that if the "Implied Share Price" is greater than the "Management Strike Price" of $1.00, then the 277mm in "Management Exercisable Shares" will be ITM and thus issued.

But do keep in mind, the table above is calculating the "Total Common Shares Outstanding" as of exit dates. These converted and exercised shares do not carry over onto the next year – we wanted to make this point clear in case there might be some confusion.

Step 10. Returns Calculation

In the final step, we will calculate and assess the returns on WSPCP's preferred equity investment into JoeCo.

Exit Valuation

Step 1: Determine Equity Value

  • Apply an Exit EBITDA Multiple Assumption: We will bring down each Exit LTM EBITDA down from the pro forma financial forecast and use an "Exit Multiple Assumption" of 14.5x, the conservative assumption provided by the prompt.

Step 2: Treatment of Preferred Equity

With the equity value established, we now need to establish whether, based on our model, WSPC will prefer to convert their preferred equity to common or whether they would prefer to simply keep their investment in the form of preferred stock.

Since the decision to convert is entirely the choice of WSPC, they will simply pick the choice that generates the higher return for them:

To determine this, we will first calculate the value of the preferred equity under the PIK interest of 8.5% and then the returns if WSPC does indeed convert:

  • Returns if WSPC keeps their preferred stock: The initial equity investment of $475mm by WSPCP will accrue each year at the 8.5% PIK rate and reaches $714mm by 2025.
  • Returns if WSPC converts: The convertible value of WSPCP's preferred equity investment requires us to multiply the "Preferred Convertible Shares" of 380mm by the "Implied Share Price" of the relevant year. 

Because of WSPC's two options, calculating the "Implied Share Price" can be little tricky:

  1. If WSPC keeps their preferred stock, the Implied Share Price = (Equity Value – WSPC Preferred Stock) / Common Shares
  2. If WSPC converts, the Implied Share Price = Equity Value / (Common Shares including the dilution from the newly converted shares to WSPC)

And since the resulting share price determines the value of the if-converted preferred equity, we also have a circularity that requires we insert a breaker:

The formula above is saying, if the "Convertible Preferred Equity Value" is greater than the "Accrued Preferred Equity Value", then the "Implied Share Price" is calculated by dividing the "Exit Equity Value" by the "Total Common Shares Outstanding".

On the other hand, if the "Accrued Preferred Equity Value" exceeds the "Convertible Preferred Equity Value", we will first deduct the "Accrued Preferred Equity Value" from the "Exit Equity Value" and then divide this amount by the "Total Common Shares Outstanding".

We can see that based on the assumptions in the model, conversion is the optimal choice for WSPC after 2021 under our current assumptions. To model this, we used a "MAX" function between the two options, which will output the one of greater size between the two.

Step 3: Add Proceeds to WSPC from Monitoring Fees + Dividend Recap

For the dividend recap, we will first multiply the "Less: Recap Dividend Issuance" from the financial forecast by the pre-dilution ownership percentage of 15% we listed in the "Investment Assumptions" section. The reason we use the pre-dilution percentage is that this was before the exit and the management pool had no dilutive impact on the equity ownership structure. Also, the prompt stated to do this specifically.

So if a dividend recap is completed in 2024, the dividend amount to WSPCP is $213mm. But we need to ensure 2025 reflects this dividend receival too.

For the "Plus: Recap Dividend to WSPCP", the formula used is shown below for 2025. It is essentially saying that "IF" the current year is greater than or equal to the recap year, then the dividend amount will be added to the "Total Proceeds to WSPCP" line item.

For "WSPCP Monitoring Fee", it is faster to just do it manually and confirm you linked it properly. It is $5mm each year received for consulting services to JoeCo. The monitoring fee is paid out annually and should thus be linked to each individual year prior to the exit. But be sure not to double-count it, and sanity check you did properly by confirming for Exit Year 5, $25mm in total was received in fees.

Step 4: Calculating IRR and MOIC w/ Sensitivity Analysis

We can now calculate the IRR using the XIRR function and MOIC by dividing the proceeds by the initial investment.

In the final step before we answer the questions, we will perform sensitivity analysis on the IRR and MOIC. The process is the same as usual, the only difference is the variable on the left will be the offer value per share rather than the entry multiple.

Conclusion

This brings our Advanced LBO Model tutorial to an end – we hope you found the instructions to be intuitive and helpful for your private equity interview preparation.

Below are a few key questions that could have potentially shown up in the prompt:

  • Q. Do you think the benefits of investing in the preferred equity of JoeCo were worth it, as opposed to just investing in the vanilla common equity?
  • Q. Why do you think WSPCP decided to invest in the preferred equity of JoeCo? Phrased another way, do you think there were sufficient risks for this required downside protection?
  • Q. What is the relationship between the timing of the dividend recap and the add-on acquisition, and if the decision was mutually exclusive – which one would you recommend to the firm?
  • Q. WSPCP contributed only $475mm into this take-private of JoeCo, if the decision was up to you – would you have invested more capital? (Hint: Recap Dividend)
  • Q. If you wanted to understand whether this was actually a "good" investment for WSPCP, what three questions would you ask the investment committee of WSPCP?

We will intentionally not provide direct answers for these questions, albeit we have already discussed many of them in detail in previous sections. But just keep these Qs in the back of your mind upon completion of the model and as you adjust the assumptions to see the return implications.

As you can deduce from all the commentary throughout this article, while there are many wrong ways to build a model like this, there isn't only one correct answer, and if you were to look at the models submitted by ten different candidates, they would all vary and even the IRR / MOICs would marginally deviate from one another.

The model does not have to be (and is not expected to be perfect), but your attention-to-detail, ability to see the big picture and decision-making under pressure will be reflected in the quality of your work.


Go Beyond the Interview: Advanced LBO Modeling Step By Step Video Training

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. We are proud that this program is now used at four of the top five largest private equity firms to train their own newly minted PE Associates.

To learn more about the course, click here.

Upon purchase, you will gain immediate access to the most comprehensive course on LBO Modeling available.

Comments
guest
0 Comments
Inline Feedbacks
View all comments
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.