Financial Modeling Tutorials

- Financial Modeling Basics
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Sensitivity Analysis
- Capital Budgeting Techniques
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Present Value vs Future Value
- Annuity vs Pension
- Present Value of an Annuity
- Doubling Time Formula
- Annuity Formula
- Annuity vs Perpetuity
- Annuity vs Lump Sum
- Deferred Annuity Formula
- Internal Rate of Return (IRR)
- IRR Examples (Internal Rate of Return)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- NPV Profile
- NPV Examples
- PV vs NPV
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Simple Interest vs Compound Interest
- Simple Interest Formula
- CAGR Formula (Compounded Annual Growth Rate)
- Effective Interest Rate
- Loan Amortization Schedule
- Mortgage Formula
- Loan Principal Amount
- Interest Rate Formula
- Rate of Return Formula
- Effective Annual Rate
- Effective Annual Rate Formula (EAR)
- Daily Compound Interest
- Monthly Compound Interest Formula
- Discount Rate vs Interest Rate
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Average Formula
- Average Rate of Return Formula
- Mean Formula
- Mean Examples
- Population Mean Formula
- Weighted Mean Formula
- Harmonic Mean Formula
- Median Formula in Statistics
- Range Formula
- Outlier Formula
- Decile Formula
- Midrange Formula
- Quartile Deviation
- Expected Value Formula
- Exponential Growth Formula
- Margin of Error Formula
- Decrease Percentage Formula
- Percent Error Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Cost Benefit Analysis Examples
- Cost Volume Profit Analysis
- Opportunity Cost Formula
- Opportunity Cost Examples
- Mortgage APR vs Interest Rate
- Normal Distribution Formula
- Standard Normal Distribution Formula
- Normalization Formula
- Bell Curve
- T Distribution Formula
- Regression Formula
- Regression Analysis Formula
- Multiple Regression Formula
- Correlation Coefficient Formula
- Correlation Formula
- Population Variance Formula
- Covariance Formula
- Coefficient of Variation Formula
- Sample Standard Deviation Formula
- Relative Standard Deviation Formula
- Standard Deviation Formula
- Volatility Formula
- Binomial Distribution Formula
- Quartile Formula
- P Value Formula
- Skewness Formula
- R Squared Formula
- Adjusted R Squared
- Regression vs ANOVA
- Z Test Formula
- F-Test Formula
- Quantitative Research

Related Courses

**Sensitivity Analysis (Table of Contents)**

## What is Sensitivity Analysis?

The term “sensitivity analysis” refers to the method applied in the determination of how the independent variable or variables eventually influence a particular dependent variable under a certain set of assumptions. Sensitivity analysis is also referred to as “what-if analysis”. It aids in the analysis of the sensitivity of the output is due to the changes in one or more input while the other inputs are kept constant.

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 = X**

^{2}+ Y^{2}### Explanation of the Sensitivity Analysis Formula

The formula for sensitivity analysis can be computed by using the following steps:

**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 is required to be sensitized as they are key to the output formula. In the NPV formula, the cost of capital and the initial investment can be the independent variables.

4.9 (927 ratings)

**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 the columns.

Range of 1^{st} independent variable

Range of 2^{nd} independent variable

**Step 5:** Next, go to “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 the reference to the 1^{st} independent variable and the “Column input cell” with the reference to the 2^{nd} 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 of Sensitivity Analysis Formula (with Excel Template)

Let’s see some simple to advanced examples of Sensitivity Analysis in excel to understand it better.

#### 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^{2} + 7^{2} = 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 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 is taken to calculate the sensitivity of the bond price.

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. For the further procedure of sensitivity analysis calculation refer to the given article here – Two-Variable Data Table in Excel

### 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 downloadable excel template. You can learn more about financial modeling from the following articles –

- What is the Marginal Cost of Capital?
- One Variable Data Table in Excel
- Regression Excel Analysis
- Financial Modeling 3 Statement Templates
- Cost-Benefit Analysis Steps

- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion

- Basic Microsoft Excel Training
- MS Excel 2010 Training Course: Advanced
- Microsoft Excel Basic Training
- Microsoft Excel 2013 – Advanced
- Microsoft Excel 2016 – Beginners
- Microsoft Excel 2016 – Advanced

## Leave a Reply