This screencast is going to go over a sensitivity analysis, and we're going to generate a tornado
plot.
A sensitivity analysis is basically a study into how sensitive is the process, so the
process outputs, to the inputs.
So just as an example here we have a process, and it's got a bunch of inputs, and it may
have one or more outputs.
An example might be a reactor where the inputs would be things like the temperature, the
pressure, or maybe the size, the flow rate, concentration of different things, and so
on, and the outputs might be conversion, just a simple example, and we want to determine
how sensitive the outputs of the process are to the inputs.
The specific example I am going to be working with has to do with net present value.
We've made a screencast on this already, and you don't necessarily have to understand this
net present value example, it has to do with engineering economics, and determining the
net present value of a venture 15 years from the current time, and we have different inputs
to the process.
The inputs would be things like the cost of land, cost of royalties per year, the total
depreciable capital, that's how much you have to invest in major equipment, working capital,
startup costs.
We have sales, which is s, other inputs include tax because tax rates might change, and cost
of sales, here we have 6 million and we have an interest rate, or the cost of capital.
So again you don't necessarily have to understand exactly what I've done in this spreadsheet
to understand sensitivity analysis, the important thing is that we have a process, and we have
multiple inputs that go into determining what the output is, and for this process the output
is down here, and this tells us that the net present value of a venture based upon on our
base case, or our baseline or nominal values of these different variables up here the net
present value of that is about 58 million.
We're going to now do a sensitivity analysis, we're going to see what happens when we change
different values here for the cost of land, maybe the annual sales, and annual costs,
and so on.
So what happens maybe if we subtract 20 percent from the nominal values and increase 20 percent.
So you'll notice here I have 58.78 million down here, and if I change something like
cost of sales, let's just say to 8, instead of being 58.78 it's a lot less, it's 50.30,
so you can see that this process, the output, which is net present value, depends upon the
input variables, so I am going to put that back to 6, and you know maybe I change land
to instead of 5 million its negative 4 million, but that didn't change it a whole lot, it
was 58.78, and now its 59.78.
So I am going to put that back to minus 5.
We're going to do a data table to look at these different input variables, and what
effect they have on the output.
So we're going to do a sensitivity analysis first on the working capital, the nominal
value, or the baseline value for working capital is negative 20 million, that's how much we're
going to request for working capital, and we want to ask ourselves how sensitive is
the net present value after 15 years, how sensitive is that to the working capital.
So if we have 80 percent that would be minus 16 instead of minus 20, as opposed to 120
percent, which is negative 24, so all I've done here is multiplied our baseline value
of negative 20, which is up here in our spreadsheet.
I've multiplied that by 80 percent all the way up to 120 percent.
We're going to make a data table here.
When you make a data table we have a column of different inputs that we're going to do
kind of a case study on.
The cell one up and one over from our values, this is a pointer formula, so I am going to
do equals, and that's, we're pointing to our net present value, that's the result, and
then what I could do is highlight all of this, of column of cells, plus one row above, and I
am going to go into data, what if analysis, data table, and this is a column data table,
and each of these working capital values is going to be placed into cell B4 up here, and
so I am going to press OK, and it's going to do sort of a case study on that.
I forgot to mention one thing, if I just did a multiplication of cell C33 here, which is negative
20, times the percentage and created a vector here I actually have to copy and paste so
that's not a formula, because if this is a formula and we put that into the data table
it doesn't quite work right, so we have to copy and paste the values so they're just
numbers instead of formulas in this column, before we do the data table.
So this is telling us if the working capital is negative 16 the net present value is about
62.5 million, and if we increase by 20 percent we see that the net present value is about
55 million.
So I can go through all of these different values, and the green here represents the
base line values, so we have working capital which I showed you, I did this for startup
costs, sales, the interest rate, or cost of capital, the land costs, total depreciable
capital, capital, and cost of sales, and what I've done for all of these I've taken the
minus 20, which is our 80 percent of nominal cost, and our 120 percent of that particular
variable, and I've made a summary table here, and what we're going to do now is create something
known as a tornado plot.
To create the tornado plot I am going to highlight one of these rows, I go up here to insert
chart, and we're making a cluster chart, a clustered bar chart, and right now it's not
looking anything like a tornado plot, but bear with me here.
I am going to format this a little, I am going to go over here and add in access titles,
I am going to add in a legend, I am going to go back up here and I am going to copy,
so I am selecting this, Ctrl+C, that's the 120 percent, and I am going to click in the
area, do control paste.
Now again this isn't looking really like a tornado plot, but we have some work to do.
We need to change this, I am going to do format access, and it's going to cross access values,
vertical access crosses axis value at 58.78, that was our nominal value, so if I go back
up here 58.78 is the net present value when we have 100 percent of all those values, that
was our base case.
So now this is sorta looking like a tornado diagram, and I am going to click on one of
these series, format data series, we're going to do 100 percent overlap, OK, sorta eclipsing,
and I am going to make this a little bigger, I am going to decrease the gap width, maybe
something around 60 percent, we can further modify this, so I am going to right click
on this axis, format axis, let's change the number to be 0 decimals, I am also going to
click on the category over here, and we will format that, so I right click on this, format
that axis, I am going to change the labels so they are low, what that tells us is it
brings those labels to the left side.
Another thing I am going to do is change those labels, so I am going to do select data.
Instead of these 1, 2 through 8 I am going to edit that, and that's going to be named
our categories up here.
So I can do that, click OK, and it just added those different categories.
And the last thing we need to do is to change our legend, so I am going to bring the legend
inside here so I can expand this a tiny bit, and I can right click in here and do select
data, and I am going to change this to minus 20 percent, editing these series, and this
will be plus 20 percent, and we're pretty much done, so that is a tornado diagram, and
what this tornado plot shows us is that if we change, for example sales, if that goes
down by 20 percent of our baseline then that has a huge effect on the net present value.
Same thing with if we increase sales by 20 percent that has a very big effect on net
present value.
Some other things that don't have as big of effect you see that C land doesn't have a
big effect. If your land costs very tremendously that's not going to have a huge effect on net present
value at all, but if your sales are off of what your anticipating then this can have
a huge effect on the net present value, so that's varying from around 20 million to 100
million, which is a huge difference there, and your boss might say, you know if you gave
him this sensitivity plot your boss might say "well, we need to put a lot more effort
into making sure we have a really good estimate on sales, because if sales are 20 percent
lower than what we're expecting then our profitability of this venture is way lower than if our sales
are 20 percent higher than what we're expecting."
This sort of tells you what are the main players in your output, and the output in this case
is net present value, and if you really wanted to you could organize this, you could put
the big bars up at the top, and the small ones at the bottom, and it sort of looks like
a tornado, so that's what the tornado plot gets its name.
OK, thanks for watching this screencast.