2026-10-17 · AI-drafted · Modeling · Finance · Decision-Making · 6 min read
Build the Sensitivity Table That Makes Your Model Honest
A financial model that returns one number is a false promise. Every output — revenue, free cash flow, enterprise value — depends on assumptions that could be wrong by 10%, 30%, or more. A sensitivity table does not make uncertainty disappear; it makes uncertainty visible. Once you can see how the output moves when two key drivers shift simultaneously, you stop defending a single forecast and start making better decisions. That shift — from point estimate to decision range — is what separates a model that survives review from one that collapses under the first question.
Why a Single Forecast Is a Liability
Consider a retailer with EUR 40m of revenue and a 12% EBIT margin. A straightforward DCF at a 9% discount rate and 3% terminal growth rate produces an enterprise value of roughly EUR 67m. That number feels precise. It is not. It is the intersection of at least five assumptions — revenue growth, margin, tax rate, capital expenditure intensity, and the discount rate — each of which carries its own uncertainty band.
When a model presents one number, it implicitly claims those assumptions are correct. Analysts who defend a single output spend their energy justifying the inputs rather than understanding the range of outcomes. A sensitivity table reframes the conversation: instead of 'is EUR 67m right?', the question becomes 'under what conditions does the value fall below EUR 50m, and how likely are those conditions?'
This is not a theoretical concern. An Indian IT services firm bidding on a long-term contract denominated in USD faces margin sensitivity to both the INR/USD exchange rate and headcount cost inflation. A Brazilian retailer expanding into Argentina faces revenue sensitivity to local inflation and FX pass-through. In each case, a single-point model obscures the real decision.
The Architecture: Inputs First, Table Second
A sensitivity table only works if the model is built correctly from the start. Every assumption that might vary — revenue growth rate, gross margin, discount rate, terminal growth rate, tax rate — must live in a dedicated Inputs sheet as a named cell. The income statement, working capital schedule, and DCF must reference those named cells directly. If a margin assumption is hard-coded into row 47 of the income statement, the sensitivity table cannot reach it.
In Course 2, the Inputs sheet is the single source of truth. When that discipline is in place, building a two-variable sensitivity table in Excel takes fewer than ten minutes. The Excel Data Table function (found under What-If Analysis in the Data tab) reads one row input and one column input, recalculates the entire model for each combination, and returns the output you specify — enterprise value, free cash flow, interest coverage ratio, or any other cell.
The mechanics: place your output formula in the top-left corner of the table range. List one variable's values across the top row and the other variable's values down the left column. Select the full range, open Data Table, assign the row input cell and the column input cell, and confirm. Excel populates every cell in the table automatically. The result is a grid — typically 5×5 or 7×7 — that shows the output across 25 or 49 combinations of your two chosen drivers.
A Worked Example: EUR 40m Retailer
Return to the EUR 40m retailer. Assume the base case uses a 9.0% WACC (weighted average cost of capital — the blended required return on debt and equity) and a 3.0% terminal growth rate. The DCF produces EUR 67m. Now build a 5×5 table with WACC ranging from 7.5% to 10.5% across the top and terminal growth rate ranging from 1.5% to 4.5% down the left side.
The table might show enterprise values from EUR 48m at the pessimistic corner (10.5% WACC, 1.5% growth) to EUR 102m at the optimistic corner (7.5% WACC, 4.5% growth). That is a EUR 54m range on a EUR 67m base case — a spread of more than 80%. A buyer or lender who sees only the base case is making a decision with incomplete information. The table does not tell you which cell is correct; it tells you how much the answer depends on assumptions you cannot fully control.
A second table might hold WACC constant at the base case and vary revenue growth rate (rows) against EBIT margin (columns). If the enterprise value stays above EUR 55m across most of the grid, the valuation is relatively robust. If it dips below EUR 55m whenever margin falls below 10%, that margin assumption deserves scrutiny — and probably a conversation with the operations team before the deal closes.
Choosing the Right Two Variables
Not every pair of variables produces a useful table. The most informative tables pair one demand-side driver with one cost-side or capital-structure driver. For a manufacturing firm reporting under IFRS — say, a German industrial company with EUR 200m in revenue — the most consequential pair is often revenue growth rate and EBIT margin, because those two drivers together determine the size of the free cash flow stream before any financing effects.
For a capital-intensive business — a Brazilian retailer building new stores — the relevant pair might be revenue per square metre and capital expenditure per new location. For a leveraged company, interest coverage ratio sensitivity to EBITDA growth and interest rate movement tells you more than enterprise value alone. The discipline is to ask: which two assumptions, if wrong simultaneously, would change the decision? Those are the variables that belong in the table.
A practical rule: run at least two sensitivity tables per model. The first should cover valuation (WACC and terminal growth, or revenue growth and margin). The second should cover a covenant or liquidity metric — interest coverage, net debt to EBITDA, or minimum cash balance — because lenders and boards often care more about whether the company stays solvent than about the precise enterprise value.
Colour-Coding and the Output Page
A sensitivity table without formatting is a grid of numbers that nobody reads. Apply conditional formatting to highlight the cells where the output crosses a decision threshold. If the acquisition only makes sense above EUR 55m, shade every cell below that value in a muted red and every cell above it in a muted green. The decision-maker's eye goes immediately to the boundary — the line between acceptable and unacceptable outcomes.
The Outputs sheet in a well-built model collects all sensitivity tables, scenario summaries, and key ratios in one place. Executives and lenders should never need to navigate the working sheets. They open the Outputs sheet, read the tables, and understand the range of outcomes in under two minutes. That is the standard a professional model must meet.
In Course 2, the Outputs sheet is the last thing built and the first thing read. Every table on it links back to the Inputs sheet and the model's core schedules — no hard-coded numbers, no copy-paste values. If an assumption changes, every table updates automatically. That is what makes the model a living tool rather than a static document.
From Table to Decision
A sensitivity table answers a specific question: how much does the output move when these two inputs move? It does not tell you which scenario is most likely, and it does not replace judgment. What it does is force the analyst — and the decision-maker — to confront the assumptions explicitly rather than accept a single number at face value.
The practical discipline is to present the table alongside a written statement of which cell represents the base case and why. If the base case sits in the optimistic half of the grid, that is worth naming. If the pessimistic corner produces an outcome that would breach a debt covenant or destroy the investment thesis, that is worth naming too. The table is the evidence; the analyst's job is to interpret it honestly.
Course 2 builds this discipline from the ground up — starting with workbook architecture and a clean Inputs sheet, moving through the three-statement model and tie-outs, and finishing with the DCF and the sensitivity and scenario outputs that make the model useful in a real decision. The goal is not a model that looks impressive; it is a model that tells the truth about what it does and does not know.
A Note on What This Course Is — and Isn’t
We don’t pursue CE accreditation. The courses are pure education, not credentialing.
Nothing in this course constitutes personalized financial, legal, or investment advice. You’ll learn frameworks and analytical tools — what you do with them is your decision.
We use AI heavily and we’re transparent about it.
Drafted with AI from the outline of Dr. Kareem Tannous’s Course 2. Published automatically under his editorial standards.
$189 per course. $504 for the bundle of three.
100% refund within 3 days of enrollment AND zero module access. Accessing any module — even briefly — waives the right to a refund permanently. Decisions are final; no appeals.
Course 2: Excel Modeling for Financial Decisions →
New to financial statements? Start with Course 1 →
Instructor: Kareem — DBA International Business · MS Applied Economics & Predictive Analytics · MBA Finance & Accounting · Series 65 · university-level instructor since 2014.
— Dr. Kareem Tannous