Deal & Banking Models

Six recipes: three-statement, LBO, debt schedule, accretion/dilution, sources & uses, credit stats & covenants. Shared mechanics (cell colours, named ranges, circularity, recalc-and-verify) are in references/build-engine.md. Circularity matters in several of these — read that section first.

---

Three-Statement Model (three-statement-model)

Integrated IS + BS + CF linked by live formulas so a change in one assumption ripples through all three and the balance sheet balances.

When: "three-statement", "integrated model", "operating model", "the balance sheet has to balance".

Tabs: Cover · Assumptions · IS · BS · CF (indirect) · Debt & Interest · Checks. Years across columns (D = historical, E onward = projection).

Assumptions: revenue growth %, gross margin %, opex %rev, tax rate, D&A logic, capex %rev, DSO/DIO/DPO days, dividend payout %, interest rate %, opening debt, scenario toggle.

IS: Revenue =D_Revenue*(1+E_GrowthPct); COGS =-E_Revenue*(1-GrossMargin); Gross profit =Revenue+COGS; Opex =-Revenue*OpexPct; EBITDA =GrossProfit+Opex; D&A =-DA; EBIT =EBITDA+DA; Interest ='Debt & Interest'!E_Interest; Pretax =EBIT+Interest; Taxes =-MAX(0,Pretax)*TaxRate; Net income =Pretax+Taxes.

Debt & Interest: Beginning =D_EndingDebt (first year =OpeningDebt); Draw/(repay) input or sweep link; Ending =Beginning+Draw; Interest =IntRate*IF(Circuit_Breaker=1, Beginning, AVERAGE(Beginning,Ending)).

CF (indirect): Net income =IS!E_NetIncome; +D&A =-IS!E_DA; ΔNWC =-(E_NWC-D_NWC) (NWC = receivables + inventory − payables from days); Capex =-Capex; Financing = net debt draw − dividends (=-NetIncome*Payout); Ending cash =BeginningCash+NetChange.

BS: Cash =CF!E_EndingCash; AR/inv/AP from days; PP&E =D_PPE+Capex-(-IS!E_DA); Debt ='Debt & Interest'!E_EndingDebt; Retained earnings =D_RE+NetIncome-Dividends; Balance =TotalAssets-TotalLiabEquity.

Checks (master tie-out): BS!TotalAssets-BS!TotalLiabEquity = 0 every period; BS cash = CF ending cash; ΔRE = NI − dividends; BS debt = Debt tab ending debt. Master =IF(SUMPRODUCT(...)=0,"PASS","FAIL").

Inputs: historical revenue + starting BS, horizon, growth/margin, tax, capex & D&A logic, working-capital days, dividend policy, opening debt + rate, beginning-vs-average interest preference.

Example: revenue 100 growing 8%, gross margin 60%, opex 35%, tax 25%, capex 5%, D&A 4, opening debt 50 @ 6% → y1 revenue 108, EBITDA 27, interest 3, NI ≈ 15; balance reads 0.0. Illustrative.

---

LBO Model (lbo-model)

Size entry debt/equity, project FCF, pay down debt with a cash sweep, solve sponsor IRR & MOIC at exit, with sensitivities.

When: "LBO", "leveraged buyout", "sponsor returns model".

Tabs: Cover (scenario + circuit-breaker toggles) · Assumptions · Sources & Uses · Operating Model · Debt Schedule · Returns · Sensitivity · Checks. Year 0 = entry.

Sources & Uses: entry EV =EntryEBITDA*EntryMultiple; uses = EV + fees (=EntryEV*FeePct) + refinanced debt + min cash; sources = debt tranches (=EntryEBITDA*Tranche_Turns) + sponsor equity plug =TotalUses-SumDebt-Rollover.

Operating Model: EBITDA =Revenue*EBITDAMargin; cash taxes =-MAX(0,(EBITDA-DA-Interest))*TaxRate; capex =-Revenue*CapexPct; ΔNWC from days; FCF before paydown =EBITDA+CashTaxes+Capex-NWCChange-MandatoryAmort (cash interest linked from Debt Schedule); cash for sweep =FCF-MinCashTopUp.

Debt Schedule (per tranche, senior→junior): Beginning =PriorEnding; mandatory amort =-MIN(Beginning, AmortPct*OriginalFace); sweep =-MIN(Beginning+MandAmort, MAX(0,CashForSweep))*SweepPct; Ending =Beginning+MandAmort+Sweep; Interest =Rate*IF(Circuit_Breaker=1, Beginning, AVERAGE(Beginning,Ending)). Revolver absorbs any shortfall to hold min cash; sweep cascades down by seniority.

Returns: exit EV =ExitEBITDA*ExitMultiple; net debt at exit =SumEndingDebt-EndingCash; exit equity =ExitEV-NetDebt; sponsor flows y0 =-SponsorEquity, exit =ExitEquity; MOIC =ExitEquity/SponsorEquity; IRR =IRR(row) or =XIRR(values,dates).

Sensitivity: two Data Tables — entry × exit multiple → IRR; leverage → IRR.

Checks: Sources = Uses (= 0); every tranche ending ≥ 0; min cash respected; revolver within commitment; master PASS/FAIL.

Circularity: document beginning-balance (toggle 1, no circularity) vs average-balance (toggle 0, iterative) on the Cover; set the breaker before the first calc.

Inputs: entry EBITDA & multiple, fees, opening leverage by tranche + rates + amort, sweep %, min cash, revenue/margin drivers, tax, capex, NWC days, hold period, exit multiple.

Example: EBITDA 50 @ 10.0x = EV 500; fees 15; debt 300; sponsor equity 215; EBITDA → 70 by y5; sweep repays TLA; exit 10.0x = 700 less net debt 120 = equity 580 → MOIC ≈ 2.7x, IRR ≈ 22%. Illustrative.

---

Debt Schedule (debt-schedule)

Standalone multi-tranche schedule: beginning, mandatory amort, optional sweep prepayment, ending, interest, with FCF waterfalling down by seniority.

When: "debt schedule", "debt paydown", "cash sweep", "tranches".

Tabs: Cover (circuit-breaker) · Assumptions · Debt Schedule · Checks. Periods across columns.

Cash-available block: FCF before debt =FCFBeforeDebt; total mandatory amort =SUM(tranche mand amort); cash for sweep =MAX(0, FCFBeforeDebt - TotalMandAmort - MinCashTopUp).

Per-tranche rollforward (top→bottom of waterfall): Beginning =PriorEnding (first =Tranche_Opening); mandatory =-MIN(Beginning, AmortPct*OriginalFace); sweep =-MIN(Beginning+Mand, CashForSweep_thisTranche)*SweepPct; cash for next (junior) tranche =PriorCashForSweep - ABS(PriorSweep); Ending =Beginning+Mand+Sweep; Interest =Rate*IF(Circuit_Breaker=1,Beginning,AVERAGE(Beginning,Ending)).

Revolver: draw =MAX(0, MinCash - CashAfterTermPaydown); ending =MIN(Commitment, Beginning+Draw-Repay); total interest =SUM(tranche interest) + undrawn fee =UndrawnFee*(Commitment-RevolverEnding).

Checks: every ending ≥ 0; revolver ending ≤ commitment; total interest = Σ tranche interest (independent re-add); sweep never exceeds cash available; master PASS/FAIL.

Circularity: sweep↔interest loop — circuit-breaker = 1 for a clean first calc, or iterative if averaging. Document on Cover.

Inputs: per tranche opening balance, rate, amort, seniority; revolver commitment + undrawn fee; sweep %; min cash; FCF before debt per period.

Example: TLA 100 @ 5% amort 5%/yr; TLB 150 @ 6%, no mand, 50% sweep; revolver 50 @ 4.5%. FCF 40 in y1 → 5 mandatory, 17.5 sweep TLA→TLB; total interest ≈ 13.5; all endings positive. Illustrative.

---

Accretion / Dilution (accretion-dilution)

Does the merger raise or lower acquirer EPS, given a cash/stock/debt mix and synergies.

When: "accretion dilution", "merger EPS impact", "pro forma EPS", "breakeven synergies".

Tabs: Cover · Assumptions (both companies + deal) · Deal Structure · Combined EPS · Sensitivity (premium × cash mix) · Checks. Single-period unless a multi-year bridge is asked.

Deal Structure: offer price =TargetPrice*(1+PremiumPct); offer equity =OfferPrice*TargetShares; cash/stock/debt =OfferEquity*MixPct (the three % sum to 100%); new acquirer shares =StockConsideration/AcquirerPrice.

Combined EPS: pro forma NI =AcquirerNI + TargetNI + AfterTaxSynergies - AfterTaxNewInterest - AfterTaxForegoneInterest where after-tax synergies =Synergies*(1-Tax), new interest =NewDebt*NewRate*(1-Tax), foregone interest =CashUsed*CashYield*(1-Tax); pro forma shares =AcquirerShares+NewShares; pro forma EPS =ProFormaNI/ProFormaShares; standalone EPS =AcquirerNI/AcquirerShares; accretion =ProFormaEPS/StandaloneEPS-1; label =IF(Accretion>=0,"Accretion","Dilution"). Breakeven synergies = the synergy level that sets accretion to zero, grossed up by (1-Tax).

Checks: mix sums to 100%; pro forma shares & NI re-add as ties; accretion sign labelled; master PASS/FAIL. Guard divisors if shares/price could be zero. No structural circularity.

Inputs: acquirer & target NI, shares, price; premium; cash/stock/debt mix; new debt rate; cash yield; tax; pre-tax synergies.

Example: acquirer NI 200 / 100 sh (EPS 2.00); target NI 60, price 20, 40 sh; premium 25% → offer 25.00, equity 1000; 50/50 cash/stock; new shares 10; tax 25%; synergies 20 → pro forma EPS ≈ 2.10, ≈ 5% accretion. Illustrative.

---

Sources & Uses (sources-and-uses)

Transaction funding table with an equity plug that balances it, plus a pro forma cap table with leverage by tranche.

When: "sources and uses", "funding table", "pro forma capitalization".

Tabs: Cover · Assumptions · Sources & Uses · Pro Forma Cap Table · Checks. Single-column snapshot.

Uses: purchase equity / entry EV; refinance existing debt; transaction fees =EntryEV*TxnFeePct; financing fees =SUM(NewDebt)*FinFeePct; min cash; Total =SUM.

Sources: each tranche =EBITDA*Tranche_Turns; rollover =PurchaseEquity*RolloverPct; cash on hand; sponsor/new equity plug =TotalUses - SUM(NewDebt) - Rollover - CashOnHand (must be a live formula); Total =SUM.

Cap Table: per tranche amount, xEBITDA =Tranche/EBITDA, cumulative leverage =CumDebt/EBITDA; net debt =TotalDebt-MinCash, net leverage =NetDebt/EBITDA; equity & total cap; equity % of cap.

Checks: Sources − Uses = 0 (confirms the plug formula); cumulative leverage ≤ max; each tranche ≥ 0; equity plug ≥ 0 (negative = over-funded, flag). Avoid circularity by sizing financing fees off debt tranches only, not off total sources including the plug.

Inputs: purchase equity / entry EV, existing debt, txn & financing fee rates, min cash, LTM EBITDA, leverage turns + rates by tranche, rollover %, cash on hand.

Example: entry EV 600, existing debt 100, fees 12 + 2.5%, min cash 10; EBITDA 60, TL 4.0x (240), bonds 1.5x (90), rollover 30, cash 20 → Total Uses ≈ 730, plug ≈ 350, Sources = Uses, net leverage ≈ 5.3x. Illustrative.

---

Credit Stats & Covenants (credit-stats-covenant)

Leverage/coverage/liquidity each period vs covenants, with headroom and PASS/FAIL.

When: "credit stats", "covenant headroom", "covenant compliance", "leverage and coverage".

Tabs: Cover · Assumptions · Credit Stats · Covenants · Checks. Periods across columns. Link input rows to a live operating/debt model if one exists, else drive from Assumptions.

Credit Stats: total leverage =TotalDebt/EBITDA; net =(TotalDebt-Cash)/EBITDA; senior =SeniorDebt/EBITDA; interest coverage =EBITDA/CashInterest; EBITDA-capex coverage =(EBITDA-Capex)/CashInterest; FCCR =(EBITDA-Capex)/(CashInterest+MandatoryAmort); liquidity =(RevolverCommitment-Drawn)+Cash. Use one EBITDA and one interest definition consistently across every ratio.

Covenants (per test): actual, covenant (input), headroom (=Covenant-Actual for max-leverage; =Actual-Covenant for min-coverage), headroom % (=Covenant/Actual-1 leverage; =Actual/Covenant-1 coverage), flag =IF(test passes,"PASS","FAIL"); period flag =IF(COUNTIF(period flags,"FAIL")=0,"PASS","FAIL"). Summary: tightest headroom via MIN, first breach via MATCH.

Checks: consistent ratio definitions (recompute one as a tie); guard zero denominators (=IF(denom=0,"n/m",ratio)); any breach flagged + first-breach period named; net leverage ≤ total each period. If linked to a model with its own interest loop, recalc that model first (or use its beginning-balance toggle).

Inputs: per-period EBITDA, capex, cash interest, mandatory amort, total & senior debt, cash, revolver commitment & drawn, covenant levels + first test date.

Example: y1 EBITDA 60, capex 8, interest 14, total debt 330, senior 240, cash 15, revolver 50 undrawn → total leverage 5.5x, net 5.3x, coverage 4.3x, FCCR ≈ 2.8x, liquidity 65; max-leverage covenant 6.5x → 1.0x headroom PASS. Illustrative.