FIN 421 Saudi Electronic University Small Business Finance Case Study

https://www.wallstreetprep.com/knowledge/lbo-modeling-test-example-solutions/

LBO Model Assignment
Small Business Finance- FIN 421
Wight: 20 Marks
Due Date 15/06/2023
Case Study: ½ Million Coffee Shop
Assignment Instructions:
A private equity firm is considering the leveraged buyout of ½ Million, a privately-owned
coffee company. In the last twelve months (“LTM”), ½ Million generated $1bn in revenue and
$180 mm in EBITDA. If acquired, the PE firm believes ½ Million revenue can continue to
grow 20% YoY while its EBITDA margin remains constant.
To fund this transaction, the PE firm was able to obtain 4.0x EBITDA in Term Loan B (“TLB”)
financing – which will come with a seven-year maturity, 5% mandatory amortization, and
priced at LIBOR + 500 with a 2% floor. Packaged alongside the TLB is a $50mm revolving
credit facility (“revolver”) priced at LIBOR + 500 with an unused commitment fee of 0.25%.
For the last debt instrument used, the PE firm raised 2.0x in Senior Notes, which carry a sevenyear maturity and an 8.5% coupon rate. The financing fees were 2% for each tranche, while
the total transaction fees incurred were $15 mm.
On the ½ Million balance sheet, there is $350 mm of existing debt and $45 mm in cash, of
which $35 mm is considered excess cash. The business will be delivered to the buyer on a
“cash-free, debt-free basis”, which means the seller is responsible for extinguishing the debt
and keeping all the excess cash. The remaining $10 mm in cash will come over in the sale, as
this is cash that the parties determined is required to keep the business operating smoothly.
Assume for each year that ½ Million depreciation & amortization expense (“D&A”) will be
5% of revenue, capital expenditures (“Capex”) requirement will be 2% of revenue, the change
in net working capital (“NWC”) will be 1% of revenue, and the tax rate will be 35%.
If the PE firm were to purchase ½ Million at 10.0x LTM EV/EBITDA on 12/31/2023 and then
exit at the exact LTM multiple after a five-year time horizon, what would the implied IRR and
cash-on-cash return of the investment be?
Instructions on submission:
Please use the excel files attached to this assignment.
Resources:
Please follow the case study on the following date.
https://www.wallstreetprep.com/knowledge/lbo-modeling-test-example-solutions/
Good Luck,
Basic LBO Model
($ in millions)
Step 1. Model Assumptions
Entry Valuation
Transaction Assumptions
LTM EBITDA
Entry Multiple
Transaction Fees
Financing Fees
Financing Fees Amortization Period
Cash to B/S
Purchase Enterprise Value
Circularity Toggle
Debt Assumptions
x EBITDA $ Amount
Rate
Floor % Amort.
% Fee
Revolver
Term Loan B
Senior Notes
Total Debt
Step 2. Sources & Uses Table
Sources & Uses
Sources
x EBITDA $ Amount
Uses
Revolver
Term Loan B
Senior Notes
Sponsor Equity
Purchase Enterprise Value
Cash to B/S
Transaction Fees
Financing Fees
Total Sources
Total Uses
Step 3. Free Cash Flow Projection
FCF Projection
($ in millions)
Revenue
EBITDA
Less: D&A
EBIT
Less: Interest
Less: Amortization of Financing Fees
EBT
Less: Taxes
Net Income
Plus: D&A
Plus: Amortization of Financing Fees
2020A
2021E
2022E
2023E
2024E
Less: Capex
Less: Δ in NWC
Less: Mandatory Amortization
Free Cash Flow (Pre-Revolver)
Revolver Drawdown / (Paydown)
Free Cash Flow (Post-Revolver)
Beginning Cash Balance
Net Change in Cash Flow
Ending Cash Balance
Operating Assumptions
Revenue Growth %
EBITDA Margin %
D&A % of Revenue
Capex % of Revenue
Δ in NWC % of Revenue
Tax Rate %
Step 4. Debt Schedule
Debt Schedule
($ in millions)
2021E
2022E
2023E
2024E
LIBOR (%)
1.5%
1.7%
1.9%
2.1%
Revolver
Beginning Balance
Revolver Drawdown / (Paydown)
Ending Balance
Total Revolver Capacity
Beginning Available Capacity
Ending Available Capacity
Revolver Interest Rate
Revolver Interest Expense
Unused Revolver Commitment Fee
Unused Commitment Fee
Term Loan B
Beginning Balance
Less: Mandatory Amortization
Ending Balance
TLB Interest Rate
TLB Interest Expense
Senior Notes
Beginning Balance
Less: Mandatory Amortization
Ending Balance
Senior Notes Interest Rate
Senior Notes Interest Expense
Step 5. Returns Calculation
Exit Valuation
($ in millions)
2020A
2021E
2022E
Year 0
Year 1
12/31/20 12/31/21
Year 2
12/31/22
2023E
2024E
Exit LTM EBITDA
Exit Multiple Assumption
Exit Enterprise Value
Less: Debt
Plus: Cash
Exit Equity Value
Cash (Outflows) / Inflows
Exit Year 2021
Exit Year 2022
Exit Year 2023
Exit Year 2024
Exit Year 2025
IRR
MOIC
Year 3
Year 4
12/31/23 12/31/24
zation Period
1
$ Fee
$ Amount
2025E
2025E
2.3%
2025E
Year 5
12/31/25