tritaptheduc/logistics-freight-audit

# AutoAudit-Logistics：自动化海运运费发票审计引擎与成本流失分析 ## 📌 项目概述 在全球供应链运营中，由于涉及多家承运商合同、波动的燃油附加费以及针对特定集装箱的额外费用（例如滞期费），海运运费发票的审计工作极为复杂。人工审计造成了严重的运营瓶颈，使公司在系统性计费错误和巨额成本流失面前不堪一击。 本项目交付了一个生产级的端到端**自动化运费发票审计引擎**。采用优化的数据仓库架构，该系统能够自动提取原始承运商发票，将其与合同规定的费率及物理提单（BOL）日志进行交叉核对，应用严格的运营业务规则，并立即分离出多收的款项以便迅速进行财务追回。 ### 🏗️ 技术栈 - **数据工程与模拟：** Python（`pandas`、`numpy`），用于模拟核心运营数据集并以编程方式注入真实的业务异常。 - **数据仓库层：** BigQuery Standard SQL（高级窗口函数、CTE 和条件路由逻辑）。 - **BI 与分析：** Power BI Desktop（星型架构数据建模与 DAX 聚合）。 - **设计系统：** 高级极简界面主题。 ## 📘 数据字典与数据类型 系统架构被构建为一个规范化的**星型架构**，旨在实现高性能的分析切片和无缝的关系映射。 ### 1. 承运商合同费率主数据 (`dim_contract_rates`) *管理不同承运商、运输航线和集装箱尺寸的合同谈判费率。* | 字段名称 | 数据类型 | 描述 | 示例 | | :--- | :--- | :--- | :--- | | `Contract_ID` | String (PK) | 谈判确定的承运商合同唯一标识符 | `CTR-1001` | | `Carrier` | String | 全球航运公司名称 | `Maersk` | | `POL_Origin` | String | 装货港（起始地位置代码） | `VNSGN (Cat Lai)` | | `POD_Destination` | String | 卸货港（目的地位置代码） | `USLAX (Los Angeles)` | | `Container_Type` | String | 设备尺寸配置 | `40HC` | | `Agreed_Base_Rate_USD`| Integer | 合同规定的基础海运费率 | `2450` | | `Agreed_Fuel_Surcharge_Pct`| Decimal | 谈判确定的燃油附加费乘数百分比 | `0.12` | | `Free_Demurrage_Days` | Integer | 港口免费集装箱存放的合同允许天数 | `7` | ### 2. 运营提单日志 (`fact_shipments_bol`) *记录由内部 ERP 记录的集装箱流转实际执行数据。* | 字段名称 | 数据类型 | 描述 | 示例 | | :--- | :--- | :--- | :--- | | `BOL_Number` | String (PK) | 唯一的提单标识追踪号 | `BOL202600001` | | `Contract_ID` | String (FK) | 指向具有约束力的合同费率的引用链接 | `CTR-1001` | | `Carrier` | String | 指派的运输承运商 | `Maersk` | | `POL_Origin` | String | 实际出发港 | `VNSGN (Cat Lai)` | | `POD_Destination` | String | 实际到达港 | `USLAX (Los Angeles)` | | `Container_Type` | String | 使用的集装箱类型 | `40HC` | | `Shipment_Date` | Date | 记录在案的物理出发日期 | `2026-03-15` | | `Actual_Demurrage_Days`| Integer | 集装箱在港口堆场实际停留的总天数 | `12` | ### 3. 原始承运商发票 (`raw_carrier_invoices`) *在审计前，存储从第三方承运商处接收的原始、非结构化账单文件。* | 字段名称 | 数据类型 | 描述 | 示例 | | :--- | :--- | :--- | :--- | | `Invoice_Number` | String (PK) | 外部账单发票标识符 | `INV-2026-5001` | | `BOL_Number` | String (FK) | 计费参考的提单号 | `BOL202600001` | | `Carrier` | String | 开票承运商实体 | `Maersk` | | `Invoice_Date` | Date | 发票正式生成的日期 | `2026-03-20` | | `Billed_Base_Rate_USD`| Decimal | 承运商收取的基础海运成本 | `2650.00` | | `Billed_Fuel_Surcharge_USD`| Decimal | 承运商收取的燃油附加费金额 | `294.00` | | `Billed_Demurrage_USD`| Decimal | 承运商收取的港口存放罚款金额 | `250.00` | | `Billed_Total_Amount_USD`| Decimal | 发票账单上列出的净总额 | `3194.00` | ## ⚙️ 核心审计规则与异常引擎逻辑 该引擎通过对每笔交易系统性处理 5 个硬编码的运营审计检查点，来评估发票的准确性： 1. **规则 1：重复计费检测 (`DUPLICATE_INVOICE`)** - *逻辑：* 评估是否针对同一个 `BOL_Number` 开具了多张发票。使用窗口分区（`ROW_NUMBER() OVER(PARTITION BY BOL_Number ORDER BY Invoice_Date)`），任何 $> 1$ 的发票出现次数都会被立即标记。预期有效总额将被重写为 `$0.00`，将整个第二笔账单隔离为 $100\%$ 可追回的多收款项。 2. **规则 2：基础费率多收验证 (`BASE_RATE_OVERCHARGE`)** - *逻辑：* 将 `Billed_Base_Rate_USD` 直接与合同维度表中的 `Agreed_Base_Rate_USD` 进行比较。任何发票费率超出合同费率的计费偏差都会触发标记。 3. **规则 3：燃油附加费准确性 (`FUEL_SURCHARGE_OVERCHARGE`)** - *逻辑：* 根据合同指标重新计算正确的燃油费： $$\text{Expected Fuel Surcharge} = \text{Agreed Base Rate} \times \text{Agreed Fuel Surcharge Pct}$$ - *差异：* 如果承运商计收的燃油费超过此精确计算值，则予以标记。 4. **规则 4：无效港口罚款索赔 (`INVALID_DEMURRAGE_CHARGE`)** - *逻辑：* 识别 `Actual_Demurrage_Days` $\le$ `Free_Demurrage_Days` 的情况，这意味着集装箱从未超过免费期限，但承运商却开出了 `Billed_Demurrage_USD` $> 0$ 的账单。 5. **规则 5：滞期费标准计算检查 (`DEMURRAGE_OVERCHARGE`)** - *逻辑：* 应用行业罚款费率（超出天数每天 $50.00 USD）： - *如果 `Actual Demurrage Days` < `Free Demurrage Days`，`Expected Demurrage` = `0`* - *如果 `Actual Demurrage Days` > `Free Demurrage Days`，`Expected Demurrage` = (`Actual Demurrage Days` - `Free Demurrage Days`) x `50.0`* $$\text{Expected Demurrage} = (\text{Actual Demurrage Days} - \text{Free Demurrage Days}) \times 50.0$$ - *差异：* 如果承运商计收的滞期费超过此计算值，则予以标记。 ## 🧠 精益六西格玛 DMAIC 案例研究 ### 🎯 1. 定义：系统性运费多收 财务团队的人工抽样覆盖率不到进境海运发票的 $5\%$，导致公司对系统性的开票错误完全一无所知。初步样本报告表明，承运商经常在基础海运费上多收费，错误计算燃油附加费百分比，并且不准确追踪集装箱在港口的停放天数。这种人工流程导致了估计每年 **$30,000 至 $50,000 的隐性成本流失**，严重损害了物流盈利能力。 ### 📊 2. 测量：核心流水线与差异基准 部署了稳健的数据集成流水线，将非结构化的对账单直接与内部 ERP 日志进行匹配。处理包含 1,020 张发票的基准批次后显示，**近 10% 的发票包含由承运商计费引擎注入的故意或系统性计费错误**。该系统在评估的运输周期内，量化了数千美元经过验证的超额计费基准财务差异。 ### 🔍 3. 分析：通过审计视图识别根本原因 通过实施自动化的 SQL 审计视图（`vw_freight_invoice_audit`），我们将异常归类到清晰的运营类别中，以定位根本原因： - **重复发票：** 捕获了 20 起承运商针对相同 BOL 重新签发带有修改后发票号（`*-DUP`）的重复发票的事件。 - **合同不匹配：** 发现承运商经常在特定集装箱航线（例如 40HC 配置）上将基础费率提高 $50–$200，或者暗中把燃油附加费多提高额外的 $5\%$。 - **滞期费计费漏洞：** 发现多张发票即使在港口存放完全处于合同批准的免费时间段内，也被收取了固定的 $150 罚款。 ### 🚀 4. 改进：自动化 SQL 引擎部署 核心程序化逻辑被迁移到优化的、自我纠正的 SQL 视图架构中。该引擎动态评估每个成本项目的有效性，并通过以下方式自动计算 `Recoverable_Overcharge_USD`： ``` $$\text{Recoverable Overcharge} = \text{Billed Total Amount} - \text{Expected Total Amount}$$ ``` 交互式 Power BI 仪表板直接连接到该干净的分析层，使物流审计团队能够立即隔离高风险承运商、导出经验证的审计追踪，并在自掏腰包进行结算之前暂扣付款。 ### 🎛️ 5. 控制：结算前财务治理 - **自动化索赔生成：** 审计专家可以隔离被标记存在差异的特定 `Invoice_Number` 行，并为承运商客户经理自动生成争议表单。 - **持续审计检查：** 引擎会自动根据历史数据库评估新上传的数据，永久防止对重复账单进行付款。 - **零财务流失：** 通过从付款后提出索赔流程转变为自动化的结算前审计工作流，企业避免了针对无效发票的现金流出，将计费流失降至绝对的零。 ## 📸 仪表板界面预览  *图 1：核心财务概述与承运商审计差异矩阵* 📄 许可证 本项目是采用 MIT 许可证授权的开源软件。您可以完全自由地定制这些 DAX 验证模型，以用于实际的企业供应链物流应用。

标签：BigQuery, Power BI, Python, 商业智能, 多线程, 数据工程, 无后门, 物流数据分析, 财务审计, 逆向工具