datapack-builder

Anthropic ｜ 金融與投資 ｜ 原文 ｜ Apache-2.0

從 CIM、SEC 文件等來源整理出標準化的投資委員會 Excel 資料包

---
name: datapack-builder
description: 從 CIM、募資備忘錄（offering memorandum）、SEC 申報文件、網路搜尋或 MCP server 等各種來源，建立專業的金融服務資料包（data pack）。擷取、標準化並統一財務資料，整理成投資委員會等級的 Excel 活頁簿，結構一致、格式正確、假設有記錄。用於併購盡職調查、私募股權分析、投資委員會資料，以及統一各投資組合公司的財務報告。不要用在簡單的財務計算，或處理已經完成的資料包。
---

# 財務資料包建構器

為私募股權、投資銀行和資產管理建立專業、標準化的財務資料包。把來自 CIM、募資備忘錄、SEC 申報文件、網路搜尋或 MCP server 存取的財務資料，轉成可供投資委員會審閱、精緻的 Excel 活頁簿。

**重要：**整個流程中，所有 Excel 檔案的建立與操作都要用 xlsx skill。

## 關鍵成功因素

每份資料包都必須達到這些標準。任何一點沒做到，交付物就無法使用。

### 1. 資料正確性（零容忍錯誤）
- 每個數字都要追溯到來源文件，並附上頁碼
- 一律用公式計算（不能寫死的值）
- 交叉檢查小計與合計，確保內部一致
- 驗證資產負債表平衡：Assets = Liabilities + Equity
- 確認現金流量與資產負債表的變動勾稽得上

### 2. 必要規則

**規則 1：財務資料（衡量金錢的）→ 帶 $ 的貨幣格式**
觸發詞：Revenue、Sales、Income、EBITDA、Profit、Loss、Cost、Expense、Cash、Debt、Assets、Liabilities、Equity、Capex
格式：百萬用 $#,##0.0，千用 $#,##0
負數：$(123.0)，不是 -$123

**規則 2：營運資料（算數量的）→ 數字格式，不加 $**
觸發詞：Units、Stores、Locations、Employees、Customers、Square Feet、Properties、Headcount
格式：#,##0，帶千分位逗號
負數：(123)，跟表格其他部分一致

**規則 3：百分比（比率與比例）→ 百分比格式**
觸發詞：Margin、Growth、Rate、Percentage、Yield、Return、Utilization、Occupancy
格式：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. 專業呈現標準

**格式標準：**

**配色：兩個層次：**

**第 1 層：字型顏色（來自 xlsx skill，強制）**
- **藍色文字（RGB: 0,0,255）**：所有寫死的輸入值（歷史資料、假設），不是一般文字
- **黑色文字（RGB: 0,0,0）**：所有公式和計算
- **綠色文字（RGB: 0,128,0）**：連結到其他工作表

**第 2 層：填色（選用，用來加強呈現）**
- 填色是選用的，只有在使用者要求，或為了加強呈現時才套用
- 如果使用者要求顏色或專業格式，使用這套標準配色：
  - **區塊標題**：深藍色（RGB: 68,114,196）背景，搭配白色文字
  - **子標題/欄位標題**：淺藍色（RGB: 217,225,242）背景，搭配黑色文字
  - **輸入儲存格**：淺綠/米色（RGB: 226,239,218）背景，搭配藍色文字
  - **計算儲存格**：白色背景，搭配黑色文字
- 如果使用者有指定，可以改用自訂的品牌色

**兩層如何搭配（如果有用填色）：**
- 輸入儲存格：藍色文字 + 淺綠色填色 = 「使用者輸入的資料」
- 公式儲存格：黑色文字 + 白色背景 = 「計算出來的值」
- 工作表連結：綠色文字 + 白色背景 = 「來自另一個分頁的參照」

**字型顏色告訴你它「是什麼」。填色告訴你它「在哪裡」（如果有用的話）。**

**重要：**xlsx skill 的字型顏色是強制的。填色是選用的，預設是白色/無填色，除非使用者要求加強格式或顏色。

**一定要套用：**
- 標題粗體、靠左對齊
- 數字靠右對齊
- 子項目縮排 2 個空格
- 小計上方用單底線
- 最終合計下方用雙底線
- 凍結列/欄標題的窗格
- 邊框要極簡（只在結構上需要的地方）
- 字型一致（通常是 Calibri 或 Arial 11pt）

**絕對不要放：**
- 每個儲存格都加邊框
- 多種字型或字型大小
- 圖表，除非特別要求
- 過度的格式或裝飾

## 結構一致性
除非明確指示不同，否則使用標準的 8 個分頁結構：
1. Executive Summary
2. Historical Financials (Income Statement)
3. Balance Sheet
4. Cash Flow Statement
5. Operating Metrics
6. Property/Segment Performance（如適用）
7. Market Analysis
8. Investment Highlights

### 分頁 1：Executive Summary
目的：給忙碌的主管看的一頁概覽

內容：
- 公司概覽（2-3 句話說明商業模式）
- 關鍵投資亮點（3-5 個重點）
- 財務快照表（最近 3 年加上預測的 Revenue、EBITDA、Growth）
- 交易概覽（如適用）
- 醒目顯示的關鍵指標

格式：乾淨、標題粗體、裝飾極簡、強調關鍵數字

### 分頁 2：Historical Financials (Income Statement)
目的：完整的損益歷史

內容：
- 依部門/產品線拆解的營收
- 銷貨成本 / 營收成本
- 毛利與毛利率 %
- 詳細的營業費用（S&M、R&D、G&A）
- EBITDA 與 Adjusted EBITDA
- 線下項目（D&A、利息、稅）
- 淨利

格式：
- 年份放在欄位（文字格式：2020、2021、2022）
- $ 百萬或 $ 千（在最上方清楚註明單位）
- 所有財務資料用會計格式
- 小計上方用單底線，淨利下方用雙底線
- 所有數字靠右對齊

### 分頁 3：Balance Sheet
目的：期末的財務狀況

內容：
- 流動資產（現金、AR、存貨、預付款、其他）
- 長期資產（PP&E、無形資產、商譽、其他）
- 流動負債（AP、應計費用、一年內到期債務、其他）
- 長期負債（長期債務、遞延稅項、其他）
- 股東權益（普通股、保留盈餘、其他）

格式：
- 驗證公式：Assets = Liabilities + Equity
- 日期標示一致
- 包含營運資金計算
- 主要小計上方用單底線，最終合計用雙底線

### 分頁 4：Cash Flow Statement
目的：現金創造與使用的分析

內容：
- 營業現金流（優先用間接法）
- 投資現金流（capex、收購、資產出售）
- 籌資現金流（債務發行/償還、權益、股利）
- 現金淨變動
- 期初與期末現金餘額

格式：
- 盡可能連結到損益表和資產負債表
- 顯示淨利到營業現金流的調節
- 清楚標示現金的用途（流出）和來源（流入）

### 分頁 5：Operating Metrics
目的：非財務的 KPI 與營運資料

內容（依產業而定）：
- 單位量、客戶數、據點數
- 生產力指標（每位員工、每家店、每單位的營收）
- 產能利用率
- 市占率
- 客戶留存/流失率
- 產業特有的 KPI

**關鍵的格式注意事項：**
營運指標**不加**金錢符號。這些是數量，不是貨幣。

格式：
- 清楚註明單位（客戶、員工、店、平方英尺等）
- 帶千分位逗號的整數：1,250，不是 $1,250
- 比率用百分比：95.0%
- 數字靠右對齊

### 分頁 6：Property/Segment Performance（如適用）
目的：依事業單位、物業或部門的詳細拆解

內容：
- 各部門的營收與獲利能力
- 各據點/產品的關鍵指標
- 部門特有的 KPI
- 比較績效分析

格式：營收/EBITDA 跟財務分頁一致，營運指標用數字格式

### 分頁 7：Market Analysis
目的：產業背景與競爭定位

內容：
- 市場規模與成長趨勢
- 競爭格局概覽
- 市占率分析
- 產業標竿與同業比較
- 監管環境（如相關）

格式：敘述文字與表格並用，市場資料要註明來源

### 分頁 8：Investment Highlights
目的：關鍵投資論點的敘述性摘要

內容：
- 競爭優勢的詳細說明
- 成長機會與策略性舉措
- 風險因素與緩解策略
- 管理層評估與過往紀錄
- 投資論點摘要

格式：標題清楚、使用重點條列、段落精簡

## 逐步工作流程

### 階段 1：文件處理與資料擷取

**步驟 1.1：分析來源資料**
- 取得來源資料：上傳的文件、網路搜尋公開申報文件，或 MCP server 的資料
- 檢視資料結構，找出關鍵區段
- 找出財務報表（通常是 3-5 年的歷史資料）
- 如果有包含管理層預測，要找出來
- 記下會計年度結束日
- 任何資料品質問題都要立刻標記

**步驟 1.2：擷取財務報表**
- 找出歷史損益表資料
- 擷取資產負債表的快照（年底或季底）
- 找出現金流量表
- 如果有管理層預測，也要擷取
- 記下所有頁碼，方便追溯

**步驟 1.3：擷取營運指標**
- 找出跟產業相關的非財務 KPI
- 擷取單位經濟（unit economics）資料
- 擷取客戶/據點/產能資料
- 記錄成長指標與趨勢

**步驟 1.4：擷取市場與產業資料**
- 競爭定位資訊
- 市場規模與成長率
- 產業標竿資料
- 同業比較資訊

**步驟 1.5：記下關鍵背景**
- 交易結構與理由
- 管理團隊背景
- 來源資料裡的投資亮點
- 風險因素與考量
- 任何資料缺口或不一致

### 階段 2：資料標準化

**步驟 2.1：統一會計表達方式**
- 確保所有年度的會計科目名稱一致
- 統一收入認列的處理方式
- 找出並記錄一次性費用
- 需要時建立「Adjusted EBITDA」的調節表
- 記下任何會計政策的變更

**步驟 2.2：套用格式偵測邏輯**
對每個資料點，根據完整的脈絡決定格式：
- 讀取分頁名稱、表格標題、欄位標題和列標籤
- 套用必要規則（見上面）
- 不確定時，查看原始來源文件
- 預設用較乾淨的格式（少即是多）

**步驟 2.3：找出需要標準化的調整項目**
要記錄的常見調整：
- 重組費用（真的是非經常性的才加回）
- 股票酬勞（依產業標準加回）
- 收購相關成本（加回，並註明金額）
- 法律和解或訴訟成本（評估再次發生的風險）
- 資產出售或減損（從營運結果中排除）
- 關係人調整（標準化成市場價格）
備註：引用來源的格式依資料來源而異（文件用頁碼，網路來源用 URL，MCP 資料用 server 參照）

**步驟 2.4：建立調整明細表**
對每一項標準化調整：
- 記錄調整了什麼、為什麼
- 引用來源（文件頁碼、URL 或資料來源參照）
- 量化每一年的金額影響
- 評估再次發生的風險
- 顯示從申報數字到調整後數字的計算

**步驟 2.5：驗證資料完整性**
- 用公式確認小計加總正確
- 驗證資產負債表平衡
- 檢查現金流量跟資產負債表的變動勾稽得上
- 交叉檢查各分頁的數字是否一致
- 標記任何差異，以便調查

### 階段 3：建立 Excel 活頁簿

**關鍵：所有 Excel 檔案的操作都要用 xlsx skill。繼續之前，先讀 xlsx skill 的文件。**

**步驟 3.1：建立標準化的分頁結構**
建立含以下分頁的活頁簿：
- Executive Summary
- Historical Financials
- Balance Sheet
- Cash Flow
- Operating Metrics
- Property Performance（如適用）
- Market Analysis
- Investment Highlights

**步驟 3.2：用正確的格式建立每個分頁**
有系統地套用格式規則：
- 標題：粗體、靠左對齊、11pt 字型
- 財務資料：貨幣格式，百萬用 $#,##0.0
- 營運資料：數字格式 #,##0（不加 $）
- 百分比：0.0% 格式
- 年份：文字格式，避免被插入逗號
- 負數：用帶括號的會計格式
- 底線：小計上方用單底線，合計下方用雙底線

**步驟 3.3：插入計算用的公式**
- 所有小計與合計都必須是公式
- 適當時把資產負債表連結到損益表
- 把現金流量表同時連結到損益表和資產負債表
- 建立跨分頁參照以便驗證
- 避免寫死任何計算出來的值

<correct_patterns>

### 追蹤列參照：照這個模式做

**寫入資料時就記下列號，之後在公式裡參照：**

```python
# ✅ 正確：寫入時追蹤列號
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

# 在公式裡使用記下來的列號
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}"
```

**複雜的模型用 dictionary：**

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

# 之後在公式裡
margin_formula = f"=B{row_refs['ebitda']}/B{row_refs['revenue']}"
```

</correct_patterns>

<common_mistakes>

### 錯誤：寫死的列偏移量

**不要用相對偏移量，表格結構一改就會壞掉：**

```python
# ❌ 錯誤：脆弱的、靠偏移量的參照
formula = f"=B{row-15}/B{row-19}"  # row-15 是什麼？row-19 又是什麼？

# ❌ 錯誤：魔術數字
formula = f"=B{current_row-10}*C{current_row-20}"
```

**為什麼會失敗：**
- 新增/刪除列時會悄悄壞掉
- 光看程式碼無法驗證正確性
- 在交付的 Excel 檔裡會變成除錯的惡夢

</common_mistakes>

**步驟 3.4：套用專業的呈現方式**
- 在每個資料分頁凍結最上面一列和第一欄
- 設定適當的欄寬（通常 12-15 個字元）
- 所有數值資料靠右對齊
- 所有文字和標題靠左對齊
- 依會計標準加上單/雙底線
- 確保外觀乾淨、簡約

### 階段 4：情境建立（如果有預測）

**Management Case：**
照來源資料提供的內容，呈現公司的預測：
- 擷取所有管理層的假設
- 記錄成長率、利潤率擴張、資本需求
- 記下關鍵驅動因素和敏感度
- 標記任何需要保持懷疑的「曲棍球棒」轉折
- 以「Management Case」呈現，並清楚標示

**Base Case（風險調整後）：**
根據公司特有的風險因素，對管理層預測做保守調整：
- 套用營收成長的折減，反映執行風險和歷史預測準確度
- 根據產業標竿和營運槓桿，調降利潤率擴張的假設
- 如果成長有賴資本支出，就提高 capex 假設
- 如果營運資金需求被低估，就補上
- 如適用，根據整合的複雜度，延後綜效的實現
- 記錄所有調整，並附上理由和佐證分析

**Downside Case（選用，但建議用在 LBO 分析）：**
根據產業的景氣循環性和公司的弱點，做壓力測試的情境：
- 建模營收下滑，反映衰退風險或競爭壓力
- 假設壓力下的利潤率壓縮（量能槓桿反轉、定價壓力）
- 測試債務條款遵循和流動性
- 評估下檔保護
- 記錄被壓力測試的關鍵風險

**情境的文件記錄要求：**
建立假設明細表，顯示：
- 各情境的關鍵假設（營收成長、利潤率、capex %）
- 每項調整的理由
- 關鍵變數的敏感度分析
- 歷史預測準確度（如果有）
- 與產業標竿的比較

### 階段 5：品質管控與驗證

**步驟 5.1：資料正確性檢查**
驗證：
- 每個數字都能追溯到來源（抽樣檢查，並引用文件/URL/server）
- 所有計算都是公式（沒有寫死的值）
- 小計與合計在數學上正確
- 年份顯示時沒有逗號（2024，不是 2,024）
- 沒有公式錯誤：#REF!、#VALUE!、#DIV/0!、#N/A

**步驟 5.2：格式一致性檢查**
確認：
- 財務資料的格式有 $ 符號
- 營運資料**沒有** $ 符號
- 百分比顯示為 %（15.0%，不是 0.15）
- 財務資料的負數用括號
- 標題是粗體、靠左對齊
- 數字靠右對齊
- 年份是文字格式

**步驟 5.3：結構與完整性檢查**
確認：
- 所有必要的分頁都有，順序正確
- Executive summary 簡潔（一頁放得下）
- 所有關鍵指標都完整擷取
- 從摘要到細節的流程合乎邏輯
- 每個分頁的細緻程度恰當
- 沒有缺漏的資料或未完成的區段

**步驟 5.4：專業呈現檢查**
檢視：
- 邊框極簡（只用在結構上）
- 縮排一致（子項目縮排 2 個空格）
- 正確的會計底線（單底線和雙底線）
- 整體乾淨、專業的外觀
- 欄寬恰當（不會太窄或太寬）

**步驟 5.5：文件記錄與假設檢查**
確保：
- 所有標準化調整都有記錄並附上理由
- 有附上來源引用（文件頁碼、URL 或資料來源參照）
- 假設有清楚說明，而且合理
- Executive summary 正確且有力
- 檔名包含公司名稱和日期

### 階段 6：最終交付

**步驟 6.1：建立 executive summary**
寫一份簡潔、有力的摘要，包含：
- 公司概覽：商業模式、產品/服務、地理（2-3 句話）
- 關鍵財務指標：Revenue、EBITDA、成長率（表格格式）
- 投資亮點：3-5 個關鍵優勢或機會
- 值得注意的風險或考量（簡短）
- 交易背景（如適用）

**步驟 6.2：最終檔案準備**
- 用正確的命名儲存活頁簿：CompanyName_DataPack_YYYY-MM-DD.xlsx

## 標準化模式

### EBITDA 的常見調整

**1. 重組費用**
- 真的是非經常性的才加回（關廠、一次性遣散費）
- 如果公司每年都在重組，就**不要**加回
- 記錄具體的性質，以及為什麼不會再發生的理由
- 範例："2023 restructuring: $3.0M facility closure, documented in source materials, one-time event"

**2. 股票酬勞**
- 業界標準：在私募股權分析中加回
- 視為非現金的營業費用
- 所有期間要一致
- 如果特別高，或包含一次性的授予，要註明

**3. 收購相關成本**
- 加回交易費用、整合成本
- 依類型記錄具體金額
- 持續性的整合投資不要加回
- 每項調整都要引用來源

**4. 法律和解與訴訟**
- 真的是孤立事件才加回
- 評估再次發生的風險（單一和解 vs 一連串的訴訟）
- 記錄和解的性質
- 考慮這是否屬於正常營運範圍

**5. 資產出售或減損**
- 從營運 EBITDA 中排除資產出售的損益
- 真的是非經常性的才移除減損費用
- 記錄出售/減損的是哪些資產、為什麼
- 如果這些資產有產生營業利益，要調整營收

**6. 關係人調整**
- 把高於市價的關係人費用（租金、管理費）標準化
- 調整成市價，並附上佐證文件
- 移除透過公司報銷的個人費用
- 記錄市價比較

### 保守 vs 積極的標準化

**Management Case：**
- 納入管理層提出的所有調整
- 接受公司對「非經常性」的定義
- EBITDA 調整較積極
- 用來了解管理層的觀點

**Base Case（投資決策建議用這個）：**
- 只納入明確非經常性的項目
- 對經常出現的「一次性」費用，審視得更嚴格
- 排除推測性的調整
- 較保守，向投資委員會說明時站得住腳

## 依產業的調整

### 科技/SaaS
要擷取的關鍵指標：
- ARR（Annual Recurring Revenue）與 MRR
- 依 cohort 的客戶數
- CAC（客戶取得成本）與 LTV（生命週期價值）
- 流失率（總額與淨額）
- 淨營收留存率
- Rule of 40（Growth % + EBITDA Margin %）
- Magic number（銷售效率）

格式備註：ARR 是貨幣（$），客戶數是數字（不加 $），比率是 %

### 製造/工業
要擷取的關鍵指標：
- 生產產能與產能利用率 %
- 各產品線的生產量
- 存貨週轉
- 各產品線的毛利率
- 訂單積壓（backlog）

格式備註：單位、產能是數字（不加 $），利用率是 %，營收/成本是貨幣

### 不動產/旅宿
要擷取的關鍵指標：
- 物業/房間數/平方英尺
- 住房率 %
- ADR（Average Daily Rate）：貨幣格式
- RevPAR（Revenue per Available Room）：貨幣格式
- NOI（Net Operating Income）：貨幣格式
- Cap rate %
- FF&E 準備金

格式備註：房間數/平方英尺是數字，住房率是 %，ADR/RevPAR 是貨幣

### 醫療保健/服務業
要擷取的關鍵指標：
- 據點/設施
- 醫療提供者/員工
- 病患/就診（量的指標）
- 每次就診營收：貨幣
- 付款人組成 %
- 同店成長 %

格式備註：據點/就診數是數字，每次就診營收是貨幣，比率是 %

## 最終交付檢查清單

交付資料包之前，完成這份檢查清單：

**結構：**
- 所有必要的分頁都有，順序合乎邏輯
- 每個分頁都有清楚的標題
- Executive summary 簡潔（一頁放得下）

**資料正確性：**
- 所有數字都能追溯到來源（文件、URL 或資料 server）
- 關鍵數字有記錄來源參照（頁碼、URL 等）
- 所有計算都是公式（沒有寫死的計算值）
- 小計與合計已驗證
- 資產負債表平衡（Assets = Liabilities + Equity）
- 沒有 #REF!、#VALUE! 或 #DIV/0! 錯誤

**格式：年份與數字：**
- 年份顯示正確：2020、2021、2022（沒有逗號）
- 財務資料有 $ 符號：$50.0、$125.5
- 營運指標**沒有** $ 符號：100 stores、250 employees
- 百分比格式正確：15.0%、25.5%
- 負數用括號：$(15.0)，不是 -$15.0

**格式：專業標準：**
- 標題粗體、靠左對齊
- 數字靠右對齊
- 縮排一致（子項目縮排 2 個空格）
- 小計上方用單底線
- 最終合計下方用雙底線
- 標題凍結窗格
- 全篇字型一致
- 邊框極簡（只用在結構上）
- 整體乾淨、專業的外觀

**內容完整性：**
- 財務報表完整（IS、BS、CF）
- 營運指標完整擷取
- 標準化調整已記錄
- 假設有清楚說明
- Executive summary 清楚、簡潔、有力
- 投資亮點有說服力
- 市場分析提供了背景脈絡

**文件記錄：**
- 所有標準化調整都有說明
- 每個資料儲存格都有來源引用，附上註解和連結（文件頁碼、URL 或資料來源參照）
- 假設有記錄並附上理由
- 任何資料限制都有註明
- 檔名符合慣例：CompanyName_DataPack_YYYY-MM-DD.xlsx

**最終輸出：**
- 檔案以正確的命名慣例儲存到 outputs
- 所有品質管控檢查都已通過

---
name: datapack-builder
description: Build professional financial services data packs from various sources including CIMs, offering memorandums, SEC filings, web search, or MCP servers. Extract, normalize, and standardize financial data into investment committee-ready Excel workbooks with consistent structure, proper formatting, and documented assumptions. Use for M&A due diligence, private equity analysis, investment committee materials, and standardizing financial reporting across portfolio companies. Do not use for simple financial calculations or working with already-completed data packs.
---

# 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.

## 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)
- 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

**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.

### 3. Professional Presentation Standards

**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

## 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

### Tab 1: Executive Summary
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

### Tab 2: Historical Financials (Income Statement)
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

### Tab 3: Balance Sheet
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
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
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

### Tab 6: Property/Segment Performance (if applicable)
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

### Tab 7: Market Analysis
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
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

**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

### Phase 2: Data Normalization and Standardization

**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

### Phase 3: Build Excel Workbook

**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>

### Row Reference Tracking - Copy This Pattern

**Store row numbers when writing data, then reference them in formulas:**

```python
# ✅ 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

# 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
}

# Later in formulas
margin_formula = f"=B{row_refs['ebitda']}/B{row_refs['revenue']}"
```

</correct_patterns>

<common_mistakes>

### WRONG: Hardcoded Row Offsets

**Don't use relative offsets - they break when table structure changes:**

```python
# ❌ WRONG - Fragile offset-based references
formula = f"=B{row-15}/B{row-19}"  # What is row-15? What is 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

### Phase 4: Scenario Building (if projections included)

**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

### Phase 5: Quality Control and Validation

**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

### Phase 6: Final Delivery

**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

## NORMALIZATION PATTERNS

### Common Adjustments to 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

### Conservative vs Aggressive Normalization

**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

## INDUSTRY-SPECIFIC ADAPTATIONS

### Technology/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 %

### 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

### 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