- Email: [email protected]
Constructing Monte Carlo simulations in lotus 1-2-3
lournal of Accounrrng Education. Vol. 11, pp. I H-132. Printed in the USA. All rights reserved.
CONSTRUCTING
1993
074%5751/93 $6.00 + .OO Copyright 0 1993 Pergamon Press Ltd.
MONTE CARLO SIMULATIONS IN LOTUS l-2-3
Paul Rouse UNIVERSITY OF AUCKLAND Abstract: The purpose of this paper is to provide a detailed description
of constructing a Monte Carlo simulation in a spreadsheet context. The examples used are from cost-volumeprofit analysis, but the methods can be applied equally to capital budgeting or any other exercise involving a Monte Carlo simulation. Both empirical and theoretical distributions are covered and the Marsaglia-Bray algorithm is described for converting random variates into normalised random variates. Students therefore (a) learn how the Monte Carlo method operates and how simulation can provide insights into the riskiness of a proposal, (b) acquire some very useful skills in Lotus and in model building, and (c) are introduced to random numbers and ways in which they can be transformed into different distributions.
This paper is addressed to individuals interested in Decision Support Systems (DSS) and instructors of management accounting and accounting information system courses. The paper describes how the Monte Carlo method of simulation may be applied to accounting models using Lotus l-2-3. The basic cost-volume-profit model has been used to illustrate the application of the Monte Carlo method, but the method can be extended to other areas such as capital investment appraisal, budgeting, costing, inventory, and forecasting. One of the goals of DSS is to bring structure into a problem which is more or less unstructured in the perception of the problem owner (Van Dissel, Borgman, & Beulens, 1990). There are many basic models in accounting that assist in providing such a structure (e.g., cost-volume-profit analysis, relevant costing, economic order quantity models) but the use of more sophisticated extensions of these models has been limited. This is perhaps due to the complexity of these models and the difficulty of fitting them to a “real world” problem which is only partially understood. The general availability and ease of use of spreadsheets has enabled problem owners to build their own models, but the treatment of risk has been mainly confined to what-if analysis and sensitivity analysis. The Monte Carlo method provides a structured approach to incorporating risk into a model to supply a formal treatment and interpretation of risk, in contrast to the more intuitive approaches of what-if analysis and sensitivity analysis. The method can be used to enhance a very basic model or a sophisticated model. The treatment of risk may range from a few discrete probabilities for selected variables to the use of theoretical distributions covering many variables. The method itself does not directly improve a model but it can assist 113
114
P. Rouse
in highlighting areas where improvement is needed. In particular, it can provide valuable insights into the dynamic behaviour of a model in response to risk.’ The ease of application of the Monte Carlo method in a spreadsheet environment makes it a useful and feasible tool for decision makers. Increasingly, business schools are including spreadsheet applications as an extension of traditional courses and as an integral part of other courses. The objective is not only to teach students skills in constructing and using models, but also to experiment with levels of complexity that previously would have been considered prohibitive. The Monte Carlo method provides good examples of model building and dealing with risk as well as acquainting students with some powerful spreadsheet features. A simple cost-volume-profit example is described first, followed by an extended model incorporating interdependencies between variables. A more complex model is then illustrated using the cost-volume-profit example drawn from Jaedicke and Robichek (1964). A BASIC MODEL General Description Building the model involves four steps. First, the variables which are considered to be stochastic are chosen and their probabilities assigned. Second, a lookup table is prepared which matches the cumulative probabilities of the variables with an integer between 1 and 100. Third, a series of trials are performed which return various profit figures according to a random selection of prices, volumes, and costs. Finally, a table is prepared which summarizes the trial results. Table 1 contains the data for this example. Selling price, volume, and variable costs are considered to be stochastic, and the respective probabilities are assigned to them. The values a variable may take are assumed to be discrete, so there is, for example, no provision for the selling price to be anywhere between $11 and $12, such as $11.50. Interdependencies are ignored, so this model does not allow for any relationship between selling price and volume. This is, however, provided for later in the paper. A cumulative probability distribution is provided for each stochastic variable: in this case, selling price, volume, and variable cost. A random number between 1 and 100 is then generated which is used to “lookup” the corresponding value. For example, if the random number generated is 25, the corresponding value for the selling price would be $11, as 25 falls within the cumulative probability interval of 13-32070.Similarly, a random number of 87 would return a selling price of $8, as it falls within the interval 86‘A good summary of the advantages and disadvantages provided in Myers (1976).
of the Monte Carlo method are
115
Monte Carlo Simulations
Table 1. Data for basic model
Selling Price $12 $11 $10 $:
Volume 80000 70000 60000 50000 40000
Variable Cost $8.00 $7.00 $6.00 $5.00 $4.50 Fixed Costs
Probability
Cumulative Probability
12% 20% 30% 23% 15%
12% 32% 62% 85% 100%
Probability
Cumulative Probability
5% 12% 33% 35% 15%
5% 17% 50% 85% 100%
Probability
Cumulative Probability
9% 25% 40% 20% 6%
9% 34% 74% 94% 100% $160,000
100%. A value for each variable is obtained in a similar fashion so that the selling price might be $11, volume 40,000 units, and variable cost $6, providing a total contribution of $200,000 and profit of $40,000 after deducting the fixed costs. This would be the result of the first trial. The process is repeated for N number of trials which returns N profit figures. The profit results may then be used to derive a cumulative profitability distribution from which the likelihood of different profit (or loss) levels may be obtained. Constructing
a Lookup Table
Using Lotus l-2-3, Table 2 shows how a lookup table for this example can be set up. Each variable is represented in a column in the table and the repetition of each value corresponds to the variable’s probability specification. For example, in the column for unit volume, 80,000 is repeated 5 times, corresponding to a probability of 5%, 70,000 is repeated 12 times, corresponding to a probability of 12070,and so on. This table forms the basic lookup from which variable values will be returned to the model itself. The table is named as “TABLE” using the range-naming facility in
P. Rouse
116
Table 2. Cumulative lookup table for basic model @i@ 1 2 s 4 5
A
c
5
0
-
T-r L
1 Ll 30KUP
SP
VOLUME
vc
-_
L OOKUP
NO
c
B
A
-I-
SP
VOLUME
0 vc
NO
1
12
Bwop
I3
54
51
10
50000
6
2
12
3OOa3
8
55
32
10
50000
6
3
12
acam
8
56
53
10
50000
6
7
4
12
mm0
8
67
54
10
50000
6
8
5
12
8OOm
B
56
55
10
50000
6
Q
6
7oooo
6
59
56
10
5owo
6
10
7
12
7ww
8
60
57
10
WOO0
6
11
6
12
7cmO
8
61
66
ICI
5OooO
6
12
*
12
70000
8
62
59
10
50000
6
13
10
12
7OOQo
7
63
60
10
mm
6
14
I,
12
70000
7
64
61
10
50000
6
15
12
12
70000
7
65
62
10
50000
66
6
“12
~
6
16
13
11
76500
7
63
9
so000
6
17
t4
11
7m
7
67
64
9
sow0
6
18
IS
11
Mood
7
68
65
19
16
11
70006
7
69
66
9
50000
6
20
17
(1
70000
7
70
67
9
50000
6
9
5OOCQ
6
16
I,
60000
7
71
50000
6
22
18
II
6Q5s
7
72
69
3
SWXIO
6
23
20
11
60000
7
70
9
sow0
6
1%
21
66
9
60000
7
73 74
71
9
50000
25
22
11
60000
7
76
72
9
50000
6
26
23
It
60000
7
76
9
5OwO
8
27
24
tt
60000
7
77
73 74
9
sow0
6
28
25
11 J
60000
7
76
75
9
5QOoo
29
26
11
60000
7
79
76
9
50000
5
30
27
11
60000
7
80
77
50000 50000
5 5
24
21
6
5
31
26
11
60000
7
8,
78
9 9
32
29
1,
60000
7
82
79
9
500-30
5
33
30
11
60000
7
83
80
9
5OOQQ
5
34
31
11
60000
7
64
81
9
sOO@a
5
35
32
11
60000
7
85
82
9
50000
36
33
10
60000
7
86
83
9
50000
5
37
34
10
60000
7
87
84
9
50000
5
5
35
10
60000
6
**
85
9
5OOCQ
5
3s
36
10
WOeI
6
89
86
8
4aOQO
5
40
37
10
60000
6
90
87
8
40069
5
41
36
10
60000
6
91
88
8
40000
5
6 6
92
89
8
4oow
s
90
6
40000
5
6
93 94
91
a
4OOQO
5
38
42
39
10
60000
43 44
40 41
10
60000 60000
45 46 47 48 49 SO
10 to
6oom
6
96
92
6
4woo
5
43 44
10
60000
6
96
93
8
4Om3
5
10
60000
6
97
94
45
10
60000
6
95
40000 4oow
5 4.5
46 47
10
60000
6
96 99
8 8
10
60000
6
100
97
6 8
40000 4OOOQ
4.5 4.5
42
96
51
46
60000
6
98
8
4OWO
4.5
52
49
10 10
101
60000
6
102
99
8
40000
4.5
53
50
10
60000
6
103
100
8
4oOm
4.5
Lotus and is designated by the shaded area. The letters across the top refer to columns and the numbers down the side refer to the row numbers. The tabIe itself does not have to cover the full 100 rows corresponding to 100 percentage points, but it has the advantage of clearly demonstrating to the students how the values and probabilities are represented and in addition, provides an intuitive glimpse into how the model will work.2 ?he lookup functions in Lotus do not require the specification of every number within the intervals. The function will work perfectly well with only the breakpoints specified.
117
Monte Carlo Simulations
Constructing the Model The model is illustrated in Table 3. For each variable, a random number is generated which is then used to lookup the corresponding value in the data table and return that value to the respective variable in the next column. The random number is obtained using two Lotus functions: @RAND and @ROUND ( ). @RAND generates a random number sequence (seeded by the time) between 0 and 0.99999. @ROUND ( ) rounds off whatever number is contained within the parentheses. Thus, the formula used in Table 3 in the columns headed up RAND is: @ROUND(@RAND*100+0.5,0). @RAND is multiplied by To obtain values between decimal places. A random each trial. This random number is used in Table 3 is:
100 in order to obtain values between 0 and 99. 1 and 100, 0.5 is added and rounded to zero number is generated for each variable and for next used to lookup the data table. The formula
@VLOOKUP(key,$TABLE,column
offset),
where: key refers to the cell containing the random number, $TABLE refers to the data table-the $ makes the range absolute, and column offset refers to the number of columns to the right of the lookup referent. For example, the first selling price in trial 1 is $11, corresponding to a random number of 25. The cell containing the random number of 25 is G4 and the number of columns to the right of the lookup column in Table 2 is 1. The formula in cell H4 will be as follows: @VLOOKUP(G4,$TABLE,l). The formula, therefore, looks in cell G4 and obtains 25 for the key. It then refers to the area named TABLE which covers cells A4 . . . D103, uses the key 25 to locate the row in column A (A28) and returns the value in this row which is 1 column to the right of the lookup column (i.e., B28) which is $11. The formulae in the other columns are similar except the column offsets are different. Thus,
E
”
9
SP
.I
20
RAND
I VOLUME
J SALES
K
38
a’
57
5
6
7
I 1
8
9
10
I I
46 47 63 27.
a 12 12 9 10
4
12
‘, : al
53
76
35
36
37.
36
39
40
36
39
40
41
42
43
Note. Only partofthe Table is shown to conserve space
9'
32..
11
19
86
34
37
,34:.
‘.
90 .' 72 ..
9
74
33
600000 540000
60000 60000
71
a9
56
74 720000 450000
60000
50000
ia 720000
60000
56
550000
12
67
6
5
6
6
7
7
6
6
I I
360000
6
6
6
a
5
5
6
VC
I I
M
43
60000
soooo
40000
I I
50000
60
I I
720000
60000
37.
10. I I
64 41
600000
SObOO
29.
6
400000
.sdooo
10
91
a5
540000
60000
93
57
RAND
L
12
'10
36
I” I
66. 40
3' 4
6 7
440000
H
440000
RAND
G
5
TRIAL
F
I
4
5
2 .
1
pOW/COL
1
I@VLOOKUP(G4,$TABLEJ)
@ROUND(@RAND*IOO+O.~,Q)
Table 3. Trial results for basic model
160000 160000
180000
160000
160000
160000
160000
160000
160000
I
I
300000
150000
360000
300000
60000
250000
120000
I I
160000
160000 200000
160000 360000
160000
160000
160000
160000
COSTS
FIXED
0
240000
60000
240000
240000
200000
CONTRIB
N
40000
200000
80000
-60000
60000
60000
40000
PROFIT
P
119
Monte Carlo Simulations
(14,$TABLE,2)
for volume, and
@VLOOKUP (L4,$TABLE,3)
for variable cost.
QVLOOKUP
The final columns calculate the contribution and the net profit. The random numbers have been disclosed in separate columns in this example. There is, however, no reason why they cannot form part of the lookup formula. Thus, @VLOOKUP(@ROUND(@RAND*100+O.5,O),$TABLE,
column offset)
will do exactly the same except that the model will be smaller and the random numbers generated cannot be viewed. From an instructional viewpoint, the use of individual columns for the random numbers may be more helpful. The next step is to copy the formulae for as many trials as are required or can be performed given the memory of the machine being used. Summarizing
the Results
There are several functions available in Lotus which can provide useful information regarding the results of the simulation. These are: @MAX(range)
returns the highest result,
@MIN(range)
returns the lowest result,
@AVG(range)
returns the mean result, and
@STD(range)
returns the (population) standard deviation,
The first two functions provide useful information for determining the range of results which are needed for choosing the “bin values” in the next step. The mean and standard deviation can be used either to construct a distribution of sample means in an attempt to use the normal distribution properties or simply to compare competing projects in terms of risk return tradeoffs. Information concerning the likelihood of breaking even or achieving some level of profit is normally regarded as useful. This may be obtained relatively easily by using the DATA DISTRIBUTION commands offered as part of the Lotus menu. The inputs necessary to this command are the bin range and the values to be distributed. The bin range consists of the class intervals covering the range of the results which has been provided using the two functions above. The results from this may be summarized in
P. Rouse
120
the form of a table and/or graphed. Table 4 provides a summary of Table 3. There is, thus, a 60% probability that the project will breakeven and less than a 20% probability that it will earn more than $100,000.
This section has described in detail how to construct a simulation for a basic cost-volume-profit example. Although the example is very simple, students learn to appreciate the power of simulations and in particular, the principles of a Monte Carlo simulation. In addition, they also learn some useful Lotus functions and acquire experience in model building. THE BASIC MODEL WITH INTERDEPENDENCIES General Description
An enhancement to the basic model would be the ability to incorporate dependencies between variables. This can be done but there are limits. As the number of interdependent variables increases, the model becomes more
TABLE 4. Summary of results for basic model Distribution Bin Values
NO
- 160000 - 140000 - 120000 -100000 -80000 - 60000 - 40000 - 20000 0 20000 40000 60000 80000 100000 120000 140000 160000 f 80000 200000
Maximum Minimum Mean STD
200000 - .I60000 33750 86159
Cumulative %
%
1 0 0 1 2 1 5 0 6 4 7 1 4 1 0 2 0 0 5
2.5 0 0 2.5 5.0 2.5 12.5 0 15.0 10.0 17.5 2.5 10.0 2.5 0 5.0 0 0 12.5
40
100.0
2.5 2.5 2.5 5.0 10.0 12.5 25.0 25.0 40.0 50.0 67.5 70.0 80.0 83.0 83.0 88.0 88.0 88.0 100.0
@MAX @MIN @AK @ST0
(P4. (P4. (P4. (P4
. . P43) . . P43) . . P43) . . P43)
Residual %
60
20
121
Monte Carlo Simulations
Table 5. Data for dependent model Selling $12
Price
Quantity (prob) 12%
80,000
70,000 60,000 50,000
(prob)
5%
20% 45% 30% 100%
$11
20%
90,000 80,000 70,000 60,000
10% 30% 40% 20% 100%
$10
30%
110,000 100,000 90,000 80,000
10% 20% 45% 25% 100%
$9
23%
120,000 110,000 100,000 90,000
10% 30% 40% 20% 100%
$8
15%
130,000
5%
120,000 110,000 100,000
20% 45% 30% 100%
100% Note: The table is in the form of a tree structure and reads from left to right. For example, if sales are $12 with a probability of 12%, then there are four possible levels of volume associated with this price: 80,000 (prob 50/o), 70,000 (prob 20%), 60,000 (prob 45%) and 50,000 (prob 30%).
complex and more cumbersome. If, however, there are only a few dependent variables, the model can be modified without becoming too complex. In this example, volume is assumed to be dependent upon selling price and the relationships are contained in Table 5. Variable cost and fixed costs remain as in Table 1.
122
P. Rouse
Construc~ng the Lookup Tabfe The data table is constructed in a similar way to that described previously, except that the independent variable, selling price, is entered across the top of the table. Table 6 illustrates how this can be entered. The selling prices are entered across columns B through F in row 2 and the lookup numbers (cumulative probabilities), in row 1. Thus, a $12 selling price with a probability of 12% is entered in column B, row 2, with the lookup range lower value of 1 being entered immediately above it. The $11 selling price is entered in C2, and its lookup range lower value of 13 is entered above it. Row 2 thus contains the prices corresponding to the cumulative probabilities. For example, $12 corresponds to a lookup range of 1-12 being 12% probability, $1 I corresponds to a lookup range of 13-32 being 20% probability, $10 corresponds to a lookup range of 33-62 being 30% probability, etc. The range is then named in order to refer to it in the lookup functions. In this example, the range Bl . . . F2 is named SP and is shaded in Table 6. Each selling price has a series of possible volumes associated with it, and these are entered into the columns below each price. Thus, column B contains the volumes and repetition of values corresponding to a selling price of $12, as described in Table 5. Variable cost is entered in a separate column, as it is not dependent upon selling price. If it were, then it could be entered in the same way as volume. The range is then named in order to refer to it in the lookup functions. In this example, the range A5 . . . Cl04 is named TABLE and is shaded in Table 6. Constructing the Model Table 7 contains the model. As in the basic model, a random number is generated and used to lookup the relevant section in the data table. This time, however, the model uses a horizontal lookup for selling price: @HLOOKUP(key,$SP,row
offset),
where: key refers to the cell containing the random number, $SP refers to the selling price range in the data table, and row offset refers to the number of rows below the lookup referent. In this example, the key is the cell containing the random number (e.g., 54). The number in ceil 54 is 30, and the function refers to the range named by SP (Bl . . . F2) and looks across row 1 for the lookup range in which 30 falls. This is column C, which corresponds to a selling price of $11 for any lookup value between 13 and 32. The row offset is t and $11 is returned. The formula in cell K4 is as follows:
Monte Carlo Simulations
Table 6. Cumulative
B
A
123
lookup table for dependent
C
D
E
model
F
G
L C UM.
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 I 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
63 $9
PROB
P'RICE VOLUME LDOKUP
NO I . 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
80000 80000 80000 80000 80000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000
vc 90000 90000 90000 90000 90000 90000 90000 90000 90000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000
110000 110000 110000 110000 110000 110000 110000 110000 110000 110000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000
I
I
60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000
80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000
90000
120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 110000 110000 110000 110000 110000 110000 110000 110000 110000 110000
130000 130000 130000 130000 130000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000
I 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
8 8 8 8 8 8 8 8 8 8 7 7 7 7 7 7 7 7 7 7 I
soodo 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000
I
90000 90000 90000 90000
90000 90000
90000 90000 90000 90000 90000 90000 90000
90000 90000 90000 90000
90000
100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000
5 5 5 5 5 5 5 5 5 5 5 5 5 4. 4. 4.3 4. 4.
58
10
30
37
38
39
40
40 41
42
43
Note. Only part of the Table
75
36
39
K
10
11
11
9
10
9
I
I
10
9
9
10
10
11
SP
is shown to conserve
47
21
60
35
38
I
83
9
80
73
I
41
7
6
I
52
6
1
21
I
30 (---Yq
RAND
5
TRIAL
J
4
3
2
1
ROW/COL
I
I@HLOOKUP(J4,$SP,lb
space
85
45
82
1
74
64
I I
61
82
43
39
79
96
N
80000
70000
60000
120000
90000
100000
I I
90000
90000
1 ooobo
90000
80000
60000
VOLUME
38 I=]
RAND
L
800000
770000
660000
900000 1080000
I 900000
I
900000
810000
900000
900000
800000
660000
880000
SALES
0
I [@VLOOKUP(L4,$TASLE,$E$2-K4+1) P
model
83
84
55
56
13
17
I I
5
71
82
17
78
75
71 [Sj
RAND
Table 7. Trial results for dependent
Q
5
5
6
6
7
7
I I
8
6
5
7
5
6:
VC
400000
420000
300000
360000
270000
200000
I I
180000
270000
400000
270000
400000
300000
400000
CONTRIS
160000
160000
160000
160000
160000
160000
I I
160000
160000
160000
160000
160000
160000
160000
COSTS
FIXED
@VL~KUP(H4,$TAElLE,S) R S
240000
260000
140000
200000
110000
40000
I I
20000
110000
240000
110000
240000
140000
240000
PROFIT
T
I
125
Monte Carlo Simulations
@HLOOKUP
(J4,$SP, 1).
The other variables (volume and variable cost), use the vertical lookup function described previously with a modification to the column offset in the case of volume. Thus, cell N4 contains the following function: @VLOOKUP (L4,$TABLE,$B$2
- K4 + 1).
Cell L4 contains the random number and TABLE refers to the range containing the data for volume and variable cost. A problem arises in how to determine the column offset. One way to do this is to take the difference between the first selling price in the SP table in cell B2 ($12) and the selling price returned for the particular trial (in this case, $11). The difference of 1 is incremented by 1 to obtain the column offset of 2. Thus, the random number in cell L4 is 38 and the value returned by the function after looking up 38 in the data table and going across 2 columns is 80,000. The function for variable cost is as described previously with a column offset of 6. The remaining parts of the model are as described for the basic model. Summary This section has described a simple method of incorporating variable dependencies into the model. Obviously, the model can quickly become cumbersome as the number of dependent variables increases, but in its simple form it provides a useful learning exercise for the student. There are, of course, other ways of arranging the data table and of determining the column offset for the dependent variable, and students should be encouraged to explore possible variations.
USING NORMALISED
RANDOM NUMBERS
General Description It is frequently the case that some of the variables in a simulation may be normally distributed or may be assumed to be normally distributed. This enables probabilities to be assigned to possible results, as in the analysis provided by Jaedicke and Robichek (1964). Unfortunately, a problem arises where several variables are uncertain, as the product of two independent and normally distributed variables can only approximate normality if the sum of the two coefficients of variation is less than or equal to 12% as described in Ferrara, Hayya, and Nachman (1972). If this is not the case, then it can not be assumed that the result is normally distributed. The Monte Carlo method is a useful way of overcoming this problem.
126
P. Rouse
An Algorithm
for Deriving Normalised
Random Numbers
There are several methods for generating normally distributed pseudorandom numbers, and they all use the transformation 2 = (X - ~)/a, so that the distribution generated is normally distributed with mean equal zero and standard deviation one. This distribution is then converted to the desired normal distribution by using the relationship X = p + (RNi’V)a where RNN is a normal variate lying between zero and one. A good algorithm is provided by Marsaglia and Bray (1964). Two random numbers are generated r-2 and r2. Then, setting V1 = - 1 + 2 * rl and V2 = - 1 + 2 * r2, S = VZ-.2 + V2^.2 is computed. If S L 1 the process is repeated until S < 1. Then, XI = V1 * SQRT(( -2 * LnS/S) X2 = V2 * SQRT(( -2 * LnS/S) where: XI and X2 are the normally distributed numbers, and LnS is the natural logarithm of S. Constructing
the Model
Table 8 shows a section of the worksheet for generating the normal numbers and the macros for copying them to the relevant sections of the model. Thus, VI and V2 are calculated in cells Bl and B2 and the sum of these squared is calculated in B3. The equations for XI and X2 above are contained in cells B4 and BS and will return normal numbers provided that B3 (or s) is less than 1; otherwise an ERR message will appear as the result of trying to take the square root of a negative number, as LnS is positive whenS 2 1. There are four macros and three named ranges (although more can be named according to the designer’s preferences). The first macro, \ A, asks for the number of random numbers required, and as the algorithm returns two normal numbers on each successful pass, the response should be half the number required (e.g., a response of 50 will generate 100 normal numbers). The macro then calls SUBRl which suppresses the screen display while the numbers are being generated and copied to the target area of the worksheet. Note that it also turns on the manual recalculation in order to speed the macros up. This can be changed manually at the end or by amending SUBR2 to read {PANELON) {WINDOWSON} (INDICATE)/WORA{RETURN). This macro requires two range names: NO and COUNT. In this example, cells B7 and B8 have been given these names and are used to record (auto-
No Count
5 6
I
\B
G5 Calculates cells Bl to 85 until S (cell 83) is less than 1.
Inputs how many random numbers are required and stores the value in cell 87. G2 Calls subroutine SUBRl to turn off screen and then loops through macro \B according to the number in 87. G3 Calls subroutine SUBR2 which restores the screen operations.
&If Gl
Explanation of Macros
7 8
SUBRl SUBRP \A
NO RANDOM
COUNT
D
GQ GIO Gi G5
El..85
88 87
E
\B
\A
algorithm I J K
L
(INDICATE “WAIT”) (PANELOFF) {WlNDOWSOFF)/IjGRM{RETURN) (PANELON) {WINDOWSON) {INDICATE) {RETURf$
(RECALC random) (IF B32=1) (BRANCH \B) /RV$b$4..$b$5w * (DOWN 2) (return)
,,,
“Enter half the number required’? ?‘,no)
H
(SUBRI ) (for count,1 ,no,i ,\B) (SUBRP) (quit)
(GETNUMBER
G
GQ Displays the message “WAIT”, turns off the screen operations and sets the worksheet recalculation to manual. GIO Restores the screen operation to normal. (Note that recalculation Is still set to manual.
G6 Copies the transformed numbers in 84 and 85 to where the cursor is located. G7 Returns control to macro \A.
SUBRl SUBRP
F
Table 8. Macros to perform the Marsaglia-Bray
;
,,,,,
M
128
P. Rouse
matically) the number of random numbers required and the counter value. The \B macro or subroutine firstly calculates the formulae in cells Bl . . . B5, which has been named RANDOM. If S or the value in cell B3 is greater than or equal to one, then it recalculates Bl . . . B5 again. The RECALC command is used to calculate only the 5 cells Bl . . . B5 (the area named RANDOM), thus avoiding time recalculating the whole worksheet. The second line of the macro copies the values only (hence the use of the RANGE VALUE command rather than the COPY command) of cells B4 and B5 to the target column. It is, therefore, critical that the cursor is placed at the top of the target column in which the normal numbers are to be copied to before the macro is started. Once the COUNT has reached the value contained in NO (cell B7), the subroutine returns control to \A which then calls SUBR2 which restores the normal window and panel operations. Note that the macro 1 B refers to cells B3 and B4 . . . B5. If the RANDOM area is placed anywhere else in the worksheet, then the corresponding cells must be entered in 1 B to replace these addresses. Further, B4 . . . B5 or their equivalents must be absolute addresses (i.e., $B$4 . . , $B$5). The main model is contained in Table 9. The example used is from Jaedicke and Robichek (1964), and 10 trials are carried out. The procedure is (a) generate four columns of normal random numbers for price, volume, variable cost, and fixed cost; and (b) use these numbers to calculate each variable’s value and result for each trial. Thus, for trial 1, the quantity is obtained by multiplying the normal number for Volume 1.195 (cell B21) by the standard deviation 400, and adding the result to the mean volume of 5000 to obtain 5478. A similar calculation is made for the other variables and the profit obtained. This is then repeated for the number of desired trials. The procedures for summarizing the results are as described previously. LOTUS ADDIN TOOLKIT FOR VERSION 3 Lotus have recently introduced the Addin Toolkit for Version 3 which enables users to write their own @ functions for use within a spreadsheet. It is, therefore, possible to write an @ function to generate a normal number. Table 10 contains a program listing which will allow a user to use a function called @NORMAL which will provide a normal random number. The @ function can only return one result so the X2 value is dropped and only XI is returned. Thus, cells B21 . . . E30 in Table 9 can be replaced by the function @NORMAL. Table 11 shows the same model, except it is using the @NORMAL function. This results in a more compact, simplified model. Unfortunately, the function is not automatically recalculated each time the worksheet is recalculated. This can be overcome, however, by writing a macro that simply recalculates the cells containing the addin func-
L \o
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
B
Normal Variates
$5,800
Mean 5000 $3,000 $1,750
C
$50 $75 $100
400
Std Deviation
D
E
1 2 3 4 5 6 7 8 9 10
1.195 -0.474 1.451 0.201 -1.132 -1.954 -0.488 2.142 -2.308 0.076
0.863 0.139 0.609 -0.244 1.842 0.262 0.630 -0.260 -1.760 -0.938 0.502 -0.184 -0.945 0.120 -1.374 1.191 1.337 0.114 0.711 1.116
0.892 -0.210 -0.440 -0.033 -0.012 0.550 -0.374 -0.562 -0.995 -0.321
G
H
I
5478 4810 5580 5080 4547 4218 4805 5857 4077 5030 3043 3007 3030 2968 3092 3013 3031 2987 2912 2953
____-- _ ------
Var cost 1788 1736 1679 1759 1647 1839 1650 1759 1603 1834
_ ------
IL
Sales Price
r-r Volume
1255 1271 1351 1229 1445 1174 1181 1228 1109 1119
t$C$13t$D$13'B21 ~t$C$l4+$D$i4*C21 +$C$l5t$D$15'D21
F
Table 9. Trial results for normalised model
(3) (1) (2) (4) ._____-- _______- ____--____ ____---- ___----_
1-RIAL
I)ata \I folume EMing Price \fadablecost F:ixedCosts (000s)
JROW/COI
A J
6113 7541 6243 6571 4951 5675 7195 4520
I
L
1
5889 5779 5756 5797 5799 5855 5763 5744 5701 5768
988 334 1785 446 772 -904 -67 1451 -1181 -138
Profit FixCost (000s) (000s) .___---- __---_____-
+$C$l6t$D$16'E21
K
130
P. Rouse
Table 10. Program listing for ~NOR~AL
addin
use Register - Register the @ function Register (“Normal”, AT-Function)
-Returns one normal number from 2 random numbers -using the Marsaglia-Bray algorithm procedure Normal returns (float xl) is float vl , v2, s, rl, r2 loop rl = random r2 = random vi = 2*r1-1 v2 = 2*r2-1 s = v1:2 + v2:2 exit when s < 1 end loop xl = vl “sqrt (-2*ln(s)/s) end procedure Normal
tions. For example, assuming that the range D27 . . . 536 had been named “norm,” ( RECALC norm> will recalculate the normal numbers each time the macro is run. Summary
This section has described some methods for generating normal numbers and incorporating them into a simulation. The methods can be extended to other distributions, such as the triangular and beta distributions. In addition, it has described how a function can be written and incorporated into the worksheet to create a simpler, more elegant modet. SUMMARY This paper has described in detail how simulations using the Monte Carlo method may be set up using Lotus I-2-3. The method can be used for a very simple extension, as was illustrated in the first example using a basic
6,
‘olume ;elling Price ‘ariable Cost :ixed Costs (000s)
5000
E F
G
5318 4949
3 4 5 6 7 8 9 10
29
30
31
32
33
34
.35
36
4648
4966
4773
5149
4950
4803
4004
1 -b 2
Var cost
2988
2984
2916
3012
3026
3027
3023
2930
2969
2969 4
Sales Price
5072 b
Volume
1702
1573
1830
1766
1746
1790
1781
1711
1789
1822
Unit Cont 1147
1287
1410
1086
1246
1280
1237
1262
1219
1180
H
8367
7501
5049
6188
8108
6367
6247
5856
5762
I J
k
5630
5856
5816
5641
5690
5822
5814
5975
Profit
1
737
1645
-767
547
418
545
433
-186 -119
.(OO-)
.594il
5615.
WOs)
Fix cost
+$C$l s+$Q$i.6’f@Noef!@L:~~
5616 A
(000s)
Cont
+$C$l3+$D$l3’@NO~MAL I +$C$14+$D$143@NORMAL I +$C$15+$D$15”@NORMAL
$50 $75 $100
400
D Std :Deviation
27
TRIAL
$3,000 $1,750 $5,600
‘j$eJl
C
'26
26
25
24
23
.22
'21
20
19
16
17
'16
'15
'14
)ataltwutj
B
12
A
.I3
[ROWICOI
Table 11. Trial results for normalised model using @NORMAL addin
7
132
P. Rouse
model, or for more sophisticated applications, as described in the other examples. Risk may be incorporated, either by specifying a set of discrete probabilities, or by using one of several available theoretical distributions, such as the normal, triangular or beta distributions. The Monte Carlo method is relatively simple to understand, particularly when illustrated in a spreadsheet context. Although computer memory requirements limit the number of trials that can be performed, a sufficient number should be possible to enable an adequate distribution of results to be obtained. For instructors, this can be a valuable learning exercise for students (a) to learn how to construct a simulation model, (b) to learn some different functions and commands in Lotus, (c) to appreciate how simulation can assist in providing insights into the riskiness of a proposal, and (d) to learn about random numbers and how they can be transformed into different distributions. REFERENCES Ferrara, W. L., Hayya, J. C., & Nachman, D. A. (1972, April). Normalcy of profit in the Jaedicke-Robichek Model. The Accounting Review, XL VII(2), 299-307. Jaedicke, R. K., & Robichek, A. A. (1964, October). Cost-volume-profit analysis under conditions of uncertainty. The Accounfing Review, XxX1X(4), 917-926. Marsaglia, G., & Bray, T. A. (1964). A convenient method for generating normal variables. SIAMReview (Society for Industrial and Applied Mathematics), 6, 260-264. Myers, S. C. (1976). Postscript: Using simulation for risk analysis. In S. C. Myers (Ed.), Modern developments in financial management. New York: Praeger. Van Dissel, H. Ci., Borgman, H. P., & Beulens, A. J. M. (1990). Task-allocation between DSS and problem owner: The example of Box & Jenkins Time Series Analysis. Decision Support Systems, 6, 339-345.
CONSTRUCTING
1993
074%5751/93 $6.00 + .OO Copyright 0 1993 Pergamon Press Ltd.
MONTE CARLO SIMULATIONS IN LOTUS l-2-3
Paul Rouse UNIVERSITY OF AUCKLAND Abstract: The purpose of this paper is to provide a detailed description
of constructing a Monte Carlo simulation in a spreadsheet context. The examples used are from cost-volumeprofit analysis, but the methods can be applied equally to capital budgeting or any other exercise involving a Monte Carlo simulation. Both empirical and theoretical distributions are covered and the Marsaglia-Bray algorithm is described for converting random variates into normalised random variates. Students therefore (a) learn how the Monte Carlo method operates and how simulation can provide insights into the riskiness of a proposal, (b) acquire some very useful skills in Lotus and in model building, and (c) are introduced to random numbers and ways in which they can be transformed into different distributions.
This paper is addressed to individuals interested in Decision Support Systems (DSS) and instructors of management accounting and accounting information system courses. The paper describes how the Monte Carlo method of simulation may be applied to accounting models using Lotus l-2-3. The basic cost-volume-profit model has been used to illustrate the application of the Monte Carlo method, but the method can be extended to other areas such as capital investment appraisal, budgeting, costing, inventory, and forecasting. One of the goals of DSS is to bring structure into a problem which is more or less unstructured in the perception of the problem owner (Van Dissel, Borgman, & Beulens, 1990). There are many basic models in accounting that assist in providing such a structure (e.g., cost-volume-profit analysis, relevant costing, economic order quantity models) but the use of more sophisticated extensions of these models has been limited. This is perhaps due to the complexity of these models and the difficulty of fitting them to a “real world” problem which is only partially understood. The general availability and ease of use of spreadsheets has enabled problem owners to build their own models, but the treatment of risk has been mainly confined to what-if analysis and sensitivity analysis. The Monte Carlo method provides a structured approach to incorporating risk into a model to supply a formal treatment and interpretation of risk, in contrast to the more intuitive approaches of what-if analysis and sensitivity analysis. The method can be used to enhance a very basic model or a sophisticated model. The treatment of risk may range from a few discrete probabilities for selected variables to the use of theoretical distributions covering many variables. The method itself does not directly improve a model but it can assist 113
114
P. Rouse
in highlighting areas where improvement is needed. In particular, it can provide valuable insights into the dynamic behaviour of a model in response to risk.’ The ease of application of the Monte Carlo method in a spreadsheet environment makes it a useful and feasible tool for decision makers. Increasingly, business schools are including spreadsheet applications as an extension of traditional courses and as an integral part of other courses. The objective is not only to teach students skills in constructing and using models, but also to experiment with levels of complexity that previously would have been considered prohibitive. The Monte Carlo method provides good examples of model building and dealing with risk as well as acquainting students with some powerful spreadsheet features. A simple cost-volume-profit example is described first, followed by an extended model incorporating interdependencies between variables. A more complex model is then illustrated using the cost-volume-profit example drawn from Jaedicke and Robichek (1964). A BASIC MODEL General Description Building the model involves four steps. First, the variables which are considered to be stochastic are chosen and their probabilities assigned. Second, a lookup table is prepared which matches the cumulative probabilities of the variables with an integer between 1 and 100. Third, a series of trials are performed which return various profit figures according to a random selection of prices, volumes, and costs. Finally, a table is prepared which summarizes the trial results. Table 1 contains the data for this example. Selling price, volume, and variable costs are considered to be stochastic, and the respective probabilities are assigned to them. The values a variable may take are assumed to be discrete, so there is, for example, no provision for the selling price to be anywhere between $11 and $12, such as $11.50. Interdependencies are ignored, so this model does not allow for any relationship between selling price and volume. This is, however, provided for later in the paper. A cumulative probability distribution is provided for each stochastic variable: in this case, selling price, volume, and variable cost. A random number between 1 and 100 is then generated which is used to “lookup” the corresponding value. For example, if the random number generated is 25, the corresponding value for the selling price would be $11, as 25 falls within the cumulative probability interval of 13-32070.Similarly, a random number of 87 would return a selling price of $8, as it falls within the interval 86‘A good summary of the advantages and disadvantages provided in Myers (1976).
of the Monte Carlo method are
115
Monte Carlo Simulations
Table 1. Data for basic model
Selling Price $12 $11 $10 $:
Volume 80000 70000 60000 50000 40000
Variable Cost $8.00 $7.00 $6.00 $5.00 $4.50 Fixed Costs
Probability
Cumulative Probability
12% 20% 30% 23% 15%
12% 32% 62% 85% 100%
Probability
Cumulative Probability
5% 12% 33% 35% 15%
5% 17% 50% 85% 100%
Probability
Cumulative Probability
9% 25% 40% 20% 6%
9% 34% 74% 94% 100% $160,000
100%. A value for each variable is obtained in a similar fashion so that the selling price might be $11, volume 40,000 units, and variable cost $6, providing a total contribution of $200,000 and profit of $40,000 after deducting the fixed costs. This would be the result of the first trial. The process is repeated for N number of trials which returns N profit figures. The profit results may then be used to derive a cumulative profitability distribution from which the likelihood of different profit (or loss) levels may be obtained. Constructing
a Lookup Table
Using Lotus l-2-3, Table 2 shows how a lookup table for this example can be set up. Each variable is represented in a column in the table and the repetition of each value corresponds to the variable’s probability specification. For example, in the column for unit volume, 80,000 is repeated 5 times, corresponding to a probability of 5%, 70,000 is repeated 12 times, corresponding to a probability of 12070,and so on. This table forms the basic lookup from which variable values will be returned to the model itself. The table is named as “TABLE” using the range-naming facility in
P. Rouse
116
Table 2. Cumulative lookup table for basic model @i@ 1 2 s 4 5
A
c
5
0
-
T-r L
1 Ll 30KUP
SP
VOLUME
vc
-_
L OOKUP
NO
c
B
A
-I-
SP
VOLUME
0 vc
NO
1
12
Bwop
I3
54
51
10
50000
6
2
12
3OOa3
8
55
32
10
50000
6
3
12
acam
8
56
53
10
50000
6
7
4
12
mm0
8
67
54
10
50000
6
8
5
12
8OOm
B
56
55
10
50000
6
Q
6
7oooo
6
59
56
10
5owo
6
10
7
12
7ww
8
60
57
10
WOO0
6
11
6
12
7cmO
8
61
66
ICI
5OooO
6
12
*
12
70000
8
62
59
10
50000
6
13
10
12
7OOQo
7
63
60
10
mm
6
14
I,
12
70000
7
64
61
10
50000
6
15
12
12
70000
7
65
62
10
50000
66
6
“12
~
6
16
13
11
76500
7
63
9
so000
6
17
t4
11
7m
7
67
64
9
sow0
6
18
IS
11
Mood
7
68
65
19
16
11
70006
7
69
66
9
50000
6
20
17
(1
70000
7
70
67
9
50000
6
9
5OOCQ
6
16
I,
60000
7
71
50000
6
22
18
II
6Q5s
7
72
69
3
SWXIO
6
23
20
11
60000
7
70
9
sow0
6
1%
21
66
9
60000
7
73 74
71
9
50000
25
22
11
60000
7
76
72
9
50000
6
26
23
It
60000
7
76
9
5OwO
8
27
24
tt
60000
7
77
73 74
9
sow0
6
28
25
11 J
60000
7
76
75
9
5QOoo
29
26
11
60000
7
79
76
9
50000
5
30
27
11
60000
7
80
77
50000 50000
5 5
24
21
6
5
31
26
11
60000
7
8,
78
9 9
32
29
1,
60000
7
82
79
9
500-30
5
33
30
11
60000
7
83
80
9
5OOQQ
5
34
31
11
60000
7
64
81
9
sOO@a
5
35
32
11
60000
7
85
82
9
50000
36
33
10
60000
7
86
83
9
50000
5
37
34
10
60000
7
87
84
9
50000
5
5
35
10
60000
6
**
85
9
5OOCQ
5
3s
36
10
WOeI
6
89
86
8
4aOQO
5
40
37
10
60000
6
90
87
8
40069
5
41
36
10
60000
6
91
88
8
40000
5
6 6
92
89
8
4oow
s
90
6
40000
5
6
93 94
91
a
4OOQO
5
38
42
39
10
60000
43 44
40 41
10
60000 60000
45 46 47 48 49 SO
10 to
6oom
6
96
92
6
4woo
5
43 44
10
60000
6
96
93
8
4Om3
5
10
60000
6
97
94
45
10
60000
6
95
40000 4oow
5 4.5
46 47
10
60000
6
96 99
8 8
10
60000
6
100
97
6 8
40000 4OOOQ
4.5 4.5
42
96
51
46
60000
6
98
8
4OWO
4.5
52
49
10 10
101
60000
6
102
99
8
40000
4.5
53
50
10
60000
6
103
100
8
4oOm
4.5
Lotus and is designated by the shaded area. The letters across the top refer to columns and the numbers down the side refer to the row numbers. The tabIe itself does not have to cover the full 100 rows corresponding to 100 percentage points, but it has the advantage of clearly demonstrating to the students how the values and probabilities are represented and in addition, provides an intuitive glimpse into how the model will work.2 ?he lookup functions in Lotus do not require the specification of every number within the intervals. The function will work perfectly well with only the breakpoints specified.
117
Monte Carlo Simulations
Constructing the Model The model is illustrated in Table 3. For each variable, a random number is generated which is then used to lookup the corresponding value in the data table and return that value to the respective variable in the next column. The random number is obtained using two Lotus functions: @RAND and @ROUND ( ). @RAND generates a random number sequence (seeded by the time) between 0 and 0.99999. @ROUND ( ) rounds off whatever number is contained within the parentheses. Thus, the formula used in Table 3 in the columns headed up RAND is: @ROUND(@RAND*100+0.5,0). @RAND is multiplied by To obtain values between decimal places. A random each trial. This random number is used in Table 3 is:
100 in order to obtain values between 0 and 99. 1 and 100, 0.5 is added and rounded to zero number is generated for each variable and for next used to lookup the data table. The formula
@VLOOKUP(key,$TABLE,column
offset),
where: key refers to the cell containing the random number, $TABLE refers to the data table-the $ makes the range absolute, and column offset refers to the number of columns to the right of the lookup referent. For example, the first selling price in trial 1 is $11, corresponding to a random number of 25. The cell containing the random number of 25 is G4 and the number of columns to the right of the lookup column in Table 2 is 1. The formula in cell H4 will be as follows: @VLOOKUP(G4,$TABLE,l). The formula, therefore, looks in cell G4 and obtains 25 for the key. It then refers to the area named TABLE which covers cells A4 . . . D103, uses the key 25 to locate the row in column A (A28) and returns the value in this row which is 1 column to the right of the lookup column (i.e., B28) which is $11. The formulae in the other columns are similar except the column offsets are different. Thus,
E
”
9
SP
.I
20
RAND
I VOLUME
J SALES
K
38
a’
57
5
6
7
I 1
8
9
10
I I
46 47 63 27.
a 12 12 9 10
4
12
‘, : al
53
76
35
36
37.
36
39
40
36
39
40
41
42
43
Note. Only partofthe Table is shown to conserve space
9'
32..
11
19
86
34
37
,34:.
‘.
90 .' 72 ..
9
74
33
600000 540000
60000 60000
71
a9
56
74 720000 450000
60000
50000
ia 720000
60000
56
550000
12
67
6
5
6
6
7
7
6
6
I I
360000
6
6
6
a
5
5
6
VC
I I
M
43
60000
soooo
40000
I I
50000
60
I I
720000
60000
37.
10. I I
64 41
600000
SObOO
29.
6
400000
.sdooo
10
91
a5
540000
60000
93
57
RAND
L
12
'10
36
I” I
66. 40
3' 4
6 7
440000
H
440000
RAND
G
5
TRIAL
F
I
4
5
2 .
1
pOW/COL
1
I@VLOOKUP(G4,$TABLEJ)
@ROUND(@RAND*IOO+O.~,Q)
Table 3. Trial results for basic model
160000 160000
180000
160000
160000
160000
160000
160000
160000
I
I
300000
150000
360000
300000
60000
250000
120000
I I
160000
160000 200000
160000 360000
160000
160000
160000
160000
COSTS
FIXED
0
240000
60000
240000
240000
200000
CONTRIB
N
40000
200000
80000
-60000
60000
60000
40000
PROFIT
P
119
Monte Carlo Simulations
(14,$TABLE,2)
for volume, and
@VLOOKUP (L4,$TABLE,3)
for variable cost.
QVLOOKUP
The final columns calculate the contribution and the net profit. The random numbers have been disclosed in separate columns in this example. There is, however, no reason why they cannot form part of the lookup formula. Thus, @VLOOKUP(@ROUND(@RAND*100+O.5,O),$TABLE,
column offset)
will do exactly the same except that the model will be smaller and the random numbers generated cannot be viewed. From an instructional viewpoint, the use of individual columns for the random numbers may be more helpful. The next step is to copy the formulae for as many trials as are required or can be performed given the memory of the machine being used. Summarizing
the Results
There are several functions available in Lotus which can provide useful information regarding the results of the simulation. These are: @MAX(range)
returns the highest result,
@MIN(range)
returns the lowest result,
@AVG(range)
returns the mean result, and
@STD(range)
returns the (population) standard deviation,
The first two functions provide useful information for determining the range of results which are needed for choosing the “bin values” in the next step. The mean and standard deviation can be used either to construct a distribution of sample means in an attempt to use the normal distribution properties or simply to compare competing projects in terms of risk return tradeoffs. Information concerning the likelihood of breaking even or achieving some level of profit is normally regarded as useful. This may be obtained relatively easily by using the DATA DISTRIBUTION commands offered as part of the Lotus menu. The inputs necessary to this command are the bin range and the values to be distributed. The bin range consists of the class intervals covering the range of the results which has been provided using the two functions above. The results from this may be summarized in
P. Rouse
120
the form of a table and/or graphed. Table 4 provides a summary of Table 3. There is, thus, a 60% probability that the project will breakeven and less than a 20% probability that it will earn more than $100,000.
This section has described in detail how to construct a simulation for a basic cost-volume-profit example. Although the example is very simple, students learn to appreciate the power of simulations and in particular, the principles of a Monte Carlo simulation. In addition, they also learn some useful Lotus functions and acquire experience in model building. THE BASIC MODEL WITH INTERDEPENDENCIES General Description
An enhancement to the basic model would be the ability to incorporate dependencies between variables. This can be done but there are limits. As the number of interdependent variables increases, the model becomes more
TABLE 4. Summary of results for basic model Distribution Bin Values
NO
- 160000 - 140000 - 120000 -100000 -80000 - 60000 - 40000 - 20000 0 20000 40000 60000 80000 100000 120000 140000 160000 f 80000 200000
Maximum Minimum Mean STD
200000 - .I60000 33750 86159
Cumulative %
%
1 0 0 1 2 1 5 0 6 4 7 1 4 1 0 2 0 0 5
2.5 0 0 2.5 5.0 2.5 12.5 0 15.0 10.0 17.5 2.5 10.0 2.5 0 5.0 0 0 12.5
40
100.0
2.5 2.5 2.5 5.0 10.0 12.5 25.0 25.0 40.0 50.0 67.5 70.0 80.0 83.0 83.0 88.0 88.0 88.0 100.0
@MAX @MIN @AK @ST0
(P4. (P4. (P4. (P4
. . P43) . . P43) . . P43) . . P43)
Residual %
60
20
121
Monte Carlo Simulations
Table 5. Data for dependent model Selling $12
Price
Quantity (prob) 12%
80,000
70,000 60,000 50,000
(prob)
5%
20% 45% 30% 100%
$11
20%
90,000 80,000 70,000 60,000
10% 30% 40% 20% 100%
$10
30%
110,000 100,000 90,000 80,000
10% 20% 45% 25% 100%
$9
23%
120,000 110,000 100,000 90,000
10% 30% 40% 20% 100%
$8
15%
130,000
5%
120,000 110,000 100,000
20% 45% 30% 100%
100% Note: The table is in the form of a tree structure and reads from left to right. For example, if sales are $12 with a probability of 12%, then there are four possible levels of volume associated with this price: 80,000 (prob 50/o), 70,000 (prob 20%), 60,000 (prob 45%) and 50,000 (prob 30%).
complex and more cumbersome. If, however, there are only a few dependent variables, the model can be modified without becoming too complex. In this example, volume is assumed to be dependent upon selling price and the relationships are contained in Table 5. Variable cost and fixed costs remain as in Table 1.
122
P. Rouse
Construc~ng the Lookup Tabfe The data table is constructed in a similar way to that described previously, except that the independent variable, selling price, is entered across the top of the table. Table 6 illustrates how this can be entered. The selling prices are entered across columns B through F in row 2 and the lookup numbers (cumulative probabilities), in row 1. Thus, a $12 selling price with a probability of 12% is entered in column B, row 2, with the lookup range lower value of 1 being entered immediately above it. The $11 selling price is entered in C2, and its lookup range lower value of 13 is entered above it. Row 2 thus contains the prices corresponding to the cumulative probabilities. For example, $12 corresponds to a lookup range of 1-12 being 12% probability, $1 I corresponds to a lookup range of 13-32 being 20% probability, $10 corresponds to a lookup range of 33-62 being 30% probability, etc. The range is then named in order to refer to it in the lookup functions. In this example, the range Bl . . . F2 is named SP and is shaded in Table 6. Each selling price has a series of possible volumes associated with it, and these are entered into the columns below each price. Thus, column B contains the volumes and repetition of values corresponding to a selling price of $12, as described in Table 5. Variable cost is entered in a separate column, as it is not dependent upon selling price. If it were, then it could be entered in the same way as volume. The range is then named in order to refer to it in the lookup functions. In this example, the range A5 . . . Cl04 is named TABLE and is shaded in Table 6. Constructing the Model Table 7 contains the model. As in the basic model, a random number is generated and used to lookup the relevant section in the data table. This time, however, the model uses a horizontal lookup for selling price: @HLOOKUP(key,$SP,row
offset),
where: key refers to the cell containing the random number, $SP refers to the selling price range in the data table, and row offset refers to the number of rows below the lookup referent. In this example, the key is the cell containing the random number (e.g., 54). The number in ceil 54 is 30, and the function refers to the range named by SP (Bl . . . F2) and looks across row 1 for the lookup range in which 30 falls. This is column C, which corresponds to a selling price of $11 for any lookup value between 13 and 32. The row offset is t and $11 is returned. The formula in cell K4 is as follows:
Monte Carlo Simulations
Table 6. Cumulative
B
A
123
lookup table for dependent
C
D
E
model
F
G
L C UM.
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 I 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
63 $9
PROB
P'RICE VOLUME LDOKUP
NO I . 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
80000 80000 80000 80000 80000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000 70000
vc 90000 90000 90000 90000 90000 90000 90000 90000 90000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000
110000 110000 110000 110000 110000 110000 110000 110000 110000 110000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000
I
I
60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000 60000
80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000 80000
90000
120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 110000 110000 110000 110000 110000 110000 110000 110000 110000 110000
130000 130000 130000 130000 130000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000 120000
I 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
8 8 8 8 8 8 8 8 8 8 7 7 7 7 7 7 7 7 7 7 I
soodo 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000 50000
I
90000 90000 90000 90000
90000 90000
90000 90000 90000 90000 90000 90000 90000
90000 90000 90000 90000
90000
100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000 100000
5 5 5 5 5 5 5 5 5 5 5 5 5 4. 4. 4.3 4. 4.
58
10
30
37
38
39
40
40 41
42
43
Note. Only part of the Table
75
36
39
K
10
11
11
9
10
9
I
I
10
9
9
10
10
11
SP
is shown to conserve
47
21
60
35
38
I
83
9
80
73
I
41
7
6
I
52
6
1
21
I
30 (---Yq
RAND
5
TRIAL
J
4
3
2
1
ROW/COL
I
I@HLOOKUP(J4,$SP,lb
space
85
45
82
1
74
64
I I
61
82
43
39
79
96
N
80000
70000
60000
120000
90000
100000
I I
90000
90000
1 ooobo
90000
80000
60000
VOLUME
38 I=]
RAND
L
800000
770000
660000
900000 1080000
I 900000
I
900000
810000
900000
900000
800000
660000
880000
SALES
0
I [@VLOOKUP(L4,$TASLE,$E$2-K4+1) P
model
83
84
55
56
13
17
I I
5
71
82
17
78
75
71 [Sj
RAND
Table 7. Trial results for dependent
Q
5
5
6
6
7
7
I I
8
6
5
7
5
6:
VC
400000
420000
300000
360000
270000
200000
I I
180000
270000
400000
270000
400000
300000
400000
CONTRIS
160000
160000
160000
160000
160000
160000
I I
160000
160000
160000
160000
160000
160000
160000
COSTS
FIXED
@VL~KUP(H4,$TAElLE,S) R S
240000
260000
140000
200000
110000
40000
I I
20000
110000
240000
110000
240000
140000
240000
PROFIT
T
I
125
Monte Carlo Simulations
@HLOOKUP
(J4,$SP, 1).
The other variables (volume and variable cost), use the vertical lookup function described previously with a modification to the column offset in the case of volume. Thus, cell N4 contains the following function: @VLOOKUP (L4,$TABLE,$B$2
- K4 + 1).
Cell L4 contains the random number and TABLE refers to the range containing the data for volume and variable cost. A problem arises in how to determine the column offset. One way to do this is to take the difference between the first selling price in the SP table in cell B2 ($12) and the selling price returned for the particular trial (in this case, $11). The difference of 1 is incremented by 1 to obtain the column offset of 2. Thus, the random number in cell L4 is 38 and the value returned by the function after looking up 38 in the data table and going across 2 columns is 80,000. The function for variable cost is as described previously with a column offset of 6. The remaining parts of the model are as described for the basic model. Summary This section has described a simple method of incorporating variable dependencies into the model. Obviously, the model can quickly become cumbersome as the number of dependent variables increases, but in its simple form it provides a useful learning exercise for the student. There are, of course, other ways of arranging the data table and of determining the column offset for the dependent variable, and students should be encouraged to explore possible variations.
USING NORMALISED
RANDOM NUMBERS
General Description It is frequently the case that some of the variables in a simulation may be normally distributed or may be assumed to be normally distributed. This enables probabilities to be assigned to possible results, as in the analysis provided by Jaedicke and Robichek (1964). Unfortunately, a problem arises where several variables are uncertain, as the product of two independent and normally distributed variables can only approximate normality if the sum of the two coefficients of variation is less than or equal to 12% as described in Ferrara, Hayya, and Nachman (1972). If this is not the case, then it can not be assumed that the result is normally distributed. The Monte Carlo method is a useful way of overcoming this problem.
126
P. Rouse
An Algorithm
for Deriving Normalised
Random Numbers
There are several methods for generating normally distributed pseudorandom numbers, and they all use the transformation 2 = (X - ~)/a, so that the distribution generated is normally distributed with mean equal zero and standard deviation one. This distribution is then converted to the desired normal distribution by using the relationship X = p + (RNi’V)a where RNN is a normal variate lying between zero and one. A good algorithm is provided by Marsaglia and Bray (1964). Two random numbers are generated r-2 and r2. Then, setting V1 = - 1 + 2 * rl and V2 = - 1 + 2 * r2, S = VZ-.2 + V2^.2 is computed. If S L 1 the process is repeated until S < 1. Then, XI = V1 * SQRT(( -2 * LnS/S) X2 = V2 * SQRT(( -2 * LnS/S) where: XI and X2 are the normally distributed numbers, and LnS is the natural logarithm of S. Constructing
the Model
Table 8 shows a section of the worksheet for generating the normal numbers and the macros for copying them to the relevant sections of the model. Thus, VI and V2 are calculated in cells Bl and B2 and the sum of these squared is calculated in B3. The equations for XI and X2 above are contained in cells B4 and BS and will return normal numbers provided that B3 (or s) is less than 1; otherwise an ERR message will appear as the result of trying to take the square root of a negative number, as LnS is positive whenS 2 1. There are four macros and three named ranges (although more can be named according to the designer’s preferences). The first macro, \ A, asks for the number of random numbers required, and as the algorithm returns two normal numbers on each successful pass, the response should be half the number required (e.g., a response of 50 will generate 100 normal numbers). The macro then calls SUBRl which suppresses the screen display while the numbers are being generated and copied to the target area of the worksheet. Note that it also turns on the manual recalculation in order to speed the macros up. This can be changed manually at the end or by amending SUBR2 to read {PANELON) {WINDOWSON} (INDICATE)/WORA{RETURN). This macro requires two range names: NO and COUNT. In this example, cells B7 and B8 have been given these names and are used to record (auto-
No Count
5 6
I
\B
G5 Calculates cells Bl to 85 until S (cell 83) is less than 1.
Inputs how many random numbers are required and stores the value in cell 87. G2 Calls subroutine SUBRl to turn off screen and then loops through macro \B according to the number in 87. G3 Calls subroutine SUBR2 which restores the screen operations.
&If Gl
Explanation of Macros
7 8
SUBRl SUBRP \A
NO RANDOM
COUNT
D
GQ GIO Gi G5
El..85
88 87
E
\B
\A
algorithm I J K
L
(INDICATE “WAIT”) (PANELOFF) {WlNDOWSOFF)/IjGRM{RETURN) (PANELON) {WINDOWSON) {INDICATE) {RETURf$
(RECALC random) (IF B32=1) (BRANCH \B) /RV$b$4..$b$5w * (DOWN 2) (return)
,,,
“Enter half the number required’? ?‘,no)
H
(SUBRI ) (for count,1 ,no,i ,\B) (SUBRP) (quit)
(GETNUMBER
G
GQ Displays the message “WAIT”, turns off the screen operations and sets the worksheet recalculation to manual. GIO Restores the screen operation to normal. (Note that recalculation Is still set to manual.
G6 Copies the transformed numbers in 84 and 85 to where the cursor is located. G7 Returns control to macro \A.
SUBRl SUBRP
F
Table 8. Macros to perform the Marsaglia-Bray
;
,,,,,
M
128
P. Rouse
matically) the number of random numbers required and the counter value. The \B macro or subroutine firstly calculates the formulae in cells Bl . . . B5, which has been named RANDOM. If S or the value in cell B3 is greater than or equal to one, then it recalculates Bl . . . B5 again. The RECALC command is used to calculate only the 5 cells Bl . . . B5 (the area named RANDOM), thus avoiding time recalculating the whole worksheet. The second line of the macro copies the values only (hence the use of the RANGE VALUE command rather than the COPY command) of cells B4 and B5 to the target column. It is, therefore, critical that the cursor is placed at the top of the target column in which the normal numbers are to be copied to before the macro is started. Once the COUNT has reached the value contained in NO (cell B7), the subroutine returns control to \A which then calls SUBR2 which restores the normal window and panel operations. Note that the macro 1 B refers to cells B3 and B4 . . . B5. If the RANDOM area is placed anywhere else in the worksheet, then the corresponding cells must be entered in 1 B to replace these addresses. Further, B4 . . . B5 or their equivalents must be absolute addresses (i.e., $B$4 . . , $B$5). The main model is contained in Table 9. The example used is from Jaedicke and Robichek (1964), and 10 trials are carried out. The procedure is (a) generate four columns of normal random numbers for price, volume, variable cost, and fixed cost; and (b) use these numbers to calculate each variable’s value and result for each trial. Thus, for trial 1, the quantity is obtained by multiplying the normal number for Volume 1.195 (cell B21) by the standard deviation 400, and adding the result to the mean volume of 5000 to obtain 5478. A similar calculation is made for the other variables and the profit obtained. This is then repeated for the number of desired trials. The procedures for summarizing the results are as described previously. LOTUS ADDIN TOOLKIT FOR VERSION 3 Lotus have recently introduced the Addin Toolkit for Version 3 which enables users to write their own @ functions for use within a spreadsheet. It is, therefore, possible to write an @ function to generate a normal number. Table 10 contains a program listing which will allow a user to use a function called @NORMAL which will provide a normal random number. The @ function can only return one result so the X2 value is dropped and only XI is returned. Thus, cells B21 . . . E30 in Table 9 can be replaced by the function @NORMAL. Table 11 shows the same model, except it is using the @NORMAL function. This results in a more compact, simplified model. Unfortunately, the function is not automatically recalculated each time the worksheet is recalculated. This can be overcome, however, by writing a macro that simply recalculates the cells containing the addin func-
L \o
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
B
Normal Variates
$5,800
Mean 5000 $3,000 $1,750
C
$50 $75 $100
400
Std Deviation
D
E
1 2 3 4 5 6 7 8 9 10
1.195 -0.474 1.451 0.201 -1.132 -1.954 -0.488 2.142 -2.308 0.076
0.863 0.139 0.609 -0.244 1.842 0.262 0.630 -0.260 -1.760 -0.938 0.502 -0.184 -0.945 0.120 -1.374 1.191 1.337 0.114 0.711 1.116
0.892 -0.210 -0.440 -0.033 -0.012 0.550 -0.374 -0.562 -0.995 -0.321
G
H
I
5478 4810 5580 5080 4547 4218 4805 5857 4077 5030 3043 3007 3030 2968 3092 3013 3031 2987 2912 2953
____-- _ ------
Var cost 1788 1736 1679 1759 1647 1839 1650 1759 1603 1834
_ ------
IL
Sales Price
r-r Volume
1255 1271 1351 1229 1445 1174 1181 1228 1109 1119
t$C$13t$D$13'B21 ~t$C$l4+$D$i4*C21 +$C$l5t$D$15'D21
F
Table 9. Trial results for normalised model
(3) (1) (2) (4) ._____-- _______- ____--____ ____---- ___----_
1-RIAL
I)ata \I folume EMing Price \fadablecost F:ixedCosts (000s)
JROW/COI
A J
6113 7541 6243 6571 4951 5675 7195 4520
I
L
1
5889 5779 5756 5797 5799 5855 5763 5744 5701 5768
988 334 1785 446 772 -904 -67 1451 -1181 -138
Profit FixCost (000s) (000s) .___---- __---_____-
+$C$l6t$D$16'E21
K
130
P. Rouse
Table 10. Program listing for ~NOR~AL
addin
use Register - Register the @ function Register (“Normal”, AT-Function)
-Returns one normal number from 2 random numbers -using the Marsaglia-Bray algorithm procedure Normal returns (float xl) is float vl , v2, s, rl, r2 loop rl = random r2 = random vi = 2*r1-1 v2 = 2*r2-1 s = v1:2 + v2:2 exit when s < 1 end loop xl = vl “sqrt (-2*ln(s)/s) end procedure Normal
tions. For example, assuming that the range D27 . . . 536 had been named “norm,” ( RECALC norm> will recalculate the normal numbers each time the macro is run. Summary
This section has described some methods for generating normal numbers and incorporating them into a simulation. The methods can be extended to other distributions, such as the triangular and beta distributions. In addition, it has described how a function can be written and incorporated into the worksheet to create a simpler, more elegant modet. SUMMARY This paper has described in detail how simulations using the Monte Carlo method may be set up using Lotus I-2-3. The method can be used for a very simple extension, as was illustrated in the first example using a basic
6,
‘olume ;elling Price ‘ariable Cost :ixed Costs (000s)
5000
E F
G
5318 4949
3 4 5 6 7 8 9 10
29
30
31
32
33
34
.35
36
4648
4966
4773
5149
4950
4803
4004
1 -b 2
Var cost
2988
2984
2916
3012
3026
3027
3023
2930
2969
2969 4
Sales Price
5072 b
Volume
1702
1573
1830
1766
1746
1790
1781
1711
1789
1822
Unit Cont 1147
1287
1410
1086
1246
1280
1237
1262
1219
1180
H
8367
7501
5049
6188
8108
6367
6247
5856
5762
I J
k
5630
5856
5816
5641
5690
5822
5814
5975
Profit
1
737
1645
-767
547
418
545
433
-186 -119
.(OO-)
.594il
5615.
WOs)
Fix cost
+$C$l s+$Q$i.6’f@Noef!@L:~~
5616 A
(000s)
Cont
+$C$l3+$D$l3’@NO~MAL I +$C$14+$D$143@NORMAL I +$C$15+$D$15”@NORMAL
$50 $75 $100
400
D Std :Deviation
27
TRIAL
$3,000 $1,750 $5,600
‘j$eJl
C
'26
26
25
24
23
.22
'21
20
19
16
17
'16
'15
'14
)ataltwutj
B
12
A
.I3
[ROWICOI
Table 11. Trial results for normalised model using @NORMAL addin
7
132
P. Rouse
model, or for more sophisticated applications, as described in the other examples. Risk may be incorporated, either by specifying a set of discrete probabilities, or by using one of several available theoretical distributions, such as the normal, triangular or beta distributions. The Monte Carlo method is relatively simple to understand, particularly when illustrated in a spreadsheet context. Although computer memory requirements limit the number of trials that can be performed, a sufficient number should be possible to enable an adequate distribution of results to be obtained. For instructors, this can be a valuable learning exercise for students (a) to learn how to construct a simulation model, (b) to learn some different functions and commands in Lotus, (c) to appreciate how simulation can assist in providing insights into the riskiness of a proposal, and (d) to learn about random numbers and how they can be transformed into different distributions. REFERENCES Ferrara, W. L., Hayya, J. C., & Nachman, D. A. (1972, April). Normalcy of profit in the Jaedicke-Robichek Model. The Accounting Review, XL VII(2), 299-307. Jaedicke, R. K., & Robichek, A. A. (1964, October). Cost-volume-profit analysis under conditions of uncertainty. The Accounfing Review, XxX1X(4), 917-926. Marsaglia, G., & Bray, T. A. (1964). A convenient method for generating normal variables. SIAMReview (Society for Industrial and Applied Mathematics), 6, 260-264. Myers, S. C. (1976). Postscript: Using simulation for risk analysis. In S. C. Myers (Ed.), Modern developments in financial management. New York: Praeger. Van Dissel, H. Ci., Borgman, H. P., & Beulens, A. J. M. (1990). Task-allocation between DSS and problem owner: The example of Box & Jenkins Time Series Analysis. Decision Support Systems, 6, 339-345.