I learned how to use Excel data tables over the summer, and it’s really coming in handy now for FinD. In case, like me 5 months ago, you are oblivious to how awesome they are, here is a quick rundown.

Purpose: Scenario analysis. They provide a great way to try modifying 1 or 2 values/assumptions to test their impact on an equation.  This makes them useful for doing a sensitivity analysis.

Here is an example of doing a 2-variable data table.

1. Set up the proposed 2-variable data table as shown below. The equation you want to check with all of the different possible values goes in the top left (green cell), the row variable (Cash Received Annually) that you are going to test with different values is in red, and the column variable (Discount Rate) that you are going to test with different values is in blue. Highlight the cells and select the “Data Table…” option.
<div class="separator" style="clear: both;"></div>
2. In the window that pops up, select the cells where you want to plug in all of the different values. Those cells should have an impact on the equation you are testing.

<div class="separator" style="clear: both;"></div>
3. Voila! The value of the equation you are testing, in this case NPV, under each of the different scenarios is automagically filled into the table.

<div class="separator" style="clear: both;"></div>
I know that my instructions are pretty bad, so here is the official Microsoft tutorial that does a much better job of explaining how to use the tables.

If you are going into consulting, file this away under “Probably going to use it in the future.”