Transcript cystal ball powerpoint

Crystal Ball: Risk Analysis
 Risk analysis uses analytical decision models or Monte
Carlo simulation models based on the probability
distributions to evaluate the desirability of certain PM
decisions.
 Crystal Ball constructs a mathematical model of the
situation and runs a simulation to determine the model’s
outcomes under various scenarios.
4/13/2016
Ardavan Asef-Vaziri
1-1
Risk Analysis: PsychoCeramic’s Case
 Open Crystal Ball and make an Excel spreadsheet copy
of Table 1-1 (p.20).
 Using CB to run a Monte Carlo simulation requires us to
define two types of cells in the Excel spreadsheet.
 Assumption cells: In Table 1-1, columns B and G, the inflow and
the rate of inflation, are the cells that contain variables or
parameters that we make assumptions about.
 Forecast cells: Cell F17 contains the outcomes (or results) we
are interested in forecasting. In PsychoCeramic’s case we want
to predict the NPV of the project.
4/13/2016
Ardavan Asef-Vaziri
1-2
Single-Point Estimates of the Cash Flows
for PsychoCeramic’s
Table 1-1
Year
Inflow
Outflow
2008*
$0.00
2008
0
2009
0
2010 $50,000.00
2011
120,000
2012
115,000
2013
105,000
2014
97,000
2015
90,000
2016
82,000
2017
65,000
2017
35,000
$125,000
100,000
90,000
0
15,000
0
15,000
0
15,000
0
0
$759,000.00 $360,000.00
4/13/2016
Discount
Net Flow
Factor
-$125,000.00
-$100,000.00
-$90,000.00
$50,000.00
$105,000.00
$115,000.00
$90,000.00
$97,000.00
$75,000.00
$82,000.00
$65,000.00
$35,000.00
$399,000.00
Ardavan Asef-Vaziri
1.00
0.87
0.76
0.66
0.57
0.50
0.43
0.38
0.33
0.28
0.25
0.25
Net Present Inflation
Value
Rate
-$125,000.00
-$86,956.52
-$68,052.93
$32,875.81
$60,034.09
$57,175.32
$38,909.48
$36,465.89
$24,517.63
$23,309.52
$16,067.01
$8,651.46
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
$17,996.77
1-3
Estimates for Cash Flow
 Table 1-2 contains the Pessimistic, Most Likely, and
Optimistic Estimates for Cash Inflows for PsychoCeramic
4/13/2016
Year
2010
2011
2012
2013
2014
2015
2016
2017
2017
Minimum
Inflow
$35,000
95,000
100,000
88,000
80,000
75,000
67,000
51,000
30,000
Most Likely
Inflow
$50,000
120,000
115,000
105,000
97,000
90,000
82,000
65,000
35,000
Maximum
Inflow
$60,000
136,000
125,000
116,000
108,000
100,000
91,000
73,000
38,000
Total
$621,000
$759,000
$847,000
Ardavan Asef-Vaziri
1-4
Define Assumption Cells: Estimates Data
 In Crystal Ball
1. Click on cell B7 to identify it as the relevant assumption cell.
2. Select the menu option Cell at the top of the screen (Excel
2007: skip this step).
3. Select Define Assumption.
CB’s Distribution
Gallery is now displayed .
4/13/2016
Ardavan Asef-Vaziri
1-5
Define Assumption Cells: Estimates Data
4. Select the Triangular box.
5. CB’s Triangular Distribution dialog box is displayed. In the
Assumption Name: enter a descriptive label, for example,
Cash Inflow 2010. Then enter the pessimistic, most likely, and
optimistic costs from Table 1-2 for the year 2010 in the Min,
Likeliest, and Max boxes.
4/13/2016
Ardavan Asef-Vaziri
1-6
Define Assumption Cells: Estimates Data
6. Click OK (note that the inflow in cell B7 automatically changes
to the mean of the triangular distribution).
Now repeat steps 1-6 for the remaining cash inflow assumption
cells (B8:B15)
4/13/2016
Ardavan Asef-Vaziri
1-7
Define Assumption Cells: Inflation Values






Define assumption cells for the inflation values in column G.
The entry at the beginning of the year 2008 is not discounted so
there is not need for an entry in Cell G4.
Select G5
Select the Normal distribution
In the Assumption Name:
enter Inflation Rate
Enter 0.02 for all cells in the
Mean textbox, and .0033 in
the Std Dev textbox
4/13/2016
Ardavan Asef-Vaziri
1-8
Define Assumption Cells: Inflation Values


Copy the assumption cell G5 to G6:G14
Excel’s copy and paste commands will not work. CB’s own copy
and paste commands must be used to copy the information
contained in both assumption and forecast cells.
1.
2.
3.
4.


Place the cursor on cell G5
Enter the command Cell, then click on Copy Data
Highlight the range G6:G14
Enter the command Cell, then Paste Data
Note that the year 2017 has two cash inflows, both occurring at the
end of the year. Because we don’t want to generate two different
inflation rates for 2017, the value generated in cell G14 will be
used for both 2017 entries.
In cell G15 enter =G14
4/13/2016
Ardavan Asef-Vaziri
1-9
Define Forecast Cells (Outcome)
1.
2.
3.
4.
1.
Click on the cell F17 to identify it as containing an outcome.
Select the menu option Cell at the top of the screen (Excel 2007:
skip this step).
Select Define Forecast…
In the Forecast Name: textbox
enter a descriptive name such as
Net Present Value of Project. Then
enter a descriptive label such as
Dollars in the Units: textbox.
Click OK. ( If there are more than 1
Forecast cells, use the same five steps
to define each of them).
4/13/2016
Ardavan Asef-Vaziri
1-10
Three-Point Estimate of Cash Flows and Inflation Rate:
Assumption & Forecast Cells Defined
Inflow
Outflow
Net Flow
Discount
Factor
2008*
$0.00
2008
0
2009
0
2010 $48,333.00
2011
117,000
2012
113,333
2013
103,000
2014
95,000
2015
88,333
2016
80,000
2017
63,000
2017
34,333
$125,000
100,000
90,000
0
15,000
0
15,000
0
15,000
0
0
-$125,000.00
-$100,000.00
-$90,000.00
$48,333.00
$102,000.00
$113,333.00
$88,000.00
$95,000.00
$73,333.00
$80,000.00
$63,000.00
$34,333.00
1.00
0.87
0.76
0.66
0.57
0.50
0.43
0.38
0.33
0.28
0.25
0.25
Year
$742,332.00
4/13/2016
$360,000.00
$382,332.00
Ardavan Asef-Vaziri
Net Present
Value
Inflation
Rate
-$125,000.00
-$86,956.52
-$68,052.93
$31,779.73
$58,318.83
$56,346.53
$38,044.83
$35,714.02
$23,972.69
$22,740.99
$15,572.64
$8,486.59
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
0.02
$10,967.40
1-11
CB: Simulation





CB randomly selects a value for each assumption cell based on
the probability distributions which we specified and then calculates
the net present value of the cell values selected. By repeating this
process many times, we can get a sense of the distribution of
possible outcomes.
To simulate the model we have constructed 1000 times, select the
Run menu. (2007: skip this step)
Select Run Preferences.
Enter 1000 in the Maximum Number of Trials textbox and then
click OK.
To perform the simulation, click Run (2007: click Start).
4/13/2016
Ardavan Asef-Vaziri
1-12
CB: Simulation Output

CB summarizes the results of the simulation in the form of a
frequency chart that changes as the simulations are executed.
4/13/2016
Ardavan Asef-Vaziri
1-13
Summary of Statistics


CB also provides information about the forecast cell including
percentile information, summary statistics, a cumulative chart, and
a reverse cumulative chart.
To see the summary statistics for a forecast cell, select View from
the Forecast dialog box toolbar and then select Statistics.
4/13/2016
Ardavan Asef-Vaziri
1-14
Probability: NPV ≥ Hurdle Rate


Using the Simulation output display, erase –Infinity from the box in
the lower left corner. Type 0 (or 1) in that box and press Enter.
The boxes at the bottom of the output display now shows that
given our estimates and assumptions of the cash flows and the
rate of inflation, there is a
.90+ probability that the
project will have an NPV
between zero and infinity,
that is, the rate of return
at or above the 13% hurdle
rate.
4/13/2016
Ardavan Asef-Vaziri
1-15