Performing Monte Carlo Step 1: Construct a model in words.
The question is, "What is the uncertainty distribution of the expected revenu Assumptions: 1 The average price I will sell this product for will be minimally $10, most likely $12 and ma 2 Sales will be between 1,000 and 100,000 products, with most likely sales of 30,000 3 To reflex this expert knowlegde, we will use the regular Triangular distributions (see a des
Simulations in Crystal Ball
s of this new product? "
imally $15.
cription of the Triangular distribution in Model Assist).
Performing Monte Carlo Step 2: Write all the data in Excel and introduce probability distributions, using Crystal Ball
1 Average Price 2 Sales
3 Total revenue
Minimum
Most likely
Maximal
$10
$12
$15
Minimum
Most likely
Maximal
1,000
30,000
100,000
Price $
12.00
Sales 30,000
$
Total revenue 360,000.00
Probability distribution (we use here the Triang
Simulations in Crystal Ball
Input data
s ular distribution)
Performing Monte Carlo Step 3: Tell Crystal Ball which cell are output (click on cell E14 and then click Crystal Ball but
1 Average Price 2 Sales
3 Total revenue
Minimum
Most likely
Maximal
$10
$12
$15
Minimum
Most likely
Maximal
1,000
30,000
100,000
Price $
12.00
Sales 30,000
$
Total revenue 360,000.00
Simulations in Crystal Ball on "Define Forecast"), and run Crystal Ball by clicking the "Play" (Start Simulation)
Output cell
Performing Monte Carlo Step 4: Review the results
Forecast: Total revenue 300 250 200 y c n e u q e r F
150 100 50 0 $23,802.53
$263,010.76
$502,219.00
$741,427.23
$980,635.46
Review:
1 Average expected total revenue is $536,643 2 There is a 10% probability that total revenues will be less than 3 There is a 10% probability that total revenues will be more tha
Simulations in Crystal Ball
$220.664 n $906.970
139353458.xls.ms_office
Page 11
139353458.xls.ms_office
Page 12
139353458.xls.ms_office
Page 13
139353458.xls.ms_office
Page 14
139353458.xls.ms_office
Total revenue Frequency $23,802.53 11 $39,749.75 16 $55,696.96 33 $71,644.18 48 $87,591.39 68 $103,538.61 73 $119,485.82 68 $135,433.04 89 $151,380.26 90 $167,327.47 107 $183,274.69 152 $199,221.90 128 $215,169.12 143 $231,116.33 150 $247,063.55 173 $263,010.76 185 $278,957.98 173 $294,905.19 223 $310,852.41 209 $326,799.63 248 $342,746.84 279 $358,694.06 270 $374,641.27 262 $390,588.49 264 $406,535.70 242 $422,482.92 235 $438,430.13 255 $454,377.35 260 $470,324.56 216 $486,271.78 230 $502,219.00 228 $518,166.21 207 $534,113.43 220 $550,060.64 211 $566,007.86 187 $581,955.07 206 $597,902.29 197 $613,849.50 185 $629,796.72 199 $645,743.94 180 $661,691.15 144 $677,638.37 168 $693,585.58 160 $709,532.80 148 $725,480.01 145 $741,427.23 121 $757,374.44 126
Page 15
139353458.xls.ms_office
$773,321.66 $789,268.87 $805,216.09 $821,163.31 $837,110.52 $853,057.74 $869,004.95 $884,952.17 $900,899.38 $916,846.60 $932,793.81 $948,741.03 $964,688.24 $980,635.46 $996,582.68 $1,012,529.89 $1,028,477.11 $1,044,424.32 $1,060,371.54 $1,076,318.75 $1,092,265.97 $1,108,213.18 $1,124,160.40 $1,140,107.62 $1,156,054.83 $1,172,002.05 $1,187,949.26 $1,203,896.48
125 159 124 131 106 118 119 94 105 99 71 97 76 77 67 44 65 44 31 47 41 34 31 23 18 18 10 22
Page 16
