Suppose it’s your first day to office or college and you are new to the area. You ask your friend for the exact way to get there along with the estimated time. You follow his directions but reach your destination one hour late!! Oops!! What happened? The bus got 20 minutes late, the road to your office/college was dug up so you had to follow a longer route, and lastly your footwear tore off so you had to spend 10 more minutes at the cobbler’s shop!! Life is so full of possibilities isn’t it?
The field of financial modelling is also a lot like life. There are numerous possibilities and a good model is the one whose sensitivity can be stress-tested against all these.
What is Sensitivity Analysis?
Technically, Sensitivity Analysis is the practice that is used to depict the way changes in certain independent variables impact the dependent variables of the financial model, under a fixed set of assumptions. Sensitivity analysis is performed with the help of Data table in excel. There are other options also such as Goal Seek and Scenario Manager, however for this post we will limit our discussion to Data Table.
Let us take a small example and see how we perform Sensitivity Analysis on a two-variable Data table. For two-variable data table we require two possible ranges of input for analyzing a given output.
Let us take example of a company ABC, for which we have done a simple calculation for Gross Profit. In this case, we will study the impact of annual revenue growth and COGS as a % of revenue on the Gross-Profit. Which means we will judge the sensitivity of the Gross-Profit based on the other two variables.
- For a two-variable data table, we need to copy of the original formula of the gross profit value at the intersection of the row and column input values.
- Select the table range from G9:L15 and click on Data—–What if Analysis—-Data Table
- Click on E5 as the Row input cell ($E$5) and Click on E3 ($E$3) as Column input cell. Then click OK.
- The table gets populated with all the possible Gross Profit under any given combination of y/y revenue growth and COGS as a % of revenue.
So, we see that through Sensitivity Analysis, we judge the impact of two independent variables on a dependent variable. Conclusion of this analysis is that more the variation an output value shows for a particular combination of variables, the more is its dependency on those variables which means its sensitivity quotient is high at that level.
Importance of sensitivity analysis
- Credibility– Future cannot be predicted; hence testing the financial model across a given set of possibilities lends more credibility to the output. Moreover it also enhances confidence amongst the users of the financial model.
- Flexibility-The premises on which a financial model is based are assumptions at the end of the day. A sensitivity analysis shows a range of outputs which lends more flexibility to the model.
- Insights– A financial model can never be static. There has to be an element of dynamism associated with it. Studying the interdependence of the independent variables with few dependent variables helps one to draw insights on various scenarios. A financial model is not just aimed at deriving an output, but empowering analysts to scrutinize the circumstances and sharpen predictability skills.
- Risk assessment-Analyzing the dependency of one variable on the other also helps in assessing the underlying risk element. A significant change in the value of output, or an increased degree of dependency indicates various levels of risk. A sensitivity analysis can help the analyst to identify prospective risk factors and take steps to mitigate it.
- Decision Making– A sensitivity analysis helps one to make informed choices. The decision-makers use the model in understanding how responsive the output is to changes in certain variables. This relation can help the analyst in deriving tangible conclusions and be instrumental in taking optimal decisions.
Difference between Sensitivity Analysis and Scenario Analysis
Many a times, sensitivity analysis is confused with scenario analysis. While both of these terms have similar connotation, there are basic difference at the principle level. While sensitivity analysis studies responsiveness between two or more variables, scenario analysis, on the other hand, studies changes due to a certain scenario. The scenarios in question can be changes in industry regulations, fluctuations in the interest rate, economic boom and bust phase etc. Under most of these circumstances, the outcome is measured under three possible scenarios: Optimistic, Pessimistic and Realistic. The situation can also be judged by assigning probabilities and drawing correlations.
A financial model is incomplete without Sensitivity Analysis
Sound analysis is one which focuses on the holistic picture rather than an isolated case. It should not just depend on an output. Sensitivity analysis ensures that a model is stress-tested against most of the possibilities. In fact it won’t be wrong to say that a financial model is absolutely incomplete without a Sensitivity Analysis.