So how exactly do I determine the likelihood of an outcome?

Monte Carlo Simulation

This is done by running the simulation thousands of times and analyzing the distribution of the output. This is particularly important when you are analyzing the output of several distribution curves that feed into one another.

Once all these distributions are intermingled, the output can be quite complex. Running thousands of iterations or simulations of these curve may give you some insights. This is particularly useful in analyzing potential risk to a decision.

He then had the Pentagon computers do many simulations of the games Tic Tac Toe to teach the computer that no one will will a nuclear war — and save the world in the process. I am assuming that you will overlook the politics, the awkward man hugging and of course, Dabney Coleman. There are various distribution curves you can use to set up your Monte Carlo simulation. And these curves may be interchanged based on the variable.

In a uniform distribution, there is equal likelihood anywhere between the minimum and a maximum. A uniform distribution looks like a rectangle. This is also your standard bell shaped curve. This Monte Carlo Simulation Formula is characterized by being evenly distributed on each side median and mean is the same — and no skewness.

The tails of the curve go on to infinity. So this may not be the ideal curve for house prices, where a few top end houses increase the average mean well above the median, or in instances where there is a hard minimum or maximum. An example of this may be the minimum wage in your locale. Please note that the name of the function varies depending on your version. A distribution where the logarithm is normally distributed with the mean and standard deviation.

This is likely the most underutilized distribution. By default, many people use a normal distribution curve when Poisson is a better fit for their models.

Poisson is best described when there is a large distribution near the very beginning that quickly dissipates to a long tail on one side.

Monte Carlo Simulation: Investment Volatility and Your Time Horizon

An example of this would be a call center, where no calls are answered before second ZERO. Followed by the majority of calls answered in the first 2 intervals say 30 and 60 seconds with a quick drop off in volume and a long tail, with very few calls answered in 20 minutes allegedly. The purpose here is not to show you every distribution possible in Excel, as that is outside the scope of this article. Rather to ensure that you know that there are many options available for your Monte Carlo Simulation.

Do not fall into the trap of assuming that a normal distribution curve is the right fit for all your data modeling. To find more curves, to go the Statistical Functions within your Excel workbook and investigate. If you have questions, pose them in the comments section below.

The setup assumes a normal distribution. A normal distribution requires three variables; probability, mean and standard deviation. We will tackle the mean and standard deviation in our first step. I assume a finance forecasting problem that consists of Revenue, Variable and Fixed Expenses. Where Revenue minus Variable Expenses minus Fixed Expenses equals Profit. The Fixed expenses are sunk cost in plant and equipment, so no distribution curve is assumed.

Introduction to Monte Carlo simulation - Excel

Distribution curves are assumed for Revenue and Variable Expenses. The example below indicates the settings for Revenue. The formula can be copy and pasted to cell D6 for variable expenses.

For Revenue and expenses we you the function NORM. INV where the parameters are:. Since RAND is used as the probability, a random probability is generated at refresh. We will use this to our advantage in the next step. There are several ways to do 1, or more variations.

The simplest option is to take the formula from step 2 and make it absolute. Then copy and paste 1, times. Once the simulations are run, it is time to gather summary statistics.

This can be done a number of ways. The likelihood of losing money is 4. This was gathered by using the COUNTIF function to count the simulations that were less than zero, and dividing by the 1, total iterations.

Download The Monte Carlo Simulation FIle Now What?

In the video above, Oz asks about the various uses for Monte Carlo Simulation. What have you used it for?

Are there any specific examples that you can share with the group? If so, leave a note below in the comments section. Also, feel free to sign up for our newsletter, so that you can stay up to date as new Excel.

TV shows are announced. Leave me a message below to stay in contact. FREE Excel Dashboard Videos XL Power User Kit Excel. Follow Excel TV jQuery document. SSSVEDA DAY 3 — Peek Inside My Excel Work Environment.

Using the Immediate Window — Excel VBA Tips. What is Monte Carlo Simulation? Monte Carlo Simulation Formula. YES, Send me the FREE Download. EXCEL POWER USER QUICK GUIDES. Yes, Send me the FREE Download.