Sensitivity Analysis

What is Sensitivity Analysis?

Sensitivity analysis is an analysis technique that works on the basis of what-if analysis like how independent factors can affect the dependent factor and is used to predict the outcome when analysis is performed under certain conditions. It is commonly used by investors who takes into consideration the conditions that affect their potential investment to test, predict and evaluate result.

Sensitivity Analysis Formula

The formula for sensitivity analysis is basically a financial model in excel where the analyst is required to identify the key variables for the output formula and then assess the output based on different combinations of the independent variables.

Mathematically, the dependent output formula is represented as,

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Sensitivity Analysis (wallstreetmojo.com)

Calculation of the Sensitivity Analysis (Step by Step)

• Range of 1^st independent variable
• Range of 2^nd independent variable

Examples

Example #1

Let us take the example of a simple output formula, which is stated as the summation of the square of two independent variables X and Y.

In this case, let us assume the range of X as 2, 4, 6, 8, and 10, while that of Y as 1, 3, 5, 7, 9, 11, and 13. Based on the above-mentioned technique, all the combinations of the two independent variables will be calculated to assess the sensitivity of the output.

For instance, if X = 3 (Cell B2) and Y = 7 (Cell B3), then Z = 3² + 7² = 58 (Cell B4)

Z = 58

For the calculation of Sensitivity Analysis, go to the Data tab in excel and then select What if analysis option. For the further procedure of sensitivity analysis calculation, refer to the given article here – Two-Variable Data Table in Excel

Example #2

Let us take another example of bond pricing where the analyst has identified the coupon rate and the yield to maturity as the independent variables, and the dependent output formula is the bond price. The coupon is paid half-yearly with a par value of $1,000, and the bond is expected to mature in five years. Determine the sensitivity of the bond price for different values of coupon rate and yield to maturityYield To MaturityYield to Maturity refers to the expected returns an investor anticipates after keeping the bond intact till the maturity date. In other words, a bond's expected returns after making all the payments on time throughout the life of a bond.read more.

In this case, the analyst has taken the range of coupon rate as 5.00%, 5.50%, 6.00%, 6.50%, and 7.00%, while that of coupon rate as 5%, 6%, 7%, 8%, and 9%. Based on the above-mentioned technique, all the combinations of yield to maturity and coupon rateCoupon RateThe coupon rate is the ROI (rate of interest) paid on the bond's face value by the bond's issuers. It determines the repayment amount made by GIS (guaranteed income security). Coupon Rate = Annualized Interest Payment / Par Value of Bond * 100%read more are taken to calculate the sensitivity of the bond priceBond PriceThe bond pricing formula calculates the present value of the probable future cash flows, which include coupon payments and the par value, which is the redemption amount at maturity. The yield to maturity (YTM) refers to the rate of interest used to discount future cash flows.read more.

Therefore, the calculation of Bond Price is as follows.

Bond Price =$102,160

For the calculation of Sensitivity Analysis, go to the Data tab in excel and then select What if analysis option.

Relevance and Uses

A sensitivity analysis is a technique which uses data table and is one of the powerful excel tools which lets a financial user understand the result of the financial model under various conditions. It can also be seen as the perfect complement to another excel tool, which is known as the scenario managerScenario ManagerScenario Manager is a what-if analysis tool that works with many scenarios that are supplied to it. It uses a set of ranges that have an effect on a certain output and can be used to generate different scenarios such as bad and medium depending on the values.read more, and as such, it adds more flexibility to the valuation model during the process of analysis and, finally, in case of the presentation.

As such, it is very important for an analyst to appreciate the method of creation of a data table and then interpret its results to ensure that the analysis is heading in the desired direction. Further, a data table can be an effective and efficient way for presentation to the boss or client when it comes to expected financial performance under different circumstances.

Recommended Articles

This has been a guide to what is Sensitivity Analysis and its definition. Here we discuss how to perform Sensitivity Analysis using What if Analysis along with examples and a downloadable excel template. You can learn more about financial modeling from the following articles –

• Top Advantages and Disadvantages of NPV 
• Marginal Cost of Capital
• One Variable Data Table in Excel
• Regression Excel Analysis