Why Sensitivity Analysis Matters in a DCF
A DCF model without a sensitivity analysis is an incomplete DCF model. Every banker, every analyst, every associate knows this, and yet I still see candidates in interviews who can describe how to build a DCF but can’t explain why sensitivity analysis is essential or how to construct one properly.
Here’s the fundamental problem with a DCF: the output (your valuation) is highly sensitive to a small number of key assumptions, particularly your discount rate (WACC) and your terminal growth rate. A tiny change in WACC (say, from 9% to 10%) can move your implied share price by 20-30%. That means the single-point estimate a DCF produces is almost meaningless without understanding how that value changes across a range of assumptions.
Sensitivity analysis solves this problem by showing you not one valuation but a range of valuations based on different combinations of key inputs. It’s what makes a DCF actually useful for decision-making.
What Variables to Use in Your Sensitivity Table
The most common sensitivity analysis in a DCF uses two variables:
- WACC (or discount rate): Usually shown as the column headers, with values spanning plus or minus 1-2 percentage points around your base case assumption
- Terminal growth rate (or terminal year EBITDA multiple): Usually shown as the row headers, with values spanning a reasonable range around your base case
The result is a matrix, often called a “two-variable data table” in Excel, where each cell shows the implied share price (or enterprise value, or IRR, depending on what you’re modeling) for a given combination of WACC and terminal growth rate.
Other common two-variable combinations include:
- Revenue growth rate vs. EBITDA margin (for an operating sensitivity)
- Entry multiple vs. exit multiple (for an LBO)
- WACC vs. terminal year EBITDA multiple (using an exit multiple approach instead of Gordon Growth)
How to Build a Sensitivity Table in Excel (Step by Step)
Here’s exactly how to build a two-variable data table in Excel:
Step 1: Build Your Base Case DCF
Before building the sensitivity table, you need a fully functioning DCF with a single base case output. Make sure your WACC and terminal growth rate are referenced from dedicated input cells (not hardcoded into formulas).
Step 2: Set Up the Table Structure
Create a grid on your spreadsheet. Put your WACC values across the top row (columns) and your terminal growth rate values down the left column (rows). Leave the top-left cell of the grid empty: this is where Excel’s data table function expects the output formula.
Step 3: Link the Output Formula
In the top-left cell of your sensitivity table (the empty corner cell), enter a formula that references your DCF output, typically your implied share price or equity value. This cell tells Excel what to calculate for each combination of inputs.
Step 4: Run the Data Table
Select the entire table range (including your input values and the output formula cell). Go to Data > What-If Analysis > Data Table. In the dialog box:
- Set “Row input cell” to your WACC input cell (since WACC runs across the columns)
- Set “Column input cell” to your terminal growth rate input cell (since growth rates run down the rows)
Click OK. Excel will populate every cell in the table with the calculated output for that combination of inputs.
Step 5: Conditional Formatting
Apply a color scale to your table, typically a green-to-red gradient where higher values are green and lower values are red. This makes the table much easier to read at a glance, particularly when presenting to a senior banker or in a client deck. Highlight the base case cell (the intersection of your base WACC and base growth rate assumptions) so it stands out.
How to Interpret a Sensitivity Table
Once you’ve built the table, you need to be able to explain what it means, both to yourself and in an interview setting. Here’s what to look for:
The Range of Outputs
Look at the lowest and highest values in the table. This gives you a realistic valuation range, not just a point estimate. In a pitch book or fairness opinion, bankers typically cite this range rather than a single DCF value.
Sensitivity to Each Variable
Compare how much the output changes when you move across a row (varying WACC while holding growth rate constant) versus down a column (varying growth rate while holding WACC constant). For most companies, the valuation is more sensitive to WACC than to terminal growth rate. If you find the opposite, it’s worth understanding why: it may be telling you something about the business’s growth profile.
The Base Case in Context
Where does your base case sit within the table? If it’s in the middle, your assumptions are balanced. If it’s at an extreme corner, you may have overly bullish or conservative assumptions that need to be justified.
Common Mistakes to Avoid
- Using too narrow a range: A sensitivity table that shows WACC from 9.9% to 10.1% is useless. Use a range that reflects genuine uncertainty, typically plus or minus 1-2 percentage points on WACC and similar on growth rate.
- Hardcoding inputs: If your WACC isn’t referenced from a single input cell, the data table won’t work. Always build clean, reference-based models.
- Not including sensitivity analysis at all: In any professional context (pitch books, fairness opinions, client materials), leaving this out signals that your model is incomplete.
- Presenting only the favorable cells: Sophisticated readers will notice if your range suspiciously only shows values above the current trading price. Include the full range honestly.
Sensitivity Analysis in IB Interviews
You should expect interview questions on sensitivity analysis if you claim DCF knowledge. Common questions include:
- “Walk me through how you’d build a sensitivity table in Excel”: answer as described above
- “What two variables would you sensitize in a DCF and why?”: WACC and terminal growth rate, explain why these are the most impactful assumptions
- “If a company’s DCF is highly sensitive to the terminal growth rate, what does that tell you?”: it means the terminal value dominates the valuation, which is common for high-growth companies and means your long-run growth assumptions matter enormously
Our technical cheatsheet covers this and every other core modeling concept you’ll need for IB interviews. And if you want to go deeper on financial modeling, check out our free resources page.
Want Personalized Investment Banking Coaching?
Wall Street Mastermind has helped thousands of students land offers at Goldman Sachs, Morgan Stanley, JPMorgan, and every top bank. If you want personalized coaching to break into IB, apply here to learn more about how we can help you.
