WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Excel Modeling » Sensitivity Analysis

Sensitivity Analysis

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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,

Z = X2 + Y2

Sensitivity-Analysis-Formula

Calculation of the Sensitivity Analysis (Step by Step)

  • Step 1: Firstly, the analyst is required to design the basic formula, which will act as the output formula. For instance, say NPV formula can be taken as the output formula.
  • Step 2: Next, the analyst needs to identify which are the variables that are required to be sensitized as they are key to the output formula. In the NPV formula in excel, the cost of capital and the initial investment can be the independent variables.
  • Step 3: Next, determine the probable range of the independent variables.
  • Step 4: Next, open an excel sheet and then put the range of one of the independent variable along the rows and the other set along with the columns.
    • Range of 1st independent variable
    • Range of 2nd independent variable
  • Step 5: Next, go to the “Data” tab and click on the “What-if Analysis” button. Under that, select the option of “Data Table.”
  • Step 6: Next, fill in the “Row input cell” with reference to the 1st independent variable and the “Column input cell” with reference to the 2nd independent variable.
  • Step 7: Finally, click enter for the table to take effect and present the probable outcomes. The table hence created is the sensitivity table.

Examples

You can download this Sensitivity Analysis Formula Excel Template here – Sensitivity Analysis Formula Excel Template

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.

Popular Course in this category
Sale
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
4.9 (1,067 ratings)
250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Sensitivity Analysis Formula Example2

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

Sensitivity Analysis Formula Example2.1jpg

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

 Example2.2jpg

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 maturity.

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 rate are taken to calculate the sensitivity of the bond price.

Sensitivity Analysis Formula Example 1

Therefore, the calculation of Bond Price is as follows.

 Example 1.1jpg

Bond Price =$102,160

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

 Example 1.2jpg

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 manager, 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
1 Shares
Share
Tweet
Share
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
  • 250+ Courses
  • 40+ Projects
  • 1000+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Sensitivity Analysis Formula Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More