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:
=(Revenues*((1+RevGrowth)^Years))-(Expenses*((1+ExpGrowth)^Years))
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.