crystal ball presentation

8/8/2019 Crystal Ball Presentation

http://slidepdf.com/reader/full/crystal-ball-presentation 1/18

The American University in CairoThe American University in Cairo

Risk QuantificationRisk Quantification

UsingUsing

CrystalBall SofwareCrystalBall Sofware

Construction Engineering DepartmentConstruction Engineering Department

ByBy

Eng: Ahmed MahdyEng: Ahmed Mahdy



What Is Risk?What Is Risk?

Uncertainty about a situation can often indicate risk.Uncertainty about a situation can often indicate risk.

Risk is the possibility of loss, damage, or any other Risk is the possibility of loss, damage, or any other

undesirable event.undesirable event.

Low risk is desired, which would translate to a highLow risk is desired, which would translate to a high

probability of success and profit.probability of success and profit.

Where is the risk?Where is the risk?

How significant is the riskHow significant is the risk

http://www.decisioneering.com



Traditional Spreadsheet RiskTraditional Spreadsheet Risk

Analysis Analysis Identifying risks in your Identifying risks in your

spreadsheetspreadsheet

e.g., costs, rates,e.g., costs, rates,

demandsdemands

Best estimate or knownBest estimate or known

fact?fact?

UncertaintyUncertainty




Cost BreakdownCost Breakdown

Trade

Activity Unit: m3

Name Unit Quantity U. Price Price

Materials

Water (0.25/

3)

3 0.2500 3.5 0.88

Gra¡

el (2G:1¢

)

3 0.6670 21.5 14.34¢

and

3 0.3330 7.5 2.50

Labor Crew Crew 1.0000 0.3 0.3

Equipment

Crew Crew 1.0000 2.5 2.5

Direct Cost * * Ot£ er

¤ irect Cost Besides

¥ aterials and

¦ a

§ or and

̈ q

© i

ent

Testin

e¡

er

25 c

3 1.0000 0.25 0.25

Sub Total * * Total ¤ irect Cost

20.76

Indirect Cost

O¡

er £ ead = 3 % 0.62

Total Cost * * Total of ¤ irect Cost + Indirect Cost

21.39

ContingencyContin

enc

= 5 % 1.07

Profit

Profit = 10 % 2.14

Total 24.59

Total Price 24.59

B.O.Q 23.25

% Change 5.78%

Earth Work

Soil Replacement and Compaction

Item Data

T esis II: C NG 491 rin ¶ 03



Traditional Spreadsheet RiskTraditional Spreadsheet Risk

Analysis Analysis The traditional modeling landscapeThe traditional modeling landscape

oint estimatesoint estimatesmost likely values, easiest, and misleading results.most likely values, easiest, and misleading results.

Range estimatesRange estimates Three scenarios: the best, worst case, and theThree scenarios: the best, worst case, and the

most likely case.most likely case.

Range of outcomes, but no probabilitiesRange of outcomes, but no probabilities

WhatWhat--if scenariosif scenariosMany scenarios, time consuming, lots of dataMany scenarios, time consuming, lots of data

No probabilitiesNo probabilities




What Is Monte Carlo Simulation?What Is Monte Carlo Simulation?

What do we mean by "simulation?"What do we mean by "simulation?"

How did Monte Carlo simulation get its name?How did Monte Carlo simulation get its name?

What do you do with uncertain variables in your What do you do with uncertain variables in your

spreadsheet?spreadsheet?

What happens during a simulation?What happens during a simulation?

How do you analyze the results of a simulation?How do you analyze the results of a simulation?




ProceduresProcedures

Select Items to be used in the analysis.Select Items to be used in the analysis.

Assume probable Minimum and Maximum cost for each Assume probable Minimum and Maximum cost for each

item.item.

Assign probability distribution for each item. Assign probability distribution for each item. Create an Excel module using Crystal ball.Create an Excel module using Crystal ball.

Run the simulation.Run the simulation.

Analyze the results. Analyze the results.

Thesis II: CEN Spring¶



Assumption Assumption

The Height represents

relative likelihood

X-Axis value determine

the impact on Cost




Which distributions?Which distributions?



ForecastForecast




ResultsResults




Sensitivity and Trend ChartsSensitivity and Trend Charts




Crystal Ball AnalysisCrystal Ball Analysis

The Cost Fluctuate between . to . LEThe Cost Fluctuate between . to . LE

The probability that the actual cost would be less than or e ualThe probability that the actual cost would be less than or e ualthe estimated Cost ( , , . ) is only .the estimated Cost ( , , . ) is only .


robability Contengency

. .

robability Contengency

. .. .

. .

. .

. .

. .

. .

Probability vs Contengency

0

2

4

6

8

10

12

14

16

0 10 20 30 40 50 60 70 80 90 100

Probability (%)

C

o n t e n g e n c y ( % )



Cost BreakdownCost Breakdown

Trade

Activity Unit: m3

Name Unit Quantity U. Price Price

Materials

Water (0.25/2

3)2

3 0.2500 3.5 0.88

Gra3

el (2G:14

)2

3 0.6670 21.5 14.34

4 and

2 3 0.3330 7.5 2.50

Labor Crew Crew 1.0000 0.3 0.3

Equipment

Crew Crew 1.0000 2.5 2.5

Direct Cost * * Ot5 er

6 irect Cost Besides

7 aterials and

8 a

9 or and

@ q

A i

B 2 ent

TestinC

e3

er D

25 c2 2

3 1.0000 0.25 0.25

Sub Total * * Total 6 irect Cost

20.76

Indirect Cost

O3

er 5 ead = 3 % 0.62

Total Cost * * Total of 6 irect Cost + Indirect Cost

21.39

Contingency ContinC enc

D

= 5 % 1.07

Profit

Profit = 10 % 2.14

Total 24.59

Total Price 24.59

B.O.Q 23.25

% Change 5.78%

Earth Work

Soil Replacement and Compaction

Item Data

T esis II: C NG 491 rin ¶ 03



What Are The Benefits f RiskWhat Are The Benefits f Risk

AnalysisW

ith Crystal Ball? AnalysisW

ith Crystal Ball? You stop guessingYou stop guessing

You break free from the limitations of spreadsheetsYou break free from the limitations of spreadsheets

You have a competitive advantageYou have a competitive advantage

You know what mattersYou know what matters

You look sharpYou look sharp


crystal ball presentation

Documents