Monte Carlo Simulation
Using RiskSim

9

9.1 RISKSIM OVERVIEW
RiskSim is a Monte Carlo Simulation add-in for Microsoft Excel (Excel 97 and later versions) for
Windows and Macintosh.
RiskSim provides random number generator functions as inputs for your model, automates Monte
Carlo simulation, and creates charts. Your spreadsheet model may include various uncontrollable uncertainties as input assumptions (e.g., demand for a new product, uncertain variable cost of production, competitor reaction), and you can use simulation to determine the uncertainty associated with the model's output (e.g., annual profit). RiskSim automates the simulation by trying hundreds of what-ifs consistent with your assessment of the uncertainties.
To use RiskSim, you
(1)

create a spreadsheet model

(2)

optionally use SensIt to identify critical inputs

(3)

enter one of RiskSim's twelve random number generator functions in each input cell of your model

(4)

choose Tools | Risk Simulation from Excel's menu

(5)

specify the model output cell and the number of what-if trials

(6)

interpret RiskSim's histogram and cumulative distribution charts.

RiskSim facilitates Monte Carlo simulation by providing:
Twelve random number generator functions
Ability to set the seed for random number generation
Automatic repeated sampling for simulation
Frequency distribution of simulation results
Histogram and cumulative distribution charts
All of RiskSim’s functionality, including its built-in help, is a part of the RiskSim XLA file. There is no separate setup file or help file. When you use RiskSim on a Windows computer, it does not create any Windows Registry entries (although Excel may use such entries to keep track of its addins).

86

Chapter 9 Monte Carlo Simulation Using RiskSim

9.2 HOW TO INSTALL AND UNINSTALL RISKSIM
EXE, ZIP, and XLA Files
The RiskSim add-in file is typically named risk231s.xla, where "risk" is an abbreviation of
RiskSim, "231" indicates version 2.31, and "s" indicates standard license (and "t" indicates tryout license and "a" indicates academic license). Excel recognizes only the XLA file, not a ZIP file or an EXE file.
You may receive or download the add-in as a compressed ZIP file, e.g., risk231s.zip. To extract the XLA file using the Windows XP operating system, right-click the ZIP file, and choose Open
With Compressed (zipped) Folder. Alternatively, download the WinZip evaluation version from www.winzip.com to extract the XLA file. On a Macintosh, extract using the free StuffIt Expander, available from www.stuffit.com.
RiskSim may be received as an executable program file with an EXE extension, e.g., risk231s.exe.
The EXE file contains the RiskSim ZIP file and software to extract or "unzip" the ZIP file. To extract the XLA file using a Windows operating system, double-click the EXE file, or right-click and choose Open. On a Macintosh, extract using StuffIt Expander.
The RiskSim XLA file may be combined with the SensIt XLA or TreePlan XLA files in a single
ZIP or EXE file, in which case the multiple XLA files are extracted.
There are several ways to install the RiskSim XLA file.

Occasional Use: File Open Command
The simplest method of using RiskSim does not require any installation step. Start Excel, and use
Excel’s File | Open command to open the RiskSim XLA file from floppy or hard drive. This method is particularly useful when you want to use RiskSim temporarily on another computer. Do not try to open the XLA file by double-clicking.
When you use Excel to open the XLA file on Windows computers, if Excel's security level is set to High or Very High, you may see a warning similar to Figure 9.1.
Figure 9.1 Excel 2003 Security Warning With Very High Security Level

Since RiskSim is an unsigned macro, it is automatically disabled if Excel's security level is High or Very High. To use RiskSim, start Excel, choose Tools | Macro | Security | Security Level, click
Medium, and click OK, as shown in Figure 9.2. Then open the RiskSim XLA file.

9.2 How to Install and Uninstall RiskSim

87

Figure 9.2 Excel 2003 Tools Macro Security Level

If the Security Level is set to Medium or Low when you open the RiskSim XLA file, you may see the warning shown in Figure 9.3. Click the Enable Macros button. To use RiskSim's features, choose the Risk Simulation item on Excel's Tools menu.
Figure 9.3 Excel 2003 Security Warning With Medium Security Level

In summary, to use Excel's File | Open command to load the RiskSim XLA file, you must choose
Tools | Macro | Security | Security Level | Medium, and you must click the Enable Macros button in the Security Warning dialog box.

Selective Use: Tools Add-Ins Library Folder
For Windows computers, copy the RiskSim XLA file to the Program Files \ Microsoft Office \
Office \ Library folder of your hard drive. The next time you start Excel, RiskSim will automatically appear in Excel's add-in manager. Start Excel, and use Excel’s Tools | Add-Ins command to load and unload RiskSim as needed by checking or unchecking the RiskSim checkbox, as shown in Figure 9.4.

88

Chapter 9 Monte Carlo Simulation Using RiskSim

Figure 9.4 Excel Add-In Manager

Since the Library folder is a trusted location, you can set the security level to High or Very High and still avoid security warnings when RiskSim is loaded. Choose Tools | Macro | Security |
Trusted Publishers, check the box for "Trust all installed add-ins and templates," and click OK, as shown in Figure 9.5. To run RiskSim during the same session when you change this setting, you may have to choose Tools | Add-Ins, uncheck RiskSim in the list, click OK, choose Tools | AddIns again, check RiskSim in the list, and click OK.
Figure 9.5 Excel 2003 Tools Macro Security Trusted Publishers

In summary, if you put the RiskSim XLA file in the Library folder on a Windows computer, you should choose Tools | Macro | Security | Trusted Publishers, and check the box for "Trust all installed add-ins and templates."

9.3 Using RiskSim Functions

89

For Macintosh, a folder similar to Library is usually found at Macintosh HD : Applications :
Microsoft Office : Office : Add-Ins, but you will need to explicitly select the XLA file to install it, using the same procedure described below when you use an arbitrary folder.
Excel's add-in manager keeps track of the location of the XLA file. To uninstall RiskSim on
Windows or Macintosh computers, first delete, move, or rename the XLA file so that Excel can no longer find it. After the XLA file no longer exists where Excel expects to find it, start Excel, choose Tools | Add-Ins, check or uncheck the RiskSim box, and answer the prompt for deleting the add-in from the list.

Selective Use: Tools Add-Ins Your Folder
Copy the RiskSim XLA file to a folder of your choice on the hard drive. For Windows computers, start Excel, choose Tools | Add-Ins | Browse, navigate to the location of the RiskSim XLA file, select it, and click OK. Subsequently, use Excel’s Tools | Add-Ins command to load and unload
RiskSim as needed by checking or unchecking the RiskSim checkbox.
If you put the RiskSim XLA file in an arbitrary folder on a Windows computer and specify its location using Tools | Add-Ins | Browse, you should choose Tools | Macro | Security | Trusted
Publishers, and check the box for "Trust all installed add-ins and templates."
For Macintosh, start Excel, choose Tools | Add-Ins | Select, navigate to the location of the
RiskSim XLA file, select it, and click Open.
To uninstall RiskSim, first delete, move, or rename the XLA file, and then follow the same steps for the Library folder described above.

Steady Use: XLStart Folder
For Windows computers, copy the RiskSim XLA file to the Program Files \ Microsoft Office \
Office \ XLStart folder of your hard drive. The file will be opened every time you start Excel.
Alternatively, copy the RiskSim XLA file to Documents and Settings \ User name \ Application
Data \ Microsoft \ Excel \ XLStart, where User name is your Windows user name.
If you put the RiskSim XLA file in an XLStart folder on a Windows computer, you should choose
Tools | Macro | Security | Trusted Publishers, and check the box for "Trust all installed add-ins and templates." For Macintosh, copy the RiskSim XLA file to the Macintosh HD : Applications : Microsoft Office
: Office : Startup : Excel folder.
To uninstall RiskSim, delete the XLA file or move the XLA file from the Windows XLStart folder or from the Macintosh Startup : Excel folder.

9.3 USING RISKSIM FUNCTIONS
RiskSim adds twelve random number generator functions to Excel. You can use these functions as inputs to your model by typing in a worksheet cell or by using the Function Wizard. From the
Insert menu choose Function, or click the Function Wizard button. RiskSim's functions are listed in a User Defined category. The twelve functions are:
RANDBINOMIAL(trials,probability_s)

90

Chapter 9 Monte Carlo Simulation Using RiskSim

RANDBIVARNORMAL(mean1,stdev1,mean2,stdev2,correl12)
RANDCUMULATIVE(value_cumulative_table)
RANDDISCRETE(value_discrete_table)
RANDEXPONENTIAL(lambda)
RANDINTEGER(bottom,top)
RANDNORMAL(mean,standard_dev)
RANDPOISSON(mean)
RANDSAMPLE(population)
RANDTRIANGULAR(minimum,most_likely,maximum)
RANDTRUNCNORMAL(Mean,StDev,MinValue,MaxValue))
RANDUNIFORM(minimum,maximum)
RiskSim's RAND... functions include extensive error checking of arguments. After verifying that the functions are working properly, you may want to substitute RiskSim's FAST... functions which have minimal error checking and therefore run faster. From the Edit menu choose Replace; in the
Replace dialog box, type =RAND in the "Find What" edit box, type =FAST in the "Replace with" edit box, and click the Replace All button.

9.4 UPDATING LINKS TO RISKSIM FUNCTIONS
When you insert a RiskSim random number generator function in a worksheet cell, the function is linked to the disk location of the RiskSim xla file you are currently using. During the current
Excel session, the formula bar shows only the name of the RiskSim function. But when you save and close the workbook, Excel saves the complete path to the disk location of RiskSim function.
For example, after closing and reopening the workbook, the formula bar might show
C:\MyAddIns\risk231s.xla\RandNormal(100, 10). This is standard behavior for Excel user defined functions like the ones contained in the RiskSim xla file.
When you open the workbook, Excel looks for the RiskSim xla file using the saved path. If Excel cannot find the RiskSim xla file at the saved path location (e.g., if you deleted the RiskSim xla file from the C:\MyAddIns folder or if you opened the workbook on another computer where the
RiskSim xla file is not located at the same path), Excel displays a dialog box like the one shown below. Figure 9.6 Excel 2003 Warning To Update Links

9.4 Updating Links To RiskSim Functions

91

If you see this dialog box or a similar warning when you open an Excel file, choose the "Don't
Update" option. The workbook will be opened, but any cell containing a reference to a RiskSim function will display the #NAME? or similar error code.
To update the links after the workbook is open, be sure that a RiskSim xla file is open. Then choose Edit | Links to see the dialog box shown below. (In this example the workbook originally used functions from the RiskSim xla file located at C:\middleton\risksim\risksim.xla.)
Figure 9.7 Excel 2003 Edit Links Dialog Box

To update the links, click the Change Source button. A file browser window will open, where you can navigate to the RiskSim xla file that is open. After you select the file using the file browser, click OK. Back in the Edit Links dialog box, click the Close button.
In Excel 2003 the Edit Links dialog box has a Startup Prompt button. To avoid possible problems when Excel tries to automatically update links while a file is being opened, we recommend the default "Let users choose to display the alert or not."
Figure 9.8 Excel 2003 Startup Prompt Dialog Box

92

Chapter 9 Monte Carlo Simulation Using RiskSim

9.5 MONTE CARLO SIMULATION
After specifying random number generator functions as inputs to your model, from the Tools choose Risk Simulation | One Output.
Figure 9.9 RiskSim Dialog Box

Optionally, select the "Output Label Cell" edit box, and point or type a reference to a cell containing the name of the model output (for example, a cell whose contents is the text label "Net
Profit").
Select the "Output Formula Cell" edit box, and point to a single cell on your worksheet or type a cell reference. The output cell of your model must contain a formula that depends, usually indirectly, on the model inputs determined by the random number generator functions.
Select the "Random Number Seed" edit box, and type a number between zero and one. (If you want to change the seed without performing a simulation, enter zero in the "Number of Trials" edit box.) Select the "Number Of Trials" edit box, and type an integer value (for example, 100 or 500). This value, sometimes called the sample size or number of iterations, specifies the number of times the worksheet will be recalculated to determine output values of your model.

9.6 RANDOM NUMBER SEED
The "Random Number Seed" edit box on the RiskSim dialog box allows you to set the seed for
RiskSim's random number generator functions. The seed must be an integer in the range 1 through
2,147,483,647. RiskSim's random number generator functions depend on RiskSim's own uniform random number function that is completely independent of Excel's built-in RAND().
Random numbers generated by the computer are actually pseudo-random. The numbers appear to be random, and they pass various statistical tests for randomness. But they are actually calculated by an algorithm where each random number depends on the previous random number. Such an algorithm generates a repeatable sequence. The seed specifies where the algorithm starts in the sequence. 9.7 One-Output Example

93

A Monte Carlo simulation model usually has uncontrollable inputs (uncertain quantities using random number generator functions), controllable inputs (decision variables that have fixed values for a particular set of simulation iterations), and an output variable (a performance measure or operating characteristic of the system).
For example, a simple queuing system model may have an uncertain arrival pattern, a controllable number of servers, and total cost (waiting time plus server cost) as output. To evaluate a different number of servers, you would specify the same seed before generating the uncertain arrivals. Then the variation in total cost should depend on the different number of servers, not on the particular sequence of random numbers that generates the arrivals.

9.7 ONE-OUTPUT EXAMPLE
In this example the decision maker has described his subjective uncertainty using normal, triangular, and discrete probability distributions.
Figure 9.10 One-Output Example Model Display
1
2
3
4
5
6
7
8
9

A
B
Software Decision Analysis
Unit Price
Units Sold
Unit Variable Cost
Fixed Costs
Net Cash Flow

C

$29
739
$8.05
$12,000

D

$3,485

A
Software Decision Analysis

F

G

Price is controllable and constant.
Normal
Mean = 700, StDev = 100
Triangular Min = $6, Mode = $8, Max = $11
Discrete Value
Probability
$10,000
0.25
$12,000
0.50
$15,000
0.25

Figure 9.11 One-Output Example Model Formulas
1
2
3
4
5
6
7
8

E

B

Unit Price
Units Sold
Unit Variable Cost
Fixed Costs

$29
=INT(RANDNORMAL(700,100))
=RANDTRIANGULAR(6,8,11)
=RANDDISCRETE(E7:F9)

Net Cash Flow

=B4*(B3-B5)-B6

H

94

Chapter 9 Monte Carlo Simulation Using RiskSim

Figure 9.12 RiskSim Dialog Box for One-Output Example

9.8 RISKSIM OUTPUT FOR ONE-OUTPUT EXAMPLE
When you click the Simulate button, RiskSim creates a new worksheet in your Excel workbook named "RiskSim Summary 1." A summary of your inputs and the output is shown in cells L1:R9 with the accompanying histogram and cumulative distribution charts.

9.8 RiskSim Output for One-Output Example

95

Figure 9.13 RiskSim Summary Output for One-Output Example
L
M
RiskSim 2.31
Date
(current date)
Time
(current time)
Workbook
risk231e.xls
Worksheet
Simulation Model for One Output
Output Cell $B$8
Output Label Net Cash Flow
Seed
99
Trials
1000

N

O

P

Q
Mean
St. Dev.
Mean St. Error
Minimum
First Quartile
Median
Third Quartile
Maximum
Skewness

R
$2,290
$2,893
$91
-$5,702
$286
$2,381
$4,279
$10,851
-0.0176

RiskSim 2.31 - Histogram
90
80

Frequency

70
60
50
40
30
20
10
0
-$6,0 -$5,0 -$4,0 -$3,0 -$2,0 -$1,0
00
00
00
00
00
00

$0

$1,00 $2,00 $3,00 $4,00 $5,00 $6,00 $7,00 $8,00 $9,00 $10,0 $11,0
0
0
0
0
0
0
0
0
0
00
00
Net Cash Flow

RiskSim 2.31 - Cumulative Chart
1.0
0.9
Cumulative Probability

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0.0
-$6,0 -$5,0 -$4,0 -$3,0 -$2,0 -$1,0
00
00
00
00
00
00

$0

$1,00 $2,00 $3,00 $4,00 $5,00 $6,00 $7,00 $8,00 $9,00 $10,0 $11,0
0
0
0
0
0
0
0
0
0
00
00
Net Cash Flow

The histogram is based on the frequency distribution in columns I:J. The cumulative distribution is based on the sorted output values in column C and the cumulative probabilities in column D.

96

Chapter 9 Monte Carlo Simulation Using RiskSim

Figure 9.14 RiskSim Numerical Output for One-Output Example
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

B
Trial Net Cash Flow
1
$4,106
2
$1,076
3
$2,626
4
-$161
5
$4,721
6
$3,912
7
$4,488
8
$3,198
9
$4,611
10
-$784
11
-$1,283
12
$4,967
13
-$1,110
14
$1,473
15
$2,676
16
-$629
17
$5,877
18
-$2,719
19
$3,054
20
$6,984
21
$1,295
22
$3,957
23
-$522
24
$5,046
25
$3,727
26
$3,050
27
$6,857
28
$4,551
29
$850
30
$8,787
31
-$3,033
32
$3,901
33
$5,130
34
$1,134
35
$1,541
36
-$932
37
$5,885

C
D
Sorted Cumulative
-$5,702
0.0005
-$5,253
0.0015
-$5,186
0.0025
-$5,152
0.0035
-$4,716
0.0045
-$4,699
0.0055
-$4,475
0.0065
-$4,471
0.0075
-$4,382
0.0085
-$4,175
0.0095
-$4,170
0.0105
-$4,048
0.0115
-$3,821
0.0125
-$3,801
0.0135
-$3,762
0.0145
-$3,737
0.0155
-$3,645
0.0165
-$3,641
0.0175
-$3,613
0.0185
-$3,538
0.0195
-$3,503
0.0205
-$3,487
0.0215
-$3,457
0.0225
-$3,340
0.0235
-$3,334
0.0245
-$3,219
0.0255
-$3,215
0.0265
-$3,196
0.0275
-$3,173
0.0285
-$3,123
0.0295
-$3,120
0.0305
-$3,101
0.0315
-$3,093
0.0325
-$3,083
0.0335
-$3,066
0.0345
-$3,052
0.0355
-$3,048
0.0365

E

F
Percent
0%
5%
10%
15%
20%
25%
30%
35%
40%
45%
50%
55%
60%
65%
70%
75%
80%
85%
90%
95%
100%

G
Percentile
-$5,702
-$2,553
-$1,491
-$824
-$230
$286
$760
$1,143
$1,504
$1,881
$2,381
$2,790
$3,171
$3,494
$3,832
$4,279
$4,801
$5,330
$5,992
$6,973
$10,851

H

I
J
Upper Limit Frequency
-$6,000
0
-$5,500
1
-$5,000
3
-$4,500
2
-$4,000
6
-$3,500
9
-$3,000
17
-$2,500
14
-$2,000
18
-$1,500
30
-$1,000
36
-$500
42
$0
45
$500
55
$1,000
51
$1,500
71
$2,000
67
$2,500
44
$3,000
64
$3,500
78
$4,000
67
$4,500
60
$5,000
36
$5,500
50
$6,000
34
$6,500
29
$7,000
23
$7,500
13
$8,000
9
$8,500
12
$9,000
8
$9,500
3
$10,000
2
$10,500
0
$11,000
1
0

In column D, the cumulative probabilities start at 1/(2*N), where N is the number of trials, and increase by 1/N. The rationale is that the lowest ranked output value of the sampled values is an estimate of the population's values in the range from 0 to 1/N, and the lowest ranked value is associated with the median of that range.
Column B contains the original sampled output values.
Columns F:G show percentiles based on Excel's PERCENTILE worksheet function. Refer to
Excel's online help for the interpolation method used by the PERCENTILE function.
The summary measures in columns Q:R are also based on Excel worksheet functions:
AVERAGE, STDEV, QUARTILE, and SKEW.

9.9 CUSTOMIZING RISKSIM CHARTS
If the labels on the horizontal axis are numbers with many digits, some of the labels may wrap around so that some of the digits display below the others. One way to remedy this anomaly is to widen the chart (click just inside the outer border of the chart so that eight chart handles are shown

9.9 Customizing RiskSim Charts

97

and then drag the middle chart handle on the left or right to widen the chart). Another way is to select the horizontal axis (click between the labels on the horizontal axis so that "Value (X) axis" appears in the name box in the upper left of Excel) and change to a smaller font size using the Font
Size drop-down edit box on the the Formatting tool bar.
The histogram chart is a combination chart using a column chart type for the vertical bars and an
XY (Scatter) chart type for the horizontal axis. The two chart types align properly as long as the horizontal axis retains the same minimum and maximum values.
For example, if you want more spacing between the dollar labels on the horizontal axis, select the horizontal axis (so that "Value (X) axis" appears in the name box in the upper left of Excel), choose Format | Selected Axis | Scale, and change the "Major unit" from 1000 to 2000. Do not change the Minimum = –6000 or the Maximum = 11000. The histogram appears as shown below.
Figure 9.15 Original Histogram With Modified Horizontal Axis Major Unit
RiskSim 2.31 - Histogram
90
80

Frequency

70
60
50
40
30
20
10
0
-$6,000

-$4,000

-$2,000

$0

$2,000

$4,000

$6,000

$8,000

$10,000

Net Cash Flow

The cumulative chart is a standard XY (Scatter) chart type, so you can change the major unit as described above, but you can also change the minimum and maximum without affecting the integrity of the chart.
Another way to obtain more spacing on the horizontal axis of the histogram or cumulative chart is to use a custom format. For example, if you want to show values in thousands instead of the original units, select the horizontal axis (click between the labels on the horizontal axis so that
"Value (X) axis" appears in the name box in the upper left of Excel), choose Format | Selected
Axis | Number | Custom, and enter a comma at the end of the current format shown in the "Type:" edit box. After changing the original format "$#,##0" to "$#,##0," and modifying the horizontal axis title, the cumulative chart appears as shown below.

98

Chapter 9 Monte Carlo Simulation Using RiskSim

Figure 9.16 Original Cumulative Chart With Horizontal Axis Custom Format
RiskSim 2.31 - Cum ulative Chart
1.0

Cumulative Probability

0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0.0
-$6

-$5

-$4

-$3

-$2

-$1

$0

$1

$2

$3

$4

$5

$6

$7

$8

$9

$10

$11

Net Cash Flow , in thousands of dollars

9.10 RANDOM NUMBER GENERATOR FUNCTIONS
RandBinomial
Returns a random value from a binomial distribution. The binomial distribution can model a process with a fixed number of trials where the outcome of each trial is a success or failure, the trials are independent, and the probability of success is constant. RANDBINOMIAL counts the total number of successes for the specified number of trials. If n is the number of trials, the possible values for RANDBINOMIAL are the non-negative integers 0,1,...,n.
RANDBINOMIAL Syntax: RANDBINOMIAL(trials,probability_s)
Trials (often denoted n) is the number of independent trials.
Probability_s (often denoted p) is the probability of success on each trial.
RANDBINOMIAL Remarks
Returns #N/A if there are too few or too many arguments.
Returns #NAME! if an argument is text and the name is undefined.
Returns #NUM! if trials is non-integer or less than one, or probability_s is less than zero or more than one.
Returns #VALUE! if an argument is a defined name of a cell and the cell is blank or contains text.
RANDBINOMIAL Example
A salesperson makes ten unsolicited calls per day, where the probability of making a sale on each call is 70 percent. The uncertain total number of sales in one day is =RANDBINOMIAL(10,0.7)

9.10 Random Number Generator Functions

99

Probability, P(X=x)

Figure 9.17 RandBinomial Example Probability Mass Function
0.30
0.20
0.10
0.00
0

1

2

3

4

5

6

7

8

9

10

Total Numbe r of Sa le s in 10 Ca lls, x

Figure 9.18 RandBinomial Example Cumulative Probability Function
1.0

Cumulative Probability, P(X