水晶球软件使用Crystal Ball

ProjectManagementSession9CrystalBallOPIM52701Session9GoalsUnderstandwhyriskmustbeanalyzedKnowpros/consforthreewaystoanalyzeriskIdentifyrandomvariablesinmodelsKnowthefourstepsofasimulationprocessGeneraterandomnumberswithCrystalBallUsethefourstepsofasimulationprocessExplainhowCrystalBallsupportsProj.Mgmt.2•Mostreal-worldbusinesssituationstodayareprobabilistic,butthedecisionmodelsusedtodealwiththemaredeterministic.•Howtodealwithrandomness?IgnoreitSimplifyproblemtomakeitanalyticallytractable,getsolution,thenignorereal-lifecomplicationsFindawaytoobtainanapproximatesolutiontoreal-worldproblems3DealingwithRandomness•MonteCarlosimulationisamethodbywhichapproximatesolutionsareobtainedtorealistic(andthereforecomplicated)problems•Thisisincontrasttoanalyticalmethods,whichobtainexactsolutionstohighlystylizedproblems•Tradeoffbetweenrigorandrelevance4MonteCarloSimulation•Whatisthis?Y=f(X1,X2,…,Xk)•Often,thevaluesforoneormoreinputcellsareunknownoruncertain•Thiscreatesuncertaintyaboutthevalueoftheoutputcell•Simulationcanbeusedtoanalyzethesetypesofmodels5IntroductiontoSimulation•Arandomvariableisanyvariablewhosevaluecannotbepredictedorsetwithcertainty.•Many“inputcells”inspreadsheetmodelsareactuallyrandomvariables.Forexample:thefuturecostofrawmaterialsfutureinterestratesfuturenumberofemployeesinafirmexpectedproductdemand•Decisionsmadeusinguncertaininformationofteninvolverisk.Whatrisks?6RandomVariables&Risk•Usingexpectedvaluesforuncertaincellstellsusnothingaboutthevariabilityoftheperformancemeasure.•Supposean$1,000investmentisexpectedtoreturn$2,000intwoyears.Wouldyouinvestif...–theoutcomescouldrangefrom$1,060to$4,000?–theoutcomescouldrangefrom$0to$2,100?•Alternativeswiththesameexpectedvaluemayinvolveverydifferentlevelsofrisk.7WhyAnalyzeRisk?•Best-Case/Worst-CaseAnalysis•What-ifAnalysis•SimulationMethodsofRiskAnalysis8•Bestcase-pluginthemostoptimisticvaluesforeachoftheuncertaincells.•Worstcase-pluginthemostpessimisticvaluesforeachoftheuncertaincells.•Thisiseasytodoandboundstheoutcomes,buttellsusnothingaboutthedistributionofpossibleoutcomeswithinthebestandworst-caselimits.•Otherproblemsorbenefits?9Best-Case/Worst-CaseAnalysisworstcasebestcaseworstcasebestcaseworstcasebestcaseworstcasebestcase10PossiblePerformanceMeasureDistributionsWithinaRange•Plugindifferentvaluesfortheuncertaincellsandseewhathappens.•Benefits:–Thisiseasytodowithspreadsheets–Other?•Problems:–Valuesmaybechoseninabiasedway.–Hundredsorthousandsofscenariosmayberequiredtogeneratearepresentativedistribution.–Doesnotsupplythetangibleevidence(factsandfigures)neededtojustifydecisionstomanagement.What-IfAnalysis11•Valuesforuncertaincellsareselectedrandomly(andinanunbiasedmanner).•Thecomputergenerateshundreds(orthousands)ofscenarios.•Weanalyzethescenarioresultstobetterunderstandthebehavioroftheperformancemeasure.•Allowsdecisionsbasedonsolidempiricalevidence.Simulation12•Properriskassessmentrequiressimulation.•Simulationisa4stepprocess:1)Identifytheuncertaincellsinthemodel.2)ImplementappropriateRandomNumberGenerators(RNGs)foreachuncertaincell.3)Replicate复制themodelntimes,andrecordthevalueofthebottom-lineperformancemeasure.4)Analyzethesamplevaluescollectedontheperformancemeasure.SimulationContinued13•ARandomNumberGeneratorisamathematicalfunctionthatrandomlygenerates(returns)avaluefromaparticularprobabilitydistribution.•WecanimplementRandomNumberGeneratorsforuncertaincellstoallowustosamplefromthedistributionofvaluesexpectedfordifferentcells.RandomNumberGenerators14•TheRAND()functionreturnsuniformlydistributedrandomnumbersbetween0.0and0.9999999.•Supposewewanttosimulatetheactoftossingafaircoin.•Let1represent“heads”and2represent“tails”.•ConsiderthefollowingRNG:=IF(RAND()0.5,1,2)HowRandomNumberGeneratorsWork15CrystalBallprovidestwodifferentwaysforcreatingRandomNumberGeneratorsinspreadsheets–CrystalBallfunctionsUsedinformulaslikeanyotherExcelfunctionRequireCBtobeinstalledonthemachinedisplayingthespreadsheet&donotsupportallCBfunctionality–TheDistributionGalleryDisplayanumber(notaformula)inacellbutgeneratesrandomnumbersforthatcellwhensimulatingthemodelDoesnotrequireCBtobeinstalledonthemachinetodisplaythespreadsheet&supportsallCBfunctionalityGeneratingRandomNumberswithCrystalBall16ClickDefineAssumptioniconSelectdistributionSpecifyparametersUsingtheDistributionGallery1718DiscreteProbabilityDistributions18ContinuousProbabilityDistributions19DefineMenuRunMenuAnalyzeMenuCrystalBallToolbarCrystalBallinMSExcel201.Determinewhichmodelinputsareuncertainanddefineaprobabilitydistribution.2.Identifywhichforecastsyouwanttoanalyze/measure(e.g.,NPV,Sigmalevel,processefficiency)3.RunSimulation4.AnalyzeResults5.GenerateReportHowdoesCrystalBallwork?21ThefirststeptousingCrystalBallistodeterminewhichmodelinputsareuncertain.Whichvaluesareestimates?Whichareaverages?Onceyouhaveidentifiedthese,youuseyourknowledgeoftheuncertaintyaroundtheinputtocreateaprobabilitydistributionforthatcell(whatCrystalBallcallsanassumption).CrystalBallletsyoudefinethesedistributionsusingtheDistributionGalleryD