财务资料-excel在投资项目不确定性风险分析中的应用14533.docx

《财务资料-excel在投资项目不确定性风险分析中的应用14533.docx》由会员分享，可在线阅读，更多相关《财务资料-excel在投资项目不确定性风险分析中的应用14533.docx（41页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel在投资项目不确定性风险分析中的应用8.1 盈亏平衡分析(1)盈亏平衡分析的原理就是根据量本利之间的关系，计算项目的盈亏平衡点的销售量，从而分析项目对市场需求变化的适应能力。一般来说，盈亏平衡点是指企业既不亏又不盈或营业利润为零时的销售量。根据是否考虑资金的时间价值，盈亏平衡分析又可分为静态盈亏平衡分析和动态盈亏平衡分析。8.1.1 静态盈亏平平衡分析静态盈亏平衡分分析是在不考考虑资金的时时间价值情况况下，对投资资项目的盈亏亏平衡进行分分析。当某年年的营业利润润为零时，可可以得到该年年盈亏平衡点点的销售量为为（这里假设设只有一种产产品）：式中，Qt为第第t年的盈亏平平衡点销售量量（又称

2、保本本销售量）；Ft为第t年的固定成成本，这里假假设非付现固固定成本只有有折旧，即FFt = Dt + Fc，Dt为第t年的折旧；Fc为付现固定定成本；p为产品单价价；v为产品的单单位变动成本本，并假设各各年的付现固固定成本、产产品单价和产产品的单位变变动成本均不不变。当产销量低于盈盈亏平衡点销销售量时，投投资项目处于于亏损状态，反反之，当产销销量超过盈亏亏平衡点销售售量时，项目目就有了盈利利。当企业在在盈亏平衡点点附近经营，即即销售量接近近于Qt时，投资项项目的经营风风险很大，或或经营上的安安全程度很低低，销售量微微小的下降都都可能使企业业发生亏损。单一产品的盈亏亏平衡分析比比较简单。根根据

3、给定的各各年的付现固固定成本、折折旧、产品单单价和单位变变动成本，即即可由上述公公式计算出各各年的静态保保本销售量。当一个投资项目目同时生产多多种不同的产产品，或对一一个生产多种种产品的整个个企业进行盈盈亏平衡分析析时，则需要要考虑多品种种产品的情况况。在进行多多品种盈亏平平衡分析时，加加权平均法是是较常用的一一种方法。【例8-1】某某企业生产AA、B、C三种产品，AA产品年销售售量1000000件，单单价10元/件，单位变变动成本8.5元/件；B产品年销售售量250000台，单价价20元/台，单位变变动成本166元/台；C产品年销售售量100000套，单价价50元/套，单位变变动成本255元

4、/套；全厂固固定成本3000000元元。根据以上上资料，可以以建立分析表表格如图8-1所示。有关计算分析公公式如下：销售收入=销售售量单价边际贡献=销售售量（单价单单位变动成本本）边际贡献率=边边际贡献销售收入销售比重=某产产品销售收入入全厂各产品品销售收入合合计全厂综合边际贡贡献率=某产品边际际贡献率该产品销售售比重全厂综合保本额额=全厂固定成成本全厂综合边边际贡献率某产品保本额=全厂综合保保本额该产品销售售比重某产品保本量=某产品保本本额该产品单价价图8-1 某企企业的多品种种盈亏平衡分分析输入已知数据及及定义完公式式后，即可马马上计算出各各个可变单元元格的数值来来，即全厂综综合保本额11

5、2000000元，产品品A、B、C的保本额分分别为6000000元、3000000元和3000000元，保保本量分别为为600000件、150000台和60000套。各单元格的计算算公式为单元格E3:EE5 ：“= B3:B5*C33:C5”（数组公式式输入）。单元格F3:FF5 ：“=B3:B5*(CC3:C5-D33:D5)”（数组公式式输入）。单元格G3:GG6 ：“=F3:F6/E33:E6”（数组公式式输入）。单元格E6：“=SUM(E3:E5)”。单元格F6：“=SUM(F3:F5)”。单元格B8:BB10 ：“=E3:E5/E66”（数组公式式输入）。单元格D8:DD10 ：“=

6、B8:B10*DD11”（数组公式式输入）。单元格E8:EE10 ：“=D8:D10/CC3:C5”（数组公式式输入）。单元格D11 ：“=H6/GG6”。图8-1建立了了各产品的单单价、单位变变动成本和固固定成本与保保本额或保本本量之间的关关系，利用图图8-1就可分分析它们对盈盈亏平衡点的的影响。8.1.2 动态盈亏平平衡分析8.1.2.11 独立项项目的动态盈盈亏平衡分析析静态盈亏平衡分分析没有考虑虑资金的时间间价值、所得得税、利率，以以及通货膨胀胀等因素的影影响，由此计计算出的盈亏亏平衡点销售售量仅仅能使使项目的当期期达到盈亏平平衡，却并不不能保证项目目的净现值恰恰好为零。在在考虑资金的

7、的时间价值和和所得税等因因素的条件下下，项目的动动态盈亏平衡衡点就是项目目净现值为零零的那一点，即即动态保本销销售量就是使使项目净现值值为零的销售售量。考虑单单一产品的情情况，令NPPV=0，则则可得到项目目各年的动态态保本销售量量的计算公式式为式中，T为所得得税税率；QQt为各年的保保本销售量；pt、vt和Fct分别为各各年的产品单单价、单位变变动成本、付付现固定成本本；i为项目的基基准收益率；I为初始投资资（并假设在在第0年一次性投投入项目）。这样，利用上述述公式，即可可分析各种情情况下项目各各年保本销售售量的变化情情况。【例8-2】某某企业准备投投资生产一种种新产品，项项目总投资3350

8、万元，项项目寿命期55年，期末无无残值，采用用直线法计提提折旧。经预预测，项目投投产后每年可可销售产品885000台台，产品单价价40元/台，单位变变动成本200元/台，年付现现固定成本550万元，企企业的基准收收益率为100%，所得税税税率33%。此时，各年的折折旧额相同，假假设各年的销销售量、产品品单价、单位位变动成本、付付现固定成本本以及折旧均均相同，可以以利用上述公公式直接导出出动态盈亏平平衡销售量（保保本销售量）的的计算公式如如下：根据此公式计算算出保本销售售量为766664件，如如图8-2所示，单单元格E4中的保本本销售量计算算公式为：“=(E3+(B3/PPV(F3,B3,-11

9、)-G3)*A3/BB3/(1-G3)/(C3-DD3)*100000”。图8-2 投投资项目的动动态盈亏平衡衡分析但实际上，各年年的销售量受受到企业内部部及市场条件件的影响，它它们是不同的的，故可以利利用规划求解解工具来计算算各年的保本本销售量，步步骤如下：（1）如图8-2所示，将将单元格C110:G10作为可可变单元格，存存放各年的保保本销售量。（2）在单元格格B7中输入公公式“= -A33”，在单元格格C7:G7中输入净净现金流量计计算公式“=(C9:G9/100000*(C3-D33)-E3)*(1-GG3)+SLLN(A3,H3,B33)*G3”（数组公式输入）。（3）在单元格格H7

10、中输入净净现值计算公公式“=NPPV(F3,C7:G7)+BB7”。（4）单击工具具菜单中的【规规划求解】项项，出现【规规划求解参数数】对话框，其其中【设置目目标单元格】选选“$H$7”，【等于】选选“0”，【可变单单元格】选“$C$9:$G$9”；单击【求求解】按钮，即即得各年的保保本销售量，如如图8-2所示。当可变单元格CC9:G9中赋予不不同的初始值值时，会得到到不同的保本本销售量,如图8-2所示），各各年的销售量量变化范围很很大，这正反反映了动态盈盈亏平衡的特特点。实际上上，各年保本本销售量组合合有无数个，故故为求得符合合实际情况的的保本销售量量组合，还要要考虑一些约约束条件，比比如各

11、年的会会计利润应大大于零，各年年取得的现金金收入应能够够偿还各年到到期的债务以以及支付股利利，等等。8.1.2.22 互斥项项目的动态盈盈亏平衡分析析在需要对若干个个方案进行比比较的情况下下，若是某一一个共有的不不确定性因素素（比如销售售量、产量、寿寿命、产品价价格、单位变变动成本等）影影响这些方案案的取舍，则则可以利用盈盈亏平衡分析析帮助决策。【例8-3】某某企业投资生生产某种产品品，现有两个个方案，有关关资料如图88-3所示，该该产品的市场场寿命具有较较大的不确定定性，如果基基准收益率为为15%，不考考虑期末资产产残值，那么么，企业应如如何根据项目目的寿命期来来选择方案？图8-3 根根据方

12、案寿命命期选择项目目如图8-3所示示，进行决策策分析的步骤骤如下：（1）首先在单单元格F3中输入“5”，在单元格格H3中输入“11”，单元格G33可先空置。（2）选取单元元格区域F4:H5，输入不不同寿命期下下两个方案的的净现值计算算公式“=PV(DD4:D5,F33:H3,-CC4:C5)-BB4:B5”（数组公式式输入）。（3）在单元格格G6中输入公公式“=G4-GG5”，将单元格格G6作为目标标单元格，将将单元格G33作为可变单单元格，利用用【规划求解解】工具或【单单变量求解】工工具，可以求求得两个方案案净现值相等等时的项目寿寿命期。由计算结果可以以看出，两个个方案净现值值相等时的寿寿命

13、期为9.92年，这这就是以项目目寿命期为共共有变量时方方案1与方案2的净现值无无差异点。因因此，当寿命命期小于9.92年时，应应采用方案11；而当寿命命期大于9.92年时，应应采用方案22。8.1.3 投资项目盈盈亏平衡分析析模型除了前面介绍的的直接利用公公式计算盈亏亏平衡点保本本销售量或设设计工作表格格进行分析外外，我们还可可以设计盈亏亏平衡分析模模型来分析每每个因素各种种可能的变动动情况下对保保本销售量的的影响。【例8-4】投投资项目的盈盈亏平衡分析析模型如图88-4所示，具具体设计步骤骤如下：图8-4 投投资项目盈亏亏平衡分析模模型（1）首先设计计好分析模型型结构，如图图8-4所示（表表

14、中数据以例例8-2为例）。（2）单击【视视图】，选择择【工具栏】，再再单击【窗体体】，出现【窗窗体】工具栏栏，如图8-5所示，单单击【滚动条条】按钮，然然后在工作表表的合适位置置（这里为EE4F4单元格）拖拖曳出一个矩矩形【组合框框】控件，并并调整其大小小。图8-5 【窗窗体】工具栏栏（3）将鼠标移移到新建立的的【滚动条】控控件上，单击击鼠标右键，出出现快捷菜单单，选择【设设置控件格式式】，出现【设设置控件格式式】对话框，选选择【控制】项项，如图8-6所示。图8-6 【设设置控件格式式】对话框（4）在【当前前解】栏输入入25，【最小小值】输入00，【最大值值】输入500，【步长】输输入1，【页

15、步长长】输入100，在【单元元格链接】填填入“E4”，然后单击击【确定】按按钮，这就建建立了初始投投资的【滚动动条】控件。（5）其他项目目的【滚动条条】控件可按按照上述方法法进行。（6）在单元格格C4:C10中建立立变动百分比比与【滚动条条】控件的联联系，即：在在单元格C44中输入公式式“=E4/1100-255%”，并将单元元格C4分别复制制到单元格CC5:C10中。说明：本例中各各因素的变动动范围为-225%+25%，而而滚动条控制制按钮的值的的变化范围为为050，为了使使滚动条控制制按钮的变化化表示为百分分数的变化，这这里将控制按按钮的值除以以100后再减减去25%，则每每次单击滚动动条

16、两端的箭箭头，单元格格C4:C10中的变变动百分比就就变化1%，而当滚滚动条在中间间位置，变动动百分比恰好好为零。（7）选取单元元格D4:D10区域并并输入变化后后数值的计算算公式“=B4:B10*(1+C4:C10)”（数组公式式输入）。（8）在单元格格C15中输入入静态盈亏平平衡销售量计计算公式“=(SLNN(D4,DD5,B111)+D6)/(D8-D9)*110000”。(9）在单元格格C16中输入入动态盈亏平平衡销售量计计算公式：“=(D6+(D4-PVV(D10,B11,-1)*SLLN(D4,D5,B111)*B113-D5/(1+D110)B111)/PVV(D10,B11,-

17、1)/(11-B13)/(D88-D9)*100000”。这里，当项目各各年的销售量量、付现成本本、产品单价价、单位变动动成本、折旧旧（直线法）相相等、且考虑虑固定资产残残值回收时，动动态盈亏平衡衡销售量计算算公式为式中，S为期末末固定资产残残值。这样，就建立了了投资项目盈盈亏平衡分析析模型。每次次单击滚动栏栏两端的箭头头或用鼠标拖拖曳滑块，变变动百分比就就变化1%，当在滚滚动框与滚动动条之间单击击滚动条时，变变动百分比就就以10%变化。则则通过不同的的因素变化可可以了解投资资项目盈亏平平衡销售量的的变化情况，有有利于企业管管理者进行决决策。第8章 Exccel在投资资项目不确定定性风险分析析

18、中的应用8.2 敏感性性分析(1) 敏感性分析是投投资决策中常常用的一种重重要的分析方方法。它是用用来衡量当投投资方案中某某个因素发生生了变动时，对对该方案预期期结果的影响响程度。如果果某因素在较较小范围内发发生了变动就就会影响原定定方案的经济济效果，即表表明该因素的的敏感性强；如果某因素素在较大范围围内变动时才才会影响原定定方案的经济济效果，即表表明该因素的的敏感性弱。通常要作敏感性性分析的因素素有：（1）投资额，包包括固定资产产投资和追加加的流动资产产投资。（2）项目寿命命期。（3）产品的产产销量。（4）产品价格格。（5）经营成本本，特别是其其中的变动成成本。（6）项目寿命命期末的设备备残

19、值。（7）折现率。在长期投资决策策中，敏感性性分析通常用用来研究有关关投资方案的的现金净流量量或固定资产产寿命发生变变动时，对该该方案的净现现值和内部收收益率的影响响程度。同时时，它也可以以用来研究有有关投资项目目的内部收益益率变动时，对对该方案的现现金净流量或或使用年限的的影响程度。敏敏感性分析有有助于企业领领导了解在执执行决策方案案时应注意的的问题，从而而可以预先考考虑措施与对对策，避免决决策上的失误误。8.2.1 一般的敏感感性分析方法法【例8-5】图图8-7为某一一投资方案的的有关资料，所所采用的数据据是根据对未未来可能出现现的情况预测测的，未来的的投资额、付付现成本和销销售收入都有有

20、可能在300%的范围内内变动。试对对这三个因素素做敏感性分分析。企业采采用直线法计计提折旧，基基准收益率为为15%。图8-7 不不确定性因素素对净现值的的影响一般性的敏感性性分析方法和和步骤如下：（1）设计如图图8-7所示的的分析表格。（2）在单元格格B10:H10中输入入投资额变动动对净现值的的影响计算公公式：“=PV(E33,10,-(C4-C5)*(1-F3)+SLN(B3*(11+B9:H9),DD6,10)*F3)+D6/(1+E3)10-BB3*(1+B9:H9)”。（3）在单元格格B11:H11中输入入销售收入变变动对净现值值的影响计算算公式：“=PV(E33,10,-(C4*(

21、1+B99:H9)-CC5)*(11-F3)+SLN(BB3,D6,10)*FF3)+DD6/(1+E3)110-B3”。（4）在单元格格B12:H12中输入入付现成本变变动对净现值值的影响计算算公式：“=PV(E33,10,-(C4-C5*(11+B9:H9)*(1-F33)+SLNN(B3,DD6,10)*F3)+D6/(1+E3)10-BB3”。以上各单元格区区域的公式输输入均为数组组公式输入，则则计算结果如如图8-7所示。对计算结果绘制制分析图如图图8-8所示，步步骤如下：图8-8 敏敏感性分析图图（1）选取单元元格区域A99:H12，单击击工具栏上的的【图表向导导】按钮，在在【图表向

22、导导4步骤之1图表类型】对对话框中，【图图表类型】选选“XY散点图”，【子图表表类型】选“平滑线散点点图”，单击【下下一步】按钮钮。（2）在【图表表向导4步骤之2图表源数据据】对话框中中，不做任何何输入，单击击【下一步】按按钮。（3）在【图表表向导4步骤之3图表选项】对对话框中，在在【图表标题题】栏中输入入“敏感性分析析图”，在【数值值(X)轴】栏栏中输入“不确定性因因素变动幅度度”，在【数值值(Y)轴】栏栏中输入“净现值”，单击【下下一步】按钮钮。（4）在【图表表向导4步骤之4图表位置】对对话框中，不不做任何输入入，单击【确确定】按钮。（5）对图表的的大小、坐标标数值、标题题等格式进行行调整

23、，使图图表赏心悦目目，则图表制制作即告完成成。可见，销售收入入对净现值的的影响最大，付付现成本其次次，而投资额额的影响最小小。然后可以利用单单变量求解工工具求出当净净现值为零时时每个不确定定性因素的变变动数值，方方法是：在JJ10中输入入公式“=PV(EE3,10,-(C44-C5)*(1-F33)+SLNN (B3*(1+I110),D66,10)*F3)+D6/(11+E3)10-B33*(1+II10)”，并将J100作为目标单单元格，I110作为可变变单元格，即即可利用单变变量求解工具具计算出净现现值为零时的的投资额最大大变动率。用用同样的方法法可以求出净净现值为零时时的销售收入入和付

24、现成本本最大变动率率。可见，当当销售收入和和付现成本不不变时，投资资额增加到441.64%以上时会使使方案变得不不应被接受；当投资额和和付现成本不不变时，销售售收入低于预预期值的111.48%以以上时会使方方案变得不应应被接受；而而当投资额和和销售收入不不变时，付现现成本高于预预期值18.79%以上上时会使方案案变得不应被被接受。因此此，三个因素素的敏感性由由强到弱的排排序依次为：销售收入、付付现成本和投投资额。8.2.2 投资项目净净现值敏感性性分析模型我们也可以参照照前面所述的的投资项目盈盈亏平衡分析析模型，来建建立投资项目目净现值敏感感性分析模型型。【例8-6】建建立投资项目目净现值敏感

25、感性分析模型型。如图8-9所示，这这里最大变化化区间取50%，在【设设置控件格式式】对话框的的【当前解】栏栏中输入“50”，【最大值值】栏中输入入“100”，【页步长长】栏中输入入“5”。其他项目目的计算方法法同投资项目目盈亏平衡分分析模型。定义一个名为“净现值”的自定义函函数，其语法法为：净现值值(初始投资, 期末残值, 寿命期, 年付现固固定成本, 年销售量, 产品价格, 单位变动成成本, 基准收益益率, 所得税税税率)。自定义函函数可以通过过一小段程序序对其参数及及参数之间的的关系进行描描述，这种程程序又称过程程代码。“净现值”自定义函数数的过程代码码如下：Public Functtio

26、n 净净现值(初始投资, 期末残值, 寿命期, 年付现固固定成本, 年销售量, 产品价格, 单位变动成成本, 基准收益益率, 所得税税税率)净现金流量 = (年销售售量 * (产品价格 - 单位变动动成本) / 100000 - 年年付现固定成成本) * (1 - 所得税税率率) + (初始投资 - 期末残值值) / 寿命命期 * 所得税税税率图8-9 投投资项目净现现值敏感性分分析模型净现值 = 净净现金流量 * (1 - (1 + 基准收收益率) -寿命期期) / 基准准收益率 - 初始投资资 + 期末残残值 / (1 + 基基准收益率) 寿命命期End Funnctionn在单元格A16

27、6中输入预计计净现值计算算公式“=净现值(B44,B5,BB6,B7,B8,B99,B10,B11,$B$12)”，（步骤为为：单击工具具栏的【粘贴贴函数】按钮钮，选择“用户定义”，选中“净现值”函数，出现现该函数对话话框，输入相相应的内容即即可），并复复制到单元格格B16中；在在单元格C116中输入公公式“=B16-A16”，在单元格格D16输入公公式“=C16/A16”。这样，就就得到了多因因素变动对净净现值的综合合影响结果。设计单因素变动动影响分析表表格，如图88-9所示，在在单元格B119:B26中输入入公式“=D4:D11”（数组公式式输入），在在单元格C119:C26中分别别粘贴各

28、个因因素单独变动动时的净现值值计算函数如如下： 单元格C19：净现值(CC4,B5,B6,B77,B8,BB9,B100,B11,B12)单元格C20：净现值(BB4,C5,B6,B77,B8,BB9,B100,B11,B12)单元格C21：净现值(BB4,B5,C6,B77,B8,BB9,B100,B11,B12)单元格C22：净现值(BB4,B5,B6,C77,B8,BB9,B100,B11,B12)单元格C23：净现值(BB4,B5,B6,B77,C8,BB9,B100,B11,B12)单元格C24：净现值(BB4,B5,B6,B77,B8,CC9,B100,B11,B12)单元格C25

29、：净现值(BB4,B5,B6,B77,B8,BB9,C100,B11,B12) 单元格C26：净现值(BB4,B5,B6,B77,B8,BB9,B100,C11,B12)在单元格D199:D26中输入入公式“=C19:C26-AA16”（数组公式式输入），在在单元格E119:E26中输入入公式“=D19:D26/AA16”（数组公式式输入）。则投资项目敏感感性分析模型型就建立起来来了。单击各各个影响因素素滚动条的箭箭头，改变其其变动幅度，就就可以很方便便地了解各个个因素对投资资项目净现值值的单独影响响程度以及综综合影响程度度。这样，通过单击击滚动栏两端端的箭头或用用鼠标拖曳滑滑块，即可改改变各

30、种因素素的变动率，并并分析其对项项目净现值的的影响。8.2.3 投资项目内内部收益率敏敏感性分析模模型【例8-7】建建立投资项目目内部收益率率敏感性分析析模型。我们们也可以对投投资项目的内内部收益率的的敏感性进行行分析，方法法与投资项目目净现值敏感感性分析模型型是一样的。但但需要注意的的是，当要分分析单因素变变动对内部收收益率的影响响时，内部收收益率的计算算是一件很麻麻烦的事，因因为当投资项项目寿命期内内各年的净现现金流量不相相等时，不能能使用RATTE函数来计计算内部收益益率，不过可可以通过自定定义内部收益益率函数来解解决这个问题题。作者研究究了一种内部部收益率的稳稳定迭代计算算方法，具有有

31、稳定、快速速、收敛性好好的优点，计计算原理及步步骤如下：（1）首先假定定一个内部收收益率的初始始值，并以此此内部收益率率作为贴现率率i，计算项目目的净现值NNPV；（2）根据计算算出的净现值值数据，利用用下面的公式式计算第1次迭代后的的内部收益率率IRR：式中I初始始投资现值。若相邻两次计算算的内部收益益率相差不大大，或计算出出的净现值接接近于零，则则停止计算，就就得到了内部部收益率的近近似值，否则则重复上述迭迭代步骤。内部收益率函数数的过程代码码如下：Public Functtion 内内部收益率(初始投资, 期末残值, 寿命期, 年付现成成本, 年销售量量, 产品价格格, 单位变动动成本,

32、 所得税税税率) 净现金金流量 = (年销售量量 * (产品品价格 - 单位变动成成本) / 100000 - 年付现成本本) * (1 - 所得税税率率) + (初始投资 - 期末残值) / 寿命期期 * 所得税税税率 x1 = 0.1110 jxzz = 净现现金流量 * (1 - (1 + x1) -寿命期) / x1 - 初始投资 + 期末残值值 / (11 + x11) 寿寿命期 x2 = (1 + x1) * (11 + jxxz / 初初始投资) (1 / 寿命期期) - 1 If Abs(xx2 - x1) = 0.000000000011 Thenn 内部收益益率 = xx2

33、 Elsse x1 = x2: GoToo 10End Funnctionn这样，就可以分分析不同因素素变动对内部部收益率的影影响。图8-10为投资资项目内部收收益率敏感性性分析模型。其其中单元格BB14中的计计算公式为“=内部收益率率(B4,BB5,B6,B7,B88,B9,BB10,B111)”；单元格D114中的计算算公式为“=内部收益率率(C4,CC5,C6,C7,C88,C9,CC10,B111)”，单元格C117:C23中的计计算公式分别别如下：图8-10 投资项目内内部收益率敏敏感性分析模模型单元格C17：“=内部收益率率(C4,BB5,B6,B7,B88,B9,BB10,B11

34、1)” 单元格C18：“=内部收益率率(B4,CC5,B6,B7,B88,B9,BB10,B111)”单元格C19：“=内部收益率率(B4,BB5,C6,B7,B88,B9,BB10,B111)” 单元格C20：“=内部收益率率(B4,BB5,B6,C7,B88,B9,BB10,B111)”单元格C21：“=内部收益率率(B4,BB5,B6,B7,C88,B9,BB10,B111)”单元格C22：“=内部收益率率(B4,BB5,B6,B7,B88,C9,BB10,B111)”单元格C23：“=内部收益率率(B4,BB5,B6,B7,B88,B9,CC10,B111)”其他各单元格的的计算公式可

35、可参阅例8-6。第8章 Exccel在投资资项目不确定定性风险分析析中的应用8.3 概率分分析 概率分析是通过过研究各种不不确定性因素素发生不同幅幅度变动的概概率分布及其其对投资方案案经济效果的的影响，对方方案的净现金金流量及其经经济效果指标标作出某种概概率描述，从从而对方案的的风险情况作作出比较准确确的判断。在实际经济活动动中，影响投投资方案经济济效果的大多多数因素（如如投资额、成成本、销售量量、产品价格格、项目寿命命期等）都是是随机变量，我我们可以预测测其未来可能能的取值范围围，估计各种种取值或值域域发生的概率率，但不能肯肯定地预知它它们取什么值值。因此，这这就需要对投投资项目进行行概率分

36、析。假设投资项目有有m种可能出现现的净现金流流量状态，各各种状态所对对应的净现金金流量序列为为yj，各种状态态发生的概率率为Pj（），则在第第j种状态下，方方案的净现值值为式中，为在第jj种状态下，第第t周期的净现现金流量；nn为项目的寿寿命期。则投资方案的净净现值期望值值为而净现值的方差差为标准差为对于独立方案，计计算其净现值值期望值和标标准差的大小小，可以分析析其获利能力力及风险的大大小。对于几几个互斥方案案，可以比较较它们的变异异系数的大小小，以便衡量量其相对风险险的高低，从从而作出决策策，变异系数数计算公式为为8.3.1 独独立项目的概概率分析8.3.1.11 各年净净现金流量互互不相

37、关情况况下的独立项项目概率分析析【例8-8】某某企业的投资资方案在其寿寿命期内可能能出现5种状态的净净现金流量序序列及其发生生的概率如图图8-11所示示。各年的净净现金流量互互不相关，基基准收益率110%，试对对方案进行概概率分析。图8-11 投资方案的的概率分析如图8-11所所示，选取单单元格C8:G8区域，输输入不同状态态下净现值的的计算公式“=PV(110%,100,-C5:G5)+CC6:G6/(11+10%)11+CC4:G4”（数组公式式输入）；在在单元格C99中输入净现现值期望值计计算公式“=SUMPPRODUCCT(C3:G3,C88:G8)”，在单元格格C10中输入入净现值方

38、差差计算公式“=SUMPPRODUCCT(C3:G3,(CC8:G8-C99)2)”，在单元格格C11中输入入净现值标准准差计算公式式“=SQRTT(C10)”，在单元格格C12中输入入变异系数计计算公式“=C11/C9”，从而得到到该方案的有有关计算结果果数据，如图图8-11所示示。可见，该该方案的净现现值期望值大大于零，是可可行的，但风风险也较大。8.3.1.22 各年净净现金流量相相关情况下的的独立项目概概率分析概率树分析析法【例8-9】某某企业拟投资资开发一项专专有技术，其其初始投资为为12万元，该该项技术预计计在3年内有效，33年内每年为为企业带来的的现金流量是是不确定的，其其有关资

39、料如如表8-1所示。该该企业的资本本成本为155%，试对该该投资项目的的可行性进行行评价。表81 投投资项目有关关资料 单位位：万元项目初始投资12寿命/年3贴现率15%第1年第2年第3年净现金流量概率净现金流量概率净现金流量概率7.50.6100.7140.6110.37.50.160.390.470.440.240.480.2100.390.480.370.490.750.350.47.50.150.32.50.6为便于分析计算算，将表8-1中的数据据填列在Exxcel上，其其格式如图88-12所示示，则具体计计算步骤如下下：（1）在单元格格G6中输入第第一种现金流流序列情况下下的净现值计

40、计算公式“=NPV($F$3,A6,C66,E6)-$B$3”，然后将此此单元格复制制到单元格GG7G19。（2）在单元格格H6:H19中输入入联合概率计计算公式“=B6:B19*DD6:D19*FF6:F19”（数组公式式输入）。（3）在单元格格I6中输入净净现值期望值值计算公式“=SUMPPRODUCCT(G6:G19,H66:H19)”。（4）在单元格格J6中输入净净现值标准差差计算公式“=SQRTT(SUMPPRODUCCT(H6:H19,(G6:G19-II6)2)”。（5）在单元格格J6中输入净净现值为负的的概率计算公公式“=SUMIIF(G6:G19,0,HH6:H19)”。可见

41、，该项目的的期望净现值值为5.211万元，净现现值为负的概概率很小。只只有0.1444，该项目目的获利能力力较高而风险险不大，故该该项目是可行行的。图8-12 投资项目概概率分析8.3.2 互斥项目的的概率分析在此种情况下，可可分别计算各各个项目的净净现值期望值值、标准差及及变异系数，对对它们进行综综合分析，以以确定最优方方案。【例8-10】某企业有A、B两个投资项项目，其投资资额及未来各各年可能的净净现金流量如如图8-133所示，假设设各年的净现现金流量相互互独立。那么么，企业应选选择哪个项目目？图8-13 各年现金流流独立的互斥斥方案的概率率分析如图8-13所所示，分析决决策过程如下下：（

42、1）在合并单单元格G5G7中输入项项目A第1年的净现金金流量期望值值计算公式“=SUMPPRODUCCT(C5:C7,D55:D7)”，然后将此此合并单元格格分别复制到到合并单元格格G8G10、G11G13、G14G16中，得得到项目A各年的净现现金流量期望望值。（2）在合并单单元格H5H7中输入项项目B第1年的净现金金流量期望值值计算公式“=SUMPPRODUCCT(E5:E7,F55:F7)”，然后将此此合并单元格格分别复制到到合并单元格格H8H10、H11H13、H14H16中，得得到项目B各年的净现现金流量期望望值。（3）在合并单单元格I5I7中输入项项目A第1年的净现金金流量标准差差

43、计算公式“=SQRTT(SUMPPRODUCCT(C5:C7,(DD5:D7-G55)2)”，然后将此此合并单元格格分别复制到到合并单元格格I8I10、I11I13、I14I16中，得得到项目A各年的净现现金流量标准准差。（4）在合并单单元格J5J7中输入项项目B第1年的净现金金流量标准差差计算公式“=SQRTT(SUMPPRODUCCT(E5:E7,(FF5:F7-H55)2)”，然后将此此合并单元格格分别复制到到合并单元格格J8J10、J11J13、J14J16中，得得到项目B各年的净现现金流量标准准差。（5）在单元格格C20中输入入项目A的净现值期期望值计算公公式“=NPV(B17,GG

44、5,G8,G11,GG14)+GG4”，在单元格格C21中输入入净现值标准准差计算公式式“=SQRTT(SUM(I5:I1622/(1+BB17)(2*A5:A16)”（数组公式式输入），在在单元格C222中输入变变异系数公式式“=C21/C20”；这里，整整个项目的净净现值标准差差现值的计算算公式为（6）同样在单单元格D200、D21、D22中分别别输入项目BB的净现值期期望值、标准准差和变异系系数计算公式式“=NPV(B17,HH5,H8,H11,HH14)+HH4”、“=SQRTT(SUM(J5:J1622/(1+BB17)(2*A5:A16)”（数组公式式输入）和“=D21/D20”。

45、根据计算结果可可见，项目AA风险较低但但收益也低，而而项目B风险较高但但收益也高。但但两个项目的的变异系数相相差并不是很很大，在这种种情况下，如如何作出决策策，在很大程程度上取决于于决策人员对对风险的态度度。愿意冒风风险的决策人人员，会选择择项目B，而对风险险很敏感的决决策者可能会会倾向于选择择项目A。第8章 Exccel在投资资项目不确定定性风险分析析中的应用8.4 蒙特卡卡罗模拟 前面介绍的概率率分析法在各各年现金流彼彼此独立、且且变量及其可可能发生的情情况较少的情情况下，进行行模拟计算是是比较简单的的。但在变量量及其可能发发生的情况较较多的情况下下，则必须进进行大量的组组合计算，例例如，

46、假设我我们估计的销销售量、单价价、单位变动动成本的可能能发生概率次次数分别为55次、4次和4次，则每年年就需要计算算544=80个可可能组合，假假设项目的寿寿命为5年，则需要要计算805=4000个可能组合合，计算工作作量非常大。利利用蒙特卡罗罗模拟法可以以解决这个问问题，并且还还可以对项目目的风险进行行更为深入的的分析。蒙特卡罗模拟法法，是根据随随机数对影响响因素的概率率分布进行随随机抽样，根根据每次抽样样值来计算项项目的净现金金流量、净现现值及内部收收益率等指标标。在利用蒙蒙特卡罗模拟拟法对投资项项目进行分析析计算时，主主要利用两个个函数：RAANDBETTWEEN 函数和VLOOOKUP函函数，利用RRANDBEETWEENN函数产生随随机数，然后后利用VLOOOKUP函函数来查找对对应随机数的的变量数值。当当进行足够的的模拟计算次次数后，即可可得到投资项项目净现值的的期望值、标标准差及概率率分布。8.4.1 独独立项目的蒙蒙特卡罗模拟拟【例8-11】某企业计划划投资一项目目，初始投资资200000元，项目寿寿命3年，期末无无残值。经统统计分析得出出产品销售量量、单价、单单位变动成本本和付现固定定成本在各年年可能达到的的水平及有关