Back to Details

datapack-builder

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Financial Data Pack Builder

金融数据包构建器

Build professional, standardized financial data packs for private equity, investment banking, and asset management. Transform financial data from CIMs, offering memorandums, SEC filings, web search, or MCP server access into polished Excel workbooks ready for investment committee review.
Important: Use the xlsx skill for all Excel file creation and manipulation throughout this workflow.
为私募股权、投资银行和资产管理行业构建专业、标准化的金融数据包。将来自CIM、招股说明书、SEC filings、网页搜索或MCP服务器的金融数据转换为打磨完善、可直接提交投资委员会审核的Excel工作簿。
重要提示： 整个工作流中所有Excel文件的创建和操作都需要使用xlsx skill。

CRITICAL SUCCESS FACTORS

关键成功要素

Every data pack must achieve these standards. Failure on any point makes the deliverable unusable.
每个数据包都必须达到以下标准，任何一项不达标都会导致交付物无法使用。

1. Data Accuracy (Zero Tolerance for Errors)

1. 数据准确性（零错误容忍）

  • Trace every number to source document with page reference
  • Use formula-based calculations exclusively (no hardcoded values)
  • Cross-check subtotals and totals for internal consistency
  • Verify balance sheet balances: Assets = Liabilities + Equity
  • Confirm cash flow ties to balance sheet changes
  • 每个数值都要追溯到源文档并标注页码
  • 所有计算完全基于公式（禁止硬编码数值）
  • 交叉校验小计和总计的内部一致性
  • 验证资产负债表平衡：资产 = 负债 + 所有者权益
  • 确认现金流量表与资产负债表变动项挂钩

2. ESSENTIAL RULES

2. 核心规则

RULE 1: Financial data (measuring money) → Currency format with $ Triggers: Revenue, Sales, Income, EBITDA, Profit, Loss, Cost, Expense, Cash, Debt, Assets, Liabilities, Equity, Capex Format: $#,##0.0 for millions, $#,##0 for thousands Negatives: $(123.0) NOT -$123
RULE 2: Operational data (counting things) → Number format, NO $ Triggers: Units, Stores, Locations, Employees, Customers, Square Feet, Properties, Headcount Format: #,##0 with commas Negatives: (123) consistent with rest of table
RULE 3: Percentages (rates and ratios) → Percentage format Triggers: Margin, Growth, Rate, Percentage, Yield, Return, Utilization, Occupancy Format: 0.0% for one decimal place Display: 15.0% NOT 0.15
RULE 4: Years → Text format to prevent comma insertion Format: Text or custom to prevent 2,024 Display: 2020, 2021, 2022, 2023A, 2024E
RULE 5: When context is mixed, each metric gets its own appropriate format Example:
Segment Analysis, 2022, 2023, 2024
Retail Revenue, $50.0, $55.0, $60.0
  Stores, 100, 110, 120
  Revenue per Store, $0.5, $0.5, $0.5
Revenue and per-store metrics use $, Store count uses number format.
RULE 6: Use formulas for all calculations → Never hardcode calculated values All subtotals, totals, ratios, and derived metrics must be formula-based, not hardcoded values. This ensures accuracy and allows for dynamic updates.
规则1：金融数据（计量金额）→ 带$的货币格式 触发字段：营收、销售额、收入、EBITDA、利润、亏损、成本、费用、现金、债务、资产、负债、所有者权益、资本支出 格式：百万级使用$#,##0.0，千级使用$#,##0 负数格式：$(123.0) 而非 -$123
规则2：运营数据（计量数量）→ 数字格式，不带$ 触发字段：单位数、门店数、位置数、员工数、客户数、平方英尺、物业数、员工总数 格式：带逗号的#,##0格式 负数格式：(123)，与表格其余部分保持一致
规则3：百分比（比率和比例）→ 百分比格式 触发字段：利润率、增长率、比率、百分比、收益率、回报率、利用率、入住率 格式：保留1位小数的0.0%格式 展示：15.0% 而非 0.15
规则4：年份 → 文本格式，避免自动添加逗号 格式：文本或自定义格式，避免显示为2,024 展示：2020, 2021, 2022, 2023A, 2024E
规则5：混合上下文场景下，每个指标使用对应合适的格式 示例：
Segment Analysis, 2022, 2023, 2024
Retail Revenue, $50.0, $55.0, $60.0
  Stores, 100, 110, 120
  Revenue per Store, $0.5, $0.5, $0.5
营收和单店指标使用$格式，门店数量使用数字格式。
规则6：所有计算使用公式 → 绝对禁止硬编码计算结果 所有小计、总计、比率和衍生指标必须基于公式生成，不得使用硬编码数值，确保准确性并支持动态更新。

3. Professional Presentation Standards

3. 专业展示标准

Formatting Standards:
Color Scheme - Two Layers:
Layer 1: Font Colors (MANDATORY from xlsx skill)
  • Blue text (RGB: 0,0,255): ALL hardcoded inputs (historical data, assumptions), NOT normal text
  • Black text (RGB: 0,0,0): ALL formulas and calculations
  • Green text (RGB: 0,128,0): Links to other sheets
Layer 2: Fill Colors (Optional for enhanced presentation)
  • Fill colors are optional and should only be applied if requested by the user or if enhancing presentation
  • If the user requests colors or professional formatting, use this standard scheme:
    • Section headers: Dark blue (RGB: 68,114,196) background with white text
    • Sub-headers/column headers: Light blue (RGB: 217,225,242) background with black text
    • Input cells: Light green/cream (RGB: 226,239,218) background with blue text
    • Calculated cells: White background with black text
  • Users can override with custom brand colors if specified
How the layers work together (if fill colors are used):
  • Input cell: Blue text + light green fill = "User-entered data"
  • Formula cell: Black text + white background = "Calculated value"
  • Sheet link: Green text + white background = "Reference from another tab"
Font color tells you WHAT it is. Fill color tells you WHERE it is (if used).
IMPORTANT: Font colors from xlsx skill are mandatory. Fill colors are optional - default is white/no fill unless the user requests enhanced formatting or colors.
Always apply:
  • Bold headers, left-aligned
  • Numbers right-aligned
  • 2-space indentation for sub-items
  • Single underline above subtotals
  • Double underline below final totals
  • Freeze panes on row/column headers
  • Minimal borders (only where structurally needed)
  • Consistent font (typically Calibri or Arial 11pt)
Never include:
  • Borders around every cell
  • Multiple fonts or font sizes
  • Charts unless specifically requested
  • Excessive formatting or decoration
格式标准：
配色方案 - 两层规则：
第一层：字体颜色（xlsx skill强制要求）
  • 蓝色字体（RGB: 0,0,255）：所有硬编码输入项（历史数据、假设条件），不适用于普通文本
  • 黑色字体（RGB: 0,0,0）：所有公式和计算结果
  • 绿色字体（RGB: 0,128,0）：指向其他工作表的链接
第二层：填充色（可选，用于提升展示效果）
  • 填充色为可选项，仅在用户要求或需要提升展示效果时添加
  • 如果用户要求配色或专业格式，使用以下标准方案：
    • 板块标题：深蓝色（RGB: 68,114,196）背景搭配白色字体
    • 子标题/列标题：浅蓝色（RGB: 217,225,242）背景搭配黑色字体
    • 输入单元格：浅绿/米白色（RGB: 226,239,218）背景搭配蓝色字体
    • 计算单元格：白色背景搭配黑色字体
  • 用户指定自定义品牌色时可覆盖上述规则
两层规则的组合逻辑（使用填充色时）：
  • 输入单元格：蓝色字体 + 浅绿色填充 = "用户输入数据"
  • 公式单元格：黑色字体 + 白色背景 = "计算生成数值"
  • 工作表链接：绿色字体 + 白色背景 = "来自其他标签页的引用"
字体颜色说明内容类型，填充色（使用时）说明内容位置。
重要提示： xlsx skill规定的字体颜色为强制要求，填充色为可选项，默认使用白色/无填充，除非用户要求增强格式或配色。
通用要求：
  • 标题加粗、左对齐
  • 数值右对齐
  • 子项使用2空格缩进
  • 小计上方添加单下划线
  • 最终总计下方添加双下划线
  • 行/列标题处冻结窗格
  • 最小化边框（仅结构必要时使用）
  • 字体统一（通常为11号Calibri或Arial）
禁止项：
  • 每个单元格都添加边框
  • 使用多种字体或字体大小
  • 除非明确要求，否则不添加图表
  • 过度格式或装饰

Structural Consistency

结构一致性

Use the standard 8-tab structure unless explicitly instructed otherwise:
  1. Executive Summary
  2. Historical Financials (Income Statement)
  3. Balance Sheet
  4. Cash Flow Statement
  5. Operating Metrics
  6. Property/Segment Performance (if applicable)
  7. Market Analysis
  8. Investment Highlights
除非另有明确指示，否则使用标准8标签页结构：
  1. 执行摘要
  2. 历史财务数据（利润表）
  3. 资产负债表
  4. 现金流量表
  5. 运营指标
  6. 物业/板块表现（如适用）
  7. 市场分析
  8. 投资亮点

Tab 1: Executive Summary

标签页1：执行摘要

Purpose: One-page overview for busy executives
Contents:
  • Company overview (2-3 sentences on business model)
  • Key investment highlights (3-5 bullet points)
  • Financial snapshot table (Revenue, EBITDA, Growth for last 3 years + projections)
  • Transaction overview if applicable
  • Key metrics prominently displayed
Format: Clean, bold headers, minimal decoration, critical numbers emphasized
用途：为忙碌的高管提供单页概览
内容：
  • 公司概览（2-3句介绍商业模式）
  • 核心投资亮点（3-5个要点）
  • 财务快照表（过去3年+预测的营收、EBITDA、增长率）
  • 交易概览（如适用）
  • 核心指标突出展示
格式：简洁、标题加粗、装饰最少，关键数字突出强调

Tab 2: Historical Financials (Income Statement)

标签页2：历史财务数据（利润表）

Purpose: Complete profit and loss history
Contents:
  • Revenue breakdown by segment/product line
  • Cost of goods sold / Cost of revenue
  • Gross profit and gross margin %
  • Operating expenses detailed (S&M, R&D, G&A)
  • EBITDA and Adjusted EBITDA
  • Below-the-line items (D&A, interest, taxes)
  • Net income
Format:
  • Years as columns (text format: 2020, 2021, 2022)
  • $ millions or $ thousands (specify units clearly at top)
  • Accounting format for all financial data
  • Single underline above subtotals, double underline below net income
  • Right-align all numbers
用途：完整的盈亏历史
内容：
  • 按板块/产品线拆分的营收
  • 销售成本/营收成本
  • 毛利润和毛利率%
  • 详细运营费用（销售营销、研发、管理费用）
  • EBITDA和调整后EBITDA
  • 线下项目（折旧摊销、利息、税费）
  • 净利润
格式：
  • 年份作为列（文本格式：2020, 2021, 2022）
  • 单位为百万美元或千美元，顶部明确标注
  • 所有金融数据使用会计格式
  • 小计上方加单下划线，净利润下方加双下划线
  • 所有数值右对齐

Tab 3: Balance Sheet

标签页3：资产负债表

Purpose: Financial position at period end
Contents:
  • Current assets (cash, AR, inventory, prepaid, other)
  • Long-term assets (PP&E, intangibles, goodwill, other)
  • Current liabilities (AP, accrued expenses, current portion of debt, other)
  • Long-term liabilities (long-term debt, deferred taxes, other)
  • Shareholders' equity (common stock, retained earnings, other)
Format:
  • Verify formula: Assets = Liabilities + Equity
  • Consistent date labeling
  • Include working capital calculation
  • Single underline above major subtotals, double underline for final totals
用途：期末财务状况
内容：
  • 流动资产（现金、应收账款、存货、预付款、其他）
  • 长期资产（不动产厂房设备、无形资产、商誉、其他）
  • 流动负债（应付账款、预提费用、短期债务、其他）
  • 长期负债（长期债务、递延税费、其他）
  • 股东权益（普通股、留存收益、其他）
格式：
  • 验证公式：资产 = 负债 + 所有者权益
  • 日期标注统一
  • 包含营运资金计算
  • 主要小计上方加单下划线，最终总计下方加双下划线

Tab 4: Cash Flow Statement

标签页4：现金流量表

Purpose: Cash generation and use analysis
Contents:
  • Operating cash flow (indirect method preferred)
  • Investing cash flow (capex, acquisitions, asset sales)
  • Financing cash flow (debt issuance/repayment, equity, dividends)
  • Net change in cash
  • Beginning and ending cash balances
Format:
  • Link to income statement and balance sheet where possible
  • Show reconciliation of net income to operating cash flow
  • Clear labeling of cash uses (outflows) vs sources (inflows)
用途：现金生成和使用分析
内容：
  • 经营现金流（优先使用间接法）
  • 投资现金流（资本支出、收购、资产出售）
  • 融资现金流（债务发行/偿还、股权、分红）
  • 现金净变动额
  • 期初和期末现金余额
格式：
  • 尽可能关联利润表和资产负债表数据
  • 展示净利润到经营现金流的调节过程
  • 明确标注现金使用（流出）和现金来源（流入）

Tab 5: Operating Metrics

标签页5：运营指标

Purpose: Non-financial KPIs and operational data
Contents (industry-dependent):
  • Unit volumes, customer counts, locations
  • Productivity metrics (revenue per employee, per store, per unit)
  • Capacity utilization
  • Market share
  • Customer retention/churn rates
  • Industry-specific KPIs
CRITICAL FORMAT NOTE: NO dollar signs on operational metrics. These are quantities, not currency.
Format:
  • Clear units specified (customers, employees, stores, square feet, etc.)
  • Whole numbers with commas: 1,250 NOT $1,250
  • Percentages for rates: 95.0%
  • Right-align numbers
用途：非财务KPI和运营数据
内容（依行业而定）：
  • 单位销量、客户数、点位数量
  • 生产力指标（人均营收、单店营收、单位营收）
  • 产能利用率
  • 市场份额
  • 客户留存/流失率
  • 行业特定KPI
关键格式提示： 运营指标不得添加美元符号，这些是数量指标，不是货币。
格式：
  • 明确标注单位（客户、员工、门店、平方英尺等）
  • 整数带逗号：1,250 而非 $1,250
  • 比率使用百分比：95.0%
  • 数值右对齐

Tab 6: Property/Segment Performance (if applicable)

标签页6：物业/板块表现（如适用）

Purpose: Detailed breakdown by business unit, property, or segment
Contents:
  • Revenue and profitability by segment
  • Key metrics by location/product
  • Segment-specific KPIs
  • Comparative performance analysis
Format: Consistent with financial tabs for revenue/EBITDA, number format for operational metrics
用途：按业务单元、物业或板块的详细拆分
内容：
  • 按板块拆分的营收和盈利情况
  • 按点位/产品的核心指标
  • 板块特定KPI
  • 对比表现分析
格式：营收/EBITDA格式与财务标签页保持一致，运营指标使用数字格式

Tab 7: Market Analysis

标签页7：市场分析

Purpose: Industry context and competitive positioning
Contents:
  • Market size and growth trends
  • Competitive landscape overview
  • Market share analysis
  • Industry benchmarks and peer comparisons
  • Regulatory environment if relevant
Format: Mix of narrative text and tables, cite sources for market data
用途：行业背景和竞争定位
内容：
  • 市场规模和增长趋势
  • 竞争格局概览
  • 市场份额分析
  • 行业基准和同行对比
  • 监管环境（如相关）
格式：混合叙述文本和表格，市场数据标注来源

Tab 8: Investment Highlights

标签页8：投资亮点

Purpose: Narrative summary of key investment thesis points
Contents:
  • Detailed writeup of competitive strengths
  • Growth opportunities and strategic initiatives
  • Risk factors and mitigation strategies
  • Management assessment and track record
  • Investment thesis summary
Format: Clear headers, bullet points, concise paragraphs
用途：核心投资逻辑要点的叙述性总结
内容：
  • 竞争优势的详细说明
  • 增长机会和战略举措
  • 风险因素和缓释策略
  • 管理团队评估和过往业绩
  • 投资逻辑总结
格式：清晰标题、要点列表、简洁段落

STEP-BY-STEP WORKFLOW

分步工作流

Phase 1: Document Processing and Data Extraction

阶段1：文档处理和数据提取

Step 1.1: Analyze source data
  • Access source materials: uploaded documents, web search for public filings, or MCP server data
  • Review data structure and identify key sections
  • Locate financial statements (typically 3-5 years historical)
  • Identify management projections if included
  • Note fiscal year end date
  • Flag any data quality issues immediately
Step 1.2: Extract financial statements
  • Locate historical income statement data
  • Extract balance sheet snapshots (year-end or quarter-end)
  • Find cash flow statement
  • Extract management projections if available
  • Note all page references for traceability
Step 1.3: Extract operating metrics
  • Identify non-financial KPIs relevant to industry
  • Capture unit economics data
  • Extract customer/location/capacity data
  • Document growth metrics and trends
Step 1.4: Extract market and industry data
  • Competitive positioning information
  • Market size and growth rates
  • Industry benchmark data
  • Peer comparison information
Step 1.5: Note key context
  • Transaction structure and rationale
  • Management team background
  • Investment highlights from source materials
  • Risk factors and considerations
  • Any data gaps or inconsistencies
步骤1.1：分析源数据
  • 访问源材料：上传的文档、公开 filings的网页搜索结果、或MCP服务器数据
  • 审查数据结构，识别核心板块
  • 定位财务报表（通常为3-5年历史数据）
  • 识别管理层预测（如有）
  • 记录财年结束日期
  • 立即标记任何数据质量问题
步骤1.2：提取财务报表
  • 定位历史利润表数据
  • 提取资产负债表快照（年末或季末）
  • 查找现金流量表
  • 提取管理层预测（如有）
  • 记录所有页码信息用于追溯
步骤1.3：提取运营指标
  • 识别行业相关的非财务KPI
  • 采集单位经济数据
  • 提取客户/点位/产能数据
  • 记录增长指标和趋势
步骤1.4：提取市场和行业数据
  • 竞争定位信息
  • 市场规模和增长率
  • 行业基准数据
  • 同行对比信息
步骤1.5：记录核心背景信息
  • 交易结构和逻辑
  • 管理团队背景
  • 源材料中的投资亮点
  • 风险因素和考量
  • 任何数据缺口或不一致之处

Phase 2: Data Normalization and Standardization

阶段2：数据归一化和标准化

Step 2.1: Normalize accounting presentation
  • Ensure consistent line item names across all years
  • Standardize revenue recognition treatment
  • Identify and document one-time charges
  • Create "Adjusted EBITDA" reconciliation if needed
  • Note any accounting policy changes
Step 2.2: Apply format detection logic For each data point, determine format based on full context:
  • Read tab name, table title, column header, and row label
  • Apply essential rules (see above)
  • When uncertain, examine original source document
  • Default to cleaner formatting (less is more)
Step 2.3: Identify normalization adjustments Common adjustments to document:
  • Restructuring charges (add back if truly non-recurring)
  • Stock-based compensation (add back per industry standard)
  • Acquisition-related costs (add back, specify amounts)
  • Legal settlements or litigation costs (evaluate recurrence risk)
  • Asset sales or impairments (exclude from operating results)
  • Related party adjustments (normalize to market rates) Note: Source citation format varies by data source (page numbers for documents, URLs for web sources, server references for MCP data)
Step 2.4: Create adjustment schedule For every normalization:
  • Document what was adjusted and why
  • Cite source (document page number, URL, or data source reference)
  • Quantify dollar impact by year
  • Assess recurrence risk
  • Show calculation from reported to adjusted figures
Step 2.5: Verify data integrity
  • Confirm subtotals sum correctly using formulas
  • Verify balance sheet balances
  • Check cash flow ties to balance sheet changes
  • Cross-check numbers across tabs for consistency
  • Flag any discrepancies for investigation
步骤2.1：归一化会计列报
  • 确保所有年份的行项目名称统一
  • 标准化收入确认处理方式
  • 识别并记录一次性费用
  • 按需创建“调整后EBITDA”调节表
  • 记录任何会计政策变更
步骤2.2：应用格式识别逻辑 针对每个数据点，结合完整上下文确定格式：
  • 读取标签页名称、表格标题、列标题和行标签
  • 应用上述核心规则
  • 不确定时查阅原始源文档
  • 默认采用更简洁的格式（少即是多）
步骤2.3：识别归一化调整项 常见需要记录的调整项：
  • 重组费用（确为非经常性则加回）
  • 股权激励（按行业标准加回）
  • 收购相关成本（加回，明确金额）
  • 法律和解或诉讼费用（评估 recurrence风险）
  • 资产出售或减值（从经营业绩中剔除）
  • 关联方调整（归一化为市场费率） 注：来源引用格式依数据源而异（文档标注页码、网页来源标注URL、MCP数据标注服务器引用）
步骤2.4：创建调整明细表 每一项归一化调整都需要：
  • 记录调整内容和原因
  • 引用来源（文档页码、URL或数据源引用）
  • 量化每年的金额影响
  • 评估重复发生风险
  • 展示从披露值到调整后值的计算过程
步骤2.5：验证数据完整性
  • 使用公式确认小计求和正确
  • 验证资产负债表平衡
  • 检查现金流量表与资产负债表变动项挂钩
  • 跨标签页交叉校验数值一致性
  • 标记任何差异待核查

Phase 3: Build Excel Workbook

阶段3：构建Excel工作簿

CRITICAL: Use xlsx skill for all Excel file manipulation. Read xlsx skill documentation before proceeding.
Step 3.1: Create standardized tab structure Create workbook with tabs:
  • Executive Summary
  • Historical Financials
  • Balance Sheet
  • Cash Flow
  • Operating Metrics
  • Property Performance (if applicable)
  • Market Analysis
  • Investment Highlights
Step 3.2: Build each tab with proper formatting Apply formatting rules systematically:
  • Headers: Bold, left-aligned, 11pt font
  • Financial data: Currency format $#,##0.0 for millions
  • Operational data: Number format #,##0 (no $)
  • Percentages: 0.0% format
  • Years: Text format to prevent comma insertion
  • Negatives: Use accounting format with parentheses
  • Underlines: Single above subtotals, double below totals
Step 3.3: Insert formulas for calculations
  • All subtotals and totals must be formula-based
  • Link balance sheet to income statement where appropriate
  • Link cash flow to both income statement and balance sheet
  • Create cross-tab references for validation
  • Avoid hardcoding any calculated values
<correct_patterns>
关键提示：所有Excel文件操作都使用xlsx skill，操作前请阅读xlsx skill文档。
步骤3.1：创建标准化标签页结构 创建包含以下标签页的工作簿：
  • 执行摘要
  • 历史财务数据
  • 资产负债表
  • 现金流量表
  • 运营指标
  • 物业表现（如适用）
  • 市场分析
  • 投资亮点
步骤3.2：按规范格式构建每个标签页 系统应用格式规则：
  • 标题：加粗、左对齐、11号字体
  • 金融数据：百万级使用货币格式 $#,##0.0
  • 运营数据：数字格式 #,##0（不带$）
  • 百分比：0.0%格式
  • 年份：文本格式避免自动添加逗号
  • 负数：使用带括号的会计格式
  • 下划线：小计上方加单下划线，总计下方加双下划线
步骤3.3：插入计算公式
  • 所有小计和总计必须基于公式
  • 资产负债表按需关联利润表数据
  • 现金流量表关联利润表和资产负债表数据
  • 创建跨标签页引用用于校验
  • 避免硬编码任何计算值
<正确模式>

Row Reference Tracking - Copy This Pattern

行引用跟踪 - 复制此模式

Store row numbers when writing data, then reference them in formulas:
python
undefined
写入数据时记录行号，后续在公式中引用：
python
undefined

✅ CORRECT - Track row numbers as you write

✅ 正确 - 写入数据时跟踪行号

revenue_row = row write_data_row(ws, row, "Revenue", revenue_values) row += 1
ebitda_row = row write_data_row(ws, row, "EBITDA", ebitda_values) row += 1
revenue_row = row write_data_row(ws, row, "Revenue", revenue_values) row += 1
ebitda_row = row write_data_row(ws, row, "EBITDA", ebitda_values) row += 1

Use stored row numbers in formulas

在公式中使用存储的行号

margin_row = row for col in year_columns: cell = ws.cell(row=margin_row, column=col) cell.value = f"={get_column_letter(col)}{ebitda_row}/{get_column_letter(col)}{revenue_row}"

**For complex models, use a dictionary:**

```python
row_refs = {
    'revenue': 5,
    'cogs': 6,
    'gross_profit': 7,
    'ebitda': 12
}
margin_row = row for col in year_columns: cell = ws.cell(row=margin_row, column=col) cell.value = f"={get_column_letter(col)}{ebitda_row}/{get_column_letter(col)}{revenue_row}"

**复杂模型使用字典存储：**

```python
row_refs = {
    'revenue': 5,
    'cogs': 6,
    'gross_profit': 7,
    'ebitda': 12
}

Later in formulas

后续公式中使用

margin_formula = f"=B{row_refs['ebitda']}/B{row_refs['revenue']}"

</correct_patterns>

<common_mistakes>
margin_formula = f"=B{row_refs['ebitda']}/B{row_refs['revenue']}"

</正确模式>

<常见错误>

WRONG: Hardcoded Row Offsets

错误：硬编码行偏移量

Don't use relative offsets - they break when table structure changes:
python
undefined
不要使用相对偏移量，表格结构变更时会失效：
python
undefined

❌ WRONG - Fragile offset-based references

❌ 错误 - 脆弱的基于偏移量的引用

formula = f"=B{row-15}/B{row-19}" # What is row-15? What is row-19?
formula = f"=B{row-15}/B{row-19}" # row-15是什么？row-19是什么？

❌ WRONG - Magic numbers

❌ 错误 - 魔数

formula = f"=B{current_row-10}*C{current_row-20}"

**Why this fails:**
- Breaks silently when you add/remove rows
- Impossible to verify correctness by reading code
- Creates debugging nightmares in the delivered Excel file

</common_mistakes>

**Step 3.4: Apply professional presentation**
- Freeze top row and first column on each data tab
- Set appropriate column widths (typically 12-15 characters)
- Right-align all numeric data
- Left-align all text and headers
- Add single/double underlines per accounting standards
- Ensure clean, minimal appearance
formula = f"=B{current_row-10}*C{current_row-20}"

**错误原因：**
- 添加/删除行时会静默失效
- 无法通过阅读代码验证正确性
- 交付的Excel文件调试难度极高

</常见错误>

**步骤3.4：应用专业展示规范**
- 每个数据标签页冻结首行和首列
- 设置合适的列宽（通常12-15个字符）
- 所有数值数据右对齐
- 所有文本和标题左对齐
- 按会计标准添加单/双下划线
- 确保整体外观简洁专业

Phase 4: Scenario Building (if projections included)

阶段4：场景构建（包含预测时适用）

Management Case: Present company's projections as provided in source materials:
  • Extract all management assumptions
  • Document growth rates, margin expansion, capital requirements
  • Note key drivers and sensitivities
  • Flag any "hockey stick" inflections that require skepticism
  • Present as "Management Case" with clear labeling
Base Case (Risk-Adjusted): Apply conservative adjustments to management projections based on company-specific risk factors:
  • Apply revenue growth haircut reflecting execution risk and historical forecast accuracy
  • Moderate margin expansion assumptions based on industry benchmarks and operating leverage
  • Increase capex assumptions if growth-dependent
  • Add working capital requirements if understated
  • Delay synergy realization if applicable, based on integration complexity
  • Document all adjustments with rationale and supporting analysis
Downside Case (optional but recommended for LBO analysis): Stress test scenario based on industry cyclicality and company vulnerabilities:
  • Model revenue decline reflecting recession risk or competitive pressure
  • Assume margin compression under stress (volume deleverage, pricing pressure)
  • Test covenant compliance and liquidity
  • Assess downside protection
  • Document key risks being stress-tested
Documentation requirements for scenarios: Create assumptions schedule showing:
  • Key assumptions by scenario (revenue growth, margins, capex %)
  • Rationale for each adjustment
  • Sensitivity analysis on key variables
  • Historical forecast accuracy if available
  • Comparison to industry benchmarks
管理层案例： 按源材料提供的内容展示公司预测：
  • 提取所有管理层假设
  • 记录增长率、利润率提升、资本需求
  • 标注核心驱动因素和敏感性
  • 标记需要谨慎对待的“曲棍球棒”式拐点
  • 明确标注为“管理层案例”
基准案例（风险调整后）： 基于公司特定风险因素对管理层预测进行保守调整：
  • 考虑执行风险和历史预测准确率，下调营收增长率
  • 基于行业基准和经营杠杆，下调利润率提升假设
  • 若增长依赖资本支出则上调资本支出假设
  • 若营运资金需求被低估则补充相关内容
  • 如适用，根据整合复杂度延后协同效应实现时间
  • 记录所有调整的逻辑和支撑分析
下行案例（LBO分析推荐使用，可选）： 基于行业周期性和公司脆弱性进行压力测试场景：
  • 模拟经济衰退或竞争压力导致的营收下滑
  • 假设压力下的利润率压缩（规模效应失效、定价压力）
  • 测试 covenant合规性和流动性
  • 评估下行保护能力
  • 记录压力测试覆盖的核心风险
场景文档要求： 创建假设明细表展示：
  • 各场景的核心假设（营收增长率、利润率、资本支出占比）
  • 每项调整的逻辑
  • 核心变量的敏感性分析
  • 历史预测准确率（如有）
  • 与行业基准的对比

Phase 5: Quality Control and Validation

阶段5：质量控制和验证

Step 5.1: Data accuracy checks Validate:
  • Every number traces to source (check spot samples, cite documents/URLs/servers)
  • All calculations are formula-based (no hardcoded values)
  • Subtotals and totals are mathematically correct
  • Years display without commas (2024 NOT 2,024)
  • No formula errors: #REF!, #VALUE!, #DIV/0!, #N/A
Step 5.2: Format consistency checks Verify:
  • Financial data has $ signs in format
  • Operational data has NO $ signs
  • Percentages display as % (15.0% not 0.15)
  • Negative numbers use parentheses for financial data
  • Headers are bold and left-aligned
  • Numbers are right-aligned
  • Years are text format
Step 5.3: Structure and completeness checks Confirm:
  • All required tabs present and properly sequenced
  • Executive summary is concise (fits on one page)
  • All key metrics captured comprehensively
  • Logical flow from summary to detail
  • Appropriate level of granularity in each tab
  • No missing data or incomplete sections
Step 5.4: Professional presentation checks Review:
  • Minimal borders (only for structure)
  • Consistent indentation (2 spaces for sub-items)
  • Proper accounting underlines (single and double)
  • Clean, professional appearance throughout
  • Appropriate column widths (not too narrow or wide)
Step 5.5: Documentation and assumptions checks Ensure:
  • All normalization adjustments documented with rationale
  • Source citations included (document page numbers, URLs, or data source references)
  • Assumptions clearly stated and reasonable
  • Executive summary accurate and impactful
  • Filename includes company name and date
步骤5.1：数据准确性检查 验证：
  • 每个数值都可追溯到来源（抽样检查，标注文档/URL/服务器来源）
  • 所有计算基于公式（无硬编码值）
  • 小计和总计数学正确
  • 年份展示无逗号（2024 而非 2,024）
  • 无公式错误：#REF!, #VALUE!, #DIV/0!, #N/A
步骤5.2：格式一致性检查 验证：
  • 金融数据格式带$符号
  • 运营数据不带$符号
  • 百分比展示为%格式（15.0% 而非 0.15）
  • 金融数据负数使用括号格式
  • 标题加粗且左对齐
  • 数值右对齐
  • 年份为文本格式
步骤5.3：结构和完整性检查 确认：
  • 所有必填标签页存在且顺序正确
  • 执行摘要简洁（单页可容纳）
  • 所有核心指标全面覆盖
  • 从摘要到明细的逻辑流畅
  • 每个标签页的粒度合适
  • 无缺失数据或不完整板块
步骤5.4：专业展示检查 审查：
  • 边框最少（仅结构必要时使用）
  • 缩进统一（子项2空格）
  • 会计下划线使用正确（单/双下划线）
  • 整体外观简洁专业
  • 列宽合适（不过窄或过宽）
步骤5.5：文档和假设检查 确保：
  • 所有归一化调整都记录了逻辑
  • 包含来源引用（文档页码、URL或数据源引用）
  • 假设清晰明确且合理
  • 执行摘要准确且有冲击力
  • 文件名包含公司名称和日期

Phase 6: Final Delivery

阶段6：最终交付

Step 6.1: Create executive summary Write concise, impactful summary including:
  • Company overview: business model, products/services, geography (2-3 sentences)
  • Key financial metrics: Revenue, EBITDA, Growth rates (table format)
  • Investment highlights: 3-5 key strengths or opportunities
  • Notable risks or considerations (briefly)
  • Transaction context if applicable
Step 6.2: Final file preparation
  • Save workbook with proper naming: CompanyName_DataPack_YYYY-MM-DD.xlsx
步骤6.1：创建执行摘要 撰写简洁有冲击力的摘要，包含：
  • 公司概览：商业模式、产品/服务、地域分布（2-3句）
  • 核心财务指标：营收、EBITDA、增长率（表格格式）
  • 投资亮点：3-5个核心优势或机会
  • 值得注意的风险或考量（简要）
  • 交易背景（如适用）
步骤6.2：最终文件准备
  • 按规范命名保存工作簿：公司名称_DataPack_YYYY-MM-DD.xlsx

NORMALIZATION PATTERNS

归一化模式

Common Adjustments to EBITDA

EBITDA常见调整项

1. Restructuring charges
  • Add back if truly non-recurring (facility closure, one-time severance)
  • Do NOT add back if company restructures every year
  • Document specific nature and rationale for non-recurrence
  • Example: "2023 restructuring: $3.0M facility closure, documented in source materials, one-time event"
2. Stock-based compensation
  • Industry standard: add back for private equity analysis
  • Treat as non-cash operating expense
  • Be consistent across all periods
  • Note if unusually high or includes one-time grants
3. Acquisition-related costs
  • Add back transaction fees, integration costs
  • Document specific amounts by type
  • Do not add back ongoing integration investments
  • Cite source for each adjustment
4. Legal settlements and litigation
  • Add back if truly isolated incident
  • Assess recurrence risk (one settlement vs pattern of litigation)
  • Document nature of settlement
  • Consider if this is normal course of business
5. Asset sales or impairments
  • Exclude gains/losses on asset sales from operating EBITDA
  • Remove impairment charges if truly non-recurring
  • Document what assets were sold/impaired and why
  • Adjust revenue if assets generated operating income
6. Related party adjustments
  • Normalize above-market related party expenses (rent, management fees)
  • Adjust to market rates with supporting documentation
  • Remove personal expenses run through business
  • Document market rate comparison
1. 重组费用
  • 确为非经常性则加回（门店关闭、一次性遣散费）
  • 若公司每年都有重组则不得加回
  • 记录具体性质和非经常性的逻辑
  • 示例："2023年重组：300万美元门店关闭费用，源文档记录为一次性事件"
2. 股权激励
  • 行业标准：私募股权分析时加回
  • 视为非现金运营费用
  • 所有期间处理一致
  • 若金额异常高或包含一次性授予需标注
3. 收购相关成本
  • 加回交易费用、整合成本
  • 按类型记录具体金额
  • 持续整合投资不得加回
  • 每项调整标注来源
4. 法律和解和诉讼
  • 确为孤立事件则加回
  • 评估重复发生风险（单次和解 vs 诉讼频发）
  • 记录和解性质
  • 考量是否属于正常业务范畴
5. 资产出售或减值
  • 资产出售的收益/损失从经营EBITDA中剔除
  • 确为非经常性则移除减值费用
  • 记录出售/减值的资产内容和原因
  • 若资产产生经营收入则同步调整营收
6. 关联方调整
  • 归一化高于市场水平的关联方费用（租金、管理费）
  • 调整为市场费率并提供支撑文档
  • 剔除通过公司走账的个人费用
  • 记录市场费率对比依据

Conservative vs Aggressive Normalization

保守vs激进归一化

Management Case:
  • Include all adjustments management proposes
  • Accept company's definition of "non-recurring"
  • More aggressive EBITDA adjustments
  • Use for understanding management's view
Base Case (Recommended for investment decisions):
  • Only clearly non-recurring items
  • Apply higher scrutiny to recurring "one-time" charges
  • Exclude speculative adjustments
  • More conservative, defensible to investment committee
管理层案例：
  • 包含管理层提出的所有调整项
  • 采纳公司对“非经常性”的定义
  • EBITDA调整更激进
  • 用于理解管理层的观点
基准案例（投资决策推荐）：
  • 仅包含明确非经常性的项目
  • 对重复出现的“一次性”费用进行更严格审查
  • 剔除投机性调整
  • 更保守，可向投资委员会举证

INDUSTRY-SPECIFIC ADAPTATIONS

行业适配规则

Technology/SaaS

科技/SaaS

Key metrics to capture:
  • ARR (Annual Recurring Revenue) and MRR
  • Customer count by cohort
  • CAC (Customer Acquisition Cost) and LTV (Lifetime Value)
  • Churn rate (gross and net)
  • Net revenue retention
  • Rule of 40 (Growth % + EBITDA Margin %)
  • Magic number (sales efficiency)
Format notes: ARR is currency ($), customer count is number (no $), rates are %
需采集的核心指标：
  • ARR（年度经常性收入）和MRR
  • 按 cohort拆分的客户数
  • CAC（客户获取成本）和LTV（客户生命周期价值）
  • 流失率（总流失和净流失）
  • 净收入留存
  • 40法则（增长率% + EBITDA利润率%）
  • 魔力数字（销售效率）
格式提示：ARR为货币（$）、客户数为数字（无$）、比率为%

Manufacturing/Industrial

制造业/工业

Key metrics to capture:
  • Production capacity and capacity utilization %
  • Units produced by product line
  • Inventory turns
  • Gross margin by product line
  • Order backlog
Format notes: Units, capacity are numbers (no $), utilization is %, revenue/costs are currency
需采集的核心指标：
  • 产能和产能利用率%
  • 按产品线划分的产量
  • 存货周转率
  • 按产品线划分的毛利率
  • 订单积压
格式提示：产量、产能为数字（无$）、利用率为%、营收/成本为货币

Real Estate/Hospitality

房地产/酒店业

Key metrics to capture:
  • Properties/rooms/square footage
  • Occupancy rates %
  • ADR (Average Daily Rate) - currency format
  • RevPAR (Revenue per Available Room) - currency format
  • NOI (Net Operating Income) - currency format
  • Cap rates %
  • FF&E reserve
Format notes: Rooms/sqft are numbers, occupancy is %, ADR/RevPAR are currency
需采集的核心指标：
  • 物业/客房数/平方英尺
  • 入住率%
  • ADR（平均每日房价）- 货币格式
  • RevPAR（每间可售房收入）- 货币格式
  • NOI（净运营收入）- 货币格式
  • 资本化率%
  • FF&E准备金
格式提示：客房数/平方英尺为数字、入住率为%、ADR/RevPAR为货币

Healthcare/Services

医疗/服务业

Key metrics to capture:
  • Locations/facilities
  • Providers/employees
  • Patients/visits (volume metrics)
  • Revenue per visit - currency
  • Payor mix %
  • Same-store growth %
Format notes: Locations/visits are numbers, revenue per visit is currency, rates are %
需采集的核心指标：
  • 点位/机构数
  • 服务人员/员工数
  • 患者/就诊量（数量指标）
  • 单客营收 - 货币
  • 支付方结构%
  • 同店增长%
格式提示：点位/就诊量为数字、单客营收为货币、比率为%

FINAL DELIVERY CHECKLIST

最终交付检查清单

Complete this checklist before delivering the data pack:
Structure:
  • All required tabs present and in logical sequence
  • Each tab has clear header and title
  • Executive summary is concise (fits on one page)
Data Accuracy:
  • All numbers trace to source (documents, URLs, or data servers)
  • Source references documented for key figures (page numbers, URLs, etc.)
  • All calculations are formula-based (no hardcoded calculated values)
  • Subtotals and totals verified
  • Balance sheet balances (Assets = Liabilities + Equity)
  • No #REF!, #VALUE!, or #DIV/0! errors
Formatting - Years and Numbers:
  • Years display correctly: 2020, 2021, 2022 (no commas)
  • Financial data has $ signs: $50.0, $125.5
  • Operational metrics have NO $ signs: 100 stores, 250 employees
  • Percentages formatted correctly: 15.0%, 25.5%
  • Negatives in parentheses: $(15.0) not -$15.0
Formatting - Professional Standards:
  • Headers bold and left-aligned
  • Numbers right-aligned
  • Consistent indentation (2 spaces for sub-items)
  • Single underline above subtotals
  • Double underline below final totals
  • Frozen panes on headers
  • Consistent font throughout
  • Minimal borders (only for structure)
  • Clean, professional appearance throughout
Content Completeness:
  • Financial statements complete (IS, BS, CF)
  • Operating metrics comprehensively captured
  • Normalization adjustments documented
  • Assumptions clearly stated
  • Executive summary clear, concise, and impactful
  • Investment highlights compelling
  • Market analysis provides context
Documentation:
  • All normalization adjustments explained
  • Every data cell cited from source with comments and links (document page numbers, URLs, or data source references)
  • Assumptions documented with rationale
  • Any data limitations noted
  • Filename follows convention: CompanyName_DataPack_YYYY-MM-DD.xlsx
Final Output:
  • File saved to outputs with proper naming convention
  • All quality control checks passed
交付数据包前完成以下检查：
结构：
  • 所有必填标签页存在且顺序逻辑合理
  • 每个标签页有清晰的标题
  • 执行摘要简洁（单页可容纳）
数据准确性：
  • 所有数值可追溯到来源（文档、URL或数据服务器）
  • 核心数据记录来源引用（页码、URL等）
  • 所有计算基于公式（无硬编码计算值）
  • 小计和总计已验证
  • 资产负债表平衡（资产 = 负债 + 所有者权益）
  • 无#REF!、#VALUE!或#DIV/0!错误
格式 - 年份和数字：
  • 年份展示正确：2020, 2021, 2022（无逗号）
  • 金融数据带$符号：$50.0, $125.5
  • 运营指标不带$符号：100家门店、250名员工
  • 百分比格式正确：15.0%, 25.5%
  • 负数使用括号格式：$(15.0) 而非 -$15.0
格式 - 专业标准：
  • 标题加粗且左对齐
  • 数值右对齐
  • 缩进统一（子项2空格）
  • 小计上方加单下划线
  • 最终总计下方加双下划线
  • 标题处冻结窗格
  • 全文档字体统一
  • 边框最少（仅结构必要时使用）
  • 整体外观简洁专业
内容完整性：
  • 财务报表完整（利润表、资产负债表、现金流量表）
  • 运营指标全面覆盖
  • 归一化调整已记录
  • 假设清晰明确
  • 执行摘要清晰、简洁、有冲击力
  • 投资亮点有说服力
  • 市场分析提供有效背景
文档：
  • 所有归一化调整已说明
  • 每个数据单元格通过注释和链接标注来源（文档页码、URL或数据源引用）
  • 假设已记录逻辑
  • 任何数据局限性已标注
  • 文件名遵循规范：公司名称_DataPack_YYYY-MM-DD.xlsx
最终输出：
  • 文件按规范命名保存到输出目录
  • 所有质量控制检查已通过