风险管理软件CrystalBall操作指南(英文版)(doc 16页)

风险管理软件CrystalBall操作指南(英文版)(doc 16页)Monte-Carlo Simulation with Crystal Ball®To run a simulation using Crystal Ball®:1. Setup SpreadsheetBuild a spreadsheet that will calculate the performance measure (e.g., profit) in terms of the inputs (random or not). For random inputs, just enter any number.2. Define Assumptions—i.e., random variablesDefine which cells are random, and what distribution they should follow.3. Define Forecast—i.e., output or performance measureDefine which cell(s) you are interested in forecasting (typically the performance measure, e.g., profit).4. Choose Number of TrialsSelect the number of trials. If you would later like to generate the Sensitivity Analysis chart, choose “Sensitivity Analysis” under Options in Run Preferences.5. 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 in the Run menu) first.6. View 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 worksheet.Crystal Ball Toolbar:Define Define Run Start Reset Forecast Trend Assumptions Forecast Preferences Simulation Simulation Window ChartWalton Bookstore Simulation with Crystal Ball®Recall 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 $7.50 and is sold for $10. After February, all unsold calendars are returned to the publisher for a refund of $2.50 per calendar. Suppose Walton predicts demand will be somewhere between 100 and 300 (discrete uniform).Demand = d ~ Uniform[100, 300]Order Quantity = Q (decision variable)Revenue = $10 * Min(Q, d)Cost = $7.50 * QRefund = $2.50 * Max(Q–d, 0)Profit = Revenue – Cost + RefundStep #1 (Setup Spreadsheet)1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17A B C D E F Simulation of Walton's BookstoreD ataU ni t C ost =$7.50U ni t P rice =$10.00U ni t R ef und =$2.50D emand D is tribution (Uniform)M inim um =100M axi mum =300D ecis ion VariableOrder Quantity =200SimulationD em and R evenue C ost R efund P rof i t200$2,000.00$1,500.00$0.00$500.0015 16 17B C D E F SimulationD em and R evenue C ost R efund P rofi t 200=C5*M IN(C13,B17)=C4*C13=C6*M A X(C13-B17,0)=C17-D17+E17Walton Bookstore Simulation with Crystal Ball ®Step #2 (Define Assumptions —i.e., random variables)—color code (blue):1617B D emand 200and click on the “Define Assumptions” button in toolbar (or in the Cell menu):Select type of distribution:Provide parameters of distributions:8910B CD emand Distribution (U niform)M inim um =100M axi mum =300Walton Bookstore Simulation with Crystal Ball ®Step #3 (Define Forecast —i.e., output)1617F P rof i t$500.00click on the “Define Forecast” button in toolbar (or in the Cell menu),and fill in the Define Forecast dialogue box.Step #4 (Choose Number of Trials)Click on the “Run Preferences” button in toolbar (or in the Run menu):and select the number of trials to run.Walton Bookstore Simulation with Crystal Ball ®Step #5 (Run Simulation)Click on the “Start Simulation” button in toolbar (or Run in the Run menu):Step #6 (View Results)The results of the simulation can be viewed in a variety of different ways (frequency chart, cumulative chart, statistics, and percentiles). Choose different options under the View menu in the forecast window.The results can be copied into a worksheet or Word document (choose Copy under the Edit menu in the simulation output window.Using Trend Charts to Find the Impact of Order Quantityon Potential 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 (i.e., one assumption cell for demand—C14—with the rest set equal to C14).1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18A B C D E F G Simulation of Walton's BookstoreD ataU ni t C ost =$7.50U ni t P rice =$10.00U ni t R ef und =$2.50D emand D is tribution (Uniform)M inim um =100M axim um =300SimulationOrder Quantity D em and R evenue C ost R efund P rof it 100200$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.0012 13 14 15 16 17 18B C D E F G SimulationOrder Quantity D em and R evenue C ost R efund P rofi t 100200=$C$5*M IN(B14,C14)=$C$4*B14=$C$6*M AX(B14-C14,0)=D14-E14+F14 150=$C$14=$C$5*M IN(B15,C15)=$C$4*B15=$C$6*M AX(B15-C15,0)=D15-E15+F15 200=$C$14=$C$5*M IN(B16,C16)=$C$4*B16=$C$6*M AX(B16-C16,0)=D16-E16+F16 250=$C$14=$C$5*M IN(B17,C17)=$C$4*B17=$C$6*M AX(B17-C17,0)=D17-E17+F17 300=$C$14=$C$5*M IN(B18,C18)=$C$4*B18=$C$6*M AX(B18-C18,0)=D18-E18+F18After running the simulation, choose “Open Trend Chart” in the Run menu. This chart gives “certainty bands” for the forecast cells. 10% of the time, the project duration will fall within the inner band (light blue), 25% of the time within the 2nd band (red), 50% of the time within the third band (green), and 90% of the time within the outside band (dark blue).Project 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. Personnel 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 oversee 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 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 interdependence) are listed below. Three estimates are made for the completion time of each activity—the minimum time, most likely time, and maximum time.Immediate Time Estimates (days)Minimum Most Likely Maximum Activity Description PredecessorA Select Office Site —21 21 21B Create Org. & Fin. Plan —20 25 30C Determine Personnel Req. B 15 20 30D Design Facility A, C 20 28 42E Construct Facility D 40 48 66F Select Personnel to Move C 12 12 12G Hire New Employees F 20 25 32H Move Key Employees F 28 28 28 I Train New PersonnelE, G, H1015 24ABCDFGHIEStartEndGlobal Oil Simulation with Crystal Ball®Step #1 (Setup Spreadsheet)1 2 3 4 5 6 7 8 9 10 11 12 13 14 15A B C D E F G H I J Global Oil Relocation ProjectActivity Tim e (Triangular)Im m edi ate M ost Start Activity Finish Activity D escri ption P redecessors M inim um Likely M axi mum Tim e Tim e Tim eA Sel ect Site-21212102121B C reate Org. & Fin. P lan-20253002525C D eterm ine P ersonnel Req.B152030252045D D esign Facil ity A, C202842452873E C onstruct Facility D4048667348121F Sel ect Personnel to M ove C121212451257G H ire New Em ployees F202532572582H M ove Key E mployees F282828572885I Train N ew P ersonnel E, G, H10152412115136P roject C om pleti on Tim e =136.003456789101112131415H I JStart Activity FinishTim e Tim e Tim e021=H5+I5025=H6+I6=J620=H7+I7=M A X(J5,J7)28=H8+I8=J848=H9+I9=J712=H10+I10=J1025=H11+I11=J1028=H12+I12=M A X(J9,J11,J12)15=H13+I13P roject C ompleti on Tim e ==J13Step #2 (Define Assumptions—i.e., random variables)Each of the random activity times (B, C, D, E, G, and I) is assumed to follow the triangular distribution.Global Oil Simulation with Crystal Ball®Step #3 (Define Forecast—i.e., output)Cell J15 is the forecast cell:15G H I J Project Completion Time =136.00Step #4 (Choose Number 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)Additional Results Available with Crystal Ball®Slide the triangles below the histograms to determine the probability that the output (project duration) is less than a certain value (e.g., 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 probability. You can also type in a probability (in “Certainty”), and it will determine the range that has that probability.There is a 79% chance the project will be completed within 150 days.There is a 2.4% chance that the project will take more than 160 days.Sensitivity ChartChoose “Open Sensitivity Chart” in the Run menu. Note that this chart is only availableif you selected the “Sensitivity Analysis” option under Run Preferences. This chartgives an indication as to which random variables (activity times) have the greatest impact on the output cell (project completion time).Variability in activity E has the greatest impact on overall project duration, followed by activity D, C, I, and B. Variability in activity G has almost no impact.Fitting a DistributionCrystal Ball can be used to “fit” a distribution to data.The following data has been collected for the previous 100 phone calls to a mail-order house:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 99 100 101 102 103 104A B C D E F G H I Phone DataArrival Interarrival Length of Call Interarrival Length of Call Cus tomer #(minutes)Time(minutes)Time(minutes)18.228.22 3.77Averages 2.004 4.51212.25 4.03 4.53312.270.02 4.04416.26 3.98 3.70Simulation24518.06 1.81 5.38618.870.81 4.36723.46 4.58 4.41823.530.08 5.14928.73 5.20 4.761030.56 1.83 4.681132.36 1.80 5.061236.90 4.54 5.751343.30 6.40 4.061443.880.57 3.251545.17 1.29 3.5795194.020.28 4.2696195.48 1.46 3.3797195.870.38 4.4598196.840.98 5.0699197.810.97 5.20100200.43 2.61 4.25345G H IInterarrival Length of CallTime(minutes)Averages=AVERAGE(D5:D104)=AVERAGE(E5:E104)(80 rows have been hidden)Fitting Data to a DistributionUsing Crystal Ball® to fit data to a distribution1. Select a spreadsheet cell.2. Choose Define Assumption.3. Click the Fit button, then select the source of the fitted data.4. Click the Next button, then select the distributions to try to fit.5. Click OK.Interarrival TimeService Time。