3 Sensitivity Analysis Formulas You Should Know
10m Read
The internal operations of a company can change in a matter of minutes, whether due to acquisition by another corporation, the passing of new government regulation, or something else entirely.
It’s crucial that business leaders keep a pulse on these changes so they can adapt accordingly. Quick, decisive, data-driven decisions are key to securing success during times of business instability.
Financial models—and sensitivity analysis, in particular—can help business leaders monitor these situations and provide insights into the best course of action in light of new information.
Sensitivity analysis, also known as what-if analysis or simulation analysis, reveals how independent variables affect a dependent variable based on certain assumptions in a given situation. Finance professionals and business leaders alike use them to model the potential outcomes of any given scenario.
Here we’ll discuss what sensitivity analysis can be used for, what a sensitivity analysis formula is, and how to conduct an analysis of your own. Finally, we’ll cover a new way that financial modelers can perform what-if simulations in a fraction of the time.
The purpose of sensitivity analysis
Simply put, sensitivity analysis helps people discover connections between disparate independent variables (or inputs) and dependent variables (outputs). In some cases, these relationships are not even realized or understood before running the analysis.
This versatile technique can be used in any number of situations to provide insight on an existing financial model or real-life scenario.
Company executives use sensitivity analysis to model the potential outcomes of a project and evaluate alternative decisions to determine the best course of action.
Let’s say, for example, that the owner of a popular restaurant chain is looking for the most cost-effective way to bring in more revenue over a 12-month period. To find the best solution, an analyst would perform sensitivity analysis to see whether, say, increasing menu prices or offering expanded delivery and take-out options would bring in more money.
What-if simulations aren’t just decision-making tools, either—these analyses also provide data to support your reasoning behind each business decision you make.
Finance professionals also perform sensitivity analysis to identify variables that will have the greatest impact on a given situation. This allows them to narrow their focus to only the most critical considerations. Sensitivity analysis can also help analysts identify which variables will have little to no impact, so you can confidently allocate valuable resources elsewhere.
Business leaders can capitalize on the insights gained from these models to cut funding for projects that don’t bring in money, and offer more resources to the ones that do.
And that’s not all. What-if analysis also helps point out any underlying errors in a financial model’s calculations or assumptions. When it runs smoothly, this tool adds credibility to the model because it backs up the information provided with hard data.
But an analyst’s job doesn’t end after running a financial model. They must also present their findings to executives that may not have the same in-depth understanding of data analysis and statistics. On a broader scale, sensitivity analysis helps present complex sets of information to stakeholders in a way that’s easy to understand and analyze.
What is a sensitivity analysis formula?
Under a set of assumptions, sensitivity analysis examines how a target variable is affected by a change in an input variable. The goal is to see how sensitive a business or organization’s outcomes are to a change in an input, such as product pricing, loan interest rates, customer foot traffic and more.
The overarching formula that shows the sensitivity of important output variables is the following:
Sensitivity = Percentage change in output / Percentage change in input
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).
From there, you can adjust the inputs of the scenario using direct or indirect methods.
Using the direct method, you would substitute numerical values directly into the sensitivity analysis formula. Inversely, the indirect method involves changing the inputs by a percentage of the value, rather than substituting in actual values.
Since sensitivity analysis explores a wide range of input and output variables, the formulas analysts use to get the numbers needed to calculate sensitivity can vary.
So, when conducting a sensitivity analysis in Excel or another financial modeling platform, an analyst must first identify the key variables that influence their analysis and then observe how the output changes based on adjustments to the input.
Note that businesses, organizations, and investors often use sensitivity analysis to determine if an investment is worthwhile. Consider the following examples:
- A retail chain may wonder if opening another store will deliver positive returns.
- A university may want to know how a new digital marketing campaign would boost enrollment and revenue.
- An investor may wish to calculate how the entry of a new competitor would affect a public company’s share price.
In order to figure out if a project or investment will deliver the desired returns, analysts like to look at net present value (NPV) as the output (dependent variable) in their sensitivity analysis. NPV helps you see whether a project is actually profitable by taking into account the time value of money. Its formula is as follows:
NPV = ( Cash flows / (1 + discount rate)t ) – initial investment
t refers to time (usually number of years).
Now that we’ve covered the sensitivity analysis formulas you should know, let’s take a closer look at how to conduct sensitivity analysis using your own what-if simulations.
How to perform sensitivity analysis
To maximize its effectiveness and ensure that your model is accurate, sensitivity analysis must be conducted methodically using the following steps:
- Decide which variables and methodology you will use to test your assumptions.
- Identify which independent variables you will change and which dependent variables you will observe.
- Define the output of your base case—the result most likely to take place in your given scenario.
- Adjust one input, while keeping all other inputs at your baseline.
- Calculate the effects of this change on your output.
- Find the percentage change in the output and the input.
- Calculate the sensitivity of the output to the input (more on this below).
- Reset the input to your baseline and adjust another input variable.
- Repeat until you have calculated the outputs for all different inputs.
The higher the number, the more sensitive the output is to this variable. This method can be used to analyze best-case and worst-case scenarios, as well—all you need to do is change the output to reflect the new situation you want to analyze.
So if you wanted to maximize a mattress store’s operating profit, for example, you could use sensitivity analysis to find out the optimal price point and number of mattresses the store would need to sell.
Let’s say you have the following assumptions:
- Mattresses sold: 1,000
- Mattress price: $500
- Operating profit: $50,000
You’d create a new table in Excel, with mattress prices down the left side of the table and the number of mattresses sold along the top. Next, you’d link in your operating profit into the top-left corner of your table so that the data table populates it in the next step.
In order to create your financial model, highlight the entire table. Under the Data tab, click What-If Analysis and then select Data Table from the drop-down menu.
Insert the variables you want to analyze in the Row Input Cell and Column Input Cell fields: 1,000 and $500, respectively. Then click OK.
The resulting data table would show you how the company’s operating profit changes based on different combinations of the inputs provided.
The two main methods to conduct sensitivity analysis
The method outlined in the previous section is known as the one-at-a-time (OAT) or local sensitivity analysis. This is one of the most popular ways to perform sensitivity analysis.
OAT analysis looks at the impact of each input on the output one at a time, while keeping all other values in the scenario the same. But because it is so cut-and-dry, OAT sensitivity analysis doesn’t always reveal the relationships between variables or the probability of different combinations and permutations.
So, what does an analyst do when they need to perform more complex analyses? They turn to the next most popular method: global sensitivity analysis.
Global sensitivity analysis is typically conducted using Monte Carlo simulation techniques. These techniques give analysts a better look at what the utility values of random variables might be via rigorous, repeated testing using known variables alongside the unknown variables.
Global sensitivity analysis is most valuable where local sensitivity analysis, such as OAT analysis, falls short: in scenarios with unclear or uncertain inputs. This type of analysis can also be used to model relationships between input variables—something that cannot be done using OAT analysis.
Between global and local sensitivity analysis, financial analysts are equipped with the right tools to more accurately predict future events and to communicate these insights to leadership.
However, new advances in modeling technology promise to simplify the entire financial modeling process for both analysts and executives.
Forward-focused modeling software for the modern organizations
The modeling problems encountered by financial analysts today are the same ones that industry professionals have dealt with for decades. For every new sensitivity analysis performed, most modelers still create the underlying rules and algorithms from scratch.
This process is time-consuming and leaves far too much room for human error. A compromised model leaves business leaders in the dust as they scramble to deal with the uncertainty of company-wide decisions.
Synario, an intelligent financial modeling software program, offers analysts and business leaders alike a chance to speed up the decision-making process and transform their companies’ financial futures.
Synario’s sensitivity analysis software equips analysts to calculate initial results and scenario adjustments in seconds. This, in turn, empowers leadership teams to make well-informed business decisions with confidence.
Synario’s intuitive interface and vast library of prebuilt algorithms allows financial modelers to conduct advanced what-if simulations without the need for hours of tedious and repetitive manual entry.
And when it comes time for analysts to meet with business leaders and present their findings, Synario’s Presentation Mode makes it easy to visualize the impacts of any executive decision.
Join hundreds of organizations from around the world and invest in your company’s financial future with Synario’s patented Multiverse Modeling™ solution.
