论文部分内容阅读
［摘 要］ 蒙特卡罗模拟法是进行项目不确定性分析的一种常用方法，Excel是具有强大数据分析功能的软件，可以很好地解决手工计算烦琐的问题。基于此，本文结合案例研究了如何运用Excel进行蒙特卡罗模拟分析。
［关键词］ Excel；蒙特卡罗模拟；数据分析
［中图分类号］F232；F275［文献标识码］A［文章编号］1673-0194（2008）07-0044-04
一、蒙特卡罗模拟分析概述
在工程项目的经济评价中，通常假定有关数据都是不变的。但项目分析中采用的数据大部分来自预测或估计，它们在很大程度上受到未来可变因素的影响；如果把它们作为固定值看待进行分析，计算结果将与实际情况不符，从而带来某种程度的风险。近年来的经济评价中越来越注意分析研究这种风险的程度和可能性，蒙特卡罗模拟法就是解决这类问题十分方便的方法。
采用蒙特卡罗模拟法可以直接从不确定性因素变化情况入手，通过建立模型直接模拟问题，从而使不确定性因素对项目经济效果的影响直接反映在对项目经济评价指标的计算中。由于采用了随机抽样获取不确定性因素的大量观测值，将其代入模型中进行模拟分析，不仅可以获得评价指标的概率分布信息，还可以从指标的离散程度、变异系数等方面来评价项目的风险；同时可以通过事先设定置信度来控制模拟的准确度，所得的信息完整性远远高于其他分析方法。
二、利用Excel进行蒙特卡罗模拟分析的优势
蒙特卡罗模拟技术的应用是与计算机技术的发展密不可分的，只有借助计算机高效、便捷的计算功能，蒙特卡罗模拟在实践中的应用才能成为可能。经过最近十几年计算机软硬件技术的飞速发展，Excel软件已成为大众首选的决策分析工具软件。在它的帮助下，管理人员完全可以根据实际问题，在相应的理论基础上，自己动手建立计算模型并对这些模型进行各种深入的分析，特别是对于蒙特卡罗模拟这类需要建立在一定模型基础上进行分析的方法来说，Excel 在建模、数据分析等方面的优势更是得到了充分的体现和发挥：
第一，Excel具有十分强大的数据分析功能，并且与各种数据库具有良好的接口，这使得操作者可以通过简单的操作从具有不同结构的数据库中获取所需要的数据，利用数据分析功能直接进行一些复杂的计算、统计工作等。
第二，Excel具有大量内建函数，操作者可以利用Excel提供的各种函数公式，通过确定参数的数值，按特定的顺序或结构自动执行复杂计算，大大简化了计算模型建立的过程，并有助于提高模型的准确性，特别是Excel的财务函数、统计函数等对于财务分析相关模型的建立有很大的帮助。
第三，Excel还具有良好的图形显示能力，将这种功能与其他功能相结合，决策者可以在决策模型的基础上经过简单的操作制作高质量的动态图形，将抽象的决策模型与直观生动的图形完美结合，从而以最有效的方法帮助决策者进行各种必要的决策分析。
三、利用Excel进行蒙特卡罗模拟分析的实施
利用Excel进行蒙特卡罗模拟分析首先应根据待解决问题的具体情况，分析影响项目经济效果的不确定性因素，确定与决策相关的目标变量与随机变量；然后根据变量分析结果在电子表格中建立经济评价指标的计算模型，同时根据项目情况预测随机变量概率分布；通过计算机程序生成符合特定分布随机变量的观测值，并将其代入模型中进行模拟计算，从而获得目标变量大量的观测值；计算目标变量各种统计指标并收集频数分布，生成直方图，对模拟结果进行解释。利用Excel进行蒙特卡罗模拟的具体步骤，如图1所示。
在利用Excel进行蒙特卡罗模拟的过程中，应注意以下几个方面：
1. 随机变量概率分布的选择
对既定分布的随机变量进行随机抽样产生随机变量观测值在蒙特卡罗模拟法中占有非常重要的地位，选择确定随机变量的概率分布类型，才能利用计算机产生符合特定概率分布的随机数，进而才能利用随机数运行模型进行模拟分析。
对于进行模拟分析的许多项目来说，大都可以利用经验数据来选择模型中随机变量概率分布的类型。无论是历史数据，还是努力收集的其他数据，都可以通过这些数据建立直方图，从考察直方图入手，寻求特定分布的特有形状，从而确定一个经验分布。
2. 特定分布随机数的产生
从概率分布中得到样本的基础在于随机数这一概念，生成符合概率分布的随机数是蒙特卡罗模拟的关键步骤之一。
可以利用Excel 内建函数生成特定分布随机数，如可以利用Excel的 RAND（）函数生成一个在（0，1）区间中均匀分布的随机数，也可以利用RANDBETWEEN（a，b）函数生成一个在[a，b]区间中取整数值的均匀分布随机数。
3. 模拟次数的选择
模拟的重复次数直接影响模拟结果的质量，通常，重复的次数越多，对输出目标变量各项统计特征的描述就越精确，对于模拟次数的确定，需要运用统计中有关样本容量的知识。有时也会根据历史经验取一个数值。
4. 模拟运行结果的记录
在进行蒙特卡罗模拟分析时有一个关键问题必须解决，即将在各个变量或参数不同随机值条件下多次运行模型所产生的目标变量的相应取值记录下来，这些目标变量值正好就是进行模拟结果分析的数据来源。
5. 模拟结果的分析
实现蒙特卡罗模拟分析还有一个非常重要的问题就是对模拟运行结果进行统计分析，内容包括样本均值、样本标准差等在内的各种描述性统计指标的计算，此外，还应当分析样本数据的频数分布，根据样本数据绘制直方图，以及求出数据的某些具有特殊意义的概率值，如投资项目中净现值大于零、内部收益率大于基准收益率的概率值等。
（1）主要统计指标计算方法。
①利用Excel内建函数计算统计指标。Excel内建的统计函数提供了数据统计指标计算的功能，如使用VAR（data range）计算样本方差、STDEV（data range） 计算样本均值、CORREL （array1，array2） 计算相关系数等。
②利用Excel数据分析功能计算统计指标。首先需要选择Excel“工具”菜单下的“数据分析”项，在其对话框中选中“描述统计”项，然后屏幕将出现一个描述统计对话框，选中对话框中的相关输入输出信息，就可以得到相应的统计结果。
利用数据分析功能可以生成一个完整的描述统计量列表，但是利用内建函数计算统计量较为灵活方便，更值得一提的是Excel的自动重算功能，只要数据发生变化，所返回的统计值量也会发生相应的变化，从而使数据与统计量之间保持着动态的联系。
（2）利用Excel的Frequency函数建立直方图。在进行操作之前，需要先确定待分析数据的极大值与极小值，以此为参照来规定待分析数据区间的划分宽度，然后在工作表的一列范围内，按升序键入作为划分区间分界点的分格数据，也就是接收数据。
接收数据确定后，就将一个与接收数据范围具有相同高度的一列单元格选黑，并键入公式Frequency（Data-array，Bins-array），将被分析的样本数据所在范围与接收数据所在范围的单元格引用分别作为该函数的第一和第二个参数，然后按“Ctrl Shift Enter”组合键，这样在选黑的单元格范围内便可得到样本出现在各个子区间的频数了。
创建了频数分布以后，就可以利用Excel柱形图的图表向导创建直方图了。这样，频数分布和数据建立了联系，由此生成的样本数据直方图就是可以随样本数据变化而自动更新的“活”的直方图了。
四、案例分析
1. NPV（净现值）计算函数简介
Excel的内建函数具有强大的数据计算和数据分析功能，特别是NPV（净现值）计算函数对于研究利用 Excel实现蒙特卡罗模拟在财务评价上的应用有很大帮助。
NPV函数表达式为：NPV（Rate，Value1，Value2，…）
式中，Rate表示各期现金流折算成当前值的贴现率，它的取值在各期中应是固定不变的，Valuel，Value2，…代表支出或收入的1~29个参数，它们在时间上必须具有相等间隔，并且都发生在期末。NPV函数中Value指定的是每一周期期末发生的现金流，如果第一个周期的期初有另外的现金流，那么在计算时，需要将这一现金流加到NPV函数返回值中，这样才能计算出正确的净现值。
2. 模拟变量构成分析
不管是对净现值的预测，还是对内部收益率以及投资回收期的预测，都与项目计算期内各年净现金流量有密不可分的关系，所以应当从项目的净现金流量从手，分析净现金流量的构成要素及其计算模型。各年的现金流量Yt的计算公式为：Y0 =投资（负值），Yt =（R-C-Dt）（1-Te） Dt （t =1，2，…，n）。其中，R表示年收入，C表示年支出，Te表示税率，采用直线折旧法进行，即折旧Dt =（投资-残值）/寿命期（t =1，2，…，n）。
3. 模拟变量构成及概率分布
某投资项目期投资、寿命期、残值以及各年的收入、支出，以及应付税金的税率、项目的资本成本等都是独立的随机变量，概率密度函数如表1所示。
表1 模拟变量构成及概率分布
（2）得到各独立变量的模拟值，下面仍以投资为例对模拟的过程进行说明。
首先使用RANDBETWEEN（0，99）函数在A31:A5030区域产生5 000个随机数，然后使用VLOOKUP（）函数搜索表格首列满足条件的元素，确定待检查单元格在区域中的行序号，再进一步返回选定单元格的值。例如，在A31产生一个随机数61，然后使用VLOOKUP（A31，$C$3:$D$5，2）就可以找到随机数对应的投资额550。
将各独立变量进行模拟后，运用净现金流量的计算公式，得到其模拟值，然后运用净现值计算公式NPV，在Q31:Q5030区域得到净现值的模拟值。
5. 运用Excel函数计算净现值模拟运算结果（见表3）
表3 净现值模拟计算结果
五、结 论
利用 Excel 程序可以完成对项目经济效果的蒙特卡罗模拟分析，获得经济评价指标的各项统计信息，不仅可以从经济评价指标高于或低于基准指标的概率上来分析项目的可行性，还可以从指标的变异系数、离散程度等方面来分析项目的风险情况。因此利用Excel对项目经济效果进行蒙特卡罗模拟分析所得的信息比较全面、科学，根据这些信息投资者可以更加科学、合理地判断项目投资的风险性。
主要参考文献
［1］ 刘清志. 石油技术经济学［M］. 东营：中国石油大学出版社，1998.
［2］ ［美］ 戴维·R ·安德森，丹尼斯·J ·斯威尼.数据模型与决策［M］. 于淼译. 北京：机械工业出版社，2003.
［3］ ［美］肯·布莱克等. 以Excel为决策工具的商务与经济统计［M］.北京：机械工业出版社，2003.
［4］ 张瑞君. 计算机财务管理［M］. 北京：中国人民大学出版社，2005.
［5］ 韩良智. Excel在财务管理与分析中的应用［M］. 北京：中国水利水电出版社，2004.
［关键词］ Excel；蒙特卡罗模拟；数据分析
［中图分类号］F232；F275［文献标识码］A［文章编号］1673-0194（2008）07-0044-04
一、蒙特卡罗模拟分析概述
在工程项目的经济评价中，通常假定有关数据都是不变的。但项目分析中采用的数据大部分来自预测或估计，它们在很大程度上受到未来可变因素的影响；如果把它们作为固定值看待进行分析，计算结果将与实际情况不符，从而带来某种程度的风险。近年来的经济评价中越来越注意分析研究这种风险的程度和可能性，蒙特卡罗模拟法就是解决这类问题十分方便的方法。
采用蒙特卡罗模拟法可以直接从不确定性因素变化情况入手，通过建立模型直接模拟问题，从而使不确定性因素对项目经济效果的影响直接反映在对项目经济评价指标的计算中。由于采用了随机抽样获取不确定性因素的大量观测值，将其代入模型中进行模拟分析，不仅可以获得评价指标的概率分布信息，还可以从指标的离散程度、变异系数等方面来评价项目的风险；同时可以通过事先设定置信度来控制模拟的准确度，所得的信息完整性远远高于其他分析方法。
二、利用Excel进行蒙特卡罗模拟分析的优势
蒙特卡罗模拟技术的应用是与计算机技术的发展密不可分的，只有借助计算机高效、便捷的计算功能，蒙特卡罗模拟在实践中的应用才能成为可能。经过最近十几年计算机软硬件技术的飞速发展，Excel软件已成为大众首选的决策分析工具软件。在它的帮助下，管理人员完全可以根据实际问题，在相应的理论基础上，自己动手建立计算模型并对这些模型进行各种深入的分析，特别是对于蒙特卡罗模拟这类需要建立在一定模型基础上进行分析的方法来说，Excel 在建模、数据分析等方面的优势更是得到了充分的体现和发挥：
第一，Excel具有十分强大的数据分析功能，并且与各种数据库具有良好的接口，这使得操作者可以通过简单的操作从具有不同结构的数据库中获取所需要的数据，利用数据分析功能直接进行一些复杂的计算、统计工作等。
第二，Excel具有大量内建函数，操作者可以利用Excel提供的各种函数公式，通过确定参数的数值，按特定的顺序或结构自动执行复杂计算，大大简化了计算模型建立的过程，并有助于提高模型的准确性，特别是Excel的财务函数、统计函数等对于财务分析相关模型的建立有很大的帮助。
第三，Excel还具有良好的图形显示能力，将这种功能与其他功能相结合，决策者可以在决策模型的基础上经过简单的操作制作高质量的动态图形，将抽象的决策模型与直观生动的图形完美结合，从而以最有效的方法帮助决策者进行各种必要的决策分析。
三、利用Excel进行蒙特卡罗模拟分析的实施
利用Excel进行蒙特卡罗模拟分析首先应根据待解决问题的具体情况，分析影响项目经济效果的不确定性因素，确定与决策相关的目标变量与随机变量；然后根据变量分析结果在电子表格中建立经济评价指标的计算模型，同时根据项目情况预测随机变量概率分布；通过计算机程序生成符合特定分布随机变量的观测值，并将其代入模型中进行模拟计算，从而获得目标变量大量的观测值；计算目标变量各种统计指标并收集频数分布，生成直方图，对模拟结果进行解释。利用Excel进行蒙特卡罗模拟的具体步骤，如图1所示。
在利用Excel进行蒙特卡罗模拟的过程中，应注意以下几个方面：
1. 随机变量概率分布的选择
对既定分布的随机变量进行随机抽样产生随机变量观测值在蒙特卡罗模拟法中占有非常重要的地位，选择确定随机变量的概率分布类型，才能利用计算机产生符合特定概率分布的随机数，进而才能利用随机数运行模型进行模拟分析。
对于进行模拟分析的许多项目来说，大都可以利用经验数据来选择模型中随机变量概率分布的类型。无论是历史数据，还是努力收集的其他数据，都可以通过这些数据建立直方图，从考察直方图入手，寻求特定分布的特有形状，从而确定一个经验分布。
2. 特定分布随机数的产生
从概率分布中得到样本的基础在于随机数这一概念，生成符合概率分布的随机数是蒙特卡罗模拟的关键步骤之一。
可以利用Excel 内建函数生成特定分布随机数，如可以利用Excel的 RAND（）函数生成一个在（0，1）区间中均匀分布的随机数，也可以利用RANDBETWEEN（a，b）函数生成一个在[a，b]区间中取整数值的均匀分布随机数。
3. 模拟次数的选择
模拟的重复次数直接影响模拟结果的质量，通常，重复的次数越多，对输出目标变量各项统计特征的描述就越精确，对于模拟次数的确定，需要运用统计中有关样本容量的知识。有时也会根据历史经验取一个数值。
4. 模拟运行结果的记录
在进行蒙特卡罗模拟分析时有一个关键问题必须解决，即将在各个变量或参数不同随机值条件下多次运行模型所产生的目标变量的相应取值记录下来，这些目标变量值正好就是进行模拟结果分析的数据来源。
5. 模拟结果的分析
实现蒙特卡罗模拟分析还有一个非常重要的问题就是对模拟运行结果进行统计分析，内容包括样本均值、样本标准差等在内的各种描述性统计指标的计算，此外，还应当分析样本数据的频数分布，根据样本数据绘制直方图，以及求出数据的某些具有特殊意义的概率值，如投资项目中净现值大于零、内部收益率大于基准收益率的概率值等。
（1）主要统计指标计算方法。
①利用Excel内建函数计算统计指标。Excel内建的统计函数提供了数据统计指标计算的功能，如使用VAR（data range）计算样本方差、STDEV（data range） 计算样本均值、CORREL （array1，array2） 计算相关系数等。
②利用Excel数据分析功能计算统计指标。首先需要选择Excel“工具”菜单下的“数据分析”项，在其对话框中选中“描述统计”项，然后屏幕将出现一个描述统计对话框，选中对话框中的相关输入输出信息，就可以得到相应的统计结果。
利用数据分析功能可以生成一个完整的描述统计量列表，但是利用内建函数计算统计量较为灵活方便，更值得一提的是Excel的自动重算功能，只要数据发生变化，所返回的统计值量也会发生相应的变化，从而使数据与统计量之间保持着动态的联系。
（2）利用Excel的Frequency函数建立直方图。在进行操作之前，需要先确定待分析数据的极大值与极小值，以此为参照来规定待分析数据区间的划分宽度，然后在工作表的一列范围内，按升序键入作为划分区间分界点的分格数据，也就是接收数据。
接收数据确定后，就将一个与接收数据范围具有相同高度的一列单元格选黑，并键入公式Frequency（Data-array，Bins-array），将被分析的样本数据所在范围与接收数据所在范围的单元格引用分别作为该函数的第一和第二个参数，然后按“Ctrl Shift Enter”组合键，这样在选黑的单元格范围内便可得到样本出现在各个子区间的频数了。
创建了频数分布以后，就可以利用Excel柱形图的图表向导创建直方图了。这样，频数分布和数据建立了联系，由此生成的样本数据直方图就是可以随样本数据变化而自动更新的“活”的直方图了。
四、案例分析
1. NPV（净现值）计算函数简介
Excel的内建函数具有强大的数据计算和数据分析功能，特别是NPV（净现值）计算函数对于研究利用 Excel实现蒙特卡罗模拟在财务评价上的应用有很大帮助。
NPV函数表达式为：NPV（Rate，Value1，Value2，…）
式中，Rate表示各期现金流折算成当前值的贴现率，它的取值在各期中应是固定不变的，Valuel，Value2，…代表支出或收入的1~29个参数，它们在时间上必须具有相等间隔，并且都发生在期末。NPV函数中Value指定的是每一周期期末发生的现金流，如果第一个周期的期初有另外的现金流，那么在计算时，需要将这一现金流加到NPV函数返回值中，这样才能计算出正确的净现值。
2. 模拟变量构成分析
不管是对净现值的预测，还是对内部收益率以及投资回收期的预测，都与项目计算期内各年净现金流量有密不可分的关系，所以应当从项目的净现金流量从手，分析净现金流量的构成要素及其计算模型。各年的现金流量Yt的计算公式为：Y0 =投资（负值），Yt =（R-C-Dt）（1-Te） Dt （t =1，2，…，n）。其中，R表示年收入，C表示年支出，Te表示税率，采用直线折旧法进行，即折旧Dt =（投资-残值）/寿命期（t =1，2，…，n）。
3. 模拟变量构成及概率分布
某投资项目期投资、寿命期、残值以及各年的收入、支出，以及应付税金的税率、项目的资本成本等都是独立的随机变量，概率密度函数如表1所示。
表1 模拟变量构成及概率分布
（2）得到各独立变量的模拟值，下面仍以投资为例对模拟的过程进行说明。
首先使用RANDBETWEEN（0，99）函数在A31:A5030区域产生5 000个随机数，然后使用VLOOKUP（）函数搜索表格首列满足条件的元素，确定待检查单元格在区域中的行序号，再进一步返回选定单元格的值。例如，在A31产生一个随机数61，然后使用VLOOKUP（A31，$C$3:$D$5，2）就可以找到随机数对应的投资额550。
将各独立变量进行模拟后，运用净现金流量的计算公式，得到其模拟值，然后运用净现值计算公式NPV，在Q31:Q5030区域得到净现值的模拟值。
5. 运用Excel函数计算净现值模拟运算结果（见表3）
表3 净现值模拟计算结果
五、结 论
利用 Excel 程序可以完成对项目经济效果的蒙特卡罗模拟分析，获得经济评价指标的各项统计信息，不仅可以从经济评价指标高于或低于基准指标的概率上来分析项目的可行性，还可以从指标的变异系数、离散程度等方面来分析项目的风险情况。因此利用Excel对项目经济效果进行蒙特卡罗模拟分析所得的信息比较全面、科学，根据这些信息投资者可以更加科学、合理地判断项目投资的风险性。
主要参考文献
［1］ 刘清志. 石油技术经济学［M］. 东营：中国石油大学出版社，1998.
［2］ ［美］ 戴维·R ·安德森，丹尼斯·J ·斯威尼.数据模型与决策［M］. 于淼译. 北京：机械工业出版社，2003.
［3］ ［美］肯·布莱克等. 以Excel为决策工具的商务与经济统计［M］.北京：机械工业出版社，2003.
［4］ 张瑞君. 计算机财务管理［M］. 北京：中国人民大学出版社，2005.
［5］ 韩良智. Excel在财务管理与分析中的应用［M］. 北京：中国水利水电出版社，2004.