风险管理软件Crystal-Ball使用指导

1/19

风险管理软件Crystal-Ball使用指导

Monte-CarloSimulationwithCrystalBall®

TorunasimulationusingCrystalBall®:

1.SetupSpreadsheet

Buildaspreadsheetthatwillcalculatetheperformancemeasure(e.g.,profit)intermsoftheinputs(randomornot).Forrandominputs,justenteranynumber.

2.DefineAssumptions—i.e.,randomvariables

Definewhichcellsarerandom,andwhatdistributiontheyshouldfollow.

3.DefineForecast—i.e.,outputorperformancemeasure

Definewhichcell(s)youareinterestedinforecasting(typicallytheperformancemeasure,e.g.,profit).

4.ChooseNumberofTrials

Selectthenumberoftrials.IfyouwouldlaterliketogeneratetheSensitivityAnalysischart,choose“SensitivityAnalysis”underOptionsinRunPreferences.

5.RunSimulation

Runthesimulation.Ifyouwouldliketochangeparametersandre-runthesimulation,youshould“reset”thesimulation(clickonthe“ResetSimulation”buttononthetoolbarorintheRunmenu)first.

6.ViewResults

Theforecastwindowshowingtheresultsofthesimulationappearsautomaticallyafter(orduring)thesimulation.Manydifferentresultsareavailable(frequencychart,cumulativechart,statistics,percentiles,sensitivityanalysis,andtrendchart).Theresultscanbecopiedintotheworksheet.

2/19

风险管理软件Crystal-Ball使用指导

CrystalBallToolbar:

DefineDefineRunStartResetForecastTrend

AssumptionsForecastPreferencesSimulationSimulationWindowChart

3/19

风险管理软件Crystal-Ball使用指导

WaltonBookstoreSimulationwithCrystalBall®

RecalltheWaltonBookstoreexample:ItisAugust,andtheymustdecidehowmanyofnextyear’snaturecalendarstoorder.Eachcalendarcoststhebookstore$7.50andissoldfor$10.AfterFebruary,allunsoldcalendarsarereturnedtothepublisherforarefundof$2.50percalendar.SupposeWaltonpredictsdemandwillbesomewherebetween100and300(discreteuniform).

Demand=d~Uniform[100,300]

OrderQuantity=Q(decisionvariable)

Revenue

Cost

Refund

=$10*Min(Q,d)

=$7.50*Q

=$2.50*Max(Q–d,0)

Profit=Revenue–Cost+Refund

Step#1(SetupSpreadsheet)

ABCDEF

SimulationofWalton'sBookstore

Data

UnitCost=

UnitPrice=

UnitRefund=

DemandDistribution(Uniform)

Minimum=

Maximum=

DecisionVariable

OrderQuantity=

Simulation

Demand

200

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

Revenue$2,000.00

Cost$1,500.00

$7.50

$10.00

$2.50

Profit$500.00

Refund$0.00

100

300

200

BCDEF

15Simulation

16DemandRevenueCostRefundProfit

17200=C5*MIN(C13,B17)=C4*C13=C6*MAX(C13-B17,0)=C17-D17+E17

风险管理软件Crystal-Ball使用指导

4/19

风险管理软件Crystal-Ball使用指导

WaltonBookstoreSimulationwithCrystalBall®

Step#2(DefineAssumptions—i.e.,randomvariables)

Selectthecellthatcontainstherandomvariable(B17)—colorcode(blue):

B

Demand

200

16

17

andclickonthe“DefineAssumptions”buttonintoolbar(orintheCellmenu):

Selecttypeofdistribution:

Provideparametersofdistributions:

BC

8DemandDistribution(Uniform)

9Minimum=100

10Maximum=300

5/19

6/19

风险管理软件Crystal-Ball使用指导

WaltonBookstoreSimulationwithCrystalBall®

Step#3(DefineForecast—i.e.,output)

Selectthecellthatcontainstheoutputvariabletoforecast(F17):

F

16Profit17$500.00

clickonthe“DefineForecast”buttonintoolbar(orintheCellmenu),

andfillintheDefineForecastdialoguebox.

Step#4(ChooseNumberofTrials)

Clickonthe“RunPreferences”buttonintoolbar(orintheRunmenu):

andselectthenumberoftrialstorun.

7/19

风险管理软件Crystal-Ball使用指导

WaltonBookstoreSimulationwithCrystalBall®

Step#5(RunSimulation)

Clickonthe“StartSimulation”buttonintoolbar(orRunintheRunmenu):

Step#6(ViewResults)

Theresultsofthesimulationcanbeviewedinavarietyofdifferentways(frequencychart,cumulativechart,statistics,andpercentiles).ChoosedifferentoptionsundertheViewmenuintheforecastwindow.

TheresultscanbecopiedintoaworksheetorWorddocument(chooseCopyundertheEditmenuinthesimulationoutputwindow.

8/19

风险管理软件Crystal-Ball使用指导

UsingTrendChartstoFindtheImpactofOrderQuantityonPotentialProfit

Defineseveralforecastcells(G14:G18)forseveralpossibleorderquantities(Q=100,150,200,250,300).Usethesamerandomorderquantityforeachtocomparethemmoreequally(i.e.,oneassumptioncellfordemand—C14—withtherestsetequaltoC14).

ABC

SimulationofWalton'sBookstore

Data

UnitCost=

UnitPrice=

UnitRefund=

DemandDistribution(Uniform)

Minimum=

Maximum=

Simulation

OrderQuantity

100

150

200

250

300

D

Revenue$1,000.00$1,500.00$2,000.00$2,000.00$2,000.00

E

Cost

$750.00$1,125.00$1,500.00$1,875.00$2,250.00

F

Refund$0.00$0.00$0.00

$125.00$250.00

G

Profit$250.00$375.00$500.00$250.00

$0.00

Demand

200

200

200

200

200

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

$7.50

$10.00

$2.50

100

300

F

Refund=$C$6*MAX(B14-C14,0)=$C$6*MAX(B15-C15,0)=$C$6*MAX(B16-C16,0)=$C$6*MAX(B17-C17,0)=$C$6*MAX(B18-C18,0)

D

Revenue=$C$5*MIN(B14,C14)=$C$5*MIN(B15,C15)=$C$5*MIN(B16,C16)=$C$5*MIN(B17,C17)=$C$5*MIN(B18,C18)

B

12Simulation

13OrderQuantity

14100

15150

16200

17250

18300

G

Profit=D14-E14+F14=D15-E15+F15=D16-E16+F16=D17-E17+F17=D18-E18+F18

E

Cost=$C$4*B14=$C$4*B15=$C$4*B16=$C$4*B17=$C$4*B18

C

Demand

200=$C$14=$C$14=$C$14=$C$14

Afterrunningthesimulation,choose“OpenTrendChart”intheRunmenu.Thischartgives“certaintybands”fortheforecastcells.10%ofthetime,theprojectdurationwillfallwithin theinnerband(lightblue),25%ofthetimewithinthe2ndband(red),50%ofthetimewithin thethirdband(green),and90%ofthetimewithintheoutsideband(darkblue).

9/19

风险管理软件Crystal-Ball使用指导

10/19

风险管理软件Crystal-Ball使用指导

ProjectManagement—GlobalOil

GlobalOilisplanningtomovetheircreditcardoperationtoDesMoines,IowafromtheirhomeofficeinDallas.Themoveinvolvesmanydifferentdivisionswithinthecompany.Realestatemustselectoneofthreeavailableofficesites.PersonnelhastodeterminewhichemployeesfromDallaswillmove,howmanynewemployeestohire,andwhowilltrainthem.Thesystemsgroupandtreasurer’sofficemustorganizethenewoperatingprocedureandmakefinancialarrangements.Thearchitectswillhavetodesigntheinteriorspace,andoverseeneededstructuralimprovements.Eachsiteisanexistingbuildingwithsufficientopenspace,butofficepartitions,computerfacilities,furnishings,andsoon,mustallbeprovided.

Acomplicatingfactoristhatthereisaninterdependenceofactivities.Inotherwords,somepartsoftheprojectcannotbestarteduntilotherpartsarecompleted.Forexample,Globalcannotconstructtheinteriorofanofficebeforeithasbeendesigned.Neithercanithirenewemployeesuntilithasdetermineditspersonnelrequirements.

Thenecessaryactivitiesandtheirnecessarypredecessors(duetointerdependence)arelistedbelow.Threeestimatesaremadeforthecompletiontimeofeachactivity—theminimumtime,mostlikelytime,andmaximumtime.

ImmediateTimeEstimates(days)

DescriptionPredecessorMinimumMostLikelyMaximum

SelectOfficeSite—212121

CreateOrg.&Fin.Plan—202530

DeterminePersonnelReq.B152030

DesignFacilityA,C202842

ConstructFacilityD404866

SelectPersonneltoMoveC121212

HireNewEmployeesF202532

MoveKeyEmployeesF282828

TrainNewPersonnelE,G,H101524

ActivityA

B

C

D

E

F

G

H

I

11/19

风险管理软件Crystal-Ball使用指导

A

Start

B

D

C

E

F

I

G

End

H

12/19

风险管理软件Crystal-Ball使用指导

GlobalOilSimulationwithCrystalBall®

Step#1(SetupSpreadsheet)

EFGHI

ActivityTime(Triangular)

Most

Likely

21

25

20

28

48

12

25

28

15

ProjectCompletionTime=

CRelocationProject

Description

SelectSite

CreateOrg.&Fin.PlanDeterminePersonnelReq.DesignFacilityConstructFacility

SelectPersonneltoMoveHireNewEmployeesMoveKeyEmployeesTrainNewPersonnel

D

ImmediatePredecessors

-

-

B

A,C

D

C

F

F

E,G,H

ABGlobalOil

ActivityABCDEFG

H

I

J

Finish

Time

21

25

45

73

121

57

82

85

136

136.00

Maximum

21

30

30

42

66

12

32

28

24

Minimum

21

20

15

20

40

12

20

28

10

ActivityTime

21

25

20

28

48

12

25

28

15

StartTime

0

0

25

45

73

45

57

57

121

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

I

Activity

Time

21

25

20

28

48

12

25

28

15

ProjectCompletionTime=

H

3Start

4Time50

60

7=J6

8=MAX(J5,J7)

9=J8

10=J7

11=J10

12=J10

13=MAX(J9,J11,J12)14

15

J

Finish

Time=H5+I5=H6+I6=H7+I7=H8+I8=H9+I9=H10+I10=H11+I11=H12+I12=H13+I13

=J13

Step#2(DefineAssumptions—i.e.,randomvariables)

Eachoftherandomactivitytimes(B,C,D,E,G,andI)isassumedtofollowthetriangulardistribution.

13/19

风险管理软件Crystal-Ball使用指导

14/19

风险管理软件Crystal-Ball使用指导

GlobalOilSimulationwithCrystalBall®

Step#3(DefineForecast—i.e.,output)

J

136.00

CellJ15istheforecastcell:

15

GHI

ProjectCompletionTime=

Step#4(ChooseNumberofTrials)

500trialswererun.Inaddition,SensitivityAnalysiswasenabledintheOptionsoftheRunPreferencesdialoguebox.Thisallowsforthegenerationofsensitivityanalysisresultslater.

Step#5(RunSimulation)

Step#6(ViewResults)

15/19

风险管理软件Crystal-Ball使用指导

AdditionalResultsAvailablewithCrystalBall®

Slidethetrianglesbelowthehistogramstodeterminetheprobabilitythattheoutput(projectduration)islessthanacertainvalue(e.g.,adeadline),greaterthanacertainvalue,orbetweenanytwovalues(byslidingbothtriangles).

Alternatively,youcantypeinvaluesforthelowerboundorupperboundtodeterminetheprobability.Youcanalsotypeinaprobability(in“Certainty”),anditwilldeterminetherangethathasthatprobability.

Thereisa79%chancetheprojectwillbecompletedwithin150days.Thereisa2.4%chancethattheprojectwilltakemorethan160days.

SensitivityChart

Choose“OpenSensitivityChart”intheRunmenu.Notethatthischartisonlyavailableifyouselectedthe“SensitivityAnalysis”optionunderRunPreferences.Thischartgivesanindicationastowhichrandomvariables(activitytimes)havethegreatestimpactontheoutputcell(projectcompletiontime).

16/19

风险管理软件Crystal-Ball使用指导

VariabilityinactivityEhasthegreatestimpactonoverallprojectduration,followedbyactivityD,C,I,andB.VariabilityinactivityGhasalmostnoimpact.

17/19

风险管理软件Crystal-Ball使用指导

FittingaDistribution

CrystalBallcanbeusedto“fit”adistributiontodata.

Thefollowingdatahasbeencollectedfortheprevious100phonecallstoamail-orderhouse:

ABPhoneData

Customer#

1

2

3