Performing Monte Carlo Simulations in Crystal Ball

Assumptions:

1 The average price I will sell this product for will be minimally $10, most likely $12 and maximally $15.

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 description of the Triangular distribution in Model Assist).

Step 1: Construct a model in words.

The question is, "What is the uncertainty distribution of the expected revenues of this new product?"

Performing Monte Carlo Simulations in Crystal Ball

The average price I will sell this product for will be minimally $10, most likely $12 and maximally $15.

To reflex this expert knowlegde, we will use the regular Triangular distributions (see a description of the Triangular distribution in Model Assist).

What is the uncertainty distribution of the expected revenues of this new product?"

Performing Monte Carlo Simulations in Crystal Ball

Minimum Most likely Maximal1 Average Price $10 $12 $15

2 Sales Minimum Most likely Maximal1,000 30,000 100,000

3 Total revenue Price Sales Total revenue $ 12.00 30,000 $ 360,000.00

Step 2: Write all the data in Excel and introduce probability distributions, using Crystal Ball

Probability distributions(we use here the Triangular distribution)

Performing Monte Carlo Simulations in Crystal Ball

Write all the data in Excel and introduce probability distributions, using Crystal Ball

Input data

Probability distributions(we use here the Triangular distribution)

Performing Monte Carlo Simulations in Crystal Ball

Minimum Most likely Maximal1 Average Price $10 $12 $15

2 Sales Minimum Most likely Maximal1,000 30,000 100,000

3 Total revenue Price Sales Total revenue $ 12.00 30,000 $ 360,000.00

Step 3: Tell Crystal Ball which cell are output (click on cell E14 and then click Crystal Ball button "Define Forecast"), and run Crystal Ball by clicking the "Play" (Start Simulation) icon.

Performing Monte Carlo Simulations in Crystal BallTell Crystal Ball which cell are output (click on cell E14 and then click Crystal Ball button "Define Forecast"), and run Crystal Ball by clicking the "Play" (Start Simulation)

Output cell

Performing Monte Carlo Simulations in Crystal Ball

Review:1 Average expected total revenue is $536,6432 There is a 10% probability that total revenues will be less than $220.6643 There is a 10% probability that total revenues will be more than $906.970

Step 4: Review the results

$23,

802.

53

$71,

644.

18

$119

,485

.82

$167

,327

.47

$215

,169

.12

$263

,010

.76

$310

,852

.41

$358

,694

.06

$406

,535

.70

$454

,377

.35

$502

,219

.00

$550

,060

.64

$597

,902

.29

$645

,743

.94

$693

,585

.58

$741

,427

.23

$789

,268

.87

$837

,110

.52

$884

,952

.17

$932

,793

.81

$980

,635

.46

$1,0

28,4

77.1

1

$1,0

76,3

18.7

5

$1,1

24,1

60.4

0

$1,1

72,0

02.0

5 0

50

100

150

200

250

300

Forecast: Total revenue

Fre

qu

en

cy

Performing Monte Carlo Simulations in Crystal Ball

There is a 10% probability that total revenues will be less than $220.664There is a 10% probability that total revenues will be more than $906.970

$23,

802.

53

$71,

644.

18

$119

,485

.82

$167

,327

.47

$215

,169

.12

$263

,010

.76

$310

,852

.41

$358

,694

.06

$406

,535

.70

$454

,377

.35

$502

,219

.00

$550

,060

.64

$597

,902

.29

$645

,743

.94

$693

,585

.58

$741

,427

.23

$789

,268

.87

$837

,110

.52

$884

,952

.17

$932

,793

.81

$980

,635

.46

$1,0

28,4

77.1

1

$1,0

76,3

18.7

5

$1,1

24,1

60.4

0

$1,1

72,0

02.0

5 0

50

100

150

200

250

300

Forecast: Total revenue

Fre

qu

en

cy

Page

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

Page

$741,427.23 121$757,374.44 126$773,321.66 125$789,268.87 159$805,216.09 124$821,163.31 131$837,110.52 106$853,057.74 118$869,004.95 119$884,952.17 94$900,899.38 105$916,846.60 99$932,793.81 71$948,741.03 97$964,688.24 76$980,635.46 77$996,582.68 67

### 44### 65### 44### 31### 47### 41### 34### 31### 23### 18### 18### 10### 22