datapack-builder

Financial Data Pack Builder

Build standardized data packs for CrowTech investment banking, PE diligence, and portfolio reporting. Transform CIMs, offering memorandums, SEC filings, or MCP-sourced data into polished Excel workbooks ready for IC review.

Important: Use the xlsx skill for all Excel file creation and manipulation throughout this workflow.

CRITICAL SUCCESS FACTORS

1. Data Accuracy (Zero Tolerance for Errors)

• Trace every number to source with page reference
• Formula-based calculations only (no hardcoded values)
• Cross-check subtotals and totals
• Verify Assets = Liabilities + Equity
• Confirm cash flow ties to balance sheet changes

2. ESSENTIAL RULES

RULE 1: Financial data (measuring money) → Currency format with $ Triggers: Revenue, EBITDA, Cost, Cash, Debt, Assets, Liabilities, Equity, Capex Format: $#,##0.0 for millions; negatives $(123.0)

RULE 2: Operational data (counting things) → Number format, NO $ Triggers: Units, Locations, Employees, Patients, Visits, Providers Format: #,##0; negatives (123)

RULE 3: Percentages → Percentage format Triggers: Margin, Growth, Rate, Yield, Utilization, Occupancy, Payor mix Format: 0.0% — display 15.0% NOT 0.15

RULE 4: Years → Text format to prevent comma insertion Display: 2020, 2021, 2022, 2023A, 2024E

RULE 5: Mixed tables — each metric gets its own appropriate format Revenue uses $, clinic count uses number format, margin uses %.

RULE 6: Formulas for all calculations — never hardcode

3. Professional Presentation Standards

Font colors (MANDATORY from xlsx skill):

• Blue (0,0,255): hardcoded inputs
• Black (0,0,0): formulas
• Green (0,128,0): links to other sheets

Fill colors (optional):

• Section headers: dark blue (68,114,196) background, white text
• Sub-headers: light blue (217,225,242), black text
• Input cells: light green/cream (226,239,218), blue text
• Calculated cells: white, black text

Font color tells you WHAT it is; fill tells you WHERE it is.

Always apply: bold headers left-aligned; numbers right-aligned; 2-space indent for sub-items; single underline above subtotals, double underline below totals; freeze panes; minimal borders; Calibri/Arial 11pt.

Never: borders around every cell; multiple fonts; charts unless requested.

Structural Consistency — standard 8-tab structure

1. Executive Summary
2. Historical Financials (Income Statement)
3. Balance Sheet
4. Cash Flow Statement
5. Operating Metrics
6. Property/Segment Performance (if applicable)
7. Market Analysis
8. Investment Highlights

Tab 1: Executive Summary

One-page overview: business model (2-3 sentences), 3-5 investment highlights, financial snapshot table (Revenue, EBITDA, Growth for last 3 years + projections), transaction overview.

Tab 2: Historical Financials (Income Statement)

Revenue by segment, COGS, gross profit + margin %, opex detail (S&M, R&D, G&A), EBITDA and Adjusted EBITDA, D&A, interest, taxes, net income. Years as text columns; $ millions or thousands (specify units); accounting format; single/double underlines.

Tab 3: Balance Sheet

Current assets (cash, AR, inventory, prepaid), long-term assets (PP&E, intangibles, goodwill), current liabilities, long-term liabilities, equity. Verify Assets = Liabilities + Equity. Include working capital.

Tab 4: Cash Flow Statement

Operating (indirect), investing (capex, acquisitions), financing (debt, equity, dividends). Link to IS and BS; reconcile NI to operating CF.

Tab 5: Operating Metrics

Non-financial KPIs. For healthcare services: locations, providers, patients/visits, payor mix %, same-store growth %. For healthcare AI: ARR, customers, net revenue retention, Rule of 40.

CRITICAL FORMAT NOTE: NO dollar signs on operational metrics — these are quantities, not currency. Whole numbers with commas (1,250 NOT $1,250); percentages for rates.

Tab 6: Property/Segment Performance (if applicable)

Revenue and profitability by segment/location; segment-specific KPIs; comparative analysis.

Tab 7: Market Analysis

Market size and growth, competitive landscape, peer benchmarks, regulatory environment (for healthcare: CMS rulemaking, CON laws, value-based care trends, HIPAA/SOC 2 for AI).

Tab 8: Investment Highlights

Narrative writeup: competitive strengths, growth opportunities, risks and mitigation, management assessment, investment thesis.

STEP-BY-STEP WORKFLOW

Phase 1: Document Processing and Data Extraction

Step 1.1: Analyze source data — review documents/filings/MCP sources, locate 3-5 years of statements, note fiscal year end, flag data quality issues.

Step 1.2: Extract financial statements — historical IS, BS snapshots, CF, management projections. Record page references.

Step 1.3: Extract operating metrics — industry KPIs, unit economics, customer/location/capacity data.

Step 1.4: Extract market and industry data — competitive positioning, market size, benchmarks, peers.

Step 1.5: Note key context — transaction structure, management background, investment highlights, risks, data gaps.

Phase 2: Data Normalization and Standardization

Step 2.1: Normalize accounting presentation — consistent line items across years; standardize revenue recognition; flag one-time charges; build Adjusted EBITDA reconciliation; note policy changes.

Step 2.2: Apply format detection logic — read tab/table/column/row context; apply essential rules; when uncertain, examine source; default to cleaner formatting.

Step 2.3: Identify normalization adjustments — restructuring (if truly non-recurring), SBC (add back per PE standard), acquisition costs, legal settlements (evaluate recurrence), asset sales/impairments (exclude), related-party normalization. Cite source per item.

Step 2.4: Create adjustment schedule — for each normalization: what, why, source citation, $ by year, recurrence risk, reported-to-adjusted calc.

Step 2.5: Verify data integrity — formulas tie; balance sheet balances; CF ties to BS; cross-tab consistency; flag discrepancies.

Phase 3: Build Excel Workbook

CRITICAL: Use xlsx skill for all Excel file manipulation. Read xlsx skill documentation before proceeding.

Step 3.1: Create standardized tab structure — the 8 tabs above (omit Property Performance if not applicable).

Step 3.2: Build each tab with proper formatting — apply the rules above systematically (bold left-aligned headers, currency vs. number vs. percent, year text format, parentheses for negatives, single/double underlines).

Step 3.3: Insert formulas for calculations — all subtotals and totals formula-based; link BS to IS; link CF to IS and BS; create cross-tab references; avoid hardcoding.

<correct_patterns>

Row Reference Tracking - Copy This Pattern

Store row numbers when writing data, then reference them in formulas:

# ✅ CORRECT - Track row numbers as you write
revenue_row = row
write_data_row(ws, row, "Revenue", revenue_values)
row += 1

ebitda_row = row
write_data_row(ws, row, "EBITDA", ebitda_values)
row += 1

# Use stored row numbers in formulas
margin_row = row
for col in year_columns:
    cell = ws.cell(row=margin_row, column=col)
    cell.value = f"={get_column_letter(col)}{ebitda_row}/{get_column_letter(col)}{revenue_row}"

For complex models, use a dictionary:

row_refs = {
    'revenue': 5,
    'cogs': 6,
    'gross_profit': 7,
    'ebitda': 12
}

# Later in formulas
margin_formula = f"=B{row_refs['ebitda']}/B{row_refs['revenue']}"

</correct_patterns>

<common_mistakes>

WRONG: Hardcoded Row Offsets

Don't use relative offsets - they break when table structure changes:

# ❌ WRONG - Fragile offset-based references
formula = f"=B{row-15}/B{row-19}"  # What is row-15? What is row-19?

# ❌ WRONG - Magic numbers
formula = f"=B{current_row-10}*C{current_row-20}"

Why this fails:

• Breaks silently when you add/remove rows
• Impossible to verify correctness by reading code
• Creates debugging nightmares in the delivered Excel file

</common_mistakes>

Step 3.4: Apply professional presentation — freeze top row and first column; column widths ~12-15; right-align numbers; left-align text; single/double underlines; minimal borders.

Phase 4: Scenario Building (if projections included)

Management Case: present company projections as provided; document growth/margin/capex assumptions; flag hockey-stick inflections.

Base Case (Risk-Adjusted): conservative adjustments — revenue growth haircut for execution risk, moderated margin expansion, higher capex if growth-dependent, working capital additions if understated, delayed synergy realization. Document each adjustment. For healthcare services, stress reimbursement-rate assumptions and same-store growth; for value-based care revenue, stress risk-sharing outcomes.

Downside Case (recommended for LBO): stress test — revenue decline, margin compression, covenant and liquidity check, downside protection.

Documentation: assumptions schedule with key drivers by scenario, rationale, sensitivities, historical forecast accuracy, industry benchmarks.

Phase 5: Quality Control and Validation

5.1 Data accuracy: every number traces to source; formulas only; subtotals correct; years without commas; no #REF!/#VALUE!/#DIV/0!/#N/A.

5.2 Format consistency: $ on financial data; no $ on operational; % formatted; negatives in parens; headers bold/left; numbers right; years text.

5.3 Structure/completeness: all tabs present; executive summary one page; key metrics captured; logical flow; appropriate granularity.

5.4 Presentation: minimal borders; consistent indentation; proper underlines; clean look; reasonable column widths.

5.5 Documentation: normalizations documented with rationale; source citations (page, URL, server); assumptions stated; executive summary impactful; filename includes company and date.

Phase 6: Final Delivery

6.1 Executive summary: business model (2-3 sentences), key metrics table, 3-5 investment highlights, notable risks, transaction context.

6.2 Final file: save as CompanyName_DataPack_YYYY-MM-DD.xlsx.

NORMALIZATION PATTERNS

Common Adjustments to EBITDA

1. Restructuring charges — add back only if truly non-recurring (one-time severance, facility closure). Do not add back if company restructures annually. Document nature and rationale.

2. Stock-based compensation — industry standard: add back for PE analysis; treat as non-cash opex; be consistent across periods.

3. Acquisition-related costs — add back transaction fees and integration costs; do not add back ongoing integration investments; cite source.

4. Legal settlements and litigation — add back if isolated; assess recurrence pattern; consider normal-course-of-business risk (for healthcare: malpractice exposure and Stark/AKS settlements deserve extra scrutiny).

5. Asset sales or impairments — exclude gains/losses on sales from operating EBITDA; remove non-recurring impairments; adjust revenue if assets generated operating income.

6. Related-party adjustments — normalize above-market rent/management fees to market rates with documentation; remove personal expenses.

Conservative vs Aggressive Normalization

Management Case: all management-proposed adjustments; accept company definition of non-recurring; more aggressive; use to understand management's view.

Base Case (Recommended): only clearly non-recurring items; higher scrutiny on recurring "one-time" charges; exclude speculative adjustments; defensible to IC.

INDUSTRY-SPECIFIC ADAPTATIONS

Technology/SaaS (including healthcare AI / automation)

ARR/MRR, customer count by cohort, CAC/LTV, gross and net churn, net revenue retention, Rule of 40, magic number. Format notes: ARR is $, customer count is #, rates are %. For healthcare AI also capture HIPAA/SOC 2 status and any FDA pathway (De Novo, 510(k)) where relevant.

Manufacturing/Industrial

Production capacity and utilization %, units by product, inventory turns, gross margin by line, order backlog. Units and capacity are numbers; utilization is %; revenue/costs are $.

Real Estate/Hospitality

Properties/rooms/sqft, occupancy %, ADR ($), RevPAR ($), NOI ($), cap rates %, FF&E reserve.

Healthcare/Services

Locations/facilities, providers/employees, patients/visits, revenue per visit ($), payor mix % (Medicare / Medicaid / commercial / self-pay), same-store growth %, value-based care exposure (% capitated / MSSP / ACO REACH / MA risk), CMS rate sensitivity, corporate-practice-of-medicine and CON exposure.

FINAL DELIVERY CHECKLIST

Structure: all tabs present and sequenced; each tab has title; executive summary fits one page.

Data Accuracy: all numbers trace to source; formulas only; subtotals verified; BS balances; no formula errors.

Formatting — years/numbers: 2020 (no comma); $ on financial data; NO $ on operational; % correctly formatted; negatives in parens.

Formatting — professional: bold left-aligned headers; right-aligned numbers; 2-space sub-item indent; single/double underlines; frozen panes; consistent font; minimal borders.

Content completeness: IS/BS/CF complete; operating metrics comprehensive; normalizations documented; assumptions stated; summary impactful; highlights compelling; market context included.

Documentation: every data cell cited; assumptions documented; limitations noted; filename CompanyName_DataPack_YYYY-MM-DD.xlsx.

Final output: file saved with proper naming; QC checks passed.