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

[e-mail]1CrystalBallMonte-CarloSimulationwithCrystalBall®TorunasimulationusingCrystalBall®:1.SetupSpreadsheetBuildaspreadsheetthatwillcalculatetheperformancemeasure(e.g.,profit)intermsoftheinputs(randomornot).Forrandominputs,justenteranynumber.2.DefineAssumptions—i.e.,randomvariablesDefinewhichcellsarerandom,andwhatdistributiontheyshouldfollow.3.DefineForecast—i.e.,outputorperformancemeasureDefinewhichcell(s)youareinterestedinforecasting(typicallytheperformancemeasure,e.g.,profit).4.ChooseNumberofTrialsSelectthenumberoftrials.IfyouwouldlaterliketogeneratetheSensitivityAnalysischart,choose“SensitivityAnalysis”underOptionsinRunPreferences.5.RunSimulationRunthesimulation.Ifyouwouldliketochangeparametersandre-runthesimulation,youshould“reset”thesimulation(clickonthe“ResetSimulation”buttononthetoolbarorintheRunmenu)first.6.ViewResultsTheforecastwindowshowingtheresultsofthesimulationappearsautomaticallyafter(orduring)thesimulation.Manydifferentresultsareavailable(frequencychart,cumulativechart,statistics,percentiles,sensitivityanalysis,andtrendchart).Theresultscanbecopiedintotheworksheet.CrystalBallToolbar:[e-mail]2CrystalBallDefineDefineRunStartResetForecastTrendAssum[e-mail]3CrystalBallWaltonBookstoreSimulationwithCrystalBall®RecalltheWaltonBookstoreexample:ItisAugust,andtheymustdecidehowmanyofnextyear’snaturecalendarstoorder.Eachcalendarcoststhebookstore$7.50andissoldfor$10.AfterFebruary,allunsoldcalendarsarereturnedtothepublisherforarefundof$2.50percalendar.SupposeWaltonpredictsdemandwillbesomewherebetween100and300(discreteuniform).Demand=d~Uniform[100,300]OrderQuantity=Q(decisionvariable)Revenue=$10*Min(Q,d)Cost=$7.50*QRefund=$2.50*Max(Q–d,0)Profit=Revenue–Cost+RefundStep#1(SetupSpreadsheet)1234567891011121314151617ABCDEFSimulationofWalton'sBookstoreDataUnitCost=$7.50UnitPrice=$10.00UnitRefund=$2.50DemandDistribution(Uniform)Minimum=100Maximum=300DecisionVariableOrderQuantity=200SimulationDemandRevenueCostRefundProfit200$2,000.00$1,500.00$0.00$500.00151617BCDEFSimulationDemandRevenueCostRefundProfit200=C5*MIN(C13,B17)=C4*C13=C6*MAX(C13-B17,0)=[e-mail]4CrystalBallWaltonBookstoreSimulationwithCrystalBall®Step#2(DefineAssumptions—i.e.,randomvariables)Selectthecellthatcontainstherandomvariable(B17)—colorcode(blue):1617BDemand200andclickonthe“DefineAssumptions”buttonintoolbar(orintheCellmenu):Selecttypeofdistribution:Provideparametersofdistributions:8910BCDemandDistribution(Uniform)Minimum=100Maximum=[e-mail]5CrystalBallWaltonBookstoreSimulationwithCrystalBall®Step#3(DefineForecast—i.e.,output)Selectthecellthatcontainstheoutputvariabletoforecast(F17):1617FProfit$500.00clickonthe“DefineForecast”buttonintoolbar(orintheCellmenu),andfillintheDefineForecastdialoguebox.Step#4(ChooseNumberofTrials)Clickonthe“RunPreferences”buttonintoolbar(orintheRunmenu):[e-mail]6CrystalBallWaltonBookstoreSimulationwithCrystalBall®Step#5(RunSimulation)Clickonthe“StartSimulation”buttonintoolbar(orRunintheRunmenu):Step#6(ViewResults)Theresultsofthesimulationcanbeviewedinavarietyofdifferentways(frequencychart,cumulativechart,statistics,andpercentiles).ChoosedifferentoptionsundertheViewmenuintheforecastwindow.TheresultscanbecopiedintoaworksheetorWorddocument([e-mail]7CrystalBallUsingTrendChartstoFindtheImpactofOrderQuantityonPotentialProfitDefineseveralforecastcells(G14:G18)forseveralpossibleorderquantities(Q=100,150,200,250,300).Usethesamerandomorderquantityforeachtocomparethemmoreequally(i.e.,oneassumptioncellfordemand—C14—withtherestsetequaltoC14).123456789101112131415161718ABCDEFGSimulationofWalton'sBookstoreDataUnitCost=$7.50UnitPrice=$10.00UnitRefund=$2.50DemandDistribution(Uniform)Minimum=100Maximum=300SimulationOrderQuantityDemandRevenueCostRefundProfit100200$1,000.00$750.00$0.00$250.00150200$1,500.00$1,125.00$0.00$375.00200200$2,000.00$1,500.00$0.00$500.00250200$2,000.00$1,875.00$125.00$250.00300200$2,000.00$2,250.00$250.00$0.0012131415161718BCDEFGSimulationOrderQuantityDemandRevenueCostRefundProfit100200=$C$5*MIN(B14,C14)=$C$4*B14=$C$6*MAX(B14-C14,0)=D14-E14+F14150=$C$14=$C$5*MIN(B15,C15)=$C$4*B15=$C$6*MAX(B15-C15,0)=D15-E15+F15200=$C$14=$C$5*MIN(B16,C16)=$C$4*B16=$C$6*MAX(B16-C16,0)=D16-E16+F16250=$C$14=$C$5*MIN(B17,C17)=$C$4*B17=$C$6*MAX(B17-C17,0)=D17-E17+F17300=$C$14=$C$5*MIN(B18,C18)=$C$4*B18=$C$6*MAX(B18-C18,0)=D18-E18+F18Afterrunningthesimulation,choose“OpenTrendChart”intheRunmenu.Thischartgives“certaintybands”fortheforecastcells.10%ofthetime,theprojectdurationwillfallwithintheinnerband(lightblue),25%ofthetimewithinthe2ndband(red),50%ofthetimewithinthethirdband(green),and90%ofthetimewithintheoutsideband(darkblue)[e-mail]8CrystalBallProjectManagement—GlobalOilGlobalOilisplanningtomovetheircreditcardoperationtoDesMoines,IowafromtheirhomeofficeinDallas.Themoveinvolvesmanydiffere