Building Defensible Valuation Models

A valuation model with flawless formulas but poor structure fails almost as badly as one with outright errors, because nobody can verify the formulas, update the assumptions, or trace the logic. Banking models are built under time pressure, reviewed by several layers of seniority, and handed between team members throughout a deal, so the architecture of the workbook matters as much as the calculations inside it. A defensible model is one where any reviewer can distinguish inputs from calculations at a glance, follow every output back to its source, and trust that changing one assumption updates everything downstream.

This is the craft layer of valuation: how the three-statement backbone gets built and linked, how DCF, comps, LBO, and merger mechanics translate into reliable spreadsheets, how sensitivity and scenario work turns a point estimate into a range, and how errors are caught before output reaches a client. The methodologies themselves are assumed here; the subject is executing them so the output stands up to scrutiny.

Architecture and Formatting

The organizing principle of every banking model is a left-to-right flow: assumptions and inputs on the left of the workbook, calculations in the middle, outputs and summaries on the right. The flow mirrors how the model is used. The analyst changes an assumption on the left, the calculation engine updates in the middle, and the result appears on the right. Anyone opening the file for the first time can follow that flow to understand what drives the answer.

The Standard Tab Structure

A well-organized model uses a consistent set of tabs:

1. 1.Cover page: company name, model type, date last updated, analyst name, and a short version history. It costs seconds to maintain and saves real time when the model is reopened weeks later or by someone else.
2. 2.Assumptions or drivers tab: every hard-coded input in one place, separated into static inputs (tax rate, opening debt balance) and dynamic inputs that vary by year (growth rates, margin trajectories).
3. 3.Calculation tabs: the income statement, balance sheet, and cash flow statement plus supporting schedules. These contain formulas only, never typed values.
4. 4.Output or dashboard tab: implied enterprise value, implied value per share, key multiples, and football field data, clean enough to screenshot straight into a client discussion.
5. 5.Sensitivity and scenario tab: the data tables and cases discussed later in this article.

The assumptions tab is the model's control panel. An MD or client should be able to change one number, say revenue growth from 8% to 10%, and see the output move without touching a formula. If changing an assumption requires edits on three different tabs, the architecture has already failed.

The calculation tabs rely on dynamic linking: the three statements are connected through cell references so a change anywhere cascades everywhere. A revenue change updates net income, which flows into operating cash flow, which updates the cash balance on the balance sheet. Dynamic linking is what keeps the model internally consistent after every assumption change, and a balance check row (assets minus liabilities minus equity, which must equal zero) is the standing proof that the linking works.

The Color System

Formatting in banking models is functional, not cosmetic; it is the navigation map that tells a reviewer what each cell is without opening the formula bar. The color conventions are universal across banks even when templates differ:

• •Blue font: hard-coded inputs, anything typed directly into a cell. Blue tells the reviewer this is an assumption that can be challenged or changed.
• •Black font: formulas referencing cells on the same sheet. Black means calculated, do not overwrite.
• •Green font: formulas pulling from another tab. Green means check the source sheet.
• •Purple font (some banks): links to a different Excel file, flagged because external references break when files move or get renamed.
• •Red font or fill: errors, placeholders, or anything that must be resolved before the model is final.

The same logic marks time: historical periods are blue because they are hard-coded from reported filings, while forecast periods are black because they are calculated from assumptions. The visible transition from blue to black is the boundary between fact and forecast, one of the most important distinctions in any model.

Number and Line-Item Conventions

Numbers follow a single standard applied everywhere: dollars in millions to one decimal with the unit stated in the header, percentages to one decimal, multiples to one decimal followed by x (11.2x, never "times"), and negative numbers in parentheses rather than with minus signs, matching audited financial statements. Major line items (revenue, EBITDA, EBIT, net income, free cash flow) are bold; sub-items are indented; a single border sits above subtotals and a double border above grand totals; shading is used sparingly. Consistency is the point: a reviewer who knows the standard spots anomalies instantly, and inconsistent formatting signals carelessness that undermines trust in the calculations themselves.

Structural Rules That Prevent Errors

Three structural habits do more to prevent errors than any amount of checking afterward:

1. 1.One formula per row: the formula in the first forecast period must be copyable across every period without modification. If Year 3 calculates differently from Year 4, the model is harder to audit and far more likely to hide a mistake.
2. 2.No hard-coded values inside formula cells. A magic number, such as a growth rate typed directly into a revenue formula instead of referenced from the assumptions tab, is invisible to anyone reviewing the assumptions and will not update when the assumption changes. Magic numbers are the leading cause of model errors in banking because they break the link between what the assumptions tab says and what the model actually computes. Every input flows from the assumptions tab, no exceptions.
3. 3.A documented sign convention, chosen once and applied everywhere: either all costs entered as positive values with the formulas doing the subtracting (more intuitive for the builder), or costs entered with natural negative signs so each column simply sums (more intuitive for the reviewer, since each line's sign matches its economic impact). Both work; mixing them silently inverts line items and is among the hardest errors to spot visually.

Documentation rounds out the discipline: cell comments recording the source of key inputs ("management guidance from the Q3 earnings call, update after Q4"), a version log on the cover tab, and for very large workbooks a linked table of contents.

The Three-Statement Backbone

Every banking model sits on a three-statement model: the income statement, balance sheet, and cash flow statement, dynamically linked. A DCF extracts unlevered free cash flow from it, an LBO rebuilds its capital structure and layers a debt schedule on top, and a merger model combines two of them with transaction adjustments. The linkage forms a loop: the income statement feeds the cash flow statement (net income is its starting point), the cash flow statement feeds the balance sheet (ending cash), and the balance sheet loops back into the income statement, where debt balances set interest expense and cash balances set interest income. That loop is simultaneously the model's greatest strength and its main technical challenge.

The Seven-Step Build

The build follows a standard sequence:

1. 1.Input 3 to 5 years of historical financials from filings, in blue, with history on the left and projections extending right.
2. 2.Calculate historical ratios and drivers: revenue growth, gross and EBITDA and EBIT and net margins, D&A and CapEx as percentages of revenue, working capital days (DSO, DIO, DPO), the effective tax rate, and the implied interest rate on debt.
3. 3.Project the income statement from revenue down to EBIT, leaving interest and taxes for later.
4. 4.Build the supporting schedules: debt, depreciation, and working capital.
5. 5.Complete the income statement with interest expense from the debt schedule and taxes on pre-tax income.
6. 6.Build the cash flow statement from net income down to the net change in cash.
7. 7.Complete the balance sheet, with the ending cash balance from the cash flow statement as the final item that makes it balance.

Step 1 carries three accuracy traps. The three historical statements must reconcile with each other (net income matching the cash flow statement's start, ending cash matching the balance sheet) before anything is projected. Non-recurring items must be separated out, because projections build off normalized earnings, not reported figures. And mid-year acquisitions or divestitures in the historical period make year-over-year comparisons misleading if they go unnoticed.

The ratios from step 2 are the starting point for assumptions, never the assumptions themselves. Revenue that grew 12% last year does not automatically grow 12% next year; the analyst must be able to explain why each assumption was chosen, not merely show it is consistent with history. A related choice at step 3 is between projecting costs as percentages of revenue (simpler, adequate for most valuation models) and a driver-based build where each cost line has its own logic, such as headcount times average compensation. Driver-based models are more accurate but far more complex, and are generally reserved for detailed operating models.

Supporting Schedules

Supporting schedules project specific line items in more detail than the statements themselves allow, then feed the results back into the statements.

The debt schedule tracks each tranche from beginning balance through borrowings and repayments to ending balance, with interest expense calculated on the average of the opening and closing balances times the applicable rate. The average convention is more accurate than using the opening balance alone, but it is what creates the circular reference discussed below. The depreciation schedule projects D&A from the existing asset base plus new CapEx; in simple models it collapses to a percentage of revenue or of gross PP&E. The working capital schedule projects receivables, inventory, and payables from the DSO, DIO, and DPO ratios.

Working capital demands care with levels versus changes. The statements work in levels, while the cash flow statement runs on the change from one period to the next: receivables rising from $100 million to $120 million means $20 million of cash consumed. The sign convention follows directly: an increase in a current asset is a cash outflow on the cash flow statement, an increase in a current liability is a cash inflow. Inverting these signs is a classic error that breaks the balance sheet.

The Cash Flow Statement and the Final Plug

The cash flow statement starts with net income, adds back non-cash charges (D&A, stock-based compensation, deferred taxes), adjusts for working capital changes, subtracts CapEx in the investing section, and captures debt activity, dividends, and share issuance or repurchase in the financing section. The net change in cash, added to beginning cash, produces the ending cash balance, which is the last number entered on the balance sheet and the item that makes it balance.

When the balance check fails, the culprit is almost always the cash flow statement: a missed working capital line, depreciation double-counted, or a balance sheet item that never flowed through. This is why the balance check row, with conditional formatting that turns red on failure, is non-negotiable.

A Linked Example in Numbers

A simplified Year 1 shows how every number traces somewhere. On the income statement: revenue $1,000M (8% growth on $926M), COGS $600M (a 40% gross margin), SG&A $200M, D&A $50M from the depreciation schedule, giving EBIT of $150M; interest expense $30M (average debt of $500M at 6%), pre-tax income $120M, taxes $30M at 25%, net income $90M.

On the cash flow statement: net income $90M, plus the $50M D&A add-back, minus a $15M working capital increase, gives operating cash flow of $125M; CapEx of $60M leaves $65M; debt repayment of $25M and dividends of $20M leave a net cash increase of $20M.

On the balance sheet: cash becomes $120M (prior $100M plus $20M), receivables $137M (50 days DSO on revenue), inventory $99M (60 days DIO on COGS), PP&E $510M (prior $500M plus $60M CapEx minus $50M D&A), debt $475M (prior $500M minus the repayment), and retained earnings up $70M (net income minus dividends). The ending cash of $120M is calculated last, and it balances the sheet. If it does not, one of these linkages is broken.

Excel Circularity and How to Manage It

Interest expense depends on the debt balance, which depends on cash flow, which depends on net income, which depends on interest expense. Interest income runs the same loop through the cash balance. This circular reference is inherent to a fully linked model, and there are three standard ways to handle it:

1. 1.Enable iterative calculation in Excel (under File, Options, Formulas), letting Excel cycle the loop until it converges. Most banking models run this way.
2. 2.Build a circular switch, a toggle cell that replaces the circular interest calculation with a manual override so the loop can be broken for debugging and re-enabled for normal use.
3. 3.Calculate interest on the prior period's balance instead of the average, which removes the circularity entirely at a small cost in accuracy.

The third option is an accepted simplification for three-statement work and timed modeling tests, but note the reconciliation with LBO practice below: in a leveraged model the average-balance convention is the industry standard, and the circularity is managed rather than avoided.

Three Entries That Trip Builders

Stock-based compensation touches all three statements and the entries must agree: an operating expense on the income statement, a non-cash add-back on the cash flow statement, and an increase in additional paid-in capital on the balance sheet, all for the same amount. Deferred taxes arise when the book tax provision differs from cash taxes paid; simple models assume they are equal, detailed models carry the difference to a deferred tax line. Minority interest means the income statement consolidates 100% of a partly owned subsidiary, subtracts the outside owners' share below net income, and shows their claim in the equity section; the cash flow treatment must avoid double-counting that add-back.

Building the DCF Model

A DCF is never a standalone spreadsheet. It sits on top of a balanced three-statement model, and building it detached creates three problems: projections disconnected from the balance sheet, no balance check to verify consistency, and a model that cannot be extended into an LBO or merger analysis later. Build the backbone first, verify it balances, then extract the DCF.

Extracting Unlevered Free Cash Flow

The DCF tab calculates, for each projection year:

Every component links directly to the three-statement model rather than being re-entered: EBIT from the income statement, D&A from the depreciation schedule, CapEx from the cash flow statement, working capital changes from the working capital schedule. Nothing on the DCF tab is typed by hand, so any assumption change flows through automatically. Using the example figures above, EBIT of $150M taxed at 25% gives NOPAT of $112.5M; adding $50M of D&A and subtracting $60M of CapEx and the $15M working capital increase yields UFCF of $87.5M.

Three execution points matter here:

• •Use the marginal tax rate, not the effective rate. The effective rate carries historical one-off tax items; the marginal rate (roughly 25 to 27% for US companies, combining the 21% federal rate with state taxes) reflects the ongoing burden.
• •Decide and document the stock-based compensation treatment: added back to UFCF (common in tech) or left as a true expense. The choice lives on the assumptions tab, not in an undocumented formula.
• •Sanity-check UFCF as a percentage of revenue and of EBITDA, the UFCF conversion ratio, against history and peers. A conversion ratio far from historical experience needs an explanation before the model goes anywhere.

Choosing the Projection Period

The explicit period must run until the company reaches a steady state in growth, margins, and capital intensity, because the terminal value assumes constant growth forever from that point. Mature, stable businesses (staples, utilities, diversified industrials) usually need 5 years. High-growth companies need 7 to 10: a SaaS business growing 30% a year is nowhere near steady state in year 5, and cutting the projections short forces the terminal value to embed an impossible growth rate or badly understates the value. Cyclical companies need at least one full cycle, typically 5 to 7 years, and if the business currently sits at a cyclical peak the projections must model the downturn and recovery first; a terminal value launched from a peak year overstates normalized cash flow.

The WACC Block

WACC is one of the most scrutinized parts of any DCF, so the model carries it as a self-contained calculation block where every input is visible, sourced, and dated. "Beta: 1.15, Bloomberg adjusted beta as of a stated date" is defensible; a bare number is not, and in a fairness opinion context every WACC input can be examined in litigation.

A typical cost of equity block reads: risk-free rate 4.30% (the 10-year Treasury yield on the stated date), equity risk premium 5.50% (a documented source such as the Damodaran implied ERP), raw Bloomberg beta 1.25 with the Blume-adjusted value 1.17, peer median unlevered beta 0.95 relevered at the target's 0.45x debt-to-equity to 1.27 (0.95 times 1 plus 0.75 times 0.45), producing a cost of equity of 11.29% (4.30% plus 1.27 times 5.50%). The beta line links to a supporting schedule showing each peer's levered beta, leverage, and unlevered beta, so a reviewer can trace the full derivation. Cost of debt uses the yield on the company's longest-dated unsecured bond, or comparable-rating yields where no public debt exists, and the weights use market values throughout.

Terminal Value, Both Ways

Build both terminal value methods and show them side by side. The perpetuity growth method:

And the exit multiple method:

Each method carries the other as its cross-check: the perpetuity method reports the exit multiple it implies, the exit method reports the perpetuity growth it implies. If the growth method implies a 25x exit in a sector trading at 10 to 12x, the growth rate is too aggressive; if the exit multiple implies 5% perpetual growth, the multiple is too high. The cross-check is a required quality control step in every DCF, not decoration.

Discounting, the Bridge, and the Output Page

Discounting uses the mid-year convention by default, with exponents of 0.5, 1.5, 2.5 and so on for the explicit years, reflecting that cash arrives throughout each year rather than on the last day; ignoring it understates value by roughly 3 to 5%. Where the valuation date does not sit at a fiscal year-end, which is most of the time, the XNPV function discounts each cash flow by its exact date rather than assuming even annual spacing.

The output shows the pieces explicitly: the present value of the explicit-period cash flows, the present value of the terminal value, their sum as implied enterprise value, and terminal value as a percentage of the total. That percentage typically lands at 60 to 80%; above 85% the DCF is effectively a terminal value model wearing a projection costume, and the analyst should consider extending the explicit period or questioning the terminal assumptions. Senior reviewers look at this number first, and displaying it prominently signals analytical self-awareness.

The bridge to per-share value uses current balance sheet data, not projected:

with diluted shares calculated under the treasury stock method at the current share price. The output tab then carries the sensitivity grids (WACC against terminal growth, WACC against exit multiple, revenue growth against EBITDA margin) with the base case highlighted.

Mistakes and Pre-Flight Checks

The recurring DCF construction errors are worth memorizing as a checklist:

• •Discounting levered free cash flow at WACC, which double-counts the cost of debt and understates value; UFCF pairs with WACC.
• •Magic numbers hard-coded into formulas, invisible to reviewers.
• •A terminal growth rate above long-term GDP, implying the company eventually outgrows the economy; cap it around 2 to 3% for developed markets.
• •Using the effective tax rate and importing temporary tax noise into every projected year.
• •Skipping the mid-year convention, the terminal value cross-check, or the sensitivity grids entirely.

Before the output goes to an MD or client, run a systematic pass. Trace precedents on key cells and spot-check 5 to 10 formulas across sections. Test reasonableness: an implied value 3 times the current market cap means the model is wrong or the market is dramatically mispricing the company, and the former is more likely; an implied EV/EBITDA far outside the peer range demands an explanation. Stress test by pushing two or three assumptions to extreme but plausible values and confirming the model returns sensible numbers rather than error codes. Finally, check the output tabs in print preview, because many pitchbook pages are built from them and formatting failures invisible on screen are obvious on paper.

Version discipline wraps the process: file names carrying company, model type, version, and date (never the word final, because on a live deal there is no final), a change log recording what moved and why in each version, a backup saved before structural surgery, and sheet protection on the balanced three-statement tabs once verified.

Comps and Precedent Transaction Models

Comps and precedent transaction spreads are the models analysts build most often and learn first, and the comps table is the one deliverable that tends to reach the client page unchanged, exactly as built. The DCF gets summarized into a football field bar; the comps table goes in whole, every company, every multiple. Accuracy beats speed here without exception.

The Comps Spread

A clean comps model has three sections, on one tab or several depending on scale. The market data section holds share price, diluted shares, equity value, and each EV bridge component (debt, preferred, minority interest, cash) in its own cell. The financial data section holds revenue, EBITDA, EBIT, and net income for LTM and NTM periods, with growth and margin context, calendarized where fiscal year-ends differ. The multiples section computes the multiples and the summary statistics: mean, median, 25th and 75th percentiles, high and low.

The bridge rule is absolute: never pull enterprise value as a single number from a data provider. Providers use differing conventions for bridge items, a black-box EV cannot be verified or explained to a reviewer, and individual components cannot be updated as new data lands. Build EV from its parts, one cell each.

Keeping the Model Current

Comps are perishable. Share prices refresh before every presentation, through live Bloomberg or Capital IQ links or a manual update to the latest close. When a peer reports earnings, the analyst rolls the LTM figures (add the new quarter, drop the year-ago quarter), checks whether consensus estimates moved, and confirms share count, debt, and cash have not materially changed. Consensus-based NTM multiples can shift even when the share price does not. Presenting a table where a peer reported three weeks ago but the numbers are stale is one of the most credibility-destroying errors a junior analyst can make; part of the job is standing watch over the peer universe for earnings, M&A announcements, and guidance changes.

Special situations get explicit treatment:

• •A peer under acquisition speculation trades with a deal premium baked in; footnote it or exclude it from the core set.
• •A peer with negative EBITDA gets NM (not meaningful) in place of the multiple, and the data point is excluded from the summary statistics; without NM discipline, one outlier corrupts the mean and median for the whole group.
• •A peer that has actually been acquired comes out of the table entirely.
• •A peer with large one-time items in its trailing numbers gets an adjusted EBITDA multiple, with the adjustment footnoted.

Many groups maintain a master comps file covering the sector's full coverage universe, refreshed each earnings season, so any deal team can pull current comps without rebuilding. Keeping it accurate is classically the most junior analyst's most thankless responsibility.

What Precedent Models Add

A precedent transaction model shares the comps structure but works point-in-time. Each deal's enterprise value must be reconstructed from the offer terms: offer price times the target's diluted shares gives equity value, plus net debt gives EV, sourced from press releases, merger proxies, and data providers. The financial data must be time-stamped to the announcement date: a deal announced in March 2024 uses the LTM figures ending nearest March 2024, not today's, which means pulling historical financials and cross-referencing the original filings, especially for targets whose filings disappeared after integration. The model also carries a premium paid analysis, showing the offer's premium to the undisturbed share price at 1-day, 1-week, and 4-week lookbacks.

The finished table, either kind, is formatted for direct pitchbook use: companies ordered by relevance or size, the target shown separately for comparison, summary statistics labeled, footnotes explaining every adjustment or exclusion, and a source citation with the as-of date.

LBO and Merger Models

These are the most complex and highest-stakes models in banking, both layered on the three-statement backbone with transaction mechanics on top. They also point in opposite directions: the LBO model works backward from a target return to the maximum payable price, while the merger model works forward from agreed deal terms to the earnings and credit impact.

The LBO Model

The standard LBO build is modular:

1. 1.Assumptions: entry multiple, leverage by tranche, EBITDA growth, exit multiple, holding period, fees.
2. 2.Sources and uses, which must balance.
3. 3.Day 1 pro forma balance sheet with purchase price allocation adjustments.
4. 4.Projected financial statements over the holding period.
5. 5.Debt schedule.
6. 6.Returns analysis: exit enterprise value, exit equity, IRR, MOIC, and attribution.

The debt schedule is the hardest component: multiple tranches at fixed and floating rates, scheduled amortization, cash sweeps with leverage-based step-downs, call protection on high-yield notes, and PIK accrual on subordinated paper, each tranche independent but interacting through the prepayment waterfall. The circular reference here is intentional, since interest depends on debt balances that depend on cash flow that depends on interest. Run iterative calculation or install a circuit breaker: a labeled toggle cell that zeroes the circular formulas when switched off so the model can be debugged, with conditional formatting that turns red as a reminder to switch it back on before presenting. In the LBO context, do not dodge the circularity with beginning-of-period balances; average balances are the standard, which is the stricter cousin of the prior-period shortcut acceptable in plain three-statement work.

Returns are computed with XIRR rather than IRR, because entry, dividend recapitalizations, and exit rarely fall on neat annual anniversaries. And the model's defining use is the solve: run it across entry multiples, find where the IRR hits the sponsor's target (say 20%), and that price is the LBO floor valuation. The build must therefore let the entry price flex cleanly through the whole structure.

The Merger Model

The merger build mirrors the modularity: assumptions (offer price, consideration mix, synergies, fees, financing), transaction adjustments, pro forma income statement, accretion/dilution analysis, pro forma balance sheet, and pro forma credit analysis against rating thresholds.

Three mechanics carry the model. Goodwill is the plug: after combining both balance sheets and applying every adjustment (new debt and equity, fees, PPA intangibles, deferred taxes), goodwill is whatever balances the pro forma sheet, so a sheet that will not balance means a broken adjustment, not a goodwill problem. PPA amortization flows into earnings: the intangibles created in the allocation amortize as a non-cash expense that makes the deal look more dilutive, so accretion/dilution is presented both with and without it to show the cash impact. And the form of consideration sets the share count: all-cash leaves the acquirer's diluted count unchanged, all-stock adds the exchange-ratio shares issued to target holders, and a mix adds shares only for the stock portion.

The Classic Errors

On the LBO side: forgetting to subtract remaining debt from exit enterprise value when computing exit equity (overstating returns), levering off the wrong EBITDA (leverage multiples key off LTM or pro forma EBITDA, not a projected year), and omitting transaction fees from uses (understating the equity check). On the merger side: leaving the target's historical equity sitting in the combined balance sheet instead of eliminating it, counting convertibles twice (as diluted shares and as debt in the bridge), and forgetting to tax-adjust PPA amortization, whose tax shield partially offsets the earnings hit.

Sensitivity and Scenario Analysis

A single-point estimate is never the final answer. Every DCF, LBO, and merger model ships with analysis showing how the output moves under different assumptions, and the two tools that provide it are not interchangeable. Sensitivity analysis varies one or two inputs mechanically across a range; scenario analysis builds complete, internally consistent alternative worlds. A model without either is an incomplete model.

Data Tables

The Excel workhorse is the two-variable data table (under Data, What-If Analysis): one input across the top row, another down the left column, the output cell referenced in the corner, and Excel fills the grid with the output for every combination. The standard pairings by model type:

Presentation follows a convention: the base case cell highlighted, the reasonable range lightly shaded, the corners left in as bounds even where they are unrealistic, and both axes clearly labeled.

Reading the grid is a skill in itself. Take a table of implied share price across WACC from 8.0 to 10.0% and exit multiples from 9 to 12x: the base case at 9.0% and 10.5x implies $24.40, the full grid spans $17.10 (10.0%, 9.0x) to $33.30 (8.0%, 12.0x), and excluding the extreme corners the defensible range is roughly $20-28. If the numbers move more along the multiple axis than the WACC axis, the exit multiple is the assumption that deserves the scrutiny.

Scenarios

A scenario changes multiple assumptions together to tell one coherent story. The base case reflects management guidance and the analyst's best judgment; the upside case combines stronger growth, faster margin expansion, and cheaper capital; the downside case combines recessionary revenue, compressed margins, and a wider risk premium. Internal consistency is the defining requirement: in a real recession, growth, margins, and WACC move together, so trimming revenue growth by two points while holding everything else still is a sensitivity wearing a scenario label, and presenting it as a downside case is misleading.

Mechanically, the standard build is a scenario toggle: one cell on the assumptions tab selecting Base, Upside, or Downside, with each assumption row pulling its active value from the corresponding scenario column via a CHOOSE or INDEX/MATCH lookup. The whole model flips cases in one click, which matters when an MD says "show me the downside" in a live meeting. Larger models lay all assumption sets on a dedicated scenarios tab feeding an active-assumptions row keyed to the selector.

Tornado Charts and How the Pieces Combine

A tornado chart ranks assumptions by impact: each variable moves from its low to high end while everything else holds at base, and the resulting swing in value is drawn as a horizontal bar, widest at the top. It answers "what actually drives this number" in one visual. If the implied share price swings $5 on a 50 basis point WACC move but only $1 on two points of revenue growth, the WACC assumption deserves the attention. In client settings it grounds the discussion in dollars: one turn of exit multiple worth roughly $500 million, each point of growth worth roughly $200 million. In a typical DCF the terminal assumptions and WACC dominate the chart.

The deliverable combines the tools: the scenario range (downside through upside) becomes the DCF bar on the football field, while the sensitivity grids sit behind it as the exhibits explaining why the range is what it is.

Auditing and Quality Control

A model containing an error is worse than no model, because it produces output that looks precise and is wrong, and in banking that output feeds deal pricing, board recommendations, and fairness opinions. Model auditing is the systematic verification that runs before any output leaves the team.

The Review Chain

Models pass through a multi-level review. The analyst builds. The associate reviews: every formula, inputs verified against source documents, assumption logic tested, output checked for sense, typically 2 to 4 hours for a standard model. The VP or MD spot-checks assumptions and output against their market judgment rather than re-checking formulas. The associate pass is the real quality gate; a thorough associate catches what nobody else will, and a rubber stamp passes errors straight to the client.

The Essential Checks

Five checks form the standing audit routine:

1. 1.Balance check: assets equal liabilities plus equity in every period, on a dedicated row with red conditional formatting on failure.
2. 2.Formula consistency: every row uses one formula across all periods; Excel's Go To Special with Row Differences finds cells that deviate from their neighbors.
3. 3.Sign conventions: outflows treated consistently throughout, the error class hardest to see by eye.
4. 4.Reasonableness: the implied multiple sits sensibly against trading comps (a DCF implying 25x against peers at 10 to 12x needs a reason), the implied price is explicable against the current share price, margins trend consistently with guidance, and terminal value sits in its normal 60 to 80% band.
5. 5.Directional stress tests: value rises when growth rises, falls when WACC rises, LBO IRR falls as entry multiple rises, and extreme inputs produce extreme but correctly signed outputs rather than errors.

The mechanical checks and the reasonableness checks are both mandatory because they catch different failures. An obviously absurd output, negative enterprise value or a 500% IRR, gets caught instantly. The dangerous error is the plausible one: an implied $25 when the right answer is $22 can survive every glance and surface only when a counterparty, client, or court challenges it.

Debugging a Broken Balance Sheet

When the balance check fails, the search is systematic. Establish the direction first: are assets high or low against liabilities plus equity. Then go to the cash flow statement, where the vast majority of balance errors live: confirm the working capital change captures every current line with correct signs, that D&A is added back exactly once, and that CapEx reduces cash while increasing PP&E. Check the debt schedule next, verifying borrowings, repayments, and ending balances flow to both the financing section and the balance sheet. Check retained earnings, which must equal the prior balance plus net income minus dividends. Then hunt hard-codes with formula view (Ctrl and the tilde key) or Go To Special with Constants, since a typed number sitting where a formula belongs will not update with assumptions. One practical shortcut: if the imbalance exactly equals a recognizable number elsewhere in the model, such as D&A or one working capital line, that is a strong pointer to the broken linkage.

From Model to Pitchbook

The model is the analytical engine; the pitchbook is what the client actually sees, and translating one into the other is an underrated skill. A brilliant model that produces unintelligible slides fails at the decision it was built to support. The valuation section of a pitchbook typically runs 5 to 20 slides, and it is the most scrutinized part of the book because it holds the numbers that drive pricing and recommendations.

What the Valuation Section Contains

The football field chart is the centerpiece: the trading comps range (25th to 75th percentile), the precedent transactions range, the DCF range driven by the sensitivity work, the LBO-implied range where relevant, and reference points such as the 52-week trading range and analyst price targets. It shows at a glance where the methodologies converge, the zone of highest confidence, and where they diverge, the zone requiring judgment.

Around it sit the supporting pages. The summary comps table compresses the Excel spread from 20-plus columns to 5 to 7, keeping the primary multiple and one or two context metrics, with the target highlighted. The DCF page pairs the key assumptions with the sensitivity grid, base case marked. Methodology-specific backup follows as needed: the full precedent set, the projection summary, the LBO returns page, the premium analysis.

Principles of Translation

Four rules govern the move from tabs to slides. Every slide gets a headline, a sentence at the top stating the takeaway that the exhibits below it prove; "the implied valuation of $45-52 per share is supported by convergence across three independent methodologies" is a headline, while a chart with no context is just a picture. Simplify without distorting: fewer peers and fewer multiples on the slide is fine only if the conclusion survives the cut. Round honestly: the model says $24.37, the slide says approximately $24-25, because per-cent precision on an implied share price is false precision. Source everything in small print with as-of dates, and format for an audience of board members, not modelers.

The discipline matters because the pitchbook outlives the model. Years after a deal closes, nobody reopens the Excel file; the book sits in the client's records and, for fairness opinions, in legal proceedings. It is the permanent record of the valuation work, and it has to stand on its own with nobody there to explain it.