水晶球软件使用Crystal Ball.ppt 30页

ProjectManagementSession9CrystalBallOPIM52701 Session9GoalsUnderstandwhyriskmustbeanalyzedKnowpros/consforthreewaystoanalyzeriskIdentifyrandomvariablesinmodelsKnowthefourstepsofasimulationprocessGeneraterandomnumberswithCrystalBallUsethefourstepsofasimulationprocessExplainhowCrystalBallsupportsProj.Mgmt.2 Mostreal-worldbusinesssituationstodayareprobabilistic,butthedecisionmodelsusedtodealwiththemaredeterministic.Howtodealwithrandomness?IgnoreitSimplifyproblemtomakeitanalyticallytractable,getsolution,thenignorereal-lifecomplicationsFindawaytoobtainanapproximatesolutiontoreal-worldproblems3DealingwithRandomness MonteCarlosimulationisamethodbywhichapproximatesolutionsareobtainedtorealistic(andthereforecomplicated)problemsThisisincontrasttoanalyticalmethods,whichobtainexactsolutionstohighlystylizedproblemsTradeoffbetweenrigorandrelevance4MonteCarloSimulation Whatisthis?Y=f(X1,X2,…,Xk)Often,thevaluesforoneormore"input"cellsareunknownoruncertainThiscreatesuncertaintyaboutthevalueofthe"output"cellSimulationcanbeusedtoanalyzethesetypesofmodels5IntroductiontoSimulation Arandomvariableisanyvariablewhosevaluecannotbepredictedorsetwithcertainty.Many“inputcells”inspreadsheetmodelsareactuallyrandomvariables.Forexample:thefuturecostofrawmaterialsfutureinterestratesfuturenumberofemployeesinafirmexpectedproductdemandDecisionsmadeusinguncertaininformationofteninvolverisk.Whatrisks?6RandomVariables&Risk Usingexpectedvaluesforuncertaincellstellsusnothingaboutthevariabilityoftheperformancemeasure.Supposean$1,000investmentisexpectedtoreturn$2,000intwoyears.Wouldyouinvestif...theoutcomescouldrangefrom$1,060to$4,000?theoutcomescouldrangefrom$0to$2,100?Alternativeswiththesameexpectedvaluemayinvolveverydifferentlevelsofrisk.7WhyAnalyzeRisk? Best-Case/Worst-CaseAnalysisWhat-ifAnalysisSimulationMethodsofRiskAnalysis8 Bestcase-pluginthemostoptimisticvaluesforeachoftheuncertaincells.Worstcase-pluginthemostpessimisticvaluesforeachoftheuncertaincells.Thisiseasytodoandboundstheoutcomes,buttellsusnothingaboutthedistributionofpossibleoutcomeswithinthebestandworst-caselimits.Otherproblemsorbenefits?9Best-Case/Worst-CaseAnalysis worstcasebestcaseworstcasebestcaseworstcasebestcaseworstcasebestcase10PossiblePerformanceMeasureDistributionsWithinaRange Plugindifferentvaluesfortheuncertaincellsandseewhathappens.Benefits:ThisiseasytodowithspreadsheetsOther?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)HowRandomNumberGeneratorsWork15 CrystalBallprovidestwodifferentwaysforcreatingRandomNumberGeneratorsinspreadsheetsCrystalBallfunctionsUsedinformulaslikeanyotherExcelfunctionRequireCBtobeinstalledonthemachinedisplayingthespreadsheet&donotsupportallCBfunctionalityTheDistributionGalleryDisplayanumber(notaformula)inacellbutgeneratesrandomnumbersforthatcellwhensimulatingthemodelDoesnotrequireCBtobeinstalledonthemachinetodisplaythespreadsheet&supportsallCBfunctionalityGeneratingRandomNumberswithCrystalBall16 ClickDefineAssumptioniconSelectdistributionSpecifyparametersUsingtheDistributionGallery17 18DiscreteProbabilityDistributions18 ContinuousProbabilityDistributions19 DefineMenuRunMenuAnalyzeMenuCrystalBallToolbarCrystalBallinMSExcel20 Determinewhichmodelinputsareuncertainanddefineaprobabilitydistribution.Identifywhichforecastsyouwanttoanalyze/measure(e.g.,NPV,Sigmalevel,processefficiency)RunSimulationAnalyzeResultsGenerateReportHowdoesCrystalBallwork?21 ThefirststeptousingCrystalBallistodeterminewhichmodelinputsareuncertain.Whichvaluesareestimates?Whichareaverages?Onceyouhaveidentifiedthese,youuseyourknowledgeoftheuncertaintyaroundtheinputtocreateaprobabilitydistributionforthatcell(whatCrystalBallcallsanassumption).CrystalBallletsyoudefinethesedistributionsusingtheDistributionGalleryDefineYourDistributions22 EntervarietyofparameterstodefinedistributionsCanfitdistributionstorawdataCancellreferenceallfieldsCancorrelatepairsofassumptionsMarkerlinesAssumptionDialog23 Thenextstepistoidentifyaforecast.Aforecastisaformulacellthatyouwanttomeasureandanalyze.Inthismodel,youselecttheNetProfit(cellC23).IdentifyYourForecasts24 NumberofsimulationtrialsperformedDisplayrangeCertainty(probability)thattheforecastwillreach$2,812,558Partswithinthespeclimitsareshowninblue,partsoutsidespeclimitsareshownredNumberofdatapointsdisplayedinthechartCrystalBallusesMonteCarlosimulationtorandomlygeneratethousandsofwhat-ifscenariosEachscenarioisthencapturedandpresentedinafrequencychart–(ForecastChart)RunSimulation25 What’sresponsibleformostofthevariationintheforecast?Thesensitivitychartshowstheinfluenceeachassumptioncellhasontheforecast.AnalyzeResults26 ReportsSelectapre-definedreportorcreateyourowncustomreport.Reportsnowincludenewstatisticsandmorecontroloverdataandcharts.ExtractDataYoucanextractdatafrombothforecastsandassumptionsandextractmultipletypesofdata.GenerateReports27 GotoCrystalBallinExcelLet’sBuildOurOwnModel28 Withsimulationsoftware(likeCrystalBall)youcanaccountfor,andmanageagainsttheuncertaintyof:Time(howlongtheprojectmaytake)Money(howmuchtheprojectmaycost)Scope(variancefromspecification)RelevancetoProjectManagement29 Simulationisusedtodescribethebehaviorofabottom-lineperformancemeasurewhenvaluesofoneormoreinputvariablesareuncertain.Often,someinputvariablesareunderthedecisionmakerscontrol.Simulation(CrystalBall)canalsoassistinfindingtheoptimalvaluesofthecontrollableinputvariables.(SimulationOptimization)AdditionalUsesofSimulation30