Sensitivity analysis entails probing the influence of independent variables on the dependent ones in data.
You can use the technique to easily predict outcomes when an analysis is performed under certain conditions. As an investor, you can easily evaluate all the factors that may negatively affect the growth of your investments using this methodology.
So, how can you establish the relationship between dependent and independent factors in your data?
This is where sensitivity analysis-based charts, such as Tornado Graph, come in.
The charts are incredibly easy to read and interpret. Besides, you can easily integrate them into your data storytelling to cement credibility.
Excel does not support sensitivity analysis-based charts.
You actually don’t have to do away with the Excel spreadsheet. Supercharge its usability by installing a particular third-party application into your spreadsheet to access ready-to-use and visually appealing Tornado Charts.
In this blog, you’ll learn the following:
Before jumping right into the how-to guide, we’ll address the following question: what is sensitivity analysis?
Sensitivity analysis determines how different values of an independent variable affect a particular dependent variable under a given set of assumptions.
In other words, sensitivity analysis study how various sources of uncertainty in a mathematical model contribute to the model’s overall uncertainty. This technique is used within specific boundaries that depend on one or more input variables.
Sensitivity analysis is used in the business world and in the field of economics. Also, it’s commonly used by financial analysts and economists and is also known as a what-if analysis.
Sensitivity analysis entails probing the relationship between key variables in data
Use sensitivity analysis-based charts to extract relationship insights you cannot see using tables and visualization designs, such as Bar Chart.
Seasoned visualization experts use sensitivity analysis mainly to test hypotheses. And this occurs during the initial stages of data visualization. If you want to extract micro insights in your bulky data, give this chart a try.
The financial model determines how key metrics are affected based on changes in other factors known as input variables. This model is also referred to as what-if or simulation analysis. It is a way to predict the outcome of a decision given a certain range of variables.
By creating a given set of variables, an analyst can determine how changes in one variable affect the outcome.
Both the target and input—or independent and dependent—variables are fully analyzed when sensitivity analysis is conducted. The person doing the analysis looks at how the variables move as well as how the target is affected by the input variable.
How to do sensitivity analysis in Excel should never be an Achilles heel for you. Keep reading to discover more.
Sometimes, there’re relationships between critical metrics in your data. And understanding these relationships can help you uncover actionable insights.
For instance, if you’re a human resource professional, the relationship between training hours and employee productivity is one of the issues you keep track of closely.
The relationship between the number of people working in a shift and the average answer time is a significant point of interest for many call center managers.
In the coming section, we’ll address the following question: when should you use sensitivity analysis charts?
You can use this insightful chart to uncover hidden correlational relationships that exist in your raw business data.
Interpreting sensitivity analysis charts is incredibly easy.
The key to interpreting this chart is to always remember the following: independent variables (metrics) are found on the horizontal axis (x-axis). And, the dependent variables are situated on the vertical axis (y-axis) in a Cartesian plane.
Use a sensitivity analysis chart to identify the general trend of your key variables in your raw data.
Data points in this chart are grouped together based on how close their values are, which makes it easier to identify outliers. You don’t want to base your business decisions on outliers because they can be misleading.
Interestingly, the nature of the correlations can also be estimated based on a specified confidence level.
Sensitivity analysis charts are widely used by seasoned data visualization experts to display the causal relationships between two variables.
The relationship between variables can be positive or negative.
Sensitivity analysis can be used to help make predictions about the share prices of public companies.
Some of the variables that affect stock prices include:
The analysis can be refined about future stock prices by making different assumptions or adding different variables.
This model can also be used to determine the effect that changes in interest rates have on bond prices. In this case, the interest rates are the independent variable, while bond prices are the dependent variable.
How to do sensitivity analysis in Excel should never be time-consuming for you. In the coming section, we’ll address the advantages of sensitivity analysis charts.
In the real world, all variables are related to each other.
For example, both inflation and market interest rates affect bond prices. Sensitivity analysis will consider how much a change in inflation will affect the bond price. And how a change in market interest rate will affect bond prices.
But it won’t consider how a change in inflation will affect the market interest rate or vice versa. This is actually an incomplete analysis.
We’re now in the heart of the blog: How to do sensitivity analysis in Excel. You don’t want to miss this.
Excel is a trusted data visualization tool because it’s familiar and has been there for decades.
But the spreadsheet application lacks ready-made sensitivity analysis-based charts, such as Tornado Graph.
We understand switching tools is not an easy task.
This is why we’re not advocating you ditch Excel in favor of other expensive data visualization tools.
There’s an easy-to-use and amazingly affordable visualization tool that comes as an add-in for excel which you can easily install in your app to access ready-made sensitivity analysis-based charts, such as Tornado Graph. The tool is called ChartExpo.
How to do sensitivity analysis in Excel should never overwhelm you. Keep reading to discover more.
So, what is ChartExpo?
ChartExpo is an incredibly intuitive add-in you can easily install in your Excel.
With many ready-to-go visualizations, the sensitivity analysis charts generator turns your complex, raw data into compelling, easy-to-digest, visual renderings that tell the performance review stories in real-time.
In the coming section, we’ll take you through how to do sensitivity analysis in Excel using the ChartExpo add-in.
You don’t want to miss this!
This section will use a Tornado Chart (one of the tested and recommended charts for sensitivity analysis).
| Risk | Low Project Impact |
High Project Impact |
| Quality Risk | 1742 | 2000 |
| Legal Risk | 1300 | 1700 |
| Technical Risk | 1468 | 80 |
| Budget Risk | 1426 | 1402 |
Mathematically, the sensitivity analysis formula that represents the dependent output can be written as follows: f(x) = y.
X is the independent variable (input). Y is the dependent variable (output). And it determines how different values of an independent variable affect a particular dependent variable under a given set of assumptions
Sue is a sales manager who wants to understand the impact of customer traffic on total sales. The price of a widget is $1,000, and Sue sold 100 last year for total sales of $100,000.
The data above is sufficient for her to build a sensitivity analysis. It can tell her what happens to sales if customer traffic increases by 10%, 50%, or 100%.
Tornado Chart sensitivity analysis determines how key variables are affected based on changes in other variables known as input variables.
This model is also referred to as what-if or simulation analysis. It is a way to predict the outcome of a decision given a certain range of variables.
Derivative-based approaches are the most common sensitivity analysis method.
To compute the derivative, the model inputs are varied within a small range around a nominal value. Use the methodology to understand the impact a range of variables has on a given outcome.
Sensitivity analysis entails probing the influence of independent factors on dependent factors in data. You can use the technique to easily predict outcomes when the analysis is performed under certain conditions.
So, how can you establish the relationship between dependent and independent factors in your data?
This is where sensitivity analysis-based charts, such as Tornado Graph, come in.
The charts are incredibly easy to read and interpret. Besides, you can easily integrate them into your data storytelling to cement credibility.
Excel does not support sensitivity analysis-based charts. You actually don’t have to do away with the Excel spreadsheet.
So, what’s the solution?
We recommend you install third-party apps, such as ChartExpo, to access ready-to-use sensitivity analysis-based charts.
ChartExpo is an add-in for Excel that’s loaded with insightful and ready-to-go sensitivity analysis-based charts, such as Tornado Graph. You don’t need programming or coding skills to use ChartExpo.
How to do sensitivity analysis in Excel should never stress you.
Sign up for a 7-day free trial today to access ready-made sensitivity analysis-based charts that are easy to interpret and visually appealing to your target audience.
How much did you enjoy this article?
What is a Marketing Report? Learn about Types and how to analyze Marketing Data using Marketing Report Examples.
Turn Excel into your leading Likert Scale tool. Use these ready-to-use Likert Scale Templates to generate rating scales and evaluate your Survey Data.
What is a Ranking Chart? Learn about importance, benefits and how to visualize ranking data using ranking chart maker in Excel.
