风险管理软件crystalball使用指导

1、Monte-Carlo Simulation with Crystal BallTo run a simulation using Crystal Ball:Setup SpreadsheetBuild a spreadsheet that will calculate the performance measure ., profit) in terms of the inputs (random or not). For random inputs, just enter any number.Define Assumptions., random variablesDefine whic

2、h cells are random, and what distribution they should follow.Define Forecast., output or performance measureDefine which cell(s) you are interested in forecasting (typically the performance measure,., profit).Choose Number of TrialsSelect the number of trials. If you would later like to generate the

3、 Sensitivity Analysis chart, choose “Sensitivity Analysis “ under Options in Run Preferences.Run SimulationRun the simulation. If you would like to change parameters and re-run the simulation, you should “reset the simulation (click on the“Reset Simulation button on the toolbar or iRun menu) first.V

4、iew ResultsThe forecast window showing the results of the simulation appears automatically after (or during) the simulation. Many different results are available (frequency chart, cumulative chart, statistics, percentiles, sensitivity analysis, and trend chart). The results can be copied into the wo

5、rksheet.Crystal Ball Toolbar:IX Lilik 口囹口 觞落，Crystal Ball Toolbar:IX Lilik 口囹口 觞落，国I/ 、 /IA B lA勒山园田的团出 XXDefineDefineRunStartResetForecastTrendAssumptionsForecastPreferencesSimulationSimulationWindowChartWalton Bookstore Simulation with Crystal BallWalton Bookstore Simulation with Crystal BallRecal

6、l the Walton Bookstore example: It is August, and they must decide how many of next year s nature calendars to order. Each calendar costs the bookstore $ and is sold for $10. After February, all unsold calendars are returned to the publisher for a refund of $ per calendar. Suppose Walton predicts de

7、mand will be somewhere between 100 and 300 (discrete uniform).DemandOrder QuantityRevenue Cost RefundDemandOrder QuantityRevenue Cost Refund Profit=$10 * Min(Q, d)=$ * Q=$ * Max(Qd 0)=RevenueCost + RefundStep #1 (Setup Spreadsheet)ABCDEF1Simulation of Waltons Bookstore23Data4Unit Cost =$7.505Unit Pr

8、ice =$10.006Unit Refund =$2.5078Demand D istribution (Uniform)9Minimum =10010Maximum =3001112Decis ion Variable13Order Quantity =2001415Simulation16DemandRevenueCostRefundProfit17200$2,000.00$1,500.00$0.00$500.00BCDEF15Simulation16DemandRevenueCostRefundProfi t17200=C5*MIN(C13,B17)=C4*C13=C6*MAX(C13

9、-B17,0)=C17-D17+E17Step #2 (Define Assumptions.，random variables)Select the cell that contains the random variable (B17) color code (blue):B16Dem and17200and click on the “ Define Assumptions button in toolbar (or in the Cell menu):lA.令 LdiL E 囹H 电整pS7 h,Select type of distribution:CeH Bl7: Distribu

10、hion GaHeryQK 1 ganctl | Morel CeH Bl7: Distribuhion GaHeryQK 1 ganctl | Morel EiL. | He IpProvide parameters of distributions:Provide parameters of distributions:Assuvnptiinn |DeiriandlCeH B17: UnitDim DiitlriibulionAssuvnptiinn |DeiriandlCeH B17: UnitDim Diitlriibulion1Ml DO 15DDD 200.00250. C0 30

11、D.0D# Stallc r DymamicMln pC9Max |-=C10总一五eqo 上BC8Demand Distribution (U niform)9Minimum =BC8Demand Distribution (U niform)9Minimum =10030010Maximum =EnterCjHlkryGnrd 电 laHelpStep #3 (Define Forecast, output)Select the cell that contains the output variable to forecast (F17):F16Prof it17$500.00click

12、 on the “ Define Forecast n tob(ttton(br in the Cell menu),LiIlStep #3 (Define Forecast, output)Select the cell that contains the output variable to forecast (F17):F16Prof it17$500.00click on the “ Define Forecast n tob(ttton(br in the Cell menu),LiIl B!囹B 幅E A 同|嗨皿田的团十and fill in the Define Forecas

13、t dialogue box.Step #4 (Choose Number of Trials)Click on the “ Run Preferences ”IX Lilllu t 囹口 腕强，button in toolbar (or in the Run menu):田图图and select the number of trials to run.Run PretBrencetMaKimum Number of Trials： |50DP Stop il Spedfied Precisian is fieachedConfidence Level： |95.00 %9 Stop II

14、Calculation Erroir OccursTurboOKCancel I HelpStep #5 (Run Simulation)Click on the“Start Simulation“ button in toolbar (or Run in the Run menu):lA. Lill B! lA. Lill B! E! d 电勰“ IH|a h,皿图由国画Step #6 (View Results)The results of the simulation can be viewed in a variety of different ways (frequency char

15、t, cumulative chart, statistics, and percentiles). Choose different options under the View menu in the forecast window.g Fgiecft; Pnplit I籍 Fgiecaft; Pnplit IEdi: Ereleticfrg yiew 日卡 Hd口500 TrialsiFrequency Chart0 OwtllersEdt Frel&ieflce 里i则 Hyi H即50D TrialsCumulative Chart0 Ouitllers占Eqpqott3 21812

16、61,000 厂一m-餐，一一I j- ($245.61)($59.21S127.19J313.6DInfinityCertainty |l00.00 % * pllnflnlly一 m 吵Do .9W.O 营=qflqotl傥期 E1($59.211S127.191313.6015D0,00Certainty% 4 |*lnflnlty|l 00005 0 537为12The results can be copied into a worksheet or Word document (choose Copy under the Edit menu in the simulation ou

17、tput window.Using Trend Charts to Find the Impact of Order Quantity onPotential ProfitDefine several forecast cells (G14:G18) for several possible order quantities (Q=100, 150, 200, 250, 300). Use the same random order quantity for each to compare them more equally ., one assumption cell for demandC

18、14 with the rest set equal to C14).ABCDEFG1Simulation of Waltons Bookstore23Data4Unit Cost =$7.505Unit Price =$10.006Unit Refund =$2.5078Demand D is tribution (Uniform)9Minimum =10010Maximum =3001112Simulation13Order QuantityDemandRevenueCostRefundProfit14100200$1,000.00$750.00$0.00$250.0015150200$1

19、,500.00$1,125.00$0.00$375.0016 200200$2,000.00$1,500.00$0.00$500.0017250200$2,000.00$1,875.00$125.00$250.0018 300200$2,000.00$2,250.00$250.00$0.00BCDEFG12Simulation13Order QuantityDem andRevenueCostRefundProfit14100200=$C$5*MIN(B14,C14)=$C$4*B14=$C$6*MAX(B14-C 14,0)=D14-E14+F1415150=$C$14=$C$5*MIN(B

20、15,C15)=$C$4*B15=$C$6*MAX(B15-C 15,0)=D15-E15+F1516200=$C$14=$C$5*MIN(B16,C16)=$C$4*B16=$C$6*MAX(B16-C 16,0)=D16-E16+F1617250=$C$14=$C$5*MIN(B17,C17)=$C$4*B17=$C$6*MAX(B17-C 17,0)=D17-E17+F1718300=$C$14=$C$5*MIN(B18,C18)=$C$4*B18=$C$6*MAX(B18-C 18,0)=D18-E18+F18After running the simulation, choose “

21、OpeiTrend Chart “in the Run menu. This chart gives “ certainty bands for the forecast cells. 10% of the time, the project duration willithikh whe inner band (light blue), 25% of the time within the 2 nd band (red), 50% of the time within the third band (green), and 90% of the time within the outside

22、 band (dark blue).T rend ChartProject Management-Global OilGlobal Oil is planning to move their credit card operation to Des Moines, Iowa from their home office in Dallas. The move involves many different divisions within the company. Real estate must select one of three available office sites. Pers

23、onnel has to determine which employees from Dallas will move, how many new employees to hire, and who will train them. The systems group and treasurer s office must organize the new operating procedure and make financial arrangements. The architects will have to design the interior space, and overse

24、e needed structural improvements. Each site is an existing building with sufficient open space, but office partitions, computer facilities, furnishings, and so on, must all be provided.A complicating factor is that there is an interdependence of activities. In other words, some parts of the project

25、cannot be started until other parts are completed. For example, Global cannot construct the interior of an office before it has been designed. Neither can it hire new employees until it has determined its personnel requirements.The necessary activities and their necessary predecessors (due to interd

26、ependence) are listed below. Three estimates are made for the completion time of each activitythe minimum time, most likely time, and maximum time.ActivityDescriptionImmediatePredecessorTime Estimates (days)Minimumi Most LikelyMaximumASelect Office Site一212121BCreate Org. & Fin. Plan一202530CDetermin

27、e Personnel Req.B152030DDesign FacilityA, C202842EConstruct FacilityD404866FSelect Personnel to MoveC121212GHire New EmployeesF202532HMove Key EmployeesF282828ITrain New PersonnelE, G, H101524Global Oil Simulation with Crystal BallStep #1 (Setup Spreadsheet)ABCDEFGHIJ1Global Oil Relocation Project2A

28、ctivity Tim e (Triangular)3ImmediateMostStartActivityFinish4ActivityDescri ptionPredecessorsMinimumLikelyMaximumTimeTimeTime5ASelect Site-212121021216BCreate Org. & Fin. P lan-202530025257CDeterm ine Personnel Req.B1520302520458DDesign Facil ityA, C2028424528739EConstruct FacilityD404866734812110FSe

29、lect Personnel to M oveC12121245125711GHire New EmployeesF20253257258212HMove Key E mployeesF28282857288513ITrain N ew PersonnelE, G, H1015241211513614115Project C ompletion Tim e =136.00HIJ3StartActivityFinish4TimeTimeTime5021=H5+I56025=H6+I67=J620=H7+I78=MAX(J5,J7)28=H8+I89=J848=H9+I910=J712=H10+I

30、1011=J1025=H11+I1112=J1028=H12+I1213=MAX(J9,J11,J12)15=H13+I131415Project Completon Time =J13Step #2 (Define Assumptions., random variables)Each of the random activity times (B, C, D, E, G, and I) is assumed to follow the triangulardistribution.CeH NG: Triangular DistributionAssumption Name： |l6J Pi

31、g恒CeH IE: D话tribsion Gallery2-qnqEd22.5325 OD 27.50 SOlOO在 StalicDynamic 4 |1DOD20.00Likeliest pF6Max |=G6tiLci I EulHal/ry | 加打臼石死|Help IGlobal Oil Simulation with Crystal BallStep #3 (Define Forecast, output)GHIJ15Project Completion Time =136.00|Cell J15 is the forecast cell:Step #4 (Choose Number

32、 of Trials)500 trials were run. In addition, Sensitivity Analysis was enabled in the Options of the Run Preferences dialogue box. This allows for the generation of sensitivity analysis results later.Step #5 (Run Simulation)Step #6 (View Results)FrRquency Ctmit? UiiilibRrsCtrlHinti 匕1巾JTlT，t1.1 IE jE

33、曰 Rm ,赤OFwm-kI: Ptciact Cwvphiion 1 iwFrRquency Ctmit? UiiilibRrsCtrlHinti 匕1巾JTlT，t1.1 IE jE曰 Rm ,赤OFwm-kI: Ptciact Cwvphiion 1 iwII网E/9r 曰 Rtfi HqipOFwm-wI: Ptejact Cwvplikm T iwAM TrlnlnCiimihlnllvc Charl匆 au wllbrs - 5WAdditional Results Available with Crystal BallSlide the triangles below the h

34、istograms to determine the probability that the output (project duration) is less than a certain value ., a deadline), greater than a certain value, or between any two values (by sliding both triangles).Alternatively, you can type in values for the lower bound or upper bound to determine the probabi

35、lity. You can also type in a probability (in Certainty a nd, it will determine the range that has that probability.1 .1 MlEifE/ih Rm H 女uullbRr? lUUllbRPSFrRquenci (.hnrt pnllnltyCertnlnr - W * 弓面口口9Fk 启 1 .1 MlEifE/ih Rm H 女uullbRr? lUUllbRPSFrRquenci (.hnrt pnllnltyCertnlnr - W * 弓面口口9Fk 启 ue-ncyE

36、 * oDOOmem2ffif.130. N1J15514250Wi，|1I1jDIICermanly 防后一H* 1531751 薇aq p InllnllyE#Riji HflbFrRqiiency Chii,Frrtlwuc-ncy 93There is a 79% chance the project will be completed within 150 days. There is a % chance that the project will take more than 160 days.Sensitivity ChartChoose “ Open Sensitivity

37、Chart “ in the Run menu. Note that this chart is only available if you selected the “Sensitivity Analysis “ option under Run Preferences. This chart gives an indication as to which random variables (activity times) have the greatest impact on the output cell (project completion time).Variability in activity E