Tip:
Highlight text to annotate it
X
Being sensitive is so important. So in this video I'll teach you all about sensitivity
analysis and how to run numerous financial modeling scenarios using Excel Data Tables.
Sensitivity analysis means that you're trying to test what happens to one model output,
when you change one or more model inputs For example we can take a look at what happens
to 2014 projected EPS when we change the revenue growth or the gross margin
Now if we increase 2014 organic revenue growth to 5% from 2%, Cash EPS rises to 77 cents.
Similarly, if we increase the gross margin to 95% cash EPS increases again to 81 cents
A sensitivity analysis would usually look at several different such scenarios, typically
at least three: a baseline scenario, an upside scenario and a downside scenario
Excel Data Tables are a quick and painless way to run sensitivities on numerous scenarios
instead of manually changing inputs as we just did
To set up a data table, we first link the statistic we want to sensitize to somewhere
where there is enough space for a three-by-three matrix
In this case we'll take 2014 Cash EPS and link that to the right of our model using
the paste special command We then choose different levels for the model
inputs we're trying to sensitize We'll go with 1%, 2% and 5% for sales growth
and 90%, 92.5% and 95% for gross margin Now to calculate our data table we first highlight
the entire matrix using shift and the arrow buttons
Then press ALT A to get to the Data Menu then W to access the What-If-Analysis submenu and
T for Data Tables In the Data Table pop-up you'll see the first
thing that you have to enter is the Row Input Cell, so you'll go in our case to cell E4
where sales growth is modified in our model The logic here is that in our Data Table we've
entered the three differrent values we're testing for 2014 sales growth the 1% downside
case, 2% base case and 5% upside case into one row, that's why our Row Input is going
to be the cell in our model where sales growth is modified, again that's cell E4 in our model
We hit the Tab Key to move to the Column Input Cell then arrow to cell E9
E9 is the cell where our other input, the Gross Margin, is modified in our model. Again
E9 is the Column Input Cell because the three different values we're running as Gross Margin
scenarios are all entered into one column in our Data Table
We can now hit enter and you see the data table calculates automatically
We'll format our Data Table a bit for better readability, copying down existing formats
using the paste special command we discussed in our last video
Now looking at the table you can see 9 different 2014 Cash EPS scenarios
It's now very important to check that your Data Table is calculating correctly.
You see for example that at 2% Sales Growth and a 90% Gross Margin our data table shows
that Cash EPS should be 68 cents, so if we go up to our model and punch in 2% for sales
growth and a 90% Margin you see here the cash EPS shows 68 cents, so we're confident that
our data table is calculating correctly If you liked today's video and would like
to learn more about financial modeling, click the link right now and sign up for our free
intro to financial modeling