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

 

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

 

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

 

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

 

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

 

第一步：将“项目投资现金流量表”复制到sheet2，将sheet2命名为“敏感性分析表”，表1“项目投资现金流量表”更名为“表2项目投资现金流量表（含基本系数）”。

 

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

 

第三步：利用基本系数乘以表2中的各项营业收入、建设投资、营运资金和运营成本，将基本系数与表2连接起来。公式如下：

 

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

 

第四步：利用模拟运算表得到不确定性±NPV和IRR(图2)在20%的变化范围内。固定资产投资的计算，设置从0.8、从0.85到1.2共有9个变化区间。

 

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

 

计算垫支流动资金，计算E23：E275区域复制E27：N29区域，将模拟运算表中引用行的单元格改为C24即可完成，同样的营业收入和运营成本见图3。

 

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

 

将第三步模拟运算表中的计算结果直接复制到图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显示，a项目的营业收入最为敏感。当营业收入从20%下降到20%增长时，IRR从2.52%上升到19.47%，NPV从-54.12上升到79.72，其次是运营成本、建设投资和营运资金。通过比较变化范围，发现a项目的收入下降了5%、经营成本增加10%、当固定资产投资增加15%时，IRR和NPV是不可行的。因此，投资者在做出投资决策时，应注重营业收入、经营成本和固定资产投资实际可能的变化率，并考虑其风险。