ProjectManagement

Session9

CrystalBallOPIM52701Session9GoalsUnderstandwhyriskmustbeanalyzedKnowpros/consforthreewaystoanalyzeriskIdentifyrandomvariablesinmodelsKnowthefourstepsofasimulationprocessGeneraterandomnumberswithCrystalBallUsethefourstepsofasimulationprocessExplainhowCrystalBallsupportsProj.Mgmt.2Mostreal-worldbusinesssituationstodayareprobabilistic,butthedecisionmodelsusedtodealwiththemaredeterministic.Howtodealwithrandomness?IgnoreitSimplifyproblemtomakeitanalyticallytractable,getsolution,thenignorereal-lifecomplicationsFindawaytoobtainanapproximatesolutiontoreal-worldproblems3DealingwithRandomnessMonteCarlosimulationisamethodbywhichapproximatesolutionsareobtainedtorealistic(andthereforecomplicated)problemsThisisincontrasttoanalyticalmethods,whichobtainexactsolutionstohighlystylizedproblemsTradeoffbetweenrigorandrelevance4MonteCarloSimulationWhatisthis?Y=f(X1,X2,…,Xk)Often,thevaluesforoneormore"input"cellsareunknownoruncertainThiscreatesuncertaintyaboutthevalueofthe"output"cellSimulationcanbeusedtoanalyzethesetypesofmodels5IntroductiontoSimulationArandomvariableisanyvariablewhosevaluecannotbepredictedorsetwithcertainty.Many“inputcells”inspreadsheetmodelsareactuallyrandomvariables.Forexample:thefuturecostofrawmaterialsfutureinterestratesfuturenumberofemployeesinafirmexpectedproductdemandDecisionsmadeusinguncertaininformationofteninvolverisk.Whatrisks?6RandomVariables&RiskUsingexpectedvaluesforuncertaincellstellsusnothingaboutthevariabilityoftheperformancemeasure.Supposean$1,000investmentisexpectedtoreturn$2,000intwoyears.Wouldyouinvestif...theoutcomescouldrangefrom$1,060to$4,000?theoutcomescouldrangefrom$0to$2,100?Alternativeswiththesameexpectedvaluemayinvolveverydifferentlevelsofrisk.7WhyAnalyzeRisk?Best-Case/Worst-CaseAnalysis

What-ifAnalysis

SimulationMethodsofRiskAnalysis8Bestcase-pluginthemostoptimisticvaluesforeachoftheuncertaincells.Worstcase-pluginthemostpessimisticvaluesforeachoftheuncertaincells.Thisiseasytodoandboundstheoutcomes,buttellsusnothingaboutthedistributionofpossibleoutcomeswithinthebestandworst-caselimits.Otherproblemsorbenefits?9Best-Case/Worst-CaseAnalysisworstcasebestcaseworstcasebestcaseworstcasebestcaseworstcasebestcase10PossiblePerformanceMeasure

DistributionsWithinaRangePlugindifferentvaluesfortheuncertaincellsandseewhathappens.Benefits:ThisiseasytodowithspreadsheetsOther?Problems:Valuesmaybechoseninabiasedway.Hundredsorthousandsofscenariosmayberequiredtogeneratearepresentativedistribution.Doesnotsupplythetangibleevidence(factsandfigures)neededtojustifydecisionstomanagement.What-IfAnalysis11Valuesforuncertaincellsareselectedrandomly(andinanunbiasedmanner).Thecomputergenerateshundreds(orthousands)ofscenarios.Weanalyzethescenarioresultstobetterunderstandthebehavioroftheperformancemeasure.Allowsdecisionsbasedonsolidempiricalevidence.Simulation12Properriskassessmentrequiressimulation.

Simulationisa4stepprocess:1)Identifytheuncertaincellsinthemodel.2)ImplementappropriateRandomNumberGenerators(RNGs)foreachuncertaincell.3)Replicate復制themodelntimes,andrecordthevalueofthebottom-lineperformancemeasure.4)Analyzethesamplevaluescollectedontheperformancemeasure.SimulationContinued13ARandomNumberGeneratorisamathematicalfunctionthatrandomlygenerates(returns)avaluefromaparticularprobabilitydistribution.

WecanimplementRandomNumberGeneratorsforuncertaincellstoallowustosamplefromthedistributionofvaluesexpectedfordifferentcells.RandomNumberGenerators14TheRAND()functionreturnsuniformlydistributedrandomnumbersbetween0.0and0.9999999.Supposewewanttosimulatetheactoftossingafaircoin.Let1represent“heads”and2represent“tails”.ConsiderthefollowingRNG:=IF(RAND()<0.5,1,2)HowRandomNumberGeneratorsWork15CrystalBallprovidestwodifferentwaysforcreatingRandomNumberGeneratorsinspreadsheetsCrystalBallfunctionsUsedinformulaslikeanyotherExcelfunctionRequireCBtobeinstalledonthemachinedisplayingthespreadsheet&donotsupportallCBfunctionalityTheDistributionGalleryDisplayanumber(notaformula)inacellbutgeneratesrandomnumbersforthatcellwhensimulatingthemodelDoesnotrequireCBtobeinstalledonthemachinetodisplaythespreadsheet&supportsallCBfunctionalityGeneratingRandomNumbers

withCrystalBall16ClickDefineAssumptioniconSelectdistributionSpecifyparametersUsingtheDistributionGallery1718DiscreteProbabilityDistributions18ContinuousProbabilityDistributions19DefineMenuRunMenuAnalyzeMenuCrystalBallToolbarCrystalBallinMSExcel20Determinewhichmodelinputsareuncertainanddefineaprobabilitydistribution.Identifywhichforecastsyouwanttoanalyze/measure

(e.g.,NPV,Sigmalevel,processefficiency)RunSimulationAnalyzeResultsGenerateReportHowdoesCrystalBallwork?21ThefirststeptousingCrystalBallistodeterminewhichmodelinputsareuncertain.Whichvaluesareestimates?Whichareaverages?

Onceyouhaveidentifiedthese,youuseyourknowledgeoftheuncertaintyaroundtheinputtocreateaprobabilitydistributionforthatcell(whatCrystalBallcallsanassumption).CrystalBallletsyoudefinethesedistributionsusingtheDistributionGalleryDefineYourDistributions22EntervarietyofparameterstodefinedistributionsCanfitdistributionstorawdataCancellreferenceallfieldsCancorrelatepairsofassumptionsMarkerlinesAssumptionDialog23Thenextstepistoidentifyaforecast.Aforecastisaformulacellthatyouwanttomeasureandanalyze.Inthismodel,youselecttheNetProfit(cellC23).IdentifyYourForecasts24NumberofsimulationtrialsperformedDisplayrangeCertainty(probability)thattheforecastwillreach$2,812,558Partswithinthespeclimitsareshowninblue,partsoutsidespeclimitsareshownredNumberofdatapointsdisplayedinthechartCrystalBallusesMonteCarlosimulationtorandomlygeneratethousandsofwhat-ifscenariosEachscenarioisthencapturedandpresentedinafrequencychart–(ForecastChart)RunSimulation25What’sresponsibleformostofthevariationintheforecast?Thesensitivitychartshowstheinfluenceeachassumptioncellhasontheforecast.AnalyzeResults26ReportsSelectapre-definedreportorcreateyourowncustomreport.Reportsnowincludenewstatisticsandmorecontroloverdataandcharts.ExtractDataYoucanextractdatafro