Excel财务建模

1、Excel 财务建模第1章概论本书旨在为您提供一系列帮助您开发、利用 和维护EXCeI模型的工具。财务模型的建立通常 被看作只是对会计数字的添加或者是进行这种 添加的方法。但是，本书将会给您展示精良的建 立财务模型的实践操作；提供一些不同的技术要 领并会给您精选出一些模型的模板。本书并不是 一本EXCel使用的工具书，因为关于这一方面已 经有很多深入的手册了，更确切地说，本书是对 一些技术的概述以便为您节省时间，帮助您在财 务管理方面变得更为有效率。1.1v什么是财务建模财务建模涵盖了一个很宽泛的领域：从简单的制表到费用的加总再使之转变为项目所需的 复杂的风险模型。此外，模型的设计还需要考虑

2、很多其它的方面。具体地说，关于财务建模我们 必须考虑：针对具体商业问题的解答建立特殊的 操作程序。如现金流量表及其易变性；对数据进行分析处理；将未来因素纳入模型考虑,对未来的情况进行考察;将数据快速准确地转化为管理信息;在一个“安全”的环境中测试假设，如 项目方案：通过一种结构化的途径来支持管理决策;更准确地认识问题中的相关变量和规则；更多地了解变量的变化过程及其变化 方式；找出关键变量并考察其敏感性O1.2.电子表格的历史电子表格被应用于个人电脑是从20世纪70年代晚期ViSiCalc（专为苹果机使用的一种操作 软件）的使用开始的。由于这种工作表的高效率 和准确性，使之在大范围内迅速取代了一

3、些早期 的方法（如高速计算机），同时，LotUSl-2-3的 使用与IBM个人电脑的使用也同步增加。从此 财务管理者也能用他们自己的数据来进行分析而不用求助于其它数据系统或是系统管理 员了。会计模型，如预算和现金流量，能根据用 户的要求进行建立，这就导致了： 有更详细的信息用于决策制定； 使在较低层次的决策制定成为可能； 对特定环节的检验或其他替代方法之 间具有灵活性。1995 年，微软在 Apple Macintosh 引入了 Excel并在 20 世纪 80 年代后期将它扩展到个人 电脑上。 Windows3.0 版本引入包含了 Excel 的 Office95，随着它的快速增长， Exc

4、el 成为了工作 表操作软件中的领头羊， 被大多数个人电脑用户 所使用。在成功开发 Office97和 Office2000后， 微软在这一领域的占有率又被大大增强。1.3 、工作表的功能Excel 包含于微软工具包之中说明它现在是 一种公认的标准， 就如同人们把 Word 作为文字 处理的标准格式一样。伴随着以下功能的加入， 它的工作表的功能不断的加强：专业的函数； 大量使得工作表自动化的宏程序的使用，或者说用编码进行公式编辑功能的使 用；工作簿技术的使用， 省去了单个工作表 之间的联系的建立；对 Visual Basic的使用提供了一种与微 软其他应用程序之间通用的语言；同其他应用软件之间

5、的数据交换功能； 添加例如关于目标区和最优化问题的 规划求解模型；三部分分析包，如财务 CAD ,RISK or Crystal Ball。今天对这种复杂分析软件包使用的结果是 使得那些非专业程序员也能设计并建立起一套 专业的解决商业问题的应用程序。Excel 也是这样一种分析软件包。大部分人 在他们需要解决一个商业问题的时候都会使用 它。作者曾经有一个这样的经历， 需要对一个项 目的租赁可盈利性进行研究， 并要编写一个模型 来考察不同的基金组合决策。 在耗费了大量的时 间和精力后，这个模型终于成功运行并给出了一 个答案。但是，这个答案很不清楚而且也不方便 其他人去理解。这里并没有模型设计的方

6、法论，而模型真的就那样“蹦出来了”许多公司或学院很少会提供如何使用 Excel 来处理财务问题的指导， 这对于大多数的管理者 来说是司空见惯的了。 这种做法的后果是许多模 型建立都很少或者根本就不考虑模型的设计和 模型未来的维护。更进一步地说， 据估计很多商业上正在使用 的模型本身都存在着严重的错误。用 Visual Basic和C+来编写应用程序是为 IT 部门进行设 计提供的。但是，通常情况下， Excel 并不受这 些影响因素的制约。 这可能并不总是个问题， 但 是，一个预算模型可能成为财务管理者自己的 “宠物设计”，而他可能会在此后离开这家公司 或者可能被提升到新加坡去了。 在文件里当

7、然不 会有注释， 这样，就再没人能了解这个模型是如 何工作的。 人们经常说信息就是力量， 但是因为 这些管理者常常不能将他们的工作充分的备案， 所以导致公司在模型的审核和错误的查找上不 得不花费大量的金钱。这样看来， Excel 的简洁可能也成为了它的 一个弱点。作者强调： Excel 的使用者应该按照 简单设计的步骤进行 ，并且注意对于操作模型相 关背景信息提供的必要性。 在这样的方法的指导 下，现在你在计划上花的时间将从长远来说为您 提供超额收益：可操作性和使用轻松； 可维护性； 对于答案和结果的信心。在接下来的章节里， 我们将会详细地说明如 何综合运用 Excel 模型，来制作功能更强大

8、、更 有活力的工作表。1.4 、本书的目标本书的目标就是展示一系列的 Excel 在财务 建模方面的应用。 这一系列的应用出自于一位致 力于将公司财务理论应用于工作表中长达 10 年 之久的非程序编写员。建模需要了解如何用 Excel 建立模型，并且将财务知识和设计与 Excel 结合在一起，特别是：设计方法和设计过程； 怎样将设计想法变成应用中的模型； 改进现有模型的有用技术； 使简单的模型更有用、更可靠 ； 如何加入风险分析技术；利用最优化和目标策略；将所用的技术结合成一个标准和模板。 操作者们需要将对工作表技术作为核心技 能来理解。现在的公司掌握着越来越多的数据而 且需要用一些低水平的简

9、单分析工具。 通过建立 模型，操作者可以更好地了解：单个变量如何变动； 怎样去找出应该包含在计算中的新变 量；怎样分离出对未来测试的关键变量； 如何通过方案测试和案例分析来避免 代价昂贵的错误。举一个例子，通过对某些会计模型的替换而 建立的简单的外包模型可能显示一个正的净现 金流量。一个正确建立的模型不仅能找到答案， 而且能：列出所有的规则和输入； 提供基于不同参数值的一系列结果； 提供关键变量的图表， 用来显示它们对 于其他变量变动的弹性；列出风险和不确定性的水平； 显示有多大的可能性你将会得到预测的结果。因此，本书的目的是应用 Excel 和财务知识 把二者结合起来， 帮助你建立更强大、

10、更有活力 的工作表。1.5 、工作表举例图 1 1 是设计工作表的一个反面例子。这 张工作表可能是你用来显示一个项目的净现金 流量的。这是在许多公司里使用的众多工作表中 比较典型的一个例子， 它存在一系列的问题， 我 们将在下面一一给出。 这个模型是光盘中命名为 Simple_Model.xls的文件。图 1-1（原书第 7 页 f.g1.1）它的主要问题可以归结为： 没有把对输入、 计算、输出的布局设计 清楚地标明；没有输入部分，不知道模型中的变量有哪些；没有对输入进行特别着色； 没有用边框和阴影来增强报告的外观；没有对输入数据的有效性进行限制， 例如，度；起。确保输入数据具有正确的类型和数

11、据长将不同小数位数的数字格式混合在一 方括号和红颜色的使用能增强此模型， 因为方括号很容易在打印出来的报告上被识别， 而且红色一般是负数的颜色；将数字和公式混合在一起。第 10 行中 的税额计算将税率对于每一个单元格都固定。 如果税率发生变化怎么办？单元格 G10 中有一处计算错误，单元 格本应该是输入公式，但是输入的却是数字；公式测试显示单元格 B10 和 B17 中是 直接输入数字而不是利用公式计算， 当贴现率 或税率发生变动时候，数值不会随之发生变 化；没有根据结果得出的操作报告。 5,411 是否已经是满足了操作的底线；条件格式化能将结果注重显现出来， 例 如，这个单元格的颜色将根据结

12、果确定；没有函数的使用，因为净现值是使用每 个时期的变量值进行计算得出来的。 通过使用NPV 函数可以减少对每个单元格的赋值，从 而减少可能出现的数字错误； 没有敏感性分析。当改变贴现率或者没 有按计划形成利润会有什么情况发生？ 图表一般用来显示管理现金流量或者 显示敏感性分析。 例如，一个累计现金流量表 表示的是可获得的回报； 没有对关键变量进行特别命名； 工作表没有分开显示。 下面的表格中计 算了设备的税收折旧， 但是这并不清楚它是不 是现金流量的一部分； 没有对单个单元格的批注， 也完全没有 对整个模型如何工作进行说明； 作者没有对数字做出解释的信息； 这个模型不适合打印。 模型没有页眉

13、和 页脚指示，例如，文件名和编写日期。打印要 输出包括税收在内的所有结果。以上的列表从模型建立的结构、 设计和方法 上罗列了这个模型的缺点。 如果管理者做出的所 有决策都是基于这样的一类表格， 他很有可能会 做出错误的决策。 即使是很快做出来的， 这个模 型也因为存在计算错误而失败。 如果使用这样的 工作表进行管理可能会造成许多严重的失败， 但 在对这个模型重新设计后 , 可能会纠正这些错 误。建立正确的模型可以避免很多类似的错误。1.6 、小结Excel 的使用是操作者的核心技能。 Excel 是一个功能强大的工具， 但是，极少数的用户接 受过正规的模型技术的培训。 本章还给出了一个 简单的

14、工作表及其在设计和构建方面包含的错 误。在接下来的章节里， 我们会提纲挈领地介绍 如何应用模型来建立有活力和可以维护的工作第 2 章 模型设计导论在第一章里，我们已经看到了传统的模型设 计的缺陷之处。 这种传统设计从本质上说只是将 Excel 用作一大组自动化的会计文件。如果我们 的目的是依据 Excel做出决策或者我们要完全依赖于这个结果， 那么我们必须采用一种不同的途 径，一种更专业化的途径。 这种途径更多地关注 目标、用户报告和结果的产生过程。图 2-1 概括 了设计中的几个阶段。图 2-1模型设计1、所有模型都遵循一定的设计程序和方法；2、确定目标和对象；3、确定用户需求和必要的用户交

15、流；4、列出关键变量和规则；5、将计算分解成若干个可操作的群组；6、制作出单个模板；7、列出菜单结构；8、管理报告和小结；9、模型发展 如灵敏度分析；10、测试和审核；11、应用操作的保护；12、备案；13、征求同行的意见。1.1 、模型设计基础设计是个性化的， 你开发出自己认可的、 欣 赏的模型风格，你就能轻而易举地将其重复运 用。这听起来可能很简单， 但一个正确实用的设 计方法会大大缩短模型设计和错误纠正的时间。 设计的必要性取决于应用的复杂性， 你必须对不 同种类的工作表采用不同的计划和方法。 在模型 设计中，你可能想要多次的插入行或者是删除 列，又或者是在工作的某个阶段想要了解一个具

16、体单元格的公式如何运行。 想要做到不过多考虑 就能正确地使用关键的公式其实是很简单的。 清 楚地列出需要考虑的事项能够便于更好的设计 模型。所有的模型设计都遵循统一的设计步骤和 方法， 并且工作表也遵循一定的设计格式。 本书 中的例子毫无疑问地是按照这样的原则编排和 设计的。 对于一个人来说， 简单的工作表就可能 够用，而模型的设计也必须符合简单的规则， 特 别是模型会被其他人使用或者被合并用于决策 的制定。 在基础的格式中， 这就意味着要将模型 中的功能划分成输入、计算和输出三个区域。2.2 、对象很多人并不对对象和目标进行深入地考虑。 尽管下面说的听起来很简单， 但是这样做确实是 有益的：

17、 将对象和目标记录在文件中， 并在模型 设计的过程中不时地进行参照以保证你并没有 偏离最初的想法。模型常常能给我们提供更多的信息， 但在很 多例子中， 由于答案隐藏在计算中， 所以我们很 难获取信息。 举个例子， 一个简单的现金流量的 预算也能更深地用于记录实际的损益表和资产 负债表。 有了预算的和实际的数字， 在绝对数和 相对数差异基础之上的差异分析报告就可以和 管理报告还有图表一起形成单个的报告。2.3 、用户界面这个需要很仔细的核查， 因为这一项需要你 和你的用户一起完成。 也许对于同一个模型会有 许多不同的用户， 他们对于输入、 其他一些细节 和信息有不同的要求。 早期建立的模型有时会

18、将 变量放在左边，在标签和数字之间，例如税率。 但是，用户可能希望在同一个地方看到所有的输 入，并且需要有关于在哪里输入数据和输入什么 样的数据的提示与指导。 通常，如果人们在拿到 一份新的应用软件的时候， 需要花费很长的时间 去了解它如何工作和在什么地方输入数据， 这样 往往令人感到沮丧。 Visual Basic 编程系统是通 过先设计对话框，然后对按钮赋予一段操作程 序，对它进行控制并使之工作。对 Excel 来说， 借鉴这一做法也是有益的， 这样既可以避免许多 的模型设计者常常不能设身处地的为用户考虑 的情况发生，同时也更好的便于用户去理解。这种对话框应该是：直观的； 清楚的； 能通过

19、提供符合逻辑的信息来指导用 户操作。多种边框、颜色和格式的应用能够有助于这 一过程的完成，就像在图 2-2( Calculator.xls) 中显示的那样。 用户根据提示给变量赋值， 并按 下按钮来计算出一个答案，这就如同 O17B那 样的手持财务计算器一样。根据用户所按的按 钮，计算出的结果在底部更新并显示出来。 从图 中可以看出，这一簇信息是从上到下的全面显 示。图 2-2（原书第 11 页 fig.2.2）2.4 、关键变量及其规则对变量及其规则要分解设计， 且必须把变量 放在一起， 就如上表显示的那样。 变量的值不是 硬性编码的，这一点尤其重要。举个例子，如果 频率是用户输入值， 当用

20、户由季度支付变为月度 支付，那么用户需要改变什么呢？将规则提炼出 来意味着模型设计者能够组织有序地处理商业 问题，而且能够更简明地理解解决商业问题的过 程。在这个过程中， 他们可能会发现一些需要纳 入模板的新变量。规则也是非常重要的： 公共税收在大多数的 管理权限内是很复杂的， 模型必须精确地反映出 税盾和税收结算日期。 公共税收的支付方法在英 国正从年支付体系变化为四季度支付体系。 这就 给模型设计者提出了新的挑战， 他们必须了解过 渡时期的安排和最终的安排。 给主要的变量和模 板命名的方法有助于简化现有模型的维护。2.5 、布局设计将计算分解成几个可操作的组， 从而使模型 的运转和结果清楚

21、地显示出来。 现在的 Excel 可 以将分离的工作表组合在一个二维的工作簿中， 而不必象在原来的 Lotus1-2-3 和 Excel做的那 样，将一组分离的工作表连接起来。 我们不必将 损益表、资产负债表和现金流量表放在同一个工 作表中，而可以将这三张独立的表放在同一个文 件中，这样的做法显然更符合逻辑规律。图 2-3 的例子把设计分解为： 用户输入； 小结处理使更新后的输入可视化。 这样做可以避免用户反复拉动滚动条来找结 果；计算区域使用上面输入区域中的变量 进行计算；结果显示； 灵敏度分析、图表或其他细节； 在打印区域外的工作区域。模型中的信息流遵循这样一种逻辑构架， 正 如用户所期待

22、的那样， 输入放在左上角。 更复杂 的模型可以将这些区域放在不同的工作表中。 但 是需要再次强调的是， 输入区域和计算不应该混 合在一起，应该分成若干个逻辑区域。在图 23 中，针对不同的数据和信息，多 种颜色、字体、模式和边框的一致性使用能有助 于显示它的逻辑框架。 本书中的模型就遵循着这 样的格式。图 2-3（原书第 13 页 fig.2.3）2.6 、个体模板在这样一个设计好的框架内， 计算被分解到 若干个可分离的区域或工作表中， 个体模板也就 相应产生了。布局的设计便于用户和模型设计者 对模型的理解，这点是非常重要的，同时，布局 设计对于日后深入地开发模型更是至关重要。计算区域必须而且

23、仅能包括公式， 而不能和 数字混杂在一起。 这样做是为了确保计算的完整 性。例如，直接乘以 0.3 来计算公司税额只能制 造麻烦， 因为如果税率发生变化， 你就不得不把 所有工作表中的内容和 Visual basic中的宏代码 找出来并进行替换。 利用有一定输入范围的输入 单元格，或者是用一个已命名的单元格意味着你 只需要改动一个单元格， 则整个文件将会准确地 自我更新。2.7 、菜单结构和宏菜单结构在复杂的模型中是很有用的， 因为 它：将模型纳入到一种结构中； 便于用户理解； 通过按钮进行浏览，这样比不停地在工 作表中作标记更方便。图 2-4( Menu_structure.xls)中的模型

24、运用 了按钮或者说是组合框连接另外两个名称为“ Inputs”和”Reports”的工作表。 这两张工作表 中也有按钮能让用户再回到主菜单下。我们将会在下一章节中详细阐述这些特征。 用户能迅速了解哪个工作表是可以利用的， 而且 也能被指引到需要数据的地方。图 2-4(原书第 14 页， fig.2.4)2.8 、管理报告对于较大的模型， 管理报告和小结通常是必 不可少的，它们应该在一个完整的管理报告区域 中。不是每一个人都需要所有的细节和计算， 小 结可以帮助用户了解计算结果和获取其中重要 的信息。例如，一个项目管理的应用应该在模型中显 示覆盖比率和证券化程度。2.9 、未来发展模型内在的发展

25、是很重要的： 一个预算模型 可能到下一年就需要添加新的变量， 而一个结构 化的模型有助于模型未来的发展。 我们可以通过 查看如何添加新变量和审核设计中的漏洞来测 试它的可发展性。此外，灵敏度分析表和方案使得用户能在同 一模型中得到多方面的答案， 并且在改变输入的 基础上进行差异分析。 一个单一的净现值模型对 于决策的制定是远远不够的， 模型的发展应该包 含一些更深入的对最终结果 8 产生变化程度的 测试。风险也是一个决定性的因素， 因此模型的设 计需要包含风险因素或模拟技术。 模拟使得模型 的发展包含一系列的输入而不仅仅是单一的数字，这样的输入就会得出相应的一组结果。类似地，图表在向操作者或其

26、他人显示结果 上也是很有用处的。 人们常常可以通过图表轻易 地理解复杂的东西。 例如，一个现金流的模型包 含在最低限额之上的现金流的图表。2.10 、测试测试是很有必要的， 它可以确保没有计算上 的错误和由模型得到的信息流是正确的。在图 2 2的表格中，结果可以通过贴现率表或者用其 他的财务计算器的结果来检验。 测试的数据必须 要用到所有的按钮、输入、频率、支付方式等。 后面会有一个章节专门介绍一些用来检验模型 准确性的技术。2.11 、保护如果模型会被别人使用， 那么模型的保护就 变得非常重要。 如果作者将所有的输入都聚合在 一起并且用不同的颜色给它们“加密” ，这种保 护就变得很简单。所有

27、的工作表格都能被保护， 对输入单元格也可以不进行保护。 保护工作表和 工作簿其实也是保护了模型设计者的成果， 并且 这样做可以确保模型在设计者的意图下进行使 用。例如，如果一个预算模型让用户使用，而用 户却在单元格里输入具体的数字而不是公式。 由 于没有对模型进行保护， 其后果是威胁到了模型 结果的准确性。 当然，其他人在使用的时候可能 会从检查每一个单元格可能的错误开始。2.12 、备案许多作者嫌麻烦而不愿写出一些关于工作 表的说明和工作表的结构说明。 这样做可能会面 临这样的一种风险： 他们或者他们的同事在日后 对模型编码进行维护的时候可能会在某些方面 遇到困难。 而许多模型也就因此成为了

28、 “宠物设 计”，因为任何一个计算机程序都需要相关的背 景信息。理想的情况是将说明写进模型而不是写 在文件的一小片纸上，并且要遵循： 采用一个特定的设计或模板的原因； 写出关键的公式和计算步骤； 规则和方法。2.13 、同行的意见模型的用户和同行们常常会对模型提出建 设性的意见。 尽管这个过程很痛苦， 尤其在你耗 用的大量的时间来制造出这样一个杰作之后。 但 是，潜在的用户需要尝试输入数据并且对模型的 操作方式是否人性化给出评判。 让用户参与到模 型的设计过程， 并且询问他们的意见能够充分调 动用户的积极性。这方面主要的因素包括： 有清楚地对话框，使用起来很方便； 用户操作指导，包括从输入开始

29、经过计 算最后得出答案和报告； 将审核和更正的复杂性降到最低点； 结果显示的明晰性。以上的 13 点将会帮助你更有序地工作。检 查一下你自己的模型， 看看在你的成果中包含了 以上的哪些方面。 显然，模型的复杂程度影响着 你所需要完成的工作量。 但是，以上这些是作者 实践了很多年总结出的经验， 它们会帮助你更好 的进行实践。我们在下一章节中会讨论几种能使你的模 型功能更强大的特征。 随后的章节会应用这些设 计方法来重新建立第一章中的那个基础模型。 这 样做的目的是为了向您展示如何应用 Excel来建 立功能更强大而没有错误的模型。2.14 、小结模型的设计是个性化的， 经过一段时间的工 作，我们

30、会形成自己的风格。 设计的连贯性和遵 循统一的设计方法是很重要的。 本章对设计步骤 的讨论并不是面面俱到的， 主要包括了以下几个 方面的内容： 建立所有的模型都要遵循的设计步骤 和方法；确立目标和对象； 了解客户的需求和必要的客户交流； 建立关键变量及其规则； 将计算分解成若干个可操作的群组； 建立个体模板； 设计菜单结构； 管理报告和小结； 模型发展，如，灵敏度分析； 测试和审核；应用操作的保护；备案；征求同行的意见。第 3 章 特征和技术在上一章里，我们讨论了模型设计的计划和 在设计中的逻辑问题。 本章将会着重介绍模型中 包含的让用户感到更亲切的特征。 本章不会把所 有的特征一一详尽地列出

31、， 但本章所要达到的目 的就是要揭示最初的和最终的模型之间的差异。 本章讨论的特征包括：格式； 数字格式； 框线和边框； 颜色和图案； 给输入和结果特别着色； 数据的有效性来控制输入； 控制对话框或按钮； 条件格式化来显示数据上的变化； 加载宏，以增加更多的财务功能； 加入更多的财务公式； 文本框和更新标签；记录版本序号、 作者、开发时间和其他信 运用名称以便使公式更便于理解； 将命名表作为备案的一部分记录； 单元格批注； 图和表； 多样化的图表来显示个体线条； 用于灵敏度分析的数据列表； 条件分析（如果 -怎样）； 单变量求解； 最优化和目标区的规划求解； 使用模板来加快模型开发。图 3-1

32、 的模型是在文件 Features.xls中。本 章节中的每一部分都包含在这个模型里。 打开文 件并依次点击来浏览工作表的制作过程。图 3-1 （原书第 19 页，图 3.1）图 3-1 是一个简单的净现值模型， 这个模型 把各期的现金流量以 10% 贴现率折现后相加。 在 C14 这个单元格中，净现值就是把折现后的 现金流量相加得到的。选择“工具 (T) ”，“选项 (O) ”，“视图(V) ”， 你可以选择“视图 (V) ”中的“公式 (F)”。这个选 项能让你看到计算用的公式 (见图 3-2)。你还可 以同时按下“ Ctrl+ ”在公式和通常的状态之间 进行切换。 正如你所看到的， 它只

33、是根据如下的 现金流量的公式来计算净现值：Period_ Factor (1 10%) Period _ Number图 3-2 ( 原书第 19 页 fig.3.2)图 3-3 显示了每个单元格的公式图 3-3( 原书第 19 页 fig.3.3)3.1 、格式图 3 4 中的模型是将数据输入和公式计算 混合在一起，因此我们的第一项工作就是对它的 布局重新进行设计。这包括： 插入边框线并移动输入； 根据输入的现金流量进行计算； 在可能检查输入值的地方做标签。例如 B9=C3；用一个输入来更正所有的因素； 用不同的字体来做出区分。现在，标题、输入、摘要和结果就用黑体字 清楚地做出来了。 这个模

34、型遵循了在图 3 5 中显示的布局。图 3-4（ 原书第 20 页 fig.3.4）图 3-5（ 原书第 20 页 fig.3.5）3.2 、数字格式多种数字格式同没有区分或是两种不同的 小数点位数的设定是不一样的。 点击“格式 （O）”， “单元格 （E） ”，“数字”来改变默认的数字格式 （见图 3-6）。图 3-6（ 原书第 22 页 fig.3.6）你可以尝试一下自己定义不同的数字格式， 正数、负数和零用半冒号来区分。 方格中用了多 种颜色。文字被引在双引号中，例如， 年'加 入数字后格式为： 0“年”。你可以将你自定义的格式添加到格式框中也可以修改现有的格式 (如 图 3 7

35、)。图 3-7（ 原书第 22 页 fig.3.7）这个摘录表明这样的会计格式： 正数在左边 而负数用红色加方框标出。 零是破折号。 这样的 格式类型很容易被激光打印机识别， 因为通常减 号很难被识别为负数的标志。会 计 格 式 类 型 ： _-* #,#0_-; 红 色(#,#0);_-* "-"_-这样做的效果是控制数字视图中最多只有 两个小数点位。3.3 、框线和边框框线和边框有助于打破单元格的编码， 并能让模型不论是在窗口显示还是打印输出后看起 来更吸引人。最好将“格式 (O) ”保持可视，选 择“视图 (V) ”，“工具栏 (T) ”，“格式”来显示格 式条(如图

36、 38 所示)。这就省去了每次都要到 “格式 (O)”，“单元格 (E)”，“边框”等地方去添加边框图 3-8（ 原书第 23 页 fig.3.8）图 39 和图 3 10 显示的是如何通过应用 工具包中的边框来突出单元格。 细线加在单元格 的周围，用双框线显示总计。图 3-9（ 原书第 24 页 fig.3.9）图 3-10（ 原书第 24 页 fig.3.9）3.4 、颜色和图案颜色和图案的使用也可以被用来帮助定义 输入和输出。在图 3-11 中，非彩色用于表示输 入，灰色表示答案。这些颜色的使用是个性化的， 但重要的是保持多种颜色和格式的一致性使用。3.5 、输入和结果的特殊颜色对输入值

37、特别着色可有助于显示在哪里需 要输入数据。 作者常常用蓝色表示输入， 绿色或 黑色表示总计， 红色或黑色表示计算结果 （如图 3-12 所示）。颜色的使用应该保守一些，因为太 过于炫耀的颜色会不合大多数人的口味。图 3-11（原书第 25 页 fig.3.11）图 3-12（ 原书第 25 页 fig.3.12）对模型添加几种颜色之后， 模型变得更加清 晰，并且颜色的使用也迫使作者从一致性的角度 考虑将输入放在一起。现在的这个模型比原先的那个模型要有序 的多，且更方便用户使用。3.6 、数据的有效性数据有效性的设置使你可以设定单元格数 据的界限。这样一来，如果你需要输入日期，那 么用户只能输入

38、日期， 或者当你需要输入一列七 个字的文本，用户也只有这么做才能继续往下进 行。有效性的设置可以通过选择主菜单栏的 “数 据（D）”，“有效性 （L） ”来设置。（如图 313 所 示） 图 3-13（ 原书第 26 页 fig.3.13）资本价值 每期的现金流量 贴现率在这个例子中， 最好对以下三个输入有个最 小值的限制：大于 0 的正数大于 0 的正数0 和 1 之间的正数，如100%这个对话框有三个标签， 当鼠标指针靠近单 元格时会出现输入信息。 若是错误的输入， 会弹 出出错警告的对话框。 你也可以通过不选中对话 框来选择不显示输入信息（如图 314 所示）。图 3-14（ 原书第 2

39、7 页 fig.3.14） 出错警告显示的是你输入了一个错误的数 字，并且在你遵守了数据有效性条件后才能继续 （如图 3 15 所示）。这意味着资本价值应该而 且只能是个正数。图 3-15（ 原书第 27 页 fig.3.15）由于每期的现金流量也符合同样的有效性 约束条件，你可以选择“复制 （C） ”，然后在“编 辑 (E)”，“选择性粘贴 (P)”，“有效性”来完成， 而不用再一次的键入有效性约束条件。图 3-16( 原书第 27 页 fig.3.16)最后的一个有效性条件只是简单的保证贴 现率小于 100% 。这样做是为了缩小输入的范围， 也是为了让用户能得到正确的答案。 如果用户试 图

40、输入一个数值为 120% 的贴现率，那么将会出 现如图 317 所示的那个错误信息。再次说明，这是从用户的角度来看模型的使 用，并试图让用户知道什么是他要做的。3.7 、控制组合框和按钮更多的加速输入和帮助用户的操作可以在 “视图 (V) ”，“工具栏 (T) ”，“控件工具箱”中找 到。在 Assess或 Visual Basic中都能找到类似的 控制器。 在这个例子中， 你可能希望用户输入一 个在 8% 12% 之间的贴现率，并且每个数字 之间间隔(步长) 0.5% 。这样的话我们就不可 能利用数据的有效性来进行处理，必须另觅他 途。因为数据的有效性只是限定了数据的上下图 3-17（ 原书

41、第 28 页 fig.3.17） 第一步是先在工作表的底部开辟一块工作 区域，然后通过剪切和粘贴将贴现率输入到区域 中（如图 318 所示）。这是为了确保在单元格 C7 中建立控制器的时候模型仍能正常工作。图 3-18（ 原书第 29 页 fig.3.17）工作区域显示了一个数值间隔， 然后贴现率 从 8% 开始并且每次都按步长值增加。图 3-19（ 原书第 29 页 fig.3.19）最终的工作区域将显示从 8% 到 12%的贴 现率（见图 319）。步长值并不是固定不变的， 它取决于单元格 C26 的值。由于这些是变量， 而大多数的用户都不需要了解这些细节， 所以我 们把它们放置在独立的工

42、作区域并清楚的标明。组合框控制器中有八个可能的选择， 它返回 你所选择的序号。 它会把序号返回到单元格 C27 中。点击“工具栏 (T) ”中的“组合框”按钮， 你就可以在单元格中画出组合框。你必须给控件确定从那里获取输入信息和 在那里输出结果。在图 3-20 中我们可以看出， 贴现率的取值是在区域 B28:B35，结果的输出是 在单元格 C27 中。图 3-20(原书第 30 页 fig.3.20)最后一步是在显示贴现率的单元格 C28 和 显示序号的单元格 C27 之间建立链接。由于 C28 用于计算，需要将它的颜色改成红色以免混淆。 函数“OFFSET ”可完成这个功能 , 你可以在“插

43、 入(I) ”，“函数 (F)”，“查找与引用”找到它(如 图 321 所示)。图 3-21(原书第 31 页 fig.3.21)通过这个函数，你可以先定义一个起始数 值，然后向下移动 X 行向左右移动 Y 列，最后 得到另一个数值。 在这个例子中， 起始数值定义 为单元格 B27 的值，并且向下移动的行数为单元格 C27 的值，没有左右移动。最终得到控制器 所需要的数值用作贴现率进行现值的计算。这个组合框可以控制用户的输入， 并且使得 对单个贴现率的选择更加迅速（见 图 3 23）。特别需要说明的是，用户仍然可以在 B27、C26、C27 中输入数据。这个组合框运行 一个宏或依照程序更新单元

44、格的数据， 但这并没 有受到保护。在工具栏中同样还有其他的控制器能使你 的输入更直观。 比如说， 数据调节器和滚动条就 可以使你只通过点击就能够增加数值， 并且还可 以根据具体输入变动的间隔需要来设置点击移 动的间隔。图 3-23（原书第 32 页 fig.3.23）工作表 Spinner_Scrollbar 中应用了这两种 控制器进行了替换操作。 在那里， 你可以选择一 个最大值、 最小值和增加值。 这种操作会有一点 点的复杂， 原因是这个控制器没有分数。 因此你 就不得不从滚动条的位置来计算一下具体可能的贴现率。在图 3-24 中的滚动条的作用是取从 1 到 8 的数值并且以一个单位递增。

45、 滚动条连接的单元 格是 C26，在单元格 C27 中的函数“ OFFSET ” 用的是选择的序号。图 3-24（原书第 33 页 fig.3.24）3.8 、条件格式条件格式可以使我们根据单元格的不同数 值进行不同的单元格显示。 这里的不同指的是字 体、边框和图案的不同。在这个例子中，我们将 会引入一个很有用的操作试验来说明如果如何 根据项目的成功与否来相应地显示结果。在图 325 中有一个新的单元格 C7，它定 义了进行操作性试验所需要的最低的净现值 7,000。用格式按钮来设置格式，使得当现值大 于或是等于单元格 C7 的值时，显示粉色。试验 的结果在图 326 中展示，在图中可以看出

46、9.5% 的贴现率能使项目达到目标。图 3-25（原书第 34 页 fig.3.25）图 3-26（原书第 34 页 fig.3.26）你还可以通过点击“添加 (I) ”键，并且选择 “编辑 (E)”，“选择性粘贴 (S)”，“格式”来复制， 以便添加更多的格式。3.9 、函数的使用以及各种类型的函数在模型中已经使用了函数“ OFFSET ”；但 是，利用 NPV 公式会使净现值的计算变得更为 简单。现在，从单元格 C17 到 H19 这个区域中 的单元格都已经被直接赋值， 这也就意味着可能 会有 12 处错误存在。使用公式的目的就是为了 减少直接赋值以便减少潜在的错误。这种利用 Excel

47、的解决方法比查贴现率表的方法要便利多 了。你可以在主菜单栏选择“插入 (I) ”，“函数 (F)”，或者从标准的工具栏中找到所用函数。函 数被划分成几个部分以便查找。 选择财务函数并 找到 NPV (见图 327)。图 3-27(原书第 35 页 fig.3.27)净现值公式可将每期的现金流贴现。 然后要 做的就是选择 1-5年并从 0 期开始添加各期的现金流量，再利用公式进行计算。=NPV（C25,D15:H15）+C15 根据这个公式可以得到贴现率为 9.5% 时正 确的答案是 7,511.85。注意到现在的工作表在进行了必要的删减 后已经变得更简洁了。 你总是可以通过点击问号 标示（如图

48、 328 所示）来获取关于公式的帮助 信息。你也可以通过选择查看来浏览一些替代公 式的列表。图 3-28（原书第 36 页 fig.3.28）3.10 、加载宏以便获得更多的功能对 Excel 的典型安装只包含了基本的函数公 式。但是除此之外，还有大量的公式可供使用。 例如， NPV 函数假定每一期包含同样的日数。 XNPV 函数则允许你输入现金流实现的日期。 （在第 19 章中讨论的估价模型就是用到了这一 函数。）选择“工具（ T）”，“加载宏（ I ）”，“分 析工具库”，确定你已经可以使用扩展函数。选 择此选项并选择“确定”进行安装。这样一来， 这些函数在你每次打开 Excel 后都能使

49、用。如果 没有“加载宏（ I ）”选项，那么你就需要重 装一下 Excel 了。图 3 29 中显示的工作表运用了 XNPV 函 数和 EDATE 函数。 EDATE 函数是一个日期函 数，它的功能是每次将事先确定的日期增加或减 少若干个月。你先提供一个起始日期然后通过调 用函数可以将时间提前或推后若干个月。 由于提 前或推后的步长是可以变动的， 因此在输入区域 中又有了一个新的控制。它在单元格 D13:H13 中确定函数需要的起始月份，并指向函数的结 果。再次输入初始的现金流得到的结果是 7,502.58，与原来的结果 7,511.85有所不同。图 3-29（原书第 37 页 fig.3.2

50、9）3.11 、文本框和更新标签你可以进一步增加模型的明晰性， 方法是通 过建立标签进行更新， 以及在结果上提供一些文 字说明。 如果净现值在底线之上， 你就应该建立 一个标签来通知用户。在文件 Feature.xls 中的 工作表 Text 就提供了两种改进方法： 在标签上显示贴现率。反馈计算后的净现值。现在单元格 B20 有一个更新标签。 Text 函 数将数字转化为文本并保留原来的格式。 这将显 示百分比到小数点后两位； “& ”用来连接文本 串：="NPV at "&TEXT(C31,"0.00%")反馈用一个 IF 函数来管理，

51、以便显示结果 在底线之上或之下时的文字串。为了减少代码， IF 中的文本陈述将由净现值决定。="NPV is "&IF(C20>=C7,"above","below")&" the limit of "&TEXT(C7,"#,#0")现在工作表能告诉用户所用的贴现率并对 结果进行说明 (见图 330)。Excel 将根据结果 做出决定，而无需用户再花时间去比照结果。图 3-30(原书第 38 页 fig.3.30)3.12 、记录版本序号、作者等正如在前面的章节中提

52、到的那样， 给模型备 案是模型的一部分。 在复杂的模型中， 我们需要 记录模型的版本序号、 作者姓名和与模型运行有 关的说明文字。 在模型建立一段时间后， 你可以 比照并记录不同版本之间的差异。 如果你要避免 重大的错误，这样做是特别重要的。另外，版本 序号会出现在你打印的每一张工作表的顶部 (见 图 3 31)。图 3-31(原书第 39 页 fig.3.31) 这部分同样也适用于给页面附图片和文字 说明。将文字说明写入模型自然是有益的， 你也 可以通过选择“格式 (O)”，“工作表 (H) ”，“隐藏 (H) ”来隐藏一个工作表。3.13 、使用名称让公式更易理解名称可以使公式更便于理解：

53、例如，不用 C28 而 使 用 Periodic cash flow 。 就 如 上 图 3 31 中的 Version、Author 等，他们使你的 模型更加标准，如： “ =Version”表示插入版本 序号。本书里的文件用了很多这种标准的名称， 例如： Author 、Company、Version和 Product。你可以在“插入 (I) ”，“名称 (N)”，“定义 （D）”来定义名称， 或者在 Excel里用标签在选 中的单元格区域的一侧建立多个名称（见图 3 32），通过“插入 （I） ”，“名称 （N）”，“指定 （C）” 完成。图 3-32（原书第 39 页 fig.3.32

54、） 这里指定名称在左边的栏中，如 Start_date（见图 3 33）。图 3-33（原书第 40 页 fig.3.33）现在函数就很容易理解了， 因为它利用了 在单元格 C20 中的各期利率。=XNPV（Int_Rate,C18:H18,C13:H13） 如果你复制一个包含名字的工作表， 同时 新的工作表也会继续引用原来的工作表。简单 说，如果你将一个工作表复制到一个新的工作 簿， Excel 会在这两个工作簿之间建立连接。你 可以通过选择“编辑 （E）”，“链接（ K ）”来检查。 如果不是这样的话， 你就不得不通过手动来移动 并重新输入单元格的公式。图 3-34（原书第 40 页 fi

55、g.3.34）3.14 、粘贴名称列表作为备案的一部分粘贴名称列表作为备案的一部分是很有 用的， 因为它给日后的审核提供了一种线索。 你 通过选择“ 插入 (I) ”，“名称 (N)”，“粘贴 (P)”， “粘贴列表 (S)”来完成。3.15 、批注附批注的单元格是给单元格附上批注， 以便 提供背景信息或帮助用户使用。 到“插入 (I) ”“批 注 (M) ”，或右单击单元格， 输入文本信息然后设 置字体大小和颜色(见图 3 35)。图 3-35(原书第 41 页 fig.3.35)图 3-36(原书第 41 页 fig.3.36) 你还可以控制批注显示的方式， 方法是点击“工具( T)”，“

56、选项( O)”，“视图 (V) ”(见图 3 36)。你可以关掉它们，显示标识符或使批注 始终可视。 在第二种情况下， 单元格的右上方拐 角会有一个红色的小三角。 再次强调， 批注可以 帮助说明重要的公式或者将指导用户如何操作。例如，人们通常用数字来表示百分比， 然后需要 再除以 100。一个关于这种格式的批注可以告诉 用户需要插入的是数字而不是百分数。3.16 、图表图表在管理报告和在向用户显示重要的结 果的时候是很有帮助的。 在此例中， 现在我们就 来添加一个累计现金流量的图表并设置它的图 案。你可以用在标准工具栏上的图表向导， 或者 选择“插入（I） ”，“图表 （P）”来完成（见图 3

57、37）。图 3-37（原书第 42 页 fig.3.37）这里只需要画一个单序列的图表，因此用 柱形图就可以清晰地表示。 第二步， 选择序列标 签而不是数据区域标签（见图 3 38）。然后点 击添加序列来添加序列的名称、数值和标签。图 3-38（原书第 43 页 fig.3.38） 这个图显示的是累积的现金流量值和相 应的 X 轴的日期（见图 339）。这个图表的名 称编码为 Graphics!$C$13:$H$13。点击“下一 步”，图表的标题和坐标轴的标题就会显示出来。Excel 不会让你输入一个参照的单元格作为标 题，但是你可以在完成向导后做到。图 3-39（原书第 43 页 fig.3

58、.39）右键单击 X 轴，可以更改显示的格式。图 表的标题输入： =Graphics!$B$20，这样它可以 自我更新。 这样做是很重要的， 因为我们并不希 望这个标签固定。回收期是一种没有考虑货币的时间价值的 投资评价方法。 实际上就是考察将用多长时间才 能收回自己投资在项目的资金。 最终的图表 （见 图 3 40）清楚的显示了这种回收会在第四年。图 3-40（原书第 44 页 fig.3.40）3.17 、动态图表来绘制单个序列简单的图表是很有用， 但是，一个动态的图 表能使你考察任何一行数据。 这里，我们虽然给 出的是个很简单的例子， 但这种方法却对于考察 现金流的单个序列或者是对公司分析很有帮助。具体的步骤如下：建立一个组合框， 输入单个的序列并且将它 和单元格连接