2026-11-04 · AI-drafted · Finance · Modeling · Decision-Making · 6 min read
Build the Scenario Engine That Makes Your Model Tell the Truth
A model with a single output is a guess dressed up in spreadsheet clothing. The number that matters is never the base case — it is the distance between your downside and your upside, and what has to be true for each to occur. Building a scenario engine forces you to make your assumptions explicit, test them against each other, and hand a decision-maker something they can actually use. That discipline is what separates a model that survives a board meeting from one that does not.
Why One Output Is the Wrong Answer
Every model starts with a base case. The base case is useful — it anchors the arithmetic and gives you a reference point. But the moment you present a single number as the answer, you have misrepresented the problem. Revenue projections carry uncertainty. Cost structures shift with volume. Exchange rates move. A single output hides all of that uncertainty behind a false precision.
Consider a German manufacturer reporting in EUR under IFRS. Its base case projects EUR 120m in revenue, a 38% gross margin, and EUR 14m in free cash flow. Those numbers look clean. But if raw material costs rise 8% — plausible in any commodity cycle — gross margin compresses to roughly 33%, and free cash flow drops to EUR 7m. That is a 50% swing on one input. A board that sees only EUR 14m has no idea that swing exists.
The scenario engine's job is to make that swing visible before the decision is made, not after. It does not eliminate uncertainty. It quantifies it — and quantified uncertainty is something a decision-maker can work with.
Architecture: Keep Scenarios Out of Your Formulas
The most common modeling error is embedding scenario logic inside formulas. You see it everywhere: an IF statement in the revenue line that checks a cell labeled 'scenario_flag' and returns one of three hard-coded numbers. That approach breaks the moment you add a fourth scenario, and it makes auditing nearly impossible. The fix is architectural, not formulaic.
Structure the workbook so that every assumption lives in a dedicated Inputs sheet — one row per assumption, one column per scenario. Label the columns Base, Downside, and Upside. Then use a single Scenario Selector cell, typically a dropdown validated against those labels, to drive a CHOOSE or INDEX function that pulls the active column into your model's live inputs. Every formula downstream reads from the live inputs, never from the scenario table directly.
This architecture means you can add a fourth scenario — say, a Stress case for a credit committee — by adding one column to the Inputs sheet. Nothing else changes. The model remains auditable because every number traces back to a single source of truth, and the scenario selector is the only switch you ever flip.
Building the Scenario Table: A Worked Example
Take an Indian IT services firm reporting in INR. Base case: INR 8,000m in revenue, 22% EBIT margin, 14% effective tax rate, and a USD/INR rate of 83. The firm bills clients in USD and pays staff in INR, so the exchange rate is a first-order driver. Build three scenarios around two key inputs: revenue growth and the USD/INR rate.
In the Downside, revenue grows 4% instead of 12%, and the rupee strengthens to 79 per dollar — compressing realized revenue in INR terms. EBIT margin holds at 22%, but absolute EBIT falls from INR 1,760m to roughly INR 1,030m. Free cash flow follows. In the Upside, growth runs at 18% and the rate weakens to 87, pushing EBIT to INR 2,140m. The range — INR 1,030m to INR 2,140m — is the real answer. The base case of INR 1,760m is simply the midpoint.
Document the assumption behind each scenario in a plain-language cell comment or a dedicated Assumptions Log sheet. 'Downside: client budget freezes in North America + INR appreciation' is more useful than a number with no context. Decision-makers read the context first.
Sensitivity Tables: One Variable at a Time
A scenario engine tests coherent combinations of assumptions. A sensitivity table tests one variable across a range while holding everything else fixed. Both tools are necessary, and they answer different questions. The scenario engine answers: what does the world look like if conditions shift together in a plausible direction? The sensitivity table answers: which single assumption moves the output the most?
Excel's Data Table function — found under What-If Analysis — builds a one-variable or two-variable sensitivity table without macros. For a Brazilian retailer reporting in BRL, you might build a two-variable table with EBITDA margin on one axis (ranging from 9% to 15% in 1-point steps) and revenue growth on the other (ranging from 5% to 20% in 5-point steps). The output cell is free cash flow. The table populates automatically and shows you the full matrix in seconds.
The discipline is in reading the table correctly. Look for the cells where the output crosses a critical threshold — say, the minimum free cash flow needed to service debt. That boundary is where the model is telling you something important. A Brazilian retailer with BRL 200m in revenue and BRL 18m in annual debt service needs to know exactly which combinations of margin and growth keep it solvent. The sensitivity table draws that line.
Connecting Scenarios to the DCF
A discounted cash flow valuation — the DCF — is only as honest as the free cash flow projections feeding it. If those projections come from a single base case, the resulting valuation is a single number, and a single number implies a certainty that no analyst actually has. The right approach is to run the DCF under each scenario and present a valuation range.
Continuing the German manufacturer example: under the base case, free cash flow runs EUR 14m in year one, growing at 6% annually for five years, with a terminal growth rate of 2% and a discount rate of 9%. That produces an enterprise value of roughly EUR 175m. Under the downside — EUR 7m in year one, 3% growth, same terminal rate and discount rate — enterprise value falls to approximately EUR 95m. The range, EUR 95m to EUR 215m in the upside, is the honest answer to the question 'what is this business worth?'
Present the range with the assumptions that drive each endpoint. A decision-maker who sees EUR 175m and nothing else will anchor to that number. A decision-maker who sees EUR 95m to EUR 215m, with the key assumptions labeled, can ask the right question: which assumptions am I most confident in, and which ones should I stress-test before I commit?
The Output Page: What You Actually Hand to a Decision-Maker
The output page is the last sheet in the workbook and the first thing a decision-maker sees. It should show three things: the scenario selector, the key outputs under each scenario side by side, and a plain-language summary of what drives the difference between them. Nothing else belongs on that page.
Format the outputs as a compact table — revenue, EBIT, free cash flow, enterprise value — with one column per scenario. Use conditional formatting to highlight the downside column in a muted red and the upside in a muted green. Keep the color subtle; the numbers carry the argument. Below the table, write two or three sentences in plain language: 'The downside assumes X. The upside assumes Y. The base case sits between them because Z.' That summary forces you to articulate the logic, and it tells the reader exactly where to push back.
Course 2 builds this entire architecture from the ground up — inputs, schedules, tie-outs, scenarios, and the DCF — so that every output you produce is traceable, auditable, and honest about what it does and does not know. The scenario engine is not a feature you add at the end. It is the reason the model exists.
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