Data Tables

Performing what-if analysis in business is very common, crucial even. What if a new project is delayed by 3 months? Or what if the company is unable to secure financing at the projected cost of capital? Successfully answering questions like these often means the difference between a business finally turning the corner and becoming profitable and, well, it not. But this type of analysis is also helpful for individuals. Far from overly complicated but seldom used by consumers, tools that allow for thoughtful consideration of important personal finance decisions should be used much more often then they are. They can help one understand how much more a car will cost over time or what term and interest rate combination is required to make sure the monthly payment fits into the family budget. They can even reveal how your credit scoreis helping or hurting you.

Data Tables, one of the features in Excel's What-If-Analysis Package, is a very useful tool for comparing.

So let’s dig in. How would you perform this type of analysis? What tools would you use? Microsoft Excel, of course! Until recently, I’d approach this—or any other 2 dimensional, two variable scenario– by building a grid in Excel with all of the intersection values. For example, I might look at an auto loan with six possible interest rates, 5% to 10%, and five different possible terms, ranging from one to five years. To fill out the grid, I’d utilize the power within absolute and relative references to allow easy copy and filling across all rows and columns of the grid. Lets’ say the interest rates were represented on the vertical axis (Column A, rows 2 through 7) and the terms along the horizontal axis (Row 1, columns B through F). Any formula that used these two variables, then, should make sure to lock the column and row respectively, allowing quick and easy duplication across all cells within the grid. If we were using Excel’s built in PMT() function, for example, we could put the following in Cell B2:

PMT(rate,# periods, price as negative number)
PMT($A2/12,B$1*12,-$A$1),
where $A$1 represents the cell in which you place the price of the item (e.g. car)
Note: Since the loan is likely compounded monthly, you must multiply the term (in years) by 12 and divide the interest rate by the same, 12.

Simple enough, right? But there is an even easier method called Data Tables. Data Tables are a feature in Excels’ “What-if-Analysis” package that allows you to quickly fill in values inside of a table (or grid) based on any formula you feed it. There are many benefits to using Data Tables versus absolute and relative references. For one, you don’t have to remember where to put the dollar signs that are necessary for absolute referencing. While an understanding of this concept is important for advanced modeling and analysis, those who dislike or just don’t use them frequently enough to be comfortable can easily avoid them in these situations. Also, while any change to the formulas in the table would require the update/revision of several cells if using the absolute/relative reference option; Data Tables require the change in only one cell, to only one formula. These should be reason enough to add Data Tables to your toolbox.

Here are step by step directions to creating a Data Table:

1. If you have any variables that may change, but will be common across all values/cells in the grid, create cells to hold them. In our example, we’ll create a cell for the total of the loan or price of the auto.
2. Create separate fields/cells to hold the two variables you are measuring or analyzing. These are the two variables that will be changed in the “What-if” Analysis. In our example, the loan term and interest rates are the two variables.
3. Setup a grid with rows and column headings. Place in these cells the different values you’d like to analyze. In our case, we have interest rates as the row headers along the vertical axis and terms (in years) as column headers along the horizontal access. Note: it doesn’t matter which variable you select for the horizontal and vertical axes.
4. In the intersecting (blank) cell for the row and column, enter the formula you are analyzing across the different 2-variable combinations. Be sure to refer to the cells created in Step 2 within the formula.
5. Test/review the result to make sure the formula is working correctly
6. Select the entire grid, including the intersecting/upper-leftmost cell that contains the formula.
7. In the Data Tab and Data Tools Ribbon, Select What-if-Analysis/Data Table
8. For “Row Input Cell” enter the cell from Step 2 that represents the values listed as Column Headers. (To avoid confusion, please note that column headers are all listed along the same row.) In our example, this is the term.
9. For “Column Input Cell” enter the cell from Step 2 that represents the values listed as Row Headers. (To avoid confusion, please note that row headers are all listed in the same column.) In our example, this is the interest rate
10. Press the “OK” button
11. Change the number format of the values as desired

And that’s it. Now, instead of having to change the formula in several cells, you can simply change the formula in one. To help you further understand, I’ve attached a file (Data_Tables) that shows both the absolute and relative reference method (sheet titled “RefsGridExample”) and the data tables method (sheet titled “DataTableExample”) to what-if analysis. For a side by side comparison of the two methods, as well as two separate examples—calculating the monthly payment or the total interest paid of the life of the loan (ouch!)—I’ve also included a third sheet, “DataTables_v_AbsRelRef”. On it, I’ve also added some custom formatting. With the change of the budget (how much you’re able to spend on a car payment), all of the term/rate combinations that are out of budget (in the red) are identified/highlighted. Enjoy and, as always, feel free to contact me.