Tutorial for Using Monte Carlo Simulations

We’ve seen what happens when I change each task’s duration from the minimum to the maximum. What happens to the project, to the overall project duration? How much impact each task’s variability has on the overall project duration? Next, we want to allow our tasks to obtain any value in the range. Creative can be any value between 3 to 7, and I want to give it some kind of likelihood, how likely is it to be 3, 5 or 7 or any value in between. Not only do I want it to obtain all the values in between this range, but I also want to see what happens when all of my tasks vary simultaneously. What is the impact on my overall project duration?

In order to do both of these things, we’re going to use an add on to Excel. We need some help. Excel on its own could do it, but it would be cumbersome to implement. So what we’re going to do is we’re going to make use of one of the most popular industry software tools out there for such a simulation. For what we’re going to call Monte Carlo simulation. And this tool that we’re going to introduce today is called Crystal Ball. There are several others, look for my references to other alternatives that you may find readily at your firm. So what does Crystal Ball allow us to do? Well, let’s begin by thinking about the individual task’s distribution of the duration.

And how we might want to model them in our spreadsheet. So we thought that Creative might take at the minimum three, most likely five, and maximum seven weeks. In order to allow this cell here, G3 to vary between that range and to obtain certain values with a certain likelihood, we need to assign a certain distribution. This step is called the find assumption in crystal ball. If you click on this icon here at the left, you get a whole gallery of options. We’re going to go with something simple, something quite commonly used in practice because of its simplicity and relative easily explained and a transparent option, which is the triangular distribution.

If I click OK, you will see that this provides a visual for the distribution, this allows us to input a minimum value. What is the minimum duration? What is the likely value of the duration? And what is the maximum duration? I’m going to link this to the spreadsheet. So I can then either change the spreadsheet if need be. And also I can copy paste this for the other tasks. And all of the individual distributions will apply. So if I press Enter here, this triangle now represents that distribution. What I see here is a probability distribution function that indicates how frequent or how likely the values are to, to be chosen.

And so if I click OK and now if I stack or I kind of trial a specific value, I see that I have randomly selected 4.97 etc, etc, for the number of weeks that this task takes. Copying the formula, or copying this crystal ball adaptation, I can highlight the other tasks that are random, as well, and I can paste that information, and each one of these tasks is now picking up a triangular distribution, using its three parameters that are listed here on the left? The only task that is not variable in our example is HR where we assume there’s no variability and it will take one week regardless.

And so now, every time I press the step function, I see a new instance or a new possible reality of the future of my project presented to me. And so these tasks are randomly generated from those triangular distributions that I assigned to them. And with that is my project overall duration. Let me give you some more precision here to see exactly how this changes. If I again step, I can see that my project duration with each step gets updated based on the realities or based on the scenario that is depicted here on the spreadsheet. For the individual task I see an overall project duration expectation. And so what can I do now?

Well, I can keep stepping and see how often the project duration ends up high or low, or I can try and kind of keep track of the different numbers that it might fall on, or I can actually ask crystal ball to do it for me. I can define a forecast. I’m forecasting my project duration. And so it’s labeled project duration. And now, if I let it run for many different trials. I would like to run a million different situations. Test a million possible futures of my project. I press the Start button and magic occurs. Crystal ball has the capability to show me what it’s generating while it’s doing that.

You see here, this little updated progress bar tells me how many trials it’s tried so far, it’s getting to the million very quickly. And here I’m keeping track of how often, or, you know, what is the probability that I will see a certain outcome. This is, again, a probability distribution function and I can see a lot of information being generated and presented to me as the simulations are drawn. I’m just going to close this small window and focus on the, the results. So what have I seen here. Okay, this is great, we have a lot of information. First what I would like to look at is I would like to look at the cumulative frequency, okay?

Even better I’m going to change this chart, and I’m using the menu on the top, it might be pretty small for you to see but I’m basically going to just change the aesthetics to be a line chart, it makes it easier to view. And this tells me, okay. What is the chance that my project will be completed by a certain number of weeks? For instance, if I’m looking at 16 weeks I can see that, if I go all the way up here, approximately 16. There’s almost something in the region of 95% certainty that I will be completing this project within 16 weeks.

Other views that might be helpful is again looking at the frequency. I’m going to change back my chart to be in this case a column chart. And I can ask crystal ball to tell me exactly where my 95th percentile. Let’s assume that’s the confidence level I would like to set my reported in my publicly known completion date to, I can go ahead an ask Crystal ball to plot on this chart for instance the 95th percentile. Okay, and when it does that, I have lines being added to my chart and I see here that there is a 95% chance that I will complete my project. All values up to 16 weeks.

So, again I have only a 5% chance of exceeding that, and that might be a pretty good comfort zone for me to work with. I can also show this visually with the red tail here, indicating the chance of being above, and the blue being the chance of being below. And I might be comfortable at setting a deadline of somewhere around 16 weeks. Feeling fairly confident that I will complete by the designated time that I set myself out and determined as a possible completion date.

And this again, all within Excel using an add-on. In this case, crystal ball.