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.