Sensitivity analysis entails identifying variables in data whose uncertainty drives the largest impact, so you can focus objectively on what’s important. And this helps you save time, reduce frustration and increase efficiency.
The methodology can help you focus on the right factors for further discussion by the top management. The Tornado Diagram uniquely identifies variables that are high in both their impact (or leverage) and uncertainty.
Most of us waste a lot of time and energy focusing on the wrong thing. And this is because the two most common approaches to managing uncertainty don’t give us the complete picture.
In sensitivity analysis, each factor is varied by +/- 10%, after which, you can easily calculate the impact of this standardized range on the output. The Tornado Chart solves this problem by requiring that each factor’s input range incorporate the best evidence at hand.
The chart is incredibly easy to read and interpret. Besides, you can easily integrate it into your data storytelling to cement credibility.
Google Sheets does not support sensitivity analysis-based charts.
You actually don’t have to do away with the Google Sheets 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 diving into the how-to guide, we’ll address the following question: what is sensitivity analysis?
Definition: 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 studies how various sources of uncertainty in a mathematical model contribute to its overall uncertainty. This technique is used within specific boundaries that depend on one or more input variables.
Use sensitivity analysis-based charts to extract relationship insights, which you cannot see using tables and visualization designs, such as Bar Charts.
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. And it’s a reliable way of predicting the outcome of a decision given a certain range of variables.
By creating a given set of variables, you 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.
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.
The diagram above shows that uncertainty in quality and legal risks has a significant impact on the outcomes of the project.
In the following section, we’ll address how to create a Tornado Chart for Sensitive Analysis in Google Sheets.
Google Sheets is a trusted data visualization tool because it’s familiar and has been there for years. But the spreadsheet application lacks ready-made Sensitivity-based Graphs, such as Tornado Charts.
We’re not advocating you ditch Google Sheets 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 you can easily install in your Google Sheets with ready-made sensitivity analysis-oriented charts. The tool is called ChartExpo.
So, what is ChartExpo?
ChartExpo is an incredibly intuitive add-on you can easily install in your Google Sheets without watching hours of YouTube tutorials.
With many ready-to-go visualizations, the sensitivity analysis-based chart creator turns your raw data into easy-to-digest charts that tell the data stories in real time.
In the coming section, we’ll take you through how to install and use ChartExpo into your Google Sheets for conducting sensitivity analysis.
You don’t want to miss this!
We’ll visualize the data below using a Tornado Chart.
You don’t want to miss this.
| Risk | Low Project Impact | High Project Impact |
| Quality Risk | 1742 | 2000 |
| Legal Risk | 1300 | 1700 |
| Technical Risk | 1468 | 80 |
| Budget Risk | 1426 | 1402 |
Install the ChartExpo add-in for Google Sheets from this link.
In the coming section, we’ll address the common applications of sensitivity analysis. You don’t want to miss this.
Sensitivity analysis can help you make strategic decisions by moving out of analysis paralysis and toward clarity on what really matters.
Besides, you can easily shift from an obsession with risk to a holistic engagement with both risk and opportunity. Also, you can easily move beyond mere mathematical sensitivity or intuition to a holistic engagement with both.
Check out more applications of the methodology.
In a business context, you can use the methodology to improve decisions based on certain calculations or modeling.
For instance, you can easily identify the inputs with a significant impact on the expected ROI. Sensitivity analysis can also be used to allocate assets and resources.
The disciplines of physics and chemistry employ sensitivity analysis to evaluate results and conclusions.
And this is because it has proven particularly useful in the evaluation and adjustment of kinetic models that involve using differential equations.
It is standard practice in engineering to use models to test the design of structures.
Sensitivity analysis helps engineers create more reliable, robust designs by assessing points of uncertainty or wide variations in possible inputs and their corresponding effects on the viability of the model. Refinement of models can significantly impact the accuracy of evaluations of bridges’ stress ability or tunneling risks.
In the following section, we’ll cover examples of sensitivity analysis scenarios.
Here are two hypothetical examples when sensitivity analysis could be used:
Peter sells backpacks at a kiosk in a mall.
He knows the back-to-school rush will start in August and intends to determine whether the increase in customer traffic at the mall will increase his sales revenue.
And, if so, by how much.
The average price of a backpack is $40. Last year, during the back-to-school rush, he sold 250 backpacks, resulting in $10,000 in sales. After using spreadsheet software, he discovered that when customer traffic at the mall increased by 20%, an 14% increase in his sales ensued.
Now that Peter knows, he can easily predict his sales revenue. If the mall customer traffic increases to 40%, his sales respond with a 28% increase.
So, mall traffic is one of the key uncertainties that drive the largest impact on Peter’s revenues.
Jane is a sales manager. She intends to gain a better understanding of how holiday shoppers’ traffic influences her sales targets.
Insights into past data suggest that holiday sales are a function of transaction volume and price. The aforementioned implies that when holiday shoppers increase by 10%, sales increase by 5%.
Jane can build a financial model and use sensitivity analysis’ “what if” statements.
Sensitivity analysis determines how different values of an independent variable affect a particular dependent variable under a given set of assumptions.
Besides, it examines how various sources of uncertainty in a mathematical model contribute to the model’s overall uncertainty.
The methodology can be categorized into two, namely local and
global sensitivity analyses.
The term ‘local’ indicates that the derivatives are taken at a single point. This method is apt for simple cost functions.
Global sensitivity analysis is implemented using Monte Carlo techniques. Also, it uses global samples to explore data variables.
Sensitivity analysis entails identifying variables in data whose uncertainty drives the largest impact, so you can focus objectively on what’s important.
And this helps you save time, reduce frustration and increase efficiency.
This insight gets us focused on the right factors for further discussion. The Tornado Diagram uniquely identifies variables that are high in both their impact (or leverage) and uncertainty.
Most of us waste a lot of time and energy focusing on the wrong thing. And this is because the two most common approaches to managing uncertainty don’t give us the complete picture.
The Tornado Chart solves this problem by requiring that each factor’s input range incorporate the best evidence at hand.
The chart is incredibly easy to read and interpret. Besides, you can easily integrate it into your data storytelling to cement credibility.
Google Sheets does not support sensitivity analysis-based charts.
So, what’s the solution?
We recommend you install third-party apps, such as ChartExpo, to access ready-to-use Tornado Graphs (one of the sensitivity analysis-based charts).
ChartExpo is an add-on for Google Sheets that’s loaded with insightful and ready-to-go Tornado Charts for easy analysis. You don’t need programming or coding skills to use ChartExpo.
Sign up for a 7-day free trial today to access ready-made Tornado Charts that are easy to interpret and visually appealing to your target audience.
How much did you enjoy this article?
Do you know the best way to ask your audience for feedback? Google Forms’ Likert Scale questions offer deeper, more valuable insights into your customers.
What is a Google forms Survey? Learn the importance and different types of Google Forms Survey Examples and how to analyze those using Google Sheets.
What is a net promoter score survey? Click to learn about benefits and how to create and analyze NPS survey questions in Google Sheets.
