Sensitivity Analysis Using Data Tables In Excel

A data table in Excel is a kind of What-If scenario. The table allows you to create instant results based on two variables which change along the rows and columns of the table.

Let’s say we’re trying to predict the number of periods until a currently unprofitable company becomes profitable. We have a goal for managing expense growth rates and a goal for increasing sales growth rates but of course, we don’t know whether or not we’ll hit our targets! A data table can show us at a glance the effects of missing and/or exceeding our target numbers.
In a new Excel spreadsheet create an area to hold our assumptions. You can create this region yourself, or download the sample spreadsheet.

We need figures in year ‘0’ (the current year) for Revenues, Expenses, Revenue Growth, and Expense Growth. We include the year of interest at the bottom so that we can quickly change the year of interest to see how the company would be doing for a particular year given a variety of revenue and expense estimates.

  Amount Growth Rate
Revenues $1,000,000 15.0%
Expenses $1,700,000 3.0%
Year 0  

Be sure to name your ranges; we’ll be using the names to create a cumulative profit formula.
We then ‘surround’ our growth targets with higher and lower estimates as shown below. We place our formula for cumulative profit at the top left corner of the table, using the named ranges we created above: 


Lastly, we formatted the center cells with some shading and borders so that we know exactly what figure represents our target.
Now let’s create the data table itself. Highlight the cells beginning with our formula all the way down to the last intersection of growth rates. If you like shortcut keys, just click on the formula and click Ctrl * or Crtl-Shift 8.

Now go to Data > Table… or go to the Data ribbon in Excel 2007 > What If… Analysis and Data Table. You should see the dialog box below. The data table wants to know what cell reference in your formula should be replace with the values in the title row and what values in your formula should be replaced in the title column.

We want to replace the variable Revenue Growth with the figures across the top of the table and the variable Expense Growth with the figures down the side. Click on OK and you’ll see a BIG field of ($700k) values from top to bottom. Not much is happening in Year 0!

Now go to the Year cell and change it to 1… to 2… to 3… and watch as the black ink slowly creeps in from the top right where are revenue growth is highest and our expense growth is lowest. Finally, in year 5, if we stick to our goals, we see a profit.

You can use Goal Seek (mentioned in the last part of the Calculating Growth Rates In Excel  article) to immediately find when you reach break even at your target growth rates. Give it a try.