How To Draw Sensitivity Analysis Graph In Excel

What is Sensitivity Analysis?

Sensitivity Analysis is a tool used in financial modeling What is Financial Modeling Financial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. to analyze how the different values of a set of independent variables affect a specific dependent variable under certain specific conditions. In general, sensitivity analysis is used in a wide range of fields, ranging from biology and geography to economics and engineering.

It is especially useful in the study and analysis of a "Black Box Process" where the output is an opaque function of several inputs. An opaque function or process is one which, for some reason, can't be studied and analyzed. For example, climate models in geography are usually very complex. As a result, the exact relationship between the inputs and outputs are not well understood.

what is sensitivity analysis

Image from CFI's Scenario & Sensitivity Analysis in Excel Course

What-If Analysis

A Financial Sensitivity Analysis, also known as a What-If analysis or a What-If simulation exercise, is most commonly used by financial analysts The Analyst Trifecta® Guide The ultimate guide on how to be a world-class financial analyst. Do you want to be a world-class financial analyst? Are you looking to follow industry-leading best practices and stand out from the crowd? Our process, called The Analyst Trifecta® consists of analytics, presentation & soft skills to predict the outcome of a specific action when performed under certain conditions.

Financial Sensitivity Analysis is done within defined boundaries that are determined by the set of independent (input) variables.

For example, sensitivity analysis can be used to study the effect of a change in interest rates on bond prices if the interest rates increased by 1%. The "What-If" question would be: " What would happen to the price of a bond If interest rates went up by 1%?". This question can be answered with sensitivity analysis.

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". These functions are both taught step-by-step in our free Excel Crash Course.

what-if analysis Excel ribbon

Sensitivity Analysis Example

John is in charge of sales for HOLIDAY CO, a business that sells Christmas decorations at a shopping mall. John knows that the holiday season is approaching and that the mall will be crowded. He wants to find out whether an increase in customer traffic at the mall will raise the total sales revenue Sales Revenue Sales revenue is the income received by a company from its sales of goods or the provision of services. In accounting, the terms "sales" and of HOLIDAY CO and, if so, then by how much.

The average price of a packet of Christmas decorations is $20. During the previous year's holiday season, HOLIDAY CO sold 500 packs of Christmas decorations, resulting in total sales of $10,000.

After carrying out a Financial Sensitivity Analysis, John determines that a 10% increase in customer traffic at the mall results in a 7% increase in the number of sales.

Using this information, John can predict how much money company XYZ will generate if customer traffic increases by 20%, 40%, or 100%. Based on John's Financial Sensitivity Analysis, such increases in traffic will result in an increase in revenue of 14%, 28%, and 70%, respectively.

sensitivity analysis example table

Learn how to build a table like this in our Free Excel Crash Course!

Download the Free Template

Enter your name and email in the form below and download the free template now!

Sensitivity Analysis Table

Download the free Excel template now to advance your finance knowledge!

Sensitivity Analysis vs. Scenario Analysis

It is important not to confuse Financial Sensitivity Analysis with Financial Scenario Analysis. Although similar to some degree, the two have some key differences.

Sensitivity Analysis is used to understand the effect of a set of independent variables on some dependent variable under certain specific conditions. For example, a financial analyst wants to find out the effect of a company's net working capital on its profit margin. The analysis will involve all the variables that have an impact on the company's profit margin, such as the cost of goods sold Accounting Our Accounting guides and resources are self-study guides to learn accounting and finance at your own pace. Browse hundreds of guides and resources. , workers' wages, managers' wages, etc. The analysis will isolate each of these fixed and variable costs Fixed and Variable Costs Cost is something that can be classified in several ways depending on its nature. One of the most popular methods is classification according and record all the possible outcomes.

Scenario Analysis, on the other hand, requires the financial analyst to examine a specific scenario in detail. Scenario Analysis is usually done to analyze situations involving major economic shocks, such as a global market shift or a major change in the nature of the business.

After specifying the details of the scenario, the analyst would then have to specify all of the relevant variables, so that they align with the scenario. The result is a very comprehensive picture of the future (a discrete scenario). The analyst would know the full range of outcomes, given all the extremes, and would have an understanding of what the various outcomes would be, given a specific set of variables defined by a specific real-life scenario.

Advantages of Financial Sensitivity Analysis

There are many important reasons to perform sensitivity analysis:

Sensitivity analysis adds credibility to any type of financial model Types of Financial Models The most common types of financial models include: 3 statement model, DCF model, M&A model, LBO model, budget model. Discover the top 10 types by testing the model across a wide set of possibilities.
Financial Sensitivity Analysis allows the analyst to be flexible with the boundaries within which to test the sensitivity of the dependent variables to the independent variables. For example, the model to study the effect of a 5-point change in interest rates on bond prices would be different from the financial model that would be used to study the effect of a 20-point change in interest rates on bond prices.
Sensitivity analysis helps one make informed choices. Decision-makers use the model to understand how responsive the output is to changes in certain variables. Thus, the analyst can be helpful in deriving tangible conclusions and be instrumental in making optimal decisions.

Best Practices in Sensitivity Analysis

#1 Layout in Excel

Layout, structure, and planning are all important for good sensitivity analysis in Excel. If a model is not well organized, then both the creator and the users of the model will be confused and the analysis will be prone to error.

The most important points to keep in mind for layout in Excel include:

Place all assumptions in one area of the model
Format all assumptions/inputs Financial Model Formatting Financial model formatting is a science all on its own. By formatting a financial model properly, the analyst maintains consistency, clarity and efficiency. in a unique font color so that they are easy to identify
Think carefully about what to test – only the most important assumptions
Understand the relationship (correlation) between dependent and independent variables (linear? – nonlinear?)
Create charts and graphs that enable users to easily visualize the data
Create a separate area for the analysis by using grouping (see example below)

sensitivity structure layout

#2 Direct versus indirect methods

The direct method involves substituting different numbers into an assumption in a model.

For example, if the revenue growth assumption in a model is 10% year-over-year (YoY YoY (Year over Year) YoY stands for Year over Year and is a type of financial analysis used for comparing time series data. It is useful for measuring growth and detecting trends. ), then the revenue formula is = (last year revenue) x (1 + 10%). In the direct approach, we substitute different numbers to replace the growth rate – for example, 0%, 5%, 15%, and 20% – and see what the resulting revenue dollars are.

The indirect method (as shown below) inserts a percent change into formulas in the model, instead of directly changing the value of an assumption.

Using the same example as above, if the revenue growth assumption in a model is 10% year-over-year (YoY YoY (Year over Year) YoY stands for Year over Year and is a type of financial analysis used for comparing time series data. It is useful for measuring growth and detecting trends. ), then the revenue formula is = (last year revenue) x (1 + 10%). Instead of changing 10% to some other number, we can change the formula to be = (last year revenue) x (1 + (10% + X)), where X is a value contained down in the sensitivity analysis area of the model.

sensitivity layout excel

Learn how to do this step-by-step – launch our sensitivity analysis course now!

#3 Tables, charts, and graphs

Sensitivity analysis can be challenging to comprehend even by the most informed and technically savvy finance professionals, so it's important to be able to express the results in a manner that's easy to comprehend and follow.

Data tables are a great way of showing the impact on a dependent variable by the changing of up to two independent variables. Below is an example of a data table that clearly shows the impact of changes in revenue growth and EV/EBITDA multiple EV/EBITDA EV/EBITDA is used in valuation to compare the value of similar businesses by evaluating their Enterprise Value (EV) to EBITDA multiple relative to an average. In this guide, we will break down the EV/EBTIDA multiple into its various components, and walk you through how to calculate it step by step on a company's share price.

data table sensitivity example

Tornado Charts can be a great way of showing the impact of changes to many variables at once. They are called Tornado Charts because they are sorted, from the most impactful to least impactful, in a way that shapes the chart like a tornado cone. To learn how to build these charts, launch our sensitivity analysis in Excel course now!

tornado chart excel example