-
Monte-Carlo Simulation with
Crystal Ball
?
To
run a simulation using Crystal
Ball
?
:
1. Setup
Spreadsheet
Build a
spreadsheet that will calculate the performance
measure (e.g., profit)
in
terms
of
the
inputs
(random
or
not).
For
random
inputs,
just
enter
any
number.
2. Define
Assumptions
—
i.e., random
variables
Define which cells
are random, and what distribution they should
follow.
3. Define
Forecast
—
i.e., output or
performance measure
Define
which
cell(s)
you
are
interested
in
forecasting
(typically
the
performance measure,
e.g., profit).
4. Choose Number of Trials
Select
the
number
of
trials.
If
you
would
later
like
to
generate
the
Sensitivity
Analysis
chart,
choose
“Sensitivity
Analysis” under Options in
Run
Preferences.
5. Run Simulation
Run
the
simulation.
If
you
would
like
to
change
parameters
and
re-run
the
simulation,
you
should
“reset”
the
simulation
(click
on
the
“Reset
Simulation” button on
the toolbar or in the Run menu) first.
6. View
Results
The
forecast
window
showing
the
results
of
the
simulation
appears
automatically
after
(or
during)
the
simulation.
Many
different
results
are
available
(frequency
chart,
cumulative
chart,
statistics,
percentiles,
sensitivity
analysis,
and
trend
chart).
The
results
can
be
copied
into
the
worksheet.
Crystal Ball
Toolbar:
Define
Define
Run
Start
Reset
Forecast
Trend
Assumptions
Forecast
Preferences Simulation
Simulation
Window
Chart
Walton Bookstore Simulation
with Crystal Ball
Recall the
Walton Bookstore example:
It is August,
and they must
decide
how
many
of
next
year’s
nature
calendars
to
order.
Each
calendar
costs
the
bookstore
$$7.50
and
is
sold
for
$$10.
After
February, all unsold
calendars are returned to the publisher for a
refund of $$2.50 per calendar. Suppose
Walton predicts demand will
be
somewhere between 100 and 300 (discrete
uniform).
Demand
= d ~ Uniform[100, 300]
Order Quantity
= Q (decision
variable)
Revenue
= $$10 *
Min(
Q
,
d
)
Cost
= $$7.50 *
Q
Refund
= $$2.50 * Max(
Q
–<
/p>
d
, 0)
Profit
= Revenue
–
Cost + Refund
Step #1 (Setup
Spreadsheet)
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
B
C
D
E
F
?
Simulation of
Walton's Bookstore
D
ata
U
ni
t C
ost
=
U
ni
t
P
rice
=
U
ni
t
R
ef
und =
$$7.50
$$10.00
$$2.50
D
emand D
is
tribution (Unifor
m)
M
inim
um
< br> =
100
M
axi
mum
=
300
D
ecis
ion
Variable
Order Quantity =
Simu
lation
D
em
and
200
C
R
evenue
=C5*M
IN
(C
< br>13,B17)
200
R
eve
nue
$$2,000.00
D
C
ost
=C4*C13
C
ost
$$1,500.00
E
R<
/p>
efund
$$0.00
P
rof
i
t
$$500.00<
/p>
F
P
rofi
t
=C17-D
17+E
17
B
15
Simulat
ion
16
D
em
and
17
200
R
efund
=C6*M
A
X
(C13-B17,0)
Walton Bookstore Simulation with
Crystal Ball
Step #2 (Define
Assumptions
—
i.e., random
variables)
Select the cell
that contains the random variable (B17)
—
color code
(blue):
16
17
B
D
em
and
200
?
and
click
on
the
“Define
Assumptions”
button
in
toolbar
(or
in
the
Cell
menu):
Select type of
distribution:
Provide parameters of distributions:
B
C
8
D
emand Distribution (U
niform)
9
M
inim
um
=
100
10
M
axi
mum
=
300
Walton Bookstore Simulation with
Crystal Ball
?
Step #3 (Define
Forecast
—
i.e.,
output)
Select the cell that
contains the output variable to forecast
(F17):
16
17
F
P
rof
i
t
$$500.00
click on the “Define Forecast” button
in toolbar (or in the Cell menu),
and fill in the Define
Forecast dialogue box.
Step #4 (Choose
Number of Trials)
Click on the “Run Preferences” button
in toolbar (or
in the Run
menu):
and
select the number of trials to run.
Walton Bookstore Simulation with
Crystal Ball
Step #5 (Run
Simulation)
Click on the
“Start Simulation” button in toolbar (or Run in
the Run menu):
?
Step #6 (View
Results)
The
results
of
the
simulation
can
be
viewed
in
a
variety
of
different
ways
(frequency
chart,
cumulative
chart,
statistics,
and
percentiles).
Choose
different options under the View menu
in the forecast window.
The
results can be copied into a worksheet or Word
document (choose Copy under the
Edit
menu in the simulation output window.
Using Trend Charts to Find the Impact
of Order
Quantity on Potential
Profit
Define
several
forecast
cells
(G14:G18)
for
several
possible
order
quantities
(Q=100,
150,
200,
250,
300).
Use
the
same
random
order
quantity
for
each
to
compare them more equally
(i.e., one assumption cell for demand
—<
/p>
C14
—
with the
rest set equal to C14).
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18<
/p>
B
C
D
E
F
G
Simulation of
Walton's Bookstore
D
ata
U
ni
t C
ost
=
U
ni
t
P
rice
=
U
ni
t
R
ef
und =
$$7.50
$$10.00
$$2.50
D
emand D
is
tribution (Unifor
m)
M
inim
um
< br> =
100
M
axim
um
=
300
Sim
ulation
Order Quantity
100
150
200
250
3
00
D
em
and
200
200
200
200
200
R
evenue
$$1,000.00
$$1,500.00
$$2,000.00
$$2,000.00
$$2,000.00
C
ost
$$750.00
$$1,12
5.00
$$1,500.00
$$1,875.00
$$2,250.00
R
efund
< br>$$0.00
$$0.00
$$0.00
$$125.00
$$250.00
P
r
of
it
$$250.00
$$375.0
0
$$500.00
$$250.00
$$0
.00
12
13
14
15
16
17
18
B
Simulation
Order Quantity
100
150
< br>200
250
300
C
D
em
and
200<
/p>
=$$C
$$14
=$$C
$$14
=$$C
$$14
=$$C
$$14
D
R
even
ue
=$$C
$$5*M
IN
(B14,C
14)
=$$C
$$5*M
IN
(B15,C
15)
=$$C
$$5*M
IN
(B16,C
16)
=$$C
$$5*M
IN
(B17,C
17)
=$$C
$$5*M
IN
(B1
8,C
18)
E
C
ost
=$$C
$$4*B14
=$$C
$$4*B15
=$$C
$$4*B16
=$$C
$$4*B17
=$$C
$$4*B18
F
R
efund
=$$C
$$6*M
AX(B14-C
14,0)
=$$C
$$6*M
AX(B15-C
15,0)
=$$C
< br>$$6*M
AX(B16-C
16,0)
< br>=$$C
$$6*M
AX(B17-C
17,0)
=$$C
$$6*M
AX(B
18-C
18,0)
G
P
rofi
t
=D14-E
14+F14
=D15-E
15+F1
5
=D16-E
16+F16
=D17
-E
17+F17
=D18-E
18+
F18
After
running
the
simulation,
choose
“Open
Trend
Chart”
in
the
Run
menu.
This
chart gives “certainty bands” for the forecast
cells. 10% of the time,
the project
duration will fall within the inner band (light
blue), 25% of the
time within the
2
nd
band (red), 50% of the
time within the third band (green),
and
90% of the time within the outside band (dark
blue).
Project
Management
—
Global
Oil
Global Oil is planning
to move their credit card operation to Des
Moines, Iowa from their home office in
Dallas. The move involves
many
different
divisions
within
the
company.
Real
estate
must
select
one
of
three
available
office
sites.
Personnel
has
to
determine
which
employees
from
Dallas
will
move,
how
many
new
employees to hire, and who will train
them. The systems group and
treasurer’s
office must organize the new operating procedure
and
make
financial
arrangements.
The
architects
will
have
to
design
the
interior
space,
and
oversee
needed
structural
improvements.
Each site is an existing building with
sufficient open space, but
office
partitions,
computer
facilities,
furnishings,
and
so
on,
must all be provided.
A
complicating
factor
is
that
there
is
an
interdependence
of
activities.
In
other
words,
some
parts
of
the
project
cannot
be
started
until
other
parts
are
completed.
For
example,
Global
cannot
construct
the
interior
of
an
office
before
it
has
been
designed.
Neither
can
it
hire
new
employees
until
it
has
determined its personnel
requirements.
The necessary activities and their
necessary predecessors (due to
interdependence)
are
listed
below.
Three
estimates
are
made
for
the
completion
time
of
each
activity
—
the
minimum
time,
most
likely time, and
maximum time.
Activit
y
A
B
C
D
E
F
G
Description
Select Office Site
Create Org. & Fin. Plan
Determine Personnel Req.
Design Facility
Construct Facility
Select Personnel to Move
Hire New Employees
Immediate
Predecesso
r
—
—
B
A, C
D
C
F
Time Estimates
(days)
Minimum
Most
Maximum
Likely
21
21
21
20
25
30
15
20
30
20
28
42
40
48
66
12
12
12
20
25
32