ab-logo

Code. Models. Analysis. Decisions.


Sensitivity (What-if) Analysis with Excel

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007, Excel 2016 for Mac

Two Variable Data Table

Data tables are a built-in tool in Excel that allow you to "stress test" spreadsheet model outputs by automatically recalculating values of the formula for an output value under various assumptions. Data Tables allow you test "what-if" scenarios independently of the base case model. For example you might want to test the impact of changing price or cost on profit.

Calculate Multiple Results with Data Table

As the name suggests a two variable or two way data tables allow you the change two inputs and examine the impact on outcomes. Results can be graphed or have conditional formatting applied for easy interpretation.

You can download the spreadsheet used in this tutorial here