In this case, I’ll respond that I’m making the model very simple so you can understand the changes I’m suggesting to your standard modeling process.Īnd if you don’t work in Finance, you’ll probably grumble that I should have used an example from your own specialty, not Finance. If you work in Finance, you’ll probably grumble that the following examples vastly oversimplify what is typically a complex financial-modeling process.

Second, you might grumble about my examples. And that could make your simulation VERY SLOW. This is because it will need to recalculate many times, and if you have other workbooks open they also will recalculate, needlessly. In this article, I’m going to show you how to do this using Excel Data Tables to record the results produced by each calculation.īefore I get into the details, however, I need to mention two things.įirst, whenever you open a Monte Carlo analysis that uses data tables, make sure that the Monte Carlo workbook is the only workbook open. This process is known as the Monte Carlo method. The models in your workbook then randomly select values from this normal distribution, use those values in their calculations, record key results, repeat the process many times, and then summarize your record of results. To use this method, you first set up your key assumptions to be defined by this curve. This figure illustrates a normal probability distribution, which probably is the best approach for most business use. The Monte Carlo method supports a “stochastic” or “probabilistic” system.Īccording to Wikipedia, “a purely stochastic system is one whose state is randomly determined, having a random probability distribution or pattern that may be analyzed statistically but may not be predicted precisely.” In the past, without thinking much about it, I wrote macros to support the Monte Carlo method.īut I was wrong back then. Instead, a more useful method would take a probabilistic approach, supported by the Monte Carlo method. To illustrate, if I were forecasting profits for a period, a deterministic model would use one number for my forecast of sales, another number for my forecast of operating expenses, and so on.

The first article is, How to Return Random Numbers from a Normal Distribution for Your Excel Forecasts.)ĭuring more than three decades of working with spreadsheets in business, most of the spreadsheet models and forecasts I’ve seen have used what statisticians call a deterministic method.

(This is the second of two articles about normal distributions.

Monte carlo simulation blackjack excel