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.

It helped me with my HW so much. Thank you! 🙂

May I ask for the excel sheet just to understand better? thanks!

Very confusing video…

The Original Chemical Process case is simple to illustrate the notion of SA.

Please update this presentation or remove it simply!

Very informative, I really appreciate it.

Thanks a lot

It was the best explanation I came across. Thanks.

Thank your for the graphical explanation. Is there a quantitative way to measure variable importance?

God bless you!

Thank you so much!!! Have been struggling to find what I was looking for, and here it is, finally! Thank you for being straight to the point – extremely helpful when running out of time 😀

very nice

This was really helpful. Thank you so much for putting this up in public domain.

Thanks Man , Awesome !

Thanks bro! this helped me a lot

good job!

clear and informative, really appreciate!!!xxxxx

Great video, thanks.