Building a Leveraged Buyout Model from Scratch

# Building Your First LBO Model: A Step-by-Step Walkthrough

Deal snapshot: $100M revenue × 25% margin = **$25M EBITDA**; 10x entry = **$250M enterprise value**; 60% debt ($150M) / 40% equity ($100M).

---

## 1. Transaction Assumptions and Sources & Uses

**Purpose:** Establish purchase price, funding mix, and fees that drive everything downstream.

- Build an **Assumptions tab** with hardcoded inputs (blue font): revenue, EBITDA margin, entry multiple, debt tranches, interest rates, fees.
- **Uses of Funds:** Purchase equity value + refinanced debt + transaction fees (~2% of EV) + financing fees (~2.5% of debt).
- **Sources of Funds:** TLB (e.g., 4.0x EBITDA = $100M at SOFR+400), Sub Notes (2.0x = $50M at 9%), Sponsor Equity (plug).
- Formula example: `=Total_Uses - TLB - Sub_Notes - Excess_Cash` for sponsor equity plug.
- **Pitfall:** Forgetting minimum cash on balance sheet (~$5M) or double-counting existing debt assumed vs. refinanced.

## 2. Pro Forma Balance Sheet Adjustments

**Purpose:** Flip the target's opening BS to reflect new ownership, debt, and goodwill.

- Start with target's standalone BS, then add adjustment columns (+/−) to arrive at pro forma.
- Key adjustments: (a) write off existing equity, (b) add new debt, (c) capitalize financing fees as an asset, (d) expense transaction fees against equity, (e) plug **Goodwill = Equity Purchase Price − Net Book Value of Target's Equity + Write-ups**.
- Formula: `Goodwill = Purchase_Equity - Target_BV_Equity`
- **Pitfall:** Balance sheet must balance post-adjustments. If Assets ≠ Liab + Equity by even $0.1M, trace each adjustment before moving on.

## 3. Operating Model / Income Statement Projections

**Purpose:** Project 5 years of P&L to generate EBITDA and free cash flow available for debt paydown.

- Project revenue with growth assumption (SaaS: 10–15% typical): `=Revenue_PY*(1+Growth_Rate)`
- EBITDA margin expansion (25% → 28% over hold is reasonable).
- D&A as % of revenue; build EBIT = EBITDA − D&A.
- Taxes on EBT at ~25%; flow to Net Income.
- **Pitfall:** Don't forget **stock-based comp** add-back treatment (most PE funds treat as a real expense — be consistent). Also model D&A separately from capex.

## 4. Debt Schedule with Mandatory and Optional Repayments

**Purpose:** Track each tranche's balance, interest, and repayments year by year.

- Build a waterfall per tranche with: Beginning Balance → Mandatory Amort → Optional Prepayment (sweep) → Ending Balance.
- **TLB:** 1% mandatory amortization: `=-TLB_Initial*1%`; 50–100% cash sweep on excess cash.
- **Sub Notes:** typically bullet (no amort), no sweep, higher rate.
- Interest expense on **average balance**: `=AVERAGE(Begin_Bal, End_Bal)*Rate`
- Revolver ($20M undrawn): sized for liquidity, drawn only if cash < minimum.
- **Pitfall:** Forgetting to cap optional prepayment at tranche balance remaining — use `=MIN(Available_Cash, Remaining_Balance)`.

## 5. Cash Flow Statement and Cash Sweep

**Purpose:** Calculate cash available for debt paydown after operating and investing needs.

- **CFO:** Net Income + D&A + Amortization of Financing Fees ± ΔWorking Capital.
- **CFI:** −Capex (typically 3–5% of revenue for SaaS).
- **CFF:** −Mandatory Amort − Optional Sweep ± Revolver.
- Cash Available for Sweep: `=Beginning_Cash + CFO + CFI − Mandatory_Amort − Min_Cash`
- Optional Sweep: `=MIN(Cash_Available, TLB_Balance_After_Mandatory)`
- **Pitfall:** Working capital sign errors — an *increase* in AR is a cash *use* (negative).

## 6. Interest Expense Circular Reference Handling

**Purpose:** Interest depends on debt balance, which depends on cash flow, which depends on interest — classic circularity.

- **Option A (preferred):** Use average balance for interest and enable iterative calcs: File → Options → Formulas → Enable iterative calculation (100 iterations, 0.001 tolerance).
- **Option B (safer for shared models):** Use **beginning balance only** for interest to avoid circularity entirely: `=Begin_Balance*Rate`
- Build a **circuit breaker**: a cell that when flipped to 1 zeros out interest, letting you reset if #REF! cascades: `=IF(Breaker=1, 0, Avg_Balance*Rate)`
- **Pitfall:** Never leave iterative calcs on without a breaker — one bad formula and the whole model returns garbage.

## 7. Exit Assumptions, Returns, and Sensitivities

**Purpose:** Calculate sponsor returns and stress-test the thesis.

- **Exit EV** = Year 5 EBITDA × Exit Multiple (often = entry multiple for base case).
- **Exit Equity** = Exit EV − Net Debt at exit.
- **MOIC** = `=Exit_Equity / Initial_Sponsor_Equity` (target 2.5x+).
- **IRR

# LBO Model Build: SaaS Company Acquisition – Complete Step-by-Step Guide

## 1. **Transaction Assumptions & Sources & Uses**

**Purpose:** Establish entry valuation, financing structure, and uses of proceeds.

**Key Line Items:**
- Enterprise Value = $100M revenue × 10x = **$1,000M**
- **Uses of Funds:**
  - Equity Purchase Price: $1,000M
  - Transaction Fees (legal, advisory): ~$15M
  - Total Uses: $1,015M

- **Sources of Funds:**
  - Sponsor Equity (40%): $406M
  - Term Loan B (TLB, senior debt): $450M
  - Subordinated Notes (Sub Notes, junior debt): $159M
  - Total Sources: $1,015M

**Excel Setup:**
- Create a dedicated "Sources & Uses" table at the top of your model
- Formula: `=Total_Uses - Total_Equity` to solve for required debt
- **Pitfall:** Don't forget transaction fees; they're part of sources/uses but also reduce opening cash

---

## 2. **Pro Forma Balance Sheet Adjustments**

**Purpose:** Establish the Day 1 balance sheet immediately post-closing, incorporating debt and equity adjustments.

**Key Adjustments:**
- **Debt:**
  - TLB: $450M (assume OID cost of ~2%, so $441M net)
  - Sub Notes: $159M (assume OID ~3%, so $154.2M net)
  - *OID (Original Issue Discount) is recorded as debt discount, amortized to interest expense*

- **Equity:**
  - Goodwill & Intangibles: plugged to balance (typically $500M+)
  - Opening Equity: $406M sponsor investment

- **Cash:**
  - Post-transaction: typically minimal ($10–20M operational cash)

**Excel Formula Example:**
```
Beginning Equity (Yr 0) = Sponsor Equity - Transaction Fees
Net Debt (Yr 0) = TLB + Sub Notes - OID Discount - Cash
```

**Pitfall:** Forgetting to deduct OID from gross debt; this creates a permanent error in interest expense.

---

## 3. **Operating Model & Income Statement Projections**

**Purpose:** Project 5-year revenue, EBITDA, and taxable income under realistic growth assumptions.

**Key Assumptions:**
- Revenue CAGR: 12–15% (typical SaaS growth)
- EBITDA Margin: maintain 25%, or model gradual improvement to 28%
- CapEx: ~5% of revenue (SaaS typically low)
- D&A: $50M annually (on intangible assets from acquisition)
- Tax Rate: 25–27%

**Excel Formulas:**
```
Yr 1 Revenue = Entry Year Revenue × (1 + Growth %)
EBITDA = Revenue × EBITDA Margin %
EBIT = EBITDA - D&A
Interest Expense = (Beginning Debt Balance × Senior Rate) + (Sub Notes × Sub Rate)
EBT = EBIT - Interest Expense
Net Income = EBT × (1 - Tax Rate)
```

**Pitfall:** Don't model interest expense statically; link it to declining debt balances (step 4).

---

## 4. **Debt Schedule with Mandatory & Optional Repayments**

**Purpose:** Model debt paydown over the hold period, accounting for financial covenants and cash sweep mechanics.

**Structure:**
- TLB: 5% annual amortization + optional prepay from excess cash flow
- Sub Notes: typically no amortization, repay at exit only
- Leverage Ratio Covenant (e.g., Debt / EBITDA < 5.0x): track quarterly

**Excel Build:**
```
Beginning Debt (Yr 1) = Ending Debt (Yr 0)
Mandatory Amortization = Beginning Debt × 5%
Optional Prepayment = IF(Excess_Cash_Available > 0, Excess_Cash_Available, 0)
Ending Debt = Beginning Debt - Mandatory Amortization - Optional Prepayment
```

**Key Points:**
- Sub Notes are "mezzanine" debt: higher rate (~8–10%), lower priority, no amortization
- TLB pricing: LIBOR + spread (e.g., 3.5% + 400bps)
- **Pitfall:** Don't forget TLB commitment fees (~0.75% annually on undrawn amounts, typically zero in LBO)

---

## 5. **Cash Flow Statement & Cash Sweep Mechanics**

**Purpose:** Track cash generation and deployment; determine available cash for debt paydown.

**Line Items:**
- Operating Cash Flow = Net Income + D&A + Interest Paid - Change in WC
- Less: CapEx (5% of revenue)
- Less: Mandatory Debt Amortization
- Less: Cash Taxes Paid
- **Equals: Excess Cash Flow** (available for optional prepay)

**Excel Formula:**
```
Excess Cash Flow = Operating CF - CapEx - Mandatory Amortization - Taxes
Cash Available for Sweep = MAX(Excess_Cash_Flow, 0)
Optional Prepayment = Cash Available for Sweep
Ending Cash = Beginning Cash + Operating CF - CapEx - Total Debt Paydown - Taxes
```

**Pitfall:** Confusing "cash taxes paid" (cash basis) with tax expense (accrual basis); model both separately.

---

## 6. **Interest Expense Circular Reference Handling**

**Purpose:** Resolve the circular loop: Interest Expense → Cash Flow → Debt Balance → Interest Expense.

**Solution:**
- **Iteration Method:** Enable iterative calculation in Excel (File → Options → Formulas → Iterations)
  - Set max iterations: 100; max change: 0.01
- **Alternative (More Transparent):** Hard-code interest rates and debt schedule separately, then solve interest expense after the cash flow waterfall closes

**Excel Setup:**
```
Year 1 Interest = (TLB Bal × TLB Rate) + (Sub Notes Bal × Sub Rate) + OID Amortization
(Build interest into the debt schedule tab, not the P&L, initially)
```

**Pitfall:** Circular references are easy to create unintentionally; test model stability by toggling iteration on/off.

---

## 7. **Exit Assumptions, Returns (IRR & MOIC), & Sensitivity Tables**

**Purpose:** Model sale at Year 5 and calculate sponsor returns.

**Exit Assumptions:**
- Exit Multiple: 11–12x EBITDA (assume 11x)
- Year 5 EBITDA: ~$188M (10x, 25% margin)
- Exit Enterprise Value: $188M × 11x = $2,068M

**Return Calculation:**
```
Enterprise Value (Exit) = Yr 5 EBITDA × Exit Multiple
Less: Remaining Debt (TLB + Sub Notes)
Equals: Equity Value
Less: Sponsor Equity Invested
Equals: Equity Gain
MOIC = (Equity Value + Dividends) / Sponsor Equity Invested
IRR = XIRR(Cash Flows)
```

**Sensitivity Tables:**
- Create 2D table: Exit EBITDA Multiple (rows: 9–13x) vs. Entry Leverage (columns: 4.5–6.0x Debt/EBITDA)
- Fill cells with MOIC or IRR
- Typical target: 20–25% IRR, 2.5–3.0x MOIC

**Pitfall:** Don't assume constant leverage at exit; model leverage decline from debt paydown.

---

## **Pre-Submission Sanity Check**

- ✅ Sources = Uses (within rounding)
- ✅ Day 1 Leverage = Total Debt / Entry Year EBITDA ≈ 5.0–5.5x (reasonable for SaaS)
- ✅ Interest expense declining year-over-year (debt paydown)
- ✅ No negative cash balances mid-hold
- ✅ Leverage covenant never breached (Debt/EBITDA trending down)
- ✅ Exit equity value > invested equity (positive returns)
- ✅ MOIC sensitivity reasonable (±10% revenue growth = ±0.2–0.3x

### 1. Transaction Assumptions and Sources & Uses
**Purpose:** Set deal pricing, financing mix, and cash flows at close to ensure sources = uses; forms foundation for pro forma BS.

- Input key assumptions in a dedicated tab: Entry EBITDA = $100M × 25% = $25M; Entry EV = $25M × 10x = $250M; Equity contrib. = 40% × $250M = $100M; Total debt = 60% = $150M (e.g., $100M TLB at L+300bps, $50M sub debt at 10%).
- Build Sources & Uses table: Sources = Debt ($150M) + Sponsor Equity ($100M) + Existing Cash ($10M assumed); Uses = Purchase Price ($250M) + Transaction Fees (2% EV = $5M) + Financing Fees (3% debt = $4.5M).
- Key formula: Ensure Sources = Uses (e.g., =SUM(Sources range) in check cell); plug with cash to balance if needed.
- Pitfall: Forgetting to capitalize fees (add to goodwill); use transaction-adjusted EBITDA for LTM.

### 2. Pro Forma Balance Sheet Adjustments
**Purpose:** Reflect post-LBO BS at close (Year 0), capturing debt issuance, equity injection, and purchase accounting.

- Start with historical BS (assume provided or simplified: Cash $10M, PP&E $20M, Goodwill $100M historical).
- Adjustments: +Debt $150M (liabs.); -Cash to Uses ($259.5M total uses - $10M existing = draw $249.5M, but net cash = $10M - $249.5M + sources plug); +Goodwill = Purchase EV $250M - Net Identifiable Assets (e.g., $150M book value × control premium adj.).
- Key formula: Pro Forma Cash = Hist. Cash + Sources Cash - Uses Cash (e.g., =B10 + SUM(B20:B25) - SUM(B30:B35)); Debt = Assumption inputs.
- Pitfall: Double-counting existing cash or missing revolver draw; always tie back to S&U for balance (=Assets - Liabs - Equity = 0 check).

### 3. Operating Model / Income Statement Projections
**Purpose:** Forecast 5-year P&L for SaaS metrics like revenue growth (15% YoY initial fade to 8%), EBITDA margin expansion (25% to 28%).

- Project in IS tab: Revenue = Prior × (1 + Growth%); EBITDA Margin input row; D&A 5% rev; Capex 10% rev; Taxes 25% (NOL shield initial).
- Key SaaS lines: ARR growth, Net Retention 110%; EBITDA = Rev × Margin; NOPAT = EBITDA - D&A - Taxes.
- Formula ex: Year 2 Rev = Year1_Rev * (1 + Growth2%) (e.g., =$C5*(1+$B15)); EBITDA = Rev * Margin% (e.g., =C5*$B20).
- Pitfall: Overly aggressive growth without churn/churn drivers; link margins to rev scale; use historical as Year 0 base, transaction adj. for fees.

### 4. Debt Schedule with Mandatory and Optional Repayments
**Purpose:** Track debt balances, interest, principal paydowns (mandatory amort. + sweeps) over hold period.

- Separate tab: Rows for TLB ($100M, 1% annual mand. amort., min cash $20M), Sub ($50M, PIK optional, 2% mand.); Columns for years 1-5.
- Interest: TLB = Beg. Bal. × (Base Rate 5% + Spread 3%); Sub = Beg. Bal. × 10%.
- Repayments: Mandatory = 1% Beg. Bal.; Optional from sweep; End Bal. = Beg. Bal. + PIK - Mand. - Sweep - Exit Payoff.
- Formula ex: TLB Int. Yr1 = C10 * (Base% + Spread%); Mand. = C10 * 1%; link Sweep from CFS later.
- Pitfall: PIK compounding error (add to principal); ensure min cash before optional paydown; revolver circular (draw if neg. FCF).

### 5. Cash Flow Statement and Cash Sweep
**Purpose:** Drive free cash flow (FCF) to debt paydowns via waterfall; links IS, BS, debt.

- CFS tab: NOPAT + D&A - Capex - ΔNWC (5% rev) = FCF; +EBITDA revolver if neg.
- Cash Sweep waterfall: Begin cash + FCF - Mand. Debt - Capex buffer ($10M) - Dividends (after Yr3) → Optional Debt → Build cash (min $20M) → Excess to equity.
- Formula ex: FCF = C_EBITDA - C_D&A - C_Capex - ΔNWC (ΔNWC = C_NWC - B_NWC, NWC=10% rev); Sweep Debt = MAX(0, C_AvailCash - MinCash).
- Pitfall: Circularity here—use manual iteration first; forget ΔNWC sign (outflow if growing); tie End Cash to BS.

### 6. Interest Expense Circular Reference Handling
**Purpose:** Resolve iteratives from interest → EBIT(1-t) → FCF → debt paydown → next interest.

- In IS: Total Int. Exp. = SUM(Debt Schedule Interest rows); links back to debt bals. from CFS sweeps.
- Excel fix: File > Options > Formulas > Enable Iterative Calc (Max 100 iterations, Change 0.001); or manual switch (Alt+T+O, F1 tab).
- Alt: Use debt paydown assumptions first, then iterate; add error check =IF(ABS(BS_Check)<0.01,"OK","Error").
- Pitfall: Infinite loops (tighten convergence); unmodeled revolver interest; test by zeroing sweeps to verify.

### 7. Exit Assumptions, Returns (IRR, MOIC), and Sensitivity Tables
**Purpose:** Calc. sponsor returns at Yr5 exit; sensitize key drivers.

- Exit: Yr5+1 EBITDA × Exit Multiple (9-12x) = Exit EV; -Net Debt +Cash = Equity Value; IRR = IRR(Equity Inflows: -$100M Yr0, +Exit Equity Yr5); MOIC = Exit Equity / Entry Equity.
- Formula ex: Exit EBITDA = F6_EBITDA * (1+Growth6%); Exit Equity = (Exit_EV - NetDebt6) * (1 - Exit Fees 1%); IRR = IRR(B1:F1) where series = -100,0,0,0,0,ExitEQ.
- Sensitivities: Data Table (Entry Mult. 8-12x rows, Exit Mult. 8-12x cols, output IRR/MOIC); add Ebix growth, margin.
- Pitfall: Exit Net Debt = Total Debt - Cash (not revolver); double-count exit fees; IRR assumes interim dividends as cash flows.

**Model Sanity Checks (Final Checklist):**
- Sources = Uses; BS balances every year (=Assets-Liabs-Equity=0).
- FCF links: Sum(FCF) = ΔDebt + ΔCash + Equity Returns.
- IRR >20% base case; no circ. errors (F9 refresh); trace precedents for breaks.
- Stress: 0% growth → still positive MOIC? Export PDF for review.

(Word count: 912)