高级财务建模最佳实践：用于智能、无错误建模的技巧

执行摘要

简介：财务模型

财务模型是每个公司财务工具包中不可或缺的一部分。 它们是详细说明给定企业的历史财务数据、预测其未来财务业绩并评估其风险和回报状况的电子表格。 财务模型通常围绕三个会计财务报表构建，即：损益表、资产负债表和现金流量表。 大多数公司的管理至少部分依赖于财务模型的细节、假设和输出，所有这些对上述公司的战略和资本决策过程都至关重要。

本文为希望在构建财务模型时遵循专家最佳实践的新手和中级财务专业人士提供分步指南。 对于高级财务建模者，本文还将展示一系列专家级技巧和技巧，以优化时间、输出和建模效率。 让我们开始。

规划你的模型

与所有复杂的事物一样，构建财务模型（“模型”）的第一步是仔细布局蓝图。 在建模过程中进行计划外、未预料到的结构更改可能会非常耗时、令人困惑且容易出错，尤其是在模型的适配器与其作者不同的情况下。 这些挑战很容易在演习开始时通过一些专门的计划时间来解决。 我建议您的计划阶段如下：

1. 定义模型的最终目标。

明确定义模型的目的是确定其最佳布局、结构和最终输出的关键。 作为此过程的一部分，请花时间确保您的模型的主要利益相关者在开始构建之前签署您的蓝图和流程设计。 这使他们有机会表达任何最终的偏好或意图，从而避免任何“范围蔓延”（行业用语）或痛苦的重定向。

2. 了解构建模型及其使用寿命的时间表。

虽然次要于模型的最终目标，但了解构建模型的时间表以及模型将使用多长时间也是确定建模练习方法的重要输入。 长期和长期（使用寿命）模型通常是从​​头开始定制的，包括大量的操作细节、灵活性和灵敏度能力。 对于更直接、更短持续时间的运营或资本项目模型，建模人员通常会使用预制模板来最大限度地提高施工速度，同时最大限度地减少错误。 此外，模型模板也往往更熟悉，因此更容易被组织内的不同利益相关者使用/操作。

3. 确定“细节”与“可重用性”之间的最佳权衡。

在决定所需的详细程度和模型可重用性之间的最佳权衡时（即，模型是打算为多种事务类型/目的重新设计，还是仅仅为这种一次性练习而设计），在我职业生涯的大部分时间里，用于决定一个人的模型选择/方法的有用框架如下：

随着蓝图/规划阶段的完成和关键决策的确定，我们现在可以进入下一阶段的建模。

构建模型

在这个时刻，我们准备打开 Excel 并开始考虑结构化。 在可能的最高级别上，每个模型都可以/应该分为三个部分：(a) 输入/驱动因素，(b) 计算（预计财务报表），和 (c) 输出。 将这些部分分离得越好，就越容易审核和修改模型，同时最大限度地减少错误并及时优化。

对于我构建的几乎每个模型，我都遵循相同的结构方法； 我和我各自的利益相关者一直认为这种方法实用、易于理解且最终有用。 其部分如下：

1. 封面（选项卡）：项目代号、模型意图描述、作者联系信息以及任何适用的免责声明。
2. 驱动因素选项卡：输入和假设。
3. 模型选项卡：计算（即三个财务报表的预测和计算）。
4. 输出选项卡：对模型最重要亮点的简洁、简洁的总结。
5. 敏感度选项卡：管理层在过渡到决策过程时将依赖的场景、敏感度和数据结果的范围。

我会为你分解这些部分，一次一个。 如下：

封面

封面是与您的作品的第一个接触点。 虽然它是最简单的构建，但如果做得好，它会给人留下很好的第一印象，并清楚地解释即将发生的事情。 一个简单的指导性封面通常是最好的方法，通常包括以下部分：

1. 模型名称：不言自明。
2. 模型的目的：描述其预期用途的段落
3. 模型索引：一个简要表，详细说明每个选项卡的描述和用途。 本节对非金融运营商特别有帮助，通过突出显示他们需要使用哪些选项卡进行输入、在决策过程中需要关注哪些输出以及他们应该使用哪些复杂的计算选项卡，帮助他们“消化”模型的结构和流程保持不变。
4. 模型版本历史记录：花几秒钟时间按日期键入对模型所做的关键更改始终可以节省时间，尤其是在您需要回溯和反转/修改更改时。 对于您在未来时期可能用作模板的复杂模型和模型尤其如此。
5. 作者的联系方式：不言自明
6. 适用的法律免责声明（如有，由您的法律顾问提供） ：不言自明

请注意：我建议始终将封面锁定给任何未经作者明确授权进行更改的所有人。

驾驶员选项卡：输入和假设

紧接着模型的封面，必须来到驱动程序（输入）选项卡。 您必须确保此选项卡清晰、简洁且易于理解，因为这是非金融运营商最常操作的选项卡。 我通常建议在输入选项卡中实现两个输入部分，一个用于静态输入，另一个用于动态输入。 我所说的静态输入是指不随时间变化的输入，例如假设的“发电厂规模”或“公司的初始债务余额”； 我所说的动态输入是指随时间变化的输入（例如，逐月或逐年），例如“通货膨胀”假设、“债务成本”或“收入增长”假设。

示例驱动程序和假设选项卡示例

在上述静态与动态输入部分中，我建议您还清楚地将数据分为两种：（1）硬编码的数字，无论假设情况如何都不会改变，以及（b）敏感参数，将驱动不同的假设场景，并最终驱动您的敏感度表。 但是请注意，您永远不会完全知道哪些参数将构成灵敏度参数，而您直到项目的最后阶段才知道哪些参数。 有关灵敏度建模的更多信息，请参阅以下文章。

模型选项卡：详细计算和操作构建

此选项卡代表模型的核心，所有输入、假设和情景共同作用，将公司的财务业绩预测到其外部年份。 在此选项卡之外，还将运行各种假设驱动的情景以及将在最终战略决策之前进行的评估部分。

示例模型选项卡示例

场景和敏感度选项卡

授权的第三方模型运营商将经常使用“场景和敏感性”选项卡，即使只是为了选择他们选择的预编程场景。 出于这个原因，您应该直观地构建场景，保护实际场景免受外部编辑，并构建足够多的敏感度，以便少数预先编程的场景足以在敏感度表（下面的示例）后产生可能的结果的广泛视图也建。

供您参考，我在整个职业生涯中所依赖的场景格式结构如下，仅作为一种示例：

关于上图的几点说明：

1. 模型用户应该只能编辑它，因为他们将在其中选择场景编号。 该数字是指电子表格右侧显示的场景之一。 然后，用户将在第一列中呈现所选场景（在本例中为第 6 号）。 这是模型中引用的场景和敏感性电子表格的唯一列。
2. 在此处添加几个描述字段，以有效总结所选场景所代表的内容。
3. 我总是觉得它非常有用，特别是如果其他人将使用该模型，添加一个指定每个输入单元的列。
4. 此列拉入所选场景（在本例中为第 6 号）的杠杆统计/字段，它们都显示在右侧（蓝色）。 驱动它所需的公式是一个偏移函数，即“=OFFSET（在上面以红色突出显示的第一个场景的左边插入空单元格，选择/突出显示场景的单元格）。” 请注意，两个单元格之间有一个空格，因此两个逗号 (,,) 不是拼写错误。
5. 将您的假设分组到宏观类别和子类别中。 这将帮助您（建模者）和您的用户清楚地了解模型选择的场景。

样品灵敏度表示例

输出选项卡

输出选项卡是模型操作员最常使用的选项卡。 多年来，我发现自己倾向于至少三个用于中复杂模型的输出选项卡：

1. 财务输出选项卡：这是模型选项卡中详细财务数据的简短摘要。 它们通常以年度为基础（即使模型可能是季度）。 此输出应介于 50 到 150 行之间，并应显示计算选项卡中的所有关键行项目。 请务必提供足够的详细信息，以确保用户不会在此选项卡和各种计算选项卡之间切换。 另请注意，作为最佳实践，任何输出选项卡都不应重新执行任何计算，并且此信息应仅包含直接链接。
2. 执行摘要选项卡：此选项卡非常标准，通常显示图形、图表和表格的组合，尽可能简单易懂地说明高管和董事会成员导航其所需的各种趋势、分析和关键摘要统计数据关键决策。
3. 特定输出选项卡：此选项卡包含特定输出，通常由投资备忘录模板、投资委员会演示文稿或高管和董事会成员提出的要求决定，以达到他们的决策点。

模型输出选项卡示例，包括表格、图表和图形

至此，模型的构建阶段正式完成。 我们可能会将注意力转向我在文章开头提到的一些专家级建模最佳实践。 让我们从格式化开始。

格式化你的模型

首先，重要的是要注意每个公司/集团可能有自己的偏好或内部做法。 因此，在构建时，重要的是首先检查并遵守您各自公司规定的任何格式。 然而，在缺乏公司特定实践的情况下，以下内容详细介绍了华尔街用于格式化模型的通用语言。

财务建模的第一个也是最简单的格式化方法是使用一致且可识别的配色方案来表示不同类型的单元格和数据。 如下：

蓝色= 输入或任何硬编码数据，例如历史值、假设和驱动因素。

黑色= 源自同一工作表的公式、计算或参考。

绿色= 公式、计算和对其他工作表的引用（请注意，尽管某些模型完全跳过此步骤并为这些单元格使用黑色）。

紫色= 到其他 Excel 文件的链接、输入、公式、引用或计算（再次注意，某些模型完全跳过此步骤，并且这些单元格也使用黑色）。

红色= 要修复的错误。

格式正确（颜色编码）的财务摘要示例

请注意，没有内置的自动化功能可以根据上述通用颜色编码标准对您的 Excel 电子表格进行颜色编码。 相反，您可以设计自己的宏来实现这些结果，然后创建快捷方式组合来自动对您的工作进行颜色编码。

在我最近的某个时候，我从一位同事（今天我感谢他）那里收到了以下宏（包括详细说明），这为我节省了几个小时的体力劳动。 如果可以的话，我想分享它们。

宏创建说明（适用于 Mac 和 PC 版本的 Excel）：

1. 同时按 Alt + W + M + R 来命名并开始录制您的宏。
2. 同时按 F5（“跳到单元格”）然后按 Alt + S 以到达“转到特殊”菜单。
3. 点击“O”选择常量，点击“X”取消选中文本。
   
4. 现在同时点击 Alt + H + FC（或 Ctrl + 1），并为这些常量选择蓝色字体颜色。
5. 按 Esc。
6. 现在做同样的事情，从 F5 开始，但选择公式 (F) 而不是常量，然后按“X”取消选中文本。
7. 现在点击 Alt + H + FC（或 Ctrl + 1）并为这些常量选择黑色字体颜色。
8. 使用 Alt + W + M + R 或 Alt + T + M + R 停止录制宏。

其他工作簿的链接

查找其他工作簿和工作表的链接很棘手，您很可能必须使用 VBA 才能使其正常工作。 这是基本思想：搜索符号“！”的存在。 在工作簿中包含公式的每个单元格中，然后将字体颜色更改为绿色。 您将需要在 VBA 编辑器中对其进行修改，并使其成为遍历所有“！”实例的for each循环。 您找到，然后更改其中每一个的字体颜色。

请注意，此快捷方式仍然无法 100% 工作，因为某些公式会引用其他工作表中的单元格，而不会直接链接到它们。 幸运的是，绿色单元格比黑色或蓝色单元格少，因此上述方法在大多数模型中都非常有效（您可以在出现或遇到其他工作表时手动将其余链接有机地格式化）。

审计模型的最佳实践

在建模时，我鼓励您始终牢记这个问题：“我是否让这个模型易于审核？” 因为对于执行的每项任务、创建的公式和建立的链接，总会有一种更快、更“肮脏”（用行业术语来说）的方式来完成这项工作。 这样的黑客和技巧，无论当时看起来多么聪明，尤其是在一段时间之后，总是会被遗忘，并会导致难以追踪的错误。 牢记第三方审阅者将指导您完成整个过程，并帮助您在关键时刻做出正确的决定。

以下是有关如何以审核员心态进行构建的一系列最佳实践。 如下：

1. 一排一式

每行应该只有一个公式，这意味着在任何给定行的第一个单元格中使用的任何公式都应该是在整个行中统一应用的相同公式。 用户应该通过查看每行的第一个单元格来了解模型的结构，因为他们垂直地沿着模型向下移动。

虽然这在原则上很简单，但它经常被违反以进一步强调。 当电子表格在“历史财务”组列和“外部年度预测”之间拆分时，通常会发生一个常见的情况（参见上图，标题为“格式良好（颜色编码）财务摘要的示例”，作为参考）。

解决这些实例的一种简单方法是使用位于电子表格顶部的标志（例如，1/0、 TRUE / FALSE ），然后使用IF语句通过模型的主体进行引用。 这在工作中的一个简单说明如下：

Excel 建模中“标志的使用”示例

2. 公式中没有嵌入硬编码的数字

永远不要使用嵌入在公式中的硬编码数字，因为如果用户对模型不太熟悉，就很难发现它们。 相反，要清楚地突出显示输入/硬代码并将其与公式分开； 更好的是，收集所有输入/硬代码（视情况而定）并将它们聚合在同一个选项卡中。 随后让您的公式根据需要从所需的单元格和相应的选项卡中提取/引用它们。

3. 简单总是更好

避免复杂的公式总是更好。 相反，将您的配方分解成易于消化的步骤。 这种方法通常会创建更多行，而不是一个看似整齐的行，从而导致更大的电子表格； 但第三方更容易跟踪和审计。

4.始终遵守您的标志约定

您应该在零时决定您的签名约定/密钥是什么。 举例来说，在模型的设计阶段问自己：“成本、费用、扣减、折旧、资本支出等将呈现为负数还是正数？” 我个人的偏好是始终将成本表示为负数，原因有两个：(a) 总和始终是直和，您将最大限度地减少用户错误，以及 (b) 仅使用符号更容易发现错误。

5.避免命名你的单元格，而是依赖Excel的网格逻辑

在可能的情况下，我强烈建议避免命名您的单元格，因为以后很难找到所述命名单元格的源输入（例如，“Inflation”）。 相反，我建议您在公式中依赖 Excel 的网格约定（例如，如果引用位于不同的选项卡或工作簿中，则只需链接到单元格 C4 或位置[Tab Name]l'!G21 ）。

6. 永远不要在多个位置使用相同的输入

简单而透明地组织您的输入。 我建议您将所有输入合并到几个驱动程序选项卡中，并在整个电子表格中从它们的奇异原点引用它们。

7.避免链接文件

避免链接到其他文件。 最好从不同的文件输入您需要的相关数据作为硬编码输入，然后根据需要手动更新。 众所周知，交叉链接会使较大的 Excel 模型崩溃或更新不一致，从而产生难以跟踪的错误。

8. 不要隐藏表格或行

在较长的电子表格中，“分组”行/列而不是“隐藏”它们。

9. 更少、更大的标签比多个更小的标签更好

这种做法是 100% 以经验为基础的。 与跨多个选项卡或更糟糕的是，跨链接的多个电子表格相比，跟踪和审计跨一个大型连续电子表格的数据更容易。

10. 通过位于一个选项卡中的“聚合错误检查”在整个模型中创建检查

检查是快速检查模型完整性的最简单方法。 “检查”涵盖了从确保应该与实际挂钩的总计到确保一个人的资产负债表实际平衡的所有内容。 我通常在每个电子表格的顶部或底部建立一些检查，然后将它们合并到一个单独的“检查选项卡”中。 这确保了很容易在模型中找到错误，然后跟踪错误的来源。

资产负债表“支票”样本

请注意，仅仅依靠检查来验证模型的完整性绝不是一个好主意，因为检查通常是非常高级的。 但这是一个很好的起点。

开拓 - 高级用户：Excel 提示

本节为我们的高级用户介绍了一些非常有效的 Excel 最佳实践。 这些可能需要一些调整，但以后应该可以节省几个小时的工作并且实施起来相对简单。 简而言之，它们如下，简明扼要的要点：

1. 尽可能多地使用键盘快捷键。 互联网上有几个适合不同需求的 Excel 键盘快捷键文件。 我将在这里引用一对：
   • 建模关键备忘单
   • 综合 Excel 提示列表
2. 使用 F5（“转到特殊”）快速找到所有硬编码的数字或公式。
3. 使用 Trace Precedents 和 Trace Dependents 来审核模型。
4. 使用XNPV和XIRR允许将自定义日期应用于现金流，在进行回报分析的途中； 这与 Excel 的NPV和IRR函数相反，后者隐含地假设计算的时间间隔是等距的。
5. 在VLOOKUP函数上使用INDEX MATCH函数来查找大型电子表格中的信息。
6. VLOOKUP几乎总是优于IF语句； 适应它。
7. 养成在公式语法中包含IFERROR的习惯。
8. 结合使用日期函数、 EOMONTH和IF语句来使日期动态化。
9. 在展示或分享财务模型时移除网格线； 它使输出文档更干净、更优美。

爱它还是恨它…

无论喜欢还是讨厌，Excel 在企业财务、分析和数据驱动的决策制定方面都是无所不知、无所不在、无所不能的。 不管你信不信，它不一定是令人生畏或痛苦的，即使对于新手或外行也是如此。 就像生活中的大多数事情一样，练习、一致性和对细节的关注（在 Excel 的情况下，快捷方式）将使您获得最大的成功。

一旦你熟悉了这个应用程序，你会发现它是一个强大的生产力和数字故事讲述工具，即使在你的个人生活中，你也很少能够在没有它的情况下使用它。 随着您在 Excel 流利程度的各个阶段取得进展，我祝您一切顺利，并鼓励您将本文作为您经常参考的实用指南。