水晶球软件_初级教程 9页

水晶球软件2000专业版(CrystalBall®2000ProfessionalEdition)初级教程摘要加载在微软公司(Microsoft®)的电子表格软件(Excel®)上的水晶球软件2000专业版(CrystalBall®2000ProfessionalEdition)是一个易于使用的软件。它可以帮助你分析与你的电子表格模型相关的风险和不确定性。这个软件包括蒙特卡洛模拟（水晶球）、时间序列预测（水晶球预言家）、最优选择（优化查询）和用来构造定制界面和程序的开发工具箱。由于电子表格缺乏设计和分析可选方案的能力，所以仅用电子表格来估算一个事件发生的概率是不合适的。而加载了水晶球软件的电子表格模型就能具备这样的功能，从而帮助用户洞察模型运行和结果产生的机制。本初级教程通过一个媒体产业的实例来演示蒙特卡洛模拟和时间序列预测工具如何用于一个电子表格模型，为商业决策的内在风险提供更深入的了解和度量。1序言1.1电子表格模型和风险分析风险就是不确定性，是指发生损失、危害和其它不愉快事件的可能性。大多数人偏好低风险，期待成功、收益或其它形式获利的较高概率。举例来说，如果下个月的销售超过一定数额（一种令人愉快的事件），那么这些订单会使存货减少，从而导致商品运送的延迟（一种令人不愉快的事件）。反过来，商品运送的延迟则会导致订单的流失。发生这种情况的可能性就代表了一种风险。当使用电子表格模型时，分析家习惯将那些本来不确定的变量用其平均值或其最佳估计值来输入。这是因为电子表格软件只允许他们在一个单元格内输入一个数值或一个公式。这些“确定的”模型只能产生一个结果。而模型结果将被用作商业或技术决策的根据。为了把握模型中本身存在的那些不确定性，分析家只能通过手工方式来改变模型变量，分析它们对关键结果的影响，来简单地实现“方案分析”。这种方法提供了可能结果的一定范围，但它无法使人得知那些特定结果出现的可能性。管理者经常需要知道那些不确定变量分别取什么值的时候，方案会达到一个最佳情况；或在什么时候，方案会达到一个最差情况；又在什么时候，方案又会达到一个最可能情况。那些包含不确定性因素的实际问题，绝大部分都是很复杂的。为了计算每一种可能发生的结果，就需要将所有输入变量的各种可选数值进行组合。对于复杂的实际问题，这种组合的数量是非常庞大的，很难一一实现。因此在实际问题中，这种办法很难奏效。1.2蒙特卡洛模拟为了解决电子表格模型在风险分析方面存在的局限性，蒙特卡洛模拟是一个现成的办法。由于电子表格软件本身不具有模拟运行和模拟分析的能力，所以用户必须依靠象水晶球软件这样一些加载在电子表格软件上的第三方程序，来扩充电子表格软件的功能。针对电子表格软件，水晶球软件增加了两项功能：一是用概率分布来替代单个数值，二是可以进行模型的随机模拟。通过水晶球软件，可以生成量化的基于概率的电子表格模型，例如可以得到“以75%的概率保证在预算内”或“某一水库装满一亿桶油的可能性有90%”这样的结果。9 蒙特卡洛模拟是一项已被确认正确并且行之有效的技术。它只要求计算机具有一个随机数表或一个随机数生成器。我们可以通过数学方法来产生服从某一概率分布的随机数。蒙特卡洛模拟是用一个城市的名字来命名的。这个城市就是摩纳哥的蒙特卡洛，它以充满各种冒险游戏的赌场而闻名，常见的赌博游戏有轮盘赌、投掷骰子、老虎机等。在蒙特卡洛模拟中，为了模拟一个模型，需要随机选择变量的取值，这与赌博中的随机现象很相似。当你投掷一个骰子时，你知道会出现1、2、3、4、5或6中的某一点，但你不知道具体某一次投掷时会出现哪一个点。与投掷骰子相同，随机变量（例如利率、需求量、股价、存货量、每分钟电话呼叫次数）的取值范围是确定的，但在具体某一时间或某一事件时，取到的值是不确定的。模拟可以产生大量的方案，通过分析这些方案，可以深入了解电子表格模型中蕴涵的风险和机理。只要使用得当，蒙特卡洛模拟就能展现非常有价值的视角，而这是确定性模型所得不到的。2水晶球软件2000专业版水晶球软件2000专业版是一个办公软件套件，以基于电子表格的分析工具为特色，包括蒙特卡洛模拟（水晶球）、时间序列预测（水晶球预言家）和最优选择（优化查询）。该套件还包括水晶球和水晶球预言家开发工具箱，采用VBA语言（VisualBasicforApplications）来构建用户界面和程序2.1水晶球水晶球是一个易于使用的电子表格插件，被设计用来帮助不同水平的Excel电子表格用户来进行蒙特卡洛模拟。水晶球可以让用户在不确定性模型变量上定义概率分布，然后通过模拟，在定义的可能范围内产生随机的数值。电子表格用户能产生和分析成千上万种可选的方案，量化任意给定方案的风险水平。水晶球可用于各种现成的或新建的电子表格模型，同时其提供的增强功能不会改变原有电子表格模型中的公式或函数。水晶球还包括一些水晶球工具，它由七个向导驱动的插件组成，可以帮助我们建立和分析模型。这些工具包括靴带分析（Bootstrap）、旋风图分析（TornadoAnalysis）、方案分析（ScenarioAnalysis）、二维模拟（2Dsimulation）和分批拟合（BatchFit）。2.2水晶球预言家水晶球预言家是一个向导驱动的Excel插件，通过时间序列预测来指引你的工作。水晶球预言家分析你的数据序列，并使用数据的水平、趋势、季节和误差来预测数据序列的未来值。水晶球预言家对数据采用八种不同的季节性和非季节性的时间序列预测方法，并当主要数据序列依赖于其它一些独立数据序列时，能采用多重线性回归。产生的预测结果可以被定义为水晶球的假设单元，并用于蒙特卡洛模拟。2.3优化查询优化查询是一个专为水晶球设计的全局优化插件。它通过自动搜索并找到模拟模型的最优解决方法，从而增强了水晶球的功能。优化查询采用多种技术的混合，包括分散搜寻和先进的禁忌搜寻方法，来找到决策变量的合适组合，以取得最佳的可能结果。当程序运行时，自适应和人工神经网络技术帮助它从过去的优化运算中自我学习，从而使其在更短时间内达到更好的结果。一个安装向导可以帮助用户定义约束条件、目标函数和要求，甚至设定一个有效的边界选项。本初级教程将不涉及优化查询的使用。2.4开发者工具箱9 通过VBA程序或任何其它Excel支持的外部语言，水晶球和水晶球预言家开发工具箱可以让用户完全自动运行和控制水晶球的模拟过程和水晶球预言家的预测过程。这个工具箱包括开启水晶球和水晶球预言家编程能力的宏指令与宏函数库。通过该工具箱，用户能自动运行多重模拟来测试不同假设单元的组合，整合水晶球和其它软件工具，生成TURNKEY应用来避免用户涉及程序的复杂机构，甚至构建定制的报告或完成模拟后的自动分析。本初级教程将不涉及开发工具箱的使用。3关于科罗拉多有线电视的实例本初级教程将展示模拟和预测是如何改进一个商业决策的。在这个实例中，将看到如何从一个历史数据序列进行预测、定义概率分布、运行蒙特卡洛模拟和进行模拟结果的分析和报告的。这里描述的实例可以从决策工程公司的网站（地址参见附件）上免费下载。在实例的最后，将会得到取胜的关键因子的统计分类、新产品底线状况的分析和如何得到一个清楚表达你的发现的报告。科罗拉多有线电视是一家本地有线电视供应商，正在考察一项被称作互动电视（ITV）的新技术。互动电视将根据需要提供内容——电影、体育比赛和新闻。科罗拉多有线电视相信本地观众会接受这项服务，但认为下滑的经济会导致一些无法预知的风险。管理层已要求对互动电视做一个从2004年到2009年的预测模型。他们想在制定该项重大投资决策之前，能更好地了解这种新技术的销售和市场潜力。互动电视是否真的具有足够的发展潜力来推向市场？六年之后该项目的净现值（NPV）会是多少呢？3.1建立模型，指定不确定因素图1：科罗拉多有线电视公司电子表格模型首先，需要建立一个有效的、可检验的决策模型，这个模型能尽可能地表示互动电视的销售方案。模型的建立需要团队合作，同时需要仔细收集数据和建立模型。这样，才能针对互动电视的市场潜力，建立一个现实的、基本的模型。该模型(图1)显示了科罗拉多有线电视的三个现有产品(有线电视、卫星电视和广播电视)的市场份额和规模，外加所期待的互动电视项目的市场份额和规模。9 然后，需要估计互动电视项目的收入和成本，并且得出一个六年期的净现值。基于上述决策估计，净现值的期望是5100万美元(其中年利率为10%)。但上述结果的可能性有多大呢？能产生出比5100万美元更大的净现值的概率是多少呢？等于、小于5100万美元的概率又是多少呢？水晶球预言家和水晶球将会帮助你回答这些问题。3.2启用水晶球12345678910图2：工具栏按钮：(1)定义假设(2)定义预测(3)运行属性(4)运行(5)停止(6)单步执行(7)预测窗口(8)灵敏度分析(9)生成报告(10)帮助模型设计和检验之后，可以通过视窗操作系统（Windows）的开始菜单打开水晶球和Excel软件。水晶球软件在Excel软件上加载了一行新的工具栏和三个新的菜单项。工具栏(图2)上的按钮从左到右以建模过程(设置、模拟、结果的分析和表达)来排列。最后一个按钮用作在线帮助。三个新增的菜单项包含了所有工具栏上的功能，另外还有一些其他功能。单元菜单包含了所有模型的设置功能。运行菜单包含了所有的模拟和分析功能。最后一个菜单是水晶球工具，用以打开专业版上的一些其它工具。3.3用历史数据预测家庭数2004年后，该地区的潜在客户（即那些有可能需要科罗拉多有线电视服务的家庭数）是不确定的。尽管实际增长速度比较难估计，但根据这一地区20年的历史数据，可粗略估计出每年新增的拥有电视的家庭数为50000户。因为该数据存在时间的成分，可以用水晶球预言家来得作更精确的时间序列预测，从而代替刚才粗略的估计值。为了运行水晶球预言家，选中计划要进行预测的数据序列中的任何单元格，通过水晶球工具菜单打开水晶球预言家程序。该程序前面的四个步骤可以帮助你定义、组织和观察数据。自相关性可用于判断数据是否具有某种季节性（显示出周期性变化）。本例中的历史数据是年度数据，不存在季节性，家庭数呈现增长的趋势。方法画廊（MethodGallery）以图画方式提供了八种时间序列预测方法，允许你选择其中任何一种，或选择全部方法来对你的数据作比较。这些方法被分成季节性和非季节性两种。双击每个方法的图画，可深入了解该方法的作用。方法画廊对每种方法都自动给出误差统计方法或参数，供你缺省使用。由于本例中的数据是单一、独立的，不需要进行多重线形回归。图3：已有家庭数的时间序列预测输入预测的时期（本例为年）为5，选择置信区间选项为5%和95%。在准备过程的任何时候，水晶球预言家都可以让你预先浏览预测结果（图3）。9 对于这组家庭数的数据，水晶球预言家自动选择二次指数平滑法作为最佳的预测方法。在这之前，水晶球预言家已经检查了这组历史数据，使用每一种非季节性方法来作拟合，挑出了拟合效果最佳、误差最小的方法，进而预测了未来五年的趋势，并计算了90%的置信区间。在预览窗口中，你可以选择输出图表和数据的格式。水晶球预言家能将预测值生成为一个正态分布。当你运行了预测之后，你可以将这些分布复制并粘贴到电子表格中家庭数预测单元格上去。通过如上更精确的时间序列预测，净现值增加到了5300万美元。3.4定义水晶球假设单元在水晶球中，概率分布被看作“假设单元”，是基本的输入量，被用来定义任何模型变量的不确定性。怎样识别那些需要从单个值变为概率分布的变量？最好的方法就是看看哪些变量是明确的，而哪些变量是“不硬”的或者说是不确定的。在这个模型中，市场占有率、首期投资额和每年的运行费用都是水晶球假设单元的主要候选对象。图4：分布画廊为了定义一个假设单元，先选中一个电子表格变量，再单击工具栏中的“定义假设”按钮。在分布画廊（图4）中，水晶球提供了16种预先定义好的分布和一种可由用户定制的分布。四种最常用的假设单元分别是正态分布、三角形分布、均匀分布和对数正态分布。水晶球软件还可以对已有数据作连续分布的拟合，并可以定义相关假设单元间的相关系数。在这个例子中，你可以根据掌握的知识和直觉来定义假设单元。当你对一个变量了解得越多，你定义的分布就越准确。3.4.1市场占有率图5：用三角形分布定义互动电视的市场份额2004年的市场占有率估计为2%，但这一数字也可能低到0%，或高到3%。单击定义假设按钮，选择三角形分布。对于最大值、最小值和最可能值已知的情况，用三角形分布来描述是非常好的。图5显示了如何对互动电视2004年市场占有率建立一个三角形分布的假设单元。如果获得的是百分位格式的数据，你也可以选择百分位法来定义该分布。9 同样，利用表1的数据，可将互动电视2005—2009年的市场占有率分别用三角形分布来定义。虽然你希望市场占有率每年都增长，但实际上不可能如基本模型预言的那样稳定增长。假设单元可以让你建立那种随时间推移的不确定性。表12005—2009年互动电视市场份额的不确定性年份最小值最可能值最大值20050%7%10%20060%10%15%20070%15%20%20080%20%30%20090%25%35%3.4.2首期投资额图6：用正态分布定义首期投资额正态分布，它的曲线形状象一口钟的外形，常用来表示那些自然生成的随机变量。正态分布随机变量存在一个最可能的值，左右对称，取值较集中在平均值附近。在基本模型中，首期投资额被估计为1亿美元。而真实的投资额会接近1亿美元这一平均值，但也有可能高到1.3亿美元，或低到7000万美元。平均值为1亿美元、标准差为1000万美元的正态分布能更准确地描述这一变量（图6）。3.4.3年度运行成本图7：采用单元格引用方式的正态分布定义2004年的运行费用最后还有六年中每年的运行成本。在基本模型中，预计每年成本以100万美元增加。利用水晶球软件，可以将这些变量定义为正态分布，它们的标准差为100万美元。而每个正态分布的平均值，可通过单元格引用的办法来直接包含电子表格中的相应数据（图7）。9 3.5定义一个预测单元图8：定义一个预测单元在定义完所有的输入变量（假设单元）后，你需要定义输出变量，输出变量在水晶球中称之为“预测单元”。预测单元是你指派水晶球软件在模拟过程中跟踪的一个公式单元。像假设单元一样，可以定义的预测变量的个数是没有限制的，当然个数越多就会消耗越多的内存。本模型只有一个预测单元，即净现值（NPV）。选中该单元格，单击预测单元定义按钮，打开预测单元定义对话框（图8），输入一个唯一的名字，然后点击OK按钮即可。3.6运行蒙特卡洛模拟3.6.1设置运行属性水晶球软件可以让你对模拟属性进行全面的控制。可以设置的运行属性包括：运行的试验次数、使用什么样抽样方法、在模拟循环中的任何时刻和地点运行宏、选择提高模拟速度的方式以及精度控制。精度控制是一种置信度检验的方法，是在模拟统计的基础上让预测的准确性达到所需的水平。抽样方法有两种：蒙特卡洛抽样和拉丁超立方抽样。使用蒙特卡洛抽样方法，产生的随机数相互之间是完全独立的。而使用拉丁超立方抽样方法，水晶球会把假设单元的概率分布分成等概率的几个区间，区间的个数由你自己定义，在模拟中为每一区间产生一系列的随机数。本例的蒙特卡洛模拟进行2000次试验。3.6.2检验和运行模拟在开始模拟之前，你可以通过单击工具栏中的单步运行按钮来检查模型是否正确。在水晶球软件中，每一步表示一次随机试验。在每一步中，水晶球软件为每一个假设单元产生一个随机数，Excel会自动重新计算模型。当模型为可选方案进行重新计算后，你可以通过检验来发现是否有计算错误。在模拟中，水晶球软件按照你要求的次数来进行大量的试验，同时将预测值保存起来，留作后面的分析用。要运行模拟，首先单击工具栏中的开始模拟按钮。当模拟开始时，净现值（NPV）预测（或频率）图表就被建立了起来，当水晶球把随机产生的值（来自概率分布）嵌入假设单元时，电子表格中的值也会同时变化。3.7预测图表图9：净现值的预测图表9 2000次试验完成之后，可以使用预测图表来分析这些结果。预测图表是一个包含所有2000次试验的统计数据的交互式直方图。原先预计的5100万美元出现的可能性有多少呢？根据模拟结果（图9），只有大约8.8%的把握达到或超过你原先估计的净现值。达到5300万美元（从家庭数历史数据作预测后计算出的净现值）的概率会更小。另外，在图表左范围框中输入0，可以得到大约有60%机会能做到不亏。图10：净现值预测单元的统计数据视图统计数据视图（图10）能帮助你得到更多的信息。从峰度（Skewness）和偏度(Kurtosis)的数值可以看出，模拟结果比较接近正态分布。其标准差是330多万美元，极差是2亿3000万美元。极差为什么有这么大呢？水晶球软件的敏感度分析能找出引起结果变化的关键因素。3.8灵敏度分析当模拟运行时，灵敏度分析使用等级系数法来动态计算13个假设单元对预测单元的影响。灵敏度图表将这些影响表示为相关系数或百分比值的形式。列在图表最上面的假设单元对净现值的影响最大，条形图的方向表明该影响是正影响还是负影响。图11：净现值的灵敏度分析图表在本例中，2006年到2009年的市场份额对净现值的影响最大（图11）。在这项研究中，最后几年的市场份额取得比较大（表1）。这样的假设合理吗？如果缩小这些分布的范围，重新运行该模拟会发生怎样的情况呢？通过提出上述这些问题，你就会明白，模拟和灵敏度分析可以如何帮助你关注模型中起最重要作用的那些因素。模拟建模是一个不断反复的过程，开始的结果可能显示互动电视项目不会达到预期的效果，但通过对模型中起关键作用的因素的了解，能帮助你改进该模型，并将内部风险转移出去。3.9保存结果9 当对模型和模拟的结果感到满意后，你有两种方式将其保存成文件。第一种方法是将模拟结果、图表和变量生成一个可打印的报告。这个包含Excel图表的报告文件可以保存为一个新的工作表，也可以保存为与模型同一工作簿的一个新工作表。另外，也可以摘录一些原始预测数据，诸如预测值、统计数据、百分比值、频数，将其保存到一个新的工作表中，或者将其保存到与模型同一工作簿的一个新工作表中。4结论对未来赢利状况进行模拟分析，可以帮助用户减少风险，大大提高决策的质量。水晶球软件2000专业版在Excel中增加一些非常容易使用的工具，其中包括蒙特卡洛模拟、全局优化和时间序列预测，从而克服了电子表格的局限性。把概率论方法引入到电子表格预测中，分析家可以更好地量化模型的内在风险，并得到更多的灵感，而这是传统定性方法所不能得到的。附录：水晶球资源本初级教程中描述的模型可以在决策工程公司网站的模型范例库<网址：www.crystalball.com/models/model_index.html>中免费获得。该网站还包括论文、教程、案例研究以及互联网链接等一些其它免费资源。另外，还可以在该网站免费下载该软件及其教程的试用版本。9