23Apr2015

Monte Carlo Simulation in Excel

What is Monte Carlo Simulation?

The name Monte Carlo simulation comes from the computer simulations performed during the 1930’s to know the probability that the chain reaction needed for an atom bomb to detonate successfully. The physicists who involved in this work were big fans of gambling hence the name Monte Carlo.

 

How to use?

It is developed to analyze the problem by running the simulation many times in order to develop a statistical data on how the model works. The resulting data from the Monte Carlo simulation is close to the mathematical statistical probability. A problem with complexity is more efficiently solved using a Monte Carlo simulation.

 

In Excel, “RAND()” function is used to generate random values for Monte Carlo models. A new random real number is generated every time the worksheet is calculated, this is only possible when the Excel calculation are in Automatic. “RANDBETWEEN()” function is used to return a random integer number between the numbers we specify. Random numbers must be used to analyze output.

 

Example 1: Simulating coin toss using Monte Carlo Simulation

Let’s simulate a coin toss to determine the probability of the coin resulting in heads or tails. We will repeat the simple coin toss many times and then we calculate the percentage of heads.

 

Step 1: Creating a table

Lets create a table with 10 tosses being generated randomly using “=RANDBETWEEN(0,1)” function as shown in the image below. A ‘zero’ mean tails while ‘one’ means heads.

monte carlo simulation 1

 

Here, we are generating a random sequence of integers between 0 and 1. Let’s populate the same formula for say 1000 simulations as shown in the image below.

monte carlo simulation 2

 

Step 2: Finding the count of Heads

To do this lets create a new column and put this formula. This will sum all the heads up (all 1’s) count

“=SUM(A3:J3)”

monte carlo simulation 3

 

Step 3: Finding the Probability

 monte carlo simulation 4

Now let’s find the probability of the Heads coming 8 times out of 10 tosses.

  • In cell “O2”, we put the total number of simulations that we are running.
  • In cell “O3”, we put the formula “=COUNTIF(L3:L1002,8)”

The above formula will help us find how many times that 8 out of 10 tosses gives heads

  • In cell “O4”, we put the formula “=COUNTIF(L3:L1002,”>8″)”

This formula will help us find how many times coin turned heads in more than 8 out of 10 tosses

  • In cell “P3”, we put the formula “=O3/O2”. This will give us the percentage of the count of heads falling 8 times.
  • In cell “P4”, we put the formula “=O4/O2”. This tell us the percentage of the count of heads greater than 8 times.
  • Hit ‘F9’ to generate random values and simulate the Monte Carlo Simulation.

 

Example 2: Predicting Sales based on Monte Carlo Simulation

 

In this example, let’s assume that we have old sales data with an average, minimum and maximum sales for a period of 10 years and we are interested in finding the statistical data on how sales will turn out based on Monte Carlo Simulation.

monte carlo simulation 11

Step 1: Generating data table

We generate random values based on our old min and max sales data as shown in the image below

“=RANDBETWEEN(742,8392)”

This will generate random values between 742 and 8392. These are generated for Q1, Q2, Q3 and Q4.

monte carlo simulation 12

Now, let’s generate a data table with these random values. Select the whole table with an extra column as shown in the image below and click on Data Table present in What if analysis in Data Section in excel ribbon.

 

monte carlo simulation 13

After clicking Data Table, we get a pop window where we need to enter the column input cell. Select some empty cell to allow excel perform its calculations.

monte carlo simulation 14

 

monte carlo simulation 15

As shown in the image above, the data table has been generated.

 

Step 2: Calculating the Minimum, Maximum and Average

Create a total sales column “=SUM(B2:E2)”

monte carlo simulation 16

monte carlo simulation 17

 

Based on 1000 simulations we find the Average, Minimum and Maximum total sales for our projected sales data as shown in the image above. By this data we can predict that Average sales and other details for a company.

 

Example 3: Creating Monte Carlo Simulation using Data Tables

Let’s see how we can create Monte Carlo Simulation using Data Tables. We have ‘S & P Average’ (Stock Market Index) and ‘Daily STD Deviation’ over the past 10 years as shown in the image below.  We are interested in generating ‘1000’ simulated data of S & P and find the average, STD deviation and the probability of getting more than 30 % S&P at the end of the week.

monte carlo simulation 5

 

Step 1: Let’s create Daily and Cumulative values

monte carlo simulation 6

We generate ‘Daily’ values by using formula “=NORM.INV(RAND(),$B$2,$B$3)”

NORMINV is a statistical function. It returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. For the probability argument we are using random function and for mean we are taking S&P Average cell ‘B2’ and for STD DEV we are taking Daily STD DEV cell ‘ B3’. Now populate the formula for remaining days.

For Cumulative, Monday will be same as ‘=B6’ and other is as shown below

‘=(B7+1)*(C6+1)-1′

Populate the same formula for remaining days in a week.

 

Step 2: Generate simulations using data table.

Select the table (first row in the table is having 29% which is the end of the week cumulative value) and click on Data Section in excel ribbon and then click on What if analysis and select Data table as shown in the image below

monte carlo simulation 7

 

After clicking Data Table we get a pop window where we need to enter the column input cell, some empty cell away from data table has to be entered to allow excel perform its calculations.

monte carlo simulation 8

Based on the end of the week cumulative value the data table is generated as shown in the image below

monte carlo simulation 18

 

Step 3: Finding Average, Standard deviation and Probability

monte carlo simulation 10

To find the Average for the simulated values we use the formula “=AVERAGE(C13:C1012)” and for STD Dev we use “=STDEV(C13:C1012)” and for Probability put the formula “=COUNTIF(C13:C1012,”>30%”)/1000”

There is 26% chance of getting more than 30% of S&P at the end of the week.

Have any Queries? Feel free to contact us here

  • 23 Apr, 2015
  • Excel for Commerce
  • 0 Comments
  • Excel Consultant, Excel Expert, monte carlo simulation,

Categories

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>