# Formula Sheet

Canonical formulas for this model. All figures must tie to XBRL facts or cited filing pages.

- Revenue growth = (Rev_t / Rev_{t-1}) - 1
- Gross margin = GrossProfit / Revenues
- Operating margin = OperatingIncomeLoss / Revenues
- FCF = OperatingCashFlow - CapEx

# Institutional Defaults

- Lookback: per skill `temporal_scope`.
- Currency: reporting currency from the first XBRL fact `unit` (no conversion at v1.0).
- XBRL: `is_primary = true` default; `?include_all_sources=true` only for audit/reconciliation.
- Dedup: API-side; do not re-implement client-side.

# xlsx-financials — Methodology Detail

Extracted from SKILL.md for progressive disclosure (US5).

## Retrieval Strategy

1. **Fetch statement structure**: call `get_statement_structure(accession_number)` to retrieve the hierarchical concept tree from `gold.xbrl_presentation` (~8M rows) with `order_in_parent` and calculation arc weights .
2. **Fetch rendered statement**: call `get_statement(accession_number, type=<type>, format=<full|markdown|structured>)` for the period-column-formatted financial data.
3. **Structure for Excel**: map the hierarchical concept tree to Excel rows with proper indentation levels, parent-child grouping, and subtotal rows.
4. **Build workbook**: write a Python script using `openpyxl` (following Anthropic FSI xlsx-author conventions) and execute via Bash.

## Protocol

1. **Receive request from parent skill**: parent skill (dcf, comps, 3-statement, recent-quarter, earnings-preview) invokes this skill with `ticker`, `statement_type`, and `fiscal_year`.
2. **Fetch statement structure** via `get_statement_structure` — get the hierarchical concept tree with indentation levels.
3. **Fetch statement data** via `get_statement` — get the period-column financial values.
4. **Apply formatting rules** per `style.md`:
 - Currency: $#,##0.0 with B/M/K auto-detection
 - Percentages: 0.0%
 - Frozen header row (row 1)
 - Parent concepts: **bold**
 - Child concepts: indented 2 spaces per level
 - Subtotal rows: bold with top border
5. **Inject calculation arcs**: when `include_calculations=true`, add a hidden "Validation" sheet with the calculation arc cross-check: parent expected value vs sum of weighted children .
6. **Write the `.xlsx` workbook** (see `contracts/office-tooling.md`):
   a. Write a self-contained `.py` script (`_build_{ticker}_{type}.py`) using `openpyxl` that creates a Workbook with sheets Cover, IS, BS, CF, Ratios, Segments, ArcCheck, NamedRanges; applies formatting per `style.md`; injects the calculation-arc cross-validation per `## Validation Gates`; and follows Anthropic FSI conventions (blue=input, black=formula, green=cross-sheet link).
   b. Execute: `Bash: python3 _build_{ticker}_{type}.py`.
   c. Verify: `Bash: ls -la {ticker}/{YYYY-MM-DD_HHMM}_statement-{type}.xlsx` — confirm the file exists and size > 0.
   d. Write the companion `.md` summary with validation results + key citations.
   e. If step (b) fails with `ModuleNotFoundError: No module named 'openpyxl'`: output the `.md` summary with full data tables, annotate `data_availability: degraded` and `openpyxl_missing: true`, and report `pip install openpyxl`.

## xlsx-author Conventions

Following Anthropic FSI `xlsx-author` conventions:

- **Blue font** = hardcoded input values (statement data fetched from API)
- **Black font** = formulas (subtotals, growth rates, validation checks)
- **Green font** = links/references to other sheets
- **Named ranges** for key metrics (Revenue, NetIncome, TotalAssets) to enable cross-sheet references
- **Checks tab**: include a "Checks" sheet with TRUE/FALSE validation:
 - BS balance: Assets = Liabilities + Equity
 - Subtotal tie-out: parent = sum of children per calculation arcs
 - Period consistency: all periods have matching concept coverage

# xlsx-financials — Analyst Mode Definitions

Derived from the skill's own methodology structure (scripts/mode_backfill.py, spec 046 M1).

### Mode: triggers

**Objective**: Triggers analysis per the skill's methodology (see SKILL.md sections).

### Mode: defaults

**Objective**: Defaults analysis per the skill's methodology (see SKILL.md sections).

### Mode: methodology

**Objective**: Methodology analysis per the skill's methodology (see SKILL.md sections).

### Mode: retrieval-scope

**Objective**: Retrieval Scope analysis per the skill's methodology (see SKILL.md sections).

### Mode: retrieval-strategy

**Objective**: Retrieval Strategy analysis per the skill's methodology (see SKILL.md sections).

# xlsx-financials — Output Structure (full template)

Extracted from SKILL.md for progressive disclosure (US5). The skill body keeps a compact summary under `## Output Structure`.

The primary deliverable is an `.xlsx` workbook with proper number formatting, frozen headers, and calculation arc cross-validation. A companion `.md` summary captures validation results and key citations. Output conventions follow the Anthropic FSI xlsx-author standard (blue font = hardcoded inputs, black font = formulas).

### Single-Ticker
```
{ticker}/{YYYY-MM-DD_HHMM}_statement-{type}.xlsx
```
Example: `LLY/2026-06-03_1430_statement-income.xlsx`

### Multi-Ticker
```
_cross/{slug}_{YYYY-MM-DD_HHMM}_statement-{type}.xlsx
```
Example: `_cross/LLY-vs-peers_2026-06-03_1430_statement-income.xlsx`

**Citations & memory**: follow `contracts/citation-and-memory.md` — ≥1 citation per 200 words; every material fact, table row, and metric is immediately followed by its inline clickable `https://agentii.ai/v/{ticker}/{citation_id}/{N}` link; a bottom **Citations** section provides a non-duplicative roll-up index; the closing TUI reply includes a compact **Key Citations** list (headline 5–10 facts) of clickable `/v/` URLs; and append the run to `agentii.md` per `contracts/agentii-md-schema.md`.

# xlsx-financials — Tool Fallbacks

Extracted from SKILL.md for progressive disclosure (US5).

| Tool | Failure Mode | Fallback Action | Coverage Annotation |
|------|-------------|-----------------|---------------------|
| `get_statement` | Endpoint unavailable | Use `search_xbrl_facts` with individual concept queries; structure manually from `get_statement_structure` tree | "Statement endpoint unavailable; built from individual XBRL facts" |
| `get_statement_structure` | Timeout | Use `list_xbrl_concepts` for concept discovery; flat structure without hierarchy | "Statement tree unavailable; flat concept list used" |
| `Bash` / `openpyxl` | `python3 -c "import openpyxl"` fails (exit ≠ 0) | TRUE LAST RESORT only: output the `.md` summary with full data tables + `data_availability: degraded` annotation; report the exact `pip install openpyxl` command | "openpyxl unavailable; markdown summary provided. Install openpyxl: pip install openpyxl" |

# Validation Checklist

- [ ] Every input figure cites a filing page or XBRL accession.
- [ ] Recalc/audit returns no hardcoded-over-formula cells.
- [ ] Statement identities balance within rounding.
- [ ] Period labels match `get_company_fiscal_calendar`.
- [ ] Coverage gaps are surfaced, not silently dropped.

# Financial Model Construction Standards -- Institutional Methodology

## Protocol

### Model Formatting Taxonomy
All cells in a professional financial model must be classified into one of four color conventions at a glance. Input cells (hard-coded values, assumption drivers) are formatted in blue font to signal "changeable by user." Formula cells (calculations, drivers computed from inputs) carry black font to signal "do not overwrite." Cross-reference cells that pull from other worksheets or workbooks use green font. Validation and balancing error checks use red font. This color taxonomy serves as an immediate audit trail: any cell's role in the model is identifiable without tracing precedents or inspecting the formula bar.

Number formatting follows institutional conventions: thousands separators enabled, one decimal place for general values, accounting format for currency with left-justified dollar signs and right-justified values, percentage format with one decimal, negative values in parentheses (or red text with parentheses for emphasis). Custom format codes enable display transformations without altering underlying values: trailing multipliers (0.0"x" for valuation multiples), appended text labels (0 "years"), and conditional formatting for binary states.

### Error Detection and Prevention
A systematic error-checking regimen proceeds from formula-level auditing to balance-level integrity. At the formula level: F2 exposes all precedent references within a cell using color-coded range highlighting; Ctrl+[ traces to the first precedent (repeated keystrokes navigate the full dependency chain); Ctrl+] traces to all dependents of the selected cell. The Formula Auditing ribbon group (Alt+M+A+A for trace precedents) provides visual tracer arrows for complex dependency trees.

At the statement level, the Go To Special dialog (F5 > Special, or Ctrl+G > Special) isolates specific cell types: select Constants with Numbers sub-filter to highlight all hard-coded values in a worksheet for audit review; select Formulas with Errors sub-filter to locate every broken calculation in one operation. This is the standard institutional workflow for isolating hardcoded values that should be formula-driven -- select all constant numeric cells, then apply a contrasting fill color as a visual flag requiring remediation.

Common error states and their root causes: #DIV/0! (division by zero or empty denominator), #REF! (reference to a deleted cell or range, typically from row/column deletion), #NUM! (numerical overflow or impossible computation such as exceedingly large exponents), #NAME? (unrecognized text in formula context, typically a misspelled function name or undefined named range), #VALUE! (incompatible argument types such as arithmetic on text strings), and ####### (display constraint where column width is insufficient to render the numeric value). Each error type maps to a specific diagnostic path rather than a generic "fix the formula" approach.

### Keyboard Efficiency for Model Construction
Financial model construction speed depends on minimizing hand-to-mouse transitions. Core navigation shortcuts: Ctrl+Shift+Right Arrow selects from the active cell to the last contiguous populated cell in the row; Ctrl+Shift+Down Arrow selects the column equivalent; Ctrl+Page Down/Up cycles between worksheet tabs. Formatting shortcuts: Ctrl+1 opens the Format Cells dialog (universal entry point for number, alignment, font, border, fill, and protection formatting); Alt+H+O+H applies AutoFit Column Width to selected columns; Alt+H+O+I applies AutoFit Row Height.

The distinction between Alt-key and Ctrl-key execution is fundamental. Alt-key sequences are sequential: press and release Alt, then H, then O, then H -- each keypress navigates one level deeper in the ribbon hierarchy, and the ribbon visually displays key tips throughout. Ctrl-key combinations are simultaneous: the Ctrl key must be held while pressing the companion key (Ctrl+B for bold, Ctrl+1 for Format Cells). This mechanical difference means Alt commands access any ribbon function without memorization by reading visible key tips, while Ctrl commands require memorization of specific combinations but execute faster.

### Template Architecture and Structure
A professional model workbook employs modular worksheet architecture rather than a single monolithic sheet. The standard template includes dedicated tabs for each financial statement (Income Statement, Balance Sheet, Cash Flow Statement), supporting schedules (PP&E Schedule, Working Capital Schedules, Retained Earnings Schedule, Debt and Interest Schedules), a revenue build, and scenario/sensitivity outputs. Tab naming follows uppercase conventions with underscores (INCOME_STATEMENT, BALANCE_SHEET, CASH_FLOW) for clarity across the workbook.

Column headers organize time periods into Historicals (actual reported data) and Forecasts (model projections), with merged cells spanning the two groups to create a visual boundary between past and future. Each period header includes both the fiscal year-end date and the period label. Dynamic headers use the TEXT function and concatenation to build labels that update automatically when input assumptions change (e.g., "Income Statement for [Company Name]" or "Share price as of [Date]"). This eliminates the need to manually update headers across multiple sheets when switching between companies or scenarios.

The model must function with calculation mode set to Manual to prevent circular reference loops from degrading performance during construction. The CALCULATE indicator in the status bar signals that the workbook requires manual recalculation (F9). Iterative calculation is disabled; circular references are managed through explicit breaker toggles rather than Excel's built-in iteration engine, which produces non-deterministic convergence paths.

## Key Formulas and Techniques

### Custom Number Formatting Codes
- Multiple display: `0.0"x"` renders the value 5 as "5.0x"
- Text append: `0 "years"` renders the value 3 as "3 years"
- Conditional boolean: `"True";;"False"` renders 1 as "True" and 0 as "False" using the three-section format (positive;negative;zero)
- Thousands with parentheses for negatives: `#,##0.0_);(#,##0.0)` applies accounting-style negative display with proper alignment
- Date formatting via TEXT function: `=TEXT(date_cell, "mm/dd/yy")` converts a serial date to a formatted text string for use in concatenated headers

### Dynamic Header Construction
- Basic concatenation: `="Income Statement for "&company_name_cell`
- Date-embedded header: `="Share price as of "&TEXT(date_cell, "mm/dd/yy")`
- Alt+Enter within a formula inserts line breaks for multi-line cell content without merging or text wrapping

### Formula Auditing Shortcuts
- F2: Enter cell edit mode with color-coded precedent highlighting
- Ctrl+[: Jump to first precedent cell; repeat to trace chain
- Ctrl+]: Jump to first dependent cell; repeat to trace forward
- Alt+M+A+A: Trace Precedents (draws visual arrows)
- Alt+M+A+D: Trace Dependents
- Alt+M+A+A: Remove All Arrows

### Selection and Navigation
- Ctrl+Shift+Arrow: Select from active cell to last contiguous populated cell in arrow direction
- Ctrl+Page Down: Move to next worksheet tab
- Ctrl+Page Up: Move to previous worksheet tab
- F5 > Special: Open Go To Special dialog for cell-type-based selection

## Practitioner Standards

### Model Integrity Verification
Before any model is circulated, three verification passes are executed. First, a Go To Special scan for Constants (Numbers) highlights every hard-coded value; each must be justified as either an explicit assumption input or a deliberate override. Second, formula consistency checks across rows verify that every cell in a projection row carries the same formula logic as its neighbors; Excel's built-in inconsistent formula detection flags deviations automatically when background error checking is enabled. Third, a balance check (`=ROUND(Total Assets - Total Liabilities - Total Equity, 3)`) must return zero in every period.

### Data Entry Protocol
All data entry proceeds via keyboard exclusively. Arrow keys navigate between cells after data entry (Enter moves down, Tab moves right). This keyboard-only workflow achieves roughly 3x the speed of mouse-dependent data entry in institutional settings. Cell text that appears truncated in the display (due to column width limitations) is an immediate formatting issue; AutoFit Column Width resolves display without altering underlying data.

### Model Security and Distribution
Workbook protection follows a layered approach. Individual worksheets are protected (Review > Protect Sheet) to prevent accidental formula overwrites while leaving input cells unlocked. Workbook structure protection prevents sheet addition, deletion, or renaming. File-level encryption with password protects the entire workbook for distribution. For final deliverables, the Mark as Final designation sets read-only status and suppresses editing prompts.

### File Management
Standard workbook settings: one sheet per new workbook (to avoid blank-sheet clutter), body font at 11pt, normal view. Recent workbooks display set to 25; quick access pinned to 4. The formula bar, headings, and gridlines remain visible. Page layout is configured only for printing, with scaling adjustments applied per-sheet rather than globally. External workbook links are tracked through the Data > Edit Links interface to prevent broken references when distributing models.