How to do Sensitivity Analysis in Excel: One & Two Variable Data Table
Originally published: 22/05/2018 15:38
Publication number: ELQ-22874-1
View all versions & Certificate
certified

How to do Sensitivity Analysis in Excel: One & Two Variable Data Table

Looking at Sensitivity Analysis in Excel for professionally modeling Discounted Cash Flow.

  • Step n°1 |

    One-Variable Data Table Sensitivity Analysis in Excel

    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 –


    DDM Formula - Constant Growth Rate
    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 with respect to the Expected Return (ke). There are two ways of doing this –
    -Donkey way

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
  • Step n°2 |

    Donkey Way

    Sensitivity Analysis in excel using Donkey way is very straightforward, but hard to implement when a 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.

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
  • Step n°3 |

    Using One Variable Data Table

    The best way to do sensitivity analysis 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 of the different variations together on your worksheet. Below are the steps that you can follow to implement a one dimensional sensitivity analysis in excel.

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
  • Step n°4 |

    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.

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
  • Step n°5 |

    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.

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
  • Step n°6 |

    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

    How to do Sensitivity Analysis in Excel: One & Two Variable Data Table image
add_shopping_cartContinue reading for free (70% left)

Reviews

  • Be the first to review this How-To Method

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Dheeraj Vaidya CFA FRM

See all
keyboard_arrow_leftkeyboard_arrow_right

Any questions on How To Do Sensitivity Analysis In Excel: One & Two Variable Data Table?

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...