In this screen cast, I'm going to show you how to perform a sensitivity analysis. I've got this in a file called Cookies- STARTER.xlsx. It's a starter file and you can work along with me. A sensitivity analysis is basically looking at all of the inputs and perturbing or changing them by a certain fraction or factor. Here, we've got a cookie recipe. We've got the ingredients over here. We've got the base costs. I've got a factor here right now these are all just set to be one. And we're going to be changing these in our sensitivity analysis. I've got the amounts required by the recipe, I've got any needed conversion factors over here is completely fine if you don't really understand this. I've got the density that just converts it from pound for example to cups because we have our base costs, sometimes in units of mass over here. So I'm just doing some conversions. The important thing over here is the total cost per batch. We've got the total cost for flour, baking soda and so on, per batch, and when we sum all that, this 652 here is the total costs for a batch of cookies. And this is known as our base case. Our base case is when all the factors are at their level one. So basically a hundred percent. And then what we're going to do is we're going to change or perturb these by a specified amount. And we're actually going to start out with a change of 25%. And we're going to do one at a time, and we're going to see what effect it has on increasing or decreasing each of our ingredients or factors by 25%. So the first thing I'm going to do is over here when all of our factor levels are at one, this is our base case. And I'm just going to go ahead and copy and I'm going to pay paste here, we're going to paste special. So this is not a formula cell Kate 14 is a formula and I just want to take the value, we see that that's 652. And it's just a fixed value, not a formula. So what a sensitivity analysis tells us, if I perturb let's say let's just say salt, or let's just say butter. If I increase this by 25%, I bring that factor from 1 to 1.25. That means if our base cost of 75% increases by 25%, then what effect is it going to have on the total cost of our batch? So if I press enter, it's increasing, it increased by 38 cents. So that's quite a bit. It seems that our cost per batch is quite sensitive to our buttercross. So let's put that back to one. Another striking example is let's just say chocolate chips go down by 25%. So instead of being to 28 per bag, they are 75% of that. You see that our total cost decreases by quite a bit. In other words our cost is quite sensitive to chocolate chip cost. I'm going to put this back to one. Let's look at something that the total cost is not very sensitive to at all. And that is salt. So let's just say maybe our salt costs go up by 25%. Then we can press enter, and you're seeing that we're only increasing the total cost by about a penny. So we would say that the cost of this batch, the total cost is very sensitive to salt. So in this way we can investigate what happens if our ingredients go up or down in cost. So maybe for whatever reason, it gets more difficult to make sugar so sugar costs go up by 25%. Or maybe sugar becomes cheaper, vanilla becomes more expensive. So you can kind of look to see which of our ingredients or factors that go into your process is your outputs most sensitive to. And in our case, our output is the total cost per batch. So let's go ahead and solve this. I've kind of got this template down here I've got this chart that will automatically complete once we put in the information in this data table. So the first thing I'm going to do is we have low and high and by the way, cell B 16, I've named change. We're going to use that. So it's kind of like our percent change, the user can change this percent to 30 or 5%. It kind of just gives us an ability to look at plus and minus for our sensitivity analysis. So the first thing I'm going to do down here, is I'm just going to do one minus change. And I've formatted this to be a percentage. I'm also going to fill out the high. So here that's 1 plus change. So we're looking at a 75% as our low value, and 125% as our high. And again, it's adaptive. So if I change this to 10, we're going from 90 to 110. Now let's go into this Low/High box. I'm going to add in data validation. We don't have to do this but I like to do it. So I'm going to add in data validation into cell B 18. This is just going to be a simple list and it's just going to be these two values we can either have a 90 or 110 for a low and our high. And so now the user can select between 90% and 110. And if we change this back to 25, the user can select between 75 and 125. Let's put a drop down data validation list here in cell B 17. I can do the same thing. We're going to add in a list. The source is going to be all of our ingredients and click OK. And now the user can select an ingredient. So let's just go down to something. Let's just do flour. Now what we need to do is we need to make it so the factor here is going to change depending upon what we select down here. So right now down here I have flour at its low level, and I want these formulas to automatically update depending upon where we select. I want cel deform to be point seven, five. So if flour is selected, I want this to be point seven, five, or if the user selects 125%, I want this factor to be 1.25. So what I'm going to do is I'm going to change these formulas here instead of one I'm going to use a couple if statements. So I'm going to say if the ingredient down here that the user has selected and make that $1 sign is equal to the ingredient over here in column A. So if that's true, then we have another condition that has to be Met so I'm going to put an if statement. And I'm going to say if this value down here that the user has selected, make that an f4 is equal to the high value down here. f4. So if that's true Then we want to factor in cell D four to be one plus change. Now I didn't just put in one plus point two, five, because we want it to adapt to whatever the user puts in cell B 16. Otherwise, so if that's false if the first condition is true flower, but the second condition is false This is not 125% that means it's going to be the low level. Then I'm just going to put in 1 minus change. And then I can close that first conditional. Now we're back to the original if statement. So if that's this is false. If our ingredient in column A does not match be 17. Then we're just going to put a 1, we just want that factor level to be at its nominal or base rate 100%. I can press Enter. And now we can just take this formula and copy it down and let's just make sure it works. So let's look at Salt Right now it's at 125. You see this updated 1.25 and now we've got 75% and that drops it down by 25%. Now it's really important, that you're not modifying anything in the contents of this table. I've already done that for you. But what this means. Is that if we take chocolate chips, and if they increase in costs for whatever reason by 25% that means that our total batch is going to be $7 instead of our base case of 652. So again, as I showed earlier, This recipe is very sensitive to the cost of chocolate chips. But if we look at something like baking soda, if baking soda goes down by 25%, we're only decreasing total cost by one penny, and so it's not very sensitive to baking soda costs. Now the last thing we're going to do is we're just going to perform a quick case study. This is a two way data table. We're going to fill out this with the low and the high. The way we do this, this is a two way data table in this cell We're going to just put in a linker formula to sell k14. That's where we're getting our result. If you want more information on data tables in part two of everyday excel in the what if analysis section I talk more about one way and two way data tables. I can select this entire region here, we go up to the Data tab. What if analysis Data Table, our low and high values here that's known as our row input vector. And that goes into cell B 18. Our column input vector here is our ingredients and that's going to go into cell B17. And when I press enter, we're going to do all combinations of selecting in cell B17, all these ingredients, selecting and cell B18 75%, 125. And when we do that We get the corresponding results. So again as an example if sugar goes down by 25% the total cookie batch cost is 642. If sugar goes up by 25% 663 Three. And I've created this plot here it's known as a tornado plot because a lot of times you'll put the bigger items at the top and you'll sort and they kind of looks like a tornado. But what we can see this is our batch Cost at the low values. So over here if the cost of that ingredient goes down by 25%, and again, we're looking at one ingredient at a time, then these are going to be the batch cost. So very sensitive. They have two chocolate chip costs. And if those costs increased by 25%, then these will be the batch costs. So this kind of shows our sensitivity analysis, and we can change this I can change it back to 15%. And there's our updated on the fly dynamic tornado plot. So this is going to help you a lot with the Sensitivity analysis problem. You should be able to adapt that problem and do much of what I just did for this cookie recipe problem. Good luck.
