0:00

Okay. Before we get to into

Â all the different distributions that are possible in this project,

Â I wanted to give you a really good example that I've created.

Â I have a recipe for cookies and we've got

Â nine different ingredients and we want to analyze the profitability of this.

Â Each batch make 60 cookies, so,

Â we have costs of all of the ingredients and we're going to put that into a spreadsheet.

Â We're going to calculate the profit per cookie and then we're going to do a simulation.

Â So, let me take a closer look at this.

Â Flour, we're going assume as a uniformly distributed variable.

Â So, if you go and purchase flour,

Â we're going to assume that it's not constant.

Â So, it's not a reliable price and all of

Â these things we're going to assume are not reliable prices.

Â Even though, for this example,

Â salt and butter, those things are relatively reliable prices.

Â But I'm going to just show the effect of

Â variability on the overall profit per cookie in this example.

Â So, for a uniform distribution,

Â we're saying it's equally likely to pay 45 cents per pound of flour,

Â as it is 70 cents per pound.

Â It's not really centered about a central average,

Â it's just equally likely to pay 45 as 55,

Â as 62 as 48.

Â So, each of those is equally likely.

Â For uniform distribution and for all distributions,

Â we have to remember that the area underneath here is equal to one.

Â So, baking soda, we're assuming is a normally distributed variable.

Â Baking soda then, we're going to assume has an average of 282 per pound.

Â So, if we take a look at baking soda,

Â it has an average of 282,

Â It's a normally distributed variable,

Â so it's going to have a Gaussian curve and

Â then the standard deviation is 50 cents per pound.

Â So these are the average and standard deviation,

Â the mean of $2.82 per pound and the standard deviation of 50 cents per pound.

Â Those are parameters that we can just plug into

Â the Gaussian distribution or the normal distribution to get a probability of a cost.

Â So, it's going to be more likely to get a cost of

Â around 282 than it is above three dollars and below about 250.

Â Salt is also normally distributed,

Â butter is a discrete variable.

Â A discrete variable means that

Â maybe for this example we have 3 different suppliers of butter.

Â They're not always going to be available.

Â So, 25 percent of the time we're going to pay 60 cents a stick,

Â 60 percent of the time we're going to have to pay.

Â So, most of the time we're going to pay 75 cents a

Â stick and 15 percent of the time we paid a dollar per stick.

Â So maybe we have a problem with suppliers.

Â Most of the time we're going to be paying 75 cents.

Â But then occasionally, maybe sometimes,

Â 25 percent of the time there's a cheap supplier

Â of butter and they only charge 60 cents per stick,

Â and then sometimes the cheap nor the middle suppliers are available,

Â and so 15 percent of the time we have to pay really expensive, which is a dollar.

Â So that kind of gives you an idea of discreetly distributed variable.

Â Sugar, we're going to assume is uniform similar to flour that we talked above.

Â Brown sugar is normally distributed.

Â Vanilla, we're going to just assume that that's uniformly distributed.

Â Eggs and chocolate chips are similar to butter.

Â For eggs, we're saying 20 percent of the time we can get

Â really cheap eggs at 15 cents an egg.

Â Most of the time,

Â we're paying 25 cents an egg.

Â But sometimes when neither of the 15 cent or 25 cent

Â per egg distributors are available then maybe we have the pay 50 cents per egg.

Â So, 30 percent of the time,

Â we have to pay 50 cents per egg.

Â And chocolate chips, maybe 70 percent of the time you can find chips for 228.

Â But occasionally, they're out, and in that case,

Â we have to pay 295 per bag.

Â So these are our inputs and they have cost

Â per weight and we're going to plug this into an Excel spreadsheet.

Â So I've got this spreadsheet.

Â I got all my ingredients here, the costs,

Â and these right now are just random values that lie in these ranges.

Â I'm just choosing values in these ranges on that spreadsheet.

Â I will explain how we do that here in subsequent screencast.

Â But I've got my cost per pound,

Â the recipe costs for maybe two and a quarter cups of

Â flour and so on, so we have the ingredients,

Â and I'm doing just some conversions here to calculate the total cost per batch.

Â So in flour, we spend 40 cents per batch,

Â five cents on baking soda.

Â So maybe some of you know about sensitivity analyses.

Â And obviously, the price is not very sensitive to salt.

Â If salt goes up or down and a huge percentage is not going to have

Â a big effect on profit because we're only spending two cents per batch on salt.

Â But butter is something that the cost is very sensitive to and same with chocolate chips.

Â So we can calculate the total here that we're going to spend per batch.

Â There are 60 cookies per batch,

Â so we can just divide the total cost by 60 cookies to get the cost per cookie.

Â I'm going to increase the decimal there so we're paying for this particular scenario.

Â This is one simulation.

Â We're paying 10.7 per cookie and maybe we sell it for 25 cents,

Â and then you can calculate the profits.

Â So I'm going to increase that by one decimal point.

Â Alright. So, for this particular scenario,

Â this one simulation, we're making 14.3 per cookie.

Â Now, a Monte Carlo simulation,

Â we choose again just one scenario.

Â For each scenario, we're choosing one point from all of

Â the distributions that describe our ingredients and we're going to do that.

Â So, we're going to run one simulation to calculate the output.

Â The output is the profit per cookie.

Â We tally that, so that's sort of like the result,

Â and then we run thousands of simulations.

Â So I've got my user form here.

Â I'm going to show you. I'm going to run this simulation.

Â You can input the different parameters for all of the ingredients here.

Â For example, the uniform distribution,

Â we have the Min and Max,

Â you can change these around.

Â We can do, for example, discrete distribution here.

Â For a discretely distributed variable here for eggs,

Â we have 20 percent chance of 15 percent per egg,

Â 50 percent chance of 25 cents per egg and so on.

Â And you can modify these if you would like.

Â I'm going to go ahead and do just thousand simulations

Â and I click go, and it's going through.

Â You see here on the spreadsheet is working through and just it's randomly selecting

Â different parameters that are described by

Â each of the ingredients and then it comes up with the result.

Â It has this nice histogram.

Â It has this Bimodal histogram mainly.

Â So if you look at chocolate chips,

Â where there's a 70 percent chance we're paying

Â 228 per bag and there's a 30 percent chance we're paying a lot more.

Â The process is very sensitive to

Â chocolate chips because chocolate chips are very expensive,

Â they're very expensive ingredient.

Â So that's why in this histogram we have kind of this Bimodal distribution.

Â We have our cheap chocolate chips here on the right.

Â We're making a lot of money.

Â This is showing profit.

Â And then we have our more expensive chocolate chips.

Â They shift our distribution to a lower profit.

Â So, you're going to want to implement this histogram in your project and it

Â outputs 87.2 percent of simulations had a profit of greater than 13 cents per cookie.

Â This is just something I just kind of pulled out.

Â Maybe your boss wants to know how likely is it that

Â you're going to make a profit of more than 13 cents per cookie.

Â And then maybe your boss wants to know or somebody else wants to know,

Â maybe you want to know,

Â what percentage of simulations are going to have a profit greater than

Â 15 cents per cookie and we have about 0.7.

Â So, that basically kind of just shows what a Monte Carlo simulation is.

Â So, for each simulation you're going to choose a cost described by these distributions.

Â So, for each scenario,

Â you're picking one of these at random.

Â So, it's a completely random process,

Â and then you plug those into the overall cost profit analysis for 1000,

Â 5000, and 10,000 simulations.

Â You tally up the profit for each of those and then you can

Â just output the results in graphical format or in tabular format.

Â So, hopefully, this explains what

Â a Monte Carlo simulation is and we're going to get started with explaining

Â the different distributions and then I'm going to go through what was involved

Â with this cookie simulation. Thanks for watching.

Â