blitz

LBO Model Assignment: Assessing Big5 Sporting Goods as a Buyout Target

For This or a Similar Paper Click Here To Order Now

LBO model assignment:
This individual assignment asks you to take a public firm’s financials and assess whether it makes a suitable LBO target. You will build an Excel file LBO model with a simple debt and equity structure. We have provided a blank version of one Download blank version of onethat has the required sections and the target historical financials (except for stock price). The goal of the exercise is to have you build all the connected parts of the model and determine whether the assumptions below, plus your own, result in a deal that exceeds our hurdle rate. You will evaluate Big5 Sporting Goods.
Questions to answer
What is your model’s expected IRR and MOIC? Does it exceed the 25% hurdle rate?
If it does, identify a change in the model’s financial forecasts that will generate a lower than 25% hurdle. Similarly, if it does not exceed the hurdle rate, adjust one of the income statement assumptions and discuss what is required to exceed 25%.
After working through the model, what are two diligence questions you would ask to the management team? The questions should be connected to parts of your model.
Deliverable
Excel file with calculations. Label the sections of your model clearly. See empty template for a reasonable starting point.
Add a sheet “Assumptions” that list what you view as key assumptions that you made in the model
Add a sheet “Answers” that address #1, #2, and #3 above
Model assumptions (some already coded into file)
The debt financing fee is 3.5% (no need to amortize, pay at close)
Control premium of the last 6 months of the traded share price of 20-25%
The revolver should not be needed and can be ignored (we will investigate those soon)
Debt to total deal value (%) in range with the last 2 years of PE deals (see slides)
A baseline model of forecasted financials that is grounded in the company’s history and incorporates some growth or new efficiencies
Select key drivers as a % of revenues.
Capex, depreciation, and NWC (as a function of current assets and liabilities) also work in this way.
No management rollover
Debt terms: See Excel template; Assume SOFR is fixed over the investment window
5-year holding period
Exit multiple that is equal to that implied by your control premium (make this clear in your Excel)
Big5 Sporting Goods specifics
Assume the deal year is the end of fiscal year 2022 (Jan 1) and you are using the Jan 2023 numbers.
See the CF statement for historical depreciation and amortization (D&A)
Assume that 50% of D&A is depreciation
Rough grading rubric:
Entry EV and equity value clear in the Excel
Defend the control premium
Sources and uses tables incorporate all the components and fees
Build out a simple income statement –> EBITDA and NI as outputs
Build out a simple schedule that tracks total debt balance over time
Calculate leveraged CF that provides the starting point for the debt repayment
Calculate the interest payments each year using the average balance of the debt; incorporate into the Income Statement (and thus Net Income)
The income statement and balance sheet drivers are clear and explained in your “Assumptions” sheet
Output a final debt level, cash balance, and equity
Calculate the IRR and MOIC of the deal

📝 Struggling with where to start this assignment? Follow this guide to tackle your assignment easily!

This assignment requires you to build an LBO (Leveraged Buyout) model in Excel for Big5 Sporting Goods and determine whether it meets the required 25% hurdle rate. You will analyze financials, create an assumption sheet, and provide answers to key questions.

📌 Step 1: Understand the LBO Model Structure

Your Excel model should have the following components:
✅ Assumptions Sheet – Lists key financial and operational assumptions.
✅ Sources and Uses Table – Shows how the acquisition is financed.
✅ Income Statement Projections – Forecasts revenue, EBITDA, and net income.
✅ Debt Schedule – Tracks total debt, interest payments, and repayments.
✅ Cash Flow Analysis – Calculates leveraged cash flow for debt repayment.
✅ Exit Valuation and Returns – Determines final debt level, cash balance, and IRR/MOIC.

📌 Step 2: Set Up the Assumptions Sheet

Your assumptions drive the entire model. Clearly define:

🔹 Transaction Assumptions:

Control Premium: 20-25% over the last 6 months of traded share price.
Debt Financing Fee: 3.5%, paid at close.
Debt Structure: Use industry benchmarks for Debt-to-Total Value.
No Management Rollover: Assume the entire company is purchased.
Exit Multiple: Equal to the multiple implied by your control premium.

🔹 Financial Drivers:

Revenue growth rate (based on historical trends).
EBITDA margin as a % of revenue.
Capex, depreciation, and NWC as % of relevant financials.

📌 Step 3: Build the Sources & Uses Table

This table details how the LBO is funded.

✅ Sources:

Equity contribution.
Debt financing (leveraged amount based on PE deal trends).

✅ Uses:

Purchase of equity (Enterprise Value).
Transaction fees, debt financing fees.

📌 Step 4: Construct the Income Statement Projections

Project revenue, costs, and net income for a 5-year holding period. Key steps:
🔹 Use historical growth rates for revenue.
🔹 Calculate EBITDA (Revenue – Operating Expenses).
🔹 Include interest expense (calculated from average debt balance).
🔹 Deduct depreciation & amortization (D&A) (assume 50% is depreciation).

📌 Step 5: Create a Debt Schedule

Your debt schedule should track:

Debt balance over time (beginning and ending).
Interest payments (average debt * interest rate).
Principal repayment (based on available cash flow).

📌 Step 6: Calculate IRR and MOIC

IRR (Internal Rate of Return) – Measures annualized returns on investment.
MOIC (Multiple on Invested Capital) – Shows how much the investment grows.

💡 Compare IRR to the 25% hurdle rate. If your model exceeds 25%, identify a financial assumption that lowers it. If it falls below 25%, adjust an income statement assumption (e.g., revenue growth, EBITDA margin) to improve returns.

📌 Step 7: Answer the Assignment Questions

In a separate Excel sheet (“Answers”), provide:

1️⃣ IRR and MOIC Analysis:

What is the IRR and MOIC in your model?
Does it exceed 25%?

2️⃣ Adjustments to Hurdle Rate:

Identify a forecast change that lowers IRR below 25% (if IRR > 25%).
Identify a financial assumption required to exceed 25% (if IRR < 25%).

3️⃣ Due Diligence Questions for Management:

Two critical questions related to your model.
Examples:
- Revenue Growth Assumptions: “What strategies are in place to sustain projected revenue growth?”
- Debt Strategy: “How does management plan to handle potential macroeconomic risks affecting debt repayment?”

📌 Step 8: Finalize Your Excel Model

✅ Clearly label all sections.
✅ Ensure formulas are linked correctly.
✅ Provide clean formatting for readability.
✅ Double-check calculations for accuracy.

🎯 Final Tip:

LBO modeling is about logical structuring and assumption testing. Stay clear, concise, and realistic in your approach. Follow this guide, and you’ll be well-prepared for your assignment! 💪📊