Financial Modeling

Sensitivity Analysis Using Data Tables

Microsoft excel provide many wonderful tools to help in building robust financial models. One of these repeatedly used tool is Data Table. Data Table eliminate the need to maintain multiple copies of our financial model based on different input variable or assumptions. In this post you will see, how you can have all the important results of your financial model based on different assumptions. There are two types of Data Table, one variable and two variable. In one variable Data Table, you can have different values of one variable either in rows or columns. You can see the different results of your financial model. In two variable Data Table, you can have different value two variables, one in column and the other in row but you see one result only.

One Variable Data Table

Below is a very basic investment model and you are interested in knowing the NPV at different discount rates.

–Make a table like below — Select the range as per 1

— Click on Data Tab < What If Analysis < Data Table. In Column input cell select the discount rate cell from your model. In Our case it is D5.  Finally click OK.
— Finally you have the NPV at different discount rates

Two Variable Data Table

— Select the range as per below.  Click on Data Tab < What If Analysis < Data Table. In Column input cell select the discount rate cell from your model. In Our case it is D5.  In Row input cell select D3 form our model. Finally click OK. Now you have NPV at different combinations of discount rate and investment.

Download Excel File


Leave a Reply

Your email address will not be published. Required fields are marked *