Let us take the Financeexample (Dividend discount model) below to understand this in detail.
Constant growth DDM gives us the Fair value of a stock as a present value of an infinite stream of dividends growing at a constant rate.
Gordon Growth formula is as per below –
Where:
- D1 = Value of dividend to be received next year
- D0 = Value of dividend received this year
- g = Growth rate of dividend
- Ke = Discount rate
Now, let’s assume that we want to understand how sensitive the stock price is concerning the Expected Return (ke). There are two ways of doing this –
- Donkey way :-)
- What if Analysis
#1 – Donkey Way
Sensitivity Analysis in Excel using Donkey’s way is very straightforward but hard to implement when many variables are involved.
Do you want to continue making this given 1000 assumptions? Not!
Learn the following sensitivity analysis in excel technique to save yourselves from trouble.
#2 –Using One Variable Data Table
The best way to do sensitivity in excel is to use Data Tables.Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all variations on your worksheet.
Below are the steps that you can follow to implement a one-dimensional sensitivity analysis in excel.
1. Create the table in a standard format.
In the first column, you have the input assumptions. In our example, inputs are the expected rate of return (ke). Also, please note a blank row (colored in blue in this exercise) below the table heading. This blank row serves an important purpose for this one-dimensional data table, which you will see in Step 2.
2. Link the reference Input and Output as given the snapshot below.
The space provided by the blank row is now used to provide input (expected return Ke) and the output formula. Why is it done like this? We will use What if Analysis; this is a way to instruct Excel that for the Input (ke), the corresponding formula provided on the right-hand side should be used to recalculate all the other input.
3. Select the What-if Analysis tool to perform Sensitivity Analysis in Excel.
It is important to note that this is sub-divided into two steps.
1. Select the table range starting from the left-hand side, starting from 10% until the lower right-hand corner of the table.
2. Click Data – What if Analysis – Data Tables
4. Data Table Dialog Box Opens Up.
The dialog box seeks two inputs – Row Input and Column Input. Since there is only one input ,Ke, is under consideration, we will provide a single column input.
5. Link the Column Input
In our case, all input is provided in a column, and hence, we will link to the column input. Column input is linked to the Expected return (Ke). Please note that the input should be linked from the source and Not from the one inside the table..
6. Enjoy the Output
