Monte Carlo methods are used in many disciplines, from nuclear physics to finance. In business models, the Monte Carlo method allows us to use random values for variables in our modeled business scenario. We did this previously using rand and rand between to estimate the demand for our products, for example. But using Monte Carlo, we can combine those random variables with a specific probability distribution. First we use our model with a set of random inputs. The model calculates, in a deterministic fashion, the results of that random trial and stores the result. Running the model many times provides a set of results that reflect the uncertainty in the environment being modeled and uses a probability distribution to combine the results into a forecast. Our objectives in this lecture are to understand the purpose of Monte Carlo simulations, and to explore one application. Then we will see a couple of ways to incorporate the Monte Carlo methods into our spreadsheet model. Let's look at an example in a spreadsheet model. In an earlier module I demonstrated how to use the random number generator in Excel to create collections of randomly generated assumptions and decision variables to use in our models. You'll see an example of that with our projected sales on this sheet. We use the data analysis tool pack tool and the rand between function. The tool pack generator let us supply different types of probability distributions to the random numbers that we generated. I showed you the normal distribution. That's the famous bell-shaped curve. But there are other types as well. We had previously looked at uniform and normal distributions, but the Bernoulli distribution is a third. Statisticians often describe the Bernoulli distribution by referring to the example of a coin toss. The results are one of two mutually exclusive options, heads or tails. And in that case, let me show you an example of that type of distribution using the data analysis tool pack random number generator. And I'll choose to create one variable, 10 random numbers. And the distribution type in this case is Bernoulli. In a Bernoulli distriution, the parameter P value shows that the probability of the two outcomes in the distribution doesn't have to be 50-50, the way it is in a coin toss. In this particular instance, I can set a P value of a 0.3 to indicate that roughly 30% of the time, I would expect to see a one instead of a zero in my selection. I'll also identify the output range as being B4, which is where I want these random numbers to appear in my model. And when I click okay, you'll see that we have a distribution of zeros and ones, favoring zeros over the ones, but randomly selected. This type of distribution is useful in models where you need to randomly generate instances of one of two possible and mutually exclusive outcomes. For example, did the stock market go up or not yesterday? Did a test drill strike oil or not? Did we get the contract? Remember that the choice you make regarding the type of distribution in a random variable in your model represents a kind of assumption you are making. In an earlier module, we generated random sales forecasts to plug into this cash flow projection for the coming year. We generated those random forecasts using a probability distribution that was based on actual historical sales data. We assumed that historical distribution to be norma,l and we used the historical mean and standard deviation to guide the generation of our random sales numbers. Those are the first steps in making a Monte Carlo simulation. That is, we start by generating random numbers pulled from a probability distribution. Next, we ran the cash flow calculations with one set of forecast numbers at a time. And here's the results of those ten forecasts in cells E6 through N8. Individually, each sales forecast in the model was run in a form that we would refer to as deterministic. I copied the output cells from E6 to N8 after each of those ten runs. Each individual forecast is considered deterministic, but by running a large number of calculations based on sets of randomly selected variables, we introduce real world uncertainty into the planning model. I've summarized those random simulations here with an average and standard deviation for the three green key outputs. Now here's the hard part. You need to run hundreds, more likely thousands, of these to build up a reliable result set. The approach I just demonstrated is going to be very labor intensive. Next, let's look at another approach to including Monte Carlo in a spreadsheet that is a little less labor intensive. First we'll take a look at a forecast for the innovative speaker sales. Let's take a look at another approach to including Monte Carlo in a spreadsheet that's a little less labor intensive. First, let's look at a forecast for innovative speaker sales. Amy, the entrepreneur behind this venture, wasn't taking any salary but did have some fixed expenses for running her innovative speaker business. And that's recorded here in cell B4. The cost of the components and the assembly for each speaker is shown here as a variable cost In cell B5. The annual sales forecast, based on our analysis of last year's sales and the previous two years, is here in cell B6. Assume that during this time, we've been pricing the units at the number that's shown here in cell B8. Earlier, we discovered that among the most sensitive variables in our model was the unit sales forecast. Using the Excel statistical functions and our three years of historical sales data, we calculated a standard deviation annually of 72 units, and that's shown here in cell B7. The objective function, our profit and loss cell, is in green here shown in cell B10. That calculates the sales forecast in units times price and subtracts the same forecasted units times the variable cost or unit cost. If Amy pays herself $50,000 and keeps another 10,000 to pay other people to help her, this $1875 profit is the likely result. But now let's look at some other possible outcomes. Let's use the data analysis tool pack in the random number generator. There are other ways to this which I'll describe later, but let's use this familiar tool first. I want to identify a set of 1,000 random sales forecast and put those in cell B12. The distribution is normal, and I've taken the mean of 625 from our historical data, as well as the standard deviation of 72. For an output range, I want to put that in cell, in this case, B13. And when I hit OK, Excel will generate for me a normally distributed range of random variables for this sales forecast. And let me label it sales here in cell A13. Next, let's copy the profit and loss formula. It's located here in cell B10. I'll paste it here in cell C13. You can see in this formula that I've used absolute references to point to fixed expenses in B4, variable expenses in B5, and the unit price that's in B8. But I'm allowing for a relative reference to the sales forecast. That number I will use as the number that appears in cell B13. And now we'll use that as well then to indicate the sales forecast for determining variable expenses. Next I will copy this formula a thousand times. Here I have added the average or the mean, the minimum, the maximum, and the standard deviation for the 1,000 trials that we just calculated. You can see in some scenarios Amy loses money, shown here in the formula, in F11 for the minimum of that range of 1,000 cells. Assuming a normal distribution for sales, we can use what my colleagues call the empirical rule to see how risky Amy's decision has been to leave her job. The empirical rule says that 95% of the time, the results of this forecast will fall within two standard deviations of the mean. That's part of the definition of a normal distribution. I added a minimum and maximum that uses the mean and adds those two standard deviations. What this means is that 95% of the time, given these assumptions, Amy can pay herself almost $50,000, 48,000 on a bad year, and see a profit as high as $35,000. But there's still a 5% chance that these numbers are wrong. So the question for Amy is whether she wants to take that risk. In this design, the sales forecast is somewhat static. We would need to run the data analysis toolpack function manually to change the random values we have generated for sales. There are other tools in Excel that make it possible to avoid that manual intervention. Excel provides functions for generating random numbers inside formulas. Those include the functions NORM.DIST and NORM.INV for generating normal distributions and their inverse around known x values. The sample files on your course website will include an example of the use of those functions with Excel's data table function. Why go through this trouble to introduce a Monte Carlo simulation into our models? Normal what if analysis could be fine for many business forecasts, but Monte Carlo's useful for measuring uncertainty and evaluating risk. The more complex and dynamic the system or business being modeled, the more useful are Monte Carlo designs. Monte Carlo is particularly helpful in probability trees, like that test marketing case we looked at in an earlier module. In general, Monte Carlo is worth the trouble when the model is complex and manual what-if scenario testing isn't feasible. It's also worth the trouble if the stakes are very high, like simulating the interactions of a new drug, or when a little bit more precision might put you ahead of your other competitors. For example, when managing your own or other people's money. We followed four steps in this example of including a Monte Carlo simulation in an Excel spreadsheet model. First, we identified the type of probability distribution we expected to see in our sales forecast. That was based on historical observations and included means and standard deviations. Next, we generated random numbers using that distribution. We then ran our forecast simulation 1,000 times using those random numbers. Finally, we summarized the simulation runs and calculated our risks. A variety of companies provide Excel add-ins to extend and simplify the use of simulations in your business models. Those include Crystal Ball from Cisco, @RISK from Palisade, Model Risk from Vose software. In addition, mathematical modeling environments like MATLAB, Mathematica, and TK Solver provided additional features for including simulations in your models. Please, none of this is an endorsement for any of those products. Some of these product can be very expensive, although most of them provide a free trial offer.
