用excel建立财务模型1(doc56)-财务综合(编辑修改稿)

用excel建立财务模型1(doc56)-财务综合(编辑修改稿)内容摘要：

书第 24页 ) 图 310(原书第 24页 ) 、颜色和图案 颜色和图案的使用也可以被用来帮助定义输入和输出。 在图 311 中，非彩色用于表示输入，灰色表示答案。 这些颜色的使用是个性化的，但重要的是保持多种颜色和格式的一致性使用。 、输 入和结果的特殊颜色 对输入值特别着色可有助于显示在哪里需要输入数据。 作者常常用蓝色表示输入，绿色或黑色表示总计，红色或黑色表示计算结果（如图 312所示）。 颜色的使用应该保守一些，因为太过于炫耀的颜色会不合大多数人的口味。 图 311(原书第 25页 ) 图 312(原书第 25页 ) 对模型添加几种颜色之后，模型变得更加清晰，并且颜色的使用也迫使作者从一致性的角度考虑将输入放在一起。 现在的这个模型比原先的那个模型要有序的多，且更方便用户使用。 、数据的有效性 数据有效 性的设置使你可以设定单元格数据的界限。 这样一来，如果你需要输入日期，那么用户只能输入日期，或者当你需要输入一列七个字的文本，用户也只有这么做才能继续往下进行。 有效性的设置可以通过选择主菜单栏的“数据 (D)”，“有效性 (L)”来设置。 （如图 3— 13所示） 此资料来自 企业 (), 大量管理资料下载 18 图 313(原书第 26页 ) 在这个例子中，最好对以下三个输入有个最小值的限制： 资本价值 大于 0的正数 每期的现金流量 大于 0的正数 贴现率 0和 1之间的正数，如 100% 这个对话框有三个标签，当鼠 标指针靠近单元格时会出现输入信息。 若是错误的输入，会弹出出错警告的对话框。 你也可以通过不选中对话框来选择不显示输入信息（如图 3— 14所示）。 图 314(原书第 27页 ) 出错警告显示的是你输入了一个错误的数字，并且在你遵守了数据有效性条件后才能继续（如图 3— 15所示）。 这意味着资本价值应该而且只能是个正数。 图 315(原书第 27页 ) 由于每期的现金流量也符合同样的有效性约束条件，你可以选择“复制 (C)”，然后在“编辑 (E)”，“选择性粘贴 (P)”，“有效性”来完成， 而不用再一次的键入有效性约束条件。 图 316(原书第 27页 ) 最后的一个有效性条件只是简单的保证贴现率小于 100%。 这样做是为了缩小输入的范围，也是为了让用户能得到正确的答案。 如果用户试图输入一个数值为 120%的贴现率，那么将会出现如图 3— 17所示的那个错误信息。 再次说明，这是从用户的角度来看模型的使用，并试图让用户知道什么是他要做的。 、控制 —— 组合框和按钮 更多的加速输入和帮助用户的操作可以在“视图 (V)”，“工具栏 (T)”，“控件工具箱”中此资料来自 企业 (), 大量管理资料下载 19 找到。 在 Assess或 Visual Basic中都能找到类似的控制器。 在这个例子中，你可能希望用户输入一个在 8%—— 12%之间的贴现率，并且每个数字之间间隔（步长） %。 这样的话我们就不可能利用数据的有效性来进行处理，必须另觅他途。 因为数据的有效性只是限定了数据的上下限。 图 317(原书第 28页 ) 第一步是先在工作表的底部开辟一块工作区域，然后通过剪切和粘贴将贴现率输入到区域中（如图 3— 18 所示）。 这是为了确保在单元格 C7 中建立控制器的时候模型仍能正常工作。 图 318(原书第 29页 ) 工作区域 显示了一个数值间隔，然后贴现率从 8%开始并且每次都按步长值增加。 图 319(原书第 29页 ) 最终的工作区域将显示从 8%到 12%的贴现率（见图 3— 19）。 步长值并不是固定不变的，它取决于单元格 C26 的值。 由于这些是变量，而大多数的用户都不需要了解这些细节，所以我们把它们放置在独立的工作区域并清楚的标明。 组合框控制器中有八个可能的选择，它返回你所选择的序号。 它会把序号返回到单元格C27中。 点击“工具栏 (T)”中的“组合框”按钮，你就可以在单元格中画出组合框。 你必须给控件确定从那里获取输 入信息和在那里输出结果。 在图 320中我们可以看出，贴现率的取值是在区域 B28:B35，结果的输出是在单元格 C27中。 图 320（原书第 30 页 ） 最后一步是在显示贴现率的单元格 C28 和显示序号的单元格 C27 之间建立链接。 由于C28 用于计算，需要将它的颜色改成红色以免混淆。 函数“ OFFSET”可完成这个功能 , 你可以在“插入 (I)”，“函数 (F)”，“查找与引用”找到它（如图 3— 21所示）。 此资料来自 企业 (), 大量管理资料下载 20 图 321（原书第 31 页 ） 通过这个函数，你可以先定义一个起始数值，然后向下 移动 X 行向左右移动 Y 列，最后得到另一个数值。 在这个例子中，起始数值定义为单元格 B27 的值，并且向下移动的行数为单元格 C27 的值，没有左右移动。 最终得到控制器所需要的数值用作贴现率进行现值的计算。 这个组合框可以控制用户的输入，并且使得对单个贴现率的选择更加迅速（见 图 3— 23）。 特别需要说明的是，用户仍然可以在 B2 C2 C27中输入数据。 这个组合框运行一个宏或依照程序更新单元格的数据，但这并没有受到保护。 在工具栏中同样还有其他的控制器能使你的输入更直观。 比如说，数据调节器和滚动条就可 以使你只通过点击就能够增加数值，并且还可以根据具体输入变动的间隔需要来设置点击移动的间隔。 图 323（原书第 32 页 ） 工作表 Spinner_Scrollbar中应用了这两种控制器进行了替换操作。 在那里，你可以选择一个最大值、最小值和增加值。 这种操作会有一点点的复杂，原因是这个控制器没有分数。 因此你就不得不从滚动条的位置来计算一下具体可能的贴现率。 在图 324 中的滚动条的作用是取从 1 到 8的数值并且以一个单位递增。 滚动条连接的单元格是 C26，在单元格 C27中的函数“ OFFSET”用的是选 择的序号。 图 324（原书第 33 页 ) 、条件格式 条件格式可以使我们根据单元格的不同数值进行不同的单元格显示。 这里的不同指的是字体、边框和图案的不同。 在这个例子中，我们将会引入一个很有用的操作试验来说明如果如何根据项目的成功与否来相应地显示结果。 在图 3— 25 中有一个新的单元格 C7，它定义了进行操作性试验所需要的最低的净现值此资料来自 企业 (), 大量管理资料下载 21 7,000。 用格式按钮来设置格式，使得当现值大于或是等于单元格 C7的值时，显示粉色。 试验的结果在图 3— 26 中展示，在图中可以看出 %的贴现率能使项目达到目标。 图 325（原书第 34 页 ） 图 326（原书第 34 页 ） 你还可以通过点击“添加 (I)”键，并且选择“编辑 (E)”，“选择性粘贴 (S)”，“格式”来复制，以便添加更多的格式。 、函数的使用以及各种类型的函数 在模型中已经使用了函数“ OFFSET”；但是，利用 NPV公式会使净现值的计算变得更为简单。 现在，从单元格 C17到 H19这个区域中的单元格都已经被直接赋值，这也就意味着可能会有 12处错误存在。 使用公式的目的就是为了减少直接赋值以便减少潜在的错误。 这种利用 Excel的 解决方法比查贴现率表的方法要便利多了。 你可以在主菜单栏选择“插入 (I)”，“函数 (F)”，或者从标准的工具栏中找到所用函数。 函数被划分成几个部分以便查找。 选择财务函数并找到 NPV（见图 3— 27）。 图 327（原书第 35 页 ） 净现值公式可将每期的现金流贴现。 然后要做的就是选择 15年并从 0期开始添加各期的现金流量，再利用公式进行计算。 =NPV(C25,D15:H15)+C15 根据这个公式可以得到贴现率为 %时正确的答案是 7,。 注意到现在的工作表在进行了必要的删减后 已经变得更简洁了。 你总是可以通过点击问号标示（如图 3— 28所示）来获取关于公式的帮助信息。 你也可以通过选择查看来浏览一些替代公式的列表。 图 328（原书第 36 页 ） 、加载宏以便获得更多的功能 对 Excel的典型安装只包含了基本的函数公式。 但是除此之外，还有大量的公式可供使此资料来自 企业 (), 大量管理资料下载 22 用。 例如， NPV 函数假定每一期包含同样的日数。 XNPV 函数则允许你输入现金流实现的日期。 （在第 19章中讨论的估价模型就是用到了这一函数。 ） 选择“工具（ T）”，“加载宏（ I） … ”，“分析工具库”，确定你已经可以使 用扩展函数。 选择此选项并选择“确定”进行安装。 这样一来，这些函数在你每次打开 Excel后都能使用。 如果没有“加载宏（ I） … ”选项，那么你就需要重装一下 Excel了。 图 3— 29 中显示的工作表运用了 XNPV函数和 EDATE函数。 EDATE 函数是一个日期函数，它的功能是每次将事先确定的日期增加或减少若干个月。 你先提供一个起始日期然后通过调用函数可以将时间提前或推后若干个月。 由于提前或推后的步长是可以变动的，因此在输入区域中又有了一个新的控制。 它在单元格 D13:H13 中确定函数需要的起始月份，并指向函数的结果。 再次输入初始的现金流得到的结果是 7,，与原来的结果 7,。 图 329（原书第 37 页 ） 、文本框和更新标签 你可以进一步增加模型的明晰性，方法是通过建立标签进行更新，以及在结果上提供一些文字说明。 如果净现值在底线之上，你就应该建立一个标签来通知用户。 在文件 中的工作表 Text就提供了两种改进方法：  在标签上显示贴现率。  反馈计算后的净现值。 现在单元格 B20 有一个更新标签。 Text 函数将数字转化为文本并保留原来的格式。 这将显示百分比到 小数点后两位；“ amp。 ”用来连接文本串： =NPV at amp。 TEXT(C31,%) 反馈用一个 IF 函数来管理，以便显示结果在底线之上或之下时的文字串。 为了减少代码， IF中的文本陈述将由净现值决定。 =NPV is amp。 IF(C20=C7,above,below)amp。 the limit of amp。 TEXT(C7,0) 现在工作表能告诉用户所用的贴现率并对结果进行说明（见图 3— 30）。 Excel 将根据结果做出决定，而无需用户再花时间去比照结果。 图 330（原书第 38 页 ） 此资料来自 企业 (), 大量管理资料下载 23 、记录版本序号、作者等 正如在前面的章节中提到的那样，给模型备案是模型的一部分。 在复杂的模型中，我们需要记录模型的版本序号、作者姓名和与模型运行有关的说明文字。 在模型建立一段时间后，你可以比照并记录不同版本之间的差异。 如果你要避免重大的错误，这样做是特别重要的。 另外，版本序号会出现在你打印的每一张工作表的顶部（见图 3— 31）。 图 331（原书第 39 页 ） 这部分同样也适用于给页面附图片和文字说明。 将文字说明写入模型自然是有益的，你也可以通过选择“格式 (O)”，“ 工作表 (H)”，“隐藏 (H)”来隐藏一个工作表。 、使用名称让公式更易理解 名称可以使公式更便于理解：例如，不用 C28 而使用 Periodic cash flow。 就如上 图 3— 31 中的 Version、 Author等，他们使你的模型更加标准，如：“ =Version”表示插入版本序号。 本书里的文件用了很多这种标准的名称，例如： Author、 Company、 Version和 Product。 你可以在“插入 (I)”，“名称 (N)”，“定义（ D）”来定义名称，或者在 Excel 里用标签在选中的单 元格区域的一侧建立多个名称（见图 3— 32），通过“插入 (I)”，“名称 (N)”，“指定 (C)”完成。 图 332（原书第 39 页 ） 这里指定名称在左边的栏中，如 Start_date（见图 3— 33）。 图 333（原书第 40 页 ） 现在函数就很容易理解了，因为它利用了在单元格 C20中的各期利率。 =XNPV(Int_Rate,C18:H18,C13:H13) 如果你复制一个包含名字的工作表，同时新的工作表也会继续引用原来的工作表。 简单说，如果你将一个工作表复制到一个新的工作簿， Excel会在这两个工作簿之间建立连接。 你可以通过选择“编辑 (E)”，“链接（ K）”来检查。 如果不是这样的话，你就不得不通过手此资料来自 企业 (), 大量管理资料下载 24 动来移动并重新输入单元格的公式。 图 334（原书第 40 页 ） 、粘贴名称列表作为备案的一部分 粘贴名称列表作为备案的一部分是很有用的，因为它给日后的审核提供了一种线索。 你通过选择“ 插入 (I)”，“名称 (N)”，“粘贴 (P)”，“粘贴列表 (S)”来完成。 、批注 附批注的单元格是给单元格附上批注，以便提供背景信息或帮助用户使用。 到“插入 (I)”“批注 (M)”，或右单击单元格，输入文本信息然后设。