Skip to content

Mastering Financial Analysis: Sensitivity and Scenario Tables in Excel

Financial modeling can often feel like peering into a black box, especially when trying to understand how different variables impact your investment outcomes. At Cambridge Finance, we believe in demystifying these processes. Today, we’re diving into two crucial techniques for robust financial analysis: Sensitivity Analysis and Scenario Analysis, and how to effectively build these tables in Excel.

What is Sensitivity Analysis?

Imagine you’re evaluating an investment, and you’ve made certain assumptions. But what if those assumptions change? That’s where Sensitivity Analysis comes in. It’s essentially a “what if” exercise. For instance, what if your exit yield (the rate at which you sell an asset) shifts from 4.75% to 5% or 5.25%? How does that impact your ungeared IRR, geared IRR, or exit value?

The core idea is to isolate key variables that have the most significant influence on your investment criteria. While Excel’s data tables make the calculations straightforward, the real challenge lies in identifying which variables are truly impactful. In real estate, for example, exit yields and rental growth are often the most critical drivers of returns due to the substantial residual value of properties.

Why is isolating variables important?

  • Avoid “Heavy” Spreadsheets: Testing too many variables at once can make your Excel file slow and cumbersome. Focus on the most influential ones.
  • Tornado Charts: This visual tool helps you compare the impact of various input changes on your output (like Net Present Value or IRR). By standardizing the change (e.g., a 1% shift for all variables), you can clearly see which factors create the biggest “tornado” of impact. For instance, a 1% change in exit yield can have a far greater impact on your IRR than a 1% change in other variables.

Building One-Way Data Tables in Excel

Let’s walk through how to create a one-way data table using exit yield as our primary variable, and observing its effect on IRR and exit value.

  1. Link Your Outputs: First, link the output cells you want to analyze (e.g., ungeared IRR, geared IRR, gross exit value) to your data table area. For example, if your ungeared IRR is in cell O11, you’d type =O11 in the corresponding cell of your data table.
  2. Structure Your Table: Your input variables (like exit yields) should be structured in a column (or a row). Your outputs will then populate the adjacent rows (or columns).
  3. Highlight the Table: Select the entire range of your data table, including the input variables and the linked output cells.
  4. Access Data Table Tool: Go to Data > What-If Analysis > Data Table.
  5. Specify Input Cell: Since your input variables are in a column, select “Column input cell” and then click on the cell in your model where the exit yield assumption resides (e.g., S16).
  6. Click OK: Excel will then populate your table, showing how your outputs change with each variation of your input variable.

You’ll often observe a logical relationship, such as a higher exit yield leading to a lower IRR and lower exit value. This immediate feedback helps validate your model and assumptions.

Understanding Two-Way Data Tables

While one-way data tables are powerful, sometimes you need to understand the interplay of two variables. For example, what happens if both your exit yield and construction costs change simultaneously?

The process is similar, with a key difference:

  1. One Output: Two-way data tables can only show the impact on one output variable (e.g., geared IRR).
  2. Row and Column Inputs: You’ll have one input variable in a column (e.g., exit yield) and another in a row (e.g., percentage change in hard costs).
  3. Dummy Variable for Percentage Changes: For percentage changes (like construction costs), you’ll need to create a “dummy” variable that links to your actual cost assumption in the model. This allows you to test percentage increases or decreases. For example, if your base hard cost is 431 million, you’d link a cell to 431,000,000 * (1 + [your percentage change cell]).
  4. Data Table Setup: In the Data Table dialog box, you’ll specify both the “Row input cell” (for your construction cost dummy variable) and the “Column input cell” (for your exit yield).

Once generated, you can use conditional formatting (Home > Conditional Formatting > Color Scales) to visually highlight trends, like how a decrease in construction costs and exit yields can lead to a significantly higher IRR.

A Crucial Consideration for Two-Way Tables: Be mindful of the correlation between your variables. Does it make sense for them to increase or decrease together, or does one typically lead to the other in the real world? For example, if you increase construction costs, you might expect a higher-quality building, which could lead to a lower (better) exit yield, rather than an increase. Your analysis only makes sense if the paired variables reflect realistic relationships.

Scenario Analysis: A Word of Caution

While sensitivity analysis focuses on “what if” single variable changes, scenario analysis explores broader outcomes based on a set of assumptions (e.g., “best case,” “worst case,” “base case”). However, a critical piece of advice: Never use the built-in “Scenario Manager” in Excel. It’s cumbersome and inflexible. Instead, focus on building dynamic scenario managers within your models.

Key Takeaways and Productivity Tips

  • Isolate Key Variables: Prioritize the variables that truly drive your investment outcomes.
  • Data Tables are Powerful: These are incredibly efficient tools for productivity in financial modeling.
  • Mind the Pitfalls: Be aware that overly complex data tables can slow down your spreadsheet.
  • Avoid Excel’s Scenario Manager: Opt for dynamic, custom-built scenario tools.
  • Start Simple: Begin with one-variable data tables before moving to two-variable analyses.
  • Understand Variable Relationships: For two-way tables, consider the realistic correlation between your input variables.

Mastering these Excel techniques will transform your financial analysis from a black box into a transparent and powerful tool for making informed investment decisions. Want to dive deeper and learn the entire underwriting process, including investor expectations and economic modeling? Join our courses at Cambridge Finance!