3-statement-model

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

把三表財務模型範本填完，公式串好，一步步跟你確認勾稽

---
name: 3-statement-model
description: 完成、填寫並補齊三表財務模型範本（損益表、資產負債表、現金流量表）。當被要求填寫模型範本、完成既有的模型框架、把資料填進財務模型、完成部分填好的 IS/BS/CF 框架，或在既有範本結構內串接整合的財務報表時使用。觸發情境包含要求填入、完成或補齊三表模型範本
---

# 三表財務模型範本填寫

完成並填寫整合式的財務模型範本，讓損益表、資產負債表和現金流量表之間的勾稽關係正確。

## ⚠️ 關鍵原則：填寫任何範本之前先讀

**環境：Office JS 還是 Python：**
- **如果在 Excel 裡執行（Office Add-in / Office JS）：**直接用 Office JS。用 `range.formulas = [["=D14*(1+Assumptions!$B$5)"]]` 寫入公式，推導出來的儲存格絕對不要用 `range.values`。不需要另外重算，Excel 會原生計算。用 `context.workbook.worksheets.getItem(...)` 切換分頁。
- **如果是產生獨立的 .xlsx 檔：**用 Python/openpyxl。寫 `ws["D15"] = "=D14*(1+Assumptions!$B$5)"`，交付前執行 `recalc.py`。
- **Office JS 合併儲存格的陷阱：**不要先呼叫 `.merge()` 再對合併後的範圍設定 `.values`，這會丟出 `InvalidArgument`，因為該範圍仍然回報合併前的尺寸。正確做法是先只對左上角的儲存格寫值，再合併並格式化整個範圍：`ws.getRange("A1").values = [["INCOME STATEMENT"]]; const h = ws.getRange("A1:G1"); h.merge(); h.format.fill.color = "#1F4E79";`
- 以下所有原則，在兩種環境下都一樣適用。

**用公式，不要寫死數值（沒有商量餘地）：**
- 每個預測儲存格、滾動結轉（roll-forward）、勾稽連結和小計，**一定要**是 Excel 公式，絕對不能是預先算好的值
- 用 Python/openpyxl 時：寫公式字串（`ws["D15"] = "=D14*(1+Assumptions!$B$5)"`），**不要**寫算好的結果（`ws["D15"] = 12500`）
- 只有這兩種儲存格可以放寫死的數字：(1) 歷史實際數，(2) Assumptions 分頁裡的假設驅動因子
- 如果你發現自己在 Python 裡算出一個值，然後把結果寫進儲存格，立刻停下來，改寫公式。
- 原因：情境切換或假設變動時，模型必須能跟著動。寫死的數值會讓所有下游的完整性檢查悄悄失效。

**每一步都跟使用者確認：**
1. **對應完範本之後**：先給使用者看你找到哪些分頁/區塊，確認後才能動任何儲存格
2. **填完歷史數之後**：把歷史區塊給使用者看，確認數值和期間跟來源資料一致
3. **建完 IS 預測之後**：跑小計檢查，把預測的 IS 給使用者看，確認後才進行 BS
4. **建完 BS 之後**：把每一期的平衡檢查（Assets = L+E）給使用者看，確認後才進行 CF
5. **建完 CF 之後**：把現金勾稽（CF 期末現金 = BS 現金）給使用者看，確認後才定稿
6. **不要一口氣把整個模型從頭填到尾，再當成完成品交出去**：每張報表做完就停一下，給人看過程，及早抓出錯誤

## 格式：專業的藍/灰配色（除非範本或使用者另有指定，否則預設用這組）

**顏色要克制。**儲存格填色只用藍色和灰色。**不要**加入綠色、黃色、橘色或多種強調色，乾淨的模型要懂得節制。

| 元素 | 填色 | 字型 |
|---|---|---|
| 區塊標題（IS / BS / CF 標題） | 深藍 `#1F4E79` | 白色粗體 |
| 欄位標題（FY2024A、FY2025E 等） | 淺藍 `#D9E1F2` | 黑色粗體 |
| 輸入儲存格（歷史數、假設驅動因子） | 淺灰 `#F2F2F2` 或白色 | 藍色 `#0000FF` |
| 公式儲存格 | 白色 | 黑色 |
| 跨分頁連結 | 白色 | 綠色 `#008000` |
| 檢查列 / 關鍵合計 | 中藍 `#BDD7EE` | 黑色粗體 |

**也就是 3 種藍 + 1 種灰 + 白色。**如果範本有自己的配色，就跟著範本走。

字型顏色表示儲存格*是什麼*（輸入/公式/連結）。填色表示你*在哪裡*（標題/資料/檢查）。

## 模型結構

### 辨識範本的分頁組織

各個範本的分頁命名慣例和組織方式不同。填寫之前，先檢視所有分頁，了解範本的結構。下面是常見的分頁名稱和它們通常的內容：

| 常見分頁名稱 | 要找的內容 |
|------------------|----------------------|
| IS, P&L, Income Statement | 損益表 |
| BS, Balance Sheet | 資產負債表 |
| CF, CFS, Cash Flow | 現金流量表 |
| WC, Working Capital | 營運資金明細表 |
| DA, D&A, Depreciation, PP&E | 折舊與攤銷明細表 |
| Debt, Debt Schedule | 債務明細表 |
| NOL, Tax, DTA | 淨營業虧損（NOL）明細表 |
| Assumptions, Inputs, Drivers | 驅動因子假設與輸入 |
| Checks, Audit, Validation | 錯誤檢查儀表板 |

**範本檢視清單**
- 辨識範本裡有哪些分頁（不是每個範本都包含所有明細表）
- 記下上表沒列出的、範本特有的分頁
- 了解分頁之間的相依關係（例如哪些明細表會餵進主要報表）
- 在每個分頁上找出輸入儲存格和公式儲存格

### 了解範本結構

填寫範本之前，先熟悉它現有的版面配置，確保資料填在正確的位置，公式也不會被破壞。

**辨識列的結構**
- 找出每個分頁最上方的模型標題
- 辨識區塊標題和它們在視覺上的分隔
- 找出標示單位的列（$ 百萬、%、x 等）
- 注意用來區分實際數與預估數期間的欄位標題
- 確認期間標籤（例如 FY2024A、FY2025E）
- 辨識輸入儲存格和公式儲存格（通常用字型顏色區分）

**辨識欄的結構**
- 確認最左邊那欄是會計科目標籤
- 確認歷史年度排在預測年度之前
- 注意區隔歷史與預測期間的視覺邊框
- 檢查所有分頁的欄位順序是否一致

**處理具名範圍（Named Range）**
範本常用具名範圍來標示關鍵的輸入和輸出。輸入資料之前：
- 檢視範本中現有的具名範圍（Excel 的 Formulas → Name Manager）
- 常見的具名範圍包含：營收成長率、成本百分比、關鍵輸出（Net Income、EBITDA、Total Debt、Cash）、情境選擇儲存格
- 確保輸入的資料是填在會餵進這些具名範圍的儲存格

### 預測期間
- 範本通常從最後一個歷史年度往後預測 5 年
- 確認歷史（A）與預測（E）欄位有清楚區隔
- 確認欄位使用會計年度的標記法（例如 FY2024A、FY2025E）

## 利潤率分析

**注意：只有在使用者要求，或範本明確需要時，才執行下面的利潤率分析。如果沒有被要求，就跳過這一節。**

在損益表（IS）分頁計算並顯示獲利率，用來追蹤營運效率，也方便跟同業比較。

### 要納入的核心利潤率

| 利潤率 | 公式 | 衡量什麼 |
|--------|---------|------------------|
| Gross Margin | Gross Profit / Revenue | 定價能力、生產效率 |
| EBITDA Margin | EBITDA / Revenue | 核心營運獲利能力 |
| EBIT Margin | EBIT / Revenue | 扣除 D&A 後的營運獲利能力 |
| Net Income Margin | Net Income / Revenue | 最終的獲利能力 |

### 含利潤率的損益表版面配置

把利潤率百分比直接顯示在每個利潤項目的下方：
- Gross Margin % 放在 Gross Profit 下方
- EBIT Margin % 放在 EBIT 下方
- EBITDA Margin % 放在 EBITDA 下方
- Net Income Margin % 放在 Net Income 下方

## 信用指標

**注意：只有在使用者要求，或範本明確需要時，才執行下面的信用分析。如果沒有被要求，就跳過這一節。**

在資產負債表（BS）分頁計算並顯示信用/槓桿指標，用來評估財務健全度、舉債能力和債務條款（covenant）的遵循情況。

### 要納入的核心信用指標

| 指標 | 公式 | 衡量什麼 |
|--------|---------|------------------|
| Total Debt / EBITDA | Total Debt / LTM EBITDA | 槓桿倍數 |
| Net Debt / EBITDA | (Total Debt - Cash) / LTM EBITDA | 扣除現金後的槓桿 |
| Interest Coverage | EBITDA / Interest Expense | 償債能力 |
| Debt / Total Cap | Total Debt / (Total Debt + Equity) | 資本結構 |
| Debt / Equity | Total Debt / Total Equity | 財務槓桿 |
| Current Ratio | Current Assets / Current Liabilities | 短期流動性 |
| Quick Ratio | (Current Assets - Inventory) / Current Liabilities | 立即流動性 |

### 信用指標的層級檢查

驗證 Upside 呈現出最強的信用狀況：
- 槓桿：Upside < Base < Downside（越低越好）
- 涵蓋倍數：Upside > Base > Downside（越高越好）
- 流動性：Upside > Base > Downside（越高越好）

### 債務條款遵循追蹤

如果已知債務條款，就加上明確的遵循檢查，把實際指標拿來跟條款門檻比較。

## 情境分析（Base / Upside / Downside）

在 Assumptions 分頁用情境切換（下拉選單），搭配 CHOOSE 或 INDEX/MATCH 公式。

| 情境 | 說明 |
|----------|-------------|
| Base Case | 管理階層的財測或市場共識預估 |
| Upside Case | 高於財測的成長、利潤率擴張 |
| Downside Case | 低於趨勢的成長、利潤率壓縮 |

**要做敏感度分析的關鍵驅動因子**：營收成長、毛利率、SG&A %、DSO/DIO/DPO、CapEx %、利率、稅率。

**情境稽核檢查**：切換會帶動所有報表、BS 在所有情境下都平衡、現金勾稽得上、層級關係成立（NI、EBITDA、FCF、利潤率都是 Upside > Base > Downside）。

## SEC 申報文件的資料擷取

如果範本明確要求從 SEC 申報文件（10-K、10-Q）撈資料，詳細的擷取指引請看 [references/sec-filings.md](references/sec-filings.md)。只有在用監管申報文件的上市公司資料填寫範本時，才需要這份參考文件。

## 完成模型範本

這一節提供完成任何三表財務模型範本的通則，同時保留既有的公式並確保資料完整。

### 步驟 1：分析範本結構

輸入任何資料之前，先徹底檢視範本，了解它的架構：

**辨識輸入儲存格與公式儲存格**
- 找視覺線索（字型顏色、儲存格底色）來區分輸入儲存格和公式儲存格
- 常見慣例：藍色字型 = 輸入，黑色字型 = 公式，綠色字型 = 連結到其他工作表
- 用 Excel 的追蹤前導/從屬參照（Formulas → Trace Precedents）了解儲存格之間的關係
- 檢查是否有控制關鍵輸入的具名範圍（Formulas → Name Manager）

**對應範本的資料流**
- 辨識哪些分頁會餵進其他分頁（例如 Assumptions → IS → BS → CF）
- 記下所有輔助明細表，以及它們和主要報表的連結
- 填寫之前，先記錄範本特有的會計科目與結構

### 步驟 2：填資料但不破壞公式

**資料輸入的黃金法則**

| 規則 | 說明 |
|------|-------------|
| 只編輯輸入儲存格 | 絕對不要覆蓋含有公式的儲存格，除非你就是有意要取代那個公式 |
| 保留儲存格參照 | 複製資料時用選擇性貼上值（Ctrl+Shift+V），避免來源的格式把公式蓋掉 |
| 配合範本的單位 | 輸入資料之前，先確認範本用的是千、百萬還是實際數值 |
| 遵守正負號慣例 | 跟著範本既有的正負號慣例（例如費用用正數還是負數） |
| 檢查循環參照 | 如果範本用了迭代計算，要確定「啟用迭代計算」有打開 |

**安全的資料輸入流程**
1. 找出指定給輸入用的儲存格（通常會標色或加標籤）
2. 先輸入歷史資料，再確認這些期間的公式有正確計算
3. 輸入餵進預測計算的假設驅動因子
4. 檢視計算出來的輸出，確認公式的運作符合預期
5. 如果必須修改某個公式儲存格，改之前先記錄原本的公式

**處理預先建好的公式**
- 如果公式參照到你還沒填的儲存格，在所有輸入都補齊之前，出現暫時性的錯誤（#REF!、#DIV/0!）是正常的
- 公式產生出乎意料的結果時，追蹤前導參照，找出缺漏或錯誤的輸入
- 沒有先檢查所有分頁的公式相依性之前，絕對不要刪除列/欄

### 步驟 3：驗證公式

**公式完整性檢查**

在信任範本的輸出之前，先驗證公式有正確運作：

| 檢查類型 | 方法 |
|------------|--------|
| 追蹤前導參照 | 選取公式儲存格 → Formulas → Trace Precedents，確認它參照的是正確的輸入 |
| 追蹤從屬參照 | 確認關鍵輸入有流向預期的輸出儲存格 |
| 公式求值 | 用 Formulas → Evaluate Formula 逐步檢視複雜的計算 |
| 檢查有沒有寫死的值 | 預測公式應該參照假設，不能含有寫死的數值 |
| 用已知的值測試 | 輸入簡單的測試值，確認公式產生預期的結果 |
| 跨分頁一致性 | 確保所有預測期間都套用相同的公式邏輯 |

**要注意的常見公式問題**
- 絕對/相對參照混用，造成跨期間複製時結果不對
- 外部檔案的連結壞掉，或範圍被刪除（#REF! 錯誤）
- 營收還沒起來的早期期間發生除以零（#DIV/0! 錯誤）
- 循環參照警告（利息計算可能是刻意的）
- 各預測欄位之間的公式不一致（用 Ctrl+\ 找差異）

**驗證跨分頁的連結**
- 確認出現在多個分頁的數值是「連結」過去的（不是各自重複填寫）
- 確認明細表的合計跟主要報表對應的科目勾稽得上
- 檢查所有分頁的期間標籤是否對齊

### 步驟 4：依工作表做品質檢查

填完範本之後，對每個工作表執行這些驗證檢查：

**損益表（IS）品質檢查**
- 歷史期間的營收數字跟來源資料一致
- 所有費用科目加總等於申報的合計
- 小計（Gross Profit、EBIT、EBT、Net Income）計算正確
- 稅額計算邏輯恰當（有正確處理虧損）
- 預測驅動因子參照 Assumptions 分頁（沒有寫死的值）
- 各期之間的變化在方向上合理

**資產負債表（BS）品質檢查**
- 每一期都符合 Assets = Liabilities + Equity（最主要的檢查）
- 現金餘額和現金流量表的期末現金一致
- 營運資金科目跟輔助明細表勾稽得上（如適用）
- Retained Earnings 滾動結轉正確：前期 RE + Net Income - 股利 +/- 調整 = 期末 RE
- 債務餘額跟債務明細表勾稽得上（如適用）
- 所有資產負債表項目的正負號都恰當（資產為正，大部分負債為正）

**現金流量表（CF）品質檢查**
- CFO 最上方的 Net Income 跟損益表的 Net Income 一致
- 非現金加回項目（D&A、SBC 等）跟它們的來源明細表/報表勾稽得上
- 營運資金變動的正負號正確（資產增加 = 現金流出 = 負數）
- CapEx 跟 PP&E 明細表或固定資產滾動結轉勾稽得上
- 籌資活動跟 BS 上債務與權益科目的變動勾稽得上
- 期末現金跟資產負債表的現金一致
- 期初現金等於前一期的期末現金

**輔助明細表品質檢查**
- 期初餘額等於前一期的期末餘額
- 滾動結轉邏輯完整（期初 + 增加 - 減少 = 期末）
- 明細表合計跟主要報表的科目勾稽得上
- 計算中使用的假設跟 Assumptions 分頁一致

### 步驟 5：跨報表完整性檢查

驗證完個別工作表之後，確認三張報表有正確整合：

| 檢查 | 公式 | 預期結果 |
|-------|---------|-----------------|
| 資產負債表平衡 | Assets - Liabilities - Equity | = 0 |
| 現金勾稽 | CF Ending Cash - BS Cash | = 0 |
| Net Income 連結 | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI - Dividends - BS Ending RE | = 0（視需要針對 SBC/其他項目調整） |

### 步驟 6：最後檢視

在認定模型完成之前：
- 切換所有情境（如適用），確認每個情境下檢查都通過
- 檢視所有 #REF!、#DIV/0!、#VALUE! 和 #NAME? 錯誤，並解決或記錄下來
- 確認所有輸入儲存格都已填寫（搜尋佔位用的值）
- 確認所有分頁的單位一致
- 在做任何額外修改之前，先存一份乾淨的版本

## 模型驗證與稽核

這一節彙整了已完成範本的所有驗證檢查和稽核程序。

### 核心勾稽關係（必須永遠成立）

所有公式細節請看 [references/formulas.md](references/formulas.md)。

| 檢查 | 公式 | 預期結果 |
|-------|---------|-----------------|
| 資產負債表平衡 | Assets - Liabilities - Equity | = 0 |
| 現金勾稽 | CF Ending Cash - BS Cash | = 0 |
| 現金月度 vs 年度 | Closing Cash (Monthly) - Closing Cash (Annual) | = 0 |
| Net Income 連結 | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI + SBC - Dividends - BS Ending RE | = 0 |
| 權益籌資 | ΔCommon Stock/APIC (BS) - Equity Issuance (CFF) | = 0 |
| 第 0 年權益 | Equity Raised (Year 0) - Beginning Equity Capital (Year 1) | = 0 |

### 正負號慣例參考

| 報表 | 項目 | 正負號慣例 |
|-----------|------|-----------------|
| CFO | D&A、SBC | 正數（加回） |
| CFO | ΔAR（增加） | 負數（現金流出） |
| CFO | ΔAP（增加） | 正數（現金來源） |
| CFI | CapEx | 負數 |
| CFF | 債務發行 | 正數 |
| CFF | 債務償還 | 負數 |
| CFF | 股利 | 負數 |

### 循環參照的處理

利息費用會造成循環：Interest → Net Income → Cash → Debt Balance → Interest

在 Excel 啟用迭代計算：File → Options → Formulas → Enable iterative calculation。把最大迭代次數設為 100，最大變動量設為 0.001。在 Assumptions 分頁加上斷路器（circuit breaker）開關。

### 檢查類別

**第 1 節：幣別一致性**
- 幣別已辨識，並記錄在 Assumptions
- 所有分頁使用一致的幣別符號與單位尺度
- 單位列跟模型的幣別一致

**第 2 節：資產負債表完整性**
- Assets = Liabilities + Equity（每一期）
- 公式：Assets - Liabilities - Equity（必須 = 0）

**第 3 節：現金流量完整性**
- 現金跟 BS 勾稽得上（CF Ending Cash = BS Cash）
- 現金月度 vs 年度：Closing Cash (Monthly) = Closing Cash (Annual)
- NI 跟 IS 勾稽得上（CF Net Income = IS Net Income）
- D&A 跟明細表勾稽得上
- SBC 跟 IS 勾稽得上
- ΔAR、ΔInventory、ΔAP 跟 WC 明細表勾稽得上
- CapEx 跟 DA 明細表勾稽得上

**第 4 節：保留盈餘**
- RE 滾動結轉檢查：Prior RE + NI + SBC - Dividends = Ending RE
- 顯示各組成項目的拆解，方便除錯

**第 5 節：營運資金**
- AR、Inventory、AP 跟 BS 勾稽得上
- DSO、DIO、DPO 的合理性檢查（超出正常範圍就標記）

**第 6 節：債務明細表**
- Total Debt 跟 BS 勾稽得上（流動 + 長期債務）
- 利息計算跟 IS 勾稽得上

**第 6b 節：權益籌資**
- 權益發行的收入跟 BS 的 Common Stock/APIC 增加勾稽得上
- 來自權益的現金增加 = 權益科目的增加（必須平衡）
- 權益募集勾稽：ΔCommon Stock/APIC (BS) = Equity Issuance (CFF)（必須 = 0）
- 第 0 年權益勾稽：Equity Raised (Year 0) = Beginning Equity Capital (Year 1)

**第 6c 節：NOL 明細表**
- 期初 NOL（第 1 年 / 設立時）= 0（新事業從零 NOL 開始）
- NOL 只會在 EBT < 0 時增加（必須實現虧損才會產生 NOL）
- DTA 跟 BS 勾稽得上（NOL 明細表 DTA = BS Deferred Tax Asset）
- NOL 使用額 ≤ EBT 的 80%（2017 年後的聯邦限制）
- NOL 餘額不得為負（使用額不能超過可用額）
- NOL 只在 EBT < 0 時產生
- 應稅所得 ≤ 0 時，稅額費用 = 0

**第 7 節：情境層級關係**
- 絕對數值指標：Upside > Base > Downside（NI、EBITDA、FCF）
- 利潤率：Upside > Base > Downside（GM%、EBITDA%、NI%）
- 信用指標：槓桿類是 Upside < Base < Downside（反向）

**第 8 節：公式完整性**
- COGS、S&M、G&A、R&D、SBC 由營收百分比驅動（沒有寫死的值）
- 各預測年度的公式一致
- 沒有 #REF!、#DIV/0!、#VALUE! 錯誤

**第 9 節：信用指標門檻**
- 依債務條款門檻，把指標標記成綠/黃/紅
- 彙整所有紅色警示

### 總檢查公式

把所有章節的狀態彙整成單一的總檢查：
- 如果所有章節都通過 → "✓ ALL CHECKS PASS"
- 如果有任何章節失敗 → "✗ ERRORS DETECTED - REVIEW BELOW"

### 快速除錯流程

當總狀態顯示有錯誤時：
1. 捲動找出紅色標示的區塊
2. 辨識哪個檢查類別有失敗
3. 前往來源分頁調查
4. 修正根本問題
5. 回到 Checks 分頁確認問題已解決

---
name: 3-statement-model
description: Complete, populate and fill out 3-statement financial model templates (Income Statement, Balance Sheet, Cash Flow Statement) . Use when asked to fill out model templates, complete existing model frameworks, populate financial models with data, complete a partially filled IS/BS/CF framework, or link integrated financial statements within an existing template structure. Triggers include requests to fill in, complete, or populate a 3-statement model template
---

# 3-Statement Financial Model Template Completion

Complete and populate integrated financial model templates with proper linkages between Income Statement, Balance Sheet, and Cash Flow Statement.

## ⚠️ CRITICAL PRINCIPLES — Read Before Populating Any Template

**Environment — Office JS vs Python:**
- **If running inside Excel (Office Add-in / Office JS):** Use Office JS directly. Write formulas via `range.formulas = [["=D14*(1+Assumptions!$B$5)"]]` — never `range.values` for derived cells. No separate recalc; Excel computes natively. Use `context.workbook.worksheets.getItem(...)` to navigate tabs.
- **If generating a standalone .xlsx file:** Use Python/openpyxl. Write `ws["D15"] = "=D14*(1+Assumptions!$B$5)"`, then run `recalc.py` before delivery.
- **Office JS merged cell pitfall:** Do NOT call `.merge()` then set `.values` on the merged range — throws `InvalidArgument` because the range still reports its pre-merge dimensions. Instead write value to top-left cell alone, then merge + format the full range: `ws.getRange("A1").values = [["INCOME STATEMENT"]]; const h = ws.getRange("A1:G1"); h.merge(); h.format.fill.color = "#1F4E79";`
- All principles below apply identically in either environment.

**Formulas over hardcodes (non-negotiable):**
- Every projection cell, roll-forward, linkage, and subtotal MUST be an Excel formula — never a pre-computed value
- When using Python/openpyxl: write formula strings (`ws["D15"] = "=D14*(1+Assumptions!$B$5)"`), NOT computed results (`ws["D15"] = 12500`)
- The ONLY cells that should contain hardcoded numbers are: (1) historical actuals, (2) assumption drivers in the Assumptions tab
- If you find yourself computing a value in Python and writing the result to a cell — STOP. Write the formula instead.
- Why: the model must flex when scenarios toggle or assumptions change. Hardcodes break every downstream integrity check silently.

**Verify step-by-step with the user:**
1. **After mapping the template** → show the user which tabs/sections you've identified and confirm before touching any cells
2. **After populating historicals** → show the user the historical block and confirm values/periods match source data
3. **After building IS projections** → run the subtotal checks, show the user the projected IS, confirm before moving to BS
4. **After building BS** → show the user the balance check (Assets = L+E) for every period, confirm before moving to CF
5. **After building CF** → show the user the cash tie-out (CF ending cash = BS cash), confirm before finalizing
6. **Do NOT populate the entire model end-to-end and present it complete** — break at each statement, show the work, catch errors early

## Formatting — Professional Blue/Grey Palette (Default unless template/user specifies otherwise)

**Keep colors minimal.** Use only blues and greys for cell fills. Do NOT introduce greens, yellows, oranges, or multiple accent colors — a clean model uses restraint.

| Element | Fill | Font |
|---|---|---|
| Section headers (IS / BS / CF titles) | Dark blue `#1F4E79` | White bold |
| Column headers (FY2024A, FY2025E, etc.) | Light blue `#D9E1F2` | Black bold |
| Input cells (historicals, assumption drivers) | Light grey `#F2F2F2` or white | Blue `#0000FF` |
| Formula cells | White | Black |
| Cross-tab links | White | Green `#008000` |
| Check rows / key totals | Medium blue `#BDD7EE` | Black bold |

**That's 3 blues + 1 grey + white.** If the template has its own color scheme, follow the template instead.

Font color signals *what* a cell is (input/formula/link). Fill color signals *where* you are (header/data/check).

## Model Structure

### Identifying Template Tab Organization

Templates vary in their tab naming conventions and organization. Before populating, review all tabs to understand the template's structure. Below are common tab names and their typical contents:

| Common Tab Names | Contents to Look For |
|------------------|----------------------|
| IS, P&L, Income Statement | Income Statement |
| BS, Balance Sheet | Balance Sheet |
| CF, CFS, Cash Flow | Cash Flow Statement |
| WC, Working Capital | Working Capital Schedule |
| DA, D&A, Depreciation, PP&E | Depreciation & Amortization Schedule |
| Debt, Debt Schedule | Debt Schedule |
| NOL, Tax, DTA | Net Operating Loss Schedule |
| Assumptions, Inputs, Drivers | Driver assumptions and inputs |
| Checks, Audit, Validation | Error-checking dashboard |

**Template Review Checklist**
- Identify which tabs exist in the template (not all templates include every schedule)
- Note any template-specific tabs not listed above
- Understand tab dependencies (e.g., which schedules feed into the main statements)
- Locate input cells vs. formula cells on each tab

### Understanding Template Structure

Before populating a template, familiarize yourself with its existing layout to ensure data is entered in the correct locations and formulas remain intact.

**Identifying Row Structure**
- Locate the model title at top of each tab
- Identify section headers and their visual separation
- Find the units row indicating $ millions, %, x, etc.
- Note column headers distinguishing Actuals vs. Estimates periods
- Confirm period labels (e.g., FY2024A, FY2025E)
- Identify input cells vs. formula cells (typically distinguished by font color)

**Identifying Column Structure**
- Confirm line item labels in leftmost column
- Verify historical years precede projection years
- Note the visual border separating historical from projected periods
- Check for consistent column order across all tabs

**Working with Named Ranges**
Templates often use named ranges for key inputs and outputs. Before entering data:
- Review existing named ranges in the template (Formulas → Name Manager in Excel)
- Common named ranges include: Revenue growth rates, cost percentages, key outputs (Net Income, EBITDA, Total Debt, Cash), scenario selector cell
- Ensure inputs are entered in cells that feed into these named ranges

### Projection Period
- Templates typically project 5 years forward from last historical year
- Verify historical (A) vs. projected (E) columns are clearly separated
- Confirm columns use fiscal year notation (e.g., FY2024A, FY2025E)

## Margin Analysis

**Note: The following margin analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.**

Calculate and display profitability margins on the Income Statement (IS) tab to track operational efficiency and enable peer comparison.

### Core Margins to Include

| Margin | Formula | What It Measures |
|--------|---------|------------------|
| Gross Margin | Gross Profit / Revenue | Pricing power, production efficiency |
| EBITDA Margin | EBITDA / Revenue | Core operating profitability |
| EBIT Margin | EBIT / Revenue | Operating profitability after D&A |
| Net Income Margin | Net Income / Revenue | Bottom-line profitability |

### Income Statement Layout with Margins

Display margin percentages directly below each profit line item:
- Gross Margin % below Gross Profit
- EBIT Margin % below EBIT
- EBITDA Margin % below EBITDA
- Net Income Margin % below Net Income

## Credit Metrics

**Note: The following Credit analysis should only be performed if prompted by the user or if the template explicitly requires it. If no prompt is given, skip this section.**

Calculate and display credit/leverage metrics on the Balance Sheet (BS) tab to assess financial health, debt capacity, and covenant compliance.

### Core Credit Metrics to Include

| Metric | Formula | What It Measures |
|--------|---------|------------------|
| Total Debt / EBITDA | Total Debt / LTM EBITDA | Leverage multiple |
| Net Debt / EBITDA | (Total Debt - Cash) / LTM EBITDA | Leverage net of cash |
| Interest Coverage | EBITDA / Interest Expense | Ability to service debt |
| Debt / Total Cap | Total Debt / (Total Debt + Equity) | Capital structure |
| Debt / Equity | Total Debt / Total Equity | Financial leverage |
| Current Ratio | Current Assets / Current Liabilities | Short-term liquidity |
| Quick Ratio | (Current Assets - Inventory) / Current Liabilities | Immediate liquidity |

### Credit Metric Hierarchy Checks

Validate that Upside shows strongest credit profile:
- Leverage: Upside < Base < Downside (lower is better)
- Coverage: Upside > Base > Downside (higher is better)
- Liquidity: Upside > Base > Downside (higher is better)

### Covenant Compliance Tracking

If debt covenants are known, add explicit compliance checks comparing actual metrics to covenant thresholds.

## Scenario Analysis (Base / Upside / Downside)

Use a scenario toggle (dropdown) in the Assumptions tab with CHOOSE or INDEX/MATCH formulas.

| Scenario | Description |
|----------|-------------|
| Base Case | Management guidance or consensus estimates |
| Upside Case | Above-guidance growth, margin expansion |
| Downside Case | Below-trend growth, margin compression |

**Key Drivers to Sensitize**: Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate.

**Scenario Audit Checks**: Toggle switches all statements, BS balances in all scenarios, Cash ties out, Hierarchy holds (Upside > Base > Downside for NI, EBITDA, FCF, margins).

## SEC Filings Data Extraction

If the template specifically requires pulling data from SEC filings (10-K, 10-Q), see [references/sec-filings.md](references/sec-filings.md) for detailed extraction guidance. This reference is only needed when populating templates with public company data from regulatory filings.

## Completing Model Templates

This section provides general guidance for completing any 3-statement financial model template while preserving existing formulas and ensuring data integrity.

### Step 1: Analyze the Template Structure

Before entering any data, thoroughly review the template to understand its architecture:

**Identify Input vs. Formula Cells**
- Look for visual cues (font color, cell shading) that distinguish input cells from formula cells
- Common conventions: Blue font = inputs, Black font = formulas, Green font = links to other sheets
- Use Excel's Trace Precedents/Dependents (Formulas → Trace Precedents) to understand cell relationships
- Check for named ranges that may control key inputs (Formulas → Name Manager)

**Map the Template's Flow**
- Identify which tabs feed into others (e.g., Assumptions → IS → BS → CF)
- Note any supporting schedules and their linkages to main statements
- Document the template's specific line items and structure before populating

### Step 2: Filling in Data Without Breaking Formulas

**Golden Rules for Data Entry**

| Rule | Description |
|------|-------------|
| Only edit input cells | Never overwrite cells containing formulas unless intentionally replacing the formula |
| Preserve cell references | When copying data, use Paste Values (Ctrl+Shift+V) to avoid overwriting formulas with source formatting |
| Match the template's units | Verify if template uses thousands, millions, or actual values before entering data |
| Respect sign conventions | Follow the template's existing sign convention (e.g., expenses as positive or negative) |
| Check for circular references | If the template uses iterative calculations, ensure Enable Iterative Calculation is turned on |

**Safe Data Entry Process**
1. Identify the exact cells designated for input (usually highlighted or labeled)
2. Enter historical data first, then verify formulas are calculating correctly for those periods
3. Enter assumption drivers that feed forecast calculations
4. Review calculated outputs to confirm formulas are working as intended
5. If a formula cell must be modified, document the original formula before making changes

**Handling Pre-Built Formulas**
- If formulas reference cells you haven't populated yet, expect temporary errors (#REF!, #DIV/0!) until all inputs are complete
- When formulas produce unexpected results, trace precedents to identify missing or incorrect inputs
- Never delete rows/columns without checking for formula dependencies across all tabs

### Step 3: Validating Formulas

**Formula Integrity Checks**

Before relying on template outputs, validate that formulas are functioning correctly:

| Check Type | Method |
|------------|--------|
| Trace precedents | Select a formula cell → Formulas → Trace Precedents to verify it references correct inputs |
| Trace dependents | Verify key inputs flow to expected output cells |
| Evaluate formula | Use Formulas → Evaluate Formula to step through complex calculations |
| Check for hardcodes | Projection formulas should reference assumptions, not contain hardcoded values |
| Test with known values | Input simple test values to verify formulas produce expected results |
| Cross-tab consistency | Ensure the same formula logic applies across all projection periods |

**Common Formula Issues to Watch For**
- Mixed absolute/relative references causing incorrect results when copied across periods
- Broken links to external files or deleted ranges (#REF! errors)
- Division by zero in early periods before revenue ramps (#DIV/0! errors)
- Circular reference warnings (may be intentional for interest calculations)
- Inconsistent formulas across projection columns (use Ctrl+\ to find differences)

**Validating Cross-Tab Linkages**
- Confirm values that appear on multiple tabs are linked (not duplicated)
- Verify schedule totals tie to corresponding line items on main statements
- Check that period labels align across all tabs

### Step 4: Quality Checks by Sheet

Perform these validation checks on each sheet after populating the template:

**Income Statement (IS) Quality Checks**
- Revenue figures match source data for historical periods
- All expense line items sum to reported totals
- Subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
- Tax calculation logic is appropriate (handles losses correctly)
- Forecast drivers reference assumptions tab (no hardcodes)
- Period-over-period changes are directionally reasonable

**Balance Sheet (BS) Quality Checks**
- Assets = Liabilities + Equity for every period (primary check)
- Cash balance matches Cash Flow Statement ending cash
- Working capital accounts tie to supporting schedules (if applicable)
- Retained Earnings rolls forward correctly: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
- Debt balances tie to debt schedule (if applicable)
- All balance sheet items have appropriate signs (assets positive, most liabilities positive)

**Cash Flow Statement (CF) Quality Checks**
- Net Income at top of CFO matches Income Statement Net Income
- Non-cash add-backs (D&A, SBC, etc.) tie to their source schedules/statements
- Working capital changes have correct signs (increase in asset = use of cash = negative)
- CapEx ties to PP&E schedule or fixed asset roll-forward
- Financing activities tie to changes in debt and equity accounts on BS
- Ending Cash matches Balance Sheet Cash
- Beginning Cash equals prior period Ending Cash

**Supporting Schedule Quality Checks**
- Opening balances equal prior period closing balances
- Roll-forward logic is complete (Beginning + Additions - Deductions = Ending)
- Schedule totals tie to main statement line items
- Assumptions used in calculations match Assumptions tab

### Step 5: Cross-Statement Integrity Checks

After validating individual sheets, confirm the three statements are properly integrated:

| Check | Formula | Expected Result |
|-------|---------|-----------------|
| Balance Sheet Balance | Assets - Liabilities - Equity | = 0 |
| Cash Tie-Out | CF Ending Cash - BS Cash | = 0 |
| Net Income Link | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI - Dividends - BS Ending RE | = 0 (adjust for SBC/other items as needed) |

### Step 6: Final Review

Before considering the model complete:
- Toggle through all scenarios (if applicable) to verify checks pass in each case
- Review all #REF!, #DIV/0!, #VALUE!, and #NAME? errors and resolve or document
- Confirm all input cells have been populated (search for placeholder values)
- Verify units are consistent across all tabs
- Save a clean version before making any additional modifications

## Model Validation and Audit

This section consolidates all validation checks and audit procedures for completed templates.

### Core Linkages (Must Always Hold)

See [references/formulas.md](references/formulas.md) for all formula details.

| Check | Formula | Expected Result |
|-------|---------|-----------------|
| Balance Sheet Balance | Assets - Liabilities - Equity | = 0 |
| Cash Tie-Out | CF Ending Cash - BS Cash | = 0 |
| Cash Monthly vs Annual | Closing Cash (Monthly) - Closing Cash (Annual) | = 0 |
| Net Income Link | IS Net Income - CF Starting Net Income | = 0 |
| Retained Earnings | Prior RE + NI + SBC - Dividends - BS Ending RE | = 0 |
| Equity Financing | ΔCommon Stock/APIC (BS) - Equity Issuance (CFF) | = 0 |
| Year 0 Equity | Equity Raised (Year 0) - Beginning Equity Capital (Year 1) | = 0 |

### Sign Convention Reference

| Statement | Item | Sign Convention |
|-----------|------|-----------------|
| CFO | D&A, SBC | Positive (add-back) |
| CFO | ΔAR (increase) | Negative (use of cash) |
| CFO | ΔAP (increase) | Positive (source of cash) |
| CFI | CapEx | Negative |
| CFF | Debt issuance | Positive |
| CFF | Debt repayments | Negative |
| CFF | Dividends | Negative |

### Circular Reference Handling

Interest expense creates circularity: Interest → Net Income → Cash → Debt Balance → Interest

Enable iterative calculation in Excel: File → Options → Formulas → Enable iterative calculation. Set maximum iterations to 100, maximum change to 0.001. Add a circuit breaker toggle in Assumptions tab.

### Check Categories

**Section 1: Currency Consistency**
- Currency identified and documented in Assumptions
- All tabs use consistent currency symbol and scale
- Units row matches model currency

**Section 2: Balance Sheet Integrity**
- Assets = Liabilities + Equity (for each period)
- Formula: Assets - Liabilities - Equity (must = 0)

**Section 3: Cash Flow Integrity**
- Cash ties to BS (CF Ending Cash = BS Cash)
- Cash Monthly vs Annual: Closing Cash (Monthly) = Closing Cash (Annual)
- NI ties to IS (CF Net Income = IS Net Income)
- D&A ties to schedule
- SBC ties to IS
- ΔAR, ΔInventory, ΔAP tie to WC schedule
- CapEx ties to DA schedule

**Section 4: Retained Earnings**
- RE roll-forward check: Prior RE + NI + SBC - Dividends = Ending RE
- Show component breakdown for debugging

**Section 5: Working Capital**
- AR, Inventory, AP tie to BS
- DSO, DIO, DPO reasonability checks (flag if outside normal ranges)

**Section 6: Debt Schedule**
- Total Debt ties to BS (Current + LT Debt)
- Interest calculation ties to IS

**Section 6b: Equity Financing**
- Equity issuance proceeds tie to BS Common Stock/APIC increase
- Cash increase from equity = Equity account increase (must balance)
- Equity Raise Tie-Out: ΔCommon Stock/APIC (BS) = Equity Issuance (CFF) (must = 0)
- Year 0 Equity Tie-Out: Equity Raised (Year 0) = Beginning Equity Capital (Year 1)

**Section 6c: NOL Schedule**
- Beginning NOL (Year 1 / Formation) = 0 (new business starts with zero NOL)
- NOL increases only when EBT < 0 (losses must be realized to generate NOL)
- DTA ties to BS (NOL Schedule DTA = BS Deferred Tax Asset)
- NOL utilization ≤ 80% of EBT (post-2017 federal limitation)
- NOL balance is non-negative (cannot utilize more than available)
- NOL generated only when EBT < 0
- Tax expense = 0 when taxable income ≤ 0

**Section 7: Scenario Hierarchy**
- Absolute metrics: Upside > Base > Downside (NI, EBITDA, FCF)
- Margins: Upside > Base > Downside (GM%, EBITDA%, NI%)
- Credit metrics: Upside < Base < Downside for leverage (inverted)

**Section 8: Formula Integrity**
- COGS, S&M, G&A, R&D, SBC driven by % of Revenue (no hardcodes)
- Consistent formulas across projection years
- No #REF!, #DIV/0!, #VALUE! errors

**Section 9: Credit Metric Thresholds**
- Flag metrics as Green/Yellow/Red based on covenant thresholds
- Summary of any red flags

### Master Check Formula

Aggregate all section statuses into a single master check:
- If all sections pass → "✓ ALL CHECKS PASS"
- If any section fails → "✗ ERRORS DETECTED - REVIEW BELOW"

### Quick Debug Workflow

When Master Status shows errors:
1. Scroll to find red-highlighted sections
2. Identify which check category has failures
3. Navigate to source tab to investigate
4. Fix the underlying issue
5. Return to Checks tab to verify resolution