How to Do Sensitivity Analysis in Excel? Easy Steps

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:

• How to do sensitivity analysis in Excel?
• We’ll address the following questions:
  • What is sensitivity analysis?
  • When should you use sensitivity analysis?
• Advantages of sensitivity analysis.
• Also, we’ll suggest the best add-in to install in your Excel to access ready-made sensitivity analysis charts.

How to Do Sensitivity Analysis in Excel?

Before jumping right into the how-to guide, we’ll address the following question: what is sensitivity analysis?

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?

When Should You Use Sensitivity Analysis Charts?

• Identification of correlations and associations

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.

• Identification of data patterns and trends

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.

• Display the relationship between two variables

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.

• Make reliable forecasts

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:

• Company earnings
• Number of shares outstanding
• Debt-to-equity ratios (D/E)
• Number of competitors in the industry

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.

Advantages of Sensitivity Analysis

• Better decision making: Sensitivity analysis presents decision-makers with a range of outcomes to help them make reliable and data-backed decisions.
• More reliable predictions: The methodology can help you forecast the performance of the metrics of care most about.
• Highlights areas for improvement: Sensitivity analysis can help you identify key areas to make improvements for more profits.
• Increases credibility: The analysis strategy adds credibility to financial models by testing them across a wide set of key data points.

Weaknesses of Sensitive Analysis

• The methodology assumes that key metrics change independently. For instance, material prices change independently of other variables.
• It only identifies how far a variable can change. So, it doesn’t look at the probability of such a change.
• It provides information on the basis for making decisions. But the methodology does not point to the correct decision directly.
• Sensitivity analysis considers each variable individually and determines the outcome.

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.

How to Do Sensitivity Analysis in Excel?

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.

• ChartExpo is cloud-hosted, which makes it extremely light. You have a 100% guarantee that your Excel won’t be slowed down.
• You can export your insightful, easy-to-read, and intuitive charts in JPEG, PDF, SVG, and PNG formats.
• ChartExpo add-in is only $10 a month after the end of the trial period.
• The app has an in-built library with over a lot easy-to-customize and ready-made charts for your data stories.

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!

Example

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

• To install ChartExpo into your Excel, click this link.
• Open the worksheet and click the Insert button to access the My Apps option.

• Click the My Apps button and then See All button to view ChartExpo, among other add-ins.

• Select ChartExpo add-in and click the Insert button.
• Once the interface below loads, click the Search box and type your preferred chart.

• In this case, type “Tornado Chart” in the Search Box as shown.

• Select the sheet holding your data and click the Create Chart from Selection button, as shown above.
• Check out the final chart below.

FAQs:

What is the sensitivity analysis formula?

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

What is a sensitivity analysis example?

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%.

What is a Tornado Chart Sensitivity Analysis?

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.

What is the most widely used method of sensitivity analysis?

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.

Wrap Up

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.