风险管理软件(Crystal-Ball)使用指导(中英文)
2023年10月28日发(作者：铃儿响叮当歌词)
灯谜跷跷板打一成语是什么-
Iso19011@
Crystal Ball
Page 1
Monte-Carlo Simulation with Crystal Ball®
用水晶球软件进行蒙特卡洛模拟
To run a simulation using Crystal Ball®:
1. Setup Spreadsheet
1．设定数据表
Build 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 variables
Define which cells are random, and what distribution they should follow.
2．定义假设的前提—例如，随机变量
确定那些单元格的数据时随机的，这些数据应该服从什么样的分布
3. Define Forecast—i.e., output or performance measure
Define which cell(s) you are interested in forecasting (typically the performance
measure, e.g., profit).
3．预测结果的确定—例如，数据输出或者性能的测定
确定哪些单元格的数据是你想预测的（典型的性能指标，例如，利润）
4. Choose Number of Trials
Select the number of trials. If you would later like to generate the Sensitivity Analysis
chart, choose “Sensitivity Analysis” under Options in Run Preferences.
4. 选择试验的次数
选择试验的次数。如果 要生成敏感度分析图表，选择优先运行下的“敏感度分析”
5. Run Simulation
Run 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 Results
The forecast window showing the results of the simulation appears automatically after
(or during) the simulation. Many different results are available (frequency chart, Iso19011@
Crystal Ball
Page 2
cumulative chart, statistics, percentiles, sensitivity analysis, and trend chart). The results
can be copied into the worksheet.
查看结果
在模拟最后或者运行的过程中，预测窗口会自动显示模拟的结果。可以获得不同的结果（频率图，累计图，统计图，百分比图，模拟分析图和趋势图），结果可以复制到工作表中。
Crystal Ball Toolbar:（水晶球的工具栏）
Define Define
Assumptions Forecast
Run Start Reset Forecast Trend
Preferences Simulation Simulation Window Chart
（确定假设）确定预测结果 优先运行 开始模拟 模拟重置 预测窗口 趋势图 Iso19011@
Crystal Ball
Page 3
Walton Bookstore Simulation with Crystal Ball®
水晶球在Walton书店模拟中的应用
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).
回想Walton书店的例子。在八月份，书店需要确定订购的明年的日历的数量。单个日历的进价是7.5美元，售价是10美元。二月份之后，所有未售的日历将会以2.5美元的价格退还给出版商。假设Walton 日历的销售量在100~300之间（离散型均匀分布）
Demand = d ~ Uniform[100, 300]
Order Quantity = Q (decision variable)
Revenue = $10 * Min(Q, d)
Cost = $7.50 * Q
Refund = $2.50 * Max(Q–d, 0)
Profit = Revenue – Cost + Refund
需求量= d ~ Uniform[100, 300]
订购量= Q (随决定变化)
收入 = $10 * Min(Q, d)
成本 =$7.50 * Q
退款 = $2.50 * Max(Q–d, 0)
利润 =收入-成本+退款
Step #1 (Setup Spreadsheet)
第一步（制作电子表格） Iso19011@
Crystal Ball
Page 4
A17BCDEFSimulation of Walton's BookstoreDataUnit Cost =Unit Price =Unit Refund =$7.50$10.00$2.50Demand Distribution (Uniform)Minimum =100Maximum =300Decision VariableOrder Quantity =SimulationDemand200CRevenue=C5*MIN(C13,B17)200Revenue$2,000.00DCost=C4*C13Cost$1,500.00ERefund$0.00Profit$500.00FProfit=C17-D17+E17
B15Simulation16Demand17200Refund=C6*MAX(C13-B17,0)
Iso19011@
Crystal Ball
Page 5
Walton Bookstore Simulation with Crystal Ball®
用水晶球对Walton书店进行模拟
Step #2 (Define Assumptions—i.e., random variables)
第二步（定义假设-例如，随机变量）
Select the cell that contains the random variable (B17) — color code (blue):
选择包含随机变量的单元（B17）—标（蓝）
1617BDemand200
and click on the “Define Assumptions” button in toolbar (or in the Cell menu):、
点击在工具栏中（单元格菜单）的“定义假设”按钮
选择分布类型
Select type of distribution:
提供分布的参数
Provide parameters of distributions:
Iso19011@
Crystal Ball
Page 6
BC8Demand Distribution (Uniform)9Minimum =10010Maximum =300 Iso19011@
Crystal Ball
Page 7
Walton Bookstore Simulation with Crystal Ball®
Step #3 (Define Forecast—i.e., output)
第三步（确定预测—例如，输出量）
Select the cell that contains the output variable to forecast (F17):
选择包含输出变量的单元格进行预测（F17）
FProfit$500.00
点击工具栏（单元格菜单）中的“确定预测”按钮
click on the “Define Forecast” button in toolbar (or in the Cell menu),
1617
在确定预测对话框中输入
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.
Iso19011@
Crystal Ball
Page 8
Iso19011@
Crystal Ball
Page 9
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.
Iso19011@
Crystal Ball
Page 10
The results can be copied into a worksheet or Word document (choose Copy under the Edit
menu in the simulation output window.
结果可以复制到工作表或者Word文档中（选择模拟输出窗口中编辑菜单下的复制） Iso19011@
Crystal Ball
Page 11
Using Trend Charts to Find the Impact of Order
Quantity on Potential Profit
用趋势图到订货量对利润的影响
Define 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).
针对不同可能的订货量(Q=100, 150, 200, 250, 300)，定义一些预测单元格（G14:G18），为了更为公平的对比，每次都使用相同的随机订货量
A1718BCDEFGSimulation of Walton's BookstoreDataUnit Cost =Unit Price =Unit Refund =$7.50$10.00$2.50Demand Distribution (Uniform)Minimum =100Maximum =300SimulationOrder Quantity100Demand200Revenue$1,000.00$1,500.00$2,000.00$2,000.00$2,000.00Cost$750.00$1,125.00$1,500.00$1,875.00$2,250.00Refund$0.00$0.00$0.00$125.00$250.00Profit$250.00$375.00$500.00$250.00$0.00
718BSimulationOrder Quantity100CDemand200=$C$14=$C$14=$C$14=$C$14DRevenue=$C$5*MIN(B14,C14)=$C$5*MIN(B15,C15)=$C$5*MIN(B16,C16)=$C$5*MIN(B17,C17)=$C$5*MIN(B18,C18)ECost=$C$4*B14=$C$4*B15=$C$4*B16=$C$4*B17=$C$4*B18FRefund=$C$6*MAX(B14-C14,0)=$C$6*MAX(B15-C15,0)=$C$6*MAX(B16-C16,0)=$C$6*MAX(B17-C17,0)=$C$6*MAX(B18-C18,0)G
Profit=D14-E14+F14=D15-E15+F15=D16-E16+F16=D17-E17+F17=D18-E18+F18
After 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).
运行模拟之后，选择运行菜单下的“打开趋势图”。趋势图针对预测单元格给出了确定的频带。项目执行期间有10%的几率位于内带（浅蓝），25%位于第二带（红），50%位于第三带（绿），90%的几率位于外带（深蓝）。 Iso19011@
Crystal Ball
Page 12
Iso19011@
Crystal Ball
Page 13
Project Management—Global Oil
工程项目管理—环球石油
Global 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.
下面列出了一些必要的工作和必须的准备工作（根据相互关联）。对每项活动给出了3个评估量—最小时间，最可能时间和最大时间。
Activity Description
A Select Office Site
B Create Org. & Fin. Plan
Immediate Time Estimates (days)
Predecessor Minimum Most Likely Maximum
— 21 21 21
— 20 25 30 Iso19011@
Crystal Ball
Page 14
C
D
E
F
G
H
I
Determine Personnel Req.
Design Facility
Construct Facility
Select Personnel to Move
Hire New Employees
Move Key Employees
Train New Personnel
StartBCADB
A, C
D
C
F
F
E, G, H
EGFH15
20
40
12
20
28
10
I20
28
48
12
25
28
15
30
42
66
12
32
28
24
End
Iso19011@
Crystal Ball
Page 15
Global Oil Simulation with Crystal Ball®
Step #1 (Setup Spreadsheet)
第一步（建立工作表）
ABCDEFGHIJ1Global Oil Relocation Project234ActivityDescription5ASelect Site6BCreate Org. & Fin. Plan7CDetermine Personnel Req.8DDesign Facility9EConstruct Facility10FSelect Personnel to Move11GHire New Employees12HMove Key Employees13ITrain New Personnel1415Activity Time (Triangular)MostMinimumLikelyMaximum2201524ImmediatePredecessors--BA, CDCFFE, G, HStartTime57121ActivityTime212525FinishTime278285136136.00Project Completion Time =
34567891HStartTime00=J6=MAX(J5,J7)=J8=J7=J10=J10=MAX(J9,J11,J12)212525IActivityTimeJFinishTime=H5+I5=H6+I6=H7+I7=H8+I8=H9+I9=H10+I10=H11+I11=H12+I12=H13+I13Project Completion Time ==J13
Step #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.
每次随机活动的次数(B, C, D, E, G, 和 I)服从三角分布。
Iso19011@
Crystal Ball
Page 16
Iso19011@
Crystal Ball
Page 17
Global Oil Simulation with Crystal Ball®
Step #3 (Define Forecast—i.e., output)
第三步（确定预测—例如，输出量）
Cell J15 is the forecast cell:
J15单元格为预测单元格GHIProject Completion Time =J136.00
15
Step #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.
运行500次。另外，选择优先运行对话框可以进行敏感度分析，之后可以生成敏感度分析的结果。
Step #5 (Run Simulation)
第五步（进行模拟）
Step #6 (View Results)
第六步（查看结果）
Iso19011@
Crystal Ball
Page 18
Iso19011@
Crystal Ball
Page 19
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.
另外，可以通过键入上限和下限确定概率。也可以键入概率（在“Certainty”中），确定概率所包含的范围。
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.
项目在150天内完成的可能性为79%。在160多天内完成的概率为2.4%。
Sensitivity Chart
敏感度图
Choose “Open Sensitivity 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).
选择运行菜单下的“打开敏感度分析图”。注意只有在选择了优先运行下的“敏感度分析”时敏感度图是可用的。敏感度图给出了随机变量（活动次数）对输出结果（项目完成时间）的最大影响。 Iso19011@
Crystal Ball
Page 20
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.
活动E的变化对整个项目的持续时间影响最大，然后依次是活动D,C,I和B。活动G的变化对项目几乎没有影响。 Iso19011@
Crystal Ball
Page 21
Fitting a Distribution
拟合分布
Crystal 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:
下面的数据来自邮购商行的前100个电话。
ABCDEFGHIPhone DataCustomer #96979899100Arrival(minutes)8.2212.2512.2716.2618.0618.8723.4623.5328.7330.5632.3636.9043.3043.8845.17194.02195.48195.87196.84197.81200.43InterarrivalTime8.224.030.023.981.810.814.580.085.201.831.804.546.400.571.290.281.460.380.980.972.61Length of Call(minutes)3.774.534.043.705.384.364.415.144.764.685.065.754.063.253.574.263.374.455.065.204.25AveragesInterarrivalTime2.004Length of Call(minutes)4.51Simulation24
G345HInterarrivalTimeAverages=AVERAGE(D5:D104)
ILength of Call(minutes)=AVERAGE(E5:E104)
(80 rows have been hidden)
（前80个被隐藏）
Iso19011@
Crystal Ball
Page 22
Fitting Data to a Distribution
数据拟合
Using Crystal Ball® to fit data to a distribution
1. Select a spreadsheet cell.
1．选择工作表中的单元格。
2. Choose Define Assumption.
2．选择定义的预测
3. Click the Fit button, then select the source of the fitted data.
3．点击拟合按钮，然后选择你和数据的来源。
4. Click the Next button, then select the distributions to try to fit.
4．点击Next按钮，然后选择分布进行拟合。
5. Click OK.
5．点击OK。
Interarrival Time
到达间隔时间
Service Time
服务时间 Iso19011@
Crystal Ball
Page 23