Sensitivity Analysis and Tornado Plots - YouTube

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.