excel在投资项目不确定性风险分析中的应用

第 8 章 Excel 在投资项目不确定性风险分析中的应用8.1 盈亏平衡分析(1)盈亏平衡分析的原理就是根据量本利之间的关系，计算项目的盈亏平衡点的销售量，从而分析项目对市场需求变化的适应能力。一般来说，盈亏平衡点是指企业既不亏又不盈或营业利润为零时的销售量。根据是否考虑资金的时间价值，盈亏平衡分析又可分为静态盈亏平衡分析和动态盈亏平衡分析。8.1.1 静态盈亏平衡分析静态盈亏平衡分析是在不考虑资金的时间价值情况下，对投资项目的盈亏平衡进行分析。当某年的营业利润为零时，可以得到该年盈亏平衡点的销售量为（这里假设只有一种产品）：式中，Q t 为第 t 年的盈亏平衡点销售量（又称保本销售量） ；F t 为第 t 年的固定成本，这里假设非付现固定成本只有折旧，即 Ft = Dt + Fc，D t 为第 t 年的折旧；F c 为付现固定成本；p 为产品单价；v 为产品的单位变动成本，并假设各年的付现固定成本、产品单价和产品的单位变动成本均不变。当产销量低于盈亏平衡点销售量时，投资项目处于亏损状态，反之，当产销量超过盈亏平衡点销售量时，项目就有了盈利。当企业在盈亏平衡点附近经营，即销售量接近于 Qt 时，投资项目的经营风险很大，或经营上的安全程度很低，销售量微小的下降都可能使企业发生亏损。单一产品的盈亏平衡分析比较简单。根据给定的各年的付现固定成本、折旧、产品单价和单位变动成本，即可由上述公式计算出各年的静态保本销售量。当一个投资项目同时生产多种不同的产品，或对一个生产多种产品的整个企业进行盈亏平衡分析时，则需要考虑多品种产品的情况。在进行多品种盈亏平衡分析时，加权平均法是较常用的一种方法。【例 8-1】某企业生产 A、B 、C 三种产品，A 产品年销售量 100000 件，单价 10 元/ 件，单位变动成本 8.5 元/件；B 产品年销售量 25000 台，单价 20 元/台，单位变动成本 16 元/台；C 产品年销售量 10000 套，单价 50 元/ 套，单位变动成本 25 元/套；全厂固定成本 300000元。根据以上资料，可以建立分析表格如图 8-1 所示。有关计算分析公式如下：销售收入=销售量单价边际贡献=销售量（单价单位变动成本）边际贡献率=边际贡献销售收入销售比重=某产品销售收入全厂各产品销售收入合计全厂综合边际贡献率=某产品边际贡献率该产品销售比重全厂综合保本额=全厂固定成本全厂综合边际贡献率某产品保本额=全厂综合保本额该产品销售比重某产品保本量=某产品保本额该产品单价图 8-1 某企业的多品种盈亏平衡分析输入已知数据及定义完公式后，即可马上计算出各个可变单元格的数值来，即全厂综合保本额 1200000 元，产品 A、B、C 的保本额分别为 600000 元、300000 元和 300000 元，保本量分别为 60000 件、15000 台和 6000 套。各单元格的计算公式为单元格 E3:E5 ：“= B3:B5*C3:C5”（数组公式输入） 。单元格 F3:F5 ：“=B3 :B5*(C3:C5-D3:D5)”（数组公式输入） 。单元格 G3:G6 ：“=F3: F6/E3:E6”（数组公式输入） 。单元格 E6：“=SUM(E3 :E5)”。单元格 F6：“=SUM(F3 :F5)”。单元格 B8:B10 ：“=E3:E5/E6” （数组公式输入） 。单元格 D8:D10 ：“=B8:B10*D11 ”（数组公式输入） 。单元格 E8:E10 ：“=D8 :D10/C3:C5”（数组公式输入） 。单元格 D11 ：“=H6/G6 ”。图 8-1 建立了各产品的单价、单位变动成本和固定成本与保本额或保本量之间的关系，利用图 8-1 就可分析它们对盈亏平衡点的影响。8.1.2 动态盈亏平衡分析8.1.2.1 独立项目的动态盈亏平衡分析静态盈亏平衡分析没有考虑资金的时间价值、所得税、利率，以及通货膨胀等因素的影响，由此计算出的盈亏平衡点销售量仅仅能使项目的当期达到盈亏平衡，却并不能保证项目的净现值恰好为零。在考虑资金的时间价值和所得税等因素的条件下，项目的动态盈亏平衡点就是项目净现值为零的那一点，即动态保本销售量就是使项目净现值为零的销售量。考虑单一产品的情况，令 NPV=0，则可得到项目各年的动态保本销售量的计算公式为式中，T 为所得税税率；Q t 为各年的保本销售量；p t、v t 和 Fct 分别为各年的产品单价、单位变动成本、付现固定成本；i 为项目的基准收益率；I 为初始投资（并假设在第 0 年一次性投入项目） 。这样，利用上述公式，即可分析各种情况下项目各年保本销售量的变化情况。【例 8-2】某企业准备投资生产一种新产品，项目总投资 350 万元，项目寿命期 5 年，期末无残值，采用直线法计提折旧。经预测，项目投产后每年可销售产品 85000 台，产品单价 40 元/台，单位变动成本 20 元/ 台，年付现固定成本 50 万元，企业的基准收益率为10%，所得税税率 33%。此时，各年的折旧额相同，假设各年的销售量、产品单价、单位变动成本、付现固定成本以及折旧均相同，可以利用上述公式直接导出动态盈亏平衡销售量（保本销售量）的计算公式如下：根据此公式计算出保本销售量为 76664 件，如图 8-2 所示，单元格 E4 中的保本销售量计算公式为：“=(E3+(B3/PV(F3,B3,-1)-G3)*A3/B3/(1-G3)/(C3-D3)*10000”。图 8-2 投资项目的动态盈亏平衡分析但实际上，各年的销售量受到企业内部及市场条件的影响，它们是不同的，故可以利用规划求解工具来计算各年的保本销售量，步骤如下：（1）如图 8-2 所示，将单元格 C10:G10 作为可变单元格，存放各年的保本销售量。（2）在单元格 B7 中输入公式 “= -A3”，在单元格 C7:G7 中输入净现金流量计算公式“=(C9:G9/10000*(C3-D3)-E3)*(1-G3)+SLN(A3,H3,B3)*G3”（数组公式输入） 。（3）在单元格 H7 中输入净现值计算公式 “=NPV(F3,C7:G7)+B7”。（4）单击工具菜单中的【规划求解】项，出现【规划求解参数】对话框，其中【设置目标单元格】选“$H$7” ， 【等于 】选“0” ， 【可变单元格】选“$C$9:$G$9 ”；单击【求解】按钮，即得各年的保本销售量，如图 8-2 所示。当可变单元格 C9:G9 中赋予不同的初始值时，会得到不同的保本销售量,如图 8-2 所示） ，各年的销售量变化范围很大，这正反映了动态盈亏平衡的特点。实际上，各年保本销售量组合有无数个，故为求得符合实际情况的保本销售量组合，还要考虑一些约束条件，比如各年的会计利润应大于零，各年取得的现金收入应能够偿还各年到期的债务以及支付股利，等等。8.1.2.2 互斥项目的动态盈亏平衡分析在需要对若干个方案进行比较的情况下，若是某一个共有的不确定性因素（比如销售量、产量、寿命、产品价格、单位变动成本等）影响这些方案的取舍，则可以利用盈亏平衡分析帮助决策。【例 8-3】某企业投资生产某种产品，现有两个方案，有关资料如图 8-3 所示，该产品的市场寿命具有较大的不确定性，如果基准收益率为 15%，不考虑期末资产残值，那么，企业应如何根据项目的寿命期来选择方案？图 8-3 根据方案寿命期选择项目如图 8-3 所示，进行决策分析的步骤如下：（1）首先在单元格 F3 中输入“5” ，在单元格 H3 中输入“11” ，单元格 G3 可先空置。（2）选取单元格区域 F4:H5，输入不同寿命期下两个方案的净现值计算公式“=PV(D4:D5,F3:H3,-C4:C5)-B4:B5”（数组公式输入） 。（3）在单元格 G6 中输入公式 “=G4-G5”，将单元格 G6 作为目标单元格，将单元格 G3 作为可变单元格，利用【规划求解】工具或【单变量求解】工具，可以求得两个方案净现值相等时的项目寿命期。由计算结果可以看出，两个方案净现值相等时的寿命期为 9.92 年，这就是以项目寿命期为共有变量时方案 1 与方案 2 的净现值无差异点。因此，当寿命期小于 9.92 年时，应采用方案 1；而当寿命期大于 9.92 年时，应采用方案 2。8.1.3 投资项目盈亏平衡分析模型除了前面介绍的直接利用公式计算盈亏平衡点保本销售量或设计工作表格进行分析外，我们还可以设计盈亏平衡分析模型来分析每个因素各种可能的变动情况下对保本销售量的影响。【例 8-4】投资项目的盈亏平衡分析模型如图 8-4 所示，具体设计步骤如下：图 8-4 投资项目盈亏平衡分析模型（1）首先设计好分析模型结构，如图 8-4 所示（表中数据以例 8-2 为例） 。（2）单击【视图】 ，选择【工具栏】 ，再单击【窗体】 ，出现【窗体】工具栏，如图 8-5 所示，单击【滚动条】按钮，然后在工作表的合适位置（这里为 E4F4 单元格）拖曳出一个矩形【组合框】控件，并调整其大小。图 8-5 【窗体】工具栏（3）将鼠标移到新建立的【滚动条】控件上，单击鼠标右键，出现快捷菜单，选择【设置控件格式】 ，出现【设置控件格式】对话框，选择【控制】项，如图 8-6 所示。图 8-6 【设置控件格式】对话框（4）在【当前解】栏输入 25， 【最小值】输入 0， 【最大值】输入 50， 【步长】输入 1， 【页步长】输入 10，在【单元格链接】填入“E4” ，然后单击【确定】按钮，这就建立了初始投资的【滚动条】控件。（5）其他项目的【滚动条】控件可按照上述方法进行。（6）在单元格 C4:C10 中建立变动百分比与 【滚动条】控件的联系，即：在单元格 C4 中输入公式“=E4/100-25%” ，并将单元格 C4 分别复制到单元格 C5:C10 中。说 明 ： 本 例 中 各 因 素 的 变 动 范 围 为 -25% +25%， 而 滚 动 条 控 制 按 钮 的 值 的 变 化 范 围 为0 50， 为 了 使 滚 动 条 控 制 按 钮 的 变 化 表 示 为 百 分 数 的 变 化 ， 这 里 将 控 制 按 钮 的 值 除 以100 后 再 减 去 25%， 则 每 次 单 击 滚 动 条 两 端 的 箭 头 ， 单 元 格 C4:C10 中 的 变 动 百 分 比 就 变化 1%， 而 当 滚 动 条 在 中 间 位 置 ， 变 动 百 分 比 恰 好 为 零 。（7）选取单元格 D4:D10 区域并输入变化后数值的计算公式“=B4 :B10*(1+C4:C10)”（数组公式输入） 。（8）在单元格 C15 中输入静态盈亏平衡销售量计算公式“=(SLN(D4,D5,B11)+D6)/(D8-D9)*10000”。(9）在单元格 C16 中输入动态盈亏平衡销售量计算公式：“=(D6+(D4-PV(D10,B11,-1)*SLN(D4,D5,B11)*B13-D5/(1+D10)B11)/PV(D10,B11,-1)/(1-B13)/(D8-D9)*10000”。这里，当项目各年的销售量、付现成本、产品单价、单位变动成本、折旧（直线法）相等、且考虑固定资产残值回收时，动态盈亏平衡销售量计算公式为式中，S 为期末固定资产残值。这样，就建立了投资项目盈亏平衡分析模型。每次单击滚动栏两端的箭头或用鼠标拖曳滑块，变动百分比就变化 1%，当在滚动框与滚动条之间单击滚动条时，变动百分比就以10%变化。则通过不同的因素变化可以了解投资项目盈亏平衡销售量的变化情况，有利于企业管理者进行决策。第 8 章 Excel 在投资项目不确定性风险分析中的应用8.2 敏感性分析(1)敏感性分析是投资决策中常用的一种重要的分析方法。它是用来衡量当投资方案中某个因素发生了变动时，对该方案预期结果的影响程度。如果某因素在较小范围内发生了变动就会影响原定方案的经济效果，即表明该因素的敏感性强；如果某因素在较大范围内变动时才会影响原定方案的经济效果，即表明该因素的敏感性弱。通常要作敏感性分析的因素有：（1）投资额，包括固定资产投资和追加的流动资产投资。（2）项目寿命期。（3）产品的产销量。（4）产品价格。（5）经营成本，特别是其中的变动成本。（6）项目寿命期末的设备残值。（7）折现率。在长期投资决策中，敏感性分析通常用来研究有关投资方案的现金净流量或固定资产寿命发生变动时，对该方案的净现值和内部收益率的影响程度。同时，它也可以用来研究有关投资项目的内部收益率变动时，对该方案的现金净流量或使用年限的影响程度。敏感性分析有助于企业领导了解在执行决策方案时应注意的问题，从而可以预先考虑措施与对策，避免决策上的失误。8.2.1 一般的敏感性分析方法【例 8-5】图 8-7 为某一投资方案的有关资料，所采用的数据是根据对未来可能出现的情况预测的，未来的投资额、付现成本和销售收入都有可能在30%的范围内变动。试对这三个因素做敏感性分析。企业采用直线法计提折旧，基准收益率为 15%。图 8-7 不确定性因素对净现值的影响一般性的敏感性分析方法和步骤如下：（1）设计如图 8-7 所示的分析表格。（2）在单元格 B10:H10 中输入投资额变动对净现值的影响计算公式：“=PV(E3,10,-(C4-C5)*(1-F3)+SLN(B3*(1+B9:H9),D6,10)*F3)+D6/(1+E3)10-B3*(1+B9:H9)”。（3）在单元格 B11:H11 中输入销售收入变动对净现值的影响计算公式：“=PV(E3,10,-(C4*(1+B9:H9)-C5)*(1-F3)+SLN(B3,D6,10)*F3)+D6/(1+E3)10-B3”。（4）在单元格 B12:H12 中输入付现成本变动对净现值的影响计算公式：“=PV(E3,10,-(C4-C5*(1+B9:H9)*(1-F3)+SLN(B3,D6,10)*F3)+D6/(1+E3)10-B3”。以 上 各 单 元 格 区 域 的 公 式 输 入 均 为 数 组 公 式 输 入 ， 则 计 算 结 果 如 图 8-7 所 示 。对计算结果绘制分析图如图 8-8 所示，步骤如下：图 8-8 敏感性分析图（1）选取单元格区域 A9:H12，单击工具栏上的【图表向导】按钮，在【图表向导4 步骤之 1图表类型】对话框中， 【图表类型】选“XY 散点图 ”， 【子图表类型】选“平滑线散点图” ，单击【下一步】按钮。（2）在【图表向导4 步骤之 2图表源数据】对话框中，不做任何输入，单击【下一步】按钮。（3）在【图表向导4 步骤之 3图表选项】对话框中，在【图表标题】栏中输入“敏感性分析图” ，在【数值(X) 轴】 栏中输入“不确定性因素变动幅度” ，在【数值(Y) 轴】栏中输入“净现值” ，单击【下一步】按钮。（4）在【图表向导4 步骤之 4图表位置】对话框中，不做任何输入，单击【确定】按钮。（5）对图表的大小、坐标数值、标题等格式进行调整，使图表赏心悦目，则图表制作即告完成。可见，销售收入对净现值的影响最大，付现成本其次，而投资额的影响最小。然后可以利用单变量求解工具求出当净现值为零时每个不确定性因素的变动数值，方法是：在 J10 中输入公式“=PV(E3,10,-(C4-C5)*(1-F3)+SLN (B3*(1+I10),D6,10)*F3)+D6/(1+E3)10-B3*(1+I10)”，并将 J10 作为目标单元格， I10 作为可变单元格，即可利用单变量求解工具计算出净现值为零时的投资额最大变动率。用同样的方法可以求出净现值为零时的销售收入和付现成本最大变动率。可见，当销售收入和付现成本不变时，投资额增加到 41.64%以上时会使方案变得不应被接受；当投资额和付现成本不变时，销售收入低于预期值的11.48%以上时会使方案变得不应被接受；而当投资额和销售收入不变时，付现成本高于预期值 18.79%以上时会使方案变得不应被接受。因此，三个因素的敏感性由强到弱的排序依次为：销售收入、付现成本和投资额。8.2.2 投资项目净现值敏感性分析模型我们也可以参照前面所述的投资项目盈亏平衡分析模型，来建立投资项目净现值敏感性分析模型。【例 8-6】建立投资项目净现值敏感性分析模型。如图 8-9 所示，这里最大变化区间取50% ，在【设置控件格式 】对话框的【当前解】栏中输入“50” ， 【最大值】栏中输入“100”， 【页步长】栏中输入“5” 。其他项目的计算方法同投资项目盈亏平衡分析模型。定义一个名为“净现值”的自定义函数，其语法为：净现值(初始投资, 期末残值, 寿命期, 年付现固定成本, 年销售量, 产品价格, 单位变动成本, 基准收益率, 所得税税率)。自定义函数可以通过一小段程序对其参数及参数之间的关系进行描述，这种程序又称过程代码。“净现值”自定义函数的过程代码如下：Public Function 净现值(初始投资, 期末残值, 寿命期, 年付现固定成本, 年销售量, 产品价格, 单位变动成本, 基准收益率, 所得税税率)净现金流量 = (年销售量 * (产品价格 - 单位变动成本) / 10000 - 年付现固定成本) * (1 - 所得税税率) + (初始投资 - 期末残值) / 寿命期 * 所得税税率图 8-9 投资项目净现值敏感性分析模型净现值 = 净现金流量 * (1 - (1 + 基准收益率) -寿命期) / 基准收益率 - 初始投资 + 期末残值 / (1 + 基准收益率) 寿命期End Function在单元格 A16 中输入预计净现值计算公式 “=净现值(B4,B5,B6,B7,B8,B9,B10,B11,$B$12)”， （步骤为：单击工具栏的【粘贴函数】 按钮，选择“用户定义” ，选中“净现值”函数，出现该函数对话框，输入相应的内容即可） ，并复制到单元格 B16 中；在单元格 C16 中输入公式“=B16-A16” ，在单元格 D16 输入公式“=C16/A16 ”。这样，就得到了多因素变动对净现值的综合影响结果。设计单因素变动影响分析表格，如图 8-9 所示，在单元格 B19:B26 中输入公式“=D4:D11”（数组公式输入） ，在单元格 C19:C26 中分别粘贴各个因素单独变动时的净现值计算函数如下： 单元格 C19：净现值(C4,B5,B6,B7,B8,B9,B10,B11,B12)单元格 C20：净现值(B4,C5,B6,B7,B8,B9,B10,B11,B12)单元格 C21：净现值(B4,B5,C6,B7,B8,B9,B10,B11,B12)单元格 C22：净现值(B4,B5,B6,C7,B8,B9,B10,B11,B12)单元格 C23：净现值(B4,B5,B6,B7,C8,B9,B10,B11,B12)单元格 C24：净现值(B4,B5,B6,B7,B8,C9,B10,B11,B12)单元格 C25：净现值(B4,B5,B6,B7,B8,B9,C10,B11,B12)单元格 C26：净现值(B4,B5,B6,B7,B8,B9,B10,C11,B12)在单元格 D19:D26 中输入公式“=C19: C26-A16”（数组公式输入） ，在单元格 E19:E26 中输入公式“=D19:D26/A16” （数组公式输入） 。则投资项目敏感性分析模型就建立起来了。单击各个影响因素滚动条的箭头，改变其变动幅度，就可以很方便地了解各个因素对投资项目净现值的单独影响程度以及综合影响程度。这样，通过单击滚动栏两端的箭头或用鼠标拖曳滑块，即可改变各种因素的变动率，并分析其对项目净现值的影响。8.2.3 投资项目内部收益率敏感性分析模型【例 8-7】建立投资项目内部收益率敏感性分析模型。我们也可以对投资项目的内部收益率的敏感性进行分析，方法与投资项目净现值敏感性分析模型是一样的。但需要注意的是，当要分析单因素变动对内部收益率的影响时，内部收益率的计算是一件很麻烦的事，因为当投资项目寿命期内各年的净现金流量不相等时，不能使用 RATE 函数来计算内部收益率，不过可以通过自定义内部收益率函数来解决这个问题。作者研究了一种内部收益率的稳定迭代计算方法，具有稳定、快速、收敛性好的优点，计算原理及步骤如下：（1）首先假定一个内部收益率的初始值，并以此内部收益率作为贴现率 i，计算项目的净现值 NPV；（2）根据计算出的净现值数据，利用下面的公式计算第 1 次迭代后的内部收益率 IRR：式中 I 初始投资现值。若相邻两次计算的内部收益率相差不大，或计算出的净现值接近于零，则停止计算，就得到了内部收益率的近似值，否则重复上述迭代步骤。内部收益率函数的过程代码如下：Public Function 内部收益率(初始投资, 期末残值, 寿命期 , 年付现成本, 年销售量, 产品价格, 单位变动成本, 所得税税率)净现金流量 = (年销售量 * (产品价格 - 单位变动成本) / 10000 - 年付现成本) * (1 - 所得税税率) + (初始投资 - 期末残值) / 寿命期 * 所得税税率x1 = 0.110 jxz = 净现金流量 * (1 - (1 + x1) -寿命期) / x1 - 初始投资 + 期末残值 / (1 + x1) 寿命期x2 = (1 + x1) * (1 + jxz / 初始投资) (1 / 寿命期) - 1If Abs(x2 - x1) = 0.0000000001 Then 内部收益率 = x2 Else x1 = x2: GoTo 10End Function这样，就可以分析不同因素变动对内部收益率的影响。图 8-10 为投资项目内部收益率敏感性分析模型。其中单元格 B14 中的计算公式为“=内部收益率(B4,B5,B6,B7,B8,B9,B10,B11) ”；单元格 D14 中的计算公式为 “=内部收益率(C4,C5,C6,C7,C8,C9,C10,B11)” ，单元格C17:C23 中的计算公式分别如下：图 8-10 投资项目内部收益率敏感性分析模型单元格 C17：“= 内部收益率(C4,B5,B6,B7,B8,B9,B10,B11) ” 单元格 C18：“= 内部收益率(B4,C5,B6,B7,B8,B9,B10,B11) ”单元格 C19：“= 内部收益率(B4,B5,C6,B7,B8,B9,B10,B11) ” 单元格 C20：“= 内部收益率(B4,B5,B6,C7,B8,B9,B10,B11) ”单元格 C21：“= 内部收益率(B4,B5,B6,B7,C8,B9,B10,B11) ”单元格 C22：“= 内部收益率(B4,B5,B6,B7,B8,C9,B10,B11) ”单元格 C23：“= 内部收益率(B4,B5,B6,B7,B8,B9,C10,B11) ”其他各单元格的计算公式可参阅例 8-6。第 8 章 Excel 在投资项目不确定性风险分析中的应用8.3 概率分析概率分析是通过研究各种不确定性因素发生不同幅度变动的概率分布及其对投资方案经济效果的影响，对方案的净现金流量及其经济效果指标作出某种概率描述，从而对方案的风险情况作出比较准确的判断。在实际经济活动中，影响投资方案经济效果的大多数因素（如投资额、成本、销售量、产品价格、项目寿命期等）都是随机变量，我们可以预测其未来可能的取值范围，估计各种取值或值域发生的概率，但不能肯定地预知它们取什么值。因此，这就需要对投资项目进行概率分析。假设投资项目有 m 种可能出现的净现金流量状态，各种状态所对应的净现金流量序列为 yj，各种状态发生的概率为 Pj（ ） ，则在第 j 种状态下，方案的净现值为式中， 为在第 j 种状态下，第 t 周期的净现金流量；n 为项目的寿命期。则投资方案的净现值期望值为而净现值的方差为标准差为对于独立方案，计算其净现值期望值和标准差的大小，可以分析其获利能力及风险的大小。对于几个互斥方案，可以比较它们的变异系数的大小，以便衡量其相对风险的高低，从而作出决策，变异系数计算公式为8.3.1 独立项目的概率分析8.3.1.1 各年净现金流量互不相关情况下的独立项目概率分析【例 8-8】某企业的投资方案在其寿命期内可能出现 5 种状态的净现金流量序列及其发生的概率如图 8-11 所示。各年的净现金流量互不相关，基准收益率 10%，试对方案进行概率分析。图 8-11 投资方案的概率分析如图 8-11 所示，选取单元格 C8:G8 区域，输入不同状态下净现值的计算公式“=PV(10%,10,-C5:G5)+C6:G6/(1+10%)11+C4:G4”（数组公式输入） ；在单元格 C9 中输入净现值期望值计算公式“=SUMPRODUCT(C3: G3,C8:G8)”，在单元格 C10 中输入净现值方差计算公式“=SUMPRODUCT(C3: G3,(C8:G8-C9)2)”，在单元格 C11 中输入净现值标准差计算公式“=SQRT(C10) ”，在单元格 C12 中输入变异系数计算公式“=C11/C9” ，从而得到该方案的有关计算结果数据，如图 8-11 所示。可见，该方案的净现值期望值大于零，是可行的，但风险也较大。8.3.1.2 各 年 净 现 金 流 量 相 关 情 况 下 的 独 立 项 目 概 率 分 析 概 率 树 分 析 法【 例 8-9】 某 企 业 拟 投 资 开 发 一 项 专 有 技 术 ， 其 初 始 投 资 为 12 万 元 ， 该 项 技 术 预 计在 3 年 内 有 效 ， 3 年 内 每 年 为 企 业 带 来 的 现 金 流 量 是 不 确 定 的 ， 其 有 关 资 料 如 表 8-1 所 示 。该 企 业 的 资 本 成 本 为 15%， 试 对 该 投 资 项 目 的 可 行 性 进 行 评 价 。表 81 投资项目有关资料 单位：万元项目初始投资 12 寿命/年 3 贴现率 15%第 1 年 第 2 年 第 3 年净现金流量 概率 净现金流量 概率 净现金流量 概率14 0.611 0.310 0.77.5 0.19 0.47 0.47.5 0.66 0.34 0.210 0.39 0.48 0.28 0.39 0.77 0.45 0.37.5 0.15 0.34 0.45 0.42.5 0.6为便于分析计算，将表 8-1 中的数据填列在 Excel 上，其格式如图 8-12 所示，则具体计算步骤如下：（1）在单元格 G6 中输入第一种现金流序列情况下的净现值计算公式“=NPV($F$3,A6,C6,E6)-$B$3”，然后将此单元格复制到单元格 G7G19。（2）在单元格 H6:H19 中输入联合概率计算公式“=B6: B19*D6:D19*F6:F19”（数组公式输入） 。（3）在单元格 I6 中输入净现值期望值计算公式 “=SUMPRODUCT(G6:G19,H6:H19)”。（4）在单元格 J6 中输入净现值标准差计算公式“=SQRT(SUMPRODUCT(H6:H19,(G6:G19-I6)2)”。（5）在单元格 J6 中输入净现值为负的概率计算公式“=SUMIF(G6: G19,“0“,H6:H19)”。可见，该项目的期望净现值为 5.21 万元，净现值为负的概率很小。只有 0.144，该项目的获利能力较高而风险不大，故该项目是可行的。图 8-12 投资项目概率分析8.3.2 互斥项目的概率分析在此种情况下，可分别计算各个项目的净现值期望值、标准差及变异系数，对它们进行综合分析，以确定最优方案。【例 8-10】某企业有 A、B 两个投资项目，其投资额及未来各年可能的净现金流量如图 8-13 所示，假设各年的净现金流量相互独立。那么，企业应选择哪个项目？图 8-13 各年现金流独立的互斥方案的概率分析如图 8-13 所示，分析决策过程如下：（1）在合并单元格 G5G7 中输入项目 A 第 1 年的净现金流量期望值计算公式“=SUMPRODUCT(C5:C7,D5:D7)”，然后将此合并单元格分别复制到合并单元格G8G10、G11G13、G14 G16 中，得到项目 A 各年的净现金流量期望值。（ 2） 在 合 并 单 元 格 H5 H7 中 输 入 项 目 B 第 1 年 的 净 现 金 流 量 期 望 值 计 算 公 式“=SUMPRODUCT(E5:E7,F5:F7)”， 然 后 将 此 合 并 单 元 格 分 别 复 制 到 合 并 单 元 格H8 H10、 H11 H13、 H14 H16 中 ， 得 到 项 目 B 各 年 的 净 现 金 流 量 期 望 值 。（3）在合并单元格 I5I7 中输入项目 A 第 1 年的净现金流量标准差计算公式“=SQRT(SUMPRODUCT(C5:C7,(D5:D7-G5)2)”，然后将此合并单元格分别复制到合并单元格 I8I10、I11 I13、I14I16 中，得到项目 A 各年的净现金流量标准差。（4）在合并单元格 J5J7 中输入项目 B 第 1 年的净现金流量标准差计算公式“=SQRT(SUMPRODUCT(E5:E7,(F5:F7-H5)2)”，然后将此合并单元格分别复制到合并单元格 J8J10 、J11 J13、J14 J16 中，得到项目 B 各年的净现金流量标准差。（5）在单元格 C20 中输入项目 A 的净现值期望值计算公式“=NPV(B17,G5,G8,G11,G14)+G4”，在单元格 C21 中输入净现值标准差计算公式“=SQRT(SUM(I5:I162/(1+B17)(2*A5:A16)”（数组公式输入） ，在单元格 C22 中输入变异系数公式 “=C21/C20”；这里，整个项目的净现值标准差现值的计算公式为（6）同样在单元格 D20、D21、D22 中分别输入项目 B 的净现值期望值、标准差和变异系数计算公式“=NPV(B17,H5,H8,H11,H14)+H4” 、 “=SQRT(SUM(J5:J162/(1+B17)(2*A5:A16)”（数组公式输入）和 “=D21/D20”。根据计算结果可见，项目 A 风险较低但收益也低，而项目 B 风险较高但收益也高。但两个项目的变异系数相差并不是很大，在这种情况下，如何作出决策，在很大程度上取决于决策人员对风险的态度。愿意冒风险的决策人员，会选择项目 B，而对风险很敏感的决策者可能会倾向于选择项目 A。第 8 章 Excel 在投资项目不确定性风险分析中的应用8.4 蒙特卡罗模拟前面介绍的概率分析法在各年现金流彼此独立、且变量及其可能发生的情况较少的情况下，进行模拟计算是比较简单的。但在变量及其可能发生的情况较多的情况下，则必须进行大量的组合计算，例如，假设我们估计的销售量、单价、单位变动成本的可能发生概率次数分别为 5 次、4 次和 4 次，则每年就需要计算 544=80 个可能组合，假设项目的寿命为5 年，则需要计算 805=400 个可能组合，计算工作量非常大。利用蒙特卡罗模拟法可以解决这个问题，并且还可以对项目的风险进行更为深入的分析。蒙特卡罗模拟法，是根据随机数对影响因素的概率分布进行随机抽样，根据每次抽样值来计算项目的净现金流量、净现值及内部收益率等指标。在利用蒙特卡罗模拟法对投资项目进行分析计算时，主要利用两个函数：RANDBETWEEN 函数和 VLOOKUP 函数，利用RANDBETWEEN 函数产生随机数，然后利用 VLOOKUP 函数来查找对应随机数的变量数值。当进行足够的模拟计算次数后，即可得到投资项目净现值的期望值、标准差及概率分布。8.4.1 独立项目的蒙特卡罗模拟【例 8-11】某企业计划投资一项目，初始投资 20000 元，项目寿命 3 年，期末无残值。经统计分析得出产品销售量、单价、单位变动成本和付现固定成本在各年可能达到的水平及有关的概率情况如图 8-14 所示。则利用蒙特卡罗模拟法进行投资项目不确定性分析的方法和步骤如下：图 8-14 投资项目的有关资料及整理（1）首先设计模拟计算表格，如图 8-15 所示。图 8-15 只列出了对第 1 年的 3 次模拟计算过程，其他各年的模拟计算过程及表格排列顺序与第 1 年相同，其中第 2 年的模拟计算单元格在第 J R 列，第 3 年的模拟计算单元格在第 S AA 列。下面仅就第 1 年的模拟计算过程进行介绍。图 8-15 第 1 年的前 3 次模拟计算结果（2）在 A25:A5024 单元格输入第 1 年销售量的随机数计算公式：“=RANDBETWEEN(0,99)”（第 25 行输入后，再将其复制到 A26A5024 单元格中） ，然后在 B25:B5024 单元格输入对应的销售量公式： “=VLOOKUP(A25:A5024,C3:D6,2)”（数组公式输入，共运算 5000 行，即进行 5000 次模拟计算） 。说明：当利用 VLOOKUP 函数寻找符合某一概率的销售量时，必须先对概率及销售量进行分区。以第 1 年为例，随机数为 0、22、56 和 86 时对应的销售量分别为4000、5000、6000、7000 件，其意义就是当产生的随机数分别为 021、2255、5685和 8699 时，对应的销售量分别为 4000、5000、6000、7000 件，而累计概率分别为0.22、0.56、0.86 和 1.00。（3）在 C25:C5024 单元格输入第 1 年单价的随机数计算公式：“=RANDBETWEEN(0,99) ”，然后在 D25:D5024 单元格输入对应的单价公式：“=VLOOKUP(C25 :C5024, C7:D11,2)”（数组公式输入） 。（4）在 E25:E5024 单元格输入第 1 年单位变动成本的随机数计算公式：“=RANDBETWEEN(0,99)”，然后在 F25:F5024 单元格输入对应的单位变动成本公式：“=VLOOKUP(E25:E5024,C12:D15,2)” （数组公式输入） 。（5）在 G25:G5024 单元格输入第 1 年付现成本的随机数计算公式：“=RANDBETWEEN(0,99)”，然后在 H25:H5024 单元格输入对应的付现固定成本公式：“=VLOOKUP(G25:G5024,C16:D19,2)” （数组公式输入） 。（6）在 I25:I5024 单元格输入第 1 年净现金流量计算公式：“=(B25:B5024*(D25:D5024-F25:F5024)-H25:H5024)*(1-F20)+B20/3*F20”（数组公式输入） ，这里采用直线法计提折旧，年折旧=$B$20/3。第 2 年、第 3 年的净现金流量分别放在单元格 R25:R5024 和单元格 AA25:AA5024 中，而净现值则存放在 AB25:AB5024 中，其计算公式为：“=I25/(1+$D$20)+R25/(1+$D$20)2+AA25/(1+$D$20)3-$B$20”（可先输入 AB25 单元格，然后再复制到 AB26AB5024 单元格中。（7）最后的模拟计算结果存放在单元格 K1:M7 中，如图 8-16 所示，各单元格的计算公式为：单元格 M2“=AVERAGE(AB25:AB5024)”，单元格 M3“=STDEV(AB25:AB5024)”，单元格 M4“=MAX(AB25:AB5024)”，单元格 M5“=MIN(AB25:AB5024)”，单元格M6“=M3/M2”，单元格 M7“=COUNTIF(AB25:AB5024,“0“)/COUNT(AB25:AB5024)”。图 8-16 模拟计算结果在模拟计算完毕后，还可以利用 Excel 的绘图工具绘制净现值概率分布图，具体步骤如下：（1）首先对净现值进行分组，以便于进行统计分析，分组结果存放在单元格 O4:O17 中。（2）设计图表上净现值的分区，存放在单元格 P4:P17 中。（3）再选取单元格区域 Q4:Q16，输入 FREQUENCY 函数：“=FREQUENCY(AB25:AB5024,O4:O16)/5000”（数组公式输入） ，即可得到净现值概率分布结果，如 8-17所示。然后插入直方图，做法是：选择单元格区域 P4:Q17，在图表向导中选柱形图，再依据有关说明输入有关资料。净现值概率分布图如图 8-18 所示。图 8-17 净现值概率分布统计图 8-18 净现值概率分布图根据计算结果及净现值概率分布图，就可以对投资项目的不确定性进行分析。由结果可以看出，该项目的净现值期望值为 10642 元，标准差 8823 元，净现值为负的概率为 0.112，故该项目的投资风险还是比较小的。该项目的净现值主要集中在 0 20000 元，概率为0.74，其中在 5000 15000 元的概率为 0.42；而净现值为负时主要集中在-5000 0 之间，概率为 0.08。由图 8-18 还可以看出，该项目的净现值大致符合正态分布。8.4.2