应用Excel建立投资项目敏感性分析模型

□（郑州升达经贸管理学院会计系河南郑州 451191）

敏感性分析是对投资项目进行不确定性分析的内容之一。敏感性分析包括单因素敏感性分析和多因素敏感性分析，进行决策时需结合经验判断选择对项目影响比较大的关键因素进行分析。实务操作中，敏感性分析计算量比较大，借助Excel分析工具，能够高效提升投资项目敏感性分析水平。

一、设计Excel敏感性分析表格和内容

Excel模型设计与决策内容相关，敏感性分析的基本要求是依据具体问题可以用净现值或内含报酬率进行单因素和多因素敏感性分析，单因素分析需计算敏感系数，多因素分析需要测算多种影响因素同时变动对评价指标的影响。

（一）案例资料。某企业拟投资甲项目，经可行性分析，有关资料如下：（1）该项目需固定资产投资100万元，第一年初全部投资到位，第一年末该项目全部竣工交付使用。（2）该项目投产时需垫支营运资金60万元，用于购买原材料、支付工资以及存货增加占用等。（3）该项目经营期预计为5年（不含建设期），固定资产按直线法计提折旧。预计残余价值为10万元。（4）根据有关部分的市场预测，该项目投产后每年销售收入均为95万元，每年的付现成本均为46万元。（5）该项目筹资的资金成本率为12%，该企业适用所得税税率为25%。要求：运用净现值对投资项目进行敏感性分析。

（二）设计表格和内容。在设计表格格式和内容时尽量考虑使用者的方便和便于理解，敏感性分析模型整体效果如图1所示。

图1内容共有三部分：（1）影响净现值因素变动分析；（2）影响净现值因素对净现值的影响；（3）每年现金流计算表。影响净现值因素变动分析部分主要包含变动因素当前值、变动比率和变动前后数值；影响净现值因素对净现值的影响分多因素和单因素，单因素分析包含净现值变动幅度和敏感系数等；每年现金流量计算表需分年列示计算现金流所需要的其他数据及不同影响因素变动时每年的现金流量：当前现金流量，仅投入、收入或成本中一个因素变动时的现金流量，三个因素同时变动时的现金流量。

图1 投资项目敏感性分析模型

二、设置Excel敏感性分析计算公式

基准收益率为该项目投资的资金成本率12%，图1中除比率外的数据单位为“万元”，图中各个部分已知数据直接录入。以Excel 2010为基础，图中其他需设置公式和表单控件的具体操作如下。

（一）影响净现值因素变动分析。

1.设置数值调节滚动条。选择“开发工具→控件→插入”命令，在“表单控件”中选择滚动条（窗体控件），将其拖到合适的位置，设置合适的大小，然后放手。在本模型中，需要3个滚动条，最好分别放在D6、D7、D8单元格上，如图1所示。固定资产投入滚动条：选中D6单元格上的滚动条，右键单击，打开“设置控件格式”对话框；设置当前值5000、最小值0、最大值10000、步长1、页步长100、单元格链接为D6单元格，如图2所示。同样的方法设置调节销售收入、付现成本滚动条，其单元格链接分别为D7、D8。若在功能区选项卡中没有“开发工具”选项，可以通过“文件→选项”命令，打开“Excel选项”对话框：选择“自定义功能区”，在“主选项卡”中选中“开发工具”选项即可显示“开发工具”选项卡。

图2 设置控件格式

2.设置变动百分比。影响因素变动的幅度通过滚动条进行调节，进而计算变动后的数值。设置变动百分比计算公式，F6单元格：=D6/10000-50%，将该公式复制到 F7、F8单元格；设置变动后的数值，H6单元格：=C6*(1+F6)，将该公式复制到H7、H8单元格。

（二）每年现金流量计算表。计算净现值需要计算影响因素变动前后的现金流量，考虑到资金的时间价值，现金流量的计算需要逐年进行。计算现金流量所需的其他数据，垫支营运资金、固定资产残值和营运资金收回数据等直接根据时间点填表即可。在本模型中，现金流量计算公式：（销售收入-付现成本-固定资产折旧）×（1-25%）+固定资产折旧-投资支出+固定资产残值收入+收回垫支的营运资金，投资支出包括固定资产投资支出和垫支的营运资金，残值收入和回收营运资金一般发生在项目结束，据此计算公式如下：

第一年初只有投入，C23、C25、C26 单元格：=0-C6，当前现金流量、固定资产投入不变，其他因素变化时，投入需用当前值计算；C24、C27单元格：=0-H6，固定资产投入变化及多因素变动时，投入需用变化后的值计算。第二年初只有垫支的营运资金，D23、D24、D25、D26、D27 单元格公式均为：=0-60，因为案例中垫支营运资金均为60万元。

第二年末到第五年末：

第六年计算公式在第二至第五年计算公式基础上加上I21和I22即可，比如I23单元格：=($C$7-$C$8-SLN($C$6,10,5))*(1-25%)+SLN($C$6,10,5)+I21+I22。

（三）影响净现值因素对净现值的影响。获得每年的现金流量和基准收益率后就可以计算投资项目的净现值。影响净现值因素变动分析中净现值计算公式C9单元格：=NPV(C3,C23:I23)。单因素变动时计算公式C14单元格：=NPV($C$3,C24:I24)，D14 单元格：=(C14-$C$9)/$C$9，F14 单元格：=D14/F6；选中C14至F14单元格，将公式复制到C16至F16单元格。多因素变动时计算公式G14单元格：=NPV(C3,C27:I27)，H14 单元格：=(G14-C9)/C9。

三、Excel敏感性分析模型的应用

调节固定资产投入、销售收入和付现成本三个影响因素的滚动条，使其变动百分比为1%，敏感性分析结果如上页图1所示，可以看出，当前情况下，三因素敏感性程度依次是销售收入、付现成本和固定资产投入。应用模型解决实际问题时，有以下两个方面需要注意。

（一）敏感性分析内容。敏感性分析有单因素和多因素分析两种，单因素敏感性分析揭示的是某一不确定性因素发生变化时，对投资项目评价的影响，通过计算敏感系数，找出关键敏感因素。本模型中选取的是固定资产投入、销售收入和付现成本三个因素进行敏感性分析，实务中有两个方面可以改进：（1）通过调整公式和表格，细化敏感因素，比如付现成本可以细化到材料成本和人工成本等；（2）增加敏感因素，只要对投资项目评价有影响的因素都可以通过本模型单因素分析，计算其敏感性程度，从而找出三至四个关键因素做进一步的敏感性分析。

实际经济环境中，往往多个敏感因素同时发生变化、有些因素之间也是有关联的，这时就需要进行多因素敏感性分析，本模型也能满足这方面的需求。

（二）影响投资项目评价因素变动分析。影响因素变动的幅度往往不一致，有些变动因素之间也是有关联的，在具体问题具体分析时通过调节滚动条均能满足要求。在本模型中设计敏感因素变动率时，变动范围是[-50%，50%]，调节滚动条每次变动幅度有0.01%、1%两种，能够满足敏感性分析要求；若有特殊变动率要求，可以通过改变公式实现。

另外，敏感性分析评价指标常采用的有净现值和内含报酬率，本模型中选用的是净现值（NPV），在不能确定基准收益率时，可以把模型中净现值函数改为内含报酬率函数（IRR），通过测算项目本身能达到的收益率进行决策。