Sensitivity analysis helps us study the uncertainty in the output of the model with the changes in the input variables. It primarily does stress testing of our modeled assumptions and leads to value added insights.
In the context of DCF valuation, Sensitivity Analysis in excel is especially useful in finance for modeling share price or valuation sensitivity to assumptions like growth rates or cost of capital.
In this article, we look at the following Sensitivity Analysis in Excel for DCF Modeling professionally.
#1 – One Dimensional Sensitivity Analysis
Let us take the Finance example (Dividend discount model) below to understand this one in detail.
Constant growth DDM gives us the Fair value of stock as present value of an infinite stream of dividends that are growing at a constant rate.
Gordon Growth formula is as per below –
- 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 with respect to the Expected Return (ke). There are two ways of doing this –
- Donkey way
- What if Analysis
#1 – Donkey Way
This one is very straight forward, but hard to implement when lot of variables are involved.
Do you want to continue doing this given 1000 assumptions? Obviously Not!
Learn the following sensitivity analysis in excel technique to save yourselves from the trouble.
#2 – Using Data Table – What-if Analysis – One Dimensional Data Table
Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all of the different variations together on your worksheet. Below are the steps that you can follow to implement a one dimensional sensitivity analysis.
Step 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 that there is a blank row (colored in blue in this exercise) below the table heading. This blank row serves important purpose for this one dimensional data table which you will see in Step 2.
Step 2 – Link the reference Input and Output as given the the snapshot below.
The space provided by the blank row is now used to provide input (expected return Ke) and the output formula. Why it is done like this?
We are going to use “What if Analysis”, this is a way to instruct excel that for the Input (ke), corresponding formula provided on the right hand side should be used to re-calculate all the other inputs.
Step 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
- Select the table range starting from the left hand side starting from 10% until the lower right hand corner of the table.
- Click Data -> What if Analysis -> Data Tables
Step 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 under consideration, we will provide a single column input.
Step 5 – Link the Column Input
In our case, all input are 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 original source and Not from the one that is inside the table
Step 6 – Enjoy the Output
#2 – Two Dimensional Sensitivity Analysis in Excel
Data tables is very useful for Sensitivity analysis in DCF. Once a base case is established, DCF analysis should always be tested under various sensitivity scenarios. Testing involves examining the incremental effect of various changes in assumptions (cost of capital, terminal growth rates, lower revenue growth, higher capital requirements, etc) on the fair value of the stock.
Let us take the sensitivity analysis finance example of Alibaba Discounted Cash Flow Analysis.
With the base assumptions of Cost of Capital as 9% and contant growth rate at 3%, we arrived at the fair valuation of $191.45 billion.
Let us now assume that you do not fully agree with the Cost of Capital Assumptions or the growth rate assumptions that I have taken in Alibaba IPO Valuation. You may want to change the assumptions and access the impact on valuations.
One way is to change the assumptions manually and check the results of each change. (codeword – Donkey method!)
However, we are here to discuss much better and efficient way to compute valuation sensitivity that not only saves time, but also provides us with a way to visualize all the output details in a effective format.
If we perform the What-if analysis in a professional way on the above data, then we get the following output.
- Here, row inputs consists of changes in Cost of capital or WACC (7% to 11%)
- Column inputs consists of changes in growth rates (1% to 6%)
- The point of intersection is Alibaba Valuation. For eg. using our base case of 9% WACC and 3% growth rates, we get the valuation as $191.45 billion.
With this background, let us now look at how we can prepare such a sensitivity analysis in excel using two dimensional data tables.
Step 1 – Create the Table Structure as given below
- Since we have two set of assumptions – Cost of Capital (WACC) and Growth Rates (g), you need to prepare a table given below.
- You are free to switch the row and column inputs. Instead of WACC, you may have growth rates and vice-versa.
Step 2 – Link the Point of Intersection to the Output Cell.
The point of intersection of the two inputs should be used to link the desired output. In this case, we want to see the effect of these two variables (WACC and growth rate) on Equity value. Hence, we have linked the intersecting cell to the output.
Step 3 – Open Two Dimensional Data Table
- Select the table that you have created
- Then click on Data -> What if Analysis -> Data Tables
Step 4 – Provide the row inputs and column inputs.
- Row input is the Cost of Capital or Ke.
- Column input is the growth rate.
- Please remember to link these inputs from the original assumption source and not from anywhere inside the table
Step 5 – Enjoy the output.
- Most pessimistic output values lies on the right hand top corner where Cost of Capital is 11% and growth rate is only 1%
- Most optimistic Alibaba IPO Value is when Ke is 7% and g is 6%
- The base case we calculated for 9% ke and 3% growth rates lies in the middle.
- This two dimensional sensitivity table provides the clients with easy scenario analysis that saves lot of time.
#3 – Goal Seek
- The Goal Seek command is used to bring one formula to a specific value
- It does this by changing one of the cells that is referenced by the formula
- Goal Seek asks for a cell reference that contains a formula (the Set cell). It also asks for a value, which is the figure you want the cell to equal
- Finally, Goal Seek asks for a cell to alter in order to take the Set cell to the required value
Let us have a look at the DCF of Alibaba IPO Valuation.
As we know from Discounted cash flow that growth rates and valuation are directly related. Increasing the growth rates increases the share price of the stock.
Let’s assume that we want to check at what growth rate will the stock price touch $80?
As always, we can do this manually by changing the growth rates continuously to see the impact on the share price. This will again be a tedious process, we may have to input growth rates many times to ensure that the stock price matches $80 in our case.
However, we can use a function like Goal Seek to solve this in easy steps.
Step 1 – Click on the cell whose value you wish to set. (The Set cell must contain a formula)
Step 2 – Choose Tools, Goal Seek from the menu, and the following dialog box appears:
- The Goal Seek command automatically suggests the active cell as the Set cell.
- This can be over-typed with a new cell reference or you may click on the appropriate cell on the spreadsheet.
- Now enter the desired value this formula should reach.
- Click inside the “To Value” box and type in the value you want your selected formula to equal
- Finally, click inside the “By Changing Cell” box and either type or click on the cell whose value can be changed to achieve the desired result
- Click the OK button and the spreadsheet will alter the cell to a value sufficient for the formula to reach your goal.
Step 3 – Enjoy the output.
Goal Seek also informs you that the goal was achieved
Sensitivity analysis increases your understanding of the financial and operating behavior of the business. As we learnt from the three approaches – One Dimensional Data Tables, Two Dimensional Data Tables and Goal Seek that sensitivity analysis is extremely useful in the finance field especially in the context of valuations – DCF or DDM.
However, you can also get a macro level understanding about the company and industry in general. You can develop cases to reflect valuation sensitivity to changes in interest rates, recession, inflation, GDP etc on the valuation. Thought and common sense should be employed in developing reasonable and useful sensitivity cases.
If you learned something new or enjoyed the post, please leave a comment below. Let me know what you think. Many thanks and take care. Happy Learning!