财务盈亏平衡分析原理

. . . Excel在投资项目不确定性风险分析中的应用8.1 盈亏平衡分析盈亏平衡分析的原理就是根据量本利之间的关系,计算项目的盈亏平衡点的销售量,从而分析项目对市场需求变化的适应能力。一般来说,盈亏平衡点是指企业既不亏又不盈或营业利润为零时的销售量。根据是否考虑资金的时间价值,盈亏平衡分析又可分为静态盈亏平衡分析和动态盈亏平衡分析。8.1.1 静态盈亏平衡分析静态盈亏平衡分析是在不考虑资金的时间价值情况下,对投资项目的盈亏平衡进行分析。当某年的营业利润为零时,可以得到该年盈亏平衡点的销售量为这里假设只有一种产品：式中,Qt为第t年的盈亏平衡点销售量又称保本销售量；Ft为第t年的固定成本,这里假设非付现固定成本只有折旧,即Ft= Dt + Fc,Dt为第t年的折旧；Fc为付现固定成本；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*数组公式输入。单元格G3:G6 ：=F3:F6/E3:E6数组公式输入。单元格E6：=SUM。单元格F6：=SUM。单元格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为所得税税率；Qt为各年的保本销售量；pt、vt和Fct分别为各年的产品单价、单位变动成本、付现固定成本；i为项目的基准收益率；I为初始投资并假设在第0年一次性投入项目。这样,利用上述公式,即可分析各种情况下项目各年保本销售量的变化情况。例8-2某企业准备投资生产一种新产品,项目总投资350万元,项目寿命期5年,期末无残值,采用直线法计提折旧。经预测,项目投产后每年可销售产品85000台,产品单价40元/台,单位变动成本20元/台,年付现固定成本50万元,企业的基准收益率为10%,所得税税率33%。此时,各年的折旧额相同,假设各年的销售量、产品单价、单位变动成本、付现固定成本以及折旧均相同,可以利用上述公式直接导出动态盈亏平衡销售量保本销售量的计算公式如下：根据此公式计算出保本销售量为76664件,如图8-2所示,单元格E4中的保本销售量计算公式为：=E3+B3/PV-G3*A3/B3/*10000。图8-2 投资项目的动态盈亏平衡分析但实际上,各年的销售量受到企业部及市场条件的影响,它们是不同的,故可以利用规划求解工具来计算各年的保本销售量,步骤如下：1如图8-2所示,将单元格C10:G10作为可变单元格,存放各年的保本销售量。2在单元格B7中输入公式= -A3,在单元格C7:G7中输入净现金流量计算公式=C9:G9/10000*-E3*+SLN*G3数组公式输入。3在单元格H7中输入净现值计算公式=NPV+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-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%,而滚动条控制按钮的值的变化围为050,为了使滚动条控制按钮的变化表示为百分数的变化,这里将控制按钮的值除以100后再减去25%,则每次单击滚动条两端的箭头,单元格C4:C10中的变动百分比就变化1%,而当滚动条在中间位置,变动百分比恰好为零。7选取单元格D4:D10区域并输入变化后数值的计算公式=B4:B10*数组公式输入。8在单元格C15中输入静态盈亏平衡销售量计算公式=SLN+D6/*10000。9在单元格C16中输入动态盈亏平衡销售量计算公式：=D6+D4-PV*SLN*B13-D5/B11/PV/*10000。这里,当项目各年的销售量、付现成本、产品单价、单位变动成本、折旧直线法相等、且考虑固定资产残值回收时,动态盈亏平衡销售量计算公式为式中,S为期末固定资产残值。这样,就建立了投资项目盈亏平衡分析模型。每次单击滚动栏两端的箭头或用鼠标拖曳滑块,变动百分比就变化1%,当在滚动框与滚动条之间单击滚动条时,变动百分比就以10%变化。则通过不同的因素变化可以了解投资项目盈亏平衡销售量的变化情况,有利于企业管理者进行决策。第8章 Excel在投资项目不确定性风险分析中的应用8.2 敏感性分析 敏感性分析是投资决策中常用的一种重要的分析方法。它是用来衡量当投资方案中某个因素发生了变动时,对该方案预期结果的影响程度。如果某因素在较小围发生了变动就会影响原定方案的经济效果,即表明该因素的敏感性强；如果某因素在较大围变动时才会影响原定方案的经济效果,即表明该因素的敏感性弱。通常要作敏感性分析的因素有：1投资额,包括固定资产投资和追加的流动资产投资。2项目寿命期。3产品的产销量。4产品价格。5经营成本,特别是其中的变动成本。6项目寿命期末的设备残值。7折现率。在长期投资决策中,敏感性分析通常用来研究有关投资方案的现金净流量或固定资产寿命发生变动时,对该方案的净现值和部收益率的影响程度。同时,它也可以用来研究有关投资项目的部收益率变动时,对该方案的现金净流量或使用年限的影响程度。敏感性分析有助于企业领导了解在执行决策方案时应注意的问题,从而可以预先考虑措施与对策,避免决策上的失误。8.2.1 一般的敏感性分析方法例8-5图8-7为某一投资方案的有关资料,所采用的数据是根据对未来可能出现的情况预测的,未来的投资额、付现成本和销售收入都有可能在30%的围变动。试对这三个因素做敏感性分析。企业采用直线法计提折旧,基准收益率为15%。图8-7 不确定性因素对净现值的影响一般性的敏感性分析方法和步骤如下：1设计如图8-7所示的分析表格。2在单元格B10:H10中输入投资额变动对净现值的影响计算公式：=PVE3,10,-*+SLNB3*,D6,10*F3+D6/10-B3*。3在单元格B11:H11中输入销售收入变动对净现值的影响计算公式：=PVE3,10,-C4*-C5*+SLN*F3+D6/10-B3。4在单元格B12:H12中输入付现成本变动对净现值的影响计算公式：=PVE3,10,-C4-C5*+SLN*F3+D6/10-B3。以上各单元格区域的公式输入均为数组公式输入,则计算结果如图8-7所示。对计算结果绘制分析图如图8-8所示,步骤如下：图8-8 敏感性分析图1选取单元格区域A9:H12,单击工具栏上的图表向导按钮,在图表向导4步骤之1图表类型对话框中,图表类型选XY散点图,子图表类型选平滑线散点图,单击下一步按钮。2在图表向导4步骤之2图表源数据对话框中,不做任何输入,单击下一步按钮。3在图表向导4步骤之3图表选项对话框中,在图表标题栏中输入敏感性分析图,在数值轴栏中输入不确定性因素变动幅度,在数值轴栏中输入净现值,单击下一步按钮。4在图表向导4步骤之4图表位置对话框中,不做任何输入,单击确定按钮。5对图表的大小、坐标数值、标题等格式进行调整,使图表赏心悦目,则图表制作即告完成。可见,销售收入对净现值的影响最大,付现成本其次,而投资额的影响最小。然后可以利用单变量求解工具求出当净现值为零时每个不确定性因素的变动数值,方法是：在J10中输入公式=PVE3,10,-*+SLN B3*,D6,10*F3+D6/10-B3*,并将J10作为目标单元格,I10作为可变单元格,即可利用单变量求解工具计算出净现值为零时的投资额最大变动率。用同样的方法可以求出净现值为零时的销售收入和付现成本最大变动率。可见,当销售收入和付现成本不变时,投资额增加到41.64%以上时会使方案变得不应被接受；当投资额和付现成本不变时,销售收入低于预期值的11.48%以上时会使方案变得不应被接受；而当投资额和销售收入不变时,付现成本高于预期值18.79%以上时会使方案变得不应被接受。因此,三个因素的敏感性由强到弱的排序依次为：销售收入、付现成本和投资额。8.2.2 投资项目净现值敏感性分析模型我们也可以参照前面所述的投资项目盈亏平衡分析模型,来建立投资项目净现值敏感性分析模型。例8-6建立投资项目净现值敏感性分析模型。如图8-9所示,这里最大变化区间取50%,在设置控件格式对话框的当前解栏中输入50,最大值栏中输入100,页步长栏中输入5。其他项目的计算方法同投资项目盈亏平衡分析模型。定义一个名为净现值的自定义函数,其语法为：净现值。自定义函数可以通过一小段程序对其参数及参数之间的关系进行描述,这种程序又称过程代码。净现值自定义函数的过程代码如下：Public Function 净现值净现金流量 = 年销售量 * / 10000 - 年付现固定成本 * + / 寿命期 * 所得税税率图8-9 投资项目净现值敏感性分析模型净现值 = 净现金流量 * 1 - -寿命期 / 基准收益率 - 初始投资 + 期末残值 / 寿命期End Function在单元格A16中输入预计净现值计算公式=净现值,步骤为：单击工具栏的粘贴函数按钮,选择用户定义,选中净现值函数,出现该函数对话框,输入相应的容即可,并复制到单元格B16中；在单元格C16中输入公式=B16-A16,在单元格D16输入公式=C16/A16。这样,就得到了多因素变动对净现值的综合影响结果。设计单因素变动影响分析表格,如图8-9所示,在单元格B19:B26中输入公式=D4:D11数组公式输入,在单元格C19:C26中分别粘贴各个因素单独变动时的净现值计算函数如下：单元格C19：净现值单元格C20：净现值单元格C21：净现值单元格C22：净现值单元格C23：净现值单元格C24：净现值单元格C25：净现值单元格C26：净现值在单元格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 - 年付现成本 * + / 寿命期 * 所得税税率 x1 = 0.110 jxz = 净现金流量 * 1 - -寿命期 / x1 - 初始投资 + 期末残值 / 寿命期 x2 = * - 1 If Abs = 0.0000000001 Then 部收益率 = x2 Else x1 = x2: GoTo 10End Function这样,就可以分析不同因素变动对部收益率的影响。图8-10为投资项目部收益率敏感性分析模型。其中单元格B14中的计算公式为=部收益率；单元格D14中的计算公式为=部收益率,单元格C17:C23中的计算公式分别如下：图8-10 投资项目部收益率敏感性分析模型单元格C17：=部收益率单元格C18：=部收益率单元格C19：=部收益率单元格C20：=部收益率单元格C21：=部收益率单元格C22：=部收益率单元格C23：=部收益率其他各单元格的计算公式可参阅例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+C6:G6/11+C4:G4数组公式输入；在单元格C9中输入净现值期望值计算公式=SUMPRODUCT,在单元格C10中输入净现值方差计算公式=SUMPRODUCTC3:G3,2,在单元格C11中输入净现值标准差计算公式=SQRT,在单元格C12中输入变异系数计算公式=C11/C9,从而得到该方案的有关计算结果数据,如图8-11所示。可见,该方案的净现值期望值大于零,是可行的,但风险也较大。8.3.1.2 各年净现金流量相关情况下的独立项目概率分析概率树分析法例8-9某企业拟投资开发一项专有技术,其初始投资为12万元,该项技术预计在3年有效,3年每年为企业带来的现金流量是不确定的,其有关资料如表8-1所示。该企业的资本成本为15%,试对该投资项目的可行性进行评价。表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中的数据填列在Excel上,其格式如图8-12所示,则具体计算步骤如下：1在单元格G6中输入第一种现金流序列情况下的净现值计算公式=NPV-$B$3,然后将此单元格复制到单元格G7G19。2在单元格H6:H19中输入联合概率计算公式=B6:B19*D6:D19*F6:F19数组公式输入。3在单元格I6中输入净现值期望值计算公式=SUMPRODUCT。4在单元格J6中输入净现值标准差计算公式=SQRTSUMPRODUCTH6:H19,2。5在单元格J6中输入净现值为负的概率计算公式=SUMIFG6:G19,。可见,该项目的期望净现值为5.21万元,净现值为负的概率很小。只有0.144,该项目的获利能力较高而风险不大,故该项目是可行的。图8-12 投资项目概率分析8.3.2 互斥项目的概率分析在此种情况下,可分别计算各个项目的净现值期望值、标准差及变异系数,对它们进行综合分析,以确定最优方案。例8-10某企业有A、B两个投资项目,其投资额及未来各年可能的净现金流量如图8-13所示,假设各年的净现金流量相互独立。那么,企业应选择哪个项目？图8-13 各年现金流独立的互斥方案的概率分析如图8-13所示,分析决策过程如下：1在合并单元格G5G7中输入项目A第1年的净现金流量期望值计算公式=SUMPRODUCT,然后将此合并单元格分别复制到合并单元格G8G10、G11G13、G14G16中,得到项目A各年的净现金流量期望值。2在合并单元格H5H7中输入项目B第1年的净现金流量期望值计算公式=SUMPRODUCT,然后将此合并单元格分别复制到合并单元格H8H10、H11H13、H14H16中,得到项目B各年的净现金流量期望值。3在合并单元格I5I7中输入项目A第1年的净现金流量标准差计算公式=SQRTSUMPRODUCTC5:C7,2,然后将此合并单元格分别复制到合并单元格I8I10、I11I13、I14I16中,得到项目A各年的净现金流量标准差。4在合并单元格J5J7中输入项目B第1年的净现金流量标准差计算公式=SQRTSUMPRODUCTE5:E7,2,然后将此合并单元格分别复制到合并单元格J8J10、J11J13、J14J16中,得到项目B各年的净现金流量标准差。5在单元格C20中输入项目A的净现值期望值计算公式=NPV+G4,在单元格C21中输入净现值标准差计算公式=SQRTSUMI5:I162/数组公式输入,在单元格C22中输入变异系数公式=C21/C20；这里,整个项目的净现值标准差现值的计算公式为6同样在单元格D20、D21、D22中分别输入项目B的净现值期望值、标准差和变异系数计算公式=NPV+H4、=SQRTSUMJ5:J162/数组公式输入和=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第25行输入后,再将其复制到A26A5024单元格中,然后在B25:B5024单元格输入对应的销售量公式：=VLOOKUP数组公式输入,共运算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,然后在D25:D5024单元格输入对应的单价公式：=VLOOKUP数组公式输入。4在E25:E5024单元格输入第1年单位变动成本的随机数计算公式：=RANDBETWEEN,然后在F25:F5024单元格输入对应的单位变动成本公式：=VLOOKUP数组公式输入。5在G25:G5024单元格输入第1年付现成本的随机数计算公式：=RANDBETWEEN,然后在H25:H5024单元格输入对应的付现固定成本公式：=VLOOKUP数组公式输入。6在I25:I5024单元格输入第1年净现金流量计算公式：=B25:B5024*-H25:H5024*+B20/3*F20数组公式输入,这里采用直线法计提折旧,年折旧=$B$20/3。第2年、第3年的净现金流量分别放在单元格R25:R5024和单元格AA25:AA5024中,而净现值则存放在AB25:AB5024中,其计算公式为：=I25/+R25/2+AA25/3-$B$20可先输入AB25单元格,然后再复制到AB26AB5024单元格中。7最后的模拟计算结果存放在单元格K1:M7中,如图8-16所示,各单元格的计算公式为：单元格M2=AVERAGE,单元格M3=STDEV,单元格M4=MAX,单元格M5=MIN,单元格M6=M3/M2,单元格M7=COUNTIFAB25:AB5024,/COUNT。图8-16 模拟计算结果在模拟计算完毕后,还可以利用Excel的绘图工具绘制净现值概率分布图,具体步骤如下：1首先对净现值进行分组,以便于进行统计分析,分组结果存放在单元格O4:O17中。2设计图表上净现值的分区,存放在单元格P4:P17中。3再选取单元格区域Q4:Q16,输入FREQUENCY函数：=FREQUENCY/5000数组公式输入,即可得到净现值概率分布结果,如8-17所示。然后插入直方图,做法是：选择单元格区域P4:Q17,在图表向导中选柱形图,再依据有关说明输入有关资料。净现值概率分布图如图8-18所示。图8-17 净现值概率分布统计图8-18 净现值概率分布图根据计算结果及净现值概率分布图,就可以对投资项目的不确定性进行分析。由结果可以看出,该项目的净现值期望值为10642元,标准差8823元,净现值为负的概率为0.112,故该项目的投资风险还是比较小的。该项目的净现值主要集中在0 20000元,概率为0.74,其中在5000 15000元的概率为0.42；而净现值为负时主要集中在-50000之间,概率为0.08。由图8-18还可以看出,该项目的净现值大致符合正态分布。8.4.2 互斥项目的蒙特卡罗模拟在对互斥方案进行比较时,可对每个方案进行蒙特卡罗模拟,计算出其净现值及其概率分布,并绘制概率分布图,进行综合比较分析,选择最优方案。例8-12以例8-7的有关资料为例,其模拟准备、模拟计算过程、模拟计算结果如图8-19所示。共进行6000次模拟计算,即从第21行到6020行。表中仅列举了三次模拟,即第21到23行,其中,随机数计算公式为= RANDBETWEEN ,项目A第1、2、3、4年的净现金流量计算公式分别为：=VLOOKUP、=VLOOKUP、=VLOOKUP和=VLOOKUP ；项目B第1、2、3、4年的净现金流量计算公式分别为：=VLOOKUP、=VLOOKUP、=VLOOKUP和=VLOOKUP 。图8-19 项目A、B的净现值概率分布计算项目A、B的净现值时,需先在单元格I21和R21中分别输入项目A、B的净现值计算公式：=NPV+$F$3和=NPV+$J$3,然后再复制到其他各行。项目A、B的净现值概率分布图如图8-20所示,其制作方法同上。图8-20 项目A、B的净现值概率分布图由模拟计算结果可知,项目A风险较低但收益也低,而项目B风险较高但收益也高。但两个项目的变异系数相差并不是很大,且净现值为负的概率也都较小分别为0.087和0.118,而从净现值分布来看,项目A的净现值主要分布在0300万元概率为0.85,项目B的净现值则主要分布在0900万元概率为0.84但比较分散,由此可见,项目B获利能力要远大于项目A,因此选择项目B要优于项目A。第8章 Excel在投资项目不确定性风险分析中的应用8.5 风险型投资项目组合决策 在实际投资活动中,企业往往面临着在多个风险型投资项目之间做出投资组合的决策。企业究竟选取哪些项目构成一个投资组合,取决于各个投资项目的收益与风险水平以及它们之间的相关程度,因为它们的相关程度直接影响到投资组合的风险大小。因此,在对风险型投资项目进行投资组合决策时,不仅要考虑投资组合所能带来的收益大小,还要考虑投资组合的风险高低,在收益与风险之间做出权衡。8.5.1 风险型投资项目的投资组合决策方法风险型投资项目的组合是否可取,取决于该组合的期望净现值、均方差和变异系数的大小。设有n个投资项目构成投资组合,其中项目i在第t种情况下的净现值为NPVi,t,发生的概率为Pt,m为发生概率的次数,则项目i的期望净现值为,那么投资组合的期望净现值NPV和方差s2分别为 式中,si2为项目i的方差；Cov为项目i和j的净现值之间的协方差,分别计算如下：这样,投资组合的收益与风险的平衡可用变异系数均方差s除以期望净现值NPV来表示： 在进行投资组合决策时,一般取变异系数最小的组合为最佳组合。若几个组合的变异系数相差不大,则可以根据每个组合的期望净现值NPV的大小进行取舍。8.5.2 应用举例例8-13图8-21为A、B、C、D四个投资项目的有关资料,每个项目各年的净现金流量完全相关,即第一年净现金流量很好的情况下,第二、三、四年的净现金流量也很好；反之,第一年净现金流量较差的情况下,第二、三、四年的净现金流量也较差。企业的基准收益率为15%,资金限额为200万元。则可以计算出各项目的期望净现值、方差及均方差,如图8-22所示,各个项目净现值间的协方差如图8-23所示,不同的投资组合下的净现值期望值、均方差以及变异系数如图8-23所示。图8-21 4个项目的有关资料图8-22 4个项目的不同状态下的净现值、净现值期望值、方差、均方差图8-23 4个项目之间的协方差、不同组合的净现值期望值、均方差及变异系数有关计算公式如下：在图8-22中,项目A在很好状态下的净现值存放在单元格B15,计算公式为：=NPV+$B$5,将此单元格复制到单元格C15F15中,可得到项目A在其他状态下的净现值；项目A的净现值期望值为单元格G15：=SUMPRODUCT,方差为单元格H15：=SUMPRODUCT$B$14:$F$14,2,均方差为I15：=SQRT。其他项目可依此计算。项目A与A之间的协方差存放在单元格K13中,计算公式为：=SUMPRODUCT,将此单元格复制到单元格L13N13中,即得项目A与B、A与C、A与D之间的协方差。其他项目之间的协方差依此方法计算。投资组合计算中,以A+B为例,使用资金合计为单元格P13：=ABS,净现值为单元格P14：=G15+G16,均方差为单元格P15：=SQRT,变异系数为单元格P16：=P15/P14。其他组合的计算以此类推。在单元格P18中输入公式=INDEXP12:U12,MATCHMIN, P16:U16,0,寻找变异系数最小的投资组合。可见,在资金限额的几种投资组合中,组合B+D的期望净现值较高,变异系数最小,故应选择投资组合B+D,可以得到期望净现值113.91万元,净现值均方差71.82万元,变异系数0.6305。30 / 30