Tag Archives: data table

Today’s topic is how to create a data table.

One of the things they teach us early in business school is to think broadly about our decisions. We are taught to think about best-case scenarios, worst-case scenarios, and everything in between. Excel can be very useful in exploring a number of different situations from a quantitative standpoint, and data tables are one of the main tools you can use to quickly and easily explore such scenarios.

Data tables allow you to see what the outcome would be if you changed one (or two) of your input variables. This means that instead of manually changing a cell one number at a time to see what would happen, you can list a large range of possible values for your input variable(s) and Excel will calculate the result for you.

Here is an example:

1. Download the example spreadsheet here
2. Make sure you are on the sheet titled “One Variable Data Table”
3. Using the 8% discount rate, we can see that if service was expanded the NPV for 5 years would be $1,147 209. However, we are not sure if 8% is correct. We would like to see what the NPV would be if the discount rate was as low as 6%, as high as 10%, and everything in between.