我们如何自动化复杂的基于Excel的库存风险分析
想象每个月不得不在Excel中浏览成千上万行数据，只是为了了解哪些库存物品处于风险之中。这是一个繁琐的过程 - 需要花费数小时进行手动计算，脆弱的公式，以及不断担心出错的恐惧。这就是我们如何将这个繁琐的过程转变为一个自动化、可扩展的解决方案的故事 - 一个能够立即将洞察力转化为行动的解决方案。我相信这可以帮助许多其他面临类似挑战的企业。
问题与Excel Excel在我们的库存风险分析中表现良好 - 直到它不行了。
最初的分析是在一个庞大的工作簿中建立的，有超过40个计算列和大量的公式。输入数据来自多个来源——ERP、计划软件和其他报告工具。用户必须在将数据粘贴到主计算文件之前，使用数据透视表和VLOOKUP手动准备数据。每个月，我们都要花费数小时进行这些步骤和重新计算风险。
这限制了我们只能每月更新一次，这意味着洞察通常来得太迟，无法发挥有效行动。手动操作也增加了错误风险，可能导致不正确的评估。显然，我们需要一种自动化解决方案，可以提供及时准确的洞察，减少工作量。
变革的愿景：将复杂性转化为可扩展性
我们的目标是将手动、基于Excel的过程转变成自动化、可扩展的解决方案。
不再需要用户手动从多个系统准备数据，我们实现了数据自动集成。复杂的Excel公式被模块化的SQL视图取代，使系统更易于管理，也更具未来发展的可扩展性。
我们还将控制权交到业务用户手中——让他们通过简单易用的表格直接调整风险参数。这减少了对IT的依赖，并确保风险评估可以迅速更新。
有了自动化系统，我们从每月分析转移到实时洞察，促使更快速和更明智的决策。
拆解 Excel 并使用 SQL 构建
在这个阶段，我们转向ChatGPT o1寻求帮助。将Excel工作簿转换为SQL逻辑需要逐步分解每个计算步骤。ChatGPT o1指导我们通过这个过程，提供SQL查询的建议，优化逻辑，并创建模块化视图。
模块化SQL视图：凭借ChatGPT o1的帮助，来自Excel的每个关键计算都变成了一个专门的SQL视图。分析不再是一个庞大的、容易出错的文件，而是使用一系列更小、更专注的视图重新构建，使得测试和调整变得更加容易。
有效使用ChatGPT o1：我们逐步进行了这个转换过程 - 逐个粘贴Excel公式并要求ChatGPT o1将其转换为SQL。ChatGPT o1还生成了大部分代码，包括测试脚本，然后我们将其粘贴到Snowflake中检查输出并确认结果符合预期。这种协作方法确保了准确性，同时也让我们能够快速迭代。
视觉映射：ChatGPT 01创建了两个关键的视觉地图。第一个地图了中间的Excel列，澄清了它们如何对最终输出有贡献；第二个地图了新的Snowflake视图，展示了每个组件如何相互契合。这些地图对于验证和调试非常宝贵。
在Excel计算的第一张地图摘录:
1. Combined Risk
├── **Description**: Determines the overall risk category for inventory items by combining MLOR Risk and Expiry Risk, considering whether inventory is stranded.
├── **Formula**:
│ =IF(
│ [@[inventory stranded ctns]] > 0,
│ SWITCH(
│ [@[MLOR Risk]],
│ "EXPIRED", IF([@[Expiry Risk]] = "EXPIRED", "EXPIRED", "PAST MLOR"),
│ "VERY HIGH", "MLOR in < 3mths",
│ [@[Expiry Risk]]
│ ),
│ "No Risk"
│ )
├── **Dependencies**:
│
├── 1.1 Inventory Stranded Cartons (`inventory stranded ctns`)
│ ├── **Description**: Calculates the number of cartons that are stranded due to insufficient demand before expiry.
│ ├── **Formula**:
│ │ =ROUND(
│ │ IF(
│ │ [@[DNU inventory for same expiry date]] = 0,
│ │ 0,
│ │ [@[Sum of On Hand Balance]] / [@[DNU inventory for same expiry date]] * [@[DNU inventory stranded by date]]
│ │ ),
│ │ 1
│ │ )
│ ├── **Dependencies**:
│ │
│ ├── 1.1.1 DNU Inventory for Same Expiry Date (`DNU inventory for same expiry date`)
│ │ ├── **Description**: Sums the on-hand balance of items with the same item number and Max MLOR Date.
│ │ ├── **Formula**:
│ │ │ =SUMIFS(
│ │ │ [Sum of On Hand Balance],
│ │ │ [Item Number], [@[Item Number]],
│ │ │ [Max MLOR Date], [@[Max MLOR Date]]
│ │ │ )
│ │ ├── **Dependencies**:
│ │ │
│ │ ├── 1.1.1.1 Sum of On Hand Balance (raw data)
│ │ ├── 1.1.1.2 Item Number (raw data)
│ │ └── 1.1.1.3 Max MLOR Date
│ │ ├── **Description**: The date by which inventory should be sold to minimize risk, considering MLOR and expiry.
│ │ ├── **Formula**:
│ │ │ =IFERROR(
│ │ │ IF(
│ │ │ [@[Max MLOR]] = 0,
│ │ │ [@[Expire Date]],
│ │ │ MIN(
│ │ │ [@[Expire Date]],
│ │ │ [@[Expire Date]] - [@[Max MLOR]]
│ │ │ )
│ │ │ ),
│ │ │ 1
│ │ │ )
雪花视图的第二张地图摘录:
1. FGAR_1_vw_combined_risk
├── **Description**: Determines the overall risk category for inventory items by combining MLOR Risk and Expiry Risk.
├── **Dependencies**:
│
├── 1.1 FGAR_1111_vw_inventory
│ ├── **Description**: Merges inventory balance with any override actions.
│ ├── **Dependencies**:
│ │
│ ├── 1.1.1 vw_inventory_balance
│ │ ├── **Description**: Provides inventory balances from Snowflake tables.
│ │
│ ├── 1.1.2 vw_actions
│ ├── **Description**: Contains any manual override actions.
│
├── 1.2 FGAR_1222_vw_mlor_risk_category
│ ├── **Description**: Assigns MLOR risk category based on days to MLOR.
│ ├── **Dependencies**:
│ │
│ ├── 1.2.1 FGAR_1221_vw_days_to_mlor
│ │ ├── **Description**: Calculates the number of days to MLOR for each inventory item.
│ │ ├── **Dependencies**:
│ │ │
│ │ ├── 1.2.1.1 FGAR_1113_vw_max_mlor_date
│ │ │ ├── **Description**: Calculates the maximum MLOR date.
│ │ │ ├── **Dependencies**:
简化数据集成：大部分输入数据已经存在于Snowflake中。之前，多个报告工具提取这些数据，但严格的报告迫使需要额外步骤，如数据透视表和VLOOKUPs — 增加了复杂性但并没有价值。直接访问Snowflake表消除了这些步骤，简化了流程。
用户可编辑的表格:我们还设计了用户可编辑的表格，例如 tbl_age_category，使业务用户能够直接控制风险参数，提供灵活性并减少对 IT 的依赖。
使用ChatGPT o1，原本需要数周才能完成的工作在几天内完成。
挑战和学习重点
解开Excel逻辑：理解复杂的Excel公式是具有挑战性的。ChatGPT 01通过促使我们澄清细节，逐步分解依赖关系，确保我们准确捕捉到所有内容，帮助了我们。
平衡自动化和灵活性：我们需要一个既自动化又适应性强的解决方案。类似 tbl_age_category 这样的用户可编辑的表格让商务用户能够在没有 IT 支持的情况下更新关键参数，从而从一开始就确保了灵活性。
迭代方法：将项目分解为可控制的块 — 一次处理一个公式或SQL视图 — 被证明是有效的。这种方法使进展稳步，减少了被压倒的风险。
从Excel思维转变：从Excel转移到SQL需要放弃熟悉的习惯。一旦我们接受了SQL的优势，如可扩展性和清晰性，新方法的优势就变得明显起来。
成果和实现的商业价值
业务收益（持续中）
实时库存风险洞察：新的基于SQL的系统现在提供了实时的库存风险可见性，允许采取积极的行动而不是延迟的、被动的决策。这意味着改进的决策制定、减少损失和更好的库存优化。
消除了手动数据准备：现在直接从Snowflake中提取数据，不再需要像数据透视表和VLOOKUP这样的手动步骤。这节省了时间，减少了错误，从而产生更可靠和及时的见解。.
赋权用户：可由用户编辑的表格让业务用户直接调整风险参数，无需IT支持。这种灵活性确保快速适应不断变化的需求，减少对技术资源的依赖，让用户拥有更多控制权。
效率提升：过去每个月都需要花费几个小时的工作现在仅需几分钟完成，使团队能够专注于高价值任务，而不是重复的手工工作。
IT 收益（仅适用于此练习）
加速变革：将复杂的Excel逻辑转化为SQL代码通常是一项艰巨的任务，需要商业知识和技术技能 - 这是稀有的专业知识。即使拥有这样的人才，这种转换也可能需要几周的时间。使用ChatGPT o1，整个转换只需8小时。
消除瓶颈:将Excel转换为SQL或Python通常取决于少数具有双重专业知识的关键人员，从而形成瓶颈。通过使用ChatGPT o1提供的代码和测试脚本，我们减少了这种依赖，释放了专业资源。
质量和可维护性：生成的SQL代码整洁且有良好的文档，易于理解和维护。模块化方法确保解决方案具有可扩展性，并且可以轻松地将新的数据源或更改集成进来。
接下来和反思
接下来是什么
生产化解决方案：将SQL代码移至DBT中，以实现更好的版本控制和可伸缩性，确保解决方案可以投入生产。
拓展至其他领域：将类似的自动化应用于其他手动的Excel流程，以提高整个组织的效率。
可视化集成：将基于SQL的分析与诸如ThoughtSpot或Power BI等BI工具连接，用于交互式实时仪表板。
增强用户控制：进一步细化用户可编辑参数，减少IT参与度，增强对变化的响应能力。
反思
AI 协作: ChatGPT 01 在几小时内帮助完成了数周的工作 — 生成了代码、测试脚本和可视化 — 展示了人工智能驱动效率的力量。
简化复杂性：将问题分解为模块化部分使过程透明且可扩展，并与未来需求很好地对齐。
拥抱变化：从手动任务转向自动化使工作向更具战略性的方向转变，强化了质疑旧方法并采用高效解决方案的价值。
离别语
如果您对了解更多有关这个过程感兴趣，我很乐意分享完整的ChatGPT聊天记录或最终的SQL代码。如果您想深入了解技术细节，或者对如何将这种方法应用到您的业务挑战中感到好奇，请随时直接联系我。
