Sensitivity Analysis: An indispensable part of Financial Modelling
Imagine, today is your day to college or even office and you are completely new about the locality. There are greater chances you might ask your friends for getting the right way to reach there along with the estimated timing. You have followed all the directions correctly, but still reach your place half an hour late!! Oops!! What really happened? The bus on which you ride was 15 minutes late, the road to your office was repairing so you have to take a different route, which was longer than the others and lastly, you’re your shoe get torn off. As a result, you have to spend additional 5 minutes at the shop of cobbler due to which you reach late to your destination. We can say that the life has lots of possibilities that we can’t even imagine?
The world of financial modeling works same like our life. There is a great range of possibilities and a great model is a thing who has a sensitivity that can be stress tested against all these things.
What is sensitivity analysis?
If we talk in a technical way, then sensitivity analysis can be defined as a practice that is utilized for depicting the way modified in particular independent variables affecting the financial model’s dependent variables under a certain set of assumptions. It is performed by using the data table available in the Excel. There are lots of other options, like Scenario Manager and Goal Seek. However, we will only talk about the data table in this discussion. We will start with an example and check out the performance of sensitivity analysis on a two-variable data table. We need two possible input ranges to find out a given input for the two variable tables.
Have a look at the example of a company named XYZ for which we have performed a basic calculation about the Gross profit. Here, we will research about the effect if yearly revenue growth and COGS in the form of a revenue percentage on the gross profit. Through this, we want to say that we will find out the sensitivity, which is based on the other two variables.
- First of all, we have to copy the original formula of the value of gross profit for a two-variable data table. The value needs to be calculated at the intersection of the column and row input values.
- Choose the table range from G9: L15 and then tap on data- What if Analysis- Data Table
- Now, tap on E3 as the column input cell and then tap on E3 as Row input cell. After that, tap on the ok option.
- The table will become populated with lots of possible gross profit through a combination given about y/y revenue growth and COGS a percentage of revenue.
So, now you have seen the method to judge the effect of two independent variables on a dependent variable through sensitivity analysis. To sum this analysis, we can say that the dependency on a combination of variables depends on the variation an output value feature for a specific combination of those variables. Through this, we mean that the level of sensitivity quotient is high at that time.
Why is sensitivity analysis important?
The foundation on which a financial model is created is none other than the assumptions that are made at the end of the day. There is a range of outputs featured by a sensitivity analysis that provides additional flexibility to the model.
No one can predict the future and that’s why testing the financial model throughout a provided set of possibilities give more credibility to the output. Furthermore, it boosts the confidence level among the individuals using that financial model.
There is no reason for the financial model to be static. There should be a part of dynamism in it. Researching the interdependence of the independent variables with some dependent variables can assist the individuals in putting some light on different conditions. A financial model should not just target on deriving an output, but also give power to analysts to scrutinize the conditions and boost up the predictability skills.
Individuals can make informed choices with the help of sensitivity analysis. The model is used by the decision-makers for understanding the level of responsiveness of the output to the modifications in particular variables. The analyst will be greatly assisted by this relation to derive the tangible conclusions. Additionally, it can be extremely instrumental while making optimal decisions.
Figuring out the dependency of one variable over the other one can assist to assess the risk of underlying element. There are many different levels of risks that are indicated by an increase in the degree of dependency or value of output. The analyst will be greatly assisted by a sensitive analysis for the identification of the prospective risk factors and a range of steps for mitigating that.
What are the differences between sensitivity analysis and scenario analysis?
There are lots of chances where the people confuse sensitivity analysis with the scenario analysis. Both these terms have a similar connotation, but still, there are lots of basic differences between them at the basic level. In a sensitivity analysis, responsiveness between two or more variables is studied, whereas the scenario analysis studies the modification because of a particular scenario. These scenarios can be about the modifications in an economic boom, bust phase, industry regulations, fluctuation in the interest rate, and other similar things. However, the outcome is calculated under three possible scenarios in the majority of circumstances, named as realistic, pessimistic, and optimistic. The circumstance can also be judged by drawing correlations and assigning probabilities.
A financial model can never be completed without sensitivity analysis
Sound analysis is the thing that targets the holistic scene instead of an isolated case. It can’t fully rely on an output. A model has stress-tested against the majority of the possibilities are ensured by this sensitivity analysis. In reality, we can say that a financial model can only be complete with the sensitivity analysis.
To Review our other Blogs – click here