In this course, learners will complete several VBA projects. It is highly recommended that learners first take "Excel/VBA for Creative Problem Solving, Part 1" and "Excel/VBA for Creative Problem Solving, Part 2". This course builds off of skills learned in those two courses. This is a project-based course. Therefore, the projects are quite open-ended and there are multiple ways to solve the problems. Through the use of Peer Review, other learners will grade learners' projects based on a grading rubric.

Impartido por:

Charlie Nuttelman

Transcripción

This is an introduction to the Monte Carlo Simulation project. So in this screen cast I'm going to kind of give you an overview of what a Monte Carlo simulation is, and why it is useful. So a Monte Carlo simulation is a simulation that takes into account the variability of the inputs. So you have a complex process. And, if it's simple enough, you can just mathematically describe this. To what it does, It takes into account variation in your inputs. So, maybe you have inputs A, B, C and D. And, maybe A is a randomly distributed variable. So it's a normally distributed variable. So looks like a Bell Curve, which means that there's some sort of average or central tendency with a standard deviation. So there's a lot of things, a lot of variables that are normally distributed. But maybe B is something different, maybe it's uniformly distributed. So maybe, there's an equal probability of getting anything between little a and little b. So, for example this is if you have a six-sided dice, there's a 1/6th chance that you'll get a one, two, three, four, five or six. So it's equally likely. So maybe your process has some input B that has a uniform distribution. You might have discrete variables. So maybe you have one, two and three are the different possibilities, and each of those has its own probability. So maybe, it will look something like this. So that's a discrete input. And, you can also have variables that have some awkward shapes maybe like something like that, where it's more likely to get something come on the right hand side of this distribution. So, all of these inputs A, B, C,D go into this process. And this is normally described by some sort of mathematical process. You've got all sorts of relationships between the different variables, and the output then is really what you're interested in. A Monte Carlo simulation looks at combining the variability of all the inputs to get a distribution of the outputs. So you're not just simulating at once, you're not just using an A,a B,a C and a D, but what you're doing is you're looking at maybe in a one thousand to tens of thousands of different simulations. Each of those one thousand to ten thousand simulations you're going to randomly choose an A from its distribution, randomly choose a B from its distribution and so on. So you're just going to kind of at random choose combinations of A, B, C and D just by chance, by random chance. You're going to put those into your model process and you're going to get the output. So for this example you might get, some sort of probability distribution that looks like this. It's not necessarily just a simple Gaussian curve because some of the inputs are not normally distributed, like A. The output usually has some sort of probability function, all right? Which is shown here. And, you might be doing a financial project. You might be looking at modeling and investment. I'm not a business person, but maybe each of your inputs A, B, C, D has different probabilities or likelihoods. And then you put this together into some sort of process. And, maybe this is the profitability of your venture. And, maybe these ones over here, these are all profitable. And that's actually sort of what the project is going to entail. But maybe and maybe that's like 96% when you simulate it. Four percent of the simulations is what this is telling you. Four percent of the simulations, the Monte Carlo simulations are giving a non-profit. So no profit over here. All right. So, a Monte Carlo simulation you look at the variability of all the inputs you put into your process. You do 1,000-10,000 different simulations. For each simulation you're only choosing one value of each of the distribution. So maybe this is you know, by random chance this is 4.2. This is 1.3. You choose maybe when you do a simulation you get two, is going to be your value because this is a discrete variable one, two or three and then maybe here you get a value of 147. And those all, depending upon what they are, they go into your process. And then you output for that single simulation maybe you get one number. And maybe you're getting a profit of $10.1 thousand, right? You do that 10,000 times and then you take the results of each of the simulations. You take random inputs for each of the simulations and you create the output. And then you plot that. And that's how you can determine, you know 96% of our simulations led to a profitable venture. So what is a probability density function? We're going to be using a lot of these probability density functions in this project. For discrete variables, this is also known as a probability mass function. The important thing is that this represents probability. And the area underneath the curve, if it's a discrete variable like here, the probability of all the bars always adds up to one. So this is probability. Example I have here is you flip two coins. What's the probability that in those two coin flips you're going to get zero heads? So that means this is also two tails. So that's just one half. For the first coin, is going to be the probability of getting a tail, and one half for the second. So it's 0.5 x 0.5 would be 0.25. So that's the probability that you'll get two tails which is zero heads. There's also a 50% probability to get one head. So you can get head then tails, or you get tails then heads. So there's two possibilities out of four possibilities total. And, so this ends up being a 50% probability that you get exactly one head in two coin flips. And similarly, there is a 25% chance that you'll get two heads because you have a 1/2 chance in the first coin, and a 1/2 chance in the second coin. So that's equal to 1/4. So a probability density function or a probability mass function. You also see this probability distribution function or just simply probability distribution. It just sort of explains or describes the probability of getting certain outcomes. Another example, this is a uniform distribution that I touched on a few minutes ago, is a dice. This is a uniform distribution because getting one, two, three, four, five or six is equally likely. So it's sort of just this flat distribution. For a continuous variable, where you can have a range of different values, not just discrete one, two, three and integer values, it looks something like this, where the area under this distribution is equal to one. The Normal Distribution is the most common distribution. I'll talk more about this in individual screen cast on each distribution. But, many many objects, times of something that costs real world phenomena are characterized by a normally distributed variable. The male height in a country, female height is normally distributed. Maybe if you have sugar packets that you're putting in your coffee, if you weighed hundreds of those, you would see that the following normal distribution, all right? So about some average. Manufactured parts. You know the dimensions, the weights of various manufactured parts are all oftentimes normally distributed. So the normal distribution also known as the Bell curve or the Gaussian distribution. So numbers kind of near the average are more common than in the tails. So if this average is equal to five, you know, i'd be unlikely, but possible to get something way away from the average. And it would be rare to get something like a one down here. The standard deviation means that, what it really means is, 68% of the distribution is plus or minus one standard deviation of the mean. So if I had a standard deviation which I represented Sigma equal to two, then that would mean 68% of all possibilities described from this distribution would lie between three to seven. So that 68% will be between three and seven, all right? That is unlikely to get something in the tails. All right. So in subsequent screen cast, I'm going to explain an example. It's a Monte Carlo simulation of a cookie recipe and the cost of cookies and the profitability of cookies.