巧用Excel实施项目的敏感性分析(1)(有图)

一、问题的提出 敏感性分析是一种处理风险和不确定性问题的常用 方法， 它是通过分析、预测主要因素变化对评价指标的影 响， 以预测项目所承担的风险。它可以帮助决策者识别投 资机会中最值得注意的地方， 从而为决策者提供制定决策 所需要的重要信息， 指导决策者确定最值得深入研究的因
［ 收稿日期］ ２００７－ ０６－ ０５ ［ 作者简介］ 张静， 中国矿业大学管理学院副教授， 主 要 从 事 技 术 经 济 评 价 与 决 策 、投 资 项 目 评 估 、项 目 管 理 等 方 面 的 研 究 。
主要参考文献
［ １］ 潘 琰 ， 林 琳． 网 络 财 务 报 告 的 基 础 ： ＸＢＲＬ 分 类 账 ［ Ｊ］ ． 财 经 论 丛 ， ２００６， （ １） ： ５０－ ５５．
［ ２］ 杨周南， 赵秀云． 可扩展商业报告语言的发展与应用研究［ Ｊ］ ． 中 国注册会计师， ２００５， （ ２） ： ４０－ ４３．
格式为： ＮＰＶ（ｒａｔｅ， ｖａｌｕｅ１， ｖａｌｕｅ２， …）
（ ４）
式中： ｒａｔｅ 为基准收益率或设定折现率； ｖａｌｕｅ１， ｖａｌ－
ｕｅ２， …为各年净现金流量的数组或对单元格区的引用。
三、应用 Ｅｘｃｅ ｌ 进行敏感性分析实例
本案例为建设期 ２ 年， 生产期 ＩＮＦＯＲＭＡＴＩＯＮＩＺＡＴＩＯＮ ／ ５１
２００８ 年 ２ 月 第 １１ 卷第 ３ 期
中国管理信息化 Ｃｈｉｎａ Ｍａｎａｇｅｍｅｎｔ Ｉｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ
Ｆｅｂ．， ２００８ Ｖｏｌ．１１， Ｎｏ．３
综合多栏账在高校预算管理中的应用
—— —以温州职业技术学院为例
２００８ 年 ２ 月 第 １１ 卷第 ３ 期
中国管理信息化 Ｃｈｉｎａ Ｍａｎａｇｅｍｅｎｔ Ｉｎｆｏｒｍａｔｉｏｎｉｚａｔｉｏｎ
Ｆｅｂ．， ２００８ Ｖｏｌ．１１， Ｎｏ．３
巧用 Ｅｘｃｅ ｌ 实施项目的敏感性分析
张静
（ 中国矿业大学 管理学院， 江苏 徐州 ２２１１１６）
［ 摘 要］ 本文在项目评价系统原理的基础上， 设计了一种用 Ｅｘｃｅｌ 进行敏感性分析的简便方法， 创建了项目评价系 统体系与评价指标之间的计算关系， 当不确定性因素变化时， 财务报表的编制、数据的平衡和评价指标的计算自动 完成， 减轻了敏感性分析的工作量。 ［ 关键词］ Ｅｘｃｅｌ； 项目； 敏感性分析 ［ 中图分类号］ Ｆ２３２； Ｆ２７５ ［ 文献标识码］ Ａ ［ 文章编号］ １６７３－ ０１９４（２００８）０３－ ００５０－ ０３
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
（ 见图 ２ 中的不确定因素与变化幅度表和评价指标一览 表） 。其他因素变化时， 重复上述程序， 直到 ３ 个因素全部 变动完毕， 然后把不确定因素每一次幅度变化后更新的指 标值填入图 ２ 对内部收益率和净现值影响表相应栏目中， 便可找出哪一个因素是敏感因素。从分析可知， 经营成本 为最敏感因素， 其次是销售收入。为了方便寻找， 可分别求 出各因素变化＋１％和－ １％时的指标变化值， 则判断更容易。
根据各因素对项目的重要程度及在预测后采用的数
据准确性的把握程度， 现选择固定资产投资、销售收入和
经营成本 ３ 个因素为不确定因素。３ 个因素的变化幅度分
别取±１０％和±２０％的范围。
３． 主要评价报表与敏感性分析关系设计
该项目的投资现金流量如图 １ 所示。数据主要来自于
投资计划与资金筹措表、总成本费用估算表、销售收入与
［ 收稿日期］ ２００７－ ０６－ １８
高校财务管理的重要组成部分， 越来越受到重视， 已成为 高校财务管理的重要内容。
一、问题的提出 目前， 大多数高校财务采用“经费本”来控制学院经费 预算。每个经费责任人手中都有经费本， 即每报销一笔经 费， 财务都要在“经费本”上登记。经费责任人员可以直接 看到自己可以使用的经费数。但使用经费本有许多不足之 处： 一是在财务账上不能及时反映各部门的预算控制数；
［ ３］ 黄俊民． 我国可扩展商业报告语言研究— ——基于 ＸＢＲＬ 的网络财 务报告［ Ｄ］ ． 广州： 暨南大学， ２００４．
财务管理信息化
（ １） 内部收益率指标
ＩＲＲ＝ ｉ１＋（ ｉ２－
ｉ１ ）
ＮＰＶ１ ＮＰＶ１＋ ＮＰＶ２
（ １）
式中： ＩＲＲ 为内部收益率； ｉ１ 为试算的低折现率； ｉ２ 为
数。
由式（ １） 、式（ ２） 可知， 各指标的计算要先确定净现金
流量的值， 而数据来源于项目投资现金流量表等财务报
表， 因此， 在进行敏感性分析之前， 必须编制出项目评价的
一系列相关的财务报表， 并根据资金筹措、财务条件、还款
方式、市场变化等方面的要求， 对各报表进行平衡计算。项
目评价财务报表的编制是一项复杂的、系统的动态过程，
二、确定性条件下项目评价指标计算模型与相 关函数模型
１． 评价指标计算模型 项目评价最常用的指标有内部收益率（ ＩＲＲ） 和净现值 （ ＮＰＶ） ， 在确定性条件下， 其计算公式分别如下：
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
而敏感性分析是在此基础上进行的， 涉及的数据是共享
的， 不仅量大， 也是动态变化的。
２． Ｅｘｃｅｌ 中的相关函数模型
（ １） 内部收益率指标函数模型
格式为： ＩＲＲ（ｖａｌｕｅｓ， ｇｕｅｓｓ）
（ ３）
式中： ｖａｌｕｅｓ 为一个数组或对数字单元格区的引用；
ｇｕｅｓｓ 为内部收益率的猜测值。
（ ２） 净现值指标函数模型
据项目的投资、销价、销量、税率、利率、借款还本付息方式
等各项资料， 其确定性条件下的财务报表已全部编出， 在
此基础上对该项目进行敏感性分析。
１． 确定分析指标
确定内部收益率指标和净现值指标作为分析指标。确
定性条件下已求出内部收益率为 １０．７８％， 净现值为 １０７ ２０７
元。
２． 设定不确定性因素
税金附加估算表和损益表（ 篇幅所限， 没有全部列出） 。为 使敏感性分析简便快捷， 把评价指标一览表、不确定因素 及变化幅度表和因素变化后对内部收益率和净现值指标 的影响表置于一个工作表内， 见图 ２。
图 １ 项目投资现金流量表（ 截图）
图 ２ 敏感性分析指标计算表（ 截图）
４． 因素变化与敏感性分析计算 项目投资现金流量表（ 图 １） 和 ３ 个因素的变化幅度 （ 图 ２） 以及相对应的其他相关报表相互自动链接成评价系 统， 如第 ４ 年销售收入 １ ２９６ ０００ ＝销售收入、税金及附加 估算表！ Ｈ６ 等。当各因素依据估算的基本数据进行正、负 幅度变化时， 相关数据互相影响， 如固定资产投资变化会 影响到总投资和折旧费， 销售收入变化会影响到销售税金 和附加以及所得税， 经营成本变化会影响到流动资金投资 等， 进而影响到现金流入、现金流出和净现金流量（ 见图 １） ， 从而使内部收益率和净现值指标发生变化。所以， 当 ３ 个因素从变化幅度 ０％开始按照±１０％和±２０％的变动范围 重新赋值时， 其他相关数据则自动更新， 内部收益率的值 和净现值的值则分别由（ ３） 式和（ ４） 式自动求得， 即 ＩＲＲ （ｖａｌｕｅｓ， ｇｕｅｓｓ） ＝ ＩＲＲ （＇ 财务现金流量表 （ 全投资） ＇ ！ Ｄ１５： Ｑ１５）， ＮＰＶ（ｒａｔｅ， ｖａｌｕｅ１， ｖａｌｕｅ２， …） ＝ ＮＰＶ（１０％，＇ 财务现金 流量表（ 全投资） ＇ ！Ｄ１５：Ｑ１５）。例如， 当固定资产投资和经营 成本不变， 销售收入减少 １０％时， 内部收益率由基本数据 １０．７８％降为 ８．７８％， 净现值由 １０７ ２０７ 元降为－ １７０ ９６８ 元
５０ ／ ＣＨＩＮＡ ＭＡＮＡＧＥＭＥＮＴ ＩＮＦＯＲＭＡＴＩＯＮＩＺＡＴＩＯＮ
代表计算链接库， 描述文件包含的元素之间的计算关系， 在 ＸＢＲＬ ＧＬ 中， 因为财务报告各个项目之间的计算关系 在总分类账中并不适用， 所以 ＸＢＲＬ ＧＬ 没有 Ｃａｌｃｕｌａｔｉｏｎ Ｌｉｎｋｓ。Ｄｅｆｉｎｉｔｉｏｎ Ｌｉｎｋ 代表定义链接库， 描述文件中包含的 元素之间的层次结构关系， 例如定义会计账簿元素和账簿 信息元素之间的关系， 并且还可以增加； 利用 Ｓｈｏｗ、Ａｃｔｕ－ ａｔｅ 及 Ｔｉｔｌｅ 属性来指明软件如何显示链接的内容、设定链 接的激活方式以及设定链接的标题。Ｐｒｅｓｅｎｔａｔｉｏｎ Ｌｉｎｋ 代 表展示链接库， 描述文件中包含的元素之间的层次关系， 使用 Ｏｒｄｅｒ 属性来表示元素的展现顺序。另外 Ｌａｂｅｌ Ｌｉｎｋｓ 用于说明不同国家语言的分类标准的各个元素之间的联 系。最终输出的 ＸＳＤ 文件的框架由名称空间、ｉｍｐｏｒｔ 相关 规范的档案、Ｌｉｎｋ、元素声明和数据类型声明组成。
试算的高折现率； ＮＰＶ１ 为 ｉ１ 对应的净现值， 为正值； ＮＰＶ２
为 ｉ２ 对应的净现值， 为负值。
（ ２） 净现值指标
ｎ
ｎ
! ! － ｔ
ＮＰＶ＝ （ＣＩ － ＣＯ） （１＋ ｉ） ＝ Ｆｔ（Ｐ ／ Ｆ， ｉ， ｔ）
（ ２）
ｉ＝０
ｉ＝０
式中： ＮＰＶ 为净现值； ｉ 为基准收益率或设定的折现
率； ＣＩ 为现金流入； ＣＯ 为现金流出； ｎ 为项目计算期； Ｆｔ 为 项目第 ｔ 年的净现金流量； （ Ｐ ／ Ｆ， ｉ， ｔ） 为一次支付现值系
相关分录明细的分类信息， 如会计事项记录日期或交易代 码等； 分录明细主要是由会计科目和金额等资料所组成 的， 如科目代码或借方金额等。为了与国际标准尽可能保 持一致， 方便数据的交换， 所以本文探讨建立的分类标准 采用与 ＸＢＲＬ ＧＬ 一致的层次关系。