2026-10-14 · AI-drafted · Finance · Modeling · Decision-Making · 6 min read
Build the DCF That Survives the Assumptions Test
A discounted cash flow model — a method that estimates a business's value today by converting its expected future cash flows into present-value terms using a discount rate — is only as honest as the assumptions buried inside it. Most practitioners build the arithmetic correctly and then let optimistic revenue growth or a conveniently low discount rate do the heavy lifting. This post shows you how to structure a DCF in Excel so that every assumption is visible, every output is traceable, and the model survives the first hard question from a skeptic.
Why Most DCF Models Break Under Questioning
The failure mode is almost always the same: assumptions are scattered across the model rather than isolated in a single inputs sheet. A revenue growth rate lives in cell D14 of the income statement tab. A margin assumption is hard-coded three rows below it. The terminal growth rate — the rate applied to cash flows beyond the explicit forecast period — is typed directly into the valuation formula. When someone asks 'what happens if growth is 2 percentage points lower?', you have no clean answer because there is no single place to change.
The fix is architectural before it is mathematical. Course 2 builds the model in layers: a dedicated Inputs sheet holds every assumption as a named range; the income statement, working capital schedule, capex schedule, and debt schedule all pull from that single source; the DCF tab pulls from the outputs of those schedules. Change one cell on the Inputs sheet and the entire model updates. That structure is not a luxury — it is the minimum standard for a model anyone else will trust.
Consider a German manufacturer reporting in EUR under IFRS. Its model might carry 14 distinct assumptions: three revenue growth rates across the forecast horizon, a gross margin, three operating cost ratios, a tax rate, a capital expenditure percentage of revenue, a working capital days assumption, a weighted average cost of capital (WACC), a terminal growth rate, and a net debt figure. If any of those 14 live outside the Inputs sheet, the model is already broken.
Building the Free Cash Flow Bridge
Free cash flow to the firm (FCFF) — the cash a business generates after operating expenses and capital investment, available to all capital providers — is the number the DCF discounts. You derive it from the three-statement model, not from a shortcut formula. Start with EBIT (earnings before interest and taxes) from the income statement. Apply the tax rate to get NOPAT (net operating profit after tax). Add back depreciation and amortization from the asset schedule. Subtract the change in net working capital from the working capital schedule. Subtract capital expenditure from the capex schedule. The result is FCFF for that period.
An illustrative example: a Brazilian retailer with BRL 200m of revenue, a 12% EBIT margin, a 34% corporate tax rate (Brazil's standard IRPJ plus CSLL combined rate for large companies), BRL 8m of depreciation, BRL 6m of working capital build, and BRL 10m of capex. NOPAT = BRL 24m × (1 − 0.34) = BRL 15.84m. Add BRL 8m depreciation, subtract BRL 6m working capital build, subtract BRL 10m capex: FCFF = BRL 7.84m. That number — not EBITDA, not net income — is what you discount.
Each line of that bridge ties back to a schedule. Depreciation comes from the asset schedule, which tracks opening gross assets, additions, disposals, and accumulated depreciation. The working capital change comes from the working capital schedule, which tracks receivables days, inventory days, and payables days against revenue and cost of goods sold. If the bridge does not tie to those schedules, you have a model that produces a number but cannot explain it.
Choosing and Defending the Discount Rate
WACC — the weighted average cost of capital, a blended required return across debt and equity weighted by their share of the capital structure — is the discount rate for FCFF. It has three moving parts: the cost of equity, the cost of debt after tax, and the capital structure weights. Each part requires a judgment. The cost of equity is typically estimated using the Capital Asset Pricing Model (CAPM), which adds a risk premium above the risk-free rate scaled by the asset's sensitivity to market movements (beta). The risk-free rate should match the currency of the cash flows — use the 10-year German Bund yield for EUR cash flows, not the US Treasury rate.
An Indian IT services firm forecasting in INR faces a different calibration. The risk-free rate anchors to the Reserve Bank of India's 10-year government bond yield. The equity risk premium for India reflects higher country risk than a Western European market. A naive analyst who copies a US WACC into an INR model will systematically overvalue the business by discounting rupee cash flows at a rate that ignores currency and country risk. The model must match the currency of the cash flows throughout — discount rate, terminal value, and net debt all in the same denomination.
Once you have a WACC, stress-test it immediately. A 50-basis-point (0.50 percentage point) change in WACC can move a terminal value by 15–20% in a typical model. Build a two-variable sensitivity table in Excel — WACC on one axis, terminal growth rate on the other — and make it the first output a reader sees. If the implied valuation range is narrow enough to be useful only in a narrow band of assumptions, say so. Honesty about sensitivity is what separates a model from a marketing document.
Terminal Value: The Number That Dominates Everything
In most DCF models, the terminal value — the estimated value of all cash flows beyond the explicit forecast period, expressed as a single lump sum at the end of that period — represents 60–80% of the total enterprise value. That concentration of value in a single formula is the most dangerous feature of the DCF. The Gordon Growth Model formula for terminal value is FCFF in the terminal year divided by (WACC minus the terminal growth rate). A terminal growth rate of 3% versus 2% on a EUR 10m terminal-year FCFF at a 9% WACC moves terminal value from EUR 143m to EUR 125m — an EUR 18m swing from a single assumption.
The discipline is to anchor the terminal growth rate to something defensible: long-run nominal GDP growth in the relevant market, or the long-run inflation target of the central bank whose currency the model uses. For a eurozone business, the European Central Bank's 2% inflation target is a reasonable ceiling for the terminal growth rate of a mature business. A terminal growth rate above the long-run GDP growth rate implies the business eventually becomes larger than the economy — which is arithmetically possible in a model and economically absurd in reality.
Build the terminal value as a named formula that pulls the terminal growth rate from the Inputs sheet. Then run the sensitivity table. If the equity value turns negative at a WACC 1 percentage point above your base case, the model is telling you something important: the valuation is fragile. A model that communicates its own fragility is more useful than one that hides it.
Scenarios That Answer Real Decisions
A sensitivity table varies one or two inputs continuously. A scenario is different: it packages a coherent set of assumptions that reflect a plausible future state. A base case, a downside, and an upside each carry their own revenue growth trajectory, margin structure, capex intensity, and working capital behavior. The downside is not simply the base case with lower revenue — it typically also carries higher working capital requirements (customers pay more slowly when conditions are difficult) and compressed margins (fixed costs spread over less revenue).
In Excel, implement scenarios using a scenario-selector cell on the Inputs sheet — a dropdown that returns 1 for base, 2 for downside, 3 for upside — and use INDEX or CHOOSE functions to feed the correct assumption set into each named range. Every schedule then reads the active scenario automatically. This approach lets you present three complete, internally consistent models without maintaining three separate files, which is where version-control errors multiply.
The output page should show the implied enterprise value and equity value per share (or per unit of ownership) under each scenario, alongside the sensitivity table for WACC and terminal growth. That layout answers the question a decision-maker actually asks: 'Under what conditions does this investment make sense, and how wrong do my assumptions have to be before it doesn't?' Course 2 builds this output structure as the final step of the model, so the DCF is not a standalone calculation but the natural conclusion of a three-statement model that ties out at every level.
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