What is sensitivity analysis in strategic finance?

What is sensitivity analysis in Excel?

In its simplest form, sensitivity analysis is a financial model that measures the effect of input variables on target variables.

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

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

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

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.

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 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 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 specifically provides automated data consolidation, sharable planning templates, customizable dashboards and reports, scenario planning and analysis, bidirectional Excel and Google Sheet integration, approval workflow, drill-throughs, audit trails, user-based controls, and centralized formulas and KPIs.

These FP&A software features translate to easier reporting and KPIs, more accurate forecasting and budgeting, faster close and consolidation cycles, and collaborative teamwork for more control and fewer mistakes. Book a demo today to get started!