carta-consolidating-pnl

What this skill does

[PATTERN carta-writing-style v0.0.2]
[PATTERN etiquette v0.0.6]
[PATTERN text v0.0.8]
[PATTERN tables v0.0.12]
[PATTERN carta-watermark v0.0.10]
[PATTERN menus-and-flows v0.0.7]
[PATTERN base v0.1.0]

# Consolidating P&L (detail + executive summary)

Generates a firm-wide consolidating Income Statement for a single month, as
**two linked tabs**:

1. A **detail tab** (`P&L - <FIRM-SHORT> <MMM-YY>`) matching the
   "P&L- with comments" format, with Month and YTD blocks of Actual /
   Budget / Variance / %.
2. A **Summary P&L** tab at sheet position 0 — a one-page executive
   summary that rolls the detail up into a small set of category lines,
   formula-linked back to the detail.

The data is pulled live from Carta's DWH via the Carta MCP connector —
nothing is embedded in the skill.

This skill runs primarily inside the **Claude for Excel** add-in. The
audience is an accountant working in their workbook, not an engineer
running CLI commands.

## UX Rules

This skill ships as a standalone Claude for Excel skill — the global `carta-skill-ux-rules` SessionStart hook covers currency formatting, status vocabulary, no-UUID display, and plain-English speech. Skill-specific deviations:

- **Citation links** to Excel ranges use the citation form: `[B1:Q72](<citation:P&L - Acme Mar-26!B1:Q72>)`.
- **No environment URLs.** This skill builds Excel output, not Carta web links — the BASE_URL rule from the global hook does not apply.

## Environment detection (Claude for Excel)

This skill does **not** call `carta auth-status` — that command isn't
available inside the Excel add-in. Instead, the active Carta environment
is detected at Gate 0 from the connected MCP server's prefix
(`mcp__claude_ai_Carta_<Env>__fetch`).

## When to use

Trigger on any request shaped like:

- "(Consolidating) P&L for `<FIRM>` for `<MONTH>`" — with or without "with executive summary"
- "P&L for all entities of `<FIRM>`" / "firm-wide income statement"
- Any ask to replicate the "P&L- with comments" + Summary P&L pair for another firm/period

Do **NOT** use this skill for:

- **Single-entity P&L** — use the single-entity P&L workflow (this skill always rolls up across every entity)
- **Balance Sheet** requests — use `carta-consolidating-balance-sheet`
- **Multi-period trend** analysis or **per-entity side-by-side columns** — clarify before building; this skill produces ONE consolidated Actual column per period

## Inputs to collect

Before running, confirm with the user:

1. **Firm name** — must match a firm reachable from the active Carta MCP
   context (resolved fuzzily). Example: `Acme Ventures`.
2. **Month** — format as `YYYY-MM` (e.g. `2026-03` for March 2026). Used
   for both the month block and the YTD-through-month block.

If the user gave both in the request, proceed without re-asking.

---

## Execution discipline

Execute all gates silently. Do not narrate tool calls, intermediate results, or status updates. Only speak at explicit decision points: Gate 4 (pre-build review, build chooser, and budget source), Gate 4b (if budget source requires a file or tab), Gate 5 (output destination if workbook is non-empty), and Gate 9 (post-action menu).

---

## Entry mode — fresh session vs. chained skill

Before Gate 0, check whether these context variables are already set from an earlier skill call in the same session (e.g. chained from `carta-consolidating-balance-sheet`):

- `<SERVER>` — connected Carta MCP server prefix
- `<FIRM_NAME>` and `<FIRM_UUID>` — the resolved firm

**If both are in context:** skip Gates 0 and 1 entirely. Proceed from Gate 2 (pull period blocks) using the firm already in context and the month from the user's prompt.

**If either is missing** (fresh session or cold invocation): run Gates 0 and 1 in order.

Do not ask "which firm?" when it is already established from the skill the user just ran.

---

## Gate 0: Identify the Carta MCP environment

1. Call `refresh_mcp_connectors`. Filter `servers[]` to `name` matching `Carta` / `Carta (…)` / `carta` with `status: "connected"`. Drop `failed`.
2. For each connected, probe both prefix forms in parallel: `mcp__claude_ai_Carta__welcome` and `mcp__carta__welcome`. First success = `<SERVER>`.
3. **Don't call any other `mcp__<SERVER>__*` tool before `welcome`** — every command is gated.

If none connected, list `failed` connectors and stop. If multiple, default to `Carta` (production).

---

## Gate 1: Resolve firm

1. `fetch(command="contexts:list", params={"firm_name": "<FIRM>"})`. Multiple matches → `AskUserQuestion`. Wait for confirmation.
2. `set_context(firm_id=<uuid>)`. Prefer granular tools when exposed.

**DWH param-name traps:** `dwh:execute:query` takes `sql:` not `query:`. `dwh:get:table_schema` takes `table_name:` not `table:`. `format` accepts `"ndjson"` / `"markdown"`, not `"csv"`.

**Do NOT call `fa:list:entities`** — firm-wide consolidation aggregates via SQL.

---

## Gate 2: Pull the two period blocks

The schema and sign conventions for the Carta DWH journal-entries
table are documented in `references/schema.md`. Load that file now
and apply its rules.

Compute the period boundaries:

- `month_start` = first day of the month
- `month_end` = last day of the month
- `ytd_start` = `<YYYY>-01-01`

**Single query, both periods at once, summed across all entities under the
firm:**

```sql
SELECT
  ACCOUNT_TYPE,
  ACCOUNT_NAME,
  SUM(CASE WHEN EFFECTIVE_DATE BETWEEN '<month_start>' AND '<month_end>' THEN AMOUNT ELSE 0 END) AS MONTH_AMT,
  SUM(CASE WHEN EFFECTIVE_DATE BETWEEN '<ytd_start>'   AND '<month_end>' THEN AMOUNT ELSE 0 END) AS YTD_AMT
FROM <journal_entries_table>
WHERE FIRM_ID = '<firm_uuid>'
  AND ACCOUNT_TYPE >= '4000'
  AND EFFECTIVE_DATE BETWEEN '<ytd_start>' AND '<month_end>'
GROUP BY 1, 2
HAVING SUM(CASE WHEN EFFECTIVE_DATE BETWEEN '<ytd_start>' AND '<month_end>' THEN AMOUNT ELSE 0 END) <> 0
ORDER BY 1, 2
```

### DWH result formatting

Queries > 50 rows: request `format: "ndjson"`, bucket into a blob. Don't paste large results — triggers `context_snip`. Use `"markdown"` only for ≤50-row previews.

Run via `fetch(command="dwh:execute:query", params={"sql": "..."})`.
SELECT-only.

**Critical**: no `FUND_NAME` filter. The GROUP BY on `ACCOUNT_TYPE,
ACCOUNT_NAME` automatically rolls up the same COA account across every
entity into a single row.

**The `HAVING ... <> 0` clause filters out accounts with no YTD actuals.**
That's correct for this stage — but **do not** treat that filtered set as
the final row list for the workbook. If a budget is loaded in Gate 4b,
accounts with budget but zero actuals must still appear as rows (with `-`
or `0` in the Actual columns). The row-set merge happens at the start of
Gate 6 — see "Row set: union of actuals + budget" there.

**Done when:** the period dataset is loaded — Month + YTD amounts for every
P&L account with non-zero YTD activity, aggregated firm-wide.

---

## Gate 2.5: Tag-category discovery (silent probe)

**Always run this gate** — even if the user hasn't asked for tag-view. The result determines whether the Gate 4 build chooser shows the "tag-view" option or hides it. **Silent — no user-facing output.**

Tag-view layout (when chosen at Gate 4) shows **all firm tag categories side by side** under each period band — no "which dimension?" picker. This probe's job is to discover whether the firm has a tag taxonomy in `REPORTING_TAGS_JSON` (or falls back to the flat `REPORTING_TAGS` column).

### Probe 1 — Detect the JSON-vs-flat path

```sql
SELECT
  COUNT_IF(REPORTING_TAGS_JSON IS NOT NULL) AS json_rows,
  COUNT_IF(REPORTING_TAGS IS NOT NULL)      AS flat_rows
FROM <journal_entries_table>
WHERE FIRM_ID = '<firm_uuid>'
  AND EFFECTIVE_DATE BETWEEN '<ytd_start>' AND '<month_end>'
```

- `json_rows > 0` → **JSON path**. Skip Probe 2 — go directly to Probe 3 (JSON path). Probe 3 returns both category names and cardinality in one query, making a separate category-discovery query redundant.
- `json_rows == 0 AND flat_rows > 0` → **flat path**. Set `<T