Monte Carlo simulation in MS Excel

Monte Carlo simulation in MS Excel

The Monte Carlo method is based on the generation of multiple trials to determine the expected value of a

random variable. The basis of the method is provided by the following relationship:

?1

Pr ?

?N

¡Æ¦Î ? ?

N

<

3¦Ò ?

? ¡Ö 99.8%

N?

There are a number of commercial packages that run Monte Carlo simulation, however a basic spreadsheet

program can be used to run a simulation. In this case the generation of multiple trials is implemented by

propagating a basic formula as many times as the number of iterations required by the model.

Lets assume that a project has six activities. Each activity has a total cost in a specified range. In some cases the

value is fixed (Activity B), but generally it can assume any value within an interval. Each of these variables can

have a unique distribution, but as we will see in a moment, we can safely assume a uniform distribution without

compromising the result.

One important assumption that we make is that each of these variables is independent of the others. This means

that the cost of any activity is not influenced by the cost of any other activity.

Activity

Minimum

Maximum

A

10,000

20,000

B

15,000

15,000

C

7,500

12,000

D

4,800

6,200

E

20,000

25,000

F

5,000

7,000

Total

62,300

85,200

The total cost of the project is a random variable with a value between the minimum and the maximum. This

variable will be normally distributed since it is the sum of a number of random variables. This is also the reason

why the individual distribution of each variable is not important.

The general scheme of the Monte Carlo method is as follows:

? Generate random values for each of the activity costs

? Add each series of random values to arrive at a total project cost.

? The expected project cost is the average of these values.

There are a number of parameters that can be calculated to assess the goodness of the solution. We will discuss

some of these parameters later on.

The first step is to generate random values for each of the activity costs. Assuming a uniform distribution, we can

use the RAND() function to generate random numbers in the interval (0,1) and multiply these by the range of

each variable. The range is the difference between the maximum value and the minimum value.

The random cost for Activity A will look like this:

=RAND()*(20,000-10,000)+10,000

This formula generates a random value between 10,000 and 20,000

If we create one of these formulas for each of the activities, the total project cost will be the sum of all these

functions. We can propagate the row of formulas for each iteration of the model.

The following table is a sample of the model showing the first seven iterations. Row 4 is the basic model, which

is propagated as required.

TU08

Monte Carlo simulation in MS Excel

A

1

2

3

B

C

D

E

F

G

Activity

Minimum

A

10,000

B

15,000

C

7,500

D

4,800

E

20,000

F

5,000

Total

62,300

Maximum

20,000

14,167

15,556

19,970

16,811

14,783

18,002

17,628

15,000

12,000

6,200

15,000

7,773

5,682

15,000

10,452

4,943

=RAND()*(A$3-A$2)+A$2

15,000

8,717

6,136

15,000

11,405

5,252

15,000

10,030

4,870

15,000

8,435

4,808

15,000

9,808

5,112

25,000

7,000

20,788

5,837

20,957

6,239

24,853

6,474

21,764

6,235

=SUM(B4:G4)

21,070

6,145

20,890

5,211

22,612

5,138

85,200

69,247

73,148

81,149

76,467

71,898

72,346

75,297

4

5

6

7

8

9

10

H

Determining the number of iterations

The Monte Carlo method provides an estimate of the expected value of a random variable and also predicts the

estimation error, which is proportional to the number of iterations.

The total error is given by:

¦Å=

3¦Ò

N

where b is the standard deviation of the random variable, and N is the

number of iterations. We can estimate an upper bound of b by calculating the standard deviation between the

maximum, the minimum and average values of the random variable:

¦Ò = STDEVP(H2:H3,AVERAGE(H2:H3)) = 9,349

Note that we used the function STDEVP, which calculates the standard deviation of the entire population, in this

case only 2 values.

Lets determine the number of iterations required for an error of less than 2%. A gross estimation of the random

variable is the average of the maximum value and the minimum value. An absolute error of 2% is this average

divided by 50:

¦Å = AVERAGE(H2:H3)/50 = 1,475

Therefore the number of iterations to obtain a result with an error of less than 2% is:

2

? 3 ¡Á 9,349 ?

N =?

? = 362

? 1,475 ?

The Model is ran by propagating line 4 a total of 362 times. The expected value of the random variable is the

average of the Total column:

Expected project cost = AVERAGE(H4:H547) = 73,428

Given that the variable is normally distributed, the median should be very close to the mean

MEDIAN(H4:H547) = 73,304. A difference of only 0.2%

The standard deviation is calculated over the entire population:

STDEVP(H4:H547)=3,604

We can now review the true error of the estimate

¦Å=

3¦Ò

N

=

3 ¡Á 3,604

362

= 568.3

This is an error of only 0.8%.

Other useful information is the Kurtosis and the Skewness of the distribution. The Kurtosis is a relative measure

of the shape compared with the shape of a normal distribution. The normal distribution has a Kurtosis of 0.

=KURT(H4:H547) = -0.296

TU08

2

Monte Carlo simulation in MS Excel

This indicates that the distribution is somewhat flatter than a normal distribution.

Skewness is a measure of asymmetry. The normal distribution has a skewness of 0.

=SKEW(H4:H547) = 0.061

This indicates that the tail of the distribution extends towards the right.

The results can be easily plotted to produce the following chart:

60

1

0.9

0.8

0.7

0.6

0.5

0.4

0.3

0.2

0.1

0

Frequency

50

40

30

20

10

63

,4

4

65 5

,7

3

68 5

,0

2

70 5

,3

1

72 5

,6

0

74 5

,8

9

77 5

,1

8

79 5

,4

7

81 5

,7

6

84 5

,0

55

0

Probability

Frequency/Cumulative Chart

Cost $

? Rob Jeges

.au

Microsoft Project (MS Project) is a trademark of the Microsoft

Corporation.

TU08

3

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download