利用Excel自动实现投资项目敏感性分析

利用Excel自动实现投资项目敏感性分析

【摘要】投资项目敏感性分析的Excel实现需解决分别测算问题、测算结果保存问题以及分期投资、收入、经营成本在不同时期不等的问题。文章创新设计了不确定性因素基本系数区域，从而可以运用Excel模拟运算表解决上述三个问题。该设计一次性解决了内部收益率、净现值多次测算、记录等繁琐问题，使投资项目敏感性分析过程得以自动实现。

【关键词】投资项目；敏感性分析；Excel；模拟运算表

投资项目敏感性分析是投资项目决策中常用的一种重要的分析方法，它是通过保持其他假设变量不变，调整某个假设变量的取值，计算改变后的评价指标内部收益率（IRR）或净现值（NPV）的影响，不断重复测算；然后将所有变动结果同基本分析结合起来，根据评价指标的变动程度判断项目的风险大小，并决定项目是否可行。

一、敏感性分析的步骤

（一）确定敏感性分析指标

一般选择项目IRR与NPV指标作为分析对象。

（二）选择不确定性因素（假设变量）

影响项目经济效益的因素很多，敏感性分析通常选择对投资项目资金流量起主要作用的因素，包括项目投资额、营业收入与经营成本。

（三）确定不确定性因素的变化范围

一般选择±20%、±15%、±10%，以5%为间隔，变化范围越大，需要测算的次数越多。

（四）进行敏感性分析并找出敏感性因素

分别对投资额、营业收入、经营成本按变化范围进行测算，得到不同变化范围下的IRR与NPV。如本例不确定性因素的变化范围选择±20%，则需要测算8（变化率）×3（不确定因素）×2（分析指标）共48次，过程较为繁琐。

（五）编制敏感性分析表，绘制敏感性分析图

二、案例资料

甲项目固定资产投资120万元，其中第1年年初和第2年年初投资分别为

70万元和50万元，第1年年末项目竣工并投入试生产；项目生产期10年，固定资产直线法折旧，预计净残值率10%；项目投产时需垫支流动资金50万元，第1年年末支付30万元，第2年年末支付20万元，项目结束时收回；项目投产后第1年营业收入60万元、第2年80万元，第3—8年100万元/年，第9年每年减少为85万元，第10年减少为60万元，项目投产后第1年经营成本38万元，第2年45万元，第3—8年65万元/年，第9年减少到48万元，第10年减少到35万元；该企业适用所得税率为25%，基准折现率10%。根据以上资料，甲项目投资现金流量表（简表）与基本评价结果如表1所示。

为进行敏感性分析，表1中回收固定资产余值（N6）、回收流动资金（N7）、调整所得税等项目都应设计为公式自动计算，因为当投资、收入与经营成本发生变化时，其结果也会发生相应变化。其中公式：

三、运用Excel模拟运算表进行投资项目敏感性分析设计思路

如何利用Excel进行投资项目敏感性分析，现有的做法归纳有两种：一是枚举法。先设计一个NPV与IRR的基本计算表，其中NPV与IRR运用Excel公式计算；然后对每个不确定性因素（假设变量）按变化率分别测算，得到各变量不同变化率的评价结果。这种方法仅解决了NPV与IRR的自动计算问题，但未简化繁琐的测算过程。二是利用Excel“窗体”中的“微调项”工具按钮设置变化程度，通过移动滚动条得到每一变量不同变化程度下的评价指标。这种做法必须假设投资是一次性投入，收入、经营成本在不同的时期保持不变，而现实中大多数项目是分期投资的，收入与经营成本在投产前期受投产率影响逐渐提高，临近项目结束时因产能下降而逐渐下降。本文案例按此情况设计。另外，现有的两种做法测算的结果不能保存，每做一次测算都需要单独记录评价结果。因此在设计中应解决分别测算问题、测算结果保存问题以及分期投资、收入、经营成本在不同时期不等的问题。

Excel模拟运算表是一种只需一步操作就能计算出所有变化的模拟分析工具。它可以显示公式中某些值的变化对计算结果的影响，为同时求解某一运算中所有可能的变化值组合提供了捷径。因此运用Excel模拟运算表能很好地解决敏感分析中的分别测算问题和测算结果保存问题。但如何解决分期投资、收入、经营成本在不同时期不等的问题，则需要单独设计。模拟运算表中引用行或列是对应原计算公式中的一个单元格。显然，表1中投资、收入与经营成本的行引用在NPV与IRR的计算公式中都是多个单元格。因此运用Excel模拟运算表的难点是解决行单元格的引用问题。

为此，本文创新地设置一个不确定性因素基本系数区域，将不确定因素的基本系数乘到基本现金流量表运算公式中，模拟运算表只需将引用行或列单元格设为基本系数就可以解决1个单元格的问题了。

四、运用Excel模拟运算表实现投资项目敏感性分析

第1步：将“项目投资现金流量表”复制到sheet2，将sheet2命名为“敏感性

分析表”，表1“项目投资现金流量表”更名为“表2项目投资现金流量表（含基本系数）”。

第2步：设置不确定性因素基本系数区域（图1）。

第3步：用基本系数乘表2中的每个营业收入、建设投资、流动资金、经营成本，将基本系数与表2联结在一起。公式为：

经过以上处理，项目基本评价结果与表1结果完全一致。

第4步：利用模拟运算表得到各不确定性因素±20%变化区间的NPV与IRR （图2）。对固定资产投资的测算，设置从0.8、0.85到1.2共9个变化区间。

在E24与E25单元格分别输入IRR与NPV的计算公式，E24=$C$18，E25=$C$19，选中E23：N25区域点菜单栏“工具”—“模拟运算表”，在引用行的单元格输入$C$23（固定资产投资的基本系数），就得到了9个变化区间的IRR 与NPV的评价结果（见图2）。

对垫支流动资金测算，将E23：N25区域复制到E27：N29区域，将模拟运算表中引用行的单元格改为C24即完成，同理营业收入与经营成本见图3。

第5步：编制敏感性分析表（图4），绘制敏感性分析图（图5）。

将第3步模拟运算表中的计算结果直接复制到图4中的表3、表4中，就完成了敏感性分析表的编制。公式：

设C41=F24，C42=F28，C43=F32，C44=F36，向右填充，完成表3；设C48=F25，C49=F29，C50=F33，C51=F37，向右填充，完成表4。

根据图4中的表3、表4绘制敏感性分析图，如图5、图6。

从图5、图6结果看，甲项目营业收入敏感程度最高，当营业收入从降低20%到增长20%，IRR则从2.52%上升到19.47%，NPV从-54.12上升到79.72，依次是经营成本、建设投资、流动资金。比较各变化区间发现，甲项目当收入下降5%、经营成本增长10%、固定资产投资增加15%时，IRR与NPV均不可行，因此投资者在进行投资决策时应重点考察营业收入、经营成本与固定资产投资实际可能的变化率，考虑其风险慎重决策。

五、结论

敏感性分析可以反映不确定性因素与项目经济效益的依存关系，有助于投资决策者了解项目在条件发生变化时的各种可能结果，从而可以预先考虑措施与对策，避免决策上的失误。本文所做创新设计一次性解决了内部收益率、净现值多次测算、记录等繁琐问题，使投资项目敏感性分析的过程得以自动实现。