What is sensitivity analysis in Excel?
Sensitivity analysis in Excel is a powerful technique that enables finance professionals to understand how variations in input values impact their financial models and forecasts.
In its simplest form, sensitivity analysis is a financial model that measures the effect of input variables on target variables. This not only aids in risk management but also enhances decision-making and strategic planning.
You can think of it as “fact-checking” specific situations.
Finance and FP&A teams might use sensitivity analysis in Excel to:
- Determine the effect of a company’s net working capital on its profit margin.
- Identify which actions have the greatest ROI.
- Make predictions on the share prices of public companies.
- Understand the impact of website traffic on sales numbers.
The analysis is performed in Excel under the Data section of the ribbon and the “What-if Analysis” button, which contains both “Goal Seek” and “Data Table.”
What is sensitivity analysis?
Sensitivity analysis doesn't just happen in Excel (though that is a common tool finance teams use to run their analyses). Zooming out for a moment, sensitivity analysis adds credibility to traditional strategic and scenario planning. By analyzing key variables and modeling the impact changes to those variables could potentially have on the business, finance teams can offer potential pictures of future business performance.
Why is sensitivity analysis important for finance teams?
In the realm of financial forecasting, sensitivity analysis helps finance teams better understand how changes in costs, revenues, or other financial inputs can affect profits, net worth, or other key financial metrics.
Sensitivity analysis stands as a pivotal tool in:
Risk Management: Identify which variables have the most impact on your forecasts, helping to identify areas that need risk mitigation strategies.
Better Decision Making: Understand the potential range of outcomes and make more informed decisions.
Strategic Planning: Plan for various scenarios and ensure that your strategies are robust under different conditions.
Resource Allocation: By pinpointing which inputs affect your bottom line the most, sensitivity analysis enables you to optimize resource distribution, ensuring that every dollar and every effort are channeled for maximum impact.
Sensitivity analysis example
Making stock price predictions for publicly traded companies is a great example of sensitivity analysis in finance.
There are several input variables such as:
- The company’s earnings
- Debt to equity ratios
- The number of competitors in the industry
- The number of outstanding shares
- Changing interest rates
- And a multitude of other factors
A data analysis can be made about future stock prices by making different assumptions or adding different variables. You might discover that stock prices are more sensitive to changes in interest rates than they are to the number of competitors in the industry.
You’ll also see how sensitive they are to different levels of change. For example, how much do the interest rates change to impact stock prices?
Obviously, analysts and equity buyers can use this information to make more informed decisions about stock futures based on current information.
How to conduct a sensitivity analysis
Conducting a sensitivity analysis involves a series of systematic steps to assess the impact of varying input values on a particular outcome. Below are the basic steps to conduct a comprehensive sensitivity analysis:
Define the Scope: Determine which variables you will analyze and what your dependent variable is.
Develop the Financial Model: Build your financial model, ensuring all variables and relationships are correctly represented.
Identify Key Input Variables: Pinpoint the key variables that you believe could have the most significant impact on your forecast.
Assign Ranges to Key Variables: Determine the range of values that each key variable can take.
Run Scenarios: Use software like Excel to run various scenarios, altering the values of your key variables within the predetermined ranges.
Analyze the Results: Look at how changes in input variables impact your forecast, identifying which variables have the most significant effect.
Develop Strategies and Contingency Plans: Based on your findings, develop strategies to mitigate risks and create contingency plans for potential scenarios.
Finance teams can utilize Excel’s data table function or other specialized software, such as Cube, for more efficient and accurate sensitivity analysis.
Two methods for performing sensitivity analysis in Excel
Depending on what data you have and what answers you’re looking for, there is a range of sensitivity analysis formulas to use in Excel.
Using a finance software solution that integrates with Excel—and provides financial modeling templates—can make the process even easier.
What is one-way, univariate, or local sensitivity analysis?
One-way sensitivity analysis assesses the impact changes in a certain input variable will have on the output results.
This is the simplest form of sensitivity analysis since only one input variable is changed at a time and correlations between input variables aren't considered. Univariate sensitivity analysis is also known as local sensitivity analysis.
Local sensitivity analysis is a good way to determine what variables will impact an outcome the most. That is, what your dependent variable is most sensitive to.
This type of analysis can be done by almost any business stakeholder to determine causal relationships.
For instance:
- Marketers might use this type of analysis to understand the customer acquisition impact of funneling more money into Google Ads
- A Head of Sales might run a univariate sensitivity analysis to determine whether hiring more SDRs correlates with more closed deals
- Pricing and packaging professionals or FP&A professionals might use one-way sensitivity analysis to determine the optimal pricing architecture to increase revenue growth but mitigate the risk of losing customers.
What is multivariate or global sensitivity analysis?
Multivariate sensitivity analysis is a technique that accounts for the fact that more than one parameter in complex models may be uncertain. It’s also known as global sensitivity analysis.
Global sensitivity analysis helps you measure the impact of more than one variable.
To complete sensitivity analysis with multiple independent variables, you change each variable one at a time to investigate what impact the changes have on the model.
Because there's a relatively unlimited combination of business variables (and because as the number of variables increases, the complexity of the analysis scales exponentially), typical multivariate sensitivity analyses investigate only a few parameters at a time.
In other words: multivariate sensitivity analysis leads to very complex models.
There are many ways in which multivariate sensitivity analyses can be put into practice. Some include:
- Analysts may use sensitivity analyses with many independent variables to properly balance supply and demand for marketplace businesses. They might answer questions like, “Are there too many sellers or buyers on the platform and which one is more impactful in making the marketplace work?”
- CFOs might use multivariate sensitivity analyses to determine the impact of spending more money on a sales force vs. investing more in marketing channels.
Sensitivity analysis vs. scenario analysis
Sensitivity Analysis is used to understand the effect of a set of independent variables on some dependent variable under certain specific conditions.
Let’s say an FP&A analyst wants to measure the impact of a company’s net working capital on its profit margin. Sensitivity analysis will analyze all the possible variables that have an impact on the company’s profit margin. It will isolate each of the fixed and variable costs and record all possible outcomes to answer specific questions about particular variables.
Although similar to sensitivity analysis, scenario analysis is used to estimate changes to a portfolio's value in response to a specific event. It’s often used to run various outcomes in a single budget or plan while changing more than one variable at a time.
A common scenario analysis use case: drawing various outcomes from an annual operating budget to make better business decisions around resource allocation and adjustment.
Scenario analysis requires the financial analyst to examine a specific real-life scenario in detail. It’s usually done to analyze situations involving major economic changes like global market shifts—or pandemics.
After specifying the details of the scenario, the analyst then determines all the relevant variables so that they align. The result is a comprehensive picture of the future with a full range of outcomes given every extreme possibility.
So scenario analysis differs from sensitivity analysis by what they measure: scenario analysis is more interested in a particular set of conditions, while sensitivity analysis addresses a range of output variables based on variable model input.
Sensitivity analysis vs. what-if analysis
Sensitivity analysis is a form of what-if analysis. What-if analysis is a technique used to determine how projected performance is affected by changes in the assumptions that projections are based on. It helps compare different scenarios and their potential outcomes based on fluctuating conditions.
The purpose of a what-if analysis is to determine the effect of these outcomes in a statistical model paired with risk assessment. It’s frequently used by researchers, analysts, scientists, and investors.
What-if analysis, sensitivity analysis, and simulation analysis are often used interchangeably.
Sensitivity analysis advantages
If used in the right situation with the right information, sensitivity analysis can provide multiple benefits.
Understanding multiple uncertainties
Complex sensitivity analysis educates users on the different elements that will impact a project. And because you’re looking at all the variables that can create a change, predictions can be more reliable and accurate.
You also know what solutions to have prepared for specific challenges.
Improves potential weaknesses
Because sensitivity analysis studies each variable independently, it can identify which variables might act as a weakness. Once the weakness is identified, it can be altered to have less of a negative impact on the business.
Better decision-making
Sensitivity analysis will provide many possible results that happen due to the change of many variables. Management can see which variables have a high impact on the success or failure of a project—and which might not be relevant at all.
When all information is taken into consideration, a company will be able to make better financial decisions for the future.
Catches errors + conducts quality checks
The assumptions in any original analysis used to inform the sensitivity analysis may have some uncaught errors. By performing different analytical iterations, finance teams can catch mistakes in the original analysis and make adjustments.
Sensitivity analysis limitations & challenges
Here are some common limitations to sensitivity analysis in Excel.
Based on assumptions
This is the biggest one: sensitivity analysis is based on historical data and assumptions made by those conducting the analysis. If these assumptions themselves are inaccurate, this means the entire analysis will be wrong.
Trying to limit biases and inaccuracies can help strengthen the reliability of the sensitivity model. This is also why it’s important to limit the number of variables in a multi-variable sensitivity analysis. Doing so decreases the chance of misleading results.
Each variable is considered individually
Sensitivity analysis looks at each factor individually. This doesn’t allow for an analysis of the interaction and correlation between variables though in reality variables are related to each other in some way.
To go back to our stock pricing example, sensitivity analysis can study how a company’s earnings impact stock price and how interest rates impact stock price.
But, it’s not looking at how interest rates impact the company’s earnings or vice versa. And it assumes a company’s earnings can change independently of any other variables—while in reality a company’s earnings are affected by a myriad of factors. Black Swan events and factors far outside an analyst’s control still exist—so different sensitivity analyses cannot completely eliminate risk.
Advanced techniques and best practices
Financial teams can enhance a sensitivity analysis using these advanced techniques to create a more robust, dynamic, and comprehensive evaluation.
Use of Tornado Diagrams: A tornado diagram visually displays the results of a one-way sensitivity analysis for all variables in a single chart, ranking them based on their influence.
Monte Carlo Simulation: By generating thousands of possible outcomes based on probability distributions of your input variables, this approach allows for a more comprehensive understanding of potential variability and risk.
Scenario Analysis: Rather than looking at changes in a single variable, scenario analysis allows you to explore the impacts of simultaneous changes by analyzing a set of diverse scenarios, such as a best-case, worst-case, and most likely scenario, to understand the full spectrum of possibilities.
Other sensitivity analysis terms to know
Independent variables
An independent variable is an input, assumption, or driver that is changed in order to assess its impact on a dependent variable or the outcome.
Dependent variables
A dependent variable is a variable whose value will change depending on the value of another variable, called the independent variable.
Reduced cost in sensitivity analysis
Reduced cost, or opportunity cost, is the most basic form of sensitivity analysis information. It’s the amount a function would have to improve for the corresponding variable to improve.
IBM explains:
The reduced cost provides the rate of change in the objective for each nonbasic variable as it moves from the bound at which it resides. The most common type of variable has a lower bound of 0 and an infinite upper bound. In this case, the reduced cost indicates the rate of change in the objective as the variable moves to a nonzero value.
Forecasting
Financial forecasting is the act of making financial projections to predict and estimate the near-term and long-term financial performance of your organization. Forecasting is fundamental to Finance and FP&A and is used by organizations across the globe as the basis for decision-making.
Financial modeling
Financial modeling is about the past, present, and future. Aggregated and analyzed historical data shows businesses not only the current financial health of a business but also an accurate estimate of the organization’s financial future. Sensitivity analysis is a form of financial modeling.
What is probabilistic sensitivity analysis?
Probabilistic sensitivity analysis is an advanced type of mathematical model that uses probability distributions to reduce uncertainty and determine the optimal course of action out of a group of possible outcomes.
This is generally well outside the realm of typical financial analysis; it's not a common sight in financial models and is generally outside the scope of FP&A
If you've determined that this type of sensitivity analysis is a good fit for what you need to accomplish, you should download and use the R programming language to perform it.
What is the Fourier amplitude sensitivity test?
The Fourier amplitude sensitivity test, also called FAST, uses "a periodic sampling approach and a Fourier transformation to decompose the variance of a model output into partial variances contributed by different model parameters."
Or, in plain English: it's a mathematical model that helps isolate the smaller variances that contribute to a large (or noisy) variance.
So FAST is a useful tool for when you need to collapse global methods into local methods.
However, this is generally more useful for assessing scientific models than it is for economic evaluation. Since the finance team can choose which input variables to manipulate and can change its modeling assumptions at will, FAST is overkill and largely unnecessary for FP&A.
What is regression analysis?
Regression analysis is the most reliable way of finding which input variables have the greatest effect on any output variable. Linear regression is the most common form of this analysis.
In other words: it's a mathematical technique for sorting out how different input values lead to different model outputs and for identifying the relative importance (in terms of their ability to change the financial model) of different values.
HBR wrote an excellent refresher on this topic.
What is risk analysis?
Risk analysis, or a quantitative risk assessment, is a method of finding and isolating the variables that lead to an adverse event.
In this sense, a good sensitivity analysis is also a type of risk analysis. By tweaking model inputs (or a specific dependent variable) to asses a model response and avoid undesired values of a given output variable, the FP&A team can tweak their financial models to avoid those model outputs.
So risk analysis, like sensitivity analysis, is a way to determine how different values interact and create a specific model output.
Conclusion: the best way to perform sensitivity analysis in Excel
Sensitivity analysis can be a complex topic, but it’s a great way for finance teams to understand the impact of financial decisions on the future of a company. Financial modeling software makes financial modeling easier, more efficient, and less resource-consuming.
Cube revolutionizes your data management and strategic planning with its suite of sophisticated tools, designed to enhance collaboration and streamline processes. Our platform seamlessly integrates with Excel and Google Sheets, providing a bi-directional flow of data, ensuring that your work remains flexible and connected.
Book your demo today to see firsthand how Cube empowers finance teams with the tools needed to help businesses thrive in an ever-changing financial landscape.
.png)
