What is Sensitivity Analysis?
Financial risk modeling takes sensitivity analysis to the next level and helps in assessing the probability and potential impact of unfavorable outcomes. Based on the assessments, various decisions with respect to managing, hedging or transferring risks are taken.
Sensitivity analysis is one of the tools that help decision makers with more than a solution to a problem. It provides an appropriate insight into the problems associated with the model under reference. Finally, the decision maker gets a decent idea about how sensitive the optimum solution is chosen by him to any changes in the input values of one or more parameters.
Have you ever been caught in a situation regarding data sensitivity analysis in Financial Modeling? If you have faced a problem before, find your answer right here!
Measurement of sensitivity analysis
Below are mentioned the steps used to conduct sensitivity analysis:
• Firstly, the base case output is defined; say the NPV at a base case input value (V1) for which the sensitivity is to be measured. All the other inputs of the model are kept constant.
• Then the value of the output at a new value of the input (V2) while keeping other inputs constant is calculated.
• Find the percentage change in the output and the percentage change in the input.
• The sensitivity is calculated by dividing the percentage change in output by the percentage change in input.
This process of testing sensitivity for another input (say cash flows growth rate) while keeping the rest of inputs constant is repeated till the sensitivity figure for each of the inputs is obtained. The conclusion would be that the higher the sensitivity figure, the more sensitive the output is to any change in that input and vice versa.
For Sensitivity Analysis follow the following steps
First LINK the output you want to check sensitivity of? (IN FMCG case link the share Price or EV)
Next decide the variable you want to check the sensitivity of (e.g. WACC; Terminal Growth rate; tax rate etc.)
Let’s say we selected WACC and Terminal Growth which originally n the model is 10.7% and 5%. Now take the range for two variable which will be 8.7; 9.7; 10.7;11.7 and 12.7% for WACC and let’s say 3; 4; 5; 6; 7% for T. Growth. place these numbers on the cell next to your linked cell in step one above. So, if you have linked EV in the cell G30 Wick will come in the cell from H30 - L30 and T Growth will come in cell G31 to G35
Now select the cell from G30 to L35 and go to data tab - "What if Analysis" - "Data table"
Now in the window which pops up in the "row input" select the cell where you have originally calculated your WACC and in Column select cell where you have originally calculated T Growth Rate and press enter
Uses of Sensitivity Analysis
• The key application of sensitivity analysis is to indicate the sensitivity of simulation to uncertainties in the input values of the model.
• They help in decision making
• Sensitivity analysis is a method for predicting the outcome of a decision if a situation turns out to be different compared to the key predictions.
• It helps in assessing the riskiness of a strategy.
• Helps in identifying how dependent the output is on a input value. Analyses if the dependency in turn helps in assessing the risk associated.
• Helps in taking informed and appropriate decisions
• Aids searching for errors in the model
Conclusion
Sensitivity analysis is one of the tools that help decision makers with more than a solution to a problem. It provides an appropriate insight into the problems associated with the model under reference. Finally, the decision maker gets a decent idea about how sensitive the optimum solution is chosen by him to any changes in the input values of one or more parameters.