Excel财务建模.docx

Excel财务建模.docx

《Excel财务建模.docx》由会员分享，可在线阅读，更多相关《Excel财务建模.docx（58页珍藏版）》请在冰豆网上搜索。

Excel财务建模

第1章概论

本书旨在为您提供一系列帮助您开发、利用和维护Excel模型的工具。

财务模型的建立通常被看作只是对会计数字的添加或者是进行这种添加的方法。

但是，本书将会给您展示精良的建立财务模型的实践操作；提供一些不同的技术要领并会给您精选出一些模型的模板。

本书并不是一本Excel使用的工具书，因为关于这一方面已经有很多深入的手册了，更确切地说，本书是对一些技术的概述以便为您节省时间，帮助您在财务管理方面变得更为有效率。

1.1、什么是财务建模

财务建模涵盖了一个很宽泛的领域：

从简单的制表到费用的加总再使之转变为项目所需的复杂的风险模型。

此外，模型的设计还需要考虑很多其它的方面。

具体地说，关于财务建模我们必须考虑：

●针对具体商业问题的解答建立特殊的操作程序。

如现金流量表及其易变性；

●对数据进行分析处理；

●将未来因素纳入模型考虑，对未来的情况进行考察；

●将数据快速准确地转化为管理信息；

●在一个“安全”的环境中测试假设，如项目方案；

●通过一种结构化的途径来支持管理决策；

●更准确地认识问题中的相关变量和规则；

●更多地了解变量的变化过程及其变化方式；

●找出关键变量并考察其敏感性。

1.2、电子表格的历史

电子表格被应用于个人电脑是从20世纪70年代晚期VisiCalc（专为苹果机使用的一种操作软件）的使用开始的。

由于这种工作表的高效率和准确性，使之在大围迅速取代了一些早期的方法（如高速计算机），同时，Lotus1-2-3的使用与IBM个人电脑的使用也同步增加。

从此财务管理者也能用他们自己的数据来进行分析而不用求助于其它数据系统或是系统管理员了。

会计模型，如预算和现金流量，能根据用户的要求进行建立，这就导致了：

●有更详细的信息用于决策制定；

●使在较低层次的决策制定成为可能；

●对特定环节的检验或其他替代方法之间具有灵活性。

1995年，微软在AppleMacintosh引入了Excel并在20世纪80年代后期将它扩展到个人电脑上。

Windows3.0版本引入包含了Excel的Office95，随着它的快速增长，Excel成为了工作表操作软件中的领头羊，被大多数个人电脑用户所使用。

在成功开发Office97和Office2000后，微软在这一领域的占有率又被大大增强。

1.3、工作表的功能

Excel包含于微软工具包之中说明它现在是一种公认的标准，就如同人们把Word作为文字处理的标准格式一样。

伴随着以下功能的加入，它的工作表的功能不断的加强：

●专业的函数；

●大量使得工作表自动化的宏程序的使用，或者说用编码进行公式编辑功能的使用；

●工作簿技术的使用，省去了单个工作表之间的联系的建立；

●对VisualBasic的使用提供了一种与微软其他应用程序之间通用的语言；

●同其他应用软件之间的数据交换功能；

●添加例如关于目标区和最优化问题的规划求解模型；

●三部分分析包，如财务CAD,RISKorCrystalBall。

今天对这种复杂分析软件包使用的结果是使得那些非专业程序员也能设计并建立起一套专业的解决商业问题的应用程序。

Excel也是这样一种分析软件包。

大部分人在他们需要解决一个商业问题的时候都会使用它。

作者曾经有一个这样的经历，需要对一个项目的租赁可盈利性进行研究，并要编写一个模型来考察不同的基金组合决策。

在耗费了大量的时间和精力后，这个模型终于成功运行并给出了一个答案。

但是，这个答案很不清楚而且也不方便其他人去理解。

这里并没有模型设计的方法论，而模型真的就那样“蹦出来了”。

许多公司或学院很少会提供如何使用Excel来处理财务问题的指导，这对于大多数的管理者来说是司空见惯的了。

这种做法的后果是许多模型建立都很少或者根本就不考虑模型的设计和模型未来的维护。

更进一步地说，据估计很多商业上正在使用的模型本身都存在着严重的错误。

用VisualBasic和C++来编写应用程序是为IT部门进行设计提供的。

但是，通常情况下，Excel并不受这些影响因素的制约。

这可能并不总是个问题，但是，一个预算模型可能成为财务管理者自己的“宠物设计”，而他可能会在此后离开这家公司或者可能被提升到新加坡去了。

在文件里当然不会有注释，这样，就再没人能了解这个模型是如何工作的。

人们经常说信息就是力量，但是因为这些管理者常常不能将他们的工作充分的备案，所以导致公司在模型的审核和错误的查找上不得不花费大量的金钱。

这样看来，Excel的简洁可能也成为了它的一个弱点。

作者强调：

Excel的使用者应该按照简单设计的步骤进行，并且注意对于操作模型相关背景信息提供的必要性。

在这样的方法的指导下，现在你在计划上花的时间将从长远来说为您提供超额收益：

●可操作性和使用轻松；

●可维护性；

●对于答案和结果的信心。

在接下来的章节里，我们将会详细地说明如何综合运用Excel模型，来制作功能更强大、更有活力的工作表。

1.4、本书的目标

本书的目标就是展示一系列的Excel在财务建模方面的应用。

这一系列的应用出自于一位致力于将公司财务理论应用于工作表中长达10年之久的非程序编写员。

建模需要了解如何用Excel建立模型，并且将财务知识和设计与Excel结合在一起，特别是：

●设计方法和设计过程；

●怎样将设计想法变成应用中的模型；

●改进现有模型的有用技术；

●使简单的模型更有用、更可靠；

●如何加入风险分析技术；

●利用最优化和目标策略；

●将所用的技术结合成一个标准和模板。

操作者们需要将对工作表技术作为核心技能来理解。

现在的公司掌握着越来越多的数据而且需要用一些低水平的简单分析工具。

通过建立模型，操作者可以更好地了解：

●单个变量如何变动；

●怎样去找出应该包含在计算中的新变量；

●怎样分离出对未来测试的关键变量；

●如何通过方案测试和案例分析来避免代价昂贵的错误。

举一个例子，通过对某些会计模型的替换而建立的简单的外包模型可能显示一个正的净现金流量。

一个正确建立的模型不仅能找到答案，而且能：

●列出所有的规则和输入；

●提供基于不同参数值的一系列结果；

●提供关键变量的图表，用来显示它们对于其他变量变动的弹性；

●列出风险和不确定性的水平；

●显示有多大的可能性你将会得到预测的结果。

因此，本书的目的是应用Excel和财务知识把二者结合起来，帮助你建立更强大、更有活力的工作表。

1.5、工作表举例

图1—1是设计工作表的一个反面例子。

这工作表可能是你用来显示一个项目的净现金流量的。

这是在许多公司里使用的众多工作表中比较典型的一个例子，它存在一系列的问题，我们将在下面一一给出。

这个模型是光盘中命名为Simple_Model.xls的文件。

图1-1（原书第7页f.g1.1）

它的主要问题可以归结为：

●没有把对输入、计算、输出的布局设计清楚地标明；

●没有输入部分，不知道模型中的变量有哪些；

●没有对输入进行特别着色；

●没有用边框和阴影来增强报告的外观；

●没有对输入数据的有效性进行限制，例如，确保输入数据具有正确的类型和数据长度；

●将不同小数位数的数字格式混合在一起。

方括号和红颜色的使用能增强此模型，因为方括号很容易在打印出来的报告上被识别，而且红色一般是负数的颜色；

●将数字和公式混合在一起。

第10行中的税额计算将税率对于每一个单元格都固定。

如果税率发生变化怎么办？

●单元格G10中有一处计算错误，单元格本应该是输入公式，但是输入的却是数字；

●公式测试显示单元格B10和B17中是直接输入数字而不是利用公式计算，当贴现率或税率发生变动时候，数值不会随之发生变化；

●没有根据结果得出的操作报告。

5,411是否已经是满足了操作的底线；

●条件格式化能将结果注重显现出来，例如，这个单元格的颜色将根据结果确定；

●没有函数的使用，因为净现值是使用每个时期的变量值进行计算得出来的。

通过使用NPV函数可以减少对每个单元格的赋值，从而减少可能出现的数字错误；

●没有敏感性分析。

当改变贴现率或者没有按计划形成利润会有什么情况发生？

●图表一般用来显示管理现金流量或者显示敏感性分析。

例如，一个累计现金流量表表示的是可获得的回报；

●没有对关键变量进行特别命名；

●工作表没有分开显示。

下面的表格中计算了设备的税收折旧，但是这并不清楚它是不是现金流量的一部分；

●没有对单个单元格的批注，也完全没有对整个模型如何工作进行说明；

●作者没有对数字做出解释的信息；

●这个模型不适合打印。

模型没有页眉和页脚指示，例如，文件名和编写日期。

打印要输出包括税收在的所有结果。

以上的列表从模型建立的结构、设计和方法上罗列了这个模型的缺点。

如果管理者做出的所有决策都是基于这样的一类表格，他很有可能会做出错误的决策。

即使是很快做出来的，这个模型也因为存在计算错误而失败。

如果使用这样的工作表进行管理可能会造成许多严重的失败，但在对这个模型重新设计后,可能会纠正这些错误。

建立正确的模型可以避免很多类似的错误。

1.6、小结

Excel的使用是操作者的核心技能。

Excel是一个功能强大的工具，但是，极少数的用户接受过正规的模型技术的培训。

本章还给出了一个简单的工作表及其在设计和构建方面包含的错误。

在接下来的章节里，我们会提纲挈领地介绍如何应用模型来建立有活力和可以维护的工作表。

第2章模型设计导论

在第一章里，我们已经看到了传统的模型设计的缺陷之处。

这种传统设计从本质上说只是将Excel用作一大组自动化的会计文件。

如果我们的目的是依据Excel做出决策或者我们要完全依赖于这个结果，那么我们必须采用一种不同的途径，一种更专业化的途径。

这种途径更多地关注目标、用户报告和结果的产生过程。

图2-1概括了设计中的几个阶段。

图2-1

模型设计

1、所有模型都遵循一定的设计程序和方法；

2、确定目标和对象；

3、确定用户需求和必要的用户交流；

4、列出关键变量和规则；

5、将计算分解成若干个可操作的群组；

6、制作出单个模板；

7、列出菜单结构；

8、管理报告和小结；

9、模型发展如灵敏度分析；

10、测试和审核；

11、应用操作的保护；

12、备案；

13、征求同行的意见。

1.1、模型设计基础

设计是个性化的，你开发出自己认可的、欣赏的模型风格，你就能轻而易举地将其重复运用。

这听起来可能很简单，但一个正确实用的设计方法会大大缩短模型设计和错误纠正的时间。

设计的必要性取决于应用的复杂性，你必须对不同种类的工作表采用不同的计划和方法。

在模型设计中，你可能想要多次的插入行或者是删除列，又或者是在工作的某个阶段想要了解一个具体单元格的公式如何运行。

想要做到不过多考虑就能正确地使用关键的公式其实是很简单的。

清楚地列出需要考虑的事项能够便于更好的设计模型。

所有的模型设计都遵循统一的设计步骤和方法，并且工作表也遵循一定的设计格式。

本书中的例子毫无疑问地是按照这样的原则编排和设计的。

对于一个人来说，简单的工作表就可能够用，而模型的设计也必须符合简单的规则，特别是模型会被其他人使用或者被合并用于决策的制定。

在基础的格式中，这就意味着要将模型中的功能划分成输入、计算和输出三个区域。

2.2、对象

很多人并不对对象和目标进行深入地考虑。

尽管下面说的听起来很简单，但是这样做确实是有益的：

将对象和目标记录在文件中，并在模型设计的过程中不时地进行参照以保证你并没有偏离最初的想法。

模型常常能给我们提供更多的信息，但在很多例子中，由于答案隐藏在计算中，所以我们很难获取信息。

举个例子，一个简单的现金流量的预算也能更深地用于记录实际的损益表和资产负债表。

有了预算的和实际的数字，在绝对数和相对数差异基础之上的差异分析报告就可以和管理报告还有图表一起形成单个的报告。

2.3、用户界面

这个需要很仔细的核查，因为这一项需要你和你的用户一起完成。

也许对于同一个模型会有许多不同的用户，他们对于输入、其他一些细节和信息有不同的要求。

早期建立的模型有时会将变量放在左边，在标签和数字之间，例如税率。

但是，用户可能希望在同一个地方看到所有的输入，并且需要有关于在哪里输入数据和输入什么样的数据的提示与指导。

通常，如果人们在拿到一份新的应用软件的时候，需要花费很长的时间去了解它如何工作和在什么地方输入数据，这样往往令人感到沮丧。

VisualBasic编程系统是通过先设计对话框，然后对按钮赋予一段操作程序，对它进行控制并使之工作。

对Excel来说，借鉴这一做法也是有益的，这样既可以避免许多的模型设计者常常不能设身处地的为用户考虑的情况发生，同时也更好的便于用户去理解。

这种对话框应该是：

●直观的；

●清楚的；

●能通过提供符合逻辑的信息来指导用户操作。

多种边框、颜色和格式的应用能够有助于这一过程的完成，就像在图2-2（Calculator.xls）中显示的那样。

用户根据提示给变量赋值，并按下按钮来计算出一个答案，这就如同O17BⅡ那样的手持财务计算器一样。

根据用户所按的按钮，计算出的结果在底部更新并显示出来。

从图中可以看出，这一簇信息是从上到下的全面显示。

图2-2（原书第11页fig.2.2）

2.4、关键变量及其规则

对变量及其规则要分解设计，且必须把变量放在一起，就如上表显示的那样。

变量的值不是硬性编码的，这一点尤其重要。

举个例子，如果频率是用户输入值，当用户由季度支付变为月度支付，那么用户需要改变什么呢？

将规则提炼出来意味着模型设计者能够组织有序地处理商业问题，而且能够更简明地理解解决商业问题的过程。

在这个过程中，他们可能会发现一些需要纳入模板的新变量。

规则也是非常重要的：

公共税收在大多数的管理权限是很复杂的，模型必须精确地反映出税盾和税收结算日期。

公共税收的支付方法在英国正从年支付体系变化为四季度支付体系。

这就给模型设计者提出了新的挑战，他们必须了解过渡时期的安排和最终的安排。

给主要的变量和模板命名的方法有助于简化现有模型的维护。

2.5、布局设计

将计算分解成几个可操作的组，从而使模型的运转和结果清楚地显示出来。

现在的Excel可以将分离的工作表组合在一个二维的工作簿中，而不必象在原来的Lotus1-2-3和Excel做的那样，将一组分离的工作表连接起来。

我们不必将损益表、资产负债表和现金流量表放在同一个工作表中，而可以将这三独立的表放在同一个文件中，这样的做法显然更符合逻辑规律。

图2-3的例子把设计分解为：

●用户输入；

●小结处理——使更新后的输入可视化。

这样做可以避免用户反复拉动滚动条来找结果；

●计算区域使用上面输入区域中的变量进行计算；

●结果显示；

●灵敏度分析、图表或其他细节；

●在打印区域外的工作区域。

模型中的信息流遵循这样一种逻辑构架，正如用户所期待的那样，输入放在左上角。

更复杂的模型可以将这些区域放在不同的工作表中。

但是需要再次强调的是，输入区域和计算不应该混合在一起，应该分成若干个逻辑区域。

在图2－3中，针对不同的数据和信息，多种颜色、字体、模式和边框的一致性使用能有助于显示它的逻辑框架。

本书中的模型就遵循着这样的格式。

图2-3（原书第13页fig.2.3）

2.6、个体模板

在这样一个设计好的框架，计算被分解到若干个可分离的区域或工作表中，个体模板也就相应产生了。

布局的设计便于用户和模型设计者对模型的理解，这点是非常重要的，同时，布局设计对于日后深入地开发模型更是至关重要。

计算区域必须而且仅能包括公式，而不能和数字混杂在一起。

这样做是为了确保计算的完整性。

例如，直接乘以0.3来计算公司税额只能制造麻烦，因为如果税率发生变化，你就不得不把所有工作表中的容和Visualbasic中的宏代码找出来并进行替换。

利用有一定输入围的输入单元格，或者是用一个已命名的单元格意味着你只需要改动一个单元格，则整个文件将会准确地自我更新。

2.7、菜单结构和宏

菜单结构在复杂的模型中是很有用的，因为它：

●将模型纳入到一种结构中；

●便于用户理解；

●通过按钮进行浏览，这样比不停地在工作表中作标记更方便。

图2-4（Menu_structure.xls）中的模型运用了按钮或者说是组合框连接另外两个名称为“Inputs”和”Reports”的工作表。

这两工作表中也有按钮能让用户再回到主菜单下。

我们将会在下一章节中详细阐述这些特征。

用户能迅速了解哪个工作表是可以利用的，而且也能被指引到需要数据的地方。

图2-4（原书第14页，fig.2.4）

2.8、管理报告

对于较大的模型，管理报告和小结通常是必不可少的，它们应该在一个完整的管理报告区域中。

不是每一个人都需要所有的细节和计算，小结可以帮助用户了解计算结果和获取其中重要的信息。

例如，一个项目管理的应用应该在模型中显示覆盖比率和证券化程度。

2.9、未来发展

模型在的发展是很重要的：

一个预算模型可能到下一年就需要添加新的变量，而一个结构化的模型有助于模型未来的发展。

我们可以通过查看如何添加新变量和审核设计中的漏洞来测试它的可发展性。

此外，灵敏度分析表和方案使得用户能在同一模型中得到多方面的答案，并且在改变输入的基础上进行差异分析。

一个单一的净现值模型对于决策的制定是远远不够的，模型的发展应该包含一些更深入的对最终结果8产生变化程度的测试。

风险也是一个决定性的因素，因此模型的设计需要包含风险因素或模拟技术。

模拟使得模型的发展包含一系列的输入而不仅仅是单一的数字，这样的输入就会得出相应的一组结果。

类似地，图表在向操作者或其他人显示结果上也是很有用处的。

人们常常可以通过图表轻易地理解复杂的东西。

例如，一个现金流的模型包含在最低限额之上的现金流的图表。

2.10、测试

测试是很有必要的，它可以确保没有计算上的错误和由模型得到的信息流是正确的。

在图2－2的表格中，结果可以通过贴现率表或者用其他的财务计算器的结果来检验。

测试的数据必须要用到所有的按钮、输入、频率、支付方式等。

后面会有一个章节专门介绍一些用来检验模型准确性的技术。

2.11、保护

如果模型会被别人使用，那么模型的保护就变得非常重要。

如果作者将所有的输入都聚合在一起并且用不同的颜色给它们“加密”，这种保护就变得很简单。

所有的工作表格都能被保护，对输入单元格也可以不进行保护。

保护工作表和工作簿其实也是保护了模型设计者的成果，并且这样做可以确保模型在设计者的意图下进行使用。

例如，如果一个预算模型让用户使用，而用户却在单元格里输入具体的数字而不是公式。

由于没有对模型进行保护，其后果是威胁到了模型结果的准确性。

当然，其他人在使用的时候可能会从检查每一个单元格可能的错误开始。

2.12、备案

许多作者嫌麻烦而不愿写出一些关于工作表的说明和工作表的结构说明。

这样做可能会面临这样的一种风险：

他们或者他们的同事在日后对模型编码进行维护的时候可能会在某些方面遇到困难。

而许多模型也就因此成为了“宠物设计”，因为任何一个计算机程序都需要相关的背景信息。

理想的情况是将说明写进模型而不是写在文件的一小片纸上，并且要遵循：

●采用一个特定的设计或模板的原因；

●写出关键的公式和计算步骤；

●规则和方法。

2.13、同行的意见

模型的用户和同行们常常会对模型提出建设性的意见。

尽管这个过程很痛苦，尤其在你耗用的大量的时间来制造出这样一个杰作之后。

但是，潜在的用户需要尝试输入数据并且对模型的操作方式是否人性化给出评判。

让用户参与到模型的设计过程，并且询问他们的意见能够充分调动用户的积极性。

这方面主要的因素包括：

●有清楚地对话框，使用起来很方便；

●用户操作指导，包括从输入开始经过计算最后得出答案和报告；

●将审核和更正的复杂性降到最低点；

●结果显示的明晰性。

以上的13点将会帮助你更有序地工作。

检查一下你自己的模型，看看在你的成果中包含了以上的哪些方面。

显然，模型的复杂程度影响着你所需要完成的工作量。

但是，以上这些是作者实践了很多年总结出的经验，它们会帮助你更好的进行实践。

我们在下一章节中会讨论几种能使你的模型功能更强大的特征。

随后的章节会应用这些设计方法来重新建立第一章中的那个基础模型。

这样做的目的是为了向您展示如何应用Excel来建立功能更强大而没有错误的模型。

2.14、小结

模型的设计是个性化的，经过一段时间的工作，我们会形成自己的风格。

设计的连贯性和遵循统一的设计方法是很重要的。

本章对设计步骤的讨论并不是面面俱到的，主要包括了以下几个方面的容：

●建立所有的模型都要遵循的设计步骤和方法；

●确立目标和对象；

●了解客户的需求和必要的客户交流；

●建立关键变量及其规则；

●将计算分解成若干个可操作的群组；

●建立个体模板；

●设计菜单结构；

●管理报告和小结；

●模型发展，如，灵敏度分析；

●测试和审核；

●应用操作的保护；

●备案；

●征求同行的意见。

第3章特征和技术

在上一章里，我们讨论了模型设计的计划和在设计中的逻辑问题。

本章将会着重介绍模型中包含的让用户感到更亲切的特征。

本章不会把所有的特征一一详尽地列出，但本章所要达到的目的就是要揭示最初的和最终的模型之间的差异。

本章讨论的特征包括：

●格式；

●数字格式；

●框线和边框；

●颜色和图案；

●给输入和结果特别着色；

●数据的有效性来控制输入；

●控制——对话框或按钮；

●条件格式化来显示数据上的变化；

●加载宏，以增加更多的财务功能；

●加入更多的财务公式；

●文本框和更新标签；

●记录版本序号、作者、开发时间和其他信息；

●运用名称以便使公式更便于理解；

●将命名表作为备案的一部分记录；

●单元格批注；

●图和表；

●多样化的图表来显示个体线条；

●用于灵敏度分析的数据列表；

●条件分析（如果-怎样）；

●单变量求解；

●最优化和目标区的规划求解；

●使用模板来加快模型开发。

图3-1的模型是在文件Features.xls中。

本章节中的每一部分都包含在这个模型里。

打开文件并依次点击来浏览工作表的制作过程。

图3-1（原书第19页，图3.1）

图3-1是一个简单的净现值模型，这个模型把各期的现金流量以10%贴现率折现后相加。

在C14这个单元格中，净现值就是把折现后的现金流量相加得到的。

选择“工具（T）”，“选项（O）”，“视图（V）”，你可以选择“视图（V）”中的“公式（F）”。

这个选项能让你看到计算用的公式（见图3-2）。

你还可以同时按下“Ctrl+`”在公式和通常的状态之间进行切换。

正如你所看到的，它只是根据如下的现金流量的公式来计算净现值：

图3-2（原书第19页fig.3.2）

图3-3显示了每个单元格的公式

图3-3（原书第19页fig.3.3）

3.1、格式

图3—4中的模型是将数据输入和公式计算混合在一起，因此我们的第一项工作就是对它的布局重新进行设计。

这包括：

●插入边框线并移动输入；

●根据输入的现金流量进行计算；

●在可能检查输入值的地方做标签。

例如B9=C3；

●用一个输入来更正所有的因素；

●用不同的字体来做出区分。

现在，标题、输入、摘要和结果就用黑体字清楚地做出来了。

这个模型遵循了在图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）。

图3-7（