- What is sensitivity analysis?
- Uses of sensitivity analysis
- How to perform sensitivity analysis
- Sensitivity analysis using bivariate date
- Sensitivity analysis using fixed outcomes
- Sensitivity analysis with more than two inputs
What is sensitivity analysis?
Sensitivity analysis is the observation of how uncertainty in the output of a system can be divided into individual sources of uncertainty in the system’s input.
In summary, it is a study of the relationship between the inputs and outputs of a system. In this guide, we will consider how to perform sensitivity analysis in Google Sheets by considering how different independent variables affect a dependent variable — our outcome.
Sensitivity analysis often involves recalculating outcomes under different assumptions (inputs) to determine the impact of a specific input variable. This knowledge is vital for many reasons.
Benefits of sensitivity analysis
In several fields, knowing how input variables to a model translate into the final output can be very helpful for obtaining optimal results.
Through sensitivity analysis, we can determine what variables in a set of input data are responsible for the most impact on an outcome. This outcome is said to be sensitive to those specific variables because it is affected by changes in them.
To illustrate this, let’s assume a marketing company is trying to decide how to reach a particular sales target for a product. It knows that it can tweak several factors such as customer growth and product price which are all inputs into a system that returns profit in the form of revenue — an outcome.
To determine the most effective way to combine these inputs, it would have to carry out a sensitivity analysis. It could at the end of this analysis learn that Ads/customer traffic affect revenue more. And It can decide on focusing their strategies on that.
The general benefits of sensitivity analysis include:
- Improvement in decision-making: Stakeholders and decision-makers can make better business decisions based on the insights from sensitivity analysis.
- Indicates areas for improvement: Sensitivity analysis helps decision-makers identify where to make future improvements.
- Reliable predictions: Through sensitivity analysis we can carry out an in-depth study of variables, resulting in more reliable business models based on our predictions.
How to perform sensitivity analysis in Google Sheets
There are no inbuilt functions or features in Google Sheets for doing sensitivity analysis. So for the most part, we will be using add-ons that present this functionality to us.
There are multiple ways to perform sensitivity analysis in Google Sheets, they include :
- Using data tables (single and bivariate data)
- Using fixed outcomes
- Using charts or simulation-based analysis (more than 2 changing inputs)
We will look at each method with appropriate examples in the succeeding sessions. Let’s begin.
You can copy the spreadsheet used in this tutorial here – copy spreadsheet.
Example 1: Perform sensitivity analysis in Google Sheets using data tables (bivariate data)
This is the simplest kind of sensitivity analysis. It involves varying an input variable and observing changes in the output. All we need to do this sort of analysis are formulas that calculate our output variables. This sensitivity analysis relies solely on historical data.
Let’s continue with the previous example.
Our marketing agency is aware of the various factors that result in increased sales and profit. It, however, wants to determine how changing the customer growth and sales volume will increase revenue.
There are 3 input/independent variables here – customer growth, item price, and sales volume.
The dependent variable is the revenue.
- Let us assume a 10% increase in customer growth
- We will keep the item price fixed
- Using the formula =I6*(1+(B6*H6)) we calculate the sales volume for a 10 percent increase in customer growth and so on.
- Revenue = sales volume x item price.
Below is our initial table:
By entering these values with the given formula all we need to do is calculate subsequent increases in our input variables. Google Sheets makes that quite easy.
In the table below we can see how changes in customer growth and sales volume impact our revenue.
With this model, we can make predictions on the required values of input needed to obtain an outcome.
Example 2: Perform sensitivity analysis in Google Sheets using fixed outcomes (Goal seek in Google Sheets)
This is a slightly different approach to sensitivity analysis as the goal is to obtain the exact value of an input variable that is needed to achieve a specific outcome.
To perform this kind of sensitivity analysis we will use the Goal seek add-on available in Google sheets. This official add-on is built to quickly find the exact values needed to achieve a goal.
To install the add-on:
- Click on Extensions from the tab
- Select Add-ons ➡ Get Add-ons. This will open the marketplace.
- Search for Goal Seek and install it (you will be required to grant certain permissions).
Now, we are going to provide the data needed for Goal seek in Google Sheets
How to use Goal seek in Google Sheets
Our goal is to reach $500,000 as total revenue. We want to know how much sales volume is needed to reach this.
The add-on simply needs 3 fields:
- A cell value
- The target value
- Cell to change to reach the target value
We will provide total revenue (cell E17) as Cell value, 500000 as the target value for this total revenue, and lastly, sales volume (cell D15) as the cell to change until the target value is reached.
- Click on Solve after providing the values and wait for Goal Seek to compute the value.
On completion, a pop-up will appear indicating success. The input and output variables will reflect the appropriate data.
The needed sales volume to reach our target is 1,325.
Goal Seek in Google Sheets is quite flexible and provides us with the ability to analyze different variables, for instance, we could decide to find out at what specific item price we would be able to hit $5,000 in revenue.
By following the steps above we can set up Goal Seek with input as Item price, the output as revenue, and target value as $5,000.
Below is the answer.
Example 3: Perform sensitivity analysis in Google Sheets using more than 2 inputs (Causal Scenario)
All we have treated so far are single and two varying inputs. These are easy to calculate using data tables and formulas.
However, it becomes really difficult to track changes in more than two variables. To do this, we will need another approach to the process — simulation-based sensitivity analysis.
The goal is to vary as many inputs as needed and to track changes in them and how they affect the outcome. This information can be returned in the form of a graph with a mean.
To perform simulation-based analysis in Google sheets we will use another add-on: Causal Scenarios.
- Click on extensions from the tab
- Select Add-ons ➡ Get Add-ons. This will open the marketplace.
- Search for Causal Scenarios
- install the add-on (you will be required to grant certain permissions).
The table below has some values for Revenue. We want to use Casual Scenario to simulate how all 3 inputs: customer growth, item price, and sales volume vary accordingly.
- Open the causal scenario add-on
- Provide the 3 inputs and select a range of 5 to 10.
- Provide the output cell range and press Run simulation.
The addon will run a simulation and present this in the form of a graph
Conclusion
Sensitivity analysis is a necessary tool for businesses. Using Google Sheets you can employ several techniques to determine the relationship between the inputs and outputs of a financial model.
Frequently Asked Questions
Can Google Sheets do a what-if analysis?
Yes. What-if analysis is the same as a sensitivity analysis in most cases. Though, unlike Excel, Google Sheets doesn’t have built-in data tables. However, you can perform sensitivity analysis in Google Sheets for single and multiple varying inputs using add-ons.
What are the two main types of sensitivity analysis?
Broadly speaking, the two major classifications of sensitivity analysis are local and global sensitivity analysis.
In local sensitivity analysis, the analyst studies the relationship between the outcome and a single independent variable. While in global sensitivity analysis, the analyst considers all the independent variables influencing the outcome.
See Also
Check out other insightful articles on our blog:
https://blog.tryamigo.com/correl-function-in-google-sheets/
How to Perform Linear Regression in Google Sheet – Amigo | Blog
https://blog.tryamigo.com/find-the-line-of-best-fit-in-google-sheets/