dl-databook-financials

name: dl-databook-financials description: >- Builds the SUCAP and FinInputs tabs of the Overland deal Databook (.xlsx) from a deal's financial source set (QoE, audited and management financials, reporting packages, compliance certificates, the Posting Memo Backup). Use at P6 diligence when the user asks to "build the Databook," "populate SUCAP and FinInputs," or "load the deal financials into the Databook." Resolves the period grid from the SUCAP anchors, applies backwards-from-most-recent restatement precedence and reclass/diligence/pro-forma classification, ties CA EBITDA to the QoE control total, and writes only input cells behind a formula-protection preservation gate via a plan-validate-execute workflow. A conditional structural pre-pass (off by default) handles per-business-unit scaffolds, off-quarter marketed LTMs, and in-workbook QoE staging. Template bundled in assets/. Not for the Posting Memo Backup (use dl-memo-posting-backup), general Excel tasks, or the other eight Databook sheets.

Populating the Databook financials (SUCAP + FinInputs)

Overview

Builds the SUCAP (sources & uses / pro-forma cap / facilities detail) and FinInputs (quarterly financial input grid) tabs of the Overland deal Databook from the deal's primary financial materials. The output is the populated workbook itself — a deal-named copy of the bundled template, written in place under a mandatory formula-protection preservation gate. Scope is SUCAP + FinInputs only; the other eight sheets and the IQ_* Capital-IQ plumbing are never touched.

This is a high-stakes batch write with cross-period validation rules, so it uses the plan-validate-execute workflow: build a plan.json, validate it deterministically, then populate only after it passes.

Template asset (bundled — no upload required):

assets/databook-template.xlsx

Step 6 copies it to a deal-named working copy at runtime. Never write back to the bundled assets/ copy.

References (read before first use):

• reference/cell-map.md — the inspected ETL surface: named-range anchors, SUCAP input cells, the FinInputs period grid and input/formula row partition, the two-step bridge identity, the plan.json contract; the structure-manifest contract, the rebased-verifier semantics, the off-quarter-LTM column algorithm; the QoEInputs scaffold contract.
• reference/etl-construction.md — source taxonomy, recency ranking, the backwards-from-most-recent restatement algorithm, the reclass/diligence/pro-forma decision tree, the QoE-tie fallback hierarchy, bespoke-deal escalation; the BU-decomposition decision gate and roll-up contract; the QoE-staging decision gate (§4).

Dependencies

pip install openpyxl

Load workbooks with data_only=False (never data_only=True — it discards formulas permanently).

Workflow

Copy this checklist and check items off as you go:

Databook financials progress:
- [ ] Step 0: Orient (deliverable, classification)
- [ ] Step 1: Mandatory user gate (anchors, QoE coverage, source dates)
- [ ] Step 2: Assemble + classify the source set
- [ ] Step 3: Resolve periods (scripts/resolve_periods.py)
- [ ] Step 3.5: Restructure IF flexibility needed (scripts/restructure_databook.py)
- [ ] Step 4: Build the reconciliation plan.json
- [ ] Step 5: Validate the plan (scripts/reconcile_sources.py) — loop to pass
- [ ] Step 6: Populate (scripts/populate_databook.py) + verify

Step 3.5 is conditional and off by default: skip it entirely unless the deal needs BU-decomposition, has an off-quarter marketed LTM, or stages the QoE in-workbook (see Step 3.5).

Step 0 — Orientation

• Deliverable: the populated bundled .xlsx (SUCAP + FinInputs only), a deal-named copy — not a regenerated workbook, not a text summary.
• The RESTRICTED IC debrief is read only for the final approved structure/conditions — never echo IC deliberation or votes into the workbook.
• [INSUFFICIENT DATA — <what is missing>] is the only uncertainty marker. Never fabricate a figure; never write 0 as if it were reported (blank ≠ zero — the template ships 0 placeholders, so leave the default untouched where not disclosed).

Step 1 — Mandatory user gate (before reading any financials)

Ask in one message and wait for the response:

1. Deal close date.
2. QoE availability and which periods it covers (drives the tie basis).
3. The list of source packages with their as-of / issue dates (the recency ranking).
4. LastFYE, LastActDate, LTMDate, and the monthly stub anchor date (AH3).
5. The final-IC structure source: the IC debrief, or the posting proposal.

If the invocation already supplies all of these, confirm and proceed.

Step 2 — Assemble and classify the source set

Scan project attachments first. Classify each into the source taxonomy (see reference/etl-construction.md): QoE, audited, management quarterly/monthly, reporting package, compliance certificate, posting-memo backup, add-on pre-acquisition.

If the attached set is insufficient to populate the required periods/lines, explicitly ask the user to connect and scan the Overland SharePoint deal folder via the Microsoft 365 connector, naming what is missing (which periods/lines). Do not auto-scan silently.

• Use fully-qualified Server:tool MCP names; confirm the server name at first invocation — do not hard-code an identifier.
• The connector is read-only and a gap-filler, never a hard dependency. If it is not connected / the folder is not found / it errors, degrade gracefully: populate only what the attachments support, set source_basis accordingly, and mark every unfilled required cell [INSUFFICIENT DATA — <line/period> not in attachments; SharePoint deal folder not accessible]. Never write a fabricated or zero value for a missing figure — a hallucinated financial input silently corrupting the credit model is the worst failure mode here.

Step 3 — Resolve periods

Run the resolver; never hand-build dates:

python scripts/resolve_periods.py --last-fye YYYY-MM-DD \
    --last-act-date YYYY-MM-DD --stub-anchor YYYY-MM-DD \
    [--ltm-date YYYY-MM-DD]

It emits the FinInputs column→period map (column, period_end, fy_label, actual_or_estimate, kind) and annual_q4_columns (the FY-Q4 columns the Posting Memo Backup FinSum annual seed and control totals align to). Pass --ltm-date (the marketed LTM = SUCAP!V3) to additionally emit the off_quarter_ltm block. Off-quarter rule: when the marketed LTM does not land on a fiscal quarter-end, set the monthly stub anchor AH3 = the marketed LTM month-end so the trio becomes LTM-24 / LTM-12 / LTM. The look-up formulas already ship in the template — they are reused, never invented.

Step 3.5 — Restructure (CONDITIONAL — off by default)

Skip this step entirely unless one of three triggers holds:

1. BU-decomposition — per-BU input scaffold tabs are warranted (an add-on's pre-acquisition results cross a period boundary, or a downstream KPI build needs BU granularity), with the trigger + reason recorded.
2. Off-quarter LTM — resolve_periods.py emitted off_quarter_ltm.is_off_quarter = true.
3. In-workbook QoE staging — the deal's financial spine is a third-party QoE with an itemizable, bridge-shaped adjustment schedule to stage as a value-blank QoEInputs scaffold.

python scripts/restructure_databook.py --structure structure.json \
    --out restructured.xlsx --manifest structure_manifest.json

This is the only code path allowed to change workbook structure. It runs before plan-build, produces a restructured-but-value-blank template plus a self-verified structure_manifest.json, and does not touch the restatement resolver, the QoE-tie, or the formula-protection gate. Base FinInputs always stays the single grid the model consumes. The decision gates, the structure.json contract, and the trigger definitions are specified in reference/etl-construction.md (BU gate + QoE-staging gate §4) and reference/cell-map.md (structure manifest, off-quarter algorithm, QoEInputs scaffold) — follow them exactly; do not re-derive the rules here.

Escalate, do not auto-decide (the script refuses with no output; record the halt): an ambiguous add-on boundary or per-BU split; a bespoke non-sum consolidation (WIP sub-ledgers, cross-BU mixing, a separate Consolidated tab); a QoE that is not itemizable / not bridge-shaped; a requested cell-for-cell base-FinInputs re-point (it defeats the formula-protection gate); or a realized_block off-quarter form (only the template's own template_trio is ever auto-wired).

If Step 3.5 ran, Steps 5–6 use restructured.xlsx and pass --manifest structure_manifest.json (the verifier is rebased, not loosened — see reference/cell-map.md).

Step 4 — Build the reconciliation plan.json

Build plan.json (the verifiable intermediate; schema mirrors the skill's own structured-output schema). Per the reference/etl-construction.md rules:

• Structure: the proposed Overland structure from the posting memo / Posting Memo Backup SUCAP, overlaid with the IC-debrief final approved decision/conditions, into the SUCAP facilities-detail + uses + anchors + TEV.
• Seeds & controls: the Posting Memo Backup FinSum annual + LTM figures seed and control-total-anchor the FinInputs annual columns and the CA-EBITDA tie.
• Quarterly detail: from interim/primary financials, applying the backwards-from-most-recent restatement precedence and the reclass / diligence / pro-forma classification — recorded per (line, period) in reconciliation_ledger, and per period in ebitda_bridge with the CA-EBITDA↔QoE control-total tie.

This is the medium-freedom analytical layer. Cell addresses, the input/formula partition, and the plan.json key contract are in reference/cell-map.md.

Step 5 — Validate the plan

python scripts/reconcile_sources.py plan.json

It deterministically asserts: the two-step bridge identity per period; CA EBITDA ties to the QoE (or fallback) total within tolerance; no restated prior is overwritten by an older as-reported; every adjustment row has a non-empty label; reclass groups net to zero at every subtotal (a "reclass" that moves EBITDA / Net Income / Pre-Tax is invalid); nothing targets a formula cell or a cell outside the input surface.

Feedback loop: every violation prints a specific message. Fix plan.json and re-run until it exits 0. Tie-out failures are surfaced as explicit flags — never silently coerce CA EBITDA to force the tie.

Step 6 — Populate

python scripts/populate_databook.py plan.json
# If Step 3.5 ran, point at the restructured template + manifest instead:
# python scripts/populate_databook.py plan.json \
#     --template restructured.xlsx --manifest structure_manifest.json

It opens the bundled assets/databook-template.xlsx (or the restructured template from Step 3.5), runs the mandatory formula-protection preservation gate on every target (refusing — with no file written — any cell whose template value is a formula / ArrayFormula / DataTableFormula: all subtotal/ratio rows, the YTD/TTM blocks, the SUCAP INDEX/MATCH columns, I42), writes only SUCAP input cells + FinInputs input rows, and saves a deal-named vS copy (<Company>_Databook_vS.xlsx). It then re-reads the saved workbook to verify: the gate logged zero violations; the eight out-of-scope sheets, all SUCAP/FinInputs formula cells, and every defined name are value-identical to the template; the written diligence/pro-forma cells re-sum to the validated bridge.

(openpyxl does not evaluate formulas; cached values stay until the workbook is opened in Excel — expected, not an error.)

Escalation

Halt and request analyst confirmation (record in escalations) before populating when the deal has add-on roll-ups, mid-period acquisitions, an add-on spanning a period boundary, multiple QoE reports, a stub-period mismatch, a HoldCo/PIK/seller-note/earn-out/preferred structure, or no QoE and no reporting package and no compliance certificate for a required period. Also halt on every Step-3.5 escalation trigger listed above, and whenever the authoritative source is ambiguous with no QoE to anchor on. Never auto-wire; never auto-decide a bespoke shape. See reference/etl-construction.md.

Classification & review state

The Databook is internal (CONFIDENTIAL, MNPI) — never outbound, never co-lender/LP-shared; no outbound-redaction checklist applies. The output is a draft pending human review: the draft signal is the vS filename — no [DRAFT — HUMAN REVIEW REQUIRED] banner in the workbook body, because the file is an in-place-edited production template and a banner would corrupt it. A reviewer approves before the Databook feeds the model; never approve, finalize, or transition the state. [INSUFFICIENT DATA — <what is missing>] for any gap — never fabricate.

Runtime

The scripts run where Python is available: Claude Desktop or claude.ai with code execution enabled. In the Excel add-in (or any chat without code execution), do not attempt to build SUCAP/FinInputs by hand — the formula-protection preservation gate and the plan validation cannot run there, and an ungated build risks corrupting the credit model. Say so and direct the user to run the skill in Claude Desktop; source classification and plan review discussion still work anywhere. The Microsoft 365 connector (Step 2) uses fully-qualified Server:tool names; confirm the server name at first invocation.