What is Sensitivity Analysis?

Sensitivity analysis, also sometimes known as “what-if” analysis, is a technique commonly used by business and financial analysts to explore the models they create.   It shows how changes to the assumptions built into a model (the model’s “inputs”) affect the results (the model’s “outputs”).

With sensitivity analysis, you can (among other things)

• Understand which factors have the most influence on your model.
• Simplify models by determining parts that have no influence on the bottom line.
• Identify potential risk, and hidden opportunities.
• Analyze possible future scenarios.
• Stress-test a model under extreme conditions.
• Understand the dynamics of a complex model.
• Quickly communicate the behavior of your model to others.

It’s important to note, while most commonly used in financial settings, sensitivity analysis is not limited to that field.  It also is widely applicable in science, engineering, and statistics.

Rudimentary Sensitivity Analysis in Excel

Microsoft Excel is a great place to build models.  However, by itself it is not very good at doing sensitivity analysis.

You can, of course, perform some rudimentary sensitivity analysis using Excel.  First and most obvious, you can simply type in different values into your input cell and watch what happens to the output cells you care about.   This has some obvious disadvantages:

• If you have many inputs and outputs, or a large number of testing values, it quickly becomes unwieldy and very hard to do methodically.
• You won’t obtain any graphical results.
• If you make changes to your model’s structure and want to see how that changes the sensitivity analysis, you’ll need to repeat everything exactly that you previously did.
• You need to be careful not to accidentally alter your model permanently.

It’s clear that manual sensitivity analysis only works for the most basic of situations.

Excel also provides a “Data Table” feature which provides some limited sensitivity analysis capabilities.   This feature can be accessed from the What-If Analysis group of the Data tab of the Excel ribbon.  Excel Data Tables have some serious drawbacks, however, and for most situations we don’t recommend you use them.  That said, they have one distinct advantage: they are integrated with the Excel calculation engine, and so can update automatically as you change your model. This itself, can be a double-edge sword since very large data tables can slow Excel down significantly.

Sensitivity Explorer

Sensitivity Explorer is an add-in tool that lets you quickly perform sophisticated sensitivity analyses directly in Excel.

It provides a simple interface for identifying your model’s input and outputs.  See the article Sensitivity Explorer Input and Outputs for more information.

It provides an array of analyses you can perform:

• Basic Analysis
• Input Comparison
• Output Comparison
• Two-Way Analysis
• Time Series Analysis

See the article Sensitivity Explorer Analyses Overview for more information.

If you repeatedly perform the same analysis, you can save it to your workbook.  See the article Saved Analyses for more information.

Each of these analyses generate both graphical and tabular results, which can be customized.  See the article Customizing Graphs and Tables.

Sensitivity Explorer allows you to generate reports in PDF, Microsoft Excel, and Microsoft Word formats.  See the article Reports Overview for more information.

Sensitivity Explorer also simplifies and extends Excel’s built-in Data Tables.  See the article Data Table Overview for more information.