samledger67-dotcom

---
name: workflow-chain
description: >
  Chain multiple pipeline scripts into a single sequential or parallel workflow.
  Acts as a "playlist" for PrecisionLedger pipeline scripts. Use when a task
  requires multiple pipelines in sequence (e.g., "full close + analysis package
  for Paulson") or when building reusable workflow templates for recurring
  multi-step client work. Reads the pipeline manifest and client SOPs to
  auto-detect which pipelines apply. Can run ad-hoc chains or saved templates.
  NOT for: single-pipeline tasks (use the specific skill), non-pipeline work
  (email, web search, content creation), or tasks that don't involve QBO data
  or pipeline scripts.
license: MIT
metadata:
  openclaw:
    emoji: "⛓️"
    negative_boundaries:
      - Do NOT use for single-pipeline tasks; use the specific pipeline skill instead
      - Do NOT use for non-pipeline work like email, web search, or content creation
      - Do NOT use when the task does not involve QBO data, pipeline scripts, or multi-step orchestration
---

# Workflow Chain — Multi-Pipeline Orchestrator

Chain multiple PrecisionLedger pipeline scripts into a single coordinated workflow.
Think of it as a "playlist" for pipeline scripts — run them in sequence or parallel,
with data flowing between steps.

---

## Trigger

Use this skill when:
- User says "run full close and analysis", "complete financial package", "run all pipelines for [client]"
- A task clearly requires 2+ pipeline scripts in sequence
- User says "chain", "workflow", "run everything", "full suite"
- Building a reusable workflow template for a client
- Need to coordinate parallel pipeline execution with a merge step

Do NOT use for:
- Single pipeline tasks → use the specific skill (pl-quick-compare, month-end-close, etc.)
- Non-pipeline work → email, web search, content creation
- Tasks without QBO data or pipeline scripts

---

## Architecture

### Pipeline Registry

All available pipelines live in `scripts/pipelines/` with a manifest at `scripts/pipelines/manifest.json`.

Current production pipelines (19 scripts, all have argparse):

| Pipeline | Script | Typical Order |
|----------|--------|---------------|
| pl-quick-compare | pl-quick-compare.py | 1 (income statement first) |
| pl-deep-analysis | pl-deep-analysis.py | 2 (after quick compare flags) |
| bs-quick-compare | bs-quick-compare.py | 3 (balance sheet) |
| bs-deep-analysis | bs-deep-analysis.py | 4 (after BS flags) |
| scf-quick-compare | scf-quick-compare.py | 5 (cash flow) |
| scf-deep-analysis | scf-deep-analysis.py | 6 (after SCF flags) |
| financial-ratios | financial-ratios.py | 7 (cross-statement ratios) |
| bank-reconciliation | bank-reconciliation.py | parallel with above |
| payroll-reconciliation | payroll-reconciliation.py | parallel with above |
| ar-collections | ar-collections.py | parallel (if client has AR) |
| budget-builder | budget-builder.py | ad-hoc |
| cash-flow-forecast | cash-flow-forecast.py | after close |
| client-dashboard | client-dashboard.py | final (needs all data) |
| doc-ingestion | doc-ingestion.py | pre-close |
| document-ingestion | document-ingestion.py | pre-close |
| financial-package | financial-package.py | standalone TTM package |
| month-end-close | month-end-close.py | orchestrates close checklist |
| tax-package-prep | tax-package-prep.py | year-end only |
| vendor-compliance-1099 | vendor-compliance-1099.py | year-end only |

### Common Arguments (all pipelines share these)

```
--slug <client-slug>        QBO company identifier (required)
--start YYYY-MM-DD          Period start
--end YYYY-MM-DD            Period end
--out <directory>            Output directory (default: ~/Desktop)
--sandbox                   Use QBO sandbox
```

---

## Workflow Execution

### Step 1: Identify the Task

Parse the user's request to determine:
1. **Client** (slug) — from name/alias matching against `clients/*/sop.md`
2. **Period** — month, quarter, or year
3. **Scope** — which pipelines are needed

### Step 2: Build the Chain

Read the client's SOP to determine which pipelines apply:
- Does the client have AR? → include ar-collections
- Does the client have payroll? → include payroll-reconciliation
- Is this year-end? → include tax-package-prep, vendor-compliance-1099
- Is this a close? → include month-end-close as the anchor

### Step 3: Determine Execution Order

Pipelines have natural dependencies:

```
Layer 0 (Pre-Close — parallel):
  doc-ingestion
  bank-reconciliation
  payroll-reconciliation

Layer 1 (Close):
  month-end-close (reads outputs from Layer 0)

Layer 2 (Analysis — parallel):
  pl-quick-compare
  bs-quick-compare
  scf-quick-compare

Layer 3 (Deep Analysis — parallel, depends on Layer 2 flags):
  pl-deep-analysis (only if PL flags > 0)
  bs-deep-analysis (only if BS flags > 0)
  scf-deep-analysis (only if SCF flags > 0)

Layer 4 (Cross-Statement):
  financial-ratios
  cash-flow-forecast

Layer 5 (Delivery):
  client-dashboard
  financial-package
```

### Step 4: Execute

For each layer:
1. Run all pipelines in that layer (parallel where possible via sub-agents)
2. Check exit codes — if any pipeline fails, log the error and continue (don't block the chain)
3. Pass shared arguments (slug, dates, output dir) to each pipeline
4. Collect outputs (Excel files, JSON caches, manifests)

### Step 5: Report

After all layers complete, produce a summary:
- Which pipelines ran ✅
- Which failed ❌ (with error)
- Which were skipped ⏭️ (not applicable per SOP)
- Output file locations
- Total execution time

---

## Pre-Built Templates

### Template: Full Monthly Close
```
Layers: 0 → 1 → 2 → 3 → 4 → 5
Pipelines: bank-rec → close → PL/BS/SCF quick → deep (if flagged) → ratios → dashboard
Trigger: "full close for [client]", "complete close [month]"
```

### Template: Quick Analysis Package  
```
Layers: 2 → 4 → 5
Pipelines: PL/BS/SCF quick compare → ratios → dashboard
Trigger: "quick analysis for [client]", "variance package [month]"
```

### Template: Year-End Tax Package
```
Layers: 0 → 1 → 2 → tax-specific
Pipelines: bank-rec → close → PL → tax-package-prep → vendor-1099
Trigger: "tax package for [client]", "year-end prep [year]"
```

### Template: Deep Dive (Single Statement)
```
Layers: quick → deep
Pipelines: [statement]-quick-compare → [statement]-deep-analysis
Trigger: "deep dive P&L", "analyze balance sheet in detail"
```

### Template: Financial Package (TTM)
```
Layers: single
Pipelines: financial-package.py (self-contained TTM generator)
Trigger: "financial package", "TTM statements"
```

---

## Example Usage

- "Run the full monthly close and analysis package for Acme for March 2026."
- "Chain PL, BS, and SCF quick compare, then build the dashboard."
- "Create a reusable year-end tax workflow for this client."

## Execution Commands

```bash
# Common pattern for all pipelines
SCRIPTS=~/.openclaw/workspace/scripts/pipelines
SLUG="my-client"
MONTH_START="2026-03-01"
MONTH_END="2026-03-31"
OUT=~/Desktop/close-$SLUG-$(date +%Y%m)

# Layer 0 (parallel)
python3 $SCRIPTS/bank-reconciliation.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT &
python3 $SCRIPTS/payroll-reconciliation.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT &
wait

# Layer 1
python3 $SCRIPTS/month-end-close.py --slug $SLUG --month 0:7 --out $OUT

# Layer 2 (parallel)
python3 $SCRIPTS/pl-quick-compare.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT &
python3 $SCRIPTS/bs-quick-compare.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT &
python3 $SCRIPTS/scf-quick-compare.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT &
wait

# Layer 3 (conditional)
# Only run deep analysis if quick compare flagged material items
python3 $SCRIPTS/pl-deep-analysis.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT
python3 $SCRIPTS/bs-deep-analysis.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT
python3 $SCRIPTS/scf-deep-analysis.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT

# Layer 4
python3 $SCRIPTS/financial-ratios.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT
python3 $SCRIPTS/cash-flow-forecast.py --slug $SLUG --out $OUT

# Layer 5
python3 $SCRIPTS/client-dashboard.py --slug $SLUG --start $MONTH_START --end $MONTH_END --out $OUT
```

---

## Client SOP Integration

Before running any chain, read `clients/{slug}/sop.md` to check:

1. **Which reports are relevant** — not every client needs AR aging or payroll rec
2. **Special instructions** — some clients have non-standard periods or reporting requirements
3. **Key financial characteristics** — informs which deep dives to prioritize
4. **Schedule** — when the client expects deliverables

The SOP is the authority. If the SOP says "no AR aging" (like SB Paulson — POS collection), skip ar-collections even in a full close chain.

---

## Error Handling

- **Pipeline fails:** Log error, mark as ❌, continue chain. Don't block subsequent layers unless the failed pipeline is a hard dependency.
- **QBO token expired:** Run `node integrations/qbo-client/bin/qbo connect <slug>` to refresh. Auto-detected by 401 response.
- **Missing data:** Some pipelines produce empty results for new clients. That's OK — the dashboard handles nulls gracefully.
- **Timeout:** Each pipeline has a 5-minute max. If exceeded, kill and mark as timed out.

---

## Output Convention

All chain outputs go to a single directory:
```
~/Desktop/close-{slug}-{YYYYMM}/
├── PLCompare_{slug}_*.xlsx
├── BSCompare_{slug}_*.xlsx
├── SCFCompare_{slug}_*.xlsx
├── PLDeep_{slug}_*.xlsx
├── BSDeep_{slug}_*.xlsx
├── SCFDeep_{slug}_*.xlsx
├── FinancialRatios_{slug}_*.xlsx
├── BankRec_{slug}_*.xlsx
├── PayrollRec_{slug}_*.xlsx
├── ClientDashboard_{slug}_*.xlsx
├── CashFlow_{slug}_*.xlsx
├── chain-summary.json          ← workflow metadata
└── chain-log.txt               ← execution log
```

---
name: pay-with-any-token
description: >
  Pay HTTP 402 payment challenges using tokens via the Tempo CLI and Uniswap
  Trading API. Use when the user encounters a 402 Payment Required response,
  needs to fulfill a machine payment, mentions "MPP", "Tempo payment", "pay for
  API access", "HTTP 402", "x402", "machine payment protocol",
  "pay-with-any-token", "use tempo", "tempo request", or "tempo wallet".
allowed-tools: Read, Glob, Grep, Bash(curl:*), Bash(jq:*), Bash(cast:*), Bash(tempo:*), Bash(*/.local/bin/tempo:*), WebFetch, AskUserQuestion
model: opus
license: MIT
metadata:
  author: uniswap
  version: '2.0.0'
---

# Pay With Tokens

Use the **Tempo CLI** to call paid APIs and handle 402 challenges automatically.
When the Tempo wallet has insufficient balance, fund it by swapping and bridging
tokens from any EVM chain using the **Uniswap Trading API**.

## Tempo CLI Setup

Run these commands in order. Do not skip steps.

**Step 1 — Install:**

```bash
mkdir -p "$HOME/.local/bin" \
  && curl -fsSL https://tempo.xyz/install -o /tmp/tempo_install.sh \
  && TEMPO_BIN_DIR="$HOME/.local/bin" bash /tmp/tempo_install.sh
```

**Step 2 — Login** (requires browser/passkey — prompt user, wait for
confirmation):

```bash
"$HOME/.local/bin/tempo" wallet login
```

> When run by agents, use a long command timeout (at least 16 minutes).

**Step 3 — Confirm readiness:**

```bash
"$HOME/.local/bin/tempo" wallet -t whoami
```

> **Rules:** Do not use `sudo`. Use full absolute paths (`$HOME/.local/bin/tempo`)
> — do not rely on `export PATH`. If `$HOME` does not expand, use the literal
> absolute path.

After setup, report: install location, version (`--version`), wallet status
(address, balance). If balance is 0, direct user to `tempo wallet fund`.

## Using Tempo Services

```bash
# Discover services
"$HOME/.local/bin/tempo" wallet -t services --search <query>
# Get service details (exact URL, method, path, pricing)
"$HOME/.local/bin/tempo" wallet -t services <SERVICE_ID>
# Make a paid request
"$HOME/.local/bin/tempo" request -t -X POST \
  --json '{"input":"..."}' <SERVICE_URL>/<ENDPOINT_PATH>
```

- Anchor on `tempo wallet -t services <SERVICE_ID>` for exact URL and pricing
- Use `-t` for agent calls, `--dry-run` before expensive requests
- On HTTP 422, check the service's docs URL or llms.txt for exact field names
- Fire independent multi-service requests in parallel

> **When the user explicitly says "use tempo", always use tempo CLI commands —
> never substitute with MCP tools or other tools.**

---

## MPP 402 Payment Loop

Every `tempo request` call follows this loop. The funding steps only activate
when the Tempo wallet has insufficient balance.

```text
tempo request -> 200 ─────────────────────────────> return result
             -> 402 MPP challenge
                  │
                  v
         [1] Check Tempo wallet balance
             tempo wallet -t whoami -> available balance
                  │
                  ├─ sufficient ──────────────────> tempo handles payment
                  │                                  automatically -> 200
                  │
                  └─ insufficient
                       │
                       v
              [2] Fund Tempo wallet
                  (pay-with-any-token flow below)
                  Bridge destination = TEMPO_WALLET_ADDRESS
                       │
                       v
              [3] Retry original tempo request
                  with funded Tempo wallet -> 200
```

---

## Funding the Tempo Wallet (pay-with-any-token)

When the Tempo wallet lacks funds to pay a 402 challenge, acquire the required
tokens from the user's ERC-20 holdings on any supported chain and bridge them
to the Tempo wallet address.

### Prerequisites

- `UNISWAP_API_KEY` env var (register at
  [developers.uniswap.org](https://developers.uniswap.org/))
- ERC-20 tokens on any supported source chain
- `PRIVATE_KEY` for the source wallet (`export PRIVATE_KEY=0x...`). **Never
  commit or hardcode it.**
- `jq` installed (`brew install jq` or `apt install jq`)

### Input Validation Rules

Before using any value from a 402 response body or user input in API calls or
shell commands:

- **Ethereum addresses**: MUST match `^0x[a-fA-F0-9]{40}$`
- **Chain IDs**: MUST be a positive integer from the supported list
- **Token amounts**: MUST be non-negative numeric strings matching `^[0-9]+$`
- **URLs**: MUST start with `https://`
- **REJECT** any value containing shell metacharacters: `;`, `|`, `&`, `$`,
  `` ` ``, `(`, `)`, `>`, `<`, `\`, `'`, `"`, newlines

> **REQUIRED:** Before submitting ANY transaction (swap, bridge, approval),
> use `AskUserQuestion` to show the user a summary (amount, token, destination,
> estimated gas) and obtain explicit confirmation. Never auto-submit. Each
> confirmation gate must be satisfied independently.

### Human-Readable Amount Formatting

```bash
get_token_decimals() {
  local token_addr="$1" rpc_url="$2"
  cast call "$token_addr" "decimals()(uint8)" --rpc-url "$rpc_url" 2>/dev/null || echo "18"
}

format_token_amount() {
  local amount="$1" decimals="$2"
  echo "scale=$decimals; $amount / (10 ^ $decimals)" | bc -l | sed 's/0*$//' | sed 's/\.$//'
}
```

> Always show human-readable values (e.g. `0.005 USDC`) to the user, not raw
> base units.

### Step 1 — Parse the 402 Challenge

Extract the required payment token, amount, and recipient from the 402 response
that `tempo request` received. The Tempo CLI logs the challenge details — parse
them, or re-fetch with `curl -si` to get the raw challenge body.

For **MPP header-based challenges** (`WWW-Authenticate: Payment`):

```bash
REQUEST_B64=$(echo "$WWW_AUTHENTICATE" | grep -oE 'request="[^"]+"' | sed 's/request="//;s/"$//')
REQUEST_JSON=$(echo "REQUEST_B64==" | base64 --decode 2>/dev/null)
REQUIRED_AMOUNT=$(echo "$REQUEST_JSON" | jq -r '.amount')
PAYMENT_TOKEN=$(echo "$REQUEST_JSON" | jq -r '.currency')
RECIPIENT=$(echo "$REQUEST_JSON" | jq -r '.recipient')
TEMPO_CHAIN_ID=$(echo "$REQUEST_JSON" | jq -r '.methodDetails.chainId')
```

For **JSON body challenges** (`payment_methods` array):

```bash
NUM_METHODS=$(echo "$CHALLENGE_BODY" | jq '.payment_methods | length')
PAYMENT_METHODS=$(echo "$CHALLENGE_BODY" | jq -c '.payment_methods')
RECIPIENT=$(echo "$CHALLENGE_BODY" | jq -r '.payment_methods[0].recipient')
TEMPO_CHAIN_ID=$(echo "$CHALLENGE_BODY" | jq -r '.payment_methods[0].chain_id')
```

If multiple payment methods are accepted, select the cheapest in Step 2.

> The Tempo mainnet chain ID is `4217`. Use as fallback if not in the challenge.

### Step 2 — Check Source Wallet Balances and Select Payment Method

> **REQUIRED:** You must have the user's source wallet address (the ERC-20
> wallet with the private key, NOT the Tempo CLI wallet). Use `AskUserQuestion`
> if not provided. Store as `WALLET_ADDRESS`.

Also capture the **Tempo wallet address** (the funding destination):

```bash
TEMPO_WALLET_ADDRESS=$("$HOME/.local/bin/tempo" wallet -t whoami | grep -oE '0x[a-fA-F0-9]{40}' | head -1)
```

Check ERC-20 balances on source chains:

```bash
# USDC on Base
cast call 0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913 \
  "balanceOf(address)(uint256)" "$WALLET_ADDRESS" \
  --rpc-url https://mainnet.base.org
```

**Select the cheapest payment method** if multiple are accepted. Priority:

1. Wallet holds USDC on Base (bridge only, minimal path)
2. Wallet holds ETH on Base or Ethereum (swap to USDC + bridge)
3. Any other liquid ERC-20 (swap + bridge)

```bash
REQUIRED_AMOUNT=$(echo "$PAYMENT_METHODS" | jq -r ".[$SELECTED_INDEX].amount")
PAYMENT_TOKEN=$(echo "$PAYMENT_METHODS" | jq -r ".[$SELECTED_INDEX].token")
```

### Step 3 — Plan the Payment Path

```text
Source token (Base/Ethereum)
  -> [Phase 4A: Uniswap Trading API swap] -> native USDC (bridge asset)
  -> [Phase 4B: bridge via Trading API]   -> USDC.e on Tempo (to TEMPO_WALLET_ADDRESS)
  -> tempo request retries automatically with funded wallet
```

> **Skip Phase 4A** if the source token is already USDC on the bridge chain.

### Phase 4A — Swap to USDC on Source Chain (if needed)

Swap the source token to USDC via the Uniswap Trading API (`EXACT_OUTPUT`).

> **Detailed steps:** Read
> [references/trading-api-flows.md](references/trading-api-flows.md#phase-4a--swap-on-source-chain)
> for full bash scripts: variable setup, approval check, quote, permit signing,
> and swap execution.

Key points:

- Base URL: `https://trade-api.gateway.uniswap.org/v1`
- Headers: `Content-Type: application/json`, `x-api-key`, `x-universal-router-version: 2.0`
- Flow: `check_approval` -> quote (`EXACT_OUTPUT`) -> sign `permitData` -> `/swap` -> broadcast
- Confirmation gates required before approval tx and before swap broadcast
- For native ETH: use WETH address as `TOKEN_IN`; `SWAP_VALUE` will be non-zero
- After swap, verify USDC balance before proceeding to Phase 4B

### Phase 4B — Bridge to Tempo Wallet

Bridge USDC from Base to the **Tempo CLI wallet address** using the Uniswap
Trading API (powered by Across Protocol).

> **CRITICAL:** The bridge recipient must be `TEMPO_WALLET_ADDRESS` (from
> `tempo wallet -t whoami`), NOT `WALLET_ADDRESS` (your source ERC-20 wallet).
> This funds the Tempo CLI wallet so `tempo request` can retry the payment.
>
> **Detailed steps:** Read
> [references/trading-api-flows.md](references/trading-api-flows.md#phase-4b--bridge-to-tempo)
> for full bash scripts: approval, bridge quote, execution, and arrival polling.

Key points:

- Route: USDC on Base -> USDC.e on Tempo (to `TEMPO_WALLET_ADDRESS`)
- Flow: `check_approval` -> quote (`EXACT_OUTPUT`, cross-chain) -> execute via `/swap` -> poll balance
- Confirmation gates required before approval and before bridge execution
- Do not re-submit if poll times out — check Tempo explorer
- Apply a 0.5% buffer to account for bridge fees

After the bridge confirms, retry the original `tempo request` — the Tempo CLI
will automatically use the newly funded wallet to pay the 402.

> **Balance buffer:** On Tempo, `balanceOf` may report more than is spendable.
> Apply a **2x buffer** when comparing to `REQUIRED_AMOUNT`. If short, swap
> additional tokens to top up.

---

## x402 Payment Flow

The x402 protocol uses a different mechanism than MPP — it is **not handled by
the Tempo CLI**. When `PROTOCOL` is `"x402"` (detected by checking
`has("x402Version")` in the 402 body), use this flow instead.

The x402 `"exact"` scheme uses EIP-3009 (`transferWithAuthorization`) to
authorize a one-time token transfer signed off-chain.

> **Detailed steps:** Read
> [references/credential-construction.md](references/credential-construction.md#phase-6x--x402-payment)
> for full code: prerequisite checks, nonce generation, EIP-3009 signing,
> X-PAYMENT payload construction, and retry.

Key points:

- Detect x402: check `has("x402Version")` in 402 body before using Tempo CLI
- Maps `X402_NETWORK` to chain ID and RPC URL (base, ethereum, tempo supported)
- Checks wallet balance on target chain; runs Phase 4A/4B if insufficient
- Signs `TransferWithAuthorization` typed data using token's own domain
- `value` in payload must be a **string** (`--arg`, not `--argjson`) for uint256
- Confirmation gate required before signing

| Protocol | Version | Handler        |
| -------- | ------- | -------------- |
| MPP      | v1      | Tempo CLI      |
| x402     | v1      | Manual (above) |

---

## Error Handling

| Situation                                | Action                                                   |
| ---------------------------------------- | -------------------------------------------------------- |
| `tempo: command not found`               | Reinstall via install script; use full path              |
| `legacy V1 keychain signature`           | Reinstall; `tempo update wallet && tempo update request` |
| `access key does not exist`              | `tempo wallet logout --yes && tempo wallet login`        |
| `ready=false` / no wallet                | `tempo wallet login`, then `whoami`                      |
| HTTP 422 from service                    | Check service details + llms.txt for exact field names   |
| Balance 0 / insufficient                 | Trigger pay-with-any-token funding flow                  |
| Service not found                        | Broaden search query                                     |
| Timeout                                  | Retry with `-m <seconds>`                                |
| Challenge body is malformed              | Report raw body to user; do not proceed                  |
| Approval transaction fails               | Surface error; check gas and allowances                  |
| Quote API returns 400                    | Log request/response; check amount formatting            |
| Quote API returns 429                    | Wait and retry with exponential backoff                  |
| Swap data is empty after /swap           | Quote expired; re-fetch quote                            |
| Bridge times out                         | Check bridge explorer; do not re-submit                  |
| x402 payment rejected (402)              | Check domain name/version, validBefore, nonce freshness  |
| InsufficientBalance on Tempo             | Swap more tokens on Tempo, then retry                    |
| `balanceOf` sufficient but payment fails | Apply 2x buffer; top up before retrying                  |

---

## Key Addresses and References

- **Tempo CLI**: `https://tempo.xyz` (install script: `https://tempo.xyz/install`)
- **Trading API**: `https://trade-api.gateway.uniswap.org/v1`
- **MPP docs**: `https://mpp.dev`
- **MPP services catalog**: `https://mpp.dev/api/services`
- **Tempo documentation**: `https://mainnet.docs.tempo.xyz`
- **Tempo chain ID**: `4217` (Tempo mainnet)
- **Tempo RPC**: `https://rpc.presto.tempo.xyz`
- **Tempo Block Explorer**: `https://explore.mainnet.tempo.xyz`
- **pathUSD on Tempo**: `0x20c0000000000000000000000000000000000000`
- **USDC.e on Tempo**: `0x20C000000000000000000000b9537d11c60E8b50`
- **Stablecoin DEX on Tempo**: `0xdec0000000000000000000000000000000000000`
- **Permit2 on Tempo**: `0x000000000022d473030f116ddee9f6b43ac78ba3`
- **Tempo payment SDK**: `mppx` (`npm install mppx viem`)
- **USDC on Base (8453)**: `0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913`
- **USDbC on Base (8453)**: `0xd9aAEc86B65D86f6A7B5B1b0c42FFA531710b6CA`
- **USDC on Ethereum (1)**: `0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48`
- **USDC-e on Arbitrum (42161)**: `0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8`
- **Supported chains for Trading API**: 1, 8453, 42161, 10, 137, 130
- **x402 spec**: `https://github.com/coinbase/x402`

## When NOT to Use

- Do NOT use for simple ETH transfers — this skill is for token swaps + 402 payment flows
- Do NOT use when no 402 challenge is present — only activate on HTTP 402 responses or explicit pay/fund requests
- Do NOT use if the user hasn't provided or approved wallet/private key access
- Do NOT use for reading blockchain state, checking balances only, or non-payment API interactions
- Do NOT use for fiat payment flows (credit card, ACH, Stripe) — crypto ERC-20 and x402 only
- Do NOT auto-submit any transaction without explicit user confirmation at each gate

## Related Skills

- [swap-integration](../swap-integration/SKILL.md) — Full Uniswap swap
  integration reference (Trading API, Universal Router, Permit2)

FILE:references/credential-construction.md
# Credential Construction

MPP and x402 credential building, signing, and submission flows.

## Table of Contents

- [Phase 6 — MPP Credential](#phase-6--mpp-credential)
- [Phase 6x — x402 Payment](#phase-6x--x402-payment)

## Phase 6 — MPP Credential

> **x402 path — STOP HERE.** If you arrived via the x402 detection gate in
> Phase 0, do not proceed with Phase 6. Phase 6 constructs an MPP credential;
> x402 payments use a different payload format handled in **Phase 6x** below.

With the required token in the wallet, fulfill the MPP challenge using the
**mppx** SDK, which handles the full 402 challenge -> credential -> retry cycle.

**Install:**

```bash
npm install mppx viem
```

### Charge intent — automatic mode

Polyfills `fetch` to intercept 402 responses automatically:

```typescript
import { Mppx, tempo } from 'mppx/client';
import { privateKeyToAccount } from 'viem/accounts';

const account = privateKeyToAccount(process.env.PRIVATE_KEY as `0xstring`);

Mppx.create({ methods: [tempo.charge({ account })] });
const response = await fetch(process.env.RESOURCE_URL!);
// response is the 200 — credential was built and submitted automatically
```

Pass `autoSwap: true` to let mppx swap from available stablecoins (USDC.e or
pathUSD) to the required token automatically — useful if your wallet holds USDC.e
or pathUSD and the challenge requires a different token, letting you skip Phase 5:

```typescript
Mppx.create({ methods: [tempo.charge({ account, autoSwap: true })] });
const response = await fetch(process.env.RESOURCE_URL!);
```

### Charge intent — manual mode

> **REQUIRED:** Use `AskUserQuestion` before calling `createCredential`. Parse
> the `WWW-Authenticate: Payment` header from the 402 response and display the
> payment details to the user (amount, token, recipient, resource URL). Only
> proceed after explicit confirmation.

```typescript
import { Mppx, tempo } from 'mppx/client';
import { Receipt } from 'mppx';
import { privateKeyToAccount } from 'viem/accounts';

const account = privateKeyToAccount(process.env.PRIVATE_KEY as `0xstring`);
const mppx = Mppx.create({ polyfill: false, methods: [tempo.charge({ account })] });

// Step 1: probe the endpoint to get the 402 challenge
const initial = await fetch(process.env.RESOURCE_URL!);
if (initial.status !== 402) throw new Error(`Expected 402, got initial.status`);

// Step 2: REQUIRED — show payment summary to user and wait for confirmation
// Parse WWW-Authenticate header; display amount, token, recipient, resource.

// Step 3: build and submit the credential
const credential = await mppx.createCredential(initial, { account });
const paidResponse = await fetch(process.env.RESOURCE_URL!, {
  headers: { Authorization: credential },
});

if (paidResponse.status !== 200) {
  const body = await paidResponse.text();
  throw new Error(`Payment rejected (paidResponse.status): body`);
}

// Step 4: parse the receipt
const receipt = Receipt.fromResponse(paidResponse);
console.log('Payment confirmed. Reference:', receipt.reference);
```

The `Authorization` header value returned by `createCredential()` has the form
`Payment <base64url-encoded credential>` — do not modify this value.

### Session intent

Pass a `maxDeposit` budget to `tempo()` to open a payment channel:

```typescript
// maxDeposit: '10' locks up to 10 pathUSD into the channel escrow
const mppx = Mppx.create({ methods: [tempo({ account, maxDeposit: '10' })] });
const response = await mppx.fetch(process.env.RESOURCE_URL!);
// The SDK manages channel lifecycle and voucher signing automatically
```

For fine-grained session control (manual open/close, sweep), see
`https://mpp.dev/sdk`.

### Direct submission

If the credential was built externally:

```bash
# $CREDENTIAL is the base64url-encoded credential string from mppx.createCredential()
# $RESOURCE_URL was set in Phase 0
curl -si "$RESOURCE_URL" \
  -H "Authorization: Payment $CREDENTIAL"
```

A `200` response with a `Payment-Receipt` header confirms success. Any other
status means the credential was rejected — check the response body and
re-inspect the challenge.

## Phase 6x — x402 Payment

> **x402 path only.** This phase is reached when `PROTOCOL` is `"x402"`
> (detected in Phase 0). Do not enter this phase from the MPP path.

The x402 `"exact"` scheme on EVM networks uses **EIP-3009**
(`transferWithAuthorization`) to authorize a one-time token transfer. The payer
signs an off-chain typed-data message; the facilitator verifies it and settles
the token transfer on-chain — no separate on-chain approval step is required.

### Prerequisite checks before signing

```bash
# 1. Confirm scheme is "exact" — only scheme currently supported
[ "$X402_SCHEME" = "exact" ] || { echo "ERROR: Only 'exact' scheme is supported. Got: $X402_SCHEME"; exit 1; }

# 2. Map network to a chain ID
# Accept both CAIP-2 format (eip155:8453) and plain names (base, ethereum)
case "$X402_NETWORK" in
  base|"eip155:8453")    X402_CHAIN_ID=8453;  SOURCE_RPC_URL="https://mainnet.base.org" ;;
  ethereum|"eip155:1")   X402_CHAIN_ID=1;     SOURCE_RPC_URL="https://eth.llamarpc.com" ;;
  tempo|"eip155:4217")   X402_CHAIN_ID=4217;  SOURCE_RPC_URL="-https://rpc.presto.tempo.xyz" ;;
  *)
    echo "ERROR: Unrecognised or unsupported x402 network: $X402_NETWORK"
    echo "Supported: base / eip155:8453, ethereum / eip155:1, tempo / eip155:4217"
    exit 1
    ;;
esac

# Tempo-network: if wallet lacks the asset on Tempo, bridge first (Phase 4B -> 5 -> return here)
if [ "$X402_CHAIN_ID" = "4217" ]; then
  echo "x402 payment targets Tempo network — checking Tempo-side balance..."
  TEMPO_BALANCE=$(cast call "$X402_ASSET" \
    "balanceOf(address)(uint256)" "$WALLET_ADDRESS" \
    --rpc-url "$SOURCE_RPC_URL" 2>/dev/null || echo "0")
  if [ "$TEMPO_BALANCE" -lt "$X402_AMOUNT" ]; then
    X402_DECIMALS=$(get_token_decimals "$X402_ASSET" "$SOURCE_RPC_URL")
    TEMPO_BAL_HUMAN=$(format_token_amount "$TEMPO_BALANCE" "$X402_DECIMALS")
    X402_AMT_HUMAN=$(format_token_amount "$X402_AMOUNT" "$X402_DECIMALS")
    echo "Insufficient balance on Tempo ($TEMPO_BAL_HUMAN < $X402_AMT_HUMAN $X402_TOKEN_NAME)."
    echo "Acquire the asset first: run Phase 4A (swap to bridge asset) ->"
    echo "Phase 4B (bridge to Tempo) -> Phase 5, then return to Phase 6x."
    exit 1
  fi
fi

# 3. Check wallet token balance — must be >= X402_AMOUNT before signing
ASSET_BALANCE=$(cast call "$X402_ASSET" \
  "balanceOf(address)(uint256)" "$WALLET_ADDRESS" \
  --rpc-url "$SOURCE_RPC_URL")
if [ "$ASSET_BALANCE" -lt "$X402_AMOUNT" ]; then
  X402_DECIMALS=-$(get_token_decimals "$X402_ASSET" "$SOURCE_RPC_URL")
  ASSET_BAL_HUMAN=$(format_token_amount "$ASSET_BALANCE" "$X402_DECIMALS")
  X402_AMT_HUMAN=$(format_token_amount "$X402_AMOUNT" "$X402_DECIMALS")
  echo "ERROR: Insufficient $X402_TOKEN_NAME balance on $X402_NETWORK."
  echo "Have: $ASSET_BAL_HUMAN $X402_TOKEN_NAME, need: $X402_AMT_HUMAN $X402_TOKEN_NAME"
  echo "Acquire the asset first: if funds are on the same chain, run Phase 4A"
  echo "(swap to $X402_ASSET). If funds are on a different chain, run"
  echo "Phase 4A + Phase 4B (bridge to $X402_NETWORK) + Phase 5, then return here."
  exit 1
fi
```

> **REQUIRED:** Use `AskUserQuestion` to show the user a payment summary before
> signing anything:
>
> - Token: `$X402_TOKEN_NAME` (`$X402_ASSET`) on `$X402_NETWORK`
> - Amount: `$(format_token_amount "$X402_AMOUNT" "$(get_token_decimals "$X402_ASSET" "$SOURCE_RPC_URL")")` `$X402_TOKEN_NAME`
> - Recipient: `$X402_PAY_TO`
> - Resource: `$X402_RESOURCE`
>
> Obtain explicit confirmation before proceeding.

### Step 6x-1 — Generate nonce and deadline

```bash
X402_NONCE="0x$(openssl rand -hex 32)"    # 32-byte random nonce
X402_VALID_AFTER=0                         # immediately valid
X402_VALID_BEFORE=$(( $(date +%s) + X402_TIMEOUT ))  # expiry = now + maxTimeoutSeconds
```

### Step 6x-2 — Sign the EIP-3009 `TransferWithAuthorization` typed data

The EIP-3009 domain uses the token contract's own `name` and `version` (from the
`extra` field in the x402 challenge body). The `verifyingContract` is the token
contract itself (`X402_ASSET`).

Sign using viem:

```typescript
import { privateKeyToAccount } from 'viem/accounts';

const account = privateKeyToAccount(process.env.PRIVATE_KEY as `0xstring`);

const domain = {
  name: process.env.X402_TOKEN_NAME!, // from extra.name, e.g. "USDC"
  version: process.env.X402_TOKEN_VERSION!, // from extra.version, e.g. "2"
  chainId: Number(process.env.X402_CHAIN_ID),
  verifyingContract: process.env.X402_ASSET as `0xstring`,
};

// REQUIRED: show the user what they are about to sign before calling signTypedData
const signature = await account.signTypedData({
  domain,
  types: {
    TransferWithAuthorization: [
      { name: 'from', type: 'address' },
      { name: 'to', type: 'address' },
      { name: 'value', type: 'uint256' },
      { name: 'validAfter', type: 'uint256' },
      { name: 'validBefore', type: 'uint256' },
      { name: 'nonce', type: 'bytes32' },
    ],
  },
  primaryType: 'TransferWithAuthorization',
  message: {
    from: process.env.WALLET_ADDRESS as `0xstring`,
    to: process.env.X402_PAY_TO as `0xstring`,
    value: BigInt(process.env.X402_AMOUNT!),
    validAfter: BigInt(process.env.X402_VALID_AFTER!),
    validBefore: BigInt(process.env.X402_VALID_BEFORE!),
    nonce: process.env.X402_NONCE as `0xstring`,
  },
});
process.env.X402_SIGNATURE = signature;
```

> **Domain warning:** The `verifyingContract` is the **token contract**
> (`X402_ASSET`), not a separate verifier. Use the `name` and `version` from
> `extra` — do not assume USDC defaults. Different tokens have different domain
> values. An incorrect domain produces a signature the server will reject
> with a 402.
>
> **REQUIRED:** Use `AskUserQuestion` before this step. Show the
> `TransferWithAuthorization` message fields (from, to, value, validBefore)
> so the user can verify what they are signing. Store the resulting signature as
> `X402_SIGNATURE`.

### Step 6x-3 — Construct the X-PAYMENT payload

```bash
X402_PAYMENT_JSON=$(jq -n \
  --arg  scheme      "$X402_SCHEME" \
  --arg  network     "$X402_NETWORK" \
  --argjson chainId  "$X402_CHAIN_ID" \
  --arg  from        "$WALLET_ADDRESS" \
  --arg  to          "$X402_PAY_TO" \
  --arg    value     "$X402_AMOUNT" \
  --argjson validAfter  "$X402_VALID_AFTER" \
  --argjson validBefore "$X402_VALID_BEFORE" \
  --arg  nonce       "$X402_NONCE" \
  --arg  sig         "$X402_SIGNATURE" \
  --arg  asset       "$X402_ASSET" \
  '{
    scheme:  $scheme,
    network: $network,
    chainId: $chainId,
    payload: {
      authorization: {
        from:        $from,
        to:          $to,
        value:       $value,
        validAfter:  $validAfter,
        validBefore: $validBefore,
        nonce:       $nonce
      },
      signature: $sig
    },
    asset: $asset
  }')

# Base64-encode — strip newlines (required by header spec)
X402_PAYMENT=$(echo "$X402_PAYMENT_JSON" | base64 | tr -d '[:space:]')
```

### Step 6x-4 — Retry the original request with `X-PAYMENT` header

```bash
RETRY_RESPONSE=$(curl -si "$X402_RESOURCE" \
  -H "X-PAYMENT: $X402_PAYMENT" \
  -H "Content-Type: application/json")

RETRY_STATUS=$(echo "$RETRY_RESPONSE" | head -1 | grep -o '[0-9]\{3\}')
RETRY_BODY=$(echo "$RETRY_RESPONSE" | awk 'found{print} /^\r?$/{found=1}')
X402_PAYMENT_RESPONSE=$(echo "$RETRY_RESPONSE" \
  | grep -i 'x-payment-response:' | cut -d' ' -f2- | tr -d '[:space:]')

echo "HTTP status: $RETRY_STATUS"
```

### Interpreting the response

| Status | Meaning                                                 | Action                                                                                       |
| ------ | ------------------------------------------------------- | -------------------------------------------------------------------------------------------- |
| 200    | Payment accepted — resource delivered                   | Display body; decode receipt with `echo "$X402_PAYMENT_RESPONSE" \| base64 --decode \| jq .` |
| 402    | Payment rejected (bad signature, expired, wrong amount) | Check domain name/version, validBefore, and amount                                           |
| 400    | Malformed payment payload                               | Verify JSON structure and base64 encoding                                                    |
| Other  | Server or network error                                 | Report raw body; do not resubmit                                                             |

**Tempo-network variant:** If `X402_NETWORK` is `"tempo"` (or
`eip155:<tempo-chain-id>`), the payment token is a Tempo TIP-20 address. You
must first bridge USDC to Tempo using Phase 4B and optionally swap using Phase 5.
After confirming the Tempo-side token balance, return here to execute Steps 6x-1
through 6x-4, using the Tempo-side token contract as `X402_ASSET` and the Tempo
chain ID as `X402_CHAIN_ID`.

FILE:references/trading-api-flows.md
# Trading API Flows

Step-by-step bash scripts for swap and bridge operations using the
Uniswap Trading API (`https://trade-api.gateway.uniswap.org/v1`).

## Table of Contents

- [Phase 4A — Swap on Source Chain](#phase-4a--swap-on-source-chain)
- [Phase 4B — Bridge to Tempo](#phase-4b--bridge-to-tempo)

## Phase 4A — Swap on Source Chain

Use the Uniswap Trading API to swap the source token to USDC (the bridge
asset). This is an EXACT_OUTPUT swap — the payee's amount determines how much
USDC to acquire.

**Variable Setup** (fill these before running any steps):

```bash
SOURCE_CHAIN_ID=8453              # Chain where you hold the source token (e.g. Base = 8453)
TOKEN_IN_ADDRESS="0x..."          # Address of your source token on SOURCE_CHAIN_ID
# For native ETH, use the WETH address for your chain (recommended — well-supported):
#   Base (8453):     0x4200000000000000000000000000000000000006
#   Ethereum (1):    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2
# The Universal Router wraps ETH before the swap, so msg.value (SWAP_VALUE) will be
# non-zero in the swap response. The ETH sentinel 0xEeeeeEeeeEeEeeEeEeEeeEEEeeeeEeeeeeeeEEEE
# is also supported but try WETH first; use the sentinel only if WETH returns a 400.
USDC_E_ADDRESS="0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913"  # USDC on Base — update for other chains (see Key Addresses)
REQUIRED_AMOUNT_IN="0"            # Use "0" for the initial approval check (Step 4A-1);
                                  # replace with the actual amountIn after Step 4A-2 (quote)
USDC_E_AMOUNT_NEEDED="$REQUIRED_AMOUNT"  # For EXACT_OUTPUT: target = payment amount
# Apply a 0.5% buffer to account for bridge fees:
# USDC_E_AMOUNT_NEEDED=$(echo "$REQUIRED_AMOUNT * 1005 / 1000" | bc)
# This ensures sufficient USDC arrives after any fee deductions.
```

> `slippageTolerance: 0.5` in the quote body means **0.5%** (not 0.005). The
> Trading API accepts slippage as a percentage value.

**Base URL**: `https://trade-api.gateway.uniswap.org/v1`

**Required headers**:

```text
Content-Type: application/json
x-api-key: <UNISWAP_API_KEY>
x-universal-router-version: 2.0
```

### Step 4A-1 — Check approval

```bash
# Build the request body safely using jq to avoid shell injection.
# The `amount` is used to determine whether the existing allowance is
# sufficient. Include it to receive an accurate approval status.
APPROVAL_BODY=$(jq -n \
  --arg wallet "$WALLET_ADDRESS" \
  --arg token "$TOKEN_IN_ADDRESS" \
  --arg amount "$REQUIRED_AMOUNT_IN" \
  --argjson chainId "$SOURCE_CHAIN_ID" \
  '{walletAddress: $wallet, token: $token, amount: $amount, chainId: $chainId}')

curl -s -X POST https://trade-api.gateway.uniswap.org/v1/check_approval \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  -H "x-universal-router-version: 2.0" \
  -d "$APPROVAL_BODY"
```

> **REQUIRED:** If the `approval` field is non-null, use `AskUserQuestion` to
> show the user the approval details (token address, spender, amount, estimated
> gas) and obtain explicit confirmation before submitting the approval
> transaction.

### Step 4A-2 — Get exact-output quote for native USDC (bridge asset)

> **Address note:** `USDC_E_ADDRESS` in the code below refers to the bridge
> asset for the source chain. For Base (chain 8453), use native USDC:
> `0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913`. For Ethereum (chain 1), use
> USDC: `0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48`. See Key Addresses section.

```bash
# Build the request body safely using jq. Chain IDs are integers; addresses
# and amounts are strings.
QUOTE_BODY=$(jq -n \
  --arg swapper "$WALLET_ADDRESS" \
  --arg tokenIn "$TOKEN_IN_ADDRESS" \
  --arg tokenOut "$USDC_E_ADDRESS" \
  --argjson tokenInChainId "$SOURCE_CHAIN_ID" \
  --argjson tokenOutChainId "$SOURCE_CHAIN_ID" \
  --arg amount "$USDC_E_AMOUNT_NEEDED" \
  --argjson slippage 0.5 \
  '{
    swapper: $swapper,
    tokenIn: $tokenIn,
    tokenOut: $tokenOut,
    tokenInChainId: $tokenInChainId,
    tokenOutChainId: $tokenOutChainId,
    amount: $amount,
    type: "EXACT_OUTPUT",
    slippageTolerance: $slippage,
    routingPreference: "BEST_PRICE"
  }')

curl -s -X POST https://trade-api.gateway.uniswap.org/v1/quote \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  -H "x-universal-router-version: 2.0" \
  -d "$QUOTE_BODY"
```

Note: `tokenInChainId` and `tokenOutChainId` must be **integers**, not strings.

Store the full quote response as `QUOTE_RESPONSE`. Then extract the actual input
amount and re-run the approval check with the real value:

```bash
REQUIRED_AMOUNT_IN=$(echo "$QUOTE_RESPONSE" | jq -r '.quote.amountIn')
# Re-run Step 4A-1 with REQUIRED_AMOUNT_IN set to the quoted amount
# to confirm the existing allowance covers the swap.
```

> **ETH/WETH approval note:** When `TOKEN_IN` is native ETH (WETH address), no
> ERC-20 approval is required. `REQUIRED_AMOUNT_IN` is the ETH value sent with
> the transaction — the approval re-check in Step 4A-1 is a no-op. Skip it and
> proceed directly to Step 4A-2.5.

### Step 4A-2.5 — Sign the permitData

If the quote response contains a non-null `permitData` field, you must sign it
off-chain before executing the swap.

> **ETH/WETH note:** When swapping native ETH (using the WETH address as
> `TOKEN_IN`), `permitData` is typically `null` — skip this step if so.
> Proceed directly to Step 4A-3.

- **For CLASSIC routing**: if `permitData` is non-null, sign it using the
  Permit2 contract's EIP-712 typed data signing scheme. The wallet's private
  key or connected signing method is required. See the Permit2 documentation
  or the [swap-integration](../swap-integration/SKILL.md) skill for signing
  details.
- **For UniswapX (DUTCH_V2, DUTCH_V3, PRIORITY)**: sign the `permitData`
  from the quote response using the same EIP-712 typed data approach.

Store the resulting signature as `PERMIT2_SIGNATURE`.

> **REQUIRED:** Use `AskUserQuestion` to confirm the signing step with the
> user before proceeding. Show the permit details (token, spender, amount,
> deadline) so the user understands what they are authorizing.

### Step 4A-3 — Execute the swap

```bash
# Strip permitData; re-attach only if non-null and routing is CLASSIC
ROUTING=$(echo "$QUOTE_RESPONSE" | jq -r '.routing')
CLEAN_QUOTE=$(echo "$QUOTE_RESPONSE" | jq 'del(.permitData, .permitTransaction)')

if [ "$ROUTING" = "CLASSIC" ]; then
  PERMIT_DATA=$(echo "$QUOTE_RESPONSE" | jq '.permitData')
  if [ "$PERMIT_DATA" != "null" ]; then
    # Guard: ensure PERMIT2_SIGNATURE was obtained in Step 4A-2.5
    if [ -z "$PERMIT2_SIGNATURE" ]; then
      echo "ERROR: permitData is present but PERMIT2_SIGNATURE is empty. Complete Step 4A-2.5 first."
      exit 1
    fi
    # Include signature + permitData in swap body
    SWAP_BODY=$(echo "$CLEAN_QUOTE" | jq \
      --arg sig "$PERMIT2_SIGNATURE" \
      --argjson pd "$PERMIT_DATA" \
      '. + {signature: $sig, permitData: $pd}')
  else
    SWAP_BODY="$CLEAN_QUOTE"
  fi
else
  # UniswapX (DUTCH_V2, DUTCH_V3, PRIORITY): signature only (no permitData in swap body)
  if [ -z "$PERMIT2_SIGNATURE" ]; then
    echo "ERROR: UniswapX order requires PERMIT2_SIGNATURE. Complete Step 4A-2.5 first."
    exit 1
  fi
  SWAP_BODY=$(echo "$CLEAN_QUOTE" | jq --arg sig "$PERMIT2_SIGNATURE" '. + {signature: $sig}')
fi

curl -s -X POST https://trade-api.gateway.uniswap.org/v1/swap \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  -H "x-universal-router-version: 2.0" \
  -d "$SWAP_BODY"
```

Store the swap response as `SWAP_RESPONSE`. The `/swap` endpoint returns
**unsigned calldata** — you must broadcast it yourself. After validating
`swap.data` is non-empty, present the transaction summary to the user via
`AskUserQuestion` then broadcast:

```bash
# Extract the transaction fields from the swap response
SWAP_TO=$(echo "$SWAP_RESPONSE" | jq -r '.swap.to')
SWAP_DATA=$(echo "$SWAP_RESPONSE" | jq -r '.swap.data')
SWAP_VALUE=$(echo "$SWAP_RESPONSE" | jq -r '.swap.value // "0x0"')

# Validate before broadcasting
[ -z "$SWAP_DATA" ] || [ "$SWAP_DATA" = "null" ] && echo "ERROR: swap.data is empty — quote may have expired. Re-fetch from Step 4A-2." && exit 1
# For native ETH swaps (TOKEN_IN is WETH address or ETH sentinel), SWAP_VALUE must
# be non-zero — it carries the ETH amount as msg.value. A zero value means the quote
# did not recognise the input as native ETH; do NOT broadcast or the swap will revert.
if [[ "$TOKEN_IN_ADDRESS" == "0x4200000000000000000000000000000000000006" || \
      "$TOKEN_IN_ADDRESS" == "0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2" || \
      "$TOKEN_IN_ADDRESS" == "0xEeeeeEeeeEeEeeEeEeEeeEEEeeeeEeeeeeeeEEEE" ]]; then
  [ "$SWAP_VALUE" = "0x0" ] || [ "$SWAP_VALUE" = "0" ] && \
    echo "ERROR: SWAP_VALUE is zero for a native ETH swap — verify TOKEN_IN_ADDRESS and re-fetch the quote." && exit 1
fi

# Broadcast via cast (replace RPC URL with your source chain endpoint)
SWAP_TX=$(cast send "$SWAP_TO" \
  --data "$SWAP_DATA" \
  --value "$SWAP_VALUE" \
  --private-key "$PRIVATE_KEY" \
  --rpc-url https://mainnet.base.org \
  --json | jq -r '.transactionHash')

# Wait for the swap to mine before bridging — a reverted swap leaves USDC at zero
SWAP_STATUS=$(cast receipt "$SWAP_TX" --rpc-url https://mainnet.base.org --json | jq -r '.status')
[ "$SWAP_STATUS" = "0x1" ] || { echo "ERROR: Swap reverted (status=$SWAP_STATUS). Do not proceed to bridge." && exit 1; }
echo "Swap confirmed: $SWAP_TX"

# Verify USDC balance landed before proceeding to Phase 4B
USDC_AFTER_SWAP=$(cast call "$USDC_E_ADDRESS" \
  "balanceOf(address)(uint256)" "$WALLET_ADDRESS" \
  --rpc-url https://mainnet.base.org)
# Format balances for human-readable display (USDC = 6 decimals)
USDC_DECIMALS=$(get_token_decimals "$USDC_E_ADDRESS" "https://mainnet.base.org")
USDC_AFTER_HUMAN=$(format_token_amount "$USDC_AFTER_SWAP" "$USDC_DECIMALS")
USDC_NEEDED_HUMAN=$(format_token_amount "$USDC_E_AMOUNT_NEEDED" "$USDC_DECIMALS")
echo "USDC balance after swap: $USDC_AFTER_HUMAN USDC (need at least $USDC_NEEDED_HUMAN USDC)"
# Halt if swap produced insufficient USDC — bridging 0 USDC wastes gas and fails silently
[ "$USDC_AFTER_SWAP" -lt "$USDC_E_AMOUNT_NEEDED" ] && \
  echo "ERROR: swap produced $USDC_AFTER_HUMAN USDC but $USDC_NEEDED_HUMAN USDC needed — check receipt, do NOT proceed to bridge." && exit 1
```

## Phase 4B — Bridge to Tempo

> **If you skipped Phase 4A** (you already hold native USDC on Base), initialize
> these variables before proceeding:
>
> ```bash
> USDC_E_AMOUNT_NEEDED="$REQUIRED_AMOUNT"
> USDC_E_ADDRESS="0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913"  # USDC on Base
> ```

Use the Uniswap Trading API to bridge USDC from Base to USDC.e on Tempo. The
bridge is powered by Across Protocol and is fully abstracted by the API —
no manual contract calls required.

**Bridge asset addresses:**

| Chain              | Asset       | Address                                      |
| ------------------ | ----------- | -------------------------------------------- |
| Base (8453) — in   | Native USDC | `0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913` |
| Tempo (4217) — out | USDC.e      | `0x20C000000000000000000000b9537d11c60E8b50` |

### Step 4B-1 — Check approval

```bash
BRIDGE_TOKEN_IN="0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913"   # USDC on Base
BRIDGE_TOKEN_OUT="0x20C000000000000000000000b9537d11c60E8b50"   # USDC.e on Tempo
BRIDGE_AMOUNT="$USDC_E_AMOUNT_NEEDED"

APPROVAL=$(curl -s "https://trade-api.gateway.uniswap.org/v1/check_approval" \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  --data "$(jq -n \
    --arg token         "$BRIDGE_TOKEN_IN" \
    --arg amount        "$BRIDGE_AMOUNT" \
    --arg walletAddress "$WALLET_ADDRESS" \
    --argjson chainId 8453 \
    '{token: $token, amount: $amount, walletAddress: $walletAddress, chainId: $chainId}')")

APPROVAL_TX=$(echo "$APPROVAL" | jq -r '.approval // empty')
echo "Approval needed: $([ -n "$APPROVAL_TX" ] && echo yes || echo no)"
```

> **REQUIRED:** If `APPROVAL_TX` is non-empty, use `AskUserQuestion` to show the
> user the approval details (token: `$BRIDGE_TOKEN_IN`, spender, amount:
> `$BRIDGE_AMOUNT`, estimated gas) and obtain explicit confirmation before
> submitting the approval transaction.

If confirmed and `APPROVAL_TX` is non-empty:

```bash
APPROVAL_TO=$(echo "$APPROVAL_TX"   | jq -r '.to')
APPROVAL_DATA=$(echo "$APPROVAL_TX" | jq -r '.data')
APPROVE_HASH=$(cast send "$APPROVAL_TO" \
  --data "$APPROVAL_DATA" \
  --private-key "$PRIVATE_KEY" \
  --rpc-url https://mainnet.base.org \
  --json | jq -r '.transactionHash')
cast receipt "$APPROVE_HASH" --rpc-url https://mainnet.base.org > /dev/null
echo "Approval confirmed: $APPROVE_HASH"
```

### Step 4B-2 — Get bridge quote (EXACT_OUTPUT)

```bash
BRIDGE_QUOTE=$(curl -s "https://trade-api.gateway.uniswap.org/v1/quote" \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  --data "$(jq -n \
    --arg tokenIn        "$BRIDGE_TOKEN_IN" \
    --arg tokenInChainId "8453" \
    --arg tokenOut       "$BRIDGE_TOKEN_OUT" \
    --arg tokenOutChainId "4217" \
    --arg amount         "$BRIDGE_AMOUNT" \
    --arg swapper        "$WALLET_ADDRESS" \
    '{
       tokenIn:         $tokenIn,
       tokenInChainId:  $tokenInChainId,
       tokenOut:        $tokenOut,
       tokenOutChainId: $tokenOutChainId,
       amount:          $amount,
       swapper:         $swapper,
       type:            "EXACT_OUTPUT"
     }')")

BRIDGE_QUOTE_ID=$(echo "$BRIDGE_QUOTE" | jq -r '.quote.quoteId')
BRIDGE_FEE=$(echo "$BRIDGE_QUOTE"      | jq -r '.quote.bridgeFee // .quote.gasFee // "unknown"')
BRIDGE_ETA=$(echo "$BRIDGE_QUOTE"      | jq -r '.quote.estimatedFillTime // "2-5 minutes"')
echo "Bridge quote: quoteId=$BRIDGE_QUOTE_ID fee=$BRIDGE_FEE eta=$BRIDGE_ETA"
```

> **REQUIRED:** Use `AskUserQuestion` before submitting the bridge transaction.
> Show the user:
>
> - Amount: `$(format_token_amount "$BRIDGE_AMOUNT" "$USDC_DECIMALS")` USDC on Base (chain 8453)
> - Destination: `$BRIDGE_TOKEN_OUT` (USDC.e) on Tempo (chain 4217)
> - Bridge fee: `$BRIDGE_FEE`
> - Estimated time: `$BRIDGE_ETA`
> - Recipient: `$WALLET_ADDRESS`
>
> Do not proceed until the user confirms.

### Step 4B-3 — Execute the bridge

```bash
BRIDGE_RESPONSE=$(curl -s "https://trade-api.gateway.uniswap.org/v1/swap" \
  -H "Content-Type: application/json" \
  -H "x-api-key: $UNISWAP_API_KEY" \
  --data "$(jq -n \
    --argjson quote  "$BRIDGE_QUOTE" \
    --arg walletAddress "$WALLET_ADDRESS" \
    '{quote: $quote.quote, walletAddress: $walletAddress}')")

BRIDGE_TO=$(echo "$BRIDGE_RESPONSE"   | jq -r '.swap.to')
BRIDGE_DATA=$(echo "$BRIDGE_RESPONSE" | jq -r '.swap.data')
BRIDGE_VALUE=$(echo "$BRIDGE_RESPONSE"| jq -r '.swap.value // "0"')

BRIDGE_TX=$(cast send "$BRIDGE_TO" \
  --data "$BRIDGE_DATA" \
  --value "$BRIDGE_VALUE" \
  --private-key "$PRIVATE_KEY" \
  --rpc-url https://mainnet.base.org \
  --json | jq -r '.transactionHash')

BRIDGE_STATUS=$(cast receipt "$BRIDGE_TX" --rpc-url https://mainnet.base.org --json | jq -r '.status')
[ "$BRIDGE_STATUS" = "0x1" ] || { echo "ERROR: Bridge tx reverted. Do not proceed."; exit 1; }
echo "Bridge submitted: $BRIDGE_TX — waiting for funds on Tempo..."
```

### Step 4B-4 — Poll for arrival on Tempo

Poll for USDC.e balance on Tempo every 30 seconds for up to 10 minutes:

```bash
TEMPO_RPC_URL="https://rpc.presto.tempo.xyz"
for i in $(seq 1 20); do
  USDC_E_ON_TEMPO=$(cast call "$BRIDGE_TOKEN_OUT" \
    "balanceOf(address)(uint256)" "$WALLET_ADDRESS" \
    --rpc-url "$TEMPO_RPC_URL" 2>/dev/null || echo "0")
  if [ "$USDC_E_ON_TEMPO" -ge "$BRIDGE_AMOUNT" ]; then
    USDC_E_DECIMALS=$(get_token_decimals "$BRIDGE_TOKEN_OUT" "$TEMPO_RPC_URL")
    USDC_E_HUMAN=$(format_token_amount "$USDC_E_ON_TEMPO" "$USDC_E_DECIMALS")
    echo "Bridge confirmed — $USDC_E_HUMAN USDC.e received on Tempo."
    break
  fi
  USDC_E_POLL_HUMAN=$(format_token_amount "$USDC_E_ON_TEMPO" "$(get_token_decimals "$BRIDGE_TOKEN_OUT" "$TEMPO_RPC_URL")")
  echo "Waiting for bridge arrival... attempt $i/20 (balance: $USDC_E_POLL_HUMAN USDC.e)"
  sleep 30
done
[ "$USDC_E_ON_TEMPO" -ge "$BRIDGE_AMOUNT" ] || \
  { echo "Bridge not confirmed after 10 minutes. Check $BRIDGE_TX on https://explore.mainnet.tempo.xyz"; exit 1; }
```

After a successful bridge, you hold **USDC.e** (`$BRIDGE_TOKEN_OUT`) on Tempo.
Use this as `TOKEN_IN` in Phase 5 to swap to the required payment token.

> **Do not re-submit** if the poll times out — duplicate bridge deposits result
> in double payment. Have the user check the transaction on the Tempo explorer.

---
name: vendor-compliance-1099
description: >
  1099 vendor compliance pipeline for accounting firms. Pulls full-year General Ledger from QBO,
  aggregates vendor payments, applies IRS $600 threshold, classifies 1099-NEC vs 1099-MISC,
  checks corporate exemptions, tracks W-9 and TIN status, filters credit card payments (1099-K
  handled by processor), calculates late-filing penalties, and produces year-over-year CDC.
  Outputs an 8-tab Excel workbook. Use for year-end 1099 compliance runs, W-9 tracking, and
  IRS threshold checks. NOT for payroll W-2, 1042-S foreign withholding, or 1099-K reconciliation.
version: 1.0.0
author: samledger67
tags:
  - finance
  - accounting
  - tax
  - 1099
  - compliance
  - vendor-management
  - QBO
updated: 2026-03-18
---

# Skill: vendor-compliance-1099

## Description
1099 vendor compliance pipeline for accounting firms. Pulls the full-year General Ledger from QBO, aggregates vendor payments by name, applies IRS $600 threshold, classifies 1099-NEC vs 1099-MISC, checks corporate exemptions, tracks W-9 and TIN status, filters credit card payments (1099-K handled by processor), calculates late-filing penalties, and produces a year-over-year CDC. Outputs an 8-tab Excel workbook.

**Trigger phrases:** "run 1099 compliance," "vendor 1099 check," "pull 1099 list," "who needs a 1099," "1099 vendor scan," "W-9 tracker," "1099-NEC list," "1099-MISC list"

**NOT for:** payroll W-2 compliance, 1042-S foreign withholding, 1099-K reconciliation (CC processor), or non-US entities.

---

## Pipeline Location
```
scripts/pipelines/vendor-compliance-1099.py
```

## Usage

```bash
# Standard run
python3 scripts/pipelines/vendor-compliance-1099.py --slug my-client --year 2025

# QBO sandbox
python3 scripts/pipelines/vendor-compliance-1099.py --slug my-client --year 2025 --sandbox

# Custom output directory
python3 scripts/pipelines/vendor-compliance-1099.py --slug my-client --year 2025 --out ~/Desktop/1099s

# Skip GL pull (empty vendor list — testing only)
python3 scripts/pipelines/vendor-compliance-1099.py --slug my-client --year 2025 --skip-gl
```

**Arguments:**
| Flag | Required | Default | Notes |
|------|----------|---------|-------|
| `--slug` | ✅ | — | QBO client slug |
| `--year` | ✅ | — | Tax year (e.g. 2025) |
| `--sandbox` | ❌ | false | Use QBO sandbox |
| `--skip-gl` | ❌ | false | Skip GL pull (empty output — testing only) |
| `--out` | ❌ | ~/Desktop | Output directory |

---

## Output: 8-Tab Excel Workbook

| Tab | Contents |
|-----|----------|
| **Vendor Summary** | All vendors: total paid, ACH/check/wire vs CC split, form type, corp exempt flag, action required |
| **1099-NEC List** | Contractors/service vendors ≥$600 (reportable amount only), W-9 status, TIN status, action |
| **1099-MISC List** | Rent, royalties, prizes ≥$600 with MISC box classification (Box 1/2/3/6/10) |
| **Exemptions** | Corp-flagged vendors (LLC/Inc/Corp/Ltd) requiring manual entity-type verification before skipping 1099 |
| **W-9 Tracker** | Per-vendor W-9 received/pending/missing status; persisted between runs; editable date/TIN fields |
| **Payment Methods** | ACH/check/wire vs. credit card split per vendor; CC excluded from 1099 reportable amount |
| **Penalties Calc** | IRC §6721/6722 penalty scenarios: on-time, 15/30/45/60/90 days late + today's actual exposure |
| **CDC Log** | New vendors (not in prior year), dropped vendors, amount changes ≥10% or ≥$500 YoY |

**Output filename:** `VendorCompliance_1099_{slug}_{year}.xlsx`

---

## Key IRS Rules Implemented

### $600 Threshold
- Applied to **ACH/check/wire amounts only** — credit card payments excluded (processor files 1099-K)
- Threshold is per-vendor, full calendar year aggregate

### 1099 Type Classification
| Form | When | Keyword triggers |
|------|------|-----------------|
| **1099-NEC** | Non-employee compensation: contractors, consultants, attorneys, sole proprietors | contractor, freelance, consultant, attorney, repair, cleaning, design, etc. |
| **1099-MISC** | Rent, royalties, prizes, medical payments | rent, royalty, prize, award, medical, healthcare |

**Default is 1099-NEC** — NEC is assumed for all service payments unless account/memo indicates MISC category.

### Corporate Exemption
- Vendor names matching `LLC|Inc|Corp|Ltd|Co.|Company|etc.` → flagged as **potentially exempt**
- Still appear in Exemptions tab for manual verification
- **Exception — always file regardless of entity type:**
  - Attorneys (IRC §6045(f)) → 1099-NEC
  - Medical providers → 1099-MISC Box 6

### Payment Method Filter
- **Credit card keywords** in memo/txn_type/split → classified as CC, excluded from reportable amount
- **ACH/check/wire** → included in reportable amount
- Unclassified payments → included (conservative — better to over-report)

### W-9 & TIN Tracking
- W-9 status persisted at `.cache/vendor-compliance-1099/{slug}-w9.json`
- New vendors auto-default to `NO` status
- TIN status persisted at `.cache/vendor-compliance-1099/{slug}-tin.json`
- **Backup withholding:** 24% applies if vendor fails to provide valid TIN

---

## Filing Deadlines

| Form | Recipient Copy | IRS Paper | IRS e-File |
|------|---------------|-----------|-----------|
| 1099-NEC | January 31 | January 31 | January 31 |
| 1099-MISC (Box 7) | January 31 | January 31 | January 31 |
| 1099-MISC (other boxes) | January 31 | February 28 | March 31 |

**e-File required** if filing 10+ information returns.

---

## Penalty Rates (IRC §6721/6722 — 2024)

| Days Late | Per Form | Small Biz Annual Cap |
|-----------|----------|---------------------|
| ≤30 days | $60 | $232,500 |
| 31–60 days | $120 | $664,500 |
| >60 days | $310 | $1,329,000 |
| Intentional disregard | $630 minimum | No cap |

Small business = avg annual gross receipts ≤$5M for 3 prior years.

---

## Cache Files

| File | Purpose |
|------|---------|
| `.cache/vendor-compliance-1099/{slug}-{year}.json` | YoY snapshot for CDC (auto-saved each run) |
| `.cache/vendor-compliance-1099/{slug}-{year-1}.json` | Prior year snapshot for comparison |
| `.cache/vendor-compliance-1099/{slug}-w9.json` | W-9 status (persisted, editable manually) |
| `.cache/vendor-compliance-1099/{slug}-tin.json` | TIN status (persisted, editable manually) |

**Updating W-9 status manually:**
```bash
# Edit cache file directly to update W-9 status
cat .cache/vendor-compliance-1099/my-client-w9.json
# Modify "VendorName": "YES" | "NO" | "PENDING"
# Then re-run pipeline — status will be loaded automatically
```

---

## Integration Requirements

- **QBO Client:** Node.js QBO client (auth token must be set)
- **Python packages:** `pip install openpyxl`
- **GL access required:** Pipeline reads full-year GL — ensure QBO auth has GL report access
- **No write access to QBO** — read-only integration

---

## Decimal Math

All financial calculations use Python `Decimal` with `ROUND_HALF_UP` at 2 decimal places. No float arithmetic. Same pattern as `tax-package-prep.py` and `pl-deep-analysis.py`.

---

## When NOT to Use This Skill

- **Payroll / W-2 compliance** → separate payroll workflow
- **1099-K reconciliation** → CC processor provides (not Sam's responsibility)
- **Foreign vendor withholding** → Form 1042-S, different rules
- **State-level 1099 filing** → varies by state, not covered here
- **1099-INT / 1099-DIV / 1099-B** → investment/bank-issued, not vendor compliance

---

## Workflow Checklist

Run this pipeline as part of year-end close:

1. **November/December:** Pre-screen — run pipeline to identify missing W-9s before year-end
2. **January (early):** Final run — full year GL aggregation
3. **January 15:** W-9 collection deadline (internal)
4. **January 25:** Prepare and review filings
5. **January 31:** File 1099-NEC (recipient + IRS)
6. **February 28 / March 31:** File 1099-MISC (IRS paper/e-file)

---

---
name: Tax Package Preparation
slug: tax-package-prep
version: 1.0.0
description: >
  Year-end tax package preparation pipeline for QBO-connected clients. Generates a 9-tab
  Excel workbook: Tax Summary, Income, Expenses, Depreciation, 1099s, State Nexus, Crypto
  (Form 8949), Checklist, and CDC. Reads client SOP for entity type, crypto wallet, vehicle,
  home office, and FBAR flags. Maps every line item to IRS form and schedule codes.
tags:
  - finance
  - accounting
  - tax
  - qbo
  - excel
  - year-end
license: MIT
---

# Tax Package Preparation

Prepare a complete year-end tax package for client filing. Pulls full-year financial data from QBO, reads the client SOP for entity configuration, and generates all tax-ready schedules with IRS form mapping.

## When To Use

Use when:
- Client needs year-end tax package for CPA/tax preparer
- Generating income/expense schedules with IRS line mapping
- Identifying 1099 vendors (paid >$600)
- Flagging crypto exposure (Form 8949) from SOP wallet address
- Checking for FBAR requirement (FinCEN 114)
- Detecting multi-state nexus risk
- Building carryforward analysis (NOL, Sec 179, charitable)
- Tracking year-over-year tax position changes (CDC)

**NOT for:**
- Monthly close → use `month-end-close.py`
- Bank reconciliation → use `bank-reconciliation.py`
- P&L variance analysis → use `pl-deep-analysis.py`
- Payroll tax returns (941, 940, W-2s) — separate workflow
- Actual tax return preparation (CPA reviews all output before filing)

## Quick Start

```bash
# Standard run — pulls full year from QBO
python3 scripts/pipelines/tax-package-prep.py --slug my-client --year 2025

# Skip GL drill (faster, less vendor detail)
python3 scripts/pipelines/tax-package-prep.py --slug my-client --year 2025 --skip-gl

# Custom output directory
python3 scripts/pipelines/tax-package-prep.py --slug my-client --year 2025 --out ~/Desktop/tax-2025

# QBO sandbox
python3 scripts/pipelines/tax-package-prep.py --slug my-client --year 2025 --sandbox
```

## Requirements

```bash
pip install openpyxl
# QBO auth token must already be configured
```

## Output: 9-Tab Excel Workbook

| Tab | Contents |
|-----|----------|
| **Tax Summary** | Entity info, key tax metrics, special flags (crypto/FBAR/vehicle/home office), SOP watch items |
| **Income Schedule** | Revenue by category with IRS Sch C / 1120 / 1065 line mapping |
| **Expense Schedule** | Expenses with IRS line mapping, 50% meal limit applied, deductible amounts |
| **Depreciation** | Fixed assets from GL + BS, MACRS / Section 179 / Straight-Line detection |
| **1099 Vendors** | Vendors paid >$600, form type (NEC/MISC), corp exemption flag, W-9 action list |
| **State Nexus** | Multi-state revenue/expense pattern detection, HIGH/MEDIUM/LOW risk rating |
| **Crypto Flag** | Form 8949 flag, wallet address, FBAR assessment, action item checklist |
| **Checklist** | READY / NEEDS INPUT / MISSING for every tax package item |
| **CDC Log** | Year-over-year income and expense position changes |

## SOP Integration

The pipeline reads `clients/{slug}/sop.md` to auto-configure:

| SOP Signal | What It Triggers |
|------------|-----------------|
| `S-Corp` / `1120-S` | Entity = S-Corp, officer W-2 flag, K-1 checklist item |
| `C-Corp` / `1120` | Entity = C-Corp, E&P tracking, charitable 10% limit |
| `Partnership` / `1065` | K-1 prep checklist, partner basis tracking |
| `0x[wallet]` (ETH address) | Crypto flag, Form 8949, wallet address in output |
| `crypto` / `bitcoin` / `defi` | Crypto flag, Form 8949 required |
| `vehicle` / `mileage` | Vehicle schedule, mileage log requirement |
| `home office` / `Form 8829` | Home office flag, sq footage action items |
| `SAFE` / `convertible note` | SAFE treatment watch item |
| `foreign` / `offshore` | FBAR flag, FinCEN 114 action |
| `NOL` / `loss carryforward` | Carryforward analysis |
| `Section 179 carry` | Section 179 carryover check |
| `interest expense` | Watch item: verify deductibility |

## IRS Schedule Mapping

### Expense Categories (auto-mapped)
- Advertising → Sch C Ln 8 / 1120 Ln 22 / 1065 Ln 21a
- Vehicle/Auto → Sch C Ln 9 (standard mileage or actual)
- Contract Labor → Sch C Ln 11 (triggers 1099-NEC scan)
- Depreciation → Sch C Ln 13 / Form 4562
- Interest → Sch C Ln 16 / 1120 Ln 18
- Legal/Professional → Sch C Ln 17
- Wages → Sch C Ln 26 / 1120 Ln 13
- Meals/Entertainment → Sch C Ln 24 (50% limit applied automatically)
- Home Office → Sch C Form 8829

### Income Categories (auto-mapped)
- Service Revenue → Sch C Ln 1 / 1120 Ln 1a
- Product Sales → Sch C Ln 1 / 1120 Ln 1a
- Interest Income → Sch B
- Crypto → Form 8949 / Sch D

## 1099 Vendor Logic

Scans all GL expense accounts and aggregates by vendor name:
- **Include:** Vendors paid ≥$600 via check/ACH/wire (not credit card)
- **1099-NEC:** Contractors, consultants, attorneys, freelancers
- **1099-MISC:** Rent, royalties, medical payments
- **Exempt:** C-Corps and S-Corps (except attorneys and medical providers)
- **Flag:** "Inc", "Corp", "LLC" in vendor name → verify entity type
- **Deadline:** January 31 (1099-NEC), January 31/March 31 (1099-MISC)

## State Nexus Detection

Scans GL memos, vendor names, and account names for US state indicators:
- **HIGH risk:** 5+ indicators in a state → likely nexus, recommend registration review
- **MEDIUM risk:** 2-4 indicators → investigate further
- **LOW risk:** 1 indicator → monitor

Physical nexus (employees, office), economic nexus ($100K revenue threshold), and payroll nexus are all flagged.

## Crypto / FBAR Logic

**Crypto:**
- Detects from SOP wallet address (0x... pattern) or crypto keywords in GL
- Flags Form 8949 and Schedule D requirement
- Detects staking (ordinary income) vs. sales (capital gains) from GL memos
- Action list includes exchange export, cost basis calculation, software recommendation

**FBAR (FinCEN 114):**
- Triggers on foreign account keywords in GL or SOP
- FBAR threshold: $10,000 aggregate foreign account balance at any point in the year
- Also checks Form 8938 (FATCA) thresholds
- Deadline: April 15 with automatic extension to October 15

## CDC Cache

Each run saves a snapshot to `.cache/tax-package-prep/{slug}-{year}.json`.  
The next year's run computes year-over-year deltas for income and expense accounts.  
CDC events are shown in the **CDC Log** tab sorted by absolute dollar change.

## Depreciation Detection

Scans GL and Balance Sheet for:
- Fixed asset account names (equipment, vehicle, computer, furniture, leasehold)
- Accumulated depreciation account names
- Memo keywords: "section 179", "MACRS", "bonus depreciation", "straight-line"
- P&L depreciation expense line items

Output includes: account, year-end balance, period activity, detected method, Section 179 flag.

## Estimated Tax Payments

Scans GL for estimated tax payment entries:
- Account names: "estimated tax", "quarterly payment", "1040-es", "1120-w"
- Memo keywords: same
- Classifies by quarter (Q1-Q4) based on payment date
- Flags missing quarters → client must verify with bank statements

## Checklist Status Definitions

| Status | Meaning |
|--------|---------|
| ✅ READY | Data found in QBO — no client action needed |
| ⚠ NEEDS INPUT | Requires additional documentation from client |
| ❌ MISSING | Not found — QBO pull failed or data doesn't exist |

## File Locations

- **Pipeline:** `scripts/pipelines/tax-package-prep.py`
- **CDC Cache:** `.cache/tax-package-prep/{slug}-{year}.json`
- **Output:** `~/Desktop/TaxPackage_{slug}_{year}.xlsx` (or `--out` dir)
- **Skill:** `skills/tax-package-prep/SKILL.md`

## Notes

- All financial math uses Python `Decimal` — no float rounding errors
- Output is for CPA/tax preparer review — not a filed return
- Vehicle deduction: requires mileage log from client; pipeline flags but cannot calculate
- Home office: requires square footage from client; pipeline flags Form 8829 requirement
- Entity-specific items (K-1s, basis tracking, E&P) are flagged as checklist items, not generated
- Carryforward amounts require prior-year return — pipeline flags for review but cannot pull from prior returns

---
name: SCF Quick Compare
slug: scf-quick-compare
version: 1.0.0
description: >
  Period-over-period variance analysis on the Statement of Cash Flows pulled
  from QuickBooks Online. Outputs a 4-tab Excel workbook: Summary, Detail,
  Flags, CDC Log. Covers Operating, Investing, and Financing sections with
  balance validation and SCF-specific analysis notes on flagged items.
tags:
  - finance
  - accounting
  - cash-flow
  - qbo
  - excel

negative_boundaries:
  - 13-week rolling cash flow forecasting → use cash-flow-forecast skill
  - P&L variance analysis → use pl-quick-compare skill
  - AR aging / collections tracking → use ar-collections skill
  - Balance sheet analysis → use bs-quick-compare skill
  - Deep CF quality analysis → use scf-deep-analysis skill
---

# SCF Quick Compare — Skill

## What This Skill Does

Runs a period-over-period variance analysis on the **Statement of Cash Flows (SCF)** pulled directly from QuickBooks Online. Outputs a 4-tab Excel workbook: Summary | Detail | Flags | CDC Log.

Mirrors the `pl-quick-compare` pattern exactly but for the Cash Flow Statement — Operating / Investing / Financing sections, balance validation, and SCF-specific analysis notes on flagged items.

## When to Use

**Use when:**
- A client needs month-over-month or YTD cash flow variance analysis
- Reviewing SCF as part of monthly close deliverables
- Investigating a material shift in operating, investing, or financing cash flows
- Client asks: "why did our cash position change?" or "what drove the cash swing?"

**NOT for:**
- 13-week rolling cash flow forecasting → use `cash-flow-forecast.py`
- P&L variance analysis → use `pl-quick-compare.py`
- AR aging / collections tracking → use `ar-collections`
- Balance sheet analysis (not cash flows)

## Script Location

```
scripts/pipelines/scf-quick-compare.py
```

## Requirements

- `pip install openpyxl` (already installed in workspace)
- Node.js QBO client with valid auth token
- QBO credentials configured

## Usage

```bash
# Current month vs. prior month (auto-detects prior)
python3 scripts/pipelines/scf-quick-compare.py \
    --slug my-client \
    --current-start 2026-03-01 --current-end 2026-03-31

# Explicit prior period
python3 scripts/pipelines/scf-quick-compare.py \
    --slug my-client \
    --current-start 2026-02-01 --current-end 2026-02-28 \
    --prior-start 2026-01-01 --prior-end 2026-01-31

# YTD vs prior YTD (Jan 1 → end of last completed month)
python3 scripts/pipelines/scf-quick-compare.py \
    --slug my-client --ytd --year 2026

# Custom output directory
python3 scripts/pipelines/scf-quick-compare.py \
    --slug my-client \
    --current-start 2026-03-01 --current-end 2026-03-31 \
    --out ~/Desktop/reports

# Sandbox mode (QBO sandbox environment)
python3 scripts/pipelines/scf-quick-compare.py \
    --slug my-client \
    --current-start 2026-03-01 --current-end 2026-03-31 \
    --sandbox
```

## Arguments

| Flag | Required | Description |
|------|----------|-------------|
| `--slug` | ✅ | Company slug (must be connected in qbo-client) |
| `--current-start` | ✅* | Current period start date (YYYY-MM-DD) |
| `--current-end` | ✅* | Current period end date (YYYY-MM-DD) |
| `--prior-start` | ❌ | Prior period start (auto-shifts 1 month if omitted) |
| `--prior-end` | ❌ | Prior period end (auto-shifts 1 month if omitted) |
| `--ytd` | ✅* | YTD mode (alternative to explicit dates) |
| `--year` | ❌ | Year for --ytd (default: current year) |
| `--out` | ❌ | Output directory (default: ~/Desktop) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

*Either `--current-start`/`--current-end` OR `--ytd` is required.

## Output

Excel file: `SCF_QuickCompare_{slug}_{period}.xlsx` saved to Desktop (or `--out` directory).

### Tab 1: Summary
- Operating / Investing / Financing section totals (current vs prior, $ variance, % variance, F/U)
- Net Change in Cash
- Beginning and Ending Cash Balance
- SCF validation: `Operating + Investing + Financing = Net Change` and `Beginning + Net Change = Ending Cash`

### Tab 2: Detail
- Every SCF line item with hierarchy preserved
- Prior period | Current period | $ Variance | % Variance | F/U label
- Color-coded by section (Operating = blue, Investing = gold, Financing = purple)

### Tab 3: ⚠ Flags
- Material variances: **≥10% change OR ≥$2,500 absolute**
- Analysis note for each flagged item — plain-English explanation of what the variance likely means
- SCF-specific interpretation (AR buildup, D&A add-back, capex, debt repayment, distributions)

### Tab 4: CDC Log
- Change Data Capture: compares current SCF flat map against last cached run
- First run: full snapshot saved (no deltas)
- Subsequent runs: shows exactly what line items changed since last run
- Cache location: `.cache/scf-quick-compare/{slug}.json`

## SCF Logic

### Section Classification
The parser classifies each QBO CF row into sections by keyword matching on row names:
- **Operating**: net income, depreciation, amortization, AR, AP, inventory, prepaid, accrued, working capital
- **Investing**: equipment, property, asset, purchase, capex, investing
- **Financing**: loan, line of credit, note payable, distribution, equity, contribution, SAFE, financing
- **Net Change**: net change, net increase/decrease in cash
- **Beginning/Ending Cash**: beginning, ending (balance check rows)

### Variance F/U Logic
For SCF: **positive delta = Favorable** (more cash generated/retained vs prior).
This is directionally correct for all sections — the goal is always more net cash.

### Balance Validation
```
Net Change = Operating + Investing + Financing      (≤$1 tolerance)
Ending Cash = Beginning Cash + Net Change           (≤$1 tolerance)
```
Both checks run on both periods and displayed in the Summary tab.

### YTD Mode
`--ytd`: Current = Jan 1 → end of last completed month. Prior = same date range in prior year.
Example: run on March 17, 2026 → Current = Jan 1 – Feb 28, 2026 | Prior = Jan 1 – Feb 28, 2025.

## Analysis Notes (Flags Tab)

The Flags tab includes an **Analysis Note** column with SCF-specific interpretation for each material variance:

| Item | Note logic |
|------|-----------|
| Net Income | Profitability driver — directs to P&L for root cause |
| Depreciation / Amortization | Non-cash add-back explanation |
| Accounts Receivable | AR buildup (cash tied up) vs. collection acceleration |
| Accounts Payable | AP extension (cash benefit) vs. paydown (cash use) |
| Inventory | Buildup (cash use) vs. drawdown (cash release) |
| Equipment / CapEx | Strategic capex alert — verify against growth plan |
| Loan proceeds / repayments | Debt structure activity — review debt schedule |
| Distributions | Owner draw alert — verify cash availability |
| SAFE / Equity | Cap table activity — verify with investor records |
| Net Change | Overall cash generation summary |

## CDC Cache

```
.cache/scf-quick-compare/{slug}.json
```

Stores the flat map of all SCF line names → amounts for the most recent run. On re-run, diffs against the prior cache and shows exactly what changed. Useful for catching mid-month QBO adjustments or reconciliation entries.

## Decimal Math

All calculations use Python `Decimal` with `ROUND_HALF_UP` — no floating-point rounding errors in financial outputs.

---
name: SCF Deep Analysis
slug: scf-deep-analysis
version: 1.0.0
description: >
  Controller-level Statement of Cash Flows deep analysis for QBO-connected
  clients. Computes CF Quality Ratio, Free Cash Flow, working capital movement
  drivers, 3-month rolling averages, GL drill-down for flagged accounts, and
  plain-English controller findings with HIGH/MEDIUM/LOW urgency action
  proposals. Outputs a 7-tab Excel workbook.
tags:
  - finance
  - accounting
  - cash-flow
  - qbo
  - excel

negative_boundaries:
  - Quick period-over-period CF snapshot → use scf-quick-compare instead
  - P&L variance analysis → use pl-deep-analysis skill
  - Budget vs. actual → use budget-vs-actual skill
  - 13-week rolling cash forecast → use cash-flow-forecast skill
---

# SCF Deep Analysis — SKILL.md

## What This Skill Does

Runs a controller-level Statement of Cash Flows deep analysis for a QBO-connected client. Extends the SCF Quick Compare with:

- **CF Quality Ratio**: Operating CF ÷ Net Income (>1.0 = cash-backed quality earnings)
- **Free Cash Flow**: Operating CF − CapEx (pulled from investing section)
- **Working Capital Movement**: AR, AP, Inventory, Prepaid — which WC components drove operating CF changes
- **3-Month Rolling Averages**: Per-account and per-section CF trend baseline
- **GL Drill-Down**: Vendor-level transaction detail for every flagged CF account
- **Controller Findings**: Plain-English narratives with urgency — "Operating CF decreased 15% because AR increased $8K (collections lagging) while AP decreased $4K (paying faster)"
- **Action Proposals**: Specific, actionable recommendations (HIGH / MEDIUM / LOW urgency)
- **CDC**: Tracks what changed in CF since last run

**Output**: Excel workbook with 7 tabs:
`Summary | Detail | ⚠ Flags | GL Drill-Down | Working Capital Movement | Controller Findings | CDC Log`

## When To Use

- Monthly close: controller-level CF review beyond the Quick Compare
- Client with CF concerns (declining operating CF, negative FCF, low CF quality)
- Board prep: need to explain *why* cash changed this period
- Internal review: identify AR collection problems, AP timing, capex overruns

## When NOT To Use

- Quick period-over-period CF snapshot → use `scf-quick-compare.py` instead
- P&L variance analysis → use `pl-deep-analysis.py`
- Budget vs. actual → use `budget-vs-actual` skill
- Non-QBO clients (no integration) → use `bank-reconciliation` skill

## Prerequisites

- QBO client connected for the slug
- QBO auth token configured
- `openpyxl` installed: `pip install openpyxl`
- Node.js available on PATH

## Script Location

```
scripts/pipelines/scf-deep-analysis.py
```

## Usage

```bash
# Basic: current month vs. prior month (auto-calculated)
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug my-client \
  --current-start 2026-03-01 --current-end 2026-03-31

# Explicit prior period
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug my-client \
  --current-start 2026-02-01 --current-end 2026-02-28 \
  --prior-start 2026-01-01 --prior-end 2026-01-31

# YTD vs prior YTD
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug my-client --ytd --year 2026

# Skip GL drill-down (faster — use when GL is unavailable or not needed)
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug my-client \
  --current-start 2026-03-01 --current-end 2026-03-31 --skip-gl

# Custom output directory
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug glowlabs \
  --current-start 2026-03-01 --current-end 2026-03-31 \
  --out ~/Desktop/reports

# Sandbox mode (QBO sandbox environment)
python3 scripts/pipelines/scf-deep-analysis.py \
  --slug glowlabs \
  --current-start 2026-03-01 --current-end 2026-03-31 --sandbox
```

## Arguments

| Argument | Required | Description |
|---|---|---|
| `--slug` | ✅ | Company slug (must match qbo-client connection) |
| `--current-start` | ✅* | Current period start YYYY-MM-DD |
| `--current-end` | ✅* | Current period end YYYY-MM-DD |
| `--prior-start` | ❌ | Prior period start — auto-calculated if omitted |
| `--prior-end` | ❌ | Prior period end — auto-calculated if omitted |
| `--ytd` | ❌ | YTD mode: Jan 1 → end of last completed month |
| `--year` | ❌ | Year for --ytd (default: current year) |
| `--skip-gl` | ❌ | Skip GL drill-down (faster run) |
| `--out` | ❌ | Output directory (default: ~/Desktop) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

*Required unless `--ytd` is used.

## Pipeline Steps (8 Steps)

1. **Pull CF** — current + prior period CF from QBO via `report {slug} cf`
2. **Pull P&L** — current + prior period P&L for Net Income extraction (CF quality ratio)
3. **Rolling Averages** — pull 3 prior months of CF; compute per-account and per-section averages
4. **CDC** — compare current flat map vs. `.cache/scf-deep-analysis/{slug}.json`
5. **CF Quality + FCF** — compute ratio + extract CapEx from investing section
6. **Working Capital Movement** — classify AR / AP / Inventory / Prepaid from operating section
7. **Variance + Flags + GL** — flag material variances (≥10% or ≥$2,500); pull GL for flagged accounts
8. **Controller Findings** — generate narrative findings with urgency and action proposals

## Output: Excel Workbook (7 Tabs)

### Tab 1: Summary
- Section totals: Operating / Investing / Financing / Net Change / Beginning / Ending Cash
- vs. prior period + 3-month rolling average
- CF Quality Ratio block (with color coding)
- Free Cash Flow calculation
- Controller Findings summary (top 8 findings, HIGH in red)

### Tab 2: Detail
- Every CF line item with prior period, current period, $ variance, % variance, rolling avg
- Section-grouped with color bands (Operating = blue, Investing = yellow, Financing = purple)
- F/U column (Favorable = more cash, Unfavorable = less cash)

### Tab 3: ⚠ Flags
- Material variances only: ≥10% change OR ≥$2,500 absolute delta
- Includes rolling avg comparison
- Sorted by absolute dollar variance

### Tab 4: GL Drill-Down
- Vendor-level transaction detail for every flagged account
- Top 3 vendor contributors shown in sub-header per account
- Max 50 transactions per account
- Skipped if `--skip-gl` flag used

### Tab 5: Working Capital Movement
- AR, AP, Inventory, Prepaid, Other WC — current vs. prior vs. delta
- Cash Impact column (Source / Use)
- Plain-English analysis note per component (e.g., "AR increased $8K — collections lagging sales")
- Total WC impact row

### Tab 6: Controller Findings
- Full narrative findings sorted HIGH → MEDIUM → LOW
- Detail / GL attribution per finding
- Specific recommended action per finding
- $ Impact column

### Tab 7: CDC Log
- All accounts/line items that changed vs. last cached run
- Prior value, current value, $ delta, % change, note (New / Changed / Removed)
- Color coded: green = cash increased, red = cash decreased

## Cache

```
.cache/scf-deep-analysis/{slug}.json
```

Stored after each run. Contains:
- `flat_map` — all CF line items and amounts
- `totals` — section totals (operating, investing, financing, net_change, ending_cash)
- `cf_quality` — CF quality label
- `fcf` — Free Cash Flow amount
- `net_income` — Net Income for the period
- `saved_at` — ISO date of last run

## Key Metrics Explained

### CF Quality Ratio
```
Operating CF / Net Income
```
| Ratio | Quality | Color |
|---|---|---|
| ≥ 1.0x | ✅ Quality Earnings — cash-backed | Green |
| 0.5–1.0x | ⚠ Adequate — partially cash-backed | Yellow |
| < 0.5x | ⚠ Low Quality — accrual-heavy | Orange |
| < 0 | 🔴 Cash Drain — cash negative despite reported profit | Red |

### Free Cash Flow
```
FCF = Operating CF + CapEx (CapEx is negative outflow, so this subtracts it)
```
CapEx detected by keyword matching in investing section (equipment, property, asset, capital expenditure, etc.)

### Working Capital Movement (in Operating section)
- **AR change** (negative = AR grew = cash used; positive = AR shrank = cash released)
- **AP change** (positive = AP grew = cash deferred; negative = AP shrank = cash paid out)
- **Inventory change** (negative = inventory built = cash used; positive = inventory drawn = cash released)
- **Prepaid change** (negative = more prepaid = cash used; positive = prepaid expensed = cash released)

## Materiality Thresholds

- **Percent threshold**: ≥10% change in any CF line item
- **Absolute threshold**: ≥$2,500 absolute variance
- **Both thresholds** checked — either triggers a flag

## Safety Rules

- **Read-only**: No writes to QBO — only pulls reports
- **All Decimal math**: No floating-point for financial calculations
- **Disclaimer footer**: Controller Findings tab includes audit disclaimer
- **Cache separation**: Uses `.cache/scf-deep-analysis/` — separate from scf-quick-compare cache

## Related Pipelines

| Pipeline | Use When |
|---|---|
| `scf-quick-compare.py` | Quick CF period-over-period snapshot (4 tabs, no GL) |
| `scf-deep-analysis.py` | Controller-level CF with GL drill-down, CF quality, FCF, WC movement |
| `pl-deep-analysis.py` | Same depth for P&L (not CF) |
| `budget-builder.py` | Build annual CF budget / BvA |

---
name: Payroll GL Reconciliation
slug: payroll-reconciliation
version: 1.0.0
description: >
  Reconcile QuickBooks Online payroll GL accounts against payroll provider
  reports (Gusto, ADP, Paychex) across 12 categories. Produces an 8-tab Excel
  workbook covering discrepancies, 941 tax recon, W-2 box verification,
  headcount, per-employee cost, and CDC logging. Integrates with
  the Month-End Close pipeline.
tags:
  - payroll
  - accounting
  - reconciliation
  - qbo
  - excel
  - 941
  - w2
negative_boundaries:
  - Bank statement reconciliation → use bank-reconciliation-agent skill
  - Broad P&L variance analysis → use pl-deep-analysis skill
  - Payroll tax preparation or filing advice → requires CPA review
  - Multi-entity payroll consolidation → not supported
---

# Payroll GL Reconciliation Skill

## What This Skill Does

Reconciles QuickBooks Online payroll GL accounts against payroll provider reports
(Gusto, ADP, Paychex) to catch missed journal entries, manual adjustments, and
tax filing discrepancies before they become audit findings or payroll tax penalties.

**Use when:**
- Closing a period and payroll GL needs to match the payroll register
- Preparing for 941 filing — verify GL matches what was reported
- W-2 season — build per-employee annual totals for box verification
- Client has a new payroll provider — validate initial period's entries
- Controller flagged unexplained payroll variance in the P&L

**Do NOT use when:**
- You need bank statement reconciliation → use `bank-reconciliation-agent`
- You need broad P&L variance analysis → use `pl-deep-analysis`
- You need payroll tax preparation or filing advice → CPA review required
- You need multi-entity payroll consolidation (not supported)

---

## Script Location

```
scripts/pipelines/payroll-reconciliation.py
```

---

## Quick Usage

```bash
# Monthly reconciliation
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --month 2026-03 \
  --payroll-file ~/Downloads/gusto-march-2026.csv

# Quarterly 941 reconciliation
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --quarter 2026-Q1 \
  --payroll-file ~/Downloads/gusto-q1-2026.csv \
  --form-941-file ~/Downloads/941-q1.csv

# Annual W-2 helper
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --year 2026 \
  --payroll-file ~/Downloads/gusto-annual-2026.csv \
  --w2-mode

# Custom date range
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --start 2026-01-01 --end 2026-06-30 \
  --payroll-file ~/Downloads/h1-2026.csv

# Provider analysis only (no QBO connection needed)
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --month 2026-03 \
  --payroll-file ~/Downloads/adp-report.csv \
  --skip-gl

# Custom output directory
python3 scripts/pipelines/payroll-reconciliation.py \
  --slug CLIENT_SLUG \
  --month 2026-03 \
  --payroll-file ~/Downloads/report.csv \
  --out ~/Desktop/reports
```

---

## Arguments

| Argument | Required | Description |
|---|---|---|
| `--slug` | Yes | Client QBO slug identifier |
| `--payroll-file` | Yes | Path to payroll provider CSV |
| `--month` | One of | YYYY-MM for single month |
| `--quarter` | One of | YYYY-Q1 through YYYY-Q4 |
| `--year` | One of | Full year (YYYY) for annual/W-2 |
| `--start` / `--end` | One of | Custom range (YYYY-MM-DD) |
| `--form-941-file` | No | Form 941 CSV for quarterly tax recon |
| `--w2-mode` | No | Enable full W-2 box verification output |
| `--skip-gl` | No | Skip QBO GL pull — provider analysis only |
| `--sandbox` | No | Use QBO sandbox environment |
| `--threshold` | No | Discrepancy threshold (default: $1.00) |
| `--out` | No | Output directory (default: current dir) |

---

## Provider Auto-Detection

The script auto-detects the payroll provider from CSV headers:

| Provider | Detection Signature |
|---|---|
| **Gusto** | Headers contain `Check Date` + `Gross Earnings` |
| **ADP** | Headers contain `Pay Date` + `Total Gross` |
| **Paychex** | Headers contain `Check Date` + `Regular Earnings` |
| **Generic** | Fallback — best-effort column matching |

For unsupported formats, rename CSV columns to match any of the above signatures,
or use the Generic fallback with standard column names (Gross, FIT, SIT, etc.).

---

## Reconciliation Categories

The pipeline reconciles **12 payroll categories** between GL and provider:

| # | Category | GL Keywords | 941 Line |
|---|---|---|---|
| 1 | Gross Wages | wages, salaries, payroll-salaries | Line 2 |
| 2 | Federal Income Tax (FIT) | federal withholding, fit, federal tax | Line 3 |
| 3 | State Income Tax (SIT) | state withholding, sit, state tax | — |
| 4 | SS Employer (FICA-SS ER) | social security employer, ss er | Line 5a |
| 5 | SS Employee (FICA-SS EE) | social security employee, ss ee | Line 5a |
| 6 | Medicare Employer (FICA-Med ER) | medicare employer, med er | Line 5c |
| 7 | Medicare Employee (FICA-Med EE) | medicare employee, med ee | Line 5c |
| 8 | FUTA / SUTA | futa, suta, unemployment tax | — |
| 9 | Health Insurance | health insurance, medical, dental | W-2 Box 12C |
| 10 | 401k / Retirement | 401k, retirement, pension | W-2 Box 12D |
| 11 | Workers Compensation | workers comp, wc premium | — |
| 12 | PTO Accrual | pto, vacation accrual, leave accrual | — |

**Discrepancy threshold:** ±$1.00 per category (configurable via `--threshold`).

---

## Excel Output (8 Tabs)

### Tab 1: Reconciliation Summary
- All 12 categories: GL amount vs provider amount vs difference
- MATCH / DISCREPANCY status per category
- Net difference and overall balance status
- Auto-suggested action for each discrepancy

### Tab 2: Category Detail
- GL transactions filtered and grouped by category
- Running total per category
- Full memo, account, and transaction type

### Tab 3: ⚠ Discrepancies
Three sections:
1. **Category discrepancies** — GL vs provider gaps with priority and action
2. **Missed journal entries** — payroll checks with no GL match
3. **Manual GL adjustments** — GL journal entries with no provider match

### Tab 4: Quarterly 941
- Compares provider totals to Form 941 line items
- Line 2 (wages), Line 3 (FIT), Lines 5a/5c (FICA), Line 6 (total taxes)
- Works with or without Form 941 upload (shows provider-only when no form)

### Tab 5: W-2 Helper
- Annual totals per employee in W-2 box format:
  - Box 1 (wages), Box 2 (FIT), Box 3/4 (SS), Box 5/6 (Medicare)
  - Box 12D (401k), Box 12C (health), Box 16/17 (state)
- Grand totals row for easy cross-check against W-3

### Tab 6: Headcount
- Active employee count per month
- New hire names and termination names per period
- Net headcount change month-over-month

### Tab 7: Per-Employee Cost
- Total employer cost per employee (wages + all taxes + benefits)
- Effective tax rate and total cost/wage ratio
- Sorted by highest total cost

### Tab 8: CDC Log
- Month-over-month changes in payroll cost by category
- New hire / termination events
- Discrepancy count changes (new or resolved)
- Flags material swings ≥10%

---

## Output File Naming

```
payroll-recon_{slug}_{period}.xlsx
```

Examples:
- `payroll-recon_sb-paulson_2026-03.xlsx`
- `payroll-recon_willo-salons_2026-Q1.xlsx`
- `payroll-recon_glowlabs_2026.xlsx`

---

## Dependencies

```bash
pip install openpyxl
```

Node.js QBO client (auth token required for GL pull).

---

## CDC Cache

Change data is persisted at:
```
.cache/payroll-reconciliation/{slug}.json
```

Keeps last 24 periods. Delete to reset baseline.

---

## Workflow: Monthly Close Integration

This pipeline integrates with the Month-End Close checklist
(`scripts/pipelines/month-end-close.py`). Run sequence:

1. Get payroll provider report (CSV export from Gusto/ADP/Paychex)
2. Run `payroll-reconciliation.py` for the closed month
3. Review Tab 3 (Discrepancies) — post any missed JEs before closing
4. File Form 941 (quarterly) — use Tab 4 to verify GL matches before filing
5. W-2 season (January) — run with `--year` + `--w2-mode` for Box verification

---

## Form 941 CSV Format

If providing a 941 CSV (`--form-941-file`), the pipeline expects columns:

```
line, amount
```

Example rows:
```
Line 2 - Total wages,  125000.00
Line 3 - Federal income tax withheld,  18750.00
Line 5a - Social security wages,  15500.00
Line 5c - Medicare wages,  3625.00
Line 6 - Total taxes,  37875.00
```

Column names are matched case-insensitively. Extra columns are ignored.

---

## Common Issues

**"QBO CLI error (exit 1)"**
→ Refresh the QBO auth token for this client slug.

**"Payroll file not found"**
→ Verify the path in `--payroll-file`. Use absolute path if needed.

**"Provider detected: Generic"**
→ Column headers didn't match Gusto/ADP/Paychex signatures. Results are best-effort.
→ Rename columns or use the Generic field names listed in the parser section.

**All categories show $0 for GL**
→ GL pull succeeded but no accounts matched payroll keywords.
→ Check QBO account names — they may use custom names. Add keywords to `PAYROLL_ACCOUNT_KEYWORDS`.

**Discrepancy threshold too sensitive**
→ Use `--threshold 5.00` to allow up to $5 variance (useful for rounding-heavy providers).

---

## Architecture Notes

- **All financial math uses `Decimal`** — no float arithmetic anywhere.
- **Provider detection is header-based** — no reliance on file naming conventions.
- **GL categorization is keyword-based** — matches account name + memo combined.
- **Missed JE detection** matches net pay amounts within ±$1 threshold.
- **Manual adjustment detection** flags GL journal entries with no provider counterpart.
- **CDC is cumulative** — each run appends to the slug's JSON cache file.
- **W-2 Box 1** = gross wages minus pre-tax deductions (401k + Section 125 health).
- **W-2 Box 3/5** = gross wages (simplified — doesn't cap SS wage base per check).
  For exact SS wage base cap ($176,100 in 2026), verify high-earner employees manually.

---
name: Financial Ratio Analysis
slug: financial-ratios
version: 1.0.0
description: >
  Compute and report 25+ financial ratios (profitability, liquidity, leverage,
  efficiency, growth) from QBO data. Produces a 9-tab Excel workbook with
  traffic-light scoring, DuPont decomposition, Altman Z-Score, 6-month trend
  charts, trend-reversal detection, and CDC logging. Integrates with
  the Month-End Close pipeline.
tags:
  - finance
  - accounting
  - ratios
  - qbo
  - excel
negative_boundaries:
  - Bank reconciliation → use bank-reconciliation skill
  - P&L line-item variance analysis → use pl-deep-analysis skill
  - Cash flow forecasting → use cash-flow-forecast skill
  - AR collections aging → use ar-collections skill
  - Budget vs. actual → use budget-vs-actual skill
---

# Financial Ratio Analysis — SKILL.md

---

## When to Use This Skill

Use when a user asks for:
- Financial ratio analysis for a QBO-connected client
- Profitability, liquidity, leverage, or efficiency metrics
- DuPont decomposition or Altman Z-Score analysis
- Month-over-month, QoQ, or YoY ratio comparison
- Ratio benchmarking against industry averages
- Trend analysis across 6 months of ratios
- Trend reversal detection for any financial ratio
- CDC (change data capture) on ratio movements

**NOT for:**
- Bank reconciliation → use `bank-reconciliation`
- P&L variance analysis (line-item drill-down) → use `pl-deep-analysis`
- Cash flow forecasting → use `cash-flow-forecast`
- AR collections aging → use `ar-collections`
- Budget vs. actual → use `budget-vs-actual`

---

## Quick Invocation

```bash
# Standard monthly ratio report
python3 scripts/pipelines/financial-ratios.py --slug sb-paulson --month 2026-03

# Custom output directory
python3 scripts/pipelines/financial-ratios.py --slug <client-slug> --month 2026-03 --out ~/Desktop/reports

# QBO sandbox
python3 scripts/pipelines/financial-ratios.py --slug sb-paulson --month 2026-03 --sandbox
```

Unknown slugs use default thresholds and disable inventory/valuation ratios unless SOP file found.

---

## What It Produces

**Excel workbook** saved to `~/Desktop` (or `--out` path):
`FinancialRatios_{slug}_{YYYY_MM}.xlsx`

| Tab | Contents |
|-----|----------|
| Ratio Summary | All ratios with traffic lights, benchmarks, DuPont & Altman summary |
| Profitability | Gross/Operating/Net margins, ROA, ROE, EBITDA margin + 6-mo trend |
| Liquidity | Current/Quick/Cash ratios, Working capital + 6-mo trend |
| Leverage | D/E, D/A, Interest coverage, Equity multiplier + 6-mo trend |
| Efficiency | Asset/Inventory/Receivable/Payable turnover, DSO, DPO, CCC + 6-mo trend |
| Growth | Revenue (MoM/QoQ/YoY), Expense, Net income growth + 6-mo trend |
| DuPont Analysis | 3-factor + 5-factor ROE decomposition, 6-month component trend |
| Trends | All ratios × 6 months with direction arrows + trend reversal flags |
| CDC Log | Ratio changes vs. prior run with trend reversal detection |

---

## Ratio Categories

### Profitability
| Ratio | Formula |
|-------|---------|
| Gross Margin % | Gross Profit / Revenue × 100 |
| Operating Margin % | EBIT / Revenue × 100 |
| Net Margin % | Net Income / Revenue × 100 |
| EBITDA Margin % | EBITDA / Revenue × 100 |
| ROA | Net Income / Total Assets × 100 |
| ROE | Net Income / Total Equity × 100 |

### Liquidity
| Ratio | Formula |
|-------|---------|
| Current Ratio | Current Assets / Current Liabilities |
| Quick Ratio | (Current Assets − Inventory) / Current Liabilities |
| Cash Ratio | Cash / Current Liabilities |
| Working Capital | Current Assets − Current Liabilities |
| Working Capital Ratio | Working Capital / Total Assets × 100 |

### Leverage
| Ratio | Formula |
|-------|---------|
| Debt-to-Equity | Total Liabilities / Total Equity |
| Debt-to-Assets | Total Liabilities / Total Assets |
| Interest Coverage | EBIT / Interest Expense |
| Equity Multiplier | Total Assets / Total Equity |

### Efficiency
| Ratio | Formula |
|-------|---------|
| Asset Turnover | Revenue / Total Assets |
| Inventory Turnover | COGS / Inventory (annualized) |
| Receivable Turnover | Revenue / Accounts Receivable |
| DSO (Days Sales Outstanding) | AR / (Revenue / Days) |
| Payable Turnover | COGS / Accounts Payable |
| DPO (Days Payable Outstanding) | AP / (COGS / Days) |
| Cash Conversion Cycle | DSO + DIO − DPO |

### Growth
| Ratio | Formula |
|-------|---------|
| Revenue Growth MoM | (Current − Prior) / |Prior| × 100 |
| Revenue Growth QoQ | (Current − 3mo ago) / |3mo ago| × 100 |
| Revenue Growth YoY | (Current − 12mo ago) / |12mo ago| × 100 |
| Expense Growth | (Current Opex − Prior Opex) / |Prior Opex| × 100 |
| Net Income Growth | (Current NI − Prior NI) / |Prior NI| × 100 |

### Valuation (SOP-gated)
| Ratio | Formula |
|-------|---------|
| Revenue Multiple | Enterprise Value / (Revenue × 12) |
| EBITDA Multiple | Enterprise Value / (EBITDA × 12) |

---

## DuPont Analysis

### Three-Factor Model
```
ROE = Net Margin × Asset Turnover × Equity Multiplier
```

### Five-Factor Model
```
ROE = Tax Burden × Interest Burden × EBIT Margin × Asset Turnover × Equity Multiplier
```

Both models computed; trend shown for each component across 6 months.

---

## Altman Z-Score

Bankruptcy risk indicator. Model selected automatically by `entity_type` in SOP config:

| Model | Used For | Zones |
|-------|----------|-------|
| Z' (Revised) | Private/Services (default) | Safe >2.9 \| Grey 1.23-2.9 \| Distress <1.23 |
| Z (Original) | Manufacturing | Safe >2.99 \| Grey 1.81-2.99 \| Distress <1.81 |

**Formula (Z' model):**
`Z' = 0.717×(WC/TA) + 0.847×(RE/TA) + 3.107×(EBIT/TA) + 0.420×(BVE/TL) + 0.998×(Revenue/TA)`

---

## Traffic Light Scoring

Each ratio scores GREEN / YELLOW / RED based on thresholds configured per client SOP.
- GREEN: Within healthy range
- YELLOW: Watch — approaching threshold
- RED: Action required

Thresholds are configured in `CLIENT_CONFIGS` within the script. Override by editing the dict or adding a client SOP file.

---

## Trend Reversal Detection

The pipeline detects when a ratio **reverses direction** over the 6-month window:
- Compares direction of first half vs. second half of history
- Flags `⚡ Trend Reversal — Now Improving` or `⚠ Trend Reversal — Now Declining`
- Flagged prominently in Trends tab and CDC Log

---

## Client SOP Integration

Place a `sop.md` file at `clients/{slug}/sop.md`. The pipeline reads it to detect:

| SOP Signal | Effect |
|-----------|--------|
| "POS collection" / "no accounts receivable" | Disables AR ratios (DSO, receivable turnover) |
| "no inventory" / "service-based" | Disables inventory ratios |
| "manufacturing" / "distribution" | Switches to original Altman Z model |
| `enterprise value: $X` | Enables valuation ratios (revenue/EBITDA multiples) |
| `benchmark: {key} {value}` | Overrides specific benchmark values |

---

## Client Configuration

Add clients to `CLIENT_CONFIGS` in the script with:
- `ratios_enabled` list (which ratios to compute/display)
- `thresholds` dict per ratio: `{"green": (min, max), "yellow": (min, max)}`
- `benchmarks` dict per ratio: Decimal values
- `entity_type`: `"services"` or `"manufacturing"` (for Altman Z model)

---

## QBO Data Pulled

| Report | Purpose |
|--------|---------|
| P&L (current month) | Revenue, COGS, OpEx, Net Income, Interest, D&A |
| Balance Sheet (current month-end) | Assets, Liabilities, Equity, Cash, AR, Inventory, AP |
| Cash Flow (current month) | Operating/Investing/Financing cash flows |
| P&L (prior month) | MoM growth ratios |
| P&L (3 months ago) | QoQ growth ratio |
| P&L (12 months ago) | YoY growth ratio |
| All of the above × 6 months | Historical trend analysis |

**Total QBO calls:** ~17 per run (6-month history + current + prior + QoQ + YoY)

---

## CDC Cache

Cache stored at: `.cache/financial-ratios/{slug}.json`

Tracks:
- All ratio values from prior run
- Detects per-ratio changes (delta + % change)
- Flags trend reversals when 6-month history available
- Compares run-over-run for drift monitoring

---

## Technical Notes

- All financial math: **Python Decimal** (no floating-point errors)
- Division-by-zero: returns `ZERO`, never crashes
- Missing QBO lines: `ZERO` (safe fallback), does not inflate ratios
- Inventory turnover: annualized (× 12 for monthly periods)
- Interest coverage = 999.99 when no interest expense (signals no debt burden)
- Altman Z-Score uses **book value of equity** (not market cap) for private company model
- EBITDA = EBIT + D&A; EBIT = Net Income + Interest + Taxes
- Operating margin uses EBIT as proxy for operating income if QBO doesn't separate it

---

## Files

| File | Description |
|------|-------------|
| `scripts/pipelines/financial-ratios.py` | Main pipeline script |
| `skills/financial-ratios/SKILL.md` | This file |
| `.cache/financial-ratios/{slug}.json` | CDC cache per client |

---

---
name: BS Quick Compare
slug: bs-quick-compare
version: 1.0.0
description: >
  Period-over-period variance analysis on the Balance Sheet pulled from
  QuickBooks Online. Outputs a 4-tab Excel workbook: Summary, Detail, Flags,
  CDC Log. Covers Assets, Liabilities, and Equity sections with accounting
  equation validation and BS-specific analysis notes on flagged items.
tags:
  - finance
  - accounting
  - balance-sheet
  - qbo
  - excel
negative_boundaries:
  - P&L variance analysis → use pl-quick-compare skill
  - Cash flow analysis → use scf-quick-compare skill
  - AR aging deep-dive → use ar-collections skill
  - Financial projections or forecasting → use cash-flow-forecast skill
  - Full three-statement analysis → run all three compare pipelines separately
---

# BS Quick Compare — Skill

## What This Skill Does

Runs a period-over-period variance analysis on the **Balance Sheet (BS)** pulled directly from QuickBooks Online. Outputs a 4-tab Excel workbook: Summary | Detail | Flags | CDC Log.

Mirrors the `pl-quick-compare` and `scf-quick-compare` patterns but for the Balance Sheet — Assets / Liabilities / Equity sections, accounting equation validation, and BS-specific analysis notes on flagged items tying changes to SCF, AR/AP aging, capex, debt schedule, and equity activity.

## When to Use

**Use when:**
- A client needs month-end or YTD balance sheet comparison
- Reviewing BS as part of monthly close deliverables
- Investigating material shifts in assets, liabilities, or equity
- Client asks: "why did our AR/AP/cash/debt change?" or "what happened to our equity?"
- Pre-loan/investor analysis requiring clear BS trend visibility

**NOT for:**
- P&L variance analysis → use `pl-quick-compare.py`
- Cash flow analysis → use `scf-quick-compare.py`
- AR aging deep-dive → use `ar-collections`
- Financial projections or forecasting → use `cash-flow-forecast.py`
- Full three-statement analysis (run all three compare pipelines and cross-reference)

## Script Location

```
scripts/pipelines/bs-quick-compare.py
```

## Requirements

- `pip install openpyxl` (already installed in workspace)
- Node.js QBO client with valid auth token
- QBO credentials configured

## Usage

```bash
# Current month-end vs. prior month-end (auto-detects prior)
python3 scripts/pipelines/bs-quick-compare.py \
    --slug sb-paulson \
    --current-end 2026-03-31

# Explicit prior period
python3 scripts/pipelines/bs-quick-compare.py \
    --slug sb-paulson \
    --current-end 2026-02-28 --prior-end 2026-01-31

# YTD (as of end of last completed month vs. same date prior year)
python3 scripts/pipelines/bs-quick-compare.py \
    --slug sb-paulson --ytd --year 2026

# Full date control (for QBO BS reports needing explicit start dates)
python3 scripts/pipelines/bs-quick-compare.py \
    --slug sb-paulson \
    --current-start 2026-01-01 --current-end 2026-03-31 \
    --prior-start 2025-01-01 --prior-end 2025-03-31

# Custom output directory
python3 scripts/pipelines/bs-quick-compare.py \
    --slug glowlabs \
    --current-end 2026-03-31 \
    --out ~/Desktop/reports

# Sandbox mode (QBO sandbox environment)
python3 scripts/pipelines/bs-quick-compare.py \
    --slug glowlabs \
    --current-end 2026-03-31 \
    --sandbox
```

## Arguments

| Flag | Required | Description |
|------|----------|-------------|
| `--slug` | ✅ | Company slug (must be connected in qbo-client) |
| `--current-end` | ✅* | Current period "as of" date (YYYY-MM-DD) |
| `--current-start` | ❌ | Current period start (default: Jan 1 same year) |
| `--prior-end` | ❌ | Prior period "as of" date (default: 1 month back) |
| `--prior-start` | ❌ | Prior period start (default: Jan 1 same year as prior-end) |
| `--ytd` | ✅* | YTD mode (alternative to explicit dates) |
| `--year` | ❌ | Year for --ytd (default: current year) |
| `--out` | ❌ | Output directory (default: ~/Desktop) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

*Either `--current-end` OR `--ytd` is required.

**Balance Sheet date note:** BS is a point-in-time statement. `--current-end` and `--prior-end` are the "as of" dates. The QBO CLI requires start/end dates even for BS — the script defaults `--current-start` to Jan 1 of the same year if omitted.

## Output

Excel file: `BS_QuickCompare_{slug}_{as-of-date}.xlsx` saved to Desktop (or `--out` directory).

### Tab 1: Summary
- Total Assets / Total Liabilities / Total Equity (current vs prior, $ change, % change, F/U)
- Total Liabilities + Equity (cross-check row)
- Accounting equation validation: `Total Assets = Total Liabilities + Total Equity` (both periods, ≤$1 tolerance)

### Tab 2: Detail
- Every BS line item with hierarchy preserved
- Prior period | Current period | $ Change | % Change | F/U label
- Color-coded by section (Assets = blue, Liabilities = gold, Equity = purple)

### Tab 3: ⚠ Flags
- Material changes: **≥10% change OR ≥$2,500 absolute**
- Analysis note for each flagged item — plain-English explanation tying the change to relevant follow-up actions
- BS-specific cross-references (see Analysis Notes section below)

### Tab 4: CDC Log
- Change Data Capture: compares current BS flat map against last cached run
- First run: full snapshot saved (no deltas)
- Subsequent runs: shows exactly what line items changed since last run
- Cache location: `.cache/bs-quick-compare/{slug}.json`

## BS Logic

### Section Classification
Each QBO BS row is classified into sections by keyword matching:
- **Assets**: cash, bank, accounts receivable, inventory, prepaid, property, equipment, fixed asset, accumulated depreciation, investment, due from, notes receivable
- **Liabilities**: liabilit, accounts payable, accrued, loan, note payable, line of credit, mortgage, deferred revenue, sales tax, payroll, credit card, due to
- **Equity**: equity, owner, capital, retained earnings, net income, distribution, contribution, member, shareholder, partner, opening balance equity, paid-in, common stock

Children inherit their parent section classification.

### Variance F/U Logic
| Section | Increase = | Decrease = |
|---------|-----------|-----------|
| Assets | ✓ Favorable | ✗ Unfavorable |
| Liabilities | ✗ Unfavorable | ✓ Favorable |
| Equity | ✓ Favorable | ✗ Unfavorable |

Strategic exceptions (new LOC for growth, timely AP buildup) are noted in the analysis notes column.

### Accounting Equation Validation
```
Total Assets = Total Liabilities + Total Equity   (≤$1 tolerance)
```
Runs on both periods and displayed in Summary tab with pass/fail icons.

### YTD Mode
`--ytd`: Current = as of end of last completed month. Prior = same date in prior year.
Example: run on March 17, 2026 → Current = as of Feb 28, 2026 | Prior = as of Feb 28, 2025.

## Analysis Notes (Flags Tab)

The Flags tab includes an **Analysis Note** column with BS-specific interpretation for each material change:

| Line Item | Analysis Note Focus |
|-----------|-------------------|
| Cash / Bank | Tie to SCF — identify source: operating CF, capex, debt, distribution |
| Accounts Receivable | Run AR aging — check DSO, past-due balances, collection trends |
| Inventory | Monitor turnover — buildup vs. drawdown relative to sales pace |
| Prepaid Expenses | Amortization vs. new prepaid spend |
| Fixed Assets / PP&E | Capex vs. disposal — verify depreciation schedule updated |
| Accumulated Depreciation | Confirm D&A add-back in SCF operating section |
| Related-party Receivables | Repayment terms and tax treatment of officer/shareholder loans |
| Accounts Payable | Run AP aging — confirm no overdue payables; payment terms intact |
| Credit Cards | Full payment schedule; confirm all charges categorized |
| Loans / Notes Payable | New borrowing vs. paydown — tie to SCF financing; verify debt schedule |
| Line of Credit | Draw vs. paydown — monitor utilization rate and available capacity |
| Mortgage | Normal amortization vs. lump-sum payment — confirm against schedule |
| Deferred Revenue | Cash received vs. revenue not yet earned — monitor recognition schedule |
| Payroll / Accrued | Settlement timing — verify no aged accruals outstanding |
| Sales Tax Payable | Confirm remittance current; no penalties |
| Retained Earnings | Tie to net income in P&L |
| Current Year Net Income | Deep-dive in P&L quick compare |
| Distributions / Draws | Verify cash availability; tie to SCF financing section |
| Equity Contributions | Tie to SCF financing; confirm cap table updated |
| Opening Balance Equity | Should zero out once books are fully set up |

## CDC Cache

```
.cache/bs-quick-compare/{slug}.json
```

Stores the flat map of all BS line names → balances for the most recent run. On re-run, diffs against the prior cache and shows exactly what changed. Useful for catching mid-month QBO adjustments, journal entries, or bank feed imports.

## Decimal Math

All calculations use Python `Decimal` with `ROUND_HALF_UP` — no floating-point rounding errors in financial outputs.

## Related Pipelines

Run all three compare pipelines for a complete monthly close package:

| Pipeline | Script | What it covers |
|----------|--------|---------------|
| P&L Quick Compare | `pl-quick-compare.py` | Revenue, COGS, expenses, net income |
| SCF Quick Compare | `scf-quick-compare.py` | Cash flows: operating, investing, financing |
| **BS Quick Compare** | `bs-quick-compare.py` | **Assets, liabilities, equity positions** |

Cross-reference flags across all three:
- Cash change on BS → tie to SCF net change
- AR/AP change on BS → tie to aging pipelines
- Net income on BS → tie to P&L net income
- Loan changes on BS → tie to SCF financing section

---
name: BS Deep Analysis
slug: bs-deep-analysis
version: 1.0.0
description: >
  Controller-level Balance Sheet deep analysis from QuickBooks Online. Pulls
  current and prior period BS, runs 3-month rolling averages, GL drill-down
  for material changes, and generates a 7-tab Excel workbook with actionable
  findings. Covers working capital health, current ratio flags, equity
  rollforward, and common-size vertical analysis.
tags:
  - finance
  - accounting
  - balance-sheet
  - qbo
  - excel
negative_boundaries:
  - Cash flow statement analysis → use scf-deep-analysis or scf-quick-compare
  - P&L variance analysis → use pl-deep-analysis skill
  - Multi-entity consolidations → not supported (single-entity only)
  - Real-time balance changes → snapshot-based, not bank-feed
---

# BS Deep Analysis

## What This Skill Does

Controller-level Balance Sheet deep analysis from QuickBooks Online. Mirrors the P&L Deep Analysis pattern but for the balance sheet — pulls current + prior period BS, runs 3-month rolling averages, GL drill-down for material changes, and generates a 7-tab Excel workbook with actionable findings.

**Use when:**
- Monthly close deliverable needs a BS review (not just a P&L comparison)
- Client needs working capital health check or current ratio flag
- Equity rollforward reconciliation is needed for a close
- Material balance changes on cash, AR, inventory, or debt need narrative explanation
- Common-size (vertical) analysis is required for a lender or board report

**NOT for:**
- Cash flow statement analysis — use a dedicated CF pipeline
- P&L variance analysis — use `pl-deep-analysis.py`
- Multi-entity consolidations — this is a single-entity BS pipeline
- Real-time balance changes (it's snapshot-based, not bank-feed)

---

## Pipeline Location

```
scripts/pipelines/bs-deep-analysis.py
```

Cache directory: `.cache/bs-deep-analysis/{slug}.json`

---

## Usage

```bash
# Current month-end vs. auto prior month-end
python3 scripts/pipelines/bs-deep-analysis.py \
  --slug sb-paulson --current-end 2026-03-31

# Explicit prior period
python3 scripts/pipelines/bs-deep-analysis.py \
  --slug sb-paulson \
  --current-end 2026-03-31 --prior-end 2026-02-28

# Skip GL drill-down (faster, no vendor-level detail)
python3 scripts/pipelines/bs-deep-analysis.py \
  --slug sb-paulson --current-end 2026-03-31 --skip-gl

# Custom output directory
python3 scripts/pipelines/bs-deep-analysis.py \
  --slug glowlabs --current-end 2026-03-31 --out ~/Desktop/reports

# QBO sandbox
python3 scripts/pipelines/bs-deep-analysis.py \
  --slug sb-paulson --current-end 2026-03-31 --sandbox
```

---

## Arguments

| Argument | Required | Description |
|---|---|---|
| `--slug` | ✅ | QBO company slug (must be connected in qbo-client) |
| `--current-end` | ✅ | As-of date for current BS (YYYY-MM-DD) |
| `--prior-end` | ❌ | Prior period as-of date (auto = prior month-end) |
| `--skip-gl` | ❌ | Skip GL drill-down (faster) |
| `--out` | ❌ | Output directory (default: ~/Desktop) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

---

## What It Pulls from QBO

1. **Balance Sheet** (as-of) — current period
2. **Balance Sheet** (as-of) — prior period
3. **Balance Sheet** (as-of) — 3 prior month-ends for rolling averages
4. **General Ledger** — current period GL for flagged accounts (unless `--skip-gl`)
5. **P&L** — current period net income for equity rollforward

---

## Analysis Modules

### 1. Horizontal Analysis (Period-over-Period)
- Every BS line: prior → current → $ change → % change
- Material threshold: ≥$2,500 absolute OR ≥10% change rate
- Flagged accounts sorted by absolute dollar change

### 2. Vertical Analysis (Common-Size)
- Every BS line as % of total assets
- Prior period % vs. current period %
- % point change highlights structural shifts

### 3. 3-Month Rolling Averages
- Pulls 3 prior month-end BS snapshots
- Per-account rolling average as trend baseline
- Rolling delta and rolling % vs. current balance

### 4. GL Drill-Down
- Transaction-level detail for all flagged accounts
- Vendor/payee aggregation: top contributors by dollar
- Max 50 transactions per account (configurable via `GL_MAX_ROWS_PER_ACCOUNT`)

### 5. Working Capital Deep Dive
- Current assets vs. current liabilities decomposition
- Current ratio, quick ratio, cash ratio
- WC delta decomposed: cash change, AR change, inventory change, AP change
- Health classification: HEALTHY / WATCH / CRITICAL

### 6. Debt Schedule Analysis
- Short-term vs. long-term debt split
- D/E ratio and D/A ratio
- ST concentration warning: flags if ST > 60% of total debt
- Leverage risk classification: LOW / LOW-MEDIUM / MEDIUM / HIGH

### 7. Equity Rollforward
```
Beginning Equity
+ Net Income
− Distributions / Owner Draws
+ New Contributions / Paid-in Capital
= Computed Ending Equity
vs. Ending Equity per BS (reconciling difference flagged if ≥ $500)
```
- Retained earnings bridge separately
- Reconciling difference investigation prompt

### 8. Controller Findings
- Narrative: "Cash decreased $45K because AP payments of $60K exceeded collections of $15K"
- Urgency-tagged: HIGH / MEDIUM / LOW
- GL vendor attribution embedded in findings

### 9. Action Proposals
- Specific recommended actions per finding
- Urgency-ranked: HIGH → MEDIUM → LOW
- Categories: LIQUIDITY, CASH MANAGEMENT, COLLECTIONS, INVENTORY, DEBT MANAGEMENT, LEVERAGE, EQUITY INTEGRITY, BALANCE SHEET

### 10. CDC (Change Data Capture)
- Compares current BS flat map vs. prior run cache
- Tracks: new accounts, removed accounts, balance changes
- Cache saved to `.cache/bs-deep-analysis/{slug}.json`

---

## Excel Output — 7 Tabs

| Tab | Contents |
|---|---|
| **Summary** | KPI table, key ratios, controller findings, action proposals |
| **Detail** | Full BS with prior/current/delta/rolling avg per account |
| **⚠ Flags** | Material change accounts + findings summary |
| **GL Drill-Down** | Transaction-level detail for flagged accounts |
| **Common-Size Analysis** | Vertical analysis: each line as % of total assets |
| **Equity Rollforward** | Period reconciliation + retained earnings bridge |
| **CDC Log** | Balance changes vs. last pipeline run |

---

## Materiality Thresholds

| Metric | Threshold |
|---|---|
| Absolute change | ≥ $2,500 |
| Percentage change | ≥ 10% |
| Equity change (tighter) | ≥ 5% |
| Working capital watch | Current ratio < 1.5x |
| Working capital critical | Current ratio < 1.0x |
| ST debt concentration warning | ST debt > 60% of total |
| Equity rollforward diff flag | ≥ $500 |

---

## Dependencies

```
pip install openpyxl
Node.js QBO client with valid auth token
```

QBO auth token must be set (same as all other pipelines).

---

## Related Pipelines

| Pipeline | File | When to Use |
|---|---|---|
| P&L Deep Analysis | `pl-deep-analysis.py` | Income statement controller review |
| Financial Ratios | `financial-ratios.py` | Full ratio suite (uses BS data) |
| BS Deep Analysis | `bs-deep-analysis.py` | **This pipeline** — balance sheet focus |

---

## Notes

- All math uses Python `Decimal` — no float rounding errors
- GL account names must match BS account names for drill-down attribution (QBO may use slightly different names between reports)
- Prior month-end is auto-calculated if `--prior-end` is omitted (always safe for monthly close)
- `--skip-gl` reduces runtime significantly; use for quick runs when vendor detail is not needed
- CDC cache is per-slug; running for a new slug always starts fresh (first run snapshot only)

---
name: client-dashboard
description: >
  Generates a client-facing executive KPI dashboard from QuickBooks Online data. Produces an
  Excel workbook with traffic-light scoring, 6-month trend sparklines, client-specific watch
  items, and a CDC log tracking KPI changes month-over-month. Use after monthly close to
  deliver the final executive summary deliverable to clients. NOT a substitute for P&L variance
  analysis, not for mid-month snapshots, and not for clients without QBO integration.
version: 1.0.0
tags:
  - finance
  - accounting
  - dashboard
  - KPI
  - client-reporting
  - QBO
updated: 2026-03-18
---

# Client Dashboard / KPI Report — SKILL.md

## What This Skill Does

Generates a client-facing executive KPI dashboard from QuickBooks Online data. Produces an Excel workbook with traffic-light scoring, 6-month trend sparklines, client-specific watch items, and a CDC log tracking KPI changes month-over-month.

## When To Use

- Monthly close is complete and it's time to generate the client dashboard
- User asks for KPI report, dashboard, or executive summary for any client
- After running P&L Quick Compare and bank rec — this is the final deliverable step

## When NOT To Use

- NOT a substitute for P&L Quick Compare (different purpose — this is executive summary, not variance analysis)
- NOT for mid-month snapshots — designed for complete monthly periods
- NOT for YTD / annual reports — use P&L Deep Analysis for those
- NOT for clients without QBO integration (no data source)

---

## Pipeline: `scripts/pipelines/client-dashboard.py`

### Prerequisites
```bash
pip install openpyxl
# Node.js qbo-client must be authenticated for the target slug
```

### Usage

```bash
# Example — March 2026
python3 scripts/pipelines/client-dashboard.py --slug <client-slug> --month 2026-03

# Custom output directory
python3 scripts/pipelines/client-dashboard.py --slug <client-slug> --month 2026-03 --out ~/Desktop/reports

# QBO sandbox
python3 scripts/pipelines/client-dashboard.py --slug <client-slug> --month 2026-03 --sandbox
```

### Arguments

| Argument | Required | Description |
|----------|----------|-------------|
| `--slug` | ✅ | Company slug (must match qbo-client connection) |
| `--month` | ✅ | Report month: `YYYY-MM` format |
| `--out` | ❌ | Output directory (default: `~/Desktop`) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

---

## Output: Excel Workbook

**Filename:** `KPI_Dashboard_{slug}_{YYYY_MM}.xlsx`

| Tab | Contents |
|-----|----------|
| **Executive Summary** | Headline numbers + traffic-light KPI table with benchmarks |
| **KPI Scorecard** | Full KPI detail with definitions, thresholds, and score |
| **Trends** | 6-month KPI trend with sparklines (↑↗→↘↓ + block chars) |
| **Cash Position** | Balance sheet cash, CF summary, liquidity ratios, runway |
| **Watch Items** | SOP-driven priority items surfaced prominently |
| **CDC Log** | Month-over-month KPI delta (what changed since last run) |

---

## KPIs Computed

| KPI | Formula | Unit |
|-----|---------|------|
| Revenue MoM Growth | `(curr_rev - prior_rev) / prior_rev × 100` | % |
| Revenue YoY Growth | `(curr_rev - yoy_rev) / yoy_rev × 100` | % |
| Gross Margin % | `gross_profit / revenue × 100` | % |
| Gross Margin (3-Mo Avg) | Rolling 3-month GP/Revenue | % |
| Net Margin % | `net_income / revenue × 100` | % |
| OpEx Ratio | `total_opex / revenue × 100` | % |
| Interest Expense Ratio | `interest_expense / revenue × 100` | % |
| Current Ratio | `current_assets / current_liabilities` | x |
| Quick Ratio | `(current_assets - inventory) / current_liabilities` | x |
| Debt-to-Equity | `total_liabilities / total_equity` | x |
| DSO | `ar / (revenue / days)` | days |
| DPO | `ap / (cogs / days)` | days |
| Cash Runway | `cash / monthly_burn` | months |
| Retail % of Revenue | `retail_revenue / total_revenue × 100` | % |

**All math uses Python `Decimal` for precision.**

---

## Traffic Light Scoring

Each KPI is scored GREEN / YELLOW / RED based on configurable thresholds per client SOP.

```
🟢 GREEN  = On target (within green band)
🟡 WATCH  = Approaching threshold (yellow band)
🔴 ACTION = Below/above acceptable range (outside yellow band)
⬜ N/A    = KPI not applicable or not configured
```

Thresholds are defined in `CLIENT_CONFIGS` in the script — one config block per client slug.

---

## Client SOP Integration

### Adding a New Client
Add a block to `CLIENT_CONFIGS` in the script:
```python
"new-slug": {
    "company_name": "Company Name",
    "industry": "Industry",
    "has_ar": True,
    "has_headcount": False,
    "kpis_enabled": ["revenue_mom", "gross_margin", ...],
    "thresholds": {
        "gross_margin": {"green": (Decimal("45"), None), "yellow": (Decimal("35"), Decimal("45"))},
        ...
    },
    "watch_items": [...],
    "benchmarks": {...},
    "benchmark_source": "Source description",
}
```

---

## CDC (Change Data Capture)

Cache stored at: `.cache/client-dashboard/{slug}.json`

- First run: saves full KPI snapshot, CDC tab shows "First run" message
- Subsequent runs: diffs current KPIs vs. prior run
- CDC log shows: KPI label | Prior | Current | Delta | % Change | Improved/Declined

---

## Trend Sparklines

6-month trend for each KPI uses two formats:
1. **Direction arrows:** `↑↑ ↑ ↗ → ↘ ↓ ↓↓` (based on % change over period)
2. **Block bars:** `█▇▅▃▁_` (relative to max value — in Trends tab mini-chart section)

---

## Peer Benchmarks

Manual config only. Benchmark values live in `CLIENT_CONFIGS[slug]["benchmarks"]`.
Source attribution displayed in every tab footer.
To update: edit `benchmarks` dict and `benchmark_source` string per client.

---

## Integration with Pipeline Suite

This pipeline is designed to run **after** monthly close is complete:

```
1. Bank Reconciliation      (bank-reconciliation.py)
2. P&L Quick Compare        (pl-quick-compare.py)
3. P&L Deep Analysis        (pl-deep-analysis.py)      ← optional for controller level
4. Client Dashboard         (client-dashboard.py)      ← this script
5. Cash Flow Forecast       (cash-flow-forecast.py)    ← quarterly advisory
```

---

## File Locations

| File | Path |
|------|------|
| Pipeline script | `scripts/pipelines/client-dashboard.py` |
| Skill file | `skills/client-dashboard/SKILL.md` |
| CDC cache | `.cache/client-dashboard/{slug}.json` |
| Output (default) | `~/Desktop/KPI_Dashboard_{slug}_{YYYY_MM}.xlsx` |

---

## Troubleshooting

**QBO CLI error:** Ensure your QBO integration is authenticated for the slug.

**Missing KPIs:** If Balance Sheet accounts don't match expected labels, values default to 0. Check `extract_bs_metrics()` candidates list for account name variants.

**New client config:** Add slug to `CLIENT_CONFIGS` before first run. Default config uses generic thresholds (not client-specific).

**Decimal errors:** All financial math uses Python `Decimal`. Do not mix `float` — use `to_d()` helper for any external values.

---
name: budget-builder
description: >
  Dual-mode budget pipeline for FP&A-quality budget management. Mode A (--build) generates an
  annual budget from 12 months of QBO history with auto-detected seasonal patterns, configurable
  growth assumptions, and manual overrides. Mode B (--compare, default) pulls YTD actuals from
  QBO, compares against the saved budget, flags material variances, generates management commentary
  stubs, builds a rolling forecast, and tracks budget accuracy over time via CDC.
  Outputs professional Excel workbooks with 4 tabs (Build) or 6 tabs (Compare).
  Use when: building the annual operating plan, running monthly BvA close, generating board-ready
  variance reports, or tracking which categories consistently miss budget.
  NOT for: multi-entity consolidations, tax preparation, real-time bookkeeping, or ad-hoc P&L
  analysis without a budget baseline (use pl-deep-analysis instead).
version: 1.0.0
tags:
  - finance
  - accounting
  - budgeting
  - FP&A
  - variance
  - management-reporting
  - forecasting
---

# Budget Builder + Budget vs Actual

## Overview

This pipeline handles the full FP&A budget cycle for QBO-connected clients:
1. **Build** an annual budget from QBO history with seasonal patterns + growth assumptions
2. **Compare** YTD actuals vs. budget with material flags, commentary, and rolling forecast

Both modes output Excel workbooks in a standard suite format (Calibri, dark header, F/U coloring, CDC log).

---

## Script Location

```
scripts/pipelines/budget-builder.py
```

## Cache Locations (auto-created)

```
.cache/budget-builder/{slug}_budget_{year}.json   # Budget file for Mode B
.cache/budget-builder/{slug}_cdc.json             # CDC accuracy tracker
```

---

## Mode A: Budget Builder (--build)

### What It Does

1. Pulls 12 months of monthly P&L from QBO (trailing from today's last completed month)
2. Discovers all accounts and their section types (income / cogs / expense)
3. Loads growth assumptions (global + per-account overrides from JSON)
4. Auto-detects seasonal patterns for each account (strength ≥ 1.30 = seasonal)
5. Generates monthly budget: `base_monthly_avg × seasonal_index × growth_factor`
6. Applies manual dollar overrides from CSV (full precedence over calculated amounts)
7. Saves budget to JSON cache + produces Excel workbook

### Excel Output (4 tabs)

| Tab | Contents |
|---|---|
| Budget Summary | Annual KPI totals + monthly Revenue/EBITDA grid |
| Monthly Detail | All line items × 12 months + Annual total |
| Assumptions | Growth method, rate, base avg, annual budget per account |
| Seasonal Patterns | Monthly indices, strength, peak/trough months per account |

### Usage

```bash
# Basic — use default growth rates (revenue +5%, COGS +3%, expenses +3%)
python3 budget-builder.py --slug <client-slug> --build --year 2026

# Custom growth assumptions
python3 budget-builder.py --slug <client-slug> --build --year 2026 --assumptions growth.json

# With manual overrides for specific accounts
python3 budget-builder.py --slug <client-slug> --build --year 2026 --overrides overrides.csv

# Custom output directory
python3 budget-builder.py --slug <client-slug> --build --year 2026 --out ~/Desktop/reports
```

### Assumptions JSON Format

```json
{
  "global": {
    "income":  0.08,
    "cogs":    0.04,
    "expense": 0.03
  },
  "overrides": {
    "Rent": {"method": "flat"},
    "Software Subscriptions": {"method": "pct_growth", "rate": 0.12},
    "Total Income": {"method": "pct_growth", "rate": 0.08}
  }
}
```

**Methods:**
- `pct_growth` (default) — apply percentage growth rate to trailing average
- `flat` — no growth from trailing average (rate = 0)

### Overrides CSV Format (--overrides)

For hard-coded monthly amounts that override the calculated budget:

```csv
account,section_type,2026-01,2026-02,2026-03,...,2026-12
Rent,expense,3500,3500,3500,...,3500
Owner's Draw,expense,8000,8000,8000,...,8000
```

Columns: `account` + `section_type` + one column per YYYY-MM (or Jan/Feb/Mar shorthand).

---

## Mode B: Budget vs Actual (--compare, default)

### What It Does

1. Loads saved budget (JSON cache or supplied file)
2. Pulls YTD actuals from QBO P&L (Jan 1 → end of last completed month)
3. Also pulls each month individually for per-month variance detail
4. Computes: $ delta, % delta, Favorable/Unfavorable per line item
5. Flags material variances: revenue ≥5% or ≥$2,500 | expenses ≥10% or ≥$2,500
6. Generates WHAT/WHY/ACTION/OUTLOOK commentary stubs for flagged variances
7. Builds rolling forecast: actuals locked for closed months + budget × blend for open months
8. Updates CDC accuracy log and surfaces systematic bias patterns

### Excel Output (6 tabs)

| Tab | Contents |
|---|---|
| Variance Summary | Headline KPIs + all material variances |
| Monthly Detail | All line items × closed months (Budget \| Actual \| Var per month) |
| Material Flags | Sorted by $ variance + monthly trend + action prompts |
| Rolling Forecast | Full-year forecast by account (gray=closed, blue=forecasted) |
| Management Commentary | WHAT/WHY/ACTION/OUTLOOK per material variance (draft) |
| CDC Log | Budget accuracy tracker + systematic bias analysis |

### Usage

```bash
# Default: compare through last completed month, use cached budget
python3 budget-builder.py --slug <client-slug> --compare

# Specify through month
python3 budget-builder.py --slug <client-slug> --compare --through 2026-02

# Use explicit budget file
python3 budget-builder.py --slug <client-slug> --compare --budget-file ~/Desktop/Budget_client_2026.json

# Use manually-prepared budget CSV
python3 budget-builder.py --slug <client-slug> --compare --budget-file my_budget.csv

# Custom output
python3 budget-builder.py --slug <client-slug> --compare --through 2026-03 --out ~/Desktop/reports
```

### Budget CSV Format (for --budget-file)

If the budget was prepared outside this tool (e.g., in Excel), export as:

```csv
account,section_type,2026-01,2026-02,...,2026-12
Total Income,income,50000,52000,...,85000
Cost of Goods Sold,cogs,15000,15600,...,25500
Rent,expense,3500,3500,...,3500
```

---

## Material Variance Thresholds

| Category | $ Threshold | % Threshold |
|---|---|---|
| Revenue | ≥ $2,500 | ≥ 5% |
| Expenses | ≥ $2,500 | ≥ 10% |

Both conditions use OR logic — either trigger alone is enough to flag.

---

## Seasonal Pattern Detection

- **Seasonal strength** = max_monthly_index / min_monthly_index
- Accounts with strength ≥ 1.30 are marked `IS SEASONAL = YES`
- Monthly index = month_amount / annual_average_monthly
- Index > 1.0 = above average month; index < 1.0 = below average
- Peak/trough months are identified and highlighted in the Seasonal Patterns tab

**Example:** Q4 revenue spike → indices for Oct/Nov/Dec will be > 1.0, Q1-Q2 will be < 1.0. Budget will correctly allocate more to Q4.

---

## Rolling Forecast Logic

```
Closed months:  Actual values locked in (not adjusted)
Open months:    Budget × blend_factor

blend_factor = (0.70 × ytd_factor) + (0.30 × 1.0)
ytd_factor   = actual_YTD / budget_YTD
```

- If actuals are running 15% above budget → open months forecasted at +10.5% above budget
- If actuals are running 10% below budget → open months forecasted at -7% below budget
- Blend weight (0.70) is configurable in script `FORECAST_TREND_WEIGHT`

---

## CDC — Budget Accuracy Tracker

Tracks per-account variance data across all BvA runs. After 2+ periods, identifies:
- **Consistently over-budget** accounts (actual > budget 75%+ of the time)
- **Consistently under-budget** accounts (actual < budget 75%+ of the time)
- **Average variance** magnitude per account

**Use case:** If Marketing always runs 20% over budget, the CDC will surface this after 2-3 months. Use for next year's budget calibration.

CDC is appended, not overwritten — it accumulates across fiscal years.

---

## Workflow Integration

```
1. [Month 1 of new FY] Run --build to generate annual budget
   → Saves: .cache/budget-builder/{slug}_budget_{year}.json
   → Share Excel with the reviewer for approval before using in BvA

2. [Each month after close] Run --compare
   → Pulls actuals from QBO automatically
   → Flags variances, generates commentary stubs
   → Updates CDC accuracy log

3. [Mid-year review] Re-run --build with updated assumptions + YTD overrides
   → Export updated budget JSON, pass to --budget-file in --compare
```

---

## Error Handling

| Scenario | Behavior |
|---|---|
| QBO connection error | Raises RuntimeError with stdout/stderr for diagnosis |
| Missing month in history | Fills with $0; warns in console |
| Budget file not found | Raises FileNotFoundError with clear message + instructions |
| Division by zero (budget = $0 for actuals) | Returns ZERO variance; flags if actual > $2,500 |
| Invalid CSV column format | Skips unrecognized columns; warns |

---

## Dependencies

```bash
pip install openpyxl
# Node.js QBO client must be configured with valid auth tokens
```

---

## Output File Naming

```
Mode A: Budget_{slug}_{year}.xlsx              (e.g., Budget_acme_2026.xlsx)
Mode B: BvA_{slug}_{month_label}.xlsx         (e.g., BvA_acme_Feb_26.xlsx)
Cache:  .cache/budget-builder/{slug}_budget_{year}.json
CDC:    .cache/budget-builder/{slug}_cdc.json
```

---

## Example: Full FY2026 Cycle

```bash
# January 2026: Build the budget
python3 budget-builder.py --slug <client-slug> --build --year 2026 \
  --assumptions clients/<client-slug>/budget-assumptions-2026.json

# February 28 (after Jan close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-01

# March 31 (after Feb close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-02

# Q2 reforecast (after March close):
python3 budget-builder.py --slug <client-slug> --compare --through 2026-03
# Review CDC log — which categories need budget adjustment?
# Build revised budget with Q1 actuals as overrides:
python3 budget-builder.py --slug <client-slug> --build --year 2026 \
  --overrides clients/<client-slug>/q1-actuals-overrides.csv
```

---
name: ar-collections
description: >
  AR Collections & Aging Analysis pipeline for QBO clients. Produces a 7-tab Excel workbook
  with AR aging buckets (Current/1-30/31-60/61-90/90+), DSO, collection priority scoring,
  bad debt reserve, payment patterns, and CDC log. SOP-gated: auto-skips clients with no AR
  (e.g. POS-based businesses). Use for AR aging reports, collections analysis, DSO tracking,
  and bad debt reserve calculations. NOT for bank reconciliation, payroll, or tax preparation.
version: 1.0.0
tags:
  - finance
  - accounting
  - accounts-receivable
  - collections
  - DSO
  - aging
summary:
  - AR Collections & Aging Analysis pipeline for QBO clients
  - SOP-gated: auto-skips if client has no AR (e.g. SB Paulson/Willo — POS collection)
  - 7-tab Excel output: AR Summary | Aging Detail | Collection Priority | Payment Patterns | Bad Debt Reserve | DSO Analysis | CDC Log
  - All Decimal math; CDC tracks aging movement between runs
  - Trigger phrases: AR aging, collections report, accounts receivable, DSO, bad debt reserve
updated: 2026-03-18
---

# AR Collections & Aging Analysis Skill

## What This Does

Runs the AR Collections & Aging Analysis pipeline (`scripts/pipelines/ar-collections.py`) to produce a controller-level accounts receivable workbook from QBO data.

**Produces:**
1. AR aging bucketed into Current | 1-30 | 31-60 | 61-90 | 90+ days
2. Aging metrics: total AR, weighted average days outstanding, concentration risk
3. Collection priority scoring per customer (HIGH / MEDIUM / LOW / MONITOR)
4. Recommended collection actions per customer
5. Bad debt reserve (percentage-of-aging method)
6. Payment pattern analysis from GL history
7. DSO: current period and rolling 3-month
8. CDC: which customers improved or deteriorated since last run
9. Excel workbook (7 tabs)

## When to Use

**Use when:**
- Client asks for AR aging report, collections status, or DSO
- Monthly close includes AR review
- Need to know who owes money and what to do about it
- Bad debt reserve needs to be calculated for month-end
- Auditors or investors request AR aging schedule

**Do NOT use when:**
- Client SOP says AR is not applicable (pipeline exits gracefully — no report needed)
- Client collects at point of sale (e.g., SB Paulson / Willo Salons)
- Running for a non-QBO client (no data source)
- User wants a P&L or balance sheet (use pl-deep-analysis or client-dashboard)

## SOP Gate

The pipeline **automatically checks the client SOP** before pulling any data:
- `sb-paulson` → exits gracefully with explanation (POS collection, no AR)
- Unknown slugs → checks SOP markdown for AR-disabled signals, defaults to AR-applicable

To add a new client's AR status, update `CLIENT_AR_CONFIG` in the pipeline, OR add these markers to their `clients/{slug}/sop.md`:
```
**AR Aging:** ❌ Not applicable (POS collection)
```

## Usage

```bash
# Standard run — as of end of month
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31

# With custom output directory
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --out ~/Desktop/reports

# Skip GL pull (faster, no payment pattern analysis)
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --skip-gl

# QBO sandbox
python3 scripts/pipelines/ar-collections.py --slug <client-slug> --as-of 2026-03-31 --sandbox

# Client with no AR — exits gracefully
python3 scripts/pipelines/ar-collections.py --slug sb-paulson --as-of 2026-03-31
```

## Output

**Default location:** `reports/ar-collections/ar-collections_{slug}_{as-of}.xlsx`

**Tabs:**

| Tab | Contents |
|-----|----------|
| AR Summary | Aging snapshot by bucket, key metrics, concentration risk |
| Aging Detail | Invoice-level list: customer, date, due date, balance, bucket |
| Collection Priority | Sorted action list: HIGH/MEDIUM/LOW/MONITOR with recommended actions |
| Payment Patterns | Avg days to pay per customer, vs. terms, reliability rating |
| Bad Debt Reserve | Percentage-of-aging reserve calc + suggested journal entry |
| DSO Analysis | Current and rolling 3-month DSO, monthly revenue detail |
| CDC Log | Changes since last run: improved / deteriorated / new / cleared |

## Collection Priority Logic

| Priority | Criteria | Recommended Action |
|----------|----------|--------------------|
| HIGH | 90+ days past due OR balance > $5K | Escalate / demand letter / write-off review |
| MEDIUM | 61-90 days OR balance > $2.5K | Follow-up call |
| LOW | 31-60 days | Send email reminder |
| MONITOR | Current or 1-30 days | Standard review next cycle |

## Bad Debt Reserve Rates (Percentage-of-Aging)

| Bucket | Rate |
|--------|------|
| Current | 1% |
| 1-30 | 3% |
| 31-60 | 10% |
| 61-90 | 25% |
| 90+ | 50% |

## DSO Formula

- **Current DSO** = (AR Balance ÷ Current Period Revenue) × Days in Period
- **Rolling DSO** = AR Balance ÷ (3-Month Revenue ÷ 90 days)

## CDC Cache

Cached at: `.cache/ar-collections/{slug}.json`

Each run saves customer balances and worst buckets. Next run computes:
- **Improved** — balance decreased or bucket moved earlier
- **Deteriorated** — balance increased or bucket moved later
- **New** — first appearance in AR
- **Cleared** — balance went to zero (collected)

## Requirements

```bash
pip install openpyxl
# Node.js QBO client must be auth'd
node bin/qbo info {slug}  # from your QBO integration directory
```

## Related Pipelines

- `pl-deep-analysis.py` — GL drill-down, P&L variance, accrual proposals
- `client-dashboard.py` — KPI dashboard (includes DSO as a KPI)
- `bank-reconciliation.py` — Bank rec (not AR-specific)
- `budget-vs-actual.py` — BvA (revenue-side context for AR)

## Clients

Configure AR applicability per client in `CLIENT_AR_CONFIG` or via `clients/{slug}/sop.md`.

---
name: agent-orchestration
description: 'Multi-agent orchestration patterns for production deployments. Covers sub-agent QC workflow, model staggering across 5+ models, cross-validation patterns, fallback chains, task routing by model strength, ACPX configuration, and cost optimization. Use when coordinating multiple agents or models for complex workflows. Do NOT use for single-agent prompting, prompt engineering, or fine-tuning — those are separate skills.'
license: MIT
metadata:
  openclaw:
    emoji: '🎭'
---

# Agent Orchestration

Production-tested patterns for coordinating multiple AI agents and models. This skill covers the full spectrum from simple fallback chains to complex multi-model workflows with cross-validation and quality control loops.

## When to Use

- Coordinating 2+ agents or models on a single workflow
- Building QC loops where one model checks another's work
- Routing tasks to the right model based on task type
- Setting up fallback chains for reliability
- Optimizing cost across subscription and API models
- Configuring ACPX (Agent Computer Protocol eXtended) for Claude Code and Codex
- Designing spawn patterns for runtime sub-agents

## When NOT to Use

- Single-agent prompting or prompt engineering (use a prompt-engineering skill)
- Fine-tuning or training models (different domain entirely)
- Simple API calls to one model (just call the API)
- RAG or retrieval pipeline design (use a RAG-specific skill)
- Agent memory architecture (use the agent-memory-architecture skill)

---

## 1. Sub-Agent QC Workflow

The core pattern: **Produce → Review → Cross-Check → Incorporate → Deliver**.

### The Five-Step Loop

```
┌─────────────┐
│  1. PRODUCE  │  Sonnet 4.6 generates first draft
│  (Grinder)   │  Fast, cost-effective, good enough for 80% of tasks
└──────┬──────┘
       ▼
┌─────────────┐
│  2. REVIEW   │  Same model self-reviews against criteria
│  (Self-QC)   │  Catches obvious errors, formatting issues
└──────┬──────┘
       ▼
┌─────────────┐
│  3. CROSS    │  Different model (GPT-4o / Grok) validates
│  CHECK       │  Catches blind spots, model-specific biases
└──────┬──────┘
       ▼
┌─────────────┐
│  4. INCORP.  │  Opus 4.6 synthesizes feedback
│  (Orchestr.) │  Resolves conflicts, applies judgment
└──────┬──────┘
       ▼
┌─────────────┐
│  5. DELIVER  │  Final output with confidence score
│  (Output)    │  Includes provenance trail
└─────────────┘
```

### Implementation Example

```python
async def qc_workflow(task: str, context: dict) -> dict:
    """Five-step QC workflow with cross-model validation."""

    # Step 1: Produce (Sonnet — fast, cheap)
    draft = await call_model(
        model="claude-sonnet-4-6",
        prompt=f"Complete this task:\n{task}",
        context=context,
        max_tokens=4096
    )

    # Step 2: Self-review (same model, different prompt)
    self_review = await call_model(
        model="claude-sonnet-4-6",
        prompt=f"""Review this output for errors, omissions, and quality:

TASK: {task}
OUTPUT: {draft}

Score 1-10 on: accuracy, completeness, clarity.
List specific issues to fix.""",
        max_tokens=1024
    )

    # Step 3: Cross-check (different model family)
    cross_check = await call_model(
        model="gpt-4o",
        prompt=f"""Independent review. Do NOT assume the draft is correct.

TASK: {task}
DRAFT: {draft}
SELF-REVIEW: {self_review}

Identify: factual errors, logical gaps, missing context, biases.""",
        max_tokens=1024
    )

    # Step 4: Incorporate (Opus — best judgment)
    final = await call_model(
        model="claude-opus-4-6",
        prompt=f"""Synthesize and produce final output.

TASK: {task}
DRAFT: {draft}
SELF-REVIEW: {self_review}
CROSS-CHECK: {cross_check}

Resolve any conflicts. Produce the best possible final output.
Include a confidence score (0-100) and list any unresolved concerns.""",
        max_tokens=4096
    )

    # Step 5: Deliver with metadata
    return {
        "output": final,
        "provenance": {
            "producer": "claude-sonnet-4-6",
            "reviewer": "claude-sonnet-4-6",
            "cross_checker": "gpt-4o",
            "synthesizer": "claude-opus-4-6",
            "steps_completed": 5
        }
    }
```

### When to Skip Steps

| Scenario | Skip | Rationale |
|----------|------|-----------|
| Low-stakes internal task | Steps 3-4 | Self-review is sufficient |
| Time-critical (<30s budget) | Steps 2-4 | Single model, accept risk |
| High-stakes client deliverable | None | Full loop, every time |
| Coding task with tests | Step 3 | Tests serve as cross-check |
| Creative/subjective work | Step 3 | Cross-check adds noise, not signal |

---

## 2. Model Staggering

Assign models to tasks based on their demonstrated strengths.

### The Model Roster

```
Model              Strength Zone              Cost Tier    Speed
────────────────────────────────────────────────────────────────
Opus 4.6           Strategy, synthesis,       $$$$$        Slow
                   complex reasoning,
                   judgment calls

Sonnet 4.6         Production work, coding,   $$$          Fast
                   analysis, writing,
                   general-purpose grinder

GPT-4o             Coding, scoring rubrics,   $$$$         Medium
                   structured output,
                   alternative perspective

Grok               X/Twitter analysis,        $$           Fast
                   social media content,
                   real-time commentary

Gemini 2.5 Pro     Deep research, long        $$$          Medium
                   context analysis,
                   multimodal processing

Haiku 4.5          Classification, routing,   $            Very Fast
                   simple extraction,
                   high-volume tasks
```

### Task Routing Rules

```yaml
routing_rules:
  # Strategic / High-judgment tasks → Opus
  strategy:
    models: [claude-opus-4-6]
    triggers:
      - "requires judgment between competing priorities"
      - "synthesize conflicting information"
      - "make a recommendation with tradeoffs"
      - "review and improve another agent's work"

  # Production work → Sonnet
  production:
    models: [claude-sonnet-4-6]
    triggers:
      - "write code to specification"
      - "generate content from template"
      - "analyze data and report findings"
      - "standard business communication"

  # Coding with scoring → GPT
  coding_and_scoring:
    models: [gpt-4o]
    triggers:
      - "write and debug complex algorithms"
      - "score outputs against rubric"
      - "generate structured JSON/YAML"
      - "cross-validate another model's output"

  # Social / real-time → Grok
  social:
    models: [grok-3]
    triggers:
      - "analyze X/Twitter trends"
      - "generate social media content"
      - "real-time event commentary"
      - "meme-aware communication"

  # Deep research → Gemini
  research:
    models: [gemini-2.5-pro]
    triggers:
      - "analyze documents >100K tokens"
      - "cross-reference multiple long sources"
      - "multimodal analysis (images + text)"
      - "broad research synthesis"

  # High-volume classification → Haiku
  classification:
    models: [claude-haiku-4-5]
    triggers:
      - "classify items into categories"
      - "extract structured fields from text"
      - "route incoming requests"
      - "simple yes/no decisions"
```

### Staggering in Practice

```
Example: "Write a market analysis report"

1. Gemini 2.5 Pro  → Research phase (long context, web search)
2. Sonnet 4.6      → Draft the report (fast production)
3. GPT-4o          → Score against quality rubric (structured eval)
4. Opus 4.6        → Final synthesis and executive summary (judgment)
5. Haiku 4.5       → Extract key metrics into structured JSON (cheap, fast)
```

---

## 3. Fallback Chains

When a model is unavailable, rate-limited, or returns low-quality output, fall through to the next option.

### Chain Configuration

```yaml
fallback_chains:
  # Primary reasoning chain
  reasoning:
    - model: claude-opus-4-6
      timeout: 60s
      retry: 1
    - model: gpt-4o
      timeout: 45s
      retry: 1
    - model: claude-sonnet-4-6
      timeout: 30s
      retry: 2
    - model: gemini-2.5-pro
      timeout: 45s
      retry: 1

  # Fast production chain
  production:
    - model: claude-sonnet-4-6
      timeout: 30s
      retry: 2
    - model: gpt-4o
      timeout: 30s
      retry: 1
    - model: grok-3
      timeout: 20s
      retry: 1

  # Classification chain (optimize for cost)
  classification:
    - model: claude-haiku-4-5
      timeout: 10s
      retry: 3
    - model: claude-sonnet-4-6
      timeout: 15s
      retry: 1
```

### Fallback Decision Logic

```python
async def call_with_fallback(chain: str, prompt: str) -> dict:
    """Try models in order until one succeeds with acceptable quality."""

    for entry in CHAINS[chain]:
        for attempt in range(entry["retry"] + 1):
            try:
                result = await call_model(
                    model=entry["model"],
                    prompt=prompt,
                    timeout=entry["timeout"]
                )

                # Quality gate: reject low-confidence outputs
                if result.get("confidence", 100) < 30:
                    log(f"{entry['model']} returned low confidence, trying next")
                    break  # Move to next model, don't retry

                return {
                    "output": result,
                    "model_used": entry["model"],
                    "attempt": attempt + 1,
                    "fallback_depth": CHAINS[chain].index(entry)
                }

            except (TimeoutError, RateLimitError) as e:
                log(f"{entry['model']} attempt {attempt+1} failed: {e}")
                continue

    raise AllModelsFailed(f"No model in chain '{chain}' produced acceptable output")
```

---

## 4. ACPX Configuration

ACPX (Agent Computer Protocol eXtended) enables tool-using agents to coordinate. Configuration for Claude Code and Codex environments.

### Claude Code Configuration

In your project's `CLAUDE.md`:

```markdown
# Agent Orchestration

## Sub-agent Spawning
When a task requires cross-model validation:
1. Use the Agent tool to spawn a sub-agent for the secondary task
2. The sub-agent inherits the project context but gets its own conversation
3. Results flow back to the orchestrator via the Agent tool response

## Model Selection
- Use claude-opus-4-6 for: architectural decisions, code review, complex debugging
- Use claude-sonnet-4-6 for: implementation, test writing, documentation
- Use claude-haiku-4-5 for: linting, formatting, simple refactors

## Tool Permissions
Sub-agents may: read files, search code, run tests
Sub-agents may NOT: push to git, modify CI/CD, delete files without confirmation
```

### ACP Server Setup

```json
{
  "mcpServers": {
    "orchestrator": {
      "command": "node",
      "args": ["./orchestrator-server.js"],
      "env": {
        "ANTHROPIC_API_KEY": "ANTHROPIC_API_KEY",
        "OPENAI_API_KEY": "OPENAI_API_KEY",
        "MAX_CONCURRENT_AGENTS": "5",
        "DEFAULT_CHAIN": "production"
      }
    }
  }
}
```

### Codex Integration

```yaml
# codex.yaml
agents:
  orchestrator:
    model: claude-opus-4-6
    role: "Route tasks and synthesize results"
    tools: [spawn_agent, review_output, merge_results]

  grinder:
    model: claude-sonnet-4-6
    role: "Execute implementation tasks"
    tools: [read_file, write_file, run_tests, search_code]

  validator:
    model: gpt-4o
    role: "Cross-validate outputs"
    tools: [read_file, run_tests, score_output]
```

---

## 5. Cost Optimization

### Subscription vs API Economics

```
Subscription Models ($20-200/month flat):
  Claude Pro/Max    → Best for: daily interactive use, long sessions
  ChatGPT Plus      → Best for: GPT-4o access, plugins
  Grok Premium      → Best for: X integration, real-time
  Gemini Advanced   → Best for: Google ecosystem, long context

API Models (per-token):
  claude-opus-4-6   → $15/M input, $75/M output
  claude-sonnet-4-6 → $3/M input, $15/M output
  claude-haiku-4-5  → $0.80/M input, $4/M output
  gpt-4o            → $2.50/M input, $10/M output
```

### $0 Marginal Cost Routing

When you have active subscriptions, route interactive and exploratory work through subscriptions (zero marginal cost) and reserve API for automated/batch workflows.

```
Decision Tree:
  Is this interactive/exploratory?
    YES → Route through subscription (Claude Code, ChatGPT, etc.)
    NO  → Is this batch/automated?
      YES → Use API with cheapest adequate model
      NO  → Is this high-volume (>1000 calls/day)?
        YES → Use Haiku via API ($0.80/M input)
        NO  → Use Sonnet via API ($3/M input)
```

### Cost Tracking Template

```
Monthly AI Spend:
  Subscriptions (fixed):
    Claude Max            $200.00
    ChatGPT Plus           $20.00
    Grok Premium           $30.00
    Gemini Advanced        $20.00
  Subtotal Fixed          $270.00

  API Usage (variable):
    Opus 4.6         42K tokens    $3.78
    Sonnet 4.6      380K tokens    $6.84
    Haiku 4.5     1.2M tokens      $1.76
    GPT-4o          95K tokens     $1.19
  Subtotal Variable                $13.57

  Total                           $283.57
  Cost per task (avg)               $0.28
  Tasks completed                  1,013
```

---

## 6. Spawn Patterns

### Pattern 1: Runtime Sub-Agent (Within Claude Code)

Use the `Agent` tool to spawn sub-agents that inherit project context.

```
Orchestrator (Opus)
  ├── Agent: "Research the API surface" (Explore subagent)
  ├── Agent: "Implement the endpoint" (general-purpose subagent)
  └── Agent: "Write tests" (general-purpose subagent)
```

Best for: tasks where sub-agents need file system access and project context.

### Pattern 2: API-Spawned Agent (External)

Call model APIs directly for tasks that don't need project context.

```python
# Spawn multiple validators in parallel
import asyncio

async def parallel_validate(content: str) -> list:
    tasks = [
        call_model("claude-sonnet-4-6", f"Review for accuracy:\n{content}"),
        call_model("gpt-4o", f"Review for accuracy:\n{content}"),
        call_model("gemini-2.5-pro", f"Review for accuracy:\n{content}"),
    ]
    return await asyncio.gather(*tasks)
```

Best for: cross-validation, scoring, classification — tasks that are self-contained.

### Pattern 3: Orchestrator-Grinder Split

The orchestrator plans and delegates. Grinders execute. Never let a grinder make strategic decisions.

```
ORCHESTRATOR (Opus 4.6):
  - Reads the task requirements
  - Breaks into subtasks
  - Assigns each subtask to appropriate grinder
  - Reviews grinder outputs
  - Synthesizes final deliverable
  - Makes judgment calls on conflicts

GRINDER (Sonnet 4.6 / GPT-4o):
  - Receives specific, scoped subtask
  - Executes without strategic decisions
  - Returns output with confidence score
  - Flags uncertainty rather than guessing
```

### Anti-Patterns to Avoid

| Anti-Pattern | Problem | Fix |
|-------------|---------|-----|
| Grinder makes strategic calls | Inconsistent decisions, wasted work | Escalate to orchestrator |
| Orchestrator does grinder work | Slow, expensive, bottleneck | Delegate production tasks |
| No quality gate between steps | Errors compound through pipeline | Add review step after each stage |
| Same model reviews its own work | Blind spots persist | Cross-model validation |
| Spawning agents for trivial tasks | Overhead exceeds task cost | Direct call for simple tasks |
| Infinite retry loops | Cost explosion | Max 3 retries, then escalate |

---

## 7. Orchestrator vs Grinder Principle

This is the foundational principle of multi-agent systems.

### The Rule

> **The orchestrator thinks. The grinder does. Never confuse the two.**

### Role Definitions

```
ORCHESTRATOR                          GRINDER
─────────────────────────────────     ─────────────────────────────────
Decides WHAT to do                    Decides HOW to do it
Chooses which model/tool              Uses the tools it's given
Reviews and judges quality            Produces and reports confidence
Resolves conflicts between agents     Flags conflicts for resolution
Owns the final output                 Owns its subtask output
Expensive, slow, high-judgment        Cheap, fast, high-throughput
1 per workflow                        N per workflow
```

### Decision Framework

```
"Should this be an orchestrator or grinder decision?"

Ask: "If two reasonable people disagreed on this, would it matter?"
  YES → Orchestrator decision (judgment required)
  NO  → Grinder decision (execution, not judgment)

Ask: "Does this affect the overall workflow direction?"
  YES → Orchestrator decision
  NO  → Grinder decision

Ask: "Could a junior employee do this with clear instructions?"
  YES → Grinder task
  NO  → Orchestrator task
```

### Example Workflow: Client Deliverable

```
ORCHESTRATOR (Opus):
  1. Read client brief → decide deliverable structure
  2. Break into sections → assign to grinders
  3. Review all sections → identify gaps
  4. Resolve quality issues → request rewrites
  5. Synthesize → produce final deliverable
  6. Generate executive summary → deliver

GRINDER 1 (Sonnet): Write Section A per outline
GRINDER 2 (Sonnet): Write Section B per outline
GRINDER 3 (GPT-4o): Generate data tables and charts
GRINDER 4 (Gemini): Research background for Section C
GRINDER 5 (Haiku): Format citations and references
```

Total cost: 1 Opus call (synthesis) + 5 cheaper calls (production)
vs. doing everything in Opus: 6 Opus calls at 5x the cost.

---
name: document-ingestion
description: "Process raw accounting source documents (PDFs, CSVs, bank statements, invoices, receipts) into standardized transaction records for QBO import. Use when batch-processing client documents for month-end close, categorizing transactions, or extracting data from 1099s and payroll reports. NOT for bank reconciliation, P&L variance analysis, or AR collections."
license: MIT
metadata:
  openclaw:
    emoji: "📄"
---

# Document Ingestion Engine — SKILL.md

## When to Use This Skill
Use when a user needs to process raw accounting source documents into standardized transaction records for QBO import. Triggers on:
- "Process these documents / invoices / receipts / bank statements"
- "Ingest docs for [client]"
- "I have PDFs/CSVs to categorize"
- "Batch import these transactions to QBO"
- "Extract data from 1099s / payroll reports"
- Document drop + categorization requests during month-end close

## When NOT to Use
- **Not** for running bank reconciliation (use `bank-reconciliation` skill)
- **Not** for P&L variance analysis (use `pl-quick-compare` skill)
- **Not** for single manual journal entries (just post directly in QBO)
- **Not** for AR collections or aging (use `ar-collections-agent` skill)

---

## What It Does

Processes 6 document types → standardized records → Excel workbook + QBO import CSV.

| Input Type | Formats | Extracts |
|---|---|---|
| Bank Statements | CSV, OFX/QFX, PDF | Date, vendor, amount |
| Credit Card Stmts | CSV, PDF | Date, merchant, amount, category |
| Invoices | PDF | Vendor, total, date, due date, invoice #, line items |
| Receipts | PDF, JPG/PNG* | Merchant, date, amount |
| 1099 / Tax Forms | PDF | Payer, TIN, form type, box amounts |
| Payroll Reports | CSV, PDF | Employee, gross, taxes, net per employee |

*Image OCR requires `tesseract` installed.

### Processing Steps
1. **File type detection** — magic bytes + extension fallback
2. **Document classification** — bank/CC/invoice/receipt/1099/payroll
3. **Content extraction** — CSV parsing, OFX parsing, PDF text extraction
4. **Format normalization** — dates (multi-format), amounts (Decimal), vendor names (strip noise)
5. **QBO COA pull** — fetches live Chart of Accounts from QBO for categorization
6. **Duplicate detection** — same amount + vendor within ±3 days → flagged
7. **Auto-categorization** — vendor map → COA keywords → doc-class default
8. **Confidence scoring** — HIGH (exact match) / MEDIUM (fuzzy) / LOW (needs review)
9. **Exception flagging** — missing dates, zero amounts, unknown vendors, LOW confidence
10. **QBO import CSV** — ready for batch import (excludes dups + failed extractions)
11. **Excel workbook** — 6 tabs (see below)
12. **CDC tracking** — delta since last run cached in `.cache/document-ingestion/{slug}.json`

### Excel Output Tabs
| Tab | Contents |
|---|---|
| Processed Transactions | All records with category, confidence, dup flag, exception |
| ⚠ Exceptions | Records needing manual review before import |
| Duplicates | Flagged potential duplicates with "Dup Of" reference |
| Category Mapping | Unique vendor → QBO account map with confidence |
| Import Ready | QBO-format rows (Date, Description, Amount, Account, Memo) |
| CDC Log | Delta metrics vs. prior run + this-run stats summary |

---

## Script Location
```
scripts/pipelines/document-ingestion.py
```

## Usage

```bash
# Process a directory of mixed documents
python3 scripts/pipelines/document-ingestion.py \
    --slug sb-paulson \
    --input-dir ~/Downloads/month-end-docs

# Single file
python3 scripts/pipelines/document-ingestion.py \
    --slug sb-paulson \
    --file ~/Downloads/invoice_march.pdf

# Multiple files + custom output dir
python3 scripts/pipelines/document-ingestion.py \
    --slug glowlabs \
    --file ~/Downloads/stmt.csv \
    --file ~/Downloads/payroll.csv \
    --out ~/Desktop/ingested

# Offline mode (no QBO auth needed)
python3 scripts/pipelines/document-ingestion.py \
    --slug sb-paulson \
    --input-dir ./docs \
    --no-qbo-coa

# QBO sandbox
python3 scripts/pipelines/document-ingestion.py \
    --slug sb-paulson \
    --input-dir ./docs \
    --sandbox
```

### All CLI Flags
| Flag | Default | Description |
|---|---|---|
| `--slug` | required | Company slug (QBO + client vendor map) |
| `--input-dir` | — | Directory of docs to process |
| `--file` | — | Single file (repeatable) |
| `--out` | `~/Desktop` | Output directory |
| `--no-qbo-coa` | false | Use built-in COA only (offline) |
| `--sandbox` | false | QBO sandbox mode |

---

## Dependencies

### Required (pip)
```bash
pip install openpyxl
```

### Optional (better extraction quality)
```bash
pip install pdfminer.six   # Better PDF text extraction
pip install ofxparse       # Better OFX/QFX parsing
brew install tesseract     # Image receipt OCR (JPG/PNG)
```

### Node.js QBO Client
```
Node.js QBO client   # Auth token must be configured
```

---

## Categorization Logic

### Priority Chain
1. **Vendor Map exact match** → `HIGH` confidence
2. **Vendor Map substring match** → `HIGH` confidence
3. **COA keyword index** (built from COA account names + keywords) → `MEDIUM` confidence
4. **Doc-class default** → `LOW` confidence

### Built-in Vendor Map
50+ known vendors pre-mapped:
- Stripe/Square/PayPal → Sales Revenue
- Gusto/ADP/Deel/Paychex → Payroll - Salaries & Wages
- Google/Microsoft/Slack/GitHub/Zoom → Software & Subscriptions
- Delta/United/Marriott/Uber → Travel
- FedEx/UPS/USPS → Postage & Delivery
- Chase/BofA service charges → Bank & Merchant Fees
- etc. (see `VENDOR_MAP` in script)

### Client-Specific Overrides
Auto-loaded by `--slug`:
- **glowlabs** → Loads GlowLabs vendor map (Deel, Toptal, Brex, Huellas Labs, etc.)
- **sb-paulson / willo** → Loads Willo Salons vendor map
- Other clients → Reads `clients/{slug}/categorization-map*.md` markdown tables

---

## Duplicate Detection Rules
- **Window:** ±3 days (configurable via `DUP_WINDOW_DAYS` constant)
- **Match criteria:** Same amount (exact Decimal) + same vendor key (first 3 meaningful words)
- **Action:** Flagged as `is_duplicate=True`, excluded from import file
- **Always confirm** before deleting — duplicates tab shows "Dup Of Row #" reference

---

## Exception Rules (auto-flagged)
| Condition | Flag |
|---|---|
| Missing transaction date | "Missing transaction date" |
| Zero amount (non-1099) | "Zero amount — verify or skip" |
| Empty/unknown vendor | "Vendor name missing or unknown" |
| LOW confidence category | "Low categorization confidence — manual review" |
| PDF extraction failed | "PDF text extraction failed — manual review required" |
| Image without tesseract | "Image OCR not available — manual entry required" |

---

## QBO Import CSV Format
Ready-to-import columns:
```
Date | Description | Amount | Vendor/Customer | Account | Class | Memo | Doc Number
```
- Amount sign: positive = expense (debit), negative = credit/income
- Memo includes source file + doc type for audit trail
- Excludes: duplicates, failed extractions

---

## CDC Cache
Location: `.cache/document-ingestion/{slug}.json`

Tracks between runs:
- `docs_processed`, `records_extracted`, `duplicates_caught`
- `exceptions_flagged`, `import_ready`
- `high_confidence`, `medium_confidence`, `low_confidence`

---

## Output File Naming
```
DocIngestion_{slug}_{YYYYMMDD}.xlsx
DocIngestion_{slug}_{YYYYMMDD}_QBO_Import.csv
```

---

## Agent Instructions

### Standard Run
1. Collect input files from user (directory path or individual files)
2. Get client slug (`sb-paulson`, `glowlabs`, etc.)
3. Run pipeline. If QBO auth not set, use `--no-qbo-coa`
4. Deliver summary:
   - Records extracted, dups caught, exceptions
   - HIGH/MED/LOW confidence split
   - Path to Excel + import CSV
5. Walk user through Exceptions tab — those need action before import

### Month-End Close Integration
- Run AFTER bank statement download, BEFORE bank reconciliation
- Use `--input-dir` pointing to client's document drop folder
- Import CSV goes into QBO → then run `bank-reconciliation.py`

### Exception Handling
- PDFs with no extractable text → LOW confidence + exception flag → send to client for re-scan
- Image receipts with no tesseract → exception flag → use `nano-pdf` skill or manual entry
- Unknown vendors → update `VENDOR_MAP` in script or add to `clients/{slug}/categorization-map.md`

### Adding New Client Vendor Maps
Edit `load_client_vendor_map()` in the script:
```python
if slug_lower in ("new-client", "nc"):
    client_map.update({
        "vendor name": "QBO Account Name",
    })
```
Or create `clients/{slug}/categorization-map.md` with markdown table:
```markdown
| Vendor / Memo Keyword | Primary Account | Notes |
|---|---|---|
| Amazon | Office Supplies | |
| Comcast | Utilities | |
```

---

## Financial Math
All amounts use Python `Decimal` with `ROUND_HALF_UP` to 2 decimal places. No float arithmetic.

---
name: month-end-close
description: "Orchestrate and validate the full month-end close for a QBO client. Reads client SOP, runs automated close checks, scores each item, proposes journal entries, tracks CDC progress, and outputs a controller-ready Excel workbook. Use when running monthly close for any QBO-connected client. NOT for P&L variance analysis, bank reconciliation only, or budget vs. actual comparisons."
license: MIT
metadata:
  openclaw:
    emoji: "📅"
---

# Month-End Close Checklist — SKILL.md

Orchestrates and validates the full month-end close for a QBO client.
Reads `clients/{slug}/sop.md` to determine which checks apply,
runs automated close checks against QBO, scores each item,
proposes journal entries for missing items, tracks progress with CDC,
and outputs a controller-ready Excel workbook.

---

## Trigger

Use this skill when:
- User says "run close", "month-end close", "close checklist", or "close [client] for [month]"
- Monthly close workflow is needed for any QBO-connected client
- Re-running close to check if open items have been resolved

Do NOT use for:
- P&L variance analysis → use `pl-quick-compare` or `pl-deep-analysis`
- Bank reconciliation only → use `bank-reconciliation`
- Budget vs. actual → use `budget-vs-actual`

---

## Script Location

```
scripts/pipelines/month-end-close.py
```

---

## Usage

```bash
# Standard close run
python3 scripts/pipelines/month-end-close.py --slug sb-paulson --month 2026-03

# Skip GL drill (faster; prepaid/depr/payroll checks have limited data)
python3 scripts/pipelines/month-end-close.py --slug willo-salons --month 2026-02 --skip-gl

# Re-run as items get resolved (CDC tracks progress between runs)
python3 scripts/pipelines/month-end-close.py --slug glowlabs --month 2026-03

# Force fresh QBO pulls (ignore CDC cache)
python3 scripts/pipelines/month-end-close.py --slug sb-paulson --month 2026-03 --rerun

# Custom output directory
python3 scripts/pipelines/month-end-close.py --slug sb-paulson --month 2026-03 --out ~/Desktop/close

# QBO Sandbox
python3 scripts/pipelines/month-end-close.py --slug sb-paulson --month 2026-03 --sandbox
```

---

## Arguments

| Argument | Required | Description |
|---|---|---|
| `--slug` | ✅ | Company slug (must match qbo-client connection) |
| `--month` | ✅ | Close period in YYYY-MM format (e.g. `2026-03`) |
| `--skip-gl` | ❌ | Skip GL pull — faster but prepaid/depr/payroll checks limited |
| `--rerun` | ❌ | Force all checks fresh — ignores CDC state for check selection |
| `--out` | ❌ | Output directory (default: `~/Desktop`) |
| `--sandbox` | ❌ | Use QBO sandbox environment |

---

## Close Checks

| Check | ID | Default | SOP Override |
|---|---|---|---|
| Bank Reconciliation | `bank_recon` | ✅ | Disable: `bank reconciliation: ❌` |
| Trial Balance (D=C) | `trial_balance` | ✅ | Always enabled |
| AP Aging | `ap_aging` | ✅ | Disable: `ap aging: ❌` |
| AR Aging | `ar_aging` | ✅ | **Auto-disabled if SOP says no AR/POS** |
| Prepaid Amortization | `prepaid_amortization` | ✅ | Disable: `prepaid: ❌` |
| Depreciation | `depreciation` | ✅ | Disable: `no fixed assets` |
| Payroll Reconciliation | `payroll_recon` | ✅ | Disable: `payroll: ❌` |
| Revenue Recognition | `revenue_recognition` | ✅ | Disable: `deferred revenue: ❌` |
| Accrued Expenses | `accrued_expenses` | ✅ | Disable: `accruals: ❌` |
| Intercompany Eliminations | `intercompany` | **❌ OFF** | Enable: `multi-entity` or `intercompany: ✅` |

---

## SOP Integration

The pipeline reads `clients/{slug}/sop.md` and parses it for:

- **AR Aging** — auto-disabled if SOP contains: `no accounts receivable`, `POS collection`,
  `AR aging: ❌`, or `AR: Not applicable`
- **Intercompany** — disabled by default; enabled if SOP contains: `multi-entity`,
  `intercompany: ✅`, or `consolidated`
- **Watch notes** — special SOP signals become tab notes in Excel
  (e.g. `interest expense`, `cash burn`, `deferred revenue`, `SAFE`)

### Client SOP Quick Reference

| Client | AR? | Interco? | Notes |
|---|---|---|---|
| `willo-salons` | ❌ No | ❌ No | POS/cash — no AR |
| `glowlabs` | ❌ No | ❌ No | Gaming/Web3; high burn |
| `sb-paulson` | ✅ Verify | ❌ No | Advisory; watch interest expense |
| `opdo` | Check SOP | ❌ No | Review SOP |

---

## Outputs

### Excel Workbook
Saved to `~/Desktop/MonthEndClose_{slug}_{YYYY-MM}_Run{N}.xlsx`

| Tab | Contents |
|---|---|
| **Close Checklist** | Dashboard — PASS/FAIL/ACTION per item + completion % + proposed JE count |
| **Trial Balance** | Full TB with debit/credit validation, per-account rows, totals |
| **Proposed Entries** | All JEs needed for missing/flagged items, sorted HIGH→MEDIUM→LOW |
| **CDC Log** | Status changes between runs — tracks close progress over time |

### Completion Score
```
Completion % = (PASS + N/A) / Total × 100
Close is "READY" when FAIL = 0 and ACTION = 0
```

### Proposed JE Format
Each proposed entry has:
- Debit Account
- Credit Account
- Amount (Decimal; "TBD" if unknown)
- Memo / basis
- Urgency: HIGH | MEDIUM | LOW

---

## CDC (Change Data Capture)

Cache file: `.cache/month-end-close/{slug}-{YYYY-MM}.json`

- First run: full snapshot saved, no delta
- Subsequent runs: shows status changes (FAIL→PASS = "Resolved ✅", PASS→FAIL = "Regressed ⚠️")
- Re-run the pipeline as items are resolved — CDC tracks progress automatically
- Use `--rerun` to force fresh QBO pulls without clearing CDC history

---

## Check Logic Summary

### Bank Recon
Checks `.cache/bank-reconciliation/{slug}.json` for a completed reconciliation matching the
close period end date. If found and `is_reconciled: true` → PASS. If different period or
missing → ACTION NEEDED with command to run.

### Trial Balance
Pulls QBO TB report via `qbo report tb`, sums all debit and credit columns, validates
`|debits - credits| < $0.02`. OUT OF BALANCE → FAIL with proposed suspense entry.

### AP / AR Aging
Scans QBO Balance Sheet for AP/AR account balances. Flags material balances ≥ $500 as
ACTION NEEDED. Proposes accrual entry for largest outstanding item > $2,500.

### Prepaid Amortization
Compares prior-month BS prepaid balances to current. If prior balance > $0 and no GL
activity or balance reduction → ACTION NEEDED with estimated monthly amort entry
(straight-line over 12 months as placeholder).

### Depreciation
Checks fixed asset accounts on BS + scans GL for depreciation/journal entries to
accumulated depreciation accounts. No activity on period with known fixed assets → ACTION NEEDED.

### Payroll Reconciliation
Sums payroll GL accounts for current period, compares to prior P&L payroll total.
Flags if variance > $500 or > 15% month-over-month.

### Revenue Recognition
Checks deferred revenue BS balance for movement. No change with known balance → ACTION NEEDED
with estimated recognition entry (straight-line over 12 months as placeholder).

### Accrued Expenses
Compares accrual account balances prior-vs-current. Flags individual account changes ≥ $1,000
or near-zero reversal from significant balance.

### Intercompany
Only runs if enabled by SOP. Checks for non-zero intercompany/due-to/due-from balances.
Net balance ≠ $0.02 → ACTION NEEDED with elimination entry.

---

## Dependencies

- Python 3.10+ with `openpyxl` (`pip install openpyxl`)
- Node.js QBO client with valid auth tokens
- Client SOP at `clients/{slug}/sop.md` (optional but recommended)
- Completed bank-recon run for `bank_recon` check to PASS

---

## Related Pipelines

| Pipeline | Script | Use |
|---|---|---|
| P&L Quick Compare | `pl-quick-compare.py` | Revenue/expense variance |
| P&L Deep Analysis | `pl-deep-analysis.py` | GL drill-down + accrual proposals |
| Bank Reconciliation | `bank-reconciliation.py` | Must complete before close PASS |

Bank Recon must be run first — `month-end-close` reads its CDC cache to verify completion.

---

## Notes

- All financial math uses Python `Decimal` for precision
- Proposed JE amounts marked "TBD" when determination requires human review (e.g. depreciation schedule)
- SOP parsing is additive: missing SOP = all checks enabled with defaults
- Run multiple times freely — CDC log accumulates close progress history

---
name: cash-flow-forecast
description: "Build a 13-week rolling cash flow forecast from QBO data with 3-scenario modeling (base, upside, downside). Pulls Balance Sheet and CF Statement for starting cash and trend baseline. Use when a client needs cash runway projection, burn rate analysis, or scenario-based liquidity planning. NOT for annual budgets, P&L variance analysis, or bank reconciliation."
license: MIT
summary:
  - Builds a 13-week rolling cash flow forecast from QBO data with 3-scenario modeling
  - Pulls live Balance Sheet (starting cash) + CF Statement (trend baseline) from QBO
  - Outputs Excel: Summary | Weekly Detail | Scenarios | Burn Rate | Assumptions | CDC Log
  - GlowLabs-type clients: runway/burn emphasis; SB Paulson-type: interest expense emphasis
  - CDC tracks forecast accuracy vs. prior runs (predicted vs. actual starting cash)
updated: 2026-03-17
metadata:
  openclaw:
    emoji: "💸"
---

# Cash Flow Forecast Skill

## When to Use
- Client asks for a 13-week (or rolling n-week) cash flow forecast
- Need to project runway for a high-burn client (GlowLabs-type)
- Monthly or quarterly cash flow advisory deliverable
- Any time cash position monitoring or scenario modeling is needed
- SOP for the client marks "Cash Flow Forecast: ✅"

## NOT for
- Historical cash flow analysis (use P&L Deep Analysis pipeline)
- Bank reconciliation (use bank-reconciliation.py)
- Full audit of cash flows (use QBO CF report directly)
- Balance sheet projections / full financial models

## Script Location
`scripts/pipelines/cash-flow-forecast.py`

## Quick Start
```bash
cd /Users/samshouse/.openclaw/workspace

# Standard 13-week forecast
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs

# 6-month history for better seasonality
python3 scripts/pipelines/cash-flow-forecast.py --slug sb-paulson --months 6

# Custom low-cash threshold (GlowLabs: high burn)
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs \
    --low-cash-threshold 50000 \
    --critical-threshold 25000 \
    --out ~/Desktop/reports

# Sandbox testing
python3 scripts/pipelines/cash-flow-forecast.py --slug glowlabs --sandbox
```

## All CLI Options
| Flag | Default | Description |
|------|---------|-------------|
| `--slug` | required | QBO company slug |
| `--weeks` | 13 | Forecast horizon (weeks) |
| `--months` | 3 | Months of CF history to pull (1–6) |
| `--low-cash-threshold` | 25000 | Warn when balance < X |
| `--critical-threshold` | 10000 | Critical alert when balance < X |
| `--account` | auto | Specific bank account name filter |
| `--out` | ~/Desktop | Output directory |
| `--sandbox` | false | Use QBO sandbox |

## Output Excel Tabs

### Tab 1: Cash Flow Summary
- Current cash position (all bank/cash accounts from Balance Sheet)
- 13-week KPI summary: total inflows, outflows, net flow, end balance
- Burn rate and runway in callout box
- Full weekly trajectory table with alert coloring
- SOP notes printed at top (client-specific warnings)

### Tab 2: Weekly Detail
- Matrix: each row = a CF subcategory, each column = a week
- Inflows section (green) → Outflows section (red)
- Subtotals, Net Flow row, Running Balance row
- Alert coloring: yellow = warn, orange/red = critical

### Tab 3: Scenarios
- Side-by-side: Base | Optimistic | Pessimistic
- 12 KPI rows: inflows, outflows, net, end balance, burn, runway, alert weeks
- Weekly balance comparison table across all 3 scenarios
- Best/Worst delta and % spread columns

### Tab 4: Burn Rate
- Detailed burn metrics (base case)
- Runway comparison table (all 3 scenarios)
- Week-by-week burn trend
- 🔴 High burn emphasis for GlowLabs-type clients

### Tab 5: Assumptions
- All parameters used (thresholds, adjustments, seasonality weights)
- Monthly averages per category (the baseline used)
- Client SOP config summary
- Methodology notes

### Tab 6: CDC Log
- Run-over-run changes: starting cash, burn rate, runway
- Forecast accuracy: compares prior-run predicted starting cash vs. actual
- First run: saves baseline; second+ run shows deltas

## Scenario Definitions
| Scenario | Collections | Expenses |
|----------|-------------|----------|
| Base | Historical avg | Historical avg |
| Optimistic | +10% | Unchanged |
| Pessimistic | -15% | +5% |

## Cash Flow Categories
Automatically classified from QBO CF rows:

**Operating — Inflows**
- `collections`: Revenue, AR, Net Income, payment processor receipts
- `interest`: Interest income

**Operating — Outflows**
- `payroll`: Wages, salaries, officer comp, Deel (GlowLabs)
- `rent`: Rent, lease, occupancy
- `vendors`: AP, COGS, professional fees, software, subscriptions
- `interest`: Interest expense (SB Paulson: material line)
- `taxes`, `insurance`

**Investing**
- `capex`: Equipment, property, asset purchases

**Financing**
- `debt`: Loans, line of credit, notes payable
- `equity`: Owner distributions/contributions
- `safe`: SAFE notes (GlowLabs)

## Client SOP Integration
The script reads `clients/{slug}/sop.md` automatically and adjusts:

| SOP Signal | Effect |
|------------|--------|
| "burn rate", "runway", "high cash burn" | Burn Rate tab emphasized, 🔴 alert |
| "interest expense" | Interest tracked separately |
| "POS collection", "collected at POS" | No AR lag — flat weekly inflow distribution |
| "accounts receivable" | AR-based: note 30-45 day receipt lag |
| "SAFE" | SAFE financing category added |
| "crypto", "wallet", "ETH" | Crypto wallet note added |
| "Deel" | Deel inbound classified as expense reduction |

## Seasonality Weights
Default week-of-month distribution (configurable in script CONFIG section):

| Week | Inflows | Outflows | Rationale |
|------|---------|----------|-----------|
| Week 1 (days 1–7) | 30% | 35% | Collections/payroll land early |
| Week 2 (days 8–14) | 20% | 20% | Mid-month quiet |
| Week 3 (days 15–21) | 20% | 25% | Mid-month payroll + rent |
| Week 4 (days 22–28) | 30% | 20% | End-month collections |
| Week 5 (overflow) | 0% | 0% | Rarely used |

POS clients (e.g. SB Paulson): inflows flattened to equal weekly distribution.

## CDC Accuracy Loop
1. **First run**: saves `starting_cash`, `first_week_balance`, `avg_monthly_burn`, `months_of_runway`
2. **Second run**: compares new `starting_cash` against prior `first_week_balance`
3. Accuracy % = how close the forecast was to reality
4. Burn rate and runway deltas tracked run-over-run
5. Cache stored in `.cache/cash-flow-forecast/{slug}.json`

## Low-Cash Alert Colors
| Color | Excel Fill | Condition |
|-------|-----------|-----------|
| ✅ OK | White | Balance ≥ low threshold |
| ⚠ Warn | Yellow | Balance < $25K (configurable) |
| 🔴 Critical | Orange/Red | Balance < $10K (configurable) |

## Client-Specific Notes

### GlowLabs (High Burn)
- `--low-cash-threshold 50000 --critical-threshold 25000` recommended
- SOP auto-detected: burn rate tab emphasized with red header
- SAFE financing and Deel categories auto-classified
- Runway is the #1 KPI — shown prominently in Summary tab

### SB Paulson / Willo Salons
- `--months 6` for better seasonality (salon business is seasonal)
- POS collection = no AR lag; inflows distributed flat across weeks
- Interest expense tracked as separate operating outflow category
- Cash Flow Forecast frequency: quarterly advisory (per SOP)

## Dependencies
```
pip install openpyxl
```
Node.js QBO client must be authenticated with a valid token.

## Output Naming
`CashFlowForecast_{slug}_{YYYY-MM-DD}.xlsx`
Default output: `~/Desktop/`

## Related Pipelines
- `pl-quick-compare.py` — P&L variance analysis
- `pl-deep-analysis.py` — Controller-level with GL drill-down
- `bank-reconciliation.py` — Bank statement reconciliation

---
name: bank-reconciliation
description: "Reconcile bank accounts against QuickBooks Online (QBO) for monthly close, discrepancy investigation, or audit workpapers. Use when a client needs GL vs bank balance matched, unrecorded items caught, or a reconciliation workpaper produced. NOT for payroll reconciliation, AR/AP aging, investment accounts, or intercompany eliminations."
license: MIT
metadata:
  openclaw:
    emoji: "🏦"
---

# Bank Reconciliation Skill

**Pipeline:** `scripts/pipelines/bank-reconciliation.py`  
**Day:** 2 of 14-Day Deterministic Pipeline Build

---

## When to Use This Skill

Use when a client needs their bank account reconciled against QBO:
- Monthly close reconciliation
- Investigating GL/bank balance discrepancies
- Catching unrecorded bank fees, interest, or deposits
- Producing auditor-ready reconciliation workpapers
- Tracking outstanding checks or deposits in transit

**NOT for:**
- Payroll reconciliation (use payroll-recon pipeline)
- AR/AP aging (use ar-collections pipeline)
- Investment/brokerage accounts
- Intercompany eliminations

---

## Requirements

```bash
pip install openpyxl
# Node.js QBO client must be connected:
node integrations/qbo-client/bin/qbo connect <slug>
```

---

## Usage Patterns

### 1. Standard Run (Chase, auto-detected)
```bash
python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/chase_feb2026.csv \
    --bank-ending-balance 42531.87
```

### 2. Specific Account Name
```bash
python3 scripts/pipelines/bank-reconciliation.py \
    --slug glowlabs \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/boa_feb2026.csv \
    --account "Business Checking" \
    --bank-ending-balance 18250.00
```

### 3. Wells Fargo (separate debit/credit columns)
```bash
python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/wf_feb.csv \
    --bank-format wellsfargo \
    --bank-ending-balance 55000.00
```

### 4. Custom Column Mapping (unknown CSV format)
```bash
python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/stmt.csv \
    --col-date "Trans Date" \
    --col-desc "Narrative" \
    --col-amount "Net Amount" \
    --bank-ending-balance 28000.00
```

### 5. Wider Date Window + Custom Output
```bash
python3 scripts/pipelines/bank-reconciliation.py \
    --slug sb-paulson \
    --end-date 2026-02-28 \
    --bank-csv ~/Downloads/chase_feb.csv \
    --date-window 5 \
    --bank-ending-balance 42531.87 \
    --out ~/Desktop/recon
```

---

## Arguments Reference

| Argument | Required | Description |
|---|---|---|
| `--slug` | ✅ | Company slug (must be connected in qbo-client) |
| `--end-date` | ✅ | Reconciliation as-of date (YYYY-MM-DD) |
| `--bank-csv` | ✅ | Path to bank statement CSV file |
| `--bank-ending-balance` | Recommended | Bank statement ending balance (float) |
| `--account` | Optional | Account name to match on Balance Sheet |
| `--bank-format` | Optional | `auto` (default), `chase`, `bofa`, `wellsfargo`, `generic` |
| `--col-date` | Optional | Override date column header |
| `--col-desc` | Override | Override description column header |
| `--col-amount` | Optional | Override single amount column header |
| `--col-debit` | Optional | Override debit/withdrawal column header |
| `--col-credit` | Optional | Override credit/deposit column header |
| `--date-window` | Optional | Days for exact match window (default: 3, fuzzy: 2×) |
| `--out` | Optional | Output directory (default: ~/Desktop) |
| `--sandbox` | Optional | Use QBO sandbox environment |

---

## Supported Bank CSV Formats

| Format | Date Column | Amount Column | Notes |
|---|---|---|---|
| `chase` | Transaction Date | Amount | Negative = withdrawal |
| `bofa` | Date | Amount | Negative = withdrawal |
| `wellsfargo` | Date | Withdrawals + Deposits | Two separate columns |
| `generic` | Tries common names | Tries common names | Use `--col-*` if fails |

Auto-detection reads column headers and picks the best format. Use `--bank-format` to override.

---

## Matching Logic

**Pass 1 — Exact:** Amount match within ±$0.01, date within ±`date-window` days (default 3).

**Pass 2 — Fuzzy Date:** Amount match within ±$0.01, date within ±`2×date-window` days (default 6).

**Pass 3 — Fuzzy Vendor:** Amount within ±$1.00, vendor key substring match (strips check numbers, dates, ref numbers), any date.

Unmatched after all 3 passes → flagged in Unmatched tabs.

---

## Reconciliation Equation

```
Book Balance (QBO BS)
+ Deposits in Transit     (book has it, bank hasn't cleared it)
- Outstanding Checks      (book has it, bank hasn't cleared it)
= Adjusted Book Balance

Bank Statement Ending Balance
+ Bank Credits Not in Book  (bank has it, not yet in QBO)
+ Bank Charges Not in Book  (negative; bank has it, not yet in QBO)
= Adjusted Bank Balance

Adjusted Book Balance = Adjusted Bank Balance → RECONCILED ✅
```

---

## Adjusting Entry Auto-Suggestions

The pipeline auto-classifies unmatched bank transactions into:

| Category | Keywords | Suggested Entry |
|---|---|---|
| Bank Fee | "service charge", "monthly fee", "wire fee", "nsf fee" | DR Bank Service Charges / CR Checking |
| Interest Earned | "interest", "dividend" | DR Checking / CR Interest Income |
| Direct Deposit | "payroll", "ach credit", "zelle" | DR Checking / CR AR/Revenue |
| Payment Processor | "stripe", "square", "paypal" | DR Checking / CR Undeposited Funds |
| NSF Returned | "returned item", "returned check" | DR Returned Check Expense / CR Checking |
| Unclassified | (no keyword match) | TBD — review manually |

---

## Output: Excel Workbook

**File:** `BankRecon_{slug}_{YYYYMMDD}.xlsx` (saved to `--out` or `~/Desktop`)

| Tab | Contents |
|---|---|
| **Reconciliation Summary** | Book/bank balance sections, stats, reconciled status badge |
| **Matched** | All matched pairs with match type (exact/fuzzy), amounts, date diff |
| **Unmatched (Book)** | Outstanding checks + deposits in transit (in QBO, not cleared) |
| **Unmatched (Bank)** | Bank items not in QBO (fees, interest, unrecorded deposits) |
| **Adjusting Entries** | Suggested DR/CR journal entries for each unmatched bank item |
| **CDC Log** | Changes since last reconciliation run (book balance, diff, etc.) |

---

## CDC (Change Data Capture)

Cache stored at: `.cache/bank-reconciliation/{slug}.json`

Tracks changes in:
- Book Balance
- Adjusted Book/Bank Balance
- Deposits in Transit
- Outstanding Checks
- Reconciling Difference

On first run: "First run — snapshot saved." On subsequent runs: only changed fields shown. Saves 90%+ API calls on recurring monthly reconciliations.

---

## Design Notes

- All financial math uses Python `Decimal` — no float drift
- Amount sign convention: positive = deposit/inflow, negative = payment/outflow (both book and bank)
- GL pull uses QBO `GeneralLedger` report filtered to the reconciliation period
- If GL pull fails (e.g. permissions), pipeline falls back to Balance Sheet only (matching disabled)
- Bank CSV preamble rows (Chase header lines before data) are auto-skipped

---

## Typical Workflow

```
1. Client sends bank statement PDF → export as CSV from bank website
2. Run pipeline with --slug, --end-date, --bank-csv, --bank-ending-balance
3. Review Reconciliation Summary tab — check RECONCILED badge
4. If difference exists: check Unmatched (Book) for outstanding items, Unmatched (Bank) for unrecorded items
5. Post adjusting entries from Adjusting Entries tab
6. Re-run pipeline — difference should clear to $0.00
7. Save Excel to client Google Drive folder
```

---
name: saas-churn-analysis
description: >
  SaaS churn and retention analysis: cohort-based churn rates, retention curves, revenue churn vs logo churn,
  at-risk customer identification, expansion vs contraction MRR, churn recovery playbooks, and net revenue
  retention (NRR) benchmarking. Produces investor-ready retention charts and actionable recovery plans.
  Use when: analyzing why customers are churning, building cohort retention tables, calculating NRR/GRR,
  identifying at-risk accounts before they cancel, or presenting retention data to investors/board.
  NOT for: executing churn recovery outreach (use CRM/email tools), real-time subscription billing changes
  (use billing platform APIs), general SaaS KPI dashboards (use saas-metrics-dashboard), or revenue
  forecasting without churn context (use startup-financial-model).
version: 1.0.0
author: PrecisionLedger
tags:
  - saas
  - churn
  - retention
  - cohort
  - nrr
  - subscription
  - metrics
  - investors
---

# SaaS Churn Analysis Skill

Deep-dive churn and retention analysis for SaaS businesses. Build cohort tables, calculate NRR/GRR, identify at-risk accounts, and produce investor-ready retention metrics with actionable recovery playbooks.

---

## When to Use This Skill

**Trigger phrases:**
- "Why are customers churning?"
- "What's our retention rate?"
- "Build a cohort analysis"
- "Show me net revenue retention"
- "Which accounts are at risk of canceling?"
- "Investor wants to see our logo churn"
- "What's our gross/net dollar retention?"
- "Analyze our expansion vs contraction MRR"

**NOT for:**
- Executing recovery outreach (emails, calls) — use CRM/email tools
- Billing changes, refunds, or cancellation processing — use billing platform
- General MRR tracking — use `saas-metrics-dashboard` or `subscription-revenue-tracker`
- Revenue forecasting — use `startup-financial-model`
- Customer success management — use a CS platform skill

---

## Core Churn Definitions

### Logo Churn (Customer Churn)
```
Logo Churn Rate (monthly) = Customers Lost / Customers at Start of Period

Example:
  Start of month: 200 customers
  Canceled: 5
  Logo churn rate: 5/200 = 2.5%
```

### Revenue Churn
```
Gross Revenue Churn Rate = MRR Lost to Cancellations / MRR at Start of Period

Example:
  Start MRR: $100,000
  Churned MRR: $4,000 (from cancellations)
  Gross churn: 4%
```

### Net Revenue Retention (NRR / NDR)
```
NRR = (Beginning MRR + Expansion MRR - Contraction MRR - Churned MRR) / Beginning MRR × 100

Components:
  + Expansion MRR: upsells, upgrades, seat additions from existing customers
  - Contraction MRR: downgrades, reduced seats
  - Churned MRR: cancellations

Example:
  Beginning MRR: $100,000
  Expansion: +$8,000
  Contraction: -$2,000
  Churn: -$4,000
  NRR = ($100,000 + $8,000 - $2,000 - $4,000) / $100,000 = 102%
```

**NRR Benchmarks (SaaS industry):**
| NRR | Signal |
|-----|--------|
| >120% | Elite (enterprise, product-led) |
| 110–120% | Strong — expansion > churn |
| 100–110% | Healthy |
| 90–100% | Adequate — watch churn trends |
| <90% | Red flag — structural problem |

### Gross Revenue Retention (GRR)
```
GRR = (Beginning MRR - Contraction MRR - Churned MRR) / Beginning MRR × 100
     (excludes expansion — pure retention, no upsell credit)

Healthy GRR benchmarks:
  Enterprise SaaS: >90%
  Mid-market: >85%
  SMB SaaS: >75%
```

---

## Cohort Analysis

### Building a Cohort Retention Table

Track customers by their **acquisition month** and measure % remaining in each subsequent month:

```python
import pandas as pd
from datetime import datetime

def build_cohort_table(subscriptions_df: pd.DataFrame) -> pd.DataFrame:
    """
    Build a cohort retention table from subscription data.
    
    Input DataFrame columns:
        - customer_id: str
        - signup_date: datetime
        - cancel_date: datetime | None (None = still active)
    
    Returns:
        Pivot table: rows = cohort month, columns = months_since_signup,
        values = retention percentage
    """
    df = subscriptions_df.copy()
    df['cohort_month'] = df['signup_date'].dt.to_period('M')
    df['active_through'] = df['cancel_date'].fillna(pd.Timestamp.now())
    
    rows = []
    for cohort, group in df.groupby('cohort_month'):
        cohort_size = len(group)
        for month_offset in range(0, 25):  # 0–24 months
            cutoff = cohort.to_timestamp() + pd.DateOffset(months=month_offset)
            active = group[group['active_through'] >= cutoff].shape[0]
            retention = active / cohort_size * 100
            rows.append({
                'cohort': str(cohort),
                'month': month_offset,
                'cohort_size': cohort_size,
                'active': active,
                'retention_pct': round(retention, 1)
            })
    
    result = pd.DataFrame(rows)
    pivot = result.pivot(index='cohort', columns='month', values='retention_pct')
    return pivot
```

**Example cohort table output:**
```
Cohort     | M0    | M1    | M3    | M6    | M12
-----------|-------|-------|-------|-------|------
2025-01    | 100%  | 91%   | 81%   | 72%   | 58%
2025-02    | 100%  | 93%   | 84%   | 76%   | —
2025-03    | 100%  | 89%   | 79%   | —     | —
2025-04    | 100%  | 94%   | —     | —     | —
```

### Revenue Cohort (Dollar Retention)

Track MRR retained and expanded per cohort:

```python
def revenue_cohort_table(mrr_events_df: pd.DataFrame) -> pd.DataFrame:
    """
    Revenue cohort analysis tracking MRR per acquisition cohort.
    
    Input DataFrame columns:
        - customer_id: str
        - event_date: datetime
        - event_type: str  # 'signup', 'expansion', 'contraction', 'churn'
        - mrr_change: float
    
    Returns:
        Cohort revenue retention table (% of original MRR retained+expanded)
    """
    # Group by signup cohort
    signups = mrr_events_df[mrr_events_df['event_type'] == 'signup'].copy()
    signups['cohort_month'] = signups['event_date'].dt.to_period('M')
    
    # For each cohort, track MRR over time
    # NRR by cohort = sum(all MRR changes for cohort customers) / initial MRR
    pass
```

### Churn Curve Analysis

Identify when in the customer lifecycle churn peaks:

```
Early churn (M1-M3): Onboarding failure, value not delivered
  → Diagnosis: activation rate, time-to-first-value, support tickets
  
Mid-term churn (M4-M12): Competitive displacement, budget cuts
  → Diagnosis: NPS trends, feature adoption, renewal engagement
  
Late churn (M12+): Strategic shifts, contract terms, enterprise competition
  → Diagnosis: executive sponsor changes, usage trends, renewal conversations
```

**Churn by tenure bucket:**
```python
def churn_by_tenure(subscriptions_df: pd.DataFrame) -> dict:
    """Calculate churn rate for different tenure buckets."""
    buckets = {
        '0-3mo': (0, 90),
        '3-6mo': (90, 180),
        '6-12mo': (180, 365),
        '12-24mo': (365, 730),
        '24mo+': (730, float('inf'))
    }
    
    results = {}
    for bucket_name, (min_days, max_days) in buckets.items():
        mask = (
            (subscriptions_df['tenure_days'] >= min_days) &
            (subscriptions_df['tenure_days'] < max_days)
        )
        bucket_df = subscriptions_df[mask]
        if len(bucket_df) == 0:
            continue
        churned = bucket_df[bucket_df['cancel_date'].notna()].shape[0]
        results[bucket_name] = {
            'total_customers': len(bucket_df),
            'churned': churned,
            'churn_rate_pct': round(churned / len(bucket_df) * 100, 1)
        }
    return results
```

---

## At-Risk Customer Identification

### Churn Risk Scoring

Score each active customer by leading indicators:

```python
CHURN_RISK_WEIGHTS = {
    'days_since_last_login': 0.25,       # Usage drop
    'feature_adoption_pct': -0.20,       # Inverse: more features = lower risk
    'support_tickets_30d': 0.15,         # Escalations
    'nps_score': -0.15,                  # Inverse: high NPS = lower risk
    'days_to_renewal': -0.10,            # Closer renewal = higher urgency
    'billing_failures_90d': 0.15,        # Payment issues
}

def churn_risk_score(customer: dict) -> float:
    """
    Calculate 0-100 churn risk score for a customer.
    Higher = more likely to churn.
    
    Inputs:
        customer: dict with keys matching CHURN_RISK_WEIGHTS
    
    Returns:
        Risk score 0-100 (>70 = high risk, 40-70 = medium, <40 = low)
    """
    raw_score = 0
    for factor, weight in CHURN_RISK_WEIGHTS.items():
        if factor in customer:
            # Normalize each factor to 0-100 scale first
            normalized = normalize_factor(factor, customer[factor])
            raw_score += normalized * weight
    
    # Scale to 0-100
    return max(0, min(100, raw_score * 100 + 50))

def get_at_risk_accounts(customers: list, threshold: float = 70.0) -> list:
    """Return customers with churn risk score above threshold, sorted by MRR."""
    at_risk = [
        {**c, 'risk_score': churn_risk_score(c)}
        for c in customers
    ]
    return sorted(
        [c for c in at_risk if c['risk_score'] >= threshold],
        key=lambda x: x.get('mrr', 0),
        reverse=True  # Highest MRR first — prioritize by revenue impact
    )
```

### Early Warning Signals

**Usage-based signals (product telemetry):**
```
🔴 High risk:
  - No login in 14+ days (was weekly user)
  - DAU/MAU ratio dropped >50% MoM
  - Core feature not used in 30 days
  - Below 20% feature adoption vs peers

🟡 Medium risk:
  - Login frequency dropped >30% MoM
  - Support ticket with "cancel" or "refund" keyword
  - NPS score ≤ 6 (detractor)
  - Seat count reduced

🟢 Healthy signals:
  - Expanded seats or upgraded tier
  - Used 3+ core features this month
  - NPS ≥ 9 (promoter)
  - Referred another customer
```

**Financial signals:**
```
🔴 High risk:
  - Payment failure (retry in progress)
  - Requested invoice-based payment shift (budget freeze)
  - Contract not opened with 30 days to renewal

🟡 Medium risk:
  - Asked about pricing alternatives
  - Billing contact changed
  - Discount request submitted
```

---

## MRR Movement Analysis

### MRR Bridge

Decompose monthly MRR change into components:

```
MRR Bridge: January → February

Beginning MRR:     $100,000
+ New Business:      +$8,500   (23 new customers × $370 avg)
+ Expansion:         +$3,200   (upgrades + seat additions)
- Contraction:       -$1,100   (downgrades + seat reductions)
- Churn:             -$4,300   (11 cancellations × $390 avg)
= Ending MRR:      $106,300

Net New MRR:        +$6,300
MoM Growth:          6.3%
```

**Python MRR bridge calculation:**
```python
from dataclasses import dataclass

@dataclass
class MRRBridge:
    period: str
    beginning_mrr: float
    new_mrr: float           # New customers
    expansion_mrr: float     # Upsells/upgrades
    contraction_mrr: float   # Downgrades (negative or positive — store as positive)
    churned_mrr: float       # Cancellations (store as positive)
    
    @property
    def ending_mrr(self) -> float:
        return self.beginning_mrr + self.new_mrr + self.expansion_mrr - self.contraction_mrr - self.churned_mrr
    
    @property
    def net_new_mrr(self) -> float:
        return self.ending_mrr - self.beginning_mrr
    
    @property
    def growth_rate_pct(self) -> float:
        return self.net_new_mrr / self.beginning_mrr * 100 if self.beginning_mrr else 0
    
    @property
    def quick_ratio(self) -> float:
        """SaaS Quick Ratio = (New + Expansion) / (Contraction + Churn). >4 = healthy."""
        numerator = self.new_mrr + self.expansion_mrr
        denominator = self.contraction_mrr + self.churned_mrr
        return numerator / denominator if denominator else float('inf')
    
    def to_summary(self) -> str:
        return (
            f"MRR Bridge ({self.period})\n"
            f"  Beginning: ,.0f\n"
            f"  + New:       ,.0f\n"
            f"  + Expansion: ,.0f\n"
            f"  - Contraction: ,.0f\n"
            f"  - Churn:     ,.0f\n"
            f"  = Ending:  ,.0f\n"
            f"  Growth: {self.growth_rate_pct:.1f}% | Quick Ratio: {self.quick_ratio:.1f}x"
        )
```

**SaaS Quick Ratio benchmarks:**
| Quick Ratio | Signal |
|-------------|--------|
| >4 | Elite growth efficiency |
| 2–4 | Healthy |
| 1–2 | Growing but inefficient — churn drag |
| <1 | Shrinking — churn exceeds new + expansion |

---

## Churn Recovery Playbooks

### Playbook 1: Early Churn (Month 1-3)

**Root cause:** Failed onboarding, didn't reach first value moment

**Diagnosis questions:**
```
□ Did they complete onboarding? (activation rate)
□ Did they use the core feature at least once? (activation event)
□ How long did it take to reach first value moment?
□ Did they get a human touchpoint in first 48 hours?
```

**Recovery actions:**
```
Day 1-7:   Personal outreach from CSM — "What would make this a 10/10?"
Day 7-14:  Offer 1:1 onboarding session + extend trial if applicable
Day 14-21: Share customer success story in their industry/use case
Day 21-30: Executive touchpoint if MRR > $500/mo
```

### Playbook 2: Mid-Term Churn (Month 4-12)

**Root cause:** Value plateau, competitive evaluation, budget pressure

**Diagnosis questions:**
```
□ Usage trend: up, flat, or declining in last 60 days?
□ When did they last use the feature most tied to their stated goal?
□ Any support escalations or complaints in the last 90 days?
□ Have they been pitched by a competitor? (ask directly)
□ Is this a budget-driven decision or product-driven?
```

**Recovery actions by root cause:**
```
Budget:
  → Offer pause plan (90-day pause vs cancel)
  → Right-size to smaller plan vs lose them entirely
  → Annual prepay at 20% discount to lock in

Product gaps:
  → Roadmap call with PM — "here's what's coming"
  → Workaround documentation for their specific use case
  → Connect to power-user customer for peer validation

Competitor evaluation:
  → Direct competitive comparison matrix
  → Migration cost analysis (switching is expensive)
  → Win-back offer if they've already left (45-day re-engagement)
```

### Playbook 3: Renewal-at-Risk (30-60 days to renewal)

**Proactive renewal pipeline:**
```
60 days out:
  □ Usage review: send personalized "Your results with [Product]" email
  □ Identify any open issues — resolve before renewal conversation

45 days out:
  □ QBR or check-in call — confirm value, surface upsell opportunity
  □ Flag to AE if NPS < 7 or usage declining

30 days out:
  □ Renewal proposal sent — include current plan + upsell option
  □ Executive sponsor confirmation (for accounts >$1k/mo)

14 days out:
  □ Follow-up if no response — switch to phone
  □ Escalate to manager if no reply

7 days out:
  □ Final decision call — accept reduced terms if needed to retain
```

---

## Output Formats

### Investor-Ready Retention Summary

```json
{
  "period": "Q4 2025",
  "generated_at": "2026-01-15",
  "retention_metrics": {
    "logo_churn_rate_monthly": 2.1,
    "mrr_gross_churn_rate_monthly": 3.8,
    "net_revenue_retention_pct": 108,
    "gross_revenue_retention_pct": 96.2,
    "quick_ratio": 3.2
  },
  "mrr_bridge": {
    "beginning_mrr": 285000,
    "new_mrr": 42000,
    "expansion_mrr": 18500,
    "contraction_mrr": 4200,
    "churned_mrr": 10800,
    "ending_mrr": 330500
  },
  "at_risk_pipeline": {
    "high_risk_count": 8,
    "high_risk_mrr_at_risk": 24600,
    "medium_risk_count": 15,
    "medium_risk_mrr_at_risk": 38200
  },
  "cohort_highlights": {
    "best_cohort": { "month": "2025-03", "m12_retention": 74 },
    "worst_cohort": { "month": "2025-08", "m3_retention": 71 },
    "avg_m12_retention": 68.5
  },
  "benchmarks": {
    "nrr_vs_industry": "above_median",
    "grr_vs_industry": "top_quartile",
    "logo_churn_vs_industry": "median"
  }
}
```

### CSV Export for Spreadsheets

```
cohort_retention_csv_template:
Cohort,Size,M1,M2,M3,M6,M9,M12,M18,M24
2025-01,45,91%,84%,81%,73%,67%,61%,55%,49%
2025-02,52,93%,87%,83%,—,—,—,—,—
...
```

---

## Step-by-Step Workflow

### Full Churn Audit

**Step 1: Data collection**
```
□ Customer list with signup date and cancel date (if churned)
□ MRR per customer per month (last 12 months)
□ Usage data: logins, feature events (from product analytics)
□ NPS scores if available
□ Cancellation reason codes (from offboarding flow)
```

**Step 2: Calculate headline metrics**
- Logo churn rate (monthly and annualized)
- Gross and net revenue retention
- Quick ratio
- Churn by tenure bucket

**Step 3: Build cohort table**
- M0–M12 retention by acquisition cohort
- Identify best and worst cohorts — find what's different

**Step 4: MRR bridge (last 6 months)**
- New vs expansion vs contraction vs churn
- Trend analysis: is churn improving or worsening?

**Step 5: At-risk identification**
- Score all active customers by churn risk signals
- Prioritize by MRR at risk (highest first)
- Output: top 10 at-risk accounts with reasons

**Step 6: Root cause analysis**
- What's driving churn? (onboarding failure, competition, budget, product gaps)
- Which segments have highest churn? (plan size, industry, use case, acquisition channel)

**Step 7: Recommend playbook**
- Match root cause to recovery playbook
- Estimate MRR at stake if intervention succeeds (recovery potential)
- Prioritize actions by expected ROI

---

## Churn by Segment Analysis

Segment churn to find structural patterns:

```python
def churn_by_segment(subscriptions_df: pd.DataFrame, segment_col: str) -> pd.DataFrame:
    """
    Calculate churn rate by customer segment.
    
    Args:
        segment_col: column name to segment by (e.g., 'plan', 'industry', 'company_size')
    
    Returns:
        DataFrame with churn rate per segment, sorted by MRR impact
    """
    results = []
    for segment, group in subscriptions_df.groupby(segment_col):
        total = len(group)
        churned = group[group['cancel_date'].notna()].shape[0]
        total_mrr = group['mrr'].sum()
        churned_mrr = group[group['cancel_date'].notna()]['mrr'].sum()
        
        results.append({
            'segment': segment,
            'total_customers': total,
            'churned_customers': churned,
            'logo_churn_pct': round(churned / total * 100, 1),
            'total_mrr': total_mrr,
            'churned_mrr': churned_mrr,
            'mrr_churn_pct': round(churned_mrr / total_mrr * 100, 1) if total_mrr else 0
        })
    
    return pd.DataFrame(results).sort_values('churned_mrr', ascending=False)
```

**Key segments to analyze:**
- By plan tier (free trial → paid → enterprise)
- By acquisition channel (organic, paid, referral)
- By company size (SMB, mid-market, enterprise)
- By industry vertical
- By geographic region
- By sales rep / CSM (is one rep's book churning faster?)

---

## Integration Points

- **`saas-metrics-dashboard`** — Display NRR, GRR, and churn rate KPIs in dashboard
- **`kpi-alert-system`** — Trigger alerts when monthly churn exceeds threshold
- **`startup-financial-model`** — Feed churn rate assumptions into revenue forecasts
- **`subscription-revenue-tracker`** — MRR bridge data source for churn calculations
- **`crypto-tax-agent`** — N/A (different domain)

---

## Key Formulas Cheat Sheet

```
Logo Churn Rate (monthly)  = Customers Lost / Customers at Start × 100
Annual Logo Churn          = 1 - (1 - monthly_churn)^12 × 100
Gross Revenue Retention    = (BOM MRR - Contraction - Churn) / BOM MRR × 100
Net Revenue Retention      = (BOM MRR + Expansion - Contraction - Churn) / BOM MRR × 100
Quick Ratio                = (New MRR + Expansion MRR) / (Contraction MRR + Churned MRR)
LTV (with churn)           = ARPU / Monthly Churn Rate
Avg Customer Lifetime      = 1 / Monthly Churn Rate (in months)

Rule of Thumb:
  2% monthly logo churn  = ~21% annual churn (B2B SMB benchmark)
  0.5% monthly logo churn = ~6% annual churn (enterprise benchmark)
  NRR >100% means you grow from existing base alone — key investor signal
```

---
name: saas-metrics-dashboard
description: >
  SaaS metrics calculator and dashboard builder for subscription businesses.
  Compute MRR, ARR, churn rate, NRR/GRR, LTV, CAC, CAC payback period,
  magic number, Rule of 40, quick ratio, and cohort retention. Accepts raw
  subscription data and outputs a structured metrics snapshot ready for
  investor reporting, board decks, or feeding into kpi-alert-system and
  startup-financial-model skills. Use when founders, CFOs, or analysts need
  a full SaaS health check, want to track metrics over time, or are preparing
  investor materials.
  NOT for: e-commerce or transactional businesses (no recurring revenue),
  real-time streaming data pipelines (use a dedicated BI tool), or QBO
  bookkeeping reconciliation (use qbo-automation). Not a replacement for
  a full data warehouse — designed for structured inputs and spot analysis.
version: 1.0.0
author: PrecisionLedger
tags:
  - saas
  - metrics
  - finance
  - startups
  - subscription
  - mrr
  - churn
  - ltv
  - investors
---

# SaaS Metrics Dashboard Skill

Calculate every critical SaaS metric from raw subscription data and produce an investor-ready health snapshot. This skill guides Sam Ledger through computing MRR/ARR, churn, retention, unit economics (LTV/CAC), growth efficiency, and Rule of 40 scoring — then formats the output for board decks, investor updates, or feeding into **kpi-alert-system** threshold monitoring.

---

## When to Use This Skill

**Trigger phrases:**
- "What's our MRR / ARR?"
- "Calculate churn rate for last quarter"
- "Give me our LTV:CAC ratio"
- "Build a SaaS metrics snapshot"
- "Are we Rule of 40 compliant?"
- "Show net revenue retention"
- "Prepare metrics for the investor update"
- "What's our quick ratio / magic number?"
- "Track cohort retention"

**Fits between:**
- `startup-financial-model` — feeds historical actuals into the 3-statement model
- `kpi-alert-system` — outputs threshold-ready metric values for alert monitoring
- `investor-memo-generator` — supplies the metrics section of investor memos

**NOT for:**
- Businesses without recurring subscription revenue
- Real-time live dashboards (needs a BI tool like Metabase/Looker)
- Revenue recognition accounting (use `revenue-recognition-agent`)
- QBO bookkeeping entries (use `qbo-automation`)

---

## Core Metric Definitions

### Revenue Metrics

| Metric | Formula | Benchmark |
|--------|---------|-----------|
| MRR | Sum of all monthly recurring charges | — |
| ARR | MRR × 12 | — |
| New MRR | MRR from new customers this period | — |
| Expansion MRR | Upsells + seat adds from existing customers | — |
| Churned MRR | MRR lost from cancellations | — |
| Contraction MRR | Downgrades from existing customers | — |
| Net New MRR | New + Expansion − Churned − Contraction | Positive = growing |

### Retention Metrics

| Metric | Formula | Benchmark |
|--------|---------|-----------|
| Logo Churn | Customers churned / Customers at start | <5%/yr for SMB, <2% enterprise |
| Revenue Churn (GRR) | Churned+Contraction MRR / MRR at start | >80% GRR good |
| Net Revenue Retention (NRR) | (Start MRR + Expansion − Churn − Contraction) / Start MRR | >110% excellent, >100% good |
| Quick Ratio | (New + Expansion) / (Churned + Contraction) | >4 = excellent, >2 = healthy |

### Unit Economics

| Metric | Formula | Benchmark |
|--------|---------|-----------|
| LTV | ARPU / Monthly Churn Rate | 3× CAC minimum |
| CAC | (S&M Spend) / New Customers | — |
| LTV:CAC | LTV / CAC | >3:1 healthy, >5:1 strong |
| CAC Payback | CAC / (ARPU × Gross Margin%) | <12mo excellent, <18mo good |
| Magic Number | Net New ARR / Prior Quarter S&M Spend | >1.5 = efficient |

### Growth Efficiency

| Metric | Formula | Benchmark |
|--------|---------|-----------|
| MoM Growth | (MRR This Mo − Prior Mo) / Prior Mo | Top decile: 15%+ early stage |
| Rule of 40 | ARR Growth Rate % + FCF Margin % | ≥40 = healthy |
| Burn Multiple | Net Burn / Net New ARR | <1 = excellent, <2 = good |

---

## Data Inputs Required

Minimum viable inputs:
```
Period: [month/quarter]
MRR at start of period: $___
New MRR: $___
Expansion MRR: $___
Churned MRR: $___
Contraction MRR: $___
New customers acquired: ___
Customers churned: ___
Total customers at start: ___
S&M spend (period): $___
Gross margin %: ___
ARR growth rate (YoY %): ___
FCF or operating cash flow: $___
```

Optional (for deeper analysis):
```
Cohort data (monthly signup + retention by cohort)
Plan-level MRR breakdown
Customer segment breakdown (SMB / Mid-Market / Enterprise)
NPS or CSAT scores
```

---

## Computation Walkthrough

### Step 1: Validate Inputs
- Confirm all required fields are present
- Check: New MRR + Expansion − Churn − Contraction = Net New MRR
- Flag negative MRR values or impossible churn rates (>100%)

### Step 2: Calculate Revenue Metrics
```python
net_new_mrr = new_mrr + expansion_mrr - churned_mrr - contraction_mrr
arr = (mrr_start + net_new_mrr) * 12
```

### Step 3: Retention Metrics
```python
logo_churn = customers_churned / customers_start
grr = 1 - ((churned_mrr + contraction_mrr) / mrr_start)
nrr = (mrr_start + expansion_mrr - churned_mrr - contraction_mrr) / mrr_start
quick_ratio = (new_mrr + expansion_mrr) / (churned_mrr + contraction_mrr)
```

### Step 4: Unit Economics
```python
arpu = (mrr_start + net_new_mrr) / (customers_start + new_customers - customers_churned)
ltv = arpu / (logo_churn / 12)  # annualized to monthly churn
cac = sm_spend / new_customers
ltv_cac = ltv / cac
cac_payback_months = cac / (arpu * gross_margin)
magic_number = (net_new_arr) / prior_sm_spend
```

### Step 5: Growth Efficiency
```python
rule_of_40 = arr_growth_pct + fcf_margin_pct
burn_multiple = abs(net_burn) / net_new_arr  # only if burning cash
```

### Step 6: Score & Grade
Apply benchmark thresholds and assign health grades:
- 🟢 Green: At or above benchmark
- 🟡 Yellow: Within 20% of benchmark
- 🔴 Red: Below benchmark threshold

---

## Output Format

### Snapshot Output (Markdown/Structured)

```
═══════════════════════════════════════
  SAAS METRICS SNAPSHOT — [Period]
  Company: [Name] | As of: [Date]
═══════════════════════════════════════

REVENUE
  MRR:              $___,___
  ARR:              $___,___
  Net New MRR:      $___,___  (New: $X | Exp: $X | Churn: -$X)
  MoM Growth:       X.X%  [🟢/🟡/🔴]

RETENTION
  NRR:              XXX%  [🟢/🟡/🔴]  (benchmark: >110%)
  GRR:              XX%   [🟢/🟡/🔴]  (benchmark: >80%)
  Logo Churn:       X.X%  [🟢/🟡/🔴]  (benchmark: <5%/yr)
  Quick Ratio:      X.X   [🟢/🟡/🔴]  (benchmark: >2)

UNIT ECONOMICS
  LTV:CAC:          X.Xx  [🟢/🟡/🔴]  (benchmark: >3:1)
  CAC Payback:      XX mo [🟢/🟡/🔴]  (benchmark: <18mo)
  Magic Number:     X.X   [🟢/🟡/🔴]  (benchmark: >1.5)

EFFICIENCY
  Rule of 40:       XX    [🟢/🟡/🔴]  (benchmark: ≥40)
  Burn Multiple:    X.X   [🟢/🟡/🔴]  (benchmark: <2)

OVERALL HEALTH:  🟢 Strong / 🟡 Watch / 🔴 At Risk
TOP RISKS:  [1-3 specific metrics to address]
```

### CSV/JSON Output (for kpi-alert-system)
When feeding downstream systems, output as:
```json
{
  "period": "2026-Q1",
  "mrr": 125000,
  "arr": 1500000,
  "nrr": 118,
  "grr": 87,
  "logo_churn_annual": 4.2,
  "quick_ratio": 3.1,
  "ltv_cac": 4.5,
  "cac_payback_months": 11,
  "magic_number": 1.8,
  "rule_of_40": 52,
  "burn_multiple": 0.9
}
```

---

## Cohort Retention Analysis

When cohort data is available, build a retention table:

```
Cohort  Mo1   Mo2   Mo3   Mo6   Mo12
Jan-25  100%  89%   82%   74%   68%
Feb-25  100%  91%   85%   77%   —
Mar-25  100%  88%   81%   —     —
```

Key outputs:
- **Average Day-30 retention** (target: >85% for SMB, >90% enterprise)
- **12-month retention** (logo) — critical for LTV calculation
- **Revenue cohort retention** (expansion often offsets logo churn)

---

## Segment Benchmarks

### By Stage
| Stage | ARR | NRR Target | Logo Churn | Rule of 40 |
|-------|-----|-----------|------------|------------|
| Pre-Seed | <$1M | >100% | <10% | N/A |
| Seed | $1-5M | >105% | <8% | Growth-driven |
| Series A | $5-20M | >110% | <5% | ≥20 |
| Series B+ | $20M+ | >120% | <3% | ≥40 |

### By Customer Segment
| Segment | NRR Target | Logo Churn | CAC Payback |
|---------|-----------|------------|-------------|
| SMB | >100% | <10%/yr | <6 months |
| Mid-Market | >110% | <5%/yr | <12 months |
| Enterprise | >120% | <2%/yr | <18 months |

---

## Integration with Other Skills

### → startup-financial-model
Pass current-period actuals as seed inputs for the 3-statement model:
- Use NRR to project expansion revenue
- Use logo churn to model customer count trajectory
- CAC payback informs S&M budget efficiency

### → kpi-alert-system
Register thresholds for automated alerts:
```
NRR < 100%       → CRITICAL alert (negative net expansion)
Logo churn > 7%  → WARNING alert (annualized)
Quick ratio < 2  → WARNING alert
LTV:CAC < 3      → WARNING alert
Rule of 40 < 30  → INFO alert
```

### → investor-memo-generator
Feed the snapshot JSON into the metrics section of investor memos. Include MoM/QoQ trends, not just point-in-time values.

---

## Examples

### Example 1: Quick Snapshot
**Input:** "Our MRR is $125K. We added $18K new, $12K expansion, lost $8K churn, $3K contraction. 210 customers, lost 7. Spent $45K on S&M. 75% gross margin. YoY growth 85%. Burning $80K/mo."

**Output:** Full snapshot with grades, flagging Rule of 40 and burn multiple.

### Example 2: Investor Prep
**Input:** "Prepare Q1 2026 SaaS metrics for our Series A data room"
**Output:** Formatted snapshot + cohort table + segment breakdown + narrative summary with investor framing.

### Example 3: Board Deck Metrics Slide
**Input:** "Give me the 6 most important metrics for our board deck"
**Output:** MRR/ARR, NRR, Logo Churn, LTV:CAC, CAC Payback, Rule of 40 — each with prior period comparison and trend indicator.

---

## Red Flags to Surface

Always flag proactively:
- **NRR < 100%** — losing more than you're gaining from existing customers
- **Quick Ratio < 1** — churn eating new growth
- **LTV:CAC < 2** — acquiring customers unprofitably
- **CAC Payback > 24 months** — severe capital efficiency problem
- **Burn Multiple > 3** — burning too much per dollar of new ARR
- **Logo churn > 15%/yr** — product-market fit concern

---

*Precision. Integrity. Results. — PrecisionLedger*

---
name: working-capital-optimizer
version: 1.0.0
description: >
  Analyze and optimize working capital for SMBs and growth-stage companies. Calculates DSO (Days Sales
  Outstanding), DPO (Days Payable Outstanding), DIO (Days Inventory Outstanding), and the full Cash
  Conversion Cycle (CCC). Identifies AR aging risks, AP extension opportunities, and inventory carrying
  costs. Produces actionable improvement recommendations with projected cash flow impact. Use when:
  a client has cash flow strain despite profitability, AR is aging, AP terms need renegotiating, or
  a CFO wants to free up working capital without new financing.
  NOT for: tax preparation, payroll, or capital structure decisions (debt vs equity raises). NOT for
  real-time accounting integrations — use qbo-automation for live QBO data pulls. NOT for inventory
  management software configuration (use vendor-specific skill).
license: MIT
author: PrecisionLedger
tags:
  - finance
  - working-capital
  - cash-flow
  - ar
  - ap
  - dso
  - dpo
  - accounting
  - cfo
---

# Working Capital Optimizer

Diagnose and improve the cash conversion cycle for SMBs and growth-stage companies. This skill guides Sam Ledger (or any financial AI agent) through working capital analysis, identifies where cash is trapped, and produces a prioritized action plan with quantified cash release projections.

---

## When to Use This Skill

**Trigger phrases:**
- "Our cash is tight but we're profitable — why?"
- "What's our DSO / DPO / cash conversion cycle?"
- "Help us optimize working capital"
- "How do we free up cash without borrowing?"
- "AR is aging — what should we do?"
- "Can we extend our AP terms?"
- "Inventory is piling up and tying up cash"
- "Working capital analysis for [company]"

**NOT for:**
- Tax filing or compliance — use a compliance workflow
- Equity or debt capital raises — use `startup-financial-model` or `cap-table-manager`
- Real-time accounting system sync — use `qbo-automation` skill
- Payroll or GL reconciliation — use `payroll-gl-reconciliation` skill
- Retail inventory management software — use vendor-specific tooling

---

## Core Metrics

### Cash Conversion Cycle (CCC)

```
CCC = DSO + DIO - DPO

Where:
  DSO = Days Sales Outstanding     (how long to collect from customers)
  DIO = Days Inventory Outstanding (how long inventory sits before sale)
  DPO = Days Payable Outstanding   (how long before you pay suppliers)

Lower CCC = Better (cash returns faster)
Negative CCC = Excellent (paid by customers before paying suppliers — e.g., Amazon)
```

### DSO — Days Sales Outstanding

```
DSO = (Accounts Receivable / Revenue) × Days in Period

Example:
  AR Balance: $150,000
  Revenue (last 90 days): $450,000
  DSO = ($150,000 / $450,000) × 90 = 30 days

Industry benchmarks:
  < 30 days: Excellent
  30-45 days: Acceptable
  45-60 days: Needs attention
  > 60 days: Cash flow risk
```

### DPO — Days Payable Outstanding

```
DPO = (Accounts Payable / COGS) × Days in Period

Example:
  AP Balance: $80,000
  COGS (last 90 days): $270,000
  DPO = ($80,000 / $270,000) × 90 = 26.7 days

Strategy: Maximize DPO (pay as late as allowed) without damaging supplier relationships.
Target: Match or slightly exceed supplier terms (Net 30 → pay day 28-30, not day 5)
```

### DIO — Days Inventory Outstanding

```
DIO = (Inventory / COGS) × Days in Period

Example:
  Inventory: $120,000
  COGS (last 90 days): $270,000
  DIO = ($120,000 / $270,000) × 90 = 40 days

For service businesses: DIO = 0 (no physical inventory)
```

---

## Analysis Workflow

### Step 1: Gather Inputs

Collect from financial statements or QBO export:

```
Balance Sheet (current period):
  □ Accounts Receivable balance
  □ Inventory balance (if applicable)
  □ Accounts Payable balance
  □ Accrued Expenses
  □ Deferred Revenue (if any)

Income Statement (trailing 90 days preferred):
  □ Revenue
  □ Cost of Goods Sold (COGS)

AR Aging Report:
  □ Current (0-30 days)
  □ 31-60 days
  □ 61-90 days
  □ 90+ days (specify amounts)

AP Aging Report:
  □ Current
  □ Overdue amounts
  □ Key supplier names and terms
```

### Step 2: Calculate Current State

```python
def calculate_working_capital_metrics(
    ar: float,
    inventory: float,
    ap: float,
    revenue_90d: float,
    cogs_90d: float,
    period_days: int = 90
) -> dict:
    """
    Calculate core working capital metrics.
    
    Args:
        ar: Accounts Receivable balance
        inventory: Inventory balance (0 for service businesses)
        ap: Accounts Payable balance
        revenue_90d: Revenue for the measurement period
        cogs_90d: COGS for the measurement period
        period_days: Length of measurement period (default 90)
    
    Returns:
        Dict with DSO, DIO, DPO, CCC, and working capital amount
    """
    dso = (ar / revenue_90d) * period_days if revenue_90d else 0
    dio = (inventory / cogs_90d) * period_days if cogs_90d and inventory else 0
    dpo = (ap / cogs_90d) * period_days if cogs_90d else 0
    ccc = dso + dio - dpo
    
    working_capital = ar + inventory - ap
    
    return {
        "dso_days": round(dso, 1),
        "dio_days": round(dio, 1),
        "dpo_days": round(dpo, 1),
        "ccc_days": round(ccc, 1),
        "working_capital_usd": working_capital,
        "ar_usd": ar,
        "inventory_usd": inventory,
        "ap_usd": ap,
        "assessment": _assess_ccc(ccc, dso, dpo)
    }

def _assess_ccc(ccc: float, dso: float, dpo: float) -> str:
    if ccc < 0:
        return "excellent"
    elif ccc <= 30:
        return "good"
    elif ccc <= 60:
        return "acceptable"
    elif ccc <= 90:
        return "needs_improvement"
    else:
        return "critical"
```

### Step 3: AR Aging Analysis

```python
def analyze_ar_aging(aging_buckets: dict, total_ar: float) -> dict:
    """
    Analyze AR aging and identify collection risk.
    
    Args:
        aging_buckets: {"0_30": x, "31_60": x, "61_90": x, "90_plus": x}
        total_ar: Total AR balance (validation check)
    
    Returns:
        Risk assessment and recommended actions
    """
    at_risk = aging_buckets.get("61_90", 0) + aging_buckets.get("90_plus", 0)
    at_risk_pct = at_risk / total_ar if total_ar else 0
    
    recommendations = []
    
    if aging_buckets.get("31_60", 0) / total_ar > 0.3:
        recommendations.append({
            "action": "Send reminder notices to 31-60 day balances",
            "priority": "medium",
            "cash_impact": aging_buckets["31_60"],
            "timeline": "within 1 week"
        })
    
    if aging_buckets.get("61_90", 0) > 0:
        recommendations.append({
            "action": "Direct phone outreach to 61-90 day accounts",
            "priority": "high",
            "cash_impact": aging_buckets["61_90"],
            "timeline": "this week"
        })
    
    if aging_buckets.get("90_plus", 0) > 0:
        recommendations.append({
            "action": "Place 90+ day accounts on collections / payment plan",
            "priority": "critical",
            "cash_impact": aging_buckets["90_plus"],
            "timeline": "immediately"
        })
    
    return {
        "at_risk_usd": at_risk,
        "at_risk_pct": round(at_risk_pct * 100, 1),
        "collectible_estimate_usd": at_risk * 0.75,  # 75% recovery assumption
        "risk_level": "critical" if at_risk_pct > 0.20 else "moderate" if at_risk_pct > 0.10 else "low",
        "recommendations": recommendations
    }
```

### Step 4: AP Optimization Analysis

```python
def analyze_ap_optimization(
    ap_balance: float,
    avg_payment_day: float,
    standard_terms_days: int,
    monthly_cogs: float
) -> dict:
    """
    Identify AP extension opportunities.
    
    Strategy: Pay on due date, not early (unless early-pay discount justifies it)
    """
    # Days of additional cash float if paying on terms vs. current practice
    days_to_extend = max(0, standard_terms_days - avg_payment_day)
    daily_cogs = monthly_cogs / 30
    cash_freed = days_to_extend * daily_cogs
    
    # Early pay discount analysis
    # Example: "2/10 Net 30" = 2% discount if paid within 10 days
    # Annualized cost of NOT taking discount = 2% / (30-10) * 365 = 36.5% annualized
    # If cost of capital < 36.5%, TAKE the discount
    
    return {
        "current_avg_payment_day": avg_payment_day,
        "standard_terms_days": standard_terms_days,
        "days_of_extension_available": days_to_extend,
        "estimated_cash_freed_usd": round(cash_freed, 0),
        "recommendation": (
            f"Extend payment timing by {days_to_extend} days to terms. "
            f"Frees ~,.0f in working capital."
        ) if days_to_extend > 0 else "Already paying close to terms. Focus on negotiating extended terms with key suppliers."
    }
```

### Step 5: Generate Action Plan

Structure recommendations by cash impact and implementation speed:

```python
def generate_action_plan(metrics: dict, ar_analysis: dict, ap_analysis: dict) -> dict:
    """
    Produce prioritized working capital improvement plan.
    """
    quick_wins = []      # < 30 days, low friction
    medium_term = []     # 30-90 days
    strategic = []       # 90+ days, structural changes
    
    total_cash_release_estimate = 0
    
    # AR Quick Wins
    if ar_analysis["at_risk_usd"] > 0:
        quick_wins.append({
            "lever": "AR Collections",
            "action": f"Collect 61-90 day and 90+ day balances (,.0f at risk)",
            "cash_impact_usd": ar_analysis["collectible_estimate_usd"],
            "timeline_days": 30,
            "effort": "medium"
        })
        total_cash_release_estimate += ar_analysis["collectible_estimate_usd"]
    
    # AP Extension
    if ap_analysis["estimated_cash_freed_usd"] > 0:
        quick_wins.append({
            "lever": "AP Extension",
            "action": ap_analysis["recommendation"],
            "cash_impact_usd": ap_analysis["estimated_cash_freed_usd"],
            "timeline_days": 14,
            "effort": "low"
        })
        total_cash_release_estimate += ap_analysis["estimated_cash_freed_usd"]
    
    # DSO Reduction
    if metrics["dso_days"] > 45:
        dso_target = 35
        daily_revenue = metrics["ar_usd"] / metrics["dso_days"] if metrics["dso_days"] else 0
        cash_from_dso = (metrics["dso_days"] - dso_target) * daily_revenue
        medium_term.append({
            "lever": "Invoice Terms",
            "action": f"Shorten payment terms from Net {int(metrics['dso_days'])} to Net {dso_target}. Offer early-pay incentive (1% Net 10).",
            "cash_impact_usd": round(cash_from_dso, 0),
            "timeline_days": 60,
            "effort": "medium"
        })
    
    # Structural AP Terms Negotiation
    strategic.append({
        "lever": "AP Terms Renegotiation",
        "action": "Renegotiate key supplier terms from Net 30 to Net 45/60",
        "cash_impact_usd": None,  # Depends on negotiation outcome
        "timeline_days": 90,
        "effort": "high"
    })
    
    return {
        "total_estimated_cash_release_usd": total_cash_release_estimate,
        "quick_wins": quick_wins,
        "medium_term": medium_term,
        "strategic": strategic,
        "summary": f"Estimated ,.0f in working capital can be freed within 30-60 days through focused collection and AP timing improvements."
    }
```

---

## Industry Benchmarks

| Industry | DSO Target | DPO Target | CCC Target |
|---|---|---|---|
| SaaS / Software | 30-45 days | 30-45 days | 0-30 days |
| Professional Services | 30-45 days | 30-45 days | 15-45 days |
| Manufacturing | 40-55 days | 40-60 days | 40-80 days |
| Distribution / Wholesale | 35-50 days | 35-55 days | 30-60 days |
| Retail | 3-7 days | 30-45 days | -20 to 20 days |
| Construction | 45-75 days | 30-45 days | 45-90 days |
| Healthcare / Medical | 45-65 days | 30-45 days | 45-75 days |

---

## Output Format

### Executive Summary (for client delivery)

```
WORKING CAPITAL ANALYSIS — [Company] — [Date]

CURRENT STATE:
  DSO: [X] days | Target: [Y] days | Gap: [Z] days
  DIO: [X] days (N/A for services)
  DPO: [X] days | Target: [Y] days
  CCC: [X] days | Status: [Good/Acceptable/Needs Improvement/Critical]

  Working Capital: $[X]
  AR at Risk (60+ days): $[X] ([Y]% of AR)

KEY FINDINGS:
  1. [Most important issue — quantified]
  2. [Second issue — quantified]
  3. [Third issue — quantified]

PRIORITIZED ACTION PLAN:
  QUICK WINS (0-30 days, ~$[X] cash release):
    □ [Action 1] → $[impact]
    □ [Action 2] → $[impact]

  MEDIUM-TERM (30-90 days, ~$[X] additional):
    □ [Action 3]
    □ [Action 4]

  STRATEGIC (90+ days):
    □ [Action 5]

NEXT STEP: [Single most important immediate action]
```

### Structured JSON Output

```json
{
  "company": "Acme Corp",
  "analysis_date": "2026-03-15",
  "period": "Q1 2026 (Jan-Mar)",
  "metrics": {
    "dso_days": 52,
    "dio_days": 0,
    "dpo_days": 24,
    "ccc_days": 28,
    "working_capital_usd": 185000,
    "assessment": "needs_improvement"
  },
  "ar_aging": {
    "current_0_30": 90000,
    "days_31_60": 45000,
    "days_61_90": 22000,
    "days_90_plus": 18000,
    "total": 175000,
    "at_risk_pct": 22.9,
    "risk_level": "critical"
  },
  "action_plan": {
    "total_estimated_cash_release_usd": 62500,
    "quick_wins": [
      {
        "lever": "AR Collections",
        "action": "Collect 61-90 and 90+ day balances ($40,000 at risk)",
        "cash_impact_usd": 30000,
        "timeline_days": 30
      },
      {
        "lever": "AP Extension",
        "action": "Extend payment timing by 6 days to terms. Frees ~$12,500.",
        "cash_impact_usd": 12500,
        "timeline_days": 14
      }
    ]
  }
}
```

---

## Example Usage

**User:** "Our QBO shows $175k AR, $70k AP, $2M in revenue last year. We're profitable but always feel cash-strapped. What's going on?"

**Sam's response:**

```
Working Capital Snapshot:

  Revenue run rate: $2M/yr → ~$500k/quarter
  AR: $175,000
  AP: $70,000

  DSO = ($175,000 / $500,000) × 90 = 31.5 days ← borderline, worth watching
  DPO = ($70,000 / estimated COGS) × 90 = need COGS to confirm
  CCC = approx 25-35 days

Potential issues despite good DSO:
  1. Revenue recognition vs cash: if billing is end-of-month heavy, AR spikes temporarily
  2. AP payment behavior: if you're paying invoices early (day 5 of Net 30 terms), you're 
     giving suppliers a 25-day float for free — roughly $58k in "gifted" working capital
  3. High AR concentration: if 20% of customers account for 80% of AR, one slow payer 
     tanks your cash position

Next step: Pull AR aging report from QBO. If 30+ day buckets are > 15% of AR, 
that's your primary fix. Also check when you're actually paying AP.
```

---

## Integration Points

- **`qbo-automation`** — Pull AR aging, AP aging, and balance sheet data directly from QuickBooks Online
- **`ar-collections-agent`** — Execute collection workflows on identified aging accounts
- **`thirteen-week-cash-flow`** — Feed working capital metrics into 13-week rolling cash forecast
- **`kpi-alert-system`** — Set DSO/DPO threshold alerts for ongoing monitoring
- **`vendor-payment-optimizer`** — Detailed AP vendor-by-vendor payment term analysis
- **`financial-analysis-agent`** — Full financial health assessment including working capital context
- **`budget-vs-actual`** — Compare working capital targets vs actuals over time

---

## Quick Reference Formulas

```
DSO = (AR / Revenue) × Period Days
DPO = (AP / COGS) × Period Days
DIO = (Inventory / COGS) × Period Days
CCC = DSO + DIO - DPO

Working Capital = Current Assets - Current Liabilities
Net Working Capital = AR + Inventory - AP (operating focus)

Current Ratio = Current Assets / Current Liabilities  (target ≥ 1.5)
Quick Ratio   = (Cash + AR) / Current Liabilities     (target ≥ 1.0)

Annual Cash Impact of 1-Day DSO Reduction = Annual Revenue / 365
Annual Cash Impact of 1-Day DPO Extension = Annual COGS / 365
```

---
name: vendor-payment-optimizer
description: >
  Optimize vendor payment timing to maximize cash flow and capture early payment discounts.
  Analyzes open AP, scores discount opportunities (2/10 net 30, etc.), calculates annualized ROI of
  early payment vs. holding cash, generates payment priority queue, and flags late payment risk.
  Use when reviewing AP aging, deciding which invoices to pay early, or building a cash-efficient
  payment strategy. NOT for: paying invoices directly (use QBO or banking integrations), vendor
  contract negotiation (use contract-review-agent), or payroll processing (use payroll-gl-reconciliation).
version: 1.0.0
author: PrecisionLedger
tags:
  - accounts-payable
  - cash-flow
  - vendor-management
  - finance
  - optimization
---

# Vendor Payment Optimizer

Turns your AP aging report into a cash flow weapon. This skill analyzes open vendor invoices, calculates the true annualized ROI of early payment discounts, scores each invoice by priority, and generates an optimized payment queue — so you capture discounts worth capturing and hold cash when you shouldn't pay early.

---

## When to Use

**Trigger phrases:**
- "Which invoices should we pay early this week?"
- "We have a 2/10 net 30 — is it worth taking the discount?"
- "Optimize our AP payment schedule"
- "What's our cash position if we pay all invoices due this month?"
- "Flag any invoices about to go late"
- "Build a payment priority list from our AP aging"

**NOT for:**
- Actually sending payments or syncing to bank — use QBO or banking tools
- Vendor contract renegotiation — use `contract-review-agent`
- Payroll — use `payroll-gl-reconciliation`
- AR (receivables) — use `ar-collections-agent`
- Real-time QBO AP sync — use `qbo-automation` to pull data first, then feed here

---

## Core Concepts

### Early Payment Discount Math

The most common discount term is **2/10 net 30**: pay within 10 days, get 2% off; otherwise pay in 30 days.

**Annualized ROI of taking the discount:**

```
Annualized ROI = (Discount % / (1 - Discount %)) × (365 / (Net Days - Discount Days))

Example: 2/10 net 30
= (0.02 / 0.98) × (365 / 20)
= 0.0204 × 18.25
= 37.2% annualized ROI
```

**Decision rule:**
- If annualized ROI > your cost of capital (or opportunity cost of cash) → **take the discount**
- If annualized ROI < cost of capital → **hold cash, pay at net due date**
- Typical cost of capital for small business: 8–15%; most 2/10 net 30 discounts are worth taking

### Payment Priority Scoring

Score each invoice (0–100) using:

```
Score = (Discount ROI Weight × 40)
      + (Days-to-Late-Fee Weight × 30)
      + (Vendor Relationship Weight × 20)
      + (Invoice Size Weight × 10)
```

| Factor | High Score | Low Score |
|---|---|---|
| Discount ROI | >30% annualized | No discount available |
| Days to late | ≤3 days | >15 days |
| Vendor criticality | Key supplier, sole source | Easily replaceable |
| Invoice size | >$10k (high absolute $ saved) | <$500 |

---

## Workflow

### Step 1: AP Aging Input

Accept AP data in any of these formats:
- Pasted table from QBO AP Aging Summary report
- CSV export from accounting software
- Manual entry: vendor, invoice #, amount, due date, discount terms

**Minimum fields needed:**
```
Vendor Name | Invoice # | Amount | Invoice Date | Due Date | Terms | Discount Terms
```

**Example input:**
```
Vendor          | Inv #   | Amount    | Due Date   | Terms     | Discount
Acme Supplies   | INV-101 | $5,200    | 2026-03-22 | Net 30    | 2/10
Tech Services   | INV-205 | $12,000   | 2026-03-25 | Net 45    | None
Office Depot    | INV-089 | $840      | 2026-03-19 | Net 30    | 1/10
Contractor LLC  | INV-312 | $18,500   | 2026-04-01 | Net 60    | None
Cloud Hosting   | INV-410 | $3,200    | 2026-03-20 | Net 30    | 2/10
```

---

### Step 2: Discount Opportunity Analysis

For each invoice with discount terms, calculate:

```
DISCOUNT ANALYSIS — [Vendor] [Invoice #]

Invoice Amount:       $X,XXX
Discount Terms:       2/10 net 30
Discount Amount:      $XX.XX (2% of invoice)
Payment if early:     $X,XXX - $XX = $X,XXX
Days to discount exp: X days
Days to net due:      X days

Annualized ROI:       XX.X%
Recommendation:       TAKE / SKIP
Reason:               [ROI vs. cost of capital analysis]
Cash impact:          Pay $X,XXX today vs. $X,XXX in X days
```

---

### Step 3: Payment Priority Queue

Output a ranked payment list:

```
PAYMENT PRIORITY QUEUE — [Date]
Cash Available for AP This Week: $[amount] (enter or estimate)

RANK | VENDOR          | INV #   | AMOUNT   | DUE DATE   | DISCOUNT | SCORE | ACTION
-----|-----------------|---------|----------|------------|----------|-------|-------
  1  | Cloud Hosting   | INV-410 | $3,136*  | 2026-03-20 | 2/10 ✓  |  94   | PAY TODAY — discount exp. tomorrow
  2  | Office Depot    | INV-089 | $831.60* | 2026-03-19 | 1/10 ✓  |  91   | PAY TODAY — overdue risk
  3  | Acme Supplies   | INV-101 | $5,096*  | 2026-03-22 | 2/10 ✓  |  87   | PAY BY 3/22 for discount
  4  | Tech Services   | INV-205 | $12,000  | 2026-03-25 | None    |  62   | PAY BY DUE DATE
  5  | Contractor LLC  | INV-312 | $18,500  | 2026-04-01 | None    |  41   | HOLD — due in 15 days

* = discount-adjusted amount
```

---

### Step 4: Cash Flow Impact Summary

```
AP CASH FLOW SUMMARY

Total Open AP:              $39,740
Invoices due this week:     $21,236
Discount savings available: $186.40 (if all discounts taken)

Scenario A — Pay all discounts now + hold rest:
  Cash out this week:       $9,063.60
  Cash out by month-end:    $30,503.60
  Discount savings:         $186.40

Scenario B — Pay only what's overdue:
  Cash out this week:       $3,200
  Late fee risk:            Office Depot INV-089 (already at due date)

Scenario C — Pay everything now:
  Cash out today:           $39,553.60 (with all discounts)
  Cash freed from AP:       $39,740 obligation cleared

RECOMMENDATION: Scenario A — capture $186 in discounts, preserve $30k for week 2+
```

---

### Step 5: Late Payment Risk Flags

```
⚠️ LATE PAYMENT ALERTS

CRITICAL (0-2 days to due):
  - Office Depot INV-089: DUE TODAY — $840. Pay now or incur late fee.
  - Cloud Hosting INV-410: Due tomorrow — discount expires in 1 day.

WARNING (3-7 days to due):
  - Acme Supplies INV-101: Due in 4 days. Discount expires in 4 days.

WATCH (8-14 days):
  - Tech Services INV-205: Due in 8 days. No discount. Monitor cash.

NO URGENCY (15+ days):
  - Contractor LLC INV-312: Due in 15 days. Hold.
```

---

## Advanced Features

### Stretching Payment Terms (Ethically)

When cash is tight, identify which vendors are safe to pay at the tail end of net terms:

```
PAYMENT STRETCH ANALYSIS

Vendor: [Name]
Current terms: Net 30
Grace period (if any): 5 days (industry standard)
Relationship risk: LOW (long-term vendor, no prior issues)
Maximum safe payment date: Day 35 without relationship damage
Savings from stretching: [Interest on held cash for 5 days]

Vendor: [Name]
Current terms: Net 30
Relationship risk: HIGH (new vendor, critical sole-source supplier)
Recommendation: DO NOT STRETCH — pay on time
```

### Dynamic Discounting Offers

When you have excess cash and want to negotiate discounts with vendors who don't offer them:

```
DYNAMIC DISCOUNT PROPOSAL — [Vendor]

Current terms: Net 30, no discount
Proposed: Pay in 5 days for 1.5% discount
Your annualized cost: 12.2% (worth it at our hurdle rate)
Vendor benefit: Early cash, improved their cash flow
Script: "We'd love to settle INV-[X] today if you can extend a 1.5% early-pay discount."
```

### Vendor Spend Concentration Report

Identify AP risk concentration:

```
VENDOR CONCENTRATION — Last 90 Days

Top vendors by spend:
  1. [Vendor A]: $45,200 (38% of AP)  ← concentration risk
  2. [Vendor B]: $22,100 (18%)
  3. [Vendor C]: $15,800 (13%)
  4. All others: $37,000 (31%)

Risk flag: Single vendor >30% of AP = supply chain dependency
Recommendation: Qualify backup vendor for [Vendor A]'s category
```

---

## Output Formats

### Weekly AP Decision Brief (default)
Short, actionable: what to pay, by when, and why. Fits in one screen.

### Full AP Analysis Report
Comprehensive with all scenarios, discount math, vendor concentrations. For CFO/board review.

### CSV Payment Queue
For importing into QBO or banking batch payment tools:
```csv
Vendor,Invoice,AmountToPay,PayBy,DiscountTaken,Notes
Cloud Hosting,INV-410,3136.00,2026-03-19,Yes (2%),Discount expires tomorrow
Office Depot,INV-089,831.60,2026-03-19,Yes (1%),Due today
...
```

---

## Usage Examples

### Example 1: Quick Weekly Scan

**Input:** "Here's my AP aging. What should I pay this week? Cash available: $15k."

**Agent output:**
1. Parses AP data
2. Flags overdue and discount-expiring invoices
3. Calculates discount ROIs
4. Returns priority queue capped at $15k cash
5. Shows total discount savings captured

### Example 2: Discount Decision

**Input:** "We got an invoice for $8,500 with 2/10 net 30. We have cash. Should we take it?"

**Agent output:**
```
2/10 net 30 Discount Analysis — $8,500 invoice

Discount amount:    $170.00
Pay now:            $8,330
Hold 20 more days:  $8,500

Annualized ROI of taking discount: 37.2%
Your implied cost of capital:      ~10-12%

VERDICT: TAKE THE DISCOUNT ✓
You're earning 37.2% annualized by paying $8,330 today vs $8,500 in 20 days.
That beats any savings account or short-term investment.
```

### Example 3: Cash Constraint Scenario

**Input:** "I only have $5k available. I have $20k in AP due this week. What's the triage?"

**Agent output:**
1. Ranks invoices by criticality (late fee risk + vendor relationship)
2. Identifies which $5k subset to pay to avoid the most damage
3. Flags which vendors to contact proactively about delayed payment
4. Suggests script for requesting payment extension from lowest-risk vendors

---

## Integration Points

- **`qbo-automation`** — Pull live AP aging data from QuickBooks to feed this skill
- **`thirteen-week-cash-flow`** — Feed payment queue output into 13-week cash model
- **`ar-collections-agent`** — Pair AP optimization with AR acceleration for complete cash cycle management
- **`kpi-alert-system`** — Trigger alerts when AP aging shows invoices approaching due dates
- **`financial-analysis-agent`** — Use vendor spend data for cost analysis and budget variance

---

## Key Formulas Reference

```
Annualized Discount ROI = (d / (1-d)) × (365 / (net_days - discount_days))
  where d = discount rate (e.g., 0.02 for 2%)

Days Payable Outstanding (DPO) = (AP Balance / COGS) × Days in Period
  Healthy range: 30-60 days (industry-dependent)

Cash Conversion Cycle = DSO + DIO - DPO
  (Days Sales Outstanding + Days Inventory Outstanding - Days Payable Outstanding)
  Lower = more efficient cash cycle

Payment Stretch Savings = Invoice × (1 + risk_free_rate)^(stretch_days/365) - Invoice
  (Small but meaningful at scale)
```

---

## Privacy & Compliance Notes

- AP data contains sensitive vendor relationships and pricing — never share externally
- Payment timing decisions should be reviewed by Irfan for amounts >$10k
- Document all payment decisions with rationale for audit trail
- Never pay invoices directly from this skill — outputs are decision support only

---
name: qbo-to-tax-bridge
description: >
  Map QuickBooks Online (QBO) chart of accounts and transaction categories to IRS tax schedules
  (Schedule C, E, F, 1120S, 1120, 1065, and more). Generate trial balance exports, produce
  tax-ready workpapers, track crypto asset cost basis within QBO, and bridge bookkeeping data
  to tax preparation workflows. PTIN-backed advisory context included. Use when: (1) preparing
  year-end workpapers from QBO data, (2) mapping QBO categories to IRS schedule line items,
  (3) identifying reclassification needs before tax filing, (4) generating crypto cost basis
  reports from QBO transactions. NOT for: filing tax returns directly, providing legal tax
  advice, replacing a licensed CPA or EA review, or accessing QBO without proper credentials.
metadata:
  openclaw:
    tags:
      - accounting
      - tax
      - quickbooks
      - irs
      - compliance
      - crypto
      - workpapers
---

# QBO → Tax Bridge

Maps QuickBooks Online bookkeeping data to IRS tax schedules and generates tax-ready workpapers.

---

## Core Capabilities

1. **QBO Category → IRS Schedule Mapping** — Translate chart of accounts to Schedule C, E, F, 1120S, 1120, 1065 line items
2. **Trial Balance → Workpaper Generation** — Export QBO trial balance and format into tax-ready supporting schedules
3. **Reclassification Identification** — Flag misclassified transactions that need adjustment before filing
4. **Crypto Cost Basis Tracking** — Calculate FIFO/LIFO/HIFO cost basis for crypto assets recorded in QBO
5. **Schedule Line Mapping Reference** — Authoritative line-by-line crosswalk tables

---

## Workflow

### Step 1 — Export QBO Trial Balance

1. In QBO: **Reports → Trial Balance**
2. Set date range to the full tax year (Jan 1 – Dec 31)
3. Customize → Show Non-Zero rows only
4. Export as **Excel or CSV**
5. Also export: **Profit & Loss (by month)** and **Balance Sheet (year-end)**

```
QBO Export Path:
Reports > Standard > Trial Balance > Export (Excel icon, top right)
```

---

### Step 2 — Entity + Schedule Selection

Identify the filing entity type to determine the correct IRS schedule:

| Entity Type | Primary Schedule | Supporting |
|---|---|---|
| Sole Proprietor / Single-Member LLC | Schedule C (Form 1040) | SE, 4562 |
| Rental Activity | Schedule E (Page 1) | 4562, 8582 |
| Farm | Schedule F | 4562, SE |
| S-Corporation | Form 1120-S + K-1 | 4562, 4797 |
| C-Corporation | Form 1120 | 4562, 4797 |
| Partnership / Multi-Member LLC | Form 1065 + K-1 | 4562, 4797 |

---

### Step 3 — Schedule C Mapping (Sole Proprietors)

**Income Accounts → Schedule C, Part I**

| QBO Account Name (Common) | Schedule C Line | Notes |
|---|---|---|
| Sales / Revenue | Line 1 (Gross receipts) | Exclude returns |
| Returns and Allowances | Line 2 | Deducted from gross |
| Cost of Goods Sold | Line 4 (via Part III) | COGS detail required |
| Other Income | Line 6 | Non-operating income |

**Expense Accounts → Schedule C, Part II**

| QBO Account Name | Sch C Line | IRS Description |
|---|---|---|
| Advertising | Line 8 | Advertising |
| Bank Charges | Line 27a (Other) | Bank service charges |
| Car & Truck Expenses | Line 9 | Requires Form 4562 or actual method |
| Commissions | Line 10 | Commissions and fees |
| Contract Labor | Line 11 | Non-employee compensation (1099-NEC trigger) |
| Depreciation | Line 13 | Form 4562 required |
| Dues & Subscriptions | Line 27a | Other expenses |
| Employee Benefits | Line 14 | Health insurance, retirement |
| Insurance (Business) | Line 15 | Not health insurance here |
| Interest - Mortgage | Line 16a | On business real property |
| Interest - Other | Line 16b | Business loans |
| Legal & Professional | Line 17 | Attorney, CPA, consultants |
| Meals (Business) | Line 24b | 50% deductible — apply haircut |
| Office Expense | Line 18 | Supplies vs. equipment distinction |
| Rent (Equipment) | Line 20a | Equipment rentals |
| Rent (Property) | Line 20b | Office/workspace rent |
| Repairs & Maintenance | Line 21 | Maintenance only; improvements → capitalize |
| Payroll Taxes | Line 23 | Employer portion FICA/FUTA |
| Wages (Employees) | Line 26 | W-2 wages (not owner draws) |
| Utilities | Line 25 | Business portion only |
| Home Office | Line 30 | Form 8829 required |
| Travel | Line 24a | Business travel (not commuting) |
| Telephone | Line 27a | Business portion of phone/internet |
| Software Subscriptions | Line 27a | Business software |

**⚠ Meals Haircut:** Apply 50% reduction to QBO Meals balance before reporting on Sch C Line 24b.

---

### Step 4 — Schedule E Mapping (Rental)

**Per-Property Tracking Required**

| QBO Account | Schedule E Line | Notes |
|---|---|---|
| Rental Income | Line 3 | Gross rents received |
| Advertising (Rental) | Line 5 | Vacancy advertising |
| Auto & Travel (Rental) | Line 6 | Property management travel |
| Cleaning & Maintenance | Line 7 | Cleaning between tenants |
| Commissions | Line 8 | Leasing agent fees |
| Insurance (Rental) | Line 9 | Property insurance |
| Legal & Professional (Rental) | Line 10 | Eviction, lease drafting |
| Management Fees | Line 11 | Property management company |
| Mortgage Interest (Rental) | Line 12 | From 1098 |
| Other Interest (Rental) | Line 13 | HELOC on rental, etc. |
| Repairs (Rental) | Line 14 | Repairs only; improvements → capitalize |
| Supplies (Rental) | Line 15 | Small supplies |
| Property Taxes | Line 16 | Real estate taxes |
| Utilities (Rental) | Line 17 | Landlord-paid utilities |
| Depreciation (Rental) | Line 18 | Form 4562; 27.5-yr residential |
| Other Expenses | Line 19 | HOA, snow removal, etc. |

---

### Step 5 — Form 1120-S / S-Corp Mapping

**Income → Form 1120-S, Page 1**

| QBO Account | 1120-S Line | Notes |
|---|---|---|
| Gross Receipts / Sales | Line 1a | Net of returns |
| Cost of Goods Sold | Line 2 | Schedule A detail |
| Other Income | Line 5 | Interest, rents, royalties |

**Deductions → Form 1120-S, Lines 7-19**

| QBO Account | 1120-S Line |
|---|---|
| Compensation of Officers | Line 7 |
| Salaries & Wages | Line 8 |
| Repairs & Maintenance | Line 9 |
| Bad Debts | Line 10 |
| Rents | Line 11 |
| Taxes & Licenses | Line 12 |
| Interest | Line 13 |
| Depreciation | Line 14 |
| Depletion | Line 15 |
| Advertising | Line 16 |
| Employee Benefit Programs | Line 17 |
| Other Deductions | Line 19 |

**K-1 Pass-Through Items (Schedule K)**

| QBO Account | K-1 Box | Description |
|---|---|---|
| Ordinary Business Income | Box 1 | Net income from operations |
| Interest Income | Box 4 | Taxable interest |
| Dividends | Box 5a/5b | Ordinary vs. qualified |
| Net Rental Income | Box 2 | Rental real estate |
| Section 179 | Box 11 | Must track separately |
| Charitable Contributions | Box 12a | Separately stated |
| Meals (50%) | Box 16C | Must track and separate |

---

### Step 6 — Crypto Asset Cost Basis (QBO-Tracked)

**QBO Setup for Crypto:**
- Create a separate **Other Current Asset** account per crypto type (e.g., "Bitcoin Holdings", "Ethereum Holdings")
- Record purchases as asset debits; sales as credits with gain/loss to **Other Income** or **Other Expense**

**Cost Basis Methods:**

| Method | Description | IRS Compliance |
|---|---|---|
| FIFO | First purchased = first sold | Default, IRS-accepted |
| LIFO | Last purchased = first sold | Accepted but must be consistent |
| HIFO | Highest cost = first sold | Accepted; minimizes gains |
| Specific ID | Choose exact lot | Requires documentation |

**Crypto Workpaper Template (per asset):**

```
Asset: Bitcoin (BTC)
QBO Account: Bitcoin Holdings

Date       | Type     | Units    | Price    | Cost Basis | Proceeds | Gain/Loss
-----------|----------|----------|----------|------------|----------|----------
2025-02-14 | Purchase | 0.50000  | $48,000  | $24,000    | —        | —
2025-06-01 | Purchase | 0.25000  | $62,000  | $15,500    | —        | —
2025-09-15 | Sale     | 0.25000  | $71,000  | $12,000*   | $17,750  | $5,750 ST
2025-11-30 | Sale     | 0.25000  | $95,000  | $12,000*   | $23,750  | $11,750 LT

*FIFO basis from 2025-02-14 purchase

Short-term gains: $5,750 → Schedule D, Part I / Form 8949 (held < 1 year)
Long-term gains: $11,750 → Schedule D, Part II / Form 8949 (held > 1 year)
```

**QBO Journal Entry for Crypto Sale:**
```
DR  Checking / USD Received         $23,750
CR  Bitcoin Holdings (cost)         $12,000
CR  Long-Term Capital Gain          $11,750
```

**DeFi / Staking Income:**
- Record staking rewards as **Other Income** at FMV on date received
- Taxed as ordinary income (not capital gains) at receipt
- Future sale creates capital gain/loss from FMV basis

---

### Step 7 — Reclassification Identification

Run these checks against QBO trial balance before workpaper finalization:

**Common Misclassifications:**

| Issue | Check | Fix |
|---|---|---|
| Capital improvements coded as repairs | Single transaction > $2,500 in Repairs & Maint. | Reclassify to Fixed Assets; depreciate |
| Owner draws in payroll | Distributions in wage accounts | Move to Owner's Draw / Equity |
| Personal expenses in business | Mixed personal charges | Reclassify to Due from Owner / Shareholder |
| Meals coded as Entertainment | Pre-2018 habit; Entertainment 0% deductible | Separate Meals (50%) from Entertainment (0%) |
| Loan proceeds in Income | Deposit from business loan coded as revenue | Reclassify to Liability account |
| Prepaid expenses expensed | Subscriptions paid for future periods | Pro-rate; book prepaid asset |
| Security deposits as income | Refundable deposits in revenue | Reclassify to Liability |

**Threshold Rules:**
- **Section 179 / Bonus Depreciation:** Fixed assets ≥ $2,500 (safe harbor) → track in 4562
- **1099-NEC Trigger:** Any contractor receiving ≥ $600/year → verify W-9 on file
- **Meals Documentation:** Receipt + business purpose required for deductibility

---

### Step 8 — Trial Balance → Workpaper Output Format

**Workpaper Header (required per SSVS standards):**
```
Client:          [Business Name]
EIN:             XX-XXXXXXX
Tax Year:        2025
Prepared By:     [Preparer Name], PTIN: PXXXXXXXXX
Reviewed By:     [Reviewer Name]
Date Prepared:   [Date]
Purpose:         Supporting schedule for [Form/Schedule]
```

**Standard Workpaper Tabs (Excel):**
1. `TB` — Trial Balance (direct QBO export, unadjusted)
2. `AJE` — Adjusting Journal Entries (reclassifications, accruals)
3. `ATB` — Adjusted Trial Balance (TB + AJE)
4. `Sch-C` or `1120-S` — Schedule mapping with line references
5. `Fixed-Assets` — 4562 depreciation schedule
6. `Crypto` — Cost basis tracker (if applicable)
7. `1099` — Contractor payment summary for 1099-NEC filing

---

## Quick Reference: Common QBO → IRS Line Crosswalk

```
QBO Account              → IRS Form/Line
─────────────────────────────────────────
Advertising              → Sch C L8 / 1120-S L16
Bank Charges             → Sch C L27a (other)
Car & Truck              → Sch C L9 (+ 4562 or actual)
Commissions              → Sch C L10
Contract Labor           → Sch C L11 (1099-NEC!)
Depreciation             → Sch C L13 / 1120-S L14
Dues & Subscriptions     → Sch C L27a
Health Insurance (self)  → 1040 L17 (not Sch C)
Insurance (business)     → Sch C L15
Interest (mortgage)      → Sch C L16a
Interest (other)         → Sch C L16b
Legal & Professional     → Sch C L17
Meals (50%)              → Sch C L24b (50% of QBO balance)
Office Expense           → Sch C L18
Payroll Taxes (ER)       → Sch C L23
Rent (equipment)         → Sch C L20a
Rent (space)             → Sch C L20b
Repairs                  → Sch C L21
Travel                   → Sch C L24a
Utilities                → Sch C L25
Wages (W-2)              → Sch C L26
Home Office              → Sch C L30 (+ 8829)
```

---

## Negative Boundaries (When NOT to Use)

- ❌ **Filing tax returns** — This skill maps and prepares workpapers; actual filing requires licensed tax software (Drake, UltraTax, ProConnect) and a licensed preparer
- ❌ **Legal tax advice** — Specific tax strategies, elections, or planning require CPA/EA/attorney opinion
- ❌ **Replacing CPA review** — Workpapers always require professional review before filing
- ❌ **Non-QBO accounting systems** — Mappings assume QBO chart of accounts; adapt for Xero, Wave, or FreshBooks
- ❌ **International tax (non-US)** — IRS schedules only; no VAT, GST, or foreign jurisdiction guidance
- ❌ **State tax returns** — State conformity varies; this skill covers federal only
- ❌ **Real-time QBO API access** — This is a workpaper and mapping skill, not a live QBO integration (see qbo-automation skill for API work)

---

## Examples

### Example 1 — Sole Prop Workpaper Summary
```
Client: Jane Smith Photography, LLC (SMLLC)
EIN: 87-1234567 | Tax Year: 2025 | Schedule C

QBO Trial Balance (Adjusted):
  Revenue                  $142,500
  COGS (equipment rental)  ($18,200)
  Gross Profit             $124,300

Schedule C Expenses:
  L8  Advertising          $3,400
  L9  Car & Truck          $4,200   (actual method, Form 4562 attached)
  L13 Depreciation         $2,800   (camera equipment, Form 4562)
  L15 Insurance            $1,200
  L17 Legal & Prof         $2,500
  L18 Office               $890
  L24b Meals               $1,450   (QBO balance $2,900 × 50%)
  L25 Utilities            $600
  L27a Other               $1,100   (software subscriptions)
  Total Expenses           $18,140

Net Profit (Line 31):      $106,160
SE Tax (Sch SE):           $14,993
```

### Example 2 — Crypto Reclassification
```
Issue found: Client coded $15,000 ETH sale proceeds as "Sales Revenue"
Fix: Reclassify to Long-Term Capital Gain (held 14 months)
AJE: DR Sales Revenue $15,000 / CR LT Capital Gain $15,000
     DR LT Capital Gain $8,200 / CR Cost Basis (ETH Holdings) $8,200
Net: $6,800 LTCG → Form 8949, Part II → Schedule D, Line 8
```

### Example 3 — S-Corp K-1 Prep
```
Form 1120-S → Schedule K Items to Separately State:
  Box 1: Ordinary Income        $87,400
  Box 4: Interest Income        $320
  Box 12a: Charitable Contrib   $2,500
  Box 16C: Meals Disallowed     $1,800 (50% of $3,600 meals)
  
Per-shareholder K-1 (50% ownership):
  Box 1: $43,700
  Box 4: $160
  Box 12a: $1,250
  Box 16C: $900
```

---

## Related Skills

- **qbo-automation** — QBO API integration, bank rules, chart of accounts setup
- **crypto-tax-agent** — Advanced crypto tax calculations, DeFi, NFTs, multi-chain
- **financial-analysis-agent** — Ratio analysis, variance reporting, management reporting

---
name: term-sheet-analyzer
description: >
  Analyze startup financing term sheets: SAFEs, convertible notes, and priced equity rounds.
  Decode economic terms, flag founder-unfriendly clauses, model dilution impact, calculate
  effective pre/post-money valuations, and compare multiple competing term sheets side-by-side.
  Outputs plain-language risk summary plus structured dilution scenarios. Use when a founder,
  CFO, or investor receives a term sheet and needs to understand its real economic and control
  implications before signing.
  NOT for: legal advice or drafting legal documents (refer to counsel), cap table management
  (use cap-table-manager skill), token sale structures (use erc20-tokenomics-builder), or
  post-close equity administration.
version: 1.0.0
author: PrecisionLedger
tags:
  - finance
  - startups
  - term-sheets
  - fundraising
  - SAFEs
  - convertible-notes
  - venture
---

# Term Sheet Analyzer Skill

Decode the real economics and control implications of any startup financing term sheet. This skill guides Sam Ledger through parsing SAFEs, convertible notes, and priced rounds — translating legal language into numbers, identifying red flags, and producing a founder-ready risk summary.

---

## When to Use This Skill

**Trigger phrases:**
- "Analyze this term sheet"
- "What does this SAFE actually mean?"
- "Is this a good deal?"
- "How much dilution am I taking?"
- "Compare these two term sheets"
- "What's the post-money valuation cap?"
- "Explain these liquidation preferences"
- "Flag any founder-unfriendly terms"
- "Investor sent a convertible note, what should I know?"

**NOT for:**
- Drafting or redlining legal documents → engage startup counsel
- Post-close cap table management → use `cap-table-manager`
- Token sale / SAFT structures → use `erc20-tokenomics-builder`
- Public company securities analysis → different regulatory framework
- Personal investment advice → not a licensed advisor

---

## Instrument Types

### 1. SAFE (Simple Agreement for Future Equity)

YC's standard instrument. Four variants:

| SAFE Type | Valuation Cap | Discount | Notes |
|---|---|---|---|
| Cap Only | ✅ Yes | ❌ No | Most common at pre-seed |
| Discount Only | ❌ No | ✅ Yes (typically 15-20%) | Less common |
| Cap + Discount | ✅ Yes | ✅ Yes | Investor gets better of the two |
| MFN (Most Favored Nation) | ❌ No | ❌ No | Converts at best future SAFE terms |

**Key SAFE economics:**

```
Conversion Price (Cap-based):
  = Valuation Cap / (Fully Diluted Shares at Qualified Financing)

Conversion Price (Discount-based):
  = Priced Round Price × (1 - Discount Rate)

Investor uses whichever price is LOWER (better for investor)

Example:
  SAFE amount: $100,000
  Valuation cap: $5,000,000
  Priced round: Series A at $10M pre-money, $2.00/share
  
  Cap price: $5M / 5,000,000 shares = $1.00/share
  Discount price (20%): $2.00 × 0.80 = $1.60/share
  
  Investor converts at $1.00/share (cap wins)
  Shares received: $100,000 / $1.00 = 100,000 shares
```

**Post-Money SAFE (YC standard post-2018):**
- Cap is post-money (includes SAFE investment itself)
- Ownership % is locked: Investment ÷ Cap = ownership
- Example: $200k on $5M post-money cap = 4.0% ownership (guaranteed)

**Pre-Money SAFE (older, less common):**
- Cap is pre-money; actual dilution depends on total SAFE stack and option pool
- Riskier for founders: SAFE stack + option pool refresh can cause severe dilution

---

### 2. Convertible Note

Debt instrument that converts to equity. Key terms to extract:

```
Principal Amount:     $[X]
Interest Rate:        [Y]% per annum (simple or compound)
Maturity Date:        [Z months] from issuance
Conversion Discount:  [D]% off priced round price
Valuation Cap:        $[C] (pre-money or post-money — MUST clarify)
Conversion Trigger:   Qualified Financing ≥ $[threshold]
Repayment:            At maturity if no conversion (cash or equity)
```

**Interest accrual impact on dilution:**

```
Example:
  Principal: $500,000
  Rate: 6% simple
  Issued: Jan 2025, Converts: Jan 2026 (12 months)
  
  Accrued interest: $500,000 × 6% = $30,000
  Principal + Interest at conversion: $530,000
  
  Converting at $1.00/share → 530,000 shares (not 500,000)
  Extra dilution from interest: 30,000 shares
```

**Note vs SAFE comparison:**

| Factor | SAFE | Convertible Note |
|---|---|---|
| Debt on balance sheet | ❌ No | ✅ Yes |
| Interest accrual | ❌ No | ✅ Yes |
| Maturity / repayment risk | ❌ No | ✅ Yes |
| Complexity | Lower | Higher |
| Investor preference | Simpler rounds | Bridge rounds |

---

### 3. Priced Equity Round

Most complex. Key terms to extract and analyze:

```
Pre-Money Valuation:     $[X]
Investment Amount:       $[Y]
Post-Money Valuation:    $[X + Y]
Price Per Share:         $[P] = Pre-Money / Pre-Round Fully Diluted Shares
Share Class:             Series [A/B/C] Preferred
Liquidation Preference:  [1x / 2x] [participating / non-participating]
Anti-Dilution:           [broad-based weighted average / narrow-based / full ratchet]
Pro-Rata Rights:         [Yes / No] — right to invest in future rounds
Board Seats:             [Lead investor gets X seats]
Option Pool:             [%] — pre-money or post-money?
Drag-Along:              [threshold %]
Information Rights:      [quarterly/annual financials]
```

---

## Red Flag Checklist

Run every term sheet through this checklist:

### 🔴 High Risk — Negotiate Hard
- [ ] **Full ratchet anti-dilution** — investor gets repriced to lowest future price; massively penalizes founders in a down round
- [ ] **Participating preferred with no cap** — investor gets liquidation preference PLUS pro-rata equity proceeds (double-dipping forever)
- [ ] **2x+ liquidation preference** — investor gets 2× their money before anyone else
- [ ] **Option pool pre-money and large (>20%)** — effective valuation is lower than stated; dilutes founders only
- [ ] **Pay-to-play without notice** — converts investors to common if they don't follow-on; can be weaponized
- [ ] **Exclusivity >30 days** — locks founder out of talking to other investors too long
- [ ] **No-shop clause with damages** — creates liability if founder walks away

### 🟡 Medium Risk — Understand the Implications
- [ ] **Broad-based weighted average anti-dilution** — standard but still dilutes founders in down rounds
- [ ] **Non-participating preferred** — investor converts to common at IPO/acquisition; usually acceptable
- [ ] **1x liquidation preference non-participating** — standard, fine
- [ ] **Pro-rata rights (major investor)** — investor can maintain ownership %; usually reasonable
- [ ] **Information rights (quarterly)** — standard; just operationally overhead
- [ ] **ROFR on secondary sales** — company/investors get first right to buy shares; limits founder liquidity
- [ ] **Drag-along at low threshold (<50%)** — can force sale without founder approval

### 🟢 Standard / Founder-Friendly
- [ ] 1x liquidation preference non-participating
- [ ] Broad-based weighted average anti-dilution
- [ ] Post-money SAFE with reasonable cap
- [ ] Board composition: 2 founders, 1 investor, 2 independents
- [ ] Option pool created post-money
- [ ] Pro-rata rights for major investors only (≥ $X invested)
- [ ] 12-month exclusivity max

---

## Dilution Modeling

### SAFE Dilution Scenario

```
Inputs:
  Pre-round shares outstanding: 8,000,000
  Option pool pre-round: 1,000,000 (reserved)
  Fully diluted pre-round: 9,000,000
  
  SAFE 1: $250,000 at $4M post-money cap → ownership = 250k/4M = 6.25%
  SAFE 2: $150,000 at $6M post-money cap → ownership = 150k/6M = 2.5%
  
  Total SAFE dilution: 8.75% of post-SAFE, post-financing cap

Series A comes in at $10M pre-money, $4M raise:
  Post-money: $14M
  New option pool: 1,500,000 shares (10% post-money, created pre-money = dilutes founders)
  
  SAFE 1 converts at $4M cap:
    Shares = 6.25% × total post-money shares
  SAFE 2 converts at $6M cap:
    Shares = 2.5% × total post-money shares
  Series A investors: $4M / Series A price per share
  
Output cap table:
  Founders: [X]%
  SAFE 1 investor: 6.25%
  SAFE 2 investor: 2.5%
  Series A investors: [Y]%
  Option pool: 10%
  Total: 100%
```

### Effective Valuation Check

When a pre-money option pool shuffle is present:

```
Stated pre-money: $8,000,000
Option pool to create (pre-money): 1,500,000 shares at Series A price
Series A price: $8M / 8,000,000 pre-round shares = $1.00/share

Option pool cost: 1,500,000 × $1.00 = $1,500,000 (borne by founders)
Effective pre-money valuation founders receive: $8M - $1.5M = $6.5M

⚠️ Always model the "founder effective valuation" — it's often 15-25% lower than stated.
```

---

## Competing Term Sheet Comparison

When multiple term sheets are provided, produce a side-by-side comparison:

```
                        Term Sheet A        Term Sheet B
Investor:               Acme Ventures       Beta Capital
Pre-Money Valuation:    $8,000,000          $10,000,000
Investment:             $2,000,000          $2,500,000
Post-Money:             $10,000,000         $12,500,000
Founder ownership post: 68.2%               72.1%
Liquidation Pref:       1x non-part.        1x participating (cap 3x)
Anti-Dilution:          Broad WA            Full ratchet ⚠️
Option Pool:            10% post-money      15% pre-money ⚠️
Board:                  2F / 1I / 2I        2F / 2I / 1I
Pro-Rata:               Yes (major)         Yes (all)
Exclusivity:            30 days             45 days ⚠️
---
Red Flags:              0                   3 ⚠️
Recommended:            ✅ Term Sheet A     ❌ Proceed with caution
```

**Economic comparison at exit:**

```
Exit at $50M (acquisition):

Term Sheet A (1x non-participating):
  Investor: $2M preference, then converts to equity
  At $50M: investor converts (equity > preference)
  Investor share: 20% × $50M = $10M
  Founders + team: $40M

Term Sheet B (1x participating, 3x cap):
  Investor takes $2.5M preference first
  Remaining: $47.5M split pro-rata
  Investor: $2.5M + (20% × $47.5M) = $2.5M + $9.5M = $12M
  Founders + team: $38M

Difference: $2M less to founders at $50M exit from participating preferred.
At 2x cap ($5M), participating preference triggers cap and converts. Above cap, behaves like non-participating.
```

---

## Plain Language Translation Examples

**Legal text:** *"The Company shall maintain a fully diluted option pool of no less than 20% of the outstanding capital stock of the Company calculated on a post-financing basis, which pool shall be created prior to the closing of the financing."*

**Translation:** You must create a 20% option pool *before* this round closes. That means it comes out of your pre-money valuation, not the investor's. If your pre-money is $8M with 8M shares, you'll need to add ~2M new shares for the pool at closing, reducing your effective per-share pre-money valuation. Your founders' ownership % drops immediately. **This is the option pool shuffle — negotiate to have the pool created post-money instead.**

---

**Legal text:** *"In the event of a Liquidation Event, the holders of Series A Preferred Stock shall be entitled to receive, prior and in preference to any distribution to the holders of Common Stock, an amount per share equal to two times (2x) the Original Issue Price plus all accrued and unpaid dividends."*

**Translation:** If the company is sold, investors get 2× their money back before you see a dollar. On a $2M investment, that's $4M off the top. If the exit is $5M, investors get $4M, founders split $1M. **This is a 2× liquidation preference — above market and founder-unfriendly. Standard is 1×.**

---

## Step-by-Step Workflow

### Step 1: Extract Key Terms
If a document is provided (PDF/text), parse out all economic and control terms using the term checklists above. If missing, ask:
- What instrument type? (SAFE, note, priced round)
- What are the economic terms? (amount, cap, discount, preference)
- What are the control terms? (board seats, pro-rata, drag-along)

### Step 2: Run Red Flag Checklist
Flag every term against the 🔴/🟡/🟢 checklist. Assign risk level.

### Step 3: Model Dilution
Calculate founder ownership % post-transaction and at key exit scenarios ($10M, $25M, $50M, $100M).

### Step 4: Produce Plain-Language Summary
One page max:
- What is this deal at a glance?
- What are the 3 most important things to understand?
- What should be negotiated?
- What's the recommendation?

### Step 5: Compare (if multiple sheets)
Side-by-side table + economic comparison at exit scenarios.

---

## Output Templates

### Quick Summary (1-pager)

```
TERM SHEET ANALYSIS
Company: [Name] | Date: [Date] | Analyst: Sam Ledger

DEAL SUMMARY
  Instrument: Post-Money SAFE
  Amount: $250,000
  Valuation Cap: $4,000,000 (post-money)
  Your implied ownership: 6.25%

RED FLAGS: 0 (clean term sheet)
YELLOW FLAGS: 1 — pro-rata rights for investor

KEY TERMS
  ✅ Post-money cap (ownership locked)
  ✅ No discount clause
  ✅ Standard YC MFN provision: No
  🟡 Pro-rata rights: Yes (investor can follow-on)

DILUTION AT SERIES A ($10M pre, $2M raise):
  Your ownership: ~52% (assuming 15% option pool, $750k total SAFEs)
  SAFE investor: ~6.0% (post-money cap math)
  Series A: ~16.7%
  Option pool: 15%

RECOMMENDATION:
  This is a standard, founder-friendly SAFE. No material changes needed.
  Confirm: post-money cap (not pre). Check MFN if issuing multiple SAFEs.
```

---

## Integration Points

- **`cap-table-manager`** — build full cap table post-conversion
- **`startup-financial-model`** — model how new funding extends runway
- **`investor-memo-generator`** — produce the investor memo that accompanies this raise
- **`due-diligence-dataroom`** — organize diligence materials investors request at term sheet stage
- **`crypto-tax-agent`** — tax implications of SAFE/equity conversion events

---

## Reference: Key Terms Glossary

```
Pre-money valuation:    Company value BEFORE new investment
Post-money valuation:   Pre-money + investment amount
Fully diluted shares:   All shares including options, warrants, convertibles
Liquidation preference: Amount investors get before common stockholders at exit
Participating preferred: Investors get preference AND pro-rata equity (double-dip)
Anti-dilution:          Adjusts investor price if future round is lower (down round)
Full ratchet:           Investor price drops to lowest future round price (harsh)
Broad-based WA:         Weighted average adjustment — more founder-friendly
Option pool shuffle:    Creating option pool pre-money to inflate stated valuation
Pro-rata rights:        Right to invest in future rounds to maintain ownership %
Drag-along:             Majority can force all shareholders to approve a sale
ROFR:                   Right of First Refusal — company/investors buy shares first
SAFE:                   Simple Agreement for Future Equity (YC standard instrument)
Qualified financing:    The trigger event that converts SAFEs/notes (typically ≥$1M raise)
MFN SAFE:               Most Favored Nation — converts at best terms of future SAFEs issued
```