Data Tables in Excel: Visualizing the range of possible outcomes

Excel is a great tool for decision makers to ask, “What if?” What happens to the outcome if you change something? Optimize your choice. What happens to the outcome if something beyond your control changes? Prepare for whatever fate might throw at you.

Make a spreadsheet to figure the outcome. Change the inputs and see what you get. Or you can add a data table to look at the effects of a whole range of inputs. Here’s an example.

In the last post, I showed a spreadsheet that figures whether or not the fund you start for your baby will be enough to pay for college when the kid is 18. The inputs are the amount you put in the fund to start, the amount you add to the fund each year, the fund’s growth rate, the growth rate of college costs and the cost of four years of college. The output is the shortfall, the college costs that the fund won’t cover. You can try different inputs for the amount you add to the fund each year and see how the outcome changes. Better yet, use a data table to see a lot of possibilities at once.

I’ll use a one-dimensional data table with one input, organized in a column. Here’s how you do it. First list all the values of the input you want to check in a column and leave empty cells just to the right it for the corresponding output values that Excel will calculate for you. In the cell just above these empty cells, put a formula to calculate the output. Then select the whole table, including the formula but not the headers. Choose Data Table from the Data menu or on the Data tab, in the Data Tools group, click What-If Analysis, and then click Data Table. In the dialog box that appears, enter the cell with your input in the Column input cell and leave the Row input cell blank.

Here I’ve added a table in columns F and G to consider amounts to add to the fund each year, from nothing to $20,000. I’ve used AutoFill to list all the values from $0 to $20,000 in $1000 increments in F14:F34. I put column headers in F13:G13 for my input (the yearly amount) and output (the resulting shortfall in the 18th year). Now I need to tell Excel how to calculate the output. There’s not a simple formula to do that—it’s the whole spreadsheet. But that actually makes it easy. The output is the final shortfall in cell D33. The formula is simply =D33 and I enter that at the top of the Shortfall column in the data table, G14. I select F14:G35, call up Data Table, put my input B9 in for the Column input cell and click OK. Excel automatically fills in the entire Shortfall column.

Now I can see how changing the yearly amount changes the shortfall at college time. (Note that cell G14, italicized, is there to hold the formula–it’s not part of the table.)

This is an example of using a data table to think about your options and the effect of each one. You can also use it to think about your risks. One of those is the rising costs of college. What if it went up by more than 9% a year? Make a table to examine shortfall versus the cost growth of college and you’ll see that the cost growth is a major factor. You’ll probably want to keep an eye on college costs over the years and adjust your savings accordingly.

Data tables are admittedly confusing, but well worth the trouble to figure out. You can even use them to make what is called a Monte Carlo model to look at random occurrences or fluctuations and assess your risks. If anyone is interested in that, please add a comment and I’ll describe it in a future post.

How have you used tables?

Leave a reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s