82 schedules

The building blocks of every model

A model is a handful of schedules wired together. Learn the schedule and you can find it in every model that uses it; each page below explains one, lists the lines a good version carries, names the mistakes a reviewer looks for, and shows exactly where it sits in each downloadable model.

The statements

Revenue build

Where the top line comes from

In Integrated three-statement model, Discounted cash flow model, Leveraged buyout model

Operating costs and margins

Cost of sales, opex, EBITDA

In Integrated three-statement model

Tax

Tax on profit, and tax on operating profit

In Discounted cash flow model, Leveraged buyout model, Merger model: accretion and dilution

Equity roll-forward

Share capital and retained earnings

In Integrated three-statement model

Cash flow statement

Indirect method, closing on balance-sheet cash

In Integrated three-statement model, Discounted cash flow model, Leveraged buyout model

Balance sheet

Every line from a schedule, and the check

In Integrated three-statement model

Operating schedules

Working capital schedule

Receivables, inventory and payables in days

In Integrated three-statement model, Discounted cash flow model, Leveraged buyout model, Medtech installed-base and consumables model

Capex and depreciation (PP&E roll-forward)

Opening, plus capex, less depreciation, closing

In Integrated three-statement model, Discounted cash flow model, Leveraged buyout model, Medtech installed-base and consumables model

Financing schedules

Debt schedule

Tranches, interest, amortisation and the sweep

In Integrated three-statement model, Leveraged buyout model, Merger model: accretion and dilution, Credit model

Debt capacity

How much a borrower can borrow, and which test binds

In Credit model

Covenant compliance and headroom

Leverage, interest cover, fixed charge cover, EBITDA cushion

In Credit model

Maturity profile and refinancing

What falls due when, and whether it can be refinanced

In Credit model

Valuation and returns

Cost of capital (WACC)

CAPM, after-tax debt, target weights

In Discounted cash flow model

Discounting and terminal value

Discount factors, two terminal methods, reconciled

In Discounted cash flow model

Enterprise value to equity value

Debt off, cash on, per share

In Discounted cash flow model

Returns analysis

Exit value, MOIC, IRR and the bridge

In Leveraged buyout model

Sensitivity tables

Two assumptions at once, without a data table

In Discounted cash flow model, Leveraged buyout model, Merger model: accretion and dilution

Calendarisation

Every peer on the same calendar year

In Comparable companies model

LTM build

Last fiscal year, plus the stub, less the prior stub

In Comparable companies model

Trading multiples

Enterprise value, the five multiples, and the range

In Comparable companies model

Precedent transactions

What acquirers paid for control

In Comparable companies model

Football field

Each range applied to the target, to a share price

In Comparable companies model

Transaction schedules

Sources and uses

What the deal costs and who funds it

In Leveraged buyout model, Merger model: accretion and dilution, Purchase price allocation model

Purchase price and consideration

Offer, premium, mix, new shares, exchange ratio

In Merger model: accretion and dilution

Synergies

Run-rate, phase-in, after tax

In Merger model: accretion and dilution

Purchase accounting

The write-up that amortises

In Merger model: accretion and dilution, Purchase price allocation model

Pro forma EPS: accretion and dilution

Standalone to pro forma, per share

In Merger model: accretion and dilution

Contribution analysis

What each side brings against what it owns

In Merger model: accretion and dilution

Goodwill calculation

From the price to goodwill, through every write-up

In Purchase price allocation model

Deferred taxes in an acquisition

The DTL on write-ups, its unwind, and the step-up

In Purchase price allocation model

Closing balance sheet

Acquirer plus target plus adjustments, balanced

In Purchase price allocation model

Venture schedules

SAFE conversion

What a post-money SAFE turns into at the round

In Cap table and exit waterfall model

Priced round and option pool

Pre-money to price per share, with the pool shuffle

In Cap table and exit waterfall model

Exit waterfall

Preference or conversion, then what reaches the common

In Cap table and exit waterfall model

Project finance schedules

Construction funding

Draws, interest during construction, fees, equity

In Project finance model, Real estate development model, PPP availability payment model

CFADS

Cash flow available for debt service

In Project finance model, PPP availability payment model

Debt sculpting

Service shaped to CFADS at a target DSCR

In Project finance model

Coverage ratios

DSCR, LLCR and PLCR

In Project finance model, PPP availability payment model

Reserve accounts

Debt service reserve and maintenance reserve

In Project finance model, PPP availability payment model

Distribution waterfall

Debt service, reserves, lock-up, equity

In Project finance model

Project and equity returns

Gearing, project IRR, equity IRR and multiple

In Project finance model, PPP availability payment model

Unitary charge and deductions

The availability payment, its indexation and the deductions

In PPP availability payment model

Annuity repayment

Level debt service: interest falls, principal rises

In PPP availability payment model

Pricing a PPP bid

The unitary charge that gives the target equity IRR

In PPP availability payment model

Value for money

PPP payments against the public sector comparator

In PPP availability payment model

Real estate schedules

Rent roll

Every lease, year by year, with expiries priced in

In Property acquisition model

NOI build

Gross rent to net operating income

In Property acquisition model

Loan sizing: LTV, DSCR and debt yield

The lender lends to the tightest of three constraints

In Property acquisition model, Real estate development model

Mortgage debt schedule

Constant payment, interest, principal, balloon

In Property acquisition model

Property returns

Unlevered and levered IRR, multiple, cash-on-cash, exit cap sensitivity

In Property acquisition model

Equity waterfall: preferred return, catch-up, promote

How the levered cash is split between investors and sponsor

In Property acquisition model

Development budget and draw profile

Land, hard and soft costs, contingency, fee, S-curve

In Real estate development model

Lease-up and stabilisation

Absorption, occupancy, NOI, TI and commissions

In Real estate development model

Development returns and spread

Yield on cost, development spread, profit on cost, IRR

In Real estate development model

Same-store NOI and portfolio growth

Growth on buildings owned, plus what was bought and sold

In REIT model: FFO, AFFO and NAV

FFO and AFFO

From net income to the measures REITs are priced on

In REIT model: FFO, AFFO and NAV

REIT capital allocation

Funding deals, the dividend, leverage and the investment spread

In REIT model: FFO, AFFO and NAV

REIT net asset value

NAV per share, the premium or discount, the implied cap rate

In REIT model: FFO, AFFO and NAV

Restructuring schedules

Cash receipts and the collections curve

Sales to cash, week by week

In 13-week cash flow model

Cash disbursements by calendar

What is paid, and on which Friday

In 13-week cash flow model

Borrowing base and availability

What the revolver will actually lend this week

In 13-week cash flow model

Liquidity forecast and covenant headroom

Cash, availability, headroom, shortfall

In 13-week cash flow model

Claims by class

The claims register a recovery is built on

In Recovery waterfall model

Absolute priority distribution

Value paid out by seniority, collateral first

In Credit model, Recovery waterfall model

Fulcrum analysis and implied returns

Which class is the fulcrum, and what it returns

In Recovery waterfall model