作为Esika的创始人，这是一家专注于精算建模和财务模型开发的咨询公司，我经常收到客户的请求，要求对他们的财务模型进行审核。通常，这些审核的目标如下：

识别建模方法中的潜在问题

公式审核

模型中的数据流分析

优化模型以减少计算时间

自动化模型以消除容易出现操作错误的手动任务

大多数情况下，这些财务模型非常复杂，难以理解，因为它们涉及大量输入、数据处理和复杂公式。

此外，这些模型的文档不完善，因为通常它们是基于初始功能描述开发的，但随着时间的推移，它们逐渐得到了改进，而没有更新原始功能描述。

因此，当我们处理这些模型时，总是需要花费大量时间来理解它们并进行准确的审核。

实际上，我们必须仔细审核模型的每个部分：

通过审查所有开发的管道来导入输入的数据摄取

输入数据格式

输入数据处理和清理

数据转换

通过审查所有使用的公式来确保计算的准确性

报告审核

输出审核

这对我们的团队来说非常耗时，并且在审核过程中容易出现操作错误。为了减少审核错误的风险，每次审核都必须由另一位顾问再次审核。

这种对人力工作时间的高要求严重影响了客户的成本。

因此，我们花了一些时间思考如何自动化目前由我们的顾问执行的大部分操作任务。

毕竟，随着AI特别是生成性AI的最新进展，应该有办法帮助顾问处理这些手动任务。

鉴于我们收到的大多数财务模型都是在Excel和VBA中开发的，我们将努力集中在自动化Excel文件的审核上。

本文描述了我们使用Python和AI自动化Excel中开发的财务模型审核每个环节的方法。

Excel财务模型审查的一般方法论

在审查Excel文件时，我们逐个分析每个单元格。这是耗时的部分。有时，同一公式在一列的多行中使用，但必须对每个单元格进行分析，因为在一个统一的列/行中，某些单元格可能包含不同的公式，这种情况并不罕见。

除了这种细致的分析外，我们还会对Excel工作簿进行宏观分析。

通常我们的审查报告包含以下几个部分：

结构和布局

工作表概述： 每个工作表的描述及其目的。

数据流： 显示工作表之间数据流的可视化图表或流程图。

控制和保护： 当前控制措施的概述，如单元格保护和验证（在我们分析的Excel文件中很少可用）。

数据源和集成

外部来源： 外部数据源的详细信息、可靠性及集成方法。

数据导入流程： 对数据导入方法的评估。

公式和计算分析

公式审查： 关键公式的详细审查，包括复杂性和必要性。

错误分析： 错误检查过程中的发现。

风险评估

识别的风险： 与Excel文件相关的操作风险列表。

控制措施： 对现有控制措施的有效性和缺口的评估。

因此，自动化的目标是使用Python生成信息，以填充报告的这些部分。

这使得可以定义工具的功能：

工作簿信息和元数据概述

工作簿属性： 提取并报告工作簿元数据，如作者、最后修改日期和文件大小。

工作簿概述： 提取关键信息，以便对工作簿的复杂性进行概述。

工作表关系分析

依赖关系图： 创建一个表示工作表依赖关系的层次图。

外部链接分析

外部引用检测： 扫描工作簿以查找任何外部链接或数据连接。

公式复杂性分析

唯一公式分析： 计算电子表格中不同公式的数量。

公式计数和类型： 计数和分类公式。

公式复杂性评分： 为每个公式计算复杂性评分。

公式长度分析： 识别异常长的公式。

自动分析方法论

在这一部分，我们描述了用于实现之前识别的工具不同功能的方法论。

工作簿信息和元数据

工作簿元数据

通过工作簿和工作表元数据，我们指的是有关Excel文件的信息，例如：文件名、路径、创建日期、版本、作者等。

通常，要检索这些信息，我们必须打开Excel文件并进入“文件”选项卡的“信息”部分：

为了在我们的工具中自动化检索这些信息，我们使用了包：openpyxl。

Openpyxl是一个用于读取和写入Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它允许轻松创建和修改Excel电子表格。该库支持多种Excel功能，包括格式设置、图表创建和公式评估。Openpyxl广泛用于自动化Excel中的数据分析和报告任务。它特别适用于从Excel文件中提取和操作元数据及单元格数据。

我们在工具中实现的大多数功能都使用这个包，它易于操作且处理Excel文件速度快。

该包的“workbook”对象具有一个名为“properties”的属性，该属性聚合了许多Excel文件的元数据。

我们使用它提取我们审核所需的所有元数据：作者、标题、描述、主题、创建日期、最后修改日期等：

工作簿信息

工作簿元数据是有用的信息，可以让我们了解工作簿的历史并识别开发者。

但是，它并不能帮助我们在开始审查之前评估工作簿的复杂性。通常，当我们对财务模型进行审查时，涉及到多个 Excel 文件。因此，我们必须识别出在我们的计划中需要最多工作量的文件。

为了评估这一点，我们通常会查看以下指标：

工作表数量：工作簿中工作表的总数

外部链接单元格比例：工作簿中带有外部链接的单元格与非空单元格总数的比例。它可以评估工作簿的复杂性。比例越高，理解工作簿所需的工作量就越大，因为我们需要深入研究链接的 Excel 文件。

数据单元格比例：工作簿中硬编码数据的单元格与非空单元格总数的比例。这个比例越高，Excel 文件的审计难度就越大。实际上，如果有很多数据是硬编码的，那么在审查 Excel 文件时将很难识别数据来源。

公式单元格比例：工作簿中带有公式的单元格与非空单元格总数的比例。这个比例越高，审查理解 Excel 文件所需的工作量就越大。

所有这些比例都可以通过使用“openpyxl”包循环计算单元格。

以下是我们工具中这些信息的截图：

工作表关系分析

自从我开始审查使用 Excel 构建的财务模型以来，我最大的痛点之一就是识别工作簿中不同工作表之间的关系。

这是一项耗时的工作，特别是对于复杂模型，例如精算师在保险公司使用的资产负债管理模型，以预测其多个年度的损益表和资产负债表。

这些模型需要大量假设，这些假设通常存储在工作簿的不同工作表中。

因此，实施此功能在我们的工具中非常重要。

为此，我们必须访问每个工作表的所有单元格，并识别工作表何时引用另一个工作表中可用的数据。当财务模型采用标准方法构建时，这很简单：

数据 → 计算 → 输出

但是，并非所有开发人员都采用这种方法，因此识别这些关系非常耗时。

使用 openpyxl 和一些算法遍历每个单元格，我们成功创建了给定工作簿中工作表之间交互的图形。

以下是我们内部 ALM 工具的工具输出示例：

箭头的方向表示每个工作表之间的链接。该图形使用“streamlit”包的“agraph”方法显示。

鉴于 ALM 工具相当复杂，上面的图像并不容易理解。我们在工具中添加了一个选项，以显示给定工作表的链接：

外部链接分析

许多使用 Excel 构建的财务模型需要外部输入。虽然有多种方法可以在 Excel 中集成外部数据，但我们在财务模型中常见的方法是通过外部链接。

开发人员直接使用 Excel 的内置功能“外部链接”对适当源工作簿中的数据进行计算。因此，当我们审查使用这种数据集成方法的 Excel 文件时，我们必须要求客户提供与我们审计的文件相关联的这些 Excel 文件。

这与识别工作簿中工作表之间的链接一样耗时。其自动化使我们的顾问节省了大量时间。

为了实现这一点，我们使用了一种与识别工作簿中工作表之间链接的方法类似的方法。

我们只有一个困难，那就是在“openpyxl”包的设计上 ⇒ 通过该包访问的集成外部链接的公式会被修改。

例如，公式：

=[Book1]Sheet1!$E$4+[Book1]Sheet1!$G$9

当通过 openpyxl 访问时，它返回以下字符串：

=[28]Sheet1!$E$4+[28]Sheet1!$G$9

因此，无法直接提取公式所引用的外部文件名。为了解决这个问题，我们开发了另一个脚本，将数字（28）映射到正确的外部文件名（Book1）。

通过这个脚本，我们可以自动列出 Excel 文件中使用的所有外部链接，并要求客户在可用时发送这些链接，以便进行深入审查。

公式复杂性分析

在“工作簿元数据和信息”功能中，我们揭示了一个KPI，用于评估Excel文件的整体复杂性：含公式单元格的比例。

这个比例越高，审查Excel文件所需的工作量就越大。通常，财务模型包含大量复杂程度不一的公式。

手动操作时，我们通常会逐一检查所有公式并进行分析：

公式复杂性评分：分析公式的结构以评估其复杂程度

识别唯一公式：某些公式在行或列中重复出现。我们仅提取唯一公式以减少审查范围。

公式构建：审查公式构建以评估其是否与功能需求一致

我们的工具自动执行这些操作。

唯一公式识别

使用 Excel 开发的财务模型通常涉及多个公式。当我们对这些公式进行审查时，需要识别唯一公式。

唯一公式是指使用完全相同的输入，仅在列或行上有所变化的公式。

例如，下面的三个公式将被视为相同：

示例 1

C3 formula: =A3 + B3

C4 formula: =A4 + B4

C5 formula: =A5 + B5

它们被视为相同，因为只有行号发生了变化。

类似地，下面的三个公式是以列为导向的，也将被视为相同：

示例 2

A4 formula: =MAX(A3,30

)

B4 formula: =MAX(B3,30

)

C4 formula: =MAX(C3,30

)

它们是相同的，因为只有列发生了变化。

我们开发了一种特定的算法来识别这些唯一公式。

该算法使用正则表达式的组合将 Excel 公式转换为“唯一公式”。

其工作原理如下：

首先识别包含公式的工作表是以行导向还是以列导向。以行导向的工作表是指公式在每一列上应该是相似的。以列导向的工作表是指列的行应该具有相同的公式。

其次，修改公式以将其转换为“中性”公式。在示例 1 中，列“C”的每一行的公式将被转换为“An + Bn”，其中“n”代表行。在示例 2 中，每一列的公式将是“Max(Col3,30)”，其中“Col”代表列。

最后，通过删除算法生成的唯一公式的重复项，生成公式列表。

公式复杂度评分

评估报告中使用的公式的复杂度可以让我们的团队估算审查模型所需的工作量和技术要求。这是在识别唯一公式后进行的。

有很多方法可以估算Excel公式的复杂度。这在很大程度上取决于分析师的经验。

我们在工具中使用了一种非常简单的评分方法：

为评分定义了六个维度：

公式中的Excel函数数量

公式中使用的特殊函数数量：这些函数由我们的团队定义。例如，“Indirect”函数被识别为特殊函数。

公式中的运算符数量

公式中使用的引用数量

公式的总长度

计算所有这些值的加权总和。权重由我们的团队定义。

最终的加权总和代表唯一公式的总复杂度评分。

公式构建

对财务模型的审查通常需要审查模型中执行的计算的一致性。对于这种分析，我们利用了在财务建模和行业专业知识方面的经验。但通常，公式非常复杂，因为在同一公式中使用了许多不同的函数。

为了帮助顾问更快地分析这些公式，我们决定使用生成性人工智能自动描述复杂公式。

我们使用 OpenAI API 调用 GPt-4-turbo 模型，并使用特定提示来描述 Excel 公式。这使我们的团队能够更轻松地审查公式。

公式复杂性分析报告

通过这些方法，我们成功创建了一个通用仪表板，使顾问能够评估Excel文件的复杂性。

它显示以下信息：

包含公式的工作表数量

工作簿中唯一公式的数量

唯一公式数量与公式总数的比率

工作簿中公式的最小复杂性得分

工作簿中公式的平均复杂性得分

工作簿中公式的最大复杂性得分

示例

我们还在仪表板上添加了一个箱线图，表示复杂性得分：

我们展示了一些有用的信息，例如使用的Excel公式的频率：

我们在工具中添加了一个特定功能，用于对Excel文件中的公式进行深入分析。

例如，对于公式“=VLOOKUP(A10,[File1]Sheet1!$A$2:$B$320,2,FALSE)”，用户可以生成一个描述该公式的图表，如下所示：

此外，用户还可以通过调用openai llm生成公式的解释。

结论

通过使用 Python 和 AI 自动化审查过程，我们显著降低了与财务模型审计相关的时间和成本。这不仅提高了效率，还增强了我们审查的准确性和可靠性，最终为我们的客户提供了更大的价值。