A three-statement model is an Excel workbook that links the income statement, balance sheet, and cash flow statement so all math ties across periods. In practice, a case study here means rebuilding that model for a simple control acquisition using only what you can prove from documents and historicals. The payoff is decision-grade numbers that hold up in diligence and in credit negotiations.
When lenders, the board, and auditors can follow the trail, you get faster credit agreement turns and lower model risk. The target deliverable is a clean, fully linked model with auditable inputs and schedules for debt, interest, working capital, capex, and taxes, plus outputs that answer returns and covenant questions. If you are new to this, start with a concise three-statement model walkthrough, then apply the control mindset described below.
Scope and assumptions that keep the build focused
For clarity, assume a single-entity, all-cash asset purchase of a US operating company with one term loan and one revolver. Work in USD under US GAAP, flag IFRS differences where relevant, and use monthly columns because most management reports monthly. No macros, limited circularity, and inputs clearly segregated from formulas will keep the file stable under time pressure.
Evidence mapping first so every number has a source
Start by tying each block of the model to a document. Build a short evidence file and map each linkage so reviewers can trace the logic without guesswork.
- Purchase agreement: Price, adjustments, assumed liabilities, working capital peg, closing cash mechanics, transaction costs, and tax gross-ups. If earnouts exist, classify them under US GAAP and decide if they run through the P&L at fair value.
- Credit agreement: EBITDA definition, leverage and coverage ratios, sweep rules, baskets, base rate definitions and floors, amortization, fees, and default rate. Extract the precise order of operations for mandatory prepayments to avoid covenant misreads.
- Historical financials: At least 24 trailing months of income statement and balance sheet. Build a working capital roll-forward and capture inventory capitalization policy.
- Capex plan: Maintenance vs growth, useful lives, and depreciation policy.
- Tax profile: Historical effective tax rate, state apportionment, NOLs, 163(j) interest limits, elections (338(h)(10)/336(e)), and expected transaction tax costs.
- Leases: Operating and finance lease schedules with the weighted-average discount rate under ASC 842. Note IFRS 16 classification if IFRS users are involved.
- Supplier finance: Disaggregate supplier finance and receivable programs for cash flow presentation and IFRS supplier finance disclosures.
- HR and incentives: MIP/option pool/ratchets with ASC 718 or IFRS 2 effects if part of go-forward costs.
Minimum viable outputs aligned to decisions
Build only what investment and credit decisions require. Avoid model bloat and reserve detail for what moves cash, covenants, or valuation.
- Primary metrics: EBITDA, unlevered and levered free cash flow, net debt, total leverage, interest or fixed charge coverage, minimum liquidity, covenant compliance, and cash available for sweep.
- Investor view: Entry and exit EV, equity bridge, gross and net IRR, MOIC, distribution timing, and downside under covenant cures.
- Lender view: Closing compliance, quarterly headroom, sweeps, and sensitivities.
- Accounting view: Pro forma balance sheet at close, purchase accounting, D&A, and tax expense aligned to enhanced disclosures under ASU 2023-09.
Architecture and standards that make review fast
Set the structure before formulas. Name sheets predictably: Cover, Inputs, Assumptions, Raw_History, Adjustments, Drivers, IS, BS, CFS, Debt, WC, Capex_PP&E, Tax, Equity, Outputs_Dashboard, and Checks. Use a single start date and monthly timeline with EOMONTH for propagation and quarter or year bridges where needed.
Color-code inputs in blue, formulas in black, and checks in red. Keep one calculation per line, avoid embedded constants, prefer non-volatile functions, and use LET or LAMBDA only to improve clarity. Add a Checks sheet tying balance ties, cash flow bridges, sources and uses, and covenant headroom to a single “model OK” flag. Stamp versions, control file naming, and maintain a change log consistent with model risk practices.
Opening balance sheet and purchase accounting setup
Anchor on the opening balance sheet the day after close. Build sources and uses that reconcile equity, term loan, and revolver draws to purchase price, transaction and financing fees, working capital true-up, and cash to balance. Under US GAAP, financing fees reduce the carrying value of debt and amortize via effective interest, while transaction costs generally hit expense unless tied to financing or equity.
For purchase accounting, use a simple intangible placeholder and a customer relationship life, then refine when the valuation arrives. Goodwill equals purchase price plus assumed liabilities minus net identifiable assets. If the structure creates a step-up, model the resulting deferred taxes under ASC 740 or IAS 12. For a deeper primer on PPA mechanics, see this short overview of purchase price allocation.
Operating plan drivers grounded in evidence
Use drivers management tracks and that history supports. Keep them simple, testable, and stable across scenarios.
- Revenue: Choose the lowest-resolution driver the business runs on. Volume times rate with a seasonality vector often works. Backtest against history and align fiscal months to avoid artifacts.
- COGS and gross margin: Drive as a percent of revenue or a cost build. If materials drive volatility, add a pass-through lag to test working capital strain and margin compression.
- Opex: Split labor vs non-labor and fixed vs variable. Model labor by headcount and comp per head, and non-labor as semi-variable or fixed dollars. Strip nonrecurring costs through Adjustments.
- Working capital: Separate AR, inventory, AP, and other accruals. Drive via DSO, DIO, DPO backed by history. Link AR to revenue, AP to COGS, and ensure inventory turns reconcile to COGS. Track supplier finance separately for liquidity interpretation.
- Capex and PP&E: Split maintenance and growth, set useful lives by class, and use straight-line depreciation. Tie capex cash to the cash flow statement.
Debt, interest, and a clean cash sweep
Build a modular debt schedule that mirrors lender math. Define benchmark rates and floors, then choose a simple forward curve or constant rate with sensitivities. Calculate interest on average balances and accrue at the right frequency.
For the term loan, set principal, amortization, maturity, base, spread, floor, and fees. Layer scheduled amortization first, then prepayments from the sweep. For the revolver, define commitment, availability blocks, draw rules, undrawn fees, and logic that draws to minimum cash and repays with surplus. Keep iterative calculation only if necessary and document it clearly. If you need a refresher on timetable logic, this overview of debt scheduling is helpful.
Extract the exact cash sweep waterfall from the credit agreement. A common order is cash taxes, scheduled amortization, capex up to basket or minimum, revolver repayment, term loan prepayment, then restricted payments subject to baskets. Feed the waterfall from a single “cash available” line on the cash flow statement. Avoid duplicate cash logic.
Taxes to the level required for cash accuracy
Model current tax as a blended statutory rate adjusted for states. Include any step-up tax shield. Implement 163(j) interest limits and carry disallowed interest as a deferred attribute with scheduled deductibility. For deferred tax, use a simple asset or liability tied to book-tax timing from D&A and step-up, with a toggle for valuation allowance and NOL use. Treat Pillar Two as a sensitivity unless size and footprint make it binding.
Lease accounting that aligns with leverage optics
Under ASC 842, distinguish operating vs finance leases. Under IFRS 16, most leases are capitalized. Confirm whether lease liabilities count in leverage per the credit agreement. Anchor lease cash flows correctly in the cash flow statement, and reconcile the ROU roll-forward to history using a weighted-average discount rate when detail is light.
Cash flow statement assembled from balance sheet movements
Build the cash flow statement from balance sheet movements to protect integrity. In operating cash flow, start with net income and add back D&A, stock comp, financing fee amortization, and working capital movements. In investing, show capex and acquisitions net of asset sale proceeds. In financing, show debt draws and repayments, equity flows, and dividends. Under US GAAP, interest paid sits in operating; add a presentation toggle if dual reporting matters. The net change in cash must equal the change on the balance sheet without plugs.
Income statement, Adjusted EBITDA, and KPIs
After the balance sheet and cash flow statement stand, layer the income statement. Present revenue, COGS, gross margin, operating expenses, D&A, interest, other items, and tax. Maintain a reconciliation from GAAP EBITDA to covenant EBITDA via a dedicated Adjustments sheet with include and exclude flags. Track KPIs that matter for boards and lenders: cash conversion, working capital days, capex as a percent of revenue, maintenance capex coverage, ROIC, and leverage metrics per the credit agreement.
Covenants that mirror the legal definitions
Lift definitions directly from the credit agreement. Total net leverage is net debt over EBITDA with permitted addbacks, and you should clarify the treatment of lease liabilities. For interest or fixed charge coverage, follow the legal definition and include required capex if applicable. Add a “legal vs management” toggle to present strict compliance and internal policy views in one place.
Scenarios and sensitivities that are hard to break
Make a scenario manager with selectors for Management, Lender, and Downside cases that override top-level drivers such as revenue growth, gross margin, headcount ramp, capex, working capital days, and base rate. Add compact sensitivities for exit multiple, revenue CAGR, and base rate via data tables that run on outputs only or behind a calc switch. For a broader perspective on scenario design, see this step-by-step guide to three-statement models.
Controls and auditability that reduce operating risk
Borrow from regulated model risk. Add tie-outs for monthly to annual bridges, cash to balance sheet, sources to uses, purchase accounting to consideration, and tax expense to rate times pre-tax income plus discrete items. Flag circularity loops in the revolver and sweep. Lag effective tax rates to prevent tax circularity if acceptable. Ensure every material figure on outputs traces to a unique input and schedule, and keep a ReadMe, change log, and evidence file with links and dates.
Implementation plan and ownership that keep time on your side
One week is realistic if inputs are ready and roles are clear. Day 0, complete the evidence file, confirm accounting choices, lock standards, and assign modules. Day 1, build structure and timeline, load historicals, sources and uses, opening balance sheet, and checks. Day 2, build working capital, capex and PP&E, depreciation, and the income statement scaffold. Day 3, complete debt including revolver logic, interest, financing fees, sweep, and initial covenants. Day 4, add tax and leases, finalize the cash flow statement, tie all statements, set initial scenarios, and build outputs. Day 5, run QA and reconciliation, finalize legal EBITDA and covenants, the lender case, documentation, and independent review. Name a single model owner. Have legal counsel validate covenant definitions and a tax advisor confirm 163(j), state effects, and step-up.
Small numerical illustration to check the build
Suppose entry EV is 200 million dollars. Equity is 87.4 million. The term loan is 120 million with a 5-year maturity at SOFR plus 400 bps, a 1 percent floor, and 5 percent annual amortization. Financing fees are 2 percent or 2.4 million. Transaction costs are 5 million.
Uses total 207.4 million: purchase price 200 million, transaction costs 5 million, and financing fees 2.4 million. Sources are the term loan at 120 million and equity at 87.4 million. The opening balance sheet shows PP&E of 20 million, identifiable intangibles of 30 million with a 10-year life, goodwill of 150 million, cash at 0, a term loan carrying value of 117.6 million net of fees, and equity at 87.4 million. Year 1 interest on an average 118 million at 8 percent is about 9.4 million, fee amortization is roughly 0.48 million straight-line, and scheduled amortization is 6 million. Taxes at a 26 percent blended rate with step-up amortization reduce cash taxes and influence the leverage path.
Reporting nuances that move numbers in practice
Income tax disclosures under ASU 2023-09 require more granular detail, so model separate cash taxes where feasible and disaggregate rate drivers. Supplier finance should be shown separately with terms and cash impact. Pillar Two deserves a switchable overlay for in-scope entities. Lease capitalization has sharpened leverage optics, so align covenant definitions explicitly.
Governance, access, and evidence retention
Treat the file as a controlled artifact. Limit write access to two editors, password protect, and store in controlled folders. Share PDF packs and values-only versions when needed. Trace every input to a file with date and source, and stamp a data as-of date. Do not edit inputs without initials and date, and require change-log entries for logic changes with rerun checks. Obtain written signoff from deal and finance leads before use in term sheets or board decks. At closeout, archive versions, Q&A, users, and full audit logs, hash the archive, apply retention schedules, and honor legal holds.
When to skip a full rebuild
Use a quick LBO template for bidding when debt structures are standard and diligence is short. The kill test is whether drivers map to evidence and whether covenant math could change price or terms. Build a separate 13-week cash flow if liquidity is central. Avoid single-entity rebuilds when multi-entity and multi-jurisdiction factors make taxes and trapped cash material.
Common pitfalls and quick kill tests
- Sources and uses do not foot: Stop and fix before layering scenarios.
- Cash mismatch: If cash does not match between the cash flow statement and balance sheet, halt until it ties.
- Revolver oscillation: Prevent same-period draw and repay by adding hysteresis and buffers.
- Covenant EBITDA drift: Keep one GAAP to Adjusted EBITDA reconciliation with flags.
- Tax fiction: Do not ignore step-up or NOLs. If unsure, model conservative statutory cash taxes and disclose.
- D&A not tied: Always calculate D&A from the PP&E schedule.
- Mixed signs: Standardize sign conventions early.
- Hard-coded scenarios: Use one selector and an override map rather than scattered hard codes.
- Volatile function bloat: Keep recalculation under two seconds for faster diligence.
A fresh angle: the 3-minute lender check
Before sending the model, run a 3-minute lender check: First, print the latest “sources and uses” and “opening balance sheet” and confirm goodwill and deferred taxes reconcile. Second, print a single-page debt and sweep summary that shows rate assumptions, floors, amortization, and the exact waterfall. Third, print covenant headroom with the GAAP to Adjusted EBITDA bridge. If any line takes more than a minute to explain, simplify the schedule or add a footnote. This light-touch discipline catches most presentation risks without adding complexity.
What good looks like
A strong rebuild ties statements every period without plugs, mirrors lender schedules precisely, converts working capital to cash consistent with history unless changed by clear drivers, and explains tax expense and cash taxes in a paragraph with discrete items identified. Outputs are tailored, with one page for lenders showing headroom and sweeps, and one page for the investment committee showing IRR, MOIC, and liquidity. The file is 5 to 10 MB, has zero broken links, and displays a green “model OK.” For more structured practice, study a short guide to building a three-statement financial model and adapt it to your credit case.
Hand-off and maintenance
Freeze version 1.0 with a data as-of date and a named owner. Archive the evidence file and a PDF of key tabs. Include a one-page user guide that explains what to change, where to add scenarios, how to update rates, and how to run checks. Monthly, replace Raw_History, lock historical periods, roll forward the forecast, revalidate covenants, and refresh scenarios. Quarterly, run an independent review and update for any new GAAP or IFRS guidance.
Closing Thoughts
Rebuild when you cannot trace a number to a document or when logic deviates from definitions that govern cash. Keep the build thin, let outputs speak plainly, and reserve detail for the places that move cash, covenants, or valuation. You can always add a tab later. Removing noise once it spreads is much harder.