What a Sensitivity Table Is and Why It Matters
A sensitivity table is a what-if analysis tool that shows how changes in one or two input variables affect a financial output—such as profit, earnings per share, revenue, or loan payments. Instead of running individual scenarios manually, you can see the full range of possible outcomes in a single view.
For investors and real estate analysts, a sensitivity table is essential. It lets you stress-test your assumptions and understand which variables have the greatest impact on returns. If you’re evaluating a property investment, for example, a sensitivity table can show you how variations in rental income and interest rates jointly affect net operating income. This clarity helps you make decisions with confidence, even when the future is uncertain.
How to Create a Sensitivity Table in Excel
Building a sensitivity table requires three foundational steps: setting up your model, identifying your input cells, and creating your output formula.
Set Up Your Model, Input Cells, and Output Formula
Start by creating a spreadsheet with the financial model you want to analyze. This might include purchase price, operating costs, lease rates, or any other variables relevant to your analysis. Next, identify which input you want to test—your variable—and which cell contains your final result. For instance, if you’re analyzing real estate returns, your output might be cash flow, and your inputs might be occupancy rate or expense growth.
Use What-If Analysis > Data Table
Once your model is ready, Excel’s Data Table feature automates the sensitivity analysis. Navigate to the Data menu, select What-If Analysis, and choose Data Table. For a one-way sensitivity table, you’ll specify your input cell and output cell. For a two-way table, you’ll specify two input cells. Excel then calculates your output for each combination of input values you’ve listed, creating a matrix of results instantly.
Interpreting One-Way vs. Two-Way Sensitivity Tables
A one-way sensitivity table tests one variable in isolation. You list values for a single input down one column and see how each change affects your result. This approach is straightforward and useful when you want to understand the direct impact of a single assumption—such as how changes in discount rate affect investment value.
A two-way sensitivity table tests two variables simultaneously. Your inputs are arranged in rows and columns, and each cell in the table shows the output for that combination. Two-way tables are more powerful for real estate and investment analysis because real outcomes depend on multiple factors working together. For example, you might examine how both rental growth and cap rate changes affect property returns.
Choose a one-way table when you’re isolating risk in a single variable. Use a two-way table when two variables are interdependent or when you need to see the interaction between them.
Best Practices, Limits, and Common Errors
Sensitivity tables work best when your input ranges are realistic and your formula is straightforward. Format your table with clear headers and consistent number formatting so results are easy to scan. Use conditional formatting (such as color scales) to highlight which outcomes are most favorable—this speeds up interpretation.
Recalculation can slow down large spreadsheets. If your model is complex, set Excel to manual recalculation before building your table, then press F9 when you’re ready to update results. A common mistake is forgetting to anchor your formula with absolute references (using $ signs), which can cause the data table to calculate incorrectly. Another frequent error is testing input ranges that don’t reflect realistic assumptions—too narrow, and you miss important risks; too wide, and you obscure the most likely outcomes.
Remember that Excel sensitivity tables are limited to one or two variables. If you need to test more than two inputs at once, consider scenario analysis or Monte Carlo simulation tools instead.
