《excel在投资项目不确定性风险分析中的应用》由会员分享，可在线阅读，更多相关《excel在投资项目不确定性风险分析中的应用（42页珍藏版）》请在金锄头文库上搜索。
1、编号：时间：2021年x月x日书山有路勤为径，学海无涯苦作舟页码：第1页 共1页第8章 Excel在投资项目不确定性风险分析中的应用8.1 盈亏平衡分析(1)盈亏平衡分析的原理就是根据量本利之间的关系，计算项目的盈亏平衡点的销售量，从而分析项目对市场需求变化的适应能力。一般来说，盈亏平衡点是指企业既不亏又不盈或营业利润为零时的销售量。根据是否考虑资金的时间价值，盈亏平衡分析又可分为静态盈亏平衡分析和动态盈亏平衡分析。8.1.1 静态盈亏平衡分析静态盈亏平衡分析是在不考虑资金的时间价值情况下，对投资项目的盈亏平衡进行分析。当某年的营业利润为零时，可以得到该年盈亏平衡点的销售量为（这里假设只有一种
2、产品）：式中，Qt为第t年的盈亏平衡点销售量（又称保本销售量）；Ft为第t年的固定成本，这里假设非付现固定成本只有折旧，即Ft = Dt + Fc，Dt为第t年的折旧；Fc为付现固定成本；p为产品单价；v为产品的单位变动成本，并假设各年的付现固定成本、产品单价和产品的单位变动成本均不变。当产销量低于盈亏平衡点销售量时，投资项目处于亏损状态，反之，当产销量超过盈亏平衡点销售量时，项目就有了盈利。当企业在盈亏平衡点附近经营，即销售量接近于Qt时，投资项目的经营风险很大，或经营上的安全程度很低，销售量微小的下降都可能使企业发生亏损。单一产品的盈亏平衡分析比较简单。根据给定的各年的付现固定成本、折旧、
3、产品单价和单位变动成本，即可由上述公式计算出各年的静态保本销售量。当一个投资项目同时生产多种不同的产品，或对一个生产多种产品的整个企业进行盈亏平衡分析时，则需要考虑多品种产品的情况。在进行多品种盈亏平衡分析时，加权平均法是较常用的一种方法。【例8-1】某企业生产A、B、C三种产品，A产品年销售量100000件，单价10元/件，单位变动成本8.5元/件；B产品年销售量25000台，单价20元/台，单位变动成本16元/台；C产品年销售量10000套，单价50元/套，单位变动成本25元/套；全厂固定成本300000元。根据以上资料，可以建立分析表格如图8-1所示。有关计算分析公式如下：销售收入=销售
4、量单价边际贡献=销售量（单价单位变动成本）边际贡献率=边际贡献销售收入销售比重=某产品销售收入全厂各产品销售收入合计全厂综合边际贡献率=某产品边际贡献率该产品销售比重全厂综合保本额=全厂固定成本全厂综合边际贡献率某产品保本额=全厂综合保本额该产品销售比重某产品保本量=某产品保本额该产品单价图8-1 某企业的多品种盈亏平衡分析输入已知数据及定义完公式后，即可马上计算出各个可变单元格的数值来，即全厂综合保本额1200000元，产品A、B、C的保本额分别为600000元、300000元和300000元，保本量分别为60000件、15000台和6000套。各单元格的计算公式为单元格E3:E5 ：“=
5、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就可分析
6、它们对盈亏平衡点的影响。8.1.2 动态盈亏平衡分析8.1.2.1 独立项目的动态盈亏平衡分析静态盈亏平衡分析没有考虑资金的时间价值、所得税、利率，以及通货膨胀等因素的影响，由此计算出的盈亏平衡点销售量仅仅能使项目的当期达到盈亏平衡，却并不能保证项目的净现值恰好为零。在考虑资金的时间价值和所得税等因素的条件下，项目的动态盈亏平衡点就是项目净现值为零的那一点，即动态保本销售量就是使项目净现值为零的销售量。考虑单一产品的情况，令NPV=0，则可得到项目各年的动态保本销售量的计算公式为式中，T为所得税税率；Qt为各年的保本销售量；pt、vt和Fct分别为各年的产品单价、单位变动成本、付现固定成本；i
7、为项目的基准收益率；I为初始投资（并假设在第0年一次性投入项目）。这样，利用上述公式，即可分析各种情况下项目各年保本销售量的变化情况。【例8-2】某企业准备投资生产一种新产品，项目总投资350万元，项目寿命期5年，期末无残值，采用直线法计提折旧。经预测，项目投产后每年可销售产品85000台，产品单价40元/台，单位变动成本20元/台，年付现固定成本50万元，企业的基准收益率为10%，所得税税率33%。此时，各年的折旧额相同，假设各年的销售量、产品单价、单位变动成本、付现固定成本以及折旧均相同，可以利用上述公式直接导出动态盈亏平衡销售量（保本销售量）的计算公式如下：根据此公式计算出保本销售量为7
8、6664件，如图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
9、”（数组公式输入）。（3）在单元格H7中输入净现值计算公式“=NPV(F3,C7:G7)+B7”。（4）单击工具菜单中的【规划求解】项，出现【规划求解参数】对话框，其中【设置目标单元格】选“$H$7”，【等于】选“0”，【可变单元格】选“$C$9:$G$9”；单击【求解】按钮，即得各年的保本销售量，如图8-2所示。当可变单元格C9:G9中赋予不同的初始值时，会得到不同的保本销售量,如图8-2所示），各年的销售量变化范围很大，这正反映了动态盈亏平衡的特点。实际上，各年保本销售量组合有无数个，故为求得符合实际情况的保本销售量组合，还要考虑一些约束条件，比如各年的会计利润应大于零，各年取得的现金收入
10、应能够偿还各年到期的债务以及支付股利，等等。8.1.2.2 互斥项目的动态盈亏平衡分析在需要对若干个方案进行比较的情况下，若是某一个共有的不确定性因素（比如销售量、产量、寿命、产品价格、单位变动成本等）影响这些方案的取舍，则可以利用盈亏平衡分析帮助决策。【例8-3】某企业投资生产某种产品，现有两个方案，有关资料如图8-3所示，该产品的市场寿命具有较大的不确定性，如果基准收益率为15%，不考虑期末资产残值，那么，企业应如何根据项目的寿命期来选择方案？图8-3 根据方案寿命期选择项目如图8-3所示，进行决策分析的步骤如下：（1）首先在单元格F3中输入“5”，在单元格H3中输入“11”，单元格G3可
11、先空置。（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 投资项目盈亏平衡分析模型除了前
12、面介绍的直接利用公式计算盈亏平衡点保本销售量或设计工作表格进行分析外，我们还可以设计盈亏平衡分析模型来分析每个因素各种可能的变动情况下对保本销售量的影响。【例8-4】投资项目的盈亏平衡分析模型如图8-4所示，具体设计步骤如下：图8-4 投资项目盈亏平衡分析模型（1）首先设计好分析模型结构，如图8-4所示（表中数据以例8-2为例）。（2）单击【视图】，选择【工具栏】，再单击【窗体】，出现【窗体】工具栏，如图8-5所示，单击【滚动条】按钮，然后在工作表的合适位置（这里为E4F4单元格）拖曳出一个矩形【组合框】控件，并调整其大小。图8-5 【窗体】工具栏（3）将鼠标移到新建立的【滚动条】控件上，单击
13、鼠标右键，出现快捷菜单，选择【设置控件格式】，出现【设置控件格式】对话框，选择【控制】项，如图8-6所示。图8-6 【设置控件格式】对话框（4）在【当前解】栏输入25，【最小值】输入0，【最大值】输入50，【步长】输入1，【页步长】输入10，在【单元格链接】填入“E4”，然后单击【确定】按钮，这就建立了初始投资的【滚动条】控件。（5）其他项目的【滚动条】控件可按照上述方法进行。（6）在单元格C4:C10中建立变动百分比与【滚动条】控件的联系，即：在单元格C4中输入公式“=E4/100-25%”，并将单元格C4分别复制到单元格C5:C10中。说明：本例中各因素的变动范围为-25%+25%，而滚动
14、条控制按钮的值的变化范围为050，为了使滚动条控制按钮的变化表示为百分数的变化，这里将控制按钮的值除以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,D
15、5,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）项目寿命期末的设备残值。
