1. Sensitivity analysis
Welcome back to Financial Analysis in Power BI. Last time, we learned about scenario analysis, and now we will take that a bit further with sensitivity analysis.
2. What is sensitivity analysis?
Sensitivity analysis is a type of financial modeling that evaluates the performance of a dependent variable given a range of inputs.
This is similar to scenario analysis in that they both analyze how a dependent variable will react to certain inputs. However, scenario analysis is specific to a certain "scenario", while sensitivity analysis is more open-ended because it gives a range of inputs and values.
The goal of sensitivity analysis is to understand how the dependent variable reacts to a range of input values, known as independent variables.
3. Dependent versus independent
When creating a sensitivity analysis, it's important to understand the difference between a dependent and an independent variable.
Dependent variables derive their value from the financial model and therefore rely on independent variables. They are often the observed output of the analysis.
Independent variables derive their value from outside of the financial model. They are the inputs of the analysis.
For example, look at this equation. Which variables are dependent and which are independent?
Revenue is a dependent variable in this example because it relies on three other independent variables; gross sales, discount, and premium.
4. Sensitivity table
Often, sensitivity analysis is presented in a table with conditional formatting that highlights the values from highest to lowest.
Here is an example of the price sensitivity of supply and demand.
Supply and demand are the independent variables labeled on the axes, and the price is the dependent variable, which holds the values in the table.
It may look overwhelming at first, but it's simple once your eye is adjusted.
Imagine that the units are for packs of bubble gum, so for example, supply can be between one-thousand and five-thousand packs of bubble gum, and the same goes for demand.
5. Sensitivity table
To read the table, first look at the column. Here, for example, the demand is 3,000. So the analyst can see all the possible prices when demand equals 3,000 packs of bubble gum.
6. Sensitivity table
Likewise, the highlighted row shows all listed prices when the supply is 3,000.
7. Sensitivity table
So now the analyst can pick a certain row and column to understand how the value changes. For example, when supply and demand equal each other, the price is 5 dollars.
8. Sensitivity table
However, when there is a shortage, and demand is 5,000 while supply is only 1,000, the price skyrockets to 25 dollars!
This is why sensitivity tables are so useful; they display a wide range of outcomes that allow the analyst to understand how the inputs impact the dependent variable.
9. Let's practice!
Now that you know about sensitivity analysis, it's your chance to put it to use and create some tables!