Use when financial-services work requires build professional financial services data packs from various sources including CIMs, offering memorandums, SEC filings, web search, or MCP servers. Extract, normalize, and standardize financial data into investment committee-ready Excel workbooks with consistent structure, proper formatting, and documented assumptions. Use for M&A due diligence, private equity analysis, investment committee materials, and standardizing financial reporting across portfolio companies. Do not use for simple financial calculations or working with already-completed data packs..
Use when financial-services work requires build professional financial services data packs from various sources including CIMs, offering memorandums, SEC filings, web search, or MCP servers. Extract, normalize, and standardize financial data into investment committee-ready Excel workbooks with consistent structure, proper formatting, and documented assumptions. Use for M&A due diligence, private equity analysis, investment committee materials, and standardizing financial reporting across portfolio companies. Do not use for simple financial calculations or working with already-completed data packs..
Build professional, standardized financial data packs for private equity, investment banking, and asset management. Transform financial data from CIMs, offering memorandums, SEC filings, web search, or MCP server access into polished Excel workbooks ready for investment committee review.
Important: Use the xlsx skill for all Excel file creation and manipulation throughout this workflow.
CRITICAL SUCCESS FACTORS
Every data pack must achieve these standards. Failure on any point makes the deliverable unusable.
1. Data Accuracy (Zero Tolerance for Errors)
Trace every number to source document with page reference
Use formula-based calculations exclusively (no hardcoded values)
Cross-check subtotals and totals for internal consistency
RULE 1: Financial data (measuring money) → Currency format with $
Triggers: Revenue, Sales, Income, EBITDA, Profit, Loss, Cost, Expense, Cash, Debt, Assets, Liabilities, Equity, Capex
Format: $#,##0.0 for millions, $#,##0 for thousands
Negatives: $(123.0) NOT -$123
RULE 2: Operational data (counting things) → Number format, NO $
Triggers: Units, Stores, Locations, Employees, Customers, Square Feet, Properties, Headcount
Format: #,##0 with commas
Negatives: (123) consistent with rest of table
RULE 3: Percentages (rates and ratios) → Percentage format
Triggers: Margin, Growth, Rate, Percentage, Yield, Return, Utilization, Occupancy
Format: 0.0% for one decimal place
Display: 15.0% NOT 0.15
RULE 4: Years → Text format to prevent comma insertion
Format: Text or custom to prevent 2,024
Display: 2020, 2021, 2022, 2023A, 2024E
RULE 5: When context is mixed, each metric gets its own appropriate format
Example:
Revenue and per-store metrics use $, Store count uses number format.
RULE 6: Use formulas for all calculations → Never hardcode calculated values
All subtotals, totals, ratios, and derived metrics must be formula-based, not hardcoded values. This ensures accuracy and allows for dynamic updates.
3. Professional Presentation Standards
Formatting Standards:
Color Scheme - Two Layers:
Layer 1: Font Colors (MANDATORY from xlsx skill)
Blue text (RGB: 0,0,255): ALL hardcoded inputs (historical data, assumptions), NOT normal text
Black text (RGB: 0,0,0): ALL formulas and calculations
Green text (RGB: 0,128,0): Links to other sheets
Layer 2: Fill Colors (Optional for enhanced presentation)
Fill colors are optional and should only be applied if requested by the user or if enhancing presentation
If the user requests colors or professional formatting, use this standard scheme:
Section headers: Dark blue (RGB: 68,114,196) background with white text
Sub-headers/column headers: Light blue (RGB: 217,225,242) background with black text
Input cells: Light green/cream (RGB: 226,239,218) background with blue text
Calculated cells: White background with black text
Users can override with custom brand colors if specified
How the layers work together (if fill colors are used):
Input cell: Blue text + light green fill = "User-entered data"
Formula cell: Black text + white background = "Calculated value"
Sheet link: Green text + white background = "Reference from another tab"
Font color tells you WHAT it is. Fill color tells you WHERE it is (if used).
IMPORTANT: Font colors from xlsx skill are mandatory. Fill colors are optional - default is white/no fill unless the user requests enhanced formatting or colors.
Always apply:
Bold headers, left-aligned
Numbers right-aligned
2-space indentation for sub-items
Single underline above subtotals
Double underline below final totals
Freeze panes on row/column headers
Minimal borders (only where structurally needed)
Consistent font (typically Calibri or Arial 11pt)
Never include:
Borders around every cell
Multiple fonts or font sizes
Charts unless specifically requested
Excessive formatting or decoration
Structural Consistency
Use the standard 8-tab structure unless explicitly instructed otherwise:
Executive Summary
Historical Financials (Income Statement)
Balance Sheet
Cash Flow Statement
Operating Metrics
Property/Segment Performance (if applicable)
Market Analysis
Investment Highlights
Tab 1: Executive Summary
Purpose: One-page overview for busy executives
Contents:
Company overview (2-3 sentences on business model)
Key investment highlights (3-5 bullet points)
Financial snapshot table (Revenue, EBITDA, Growth for last 3 years + projections)
Legal settlements or litigation costs (evaluate recurrence risk)
Asset sales or impairments (exclude from operating results)
Related party adjustments (normalize to market rates)
Note: Source citation format varies by data source (page numbers for documents, URLs for web sources, server references for MCP data)
Step 2.4: Create adjustment schedule
For every normalization:
Document what was adjusted and why
Cite source (document page number, URL, or data source reference)
Quantify dollar impact by year
Assess recurrence risk
Show calculation from reported to adjusted figures
Step 2.5: Verify data integrity
Confirm subtotals sum correctly using formulas
Verify balance sheet balances
Check cash flow ties to balance sheet changes
Cross-check numbers across tabs for consistency
Flag any discrepancies for investigation
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
Create workbook with tabs:
Executive Summary
Historical Financials
Balance Sheet
Cash Flow
Operating Metrics
Property Performance (if applicable)
Market Analysis
Investment Highlights
Step 3.2: Build each tab with proper formatting
Apply formatting rules systematically:
Headers: Bold, left-aligned, 11pt font
Financial data: Currency format $#,##0.0 for millions
Operational data: Number format #,##0 (no $)
Percentages: 0.0% format
Years: Text format to prevent comma insertion
Negatives: Use accounting format with parentheses
Underlines: Single above subtotals, double below totals
Step 3.3: Insert formulas for calculations
All subtotals and totals must be formula-based
Link balance sheet to income statement where appropriate
Link cash flow to both income statement and balance sheet
Create cross-tab references for validation
Avoid hardcoding any calculated values
<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 on each data tab
Set appropriate column widths (typically 12-15 characters)
Right-align all numeric data
Left-align all text and headers
Add single/double underlines per accounting standards
Ensure clean, minimal appearance
Phase 4: Scenario Building (if projections included)
Management Case:
Present company's projections as provided in source materials:
Extract all management assumptions
Document growth rates, margin expansion, capital requirements
Note key drivers and sensitivities
Flag any "hockey stick" inflections that require skepticism
Present as "Management Case" with clear labeling
Base Case (Risk-Adjusted):
Apply conservative adjustments to management projections based on company-specific risk factors:
File saved to outputs with proper naming convention
All quality control checks passed
Hermes Profile Notes
This skill was packaged from pi-financial-services source path plugins/vertical-plugins/investment-banking/skills/datapack-builder for the Hermes financial-services profile. Use institutional data connectors first when available, cite sources, and stage outputs for qualified human review.
Common Pitfalls
Do not present drafts as investment, legal, tax, or accounting advice.
Do not use web search as the primary source when an institutional MCP/data connector is available.
Do not execute transactions, contact clients, post to a ledger, or approve onboarding. Stage outputs for review.
Verification Checklist
Inputs, assumptions, dates, and currencies are explicit.
Numbers tie across tables, models, decks, and memos.
Sources are cited and institutional data is preferred where available.
Output is labeled draft / for human review where appropriate.
