Neura Market Financial Model Builder Reference

Overview

The Neura Market Financial Model Builder populates integrated 3-statement financial model templates (Income Statement, Balance Sheet, Cash Flow Statement) with proper linkages. It uses headless openpyxl to produce an .xlsx file on disk. The tool ensures cross-statement integrity and formula-driven projections, and validates model outputs against source data and scenario assumptions.

Use this tool when building or populating a 3-statement financial model from a template, ensuring cross-statement integrity and formula-driven projections, or validating model outputs against source data and scenario assumptions.

Capabilities

The tool can:

• Identify template tab organization and structure
• Populate historical actuals and assumption drivers
• Write Excel formulas for all projection cells (no hardcoded computed values)
• Link Income Statement, Balance Sheet, and Cash Flow Statement
• Perform margin analysis (if prompted or template requires)
• Calculate credit/leverage metrics (if prompted or template requires)
• Implement scenario analysis (Base/Upside/Downside) with a toggle and CHOOSE/INDEX/MATCH
• Validate cross-statement integrity (balance sheet balance, cash tie-out, net income link, retained earnings roll-forward)
• Run comprehensive audit checks (currency, sign conventions, circular reference handling, formula integrity, scenario hierarchy)
• Extract data from SEC filings via MCP or web fallback (if template requires)

Prerequisites

Before using the tool, ensure the following are in place:

• A headless openpyxl environment
• Access to excel-author skill conventions
• A template .xlsx file with existing structure
• A historical data source (user-provided, SEC filings, MCP, or web)
• Assumption driver values
• The recalculation script available at python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx
• If using MCP: structured financial-data MCP configured (Hermes native-mcp support)
• If using web fallback: ability to search/extract from SEC EDGAR, company IR pages, or browser_navigate
• User willingness to review and confirm at each step (after mapping, historicals, IS, BS, CF)

Parameters

Parameter	Meaning	Required
Template file path	Path to the .xlsx template to populate	Yes
Historical data source	Source of historical actuals (user-provided, SEC filings, MCP, web)	Yes
Assumption drivers	Values for revenue growth, margins, working capital days, CapEx %, interest rate, tax rate, etc.	Yes
Scenario definitions (optional)	Base/Upside/Downside driver values if scenario analysis is needed	No
Margin analysis flag	Whether to include margin analysis (only if prompted or template requires)	No
Credit metrics flag	Whether to include credit/leverage metrics (only if prompted or template requires)	No
SEC filings flag	Whether to pull data from SEC filings (only if template specifically requires)	No

Procedures

Analyze Template Structure

1. Review all tabs to identify which exist (IS, BS, CF, WC, DA, Debt, NOL, Assumptions, Checks, etc.)
2. Note tab dependencies (which schedules feed into main statements)
3. Locate input cells (blue font, light grey/white fill) vs. formula cells (black font, white fill) vs. cross-tab links (green font)
4. Identify row structure: model title, section headers, units row, column headers (Actuals vs. Estimates), period labels (e.g., FY2024A, FY2025E)
5. Identify column structure: line item labels in leftmost column, historical years precede projection years, visual border separating them
6. Review existing named ranges (Formulas → Name Manager) and ensure inputs feed into them
7. Confirm projection period (typically 5 years forward from last historical year)
8. Show user which tabs/sections you've identified and confirm before touching any cells

Populate Historical Data

1. Enter historical actuals only into designated input cells (hardcoded numbers allowed here)
2. Match the template's units (thousands, millions, actual values) and sign conventions
3. Use Paste Values (Ctrl+Shift+V) to avoid overwriting formulas with source formatting
4. Verify formulas calculate correctly for historical periods
5. Show user the historical block and confirm values/periods match source data

Only hardcoded numbers allowed in two places: (1) historical actuals, (2) assumption drivers in Assumptions tab. When using Python/openpyxl, write formula strings, not computed results. For example, do not write ws["D15"] = 12500 for a projection cell; instead write the formula string.

Build Income Statement Projections

1. Enter assumption drivers in Assumptions tab (revenue growth, gross margin, SG&A %, etc.)
2. Write formula strings for all projection cells (e.g., ws["D15"] = "=D14*(1+Assumptions!$B$5)") — never write computed results
3. Ensure all expense line items sum to reported totals
4. Ensure subtotals (Gross Profit, EBIT, EBT, Net Income) calculate correctly
5. Ensure tax calculation handles losses correctly
6. Ensure forecast drivers reference Assumptions tab (no hardcodes)
7. Check period-over-period changes are directionally reasonable
8. If prompted or template requires: add margin analysis (Gross Margin %, EBITDA Margin %, EBIT Margin %, Net Income Margin %) directly below each profit line item
9. Run subtotal checks, show user the projected IS, confirm before moving to BS

Every projection cell, roll-forward, linkage, and subtotal must be an Excel formula — never a pre-computed value.

Build Balance Sheet Projections

1. Link cash balance to Cash Flow Statement ending cash
2. Link working capital accounts to supporting schedules (if applicable)
3. Ensure Retained Earnings rolls forward: Prior RE + Net Income - Dividends +/- Adjustments = Ending RE
4. Link debt balances to debt schedule (if applicable)
5. Ensure all balance sheet items have appropriate signs (assets positive, most liabilities positive)
6. If prompted or template requires: add credit/leverage metrics (Total Debt/EBITDA, Net Debt/EBITDA, Interest Coverage, Debt/Total Cap, Debt/Equity, Current Ratio, Quick Ratio) on BS tab
7. If credit metrics added: validate hierarchy (Upside < Base < Downside for leverage, Upside > Base > Downside for coverage and liquidity)
8. If debt covenants known: add explicit compliance checks
9. Show user the balance check (Assets = Liabilities + Equity) for every period, confirm before moving to CF

Build Cash Flow Statement Projections

1. Ensure Net Income at top of CFO matches IS Net Income
2. Ensure non-cash add-backs (D&A, SBC) tie to their source schedules/statements
3. Ensure working capital changes have correct signs (increase in asset = use of cash = negative)
4. Ensure CapEx ties to PP&E schedule or fixed asset roll-forward
5. Ensure financing activities tie to changes in debt and equity accounts on BS
6. Ensure Ending Cash matches BS Cash
7. Ensure Beginning Cash equals prior period Ending Cash
8. Show user the cash tie-out (CF ending cash = BS cash), confirm before finalizing

Implement Scenario Analysis (if applicable)

1. Create scenario toggle (dropdown) in Assumptions tab using CHOOSE or INDEX/MATCH formulas
2. Define Base Case (management guidance/consensus), Upside Case (above-guidance growth, margin expansion), Downside Case (below-trend growth, margin compression)
3. Sensitize key drivers: Revenue growth, Gross margin, SG&A %, DSO/DIO/DPO, CapEx %, Interest rate, Tax rate
4. Verify toggling switches all statements correctly
5. Verify BS balances in all scenarios
6. Verify cash ties out in all scenarios
7. Verify hierarchy holds (Upside > Base > Downside for NI, EBITDA, FCF, margins)

Perform Cross-Statement Integrity Checks

• Check Balance Sheet Balance: Assets - Liabilities - Equity = 0 for every period
• Check Cash Tie-Out: CF Ending Cash - BS Cash = 0
• Check Net Income Link: IS Net Income - CF Starting Net Income = 0
• Check Retained Earnings: Prior RE + NI + SBC - Dividends - BS Ending RE = 0 (adjust for other items as needed)
• Check Equity Financing: ΔCommon Stock/APIC (BS) - Equity Issuance (CFF) = 0
• Check Year 0 Equity: Equity Raised (Year 0) - Beginning Equity Capital (Year 1) = 0

Run Comprehensive Audit Checks

1. Check currency consistency across all tabs
2. Check balance sheet integrity (Assets = L+E for each period)
3. Check cash flow integrity (cash ties to BS, NI ties to IS, D&A/SBC/ΔAR/ΔInventory/ΔAP tie to schedules, CapEx ties to DA schedule)
4. Check retained earnings roll-forward with component breakdown
5. Check working capital (AR, Inventory, AP tie to BS; DSO/DIO/DPO reasonability)
6. Check debt schedule (Total Debt ties to BS; Interest calculation ties to IS)
7. Check equity financing (equity issuance proceeds tie to BS Common Stock/APIC increase)
8. Check NOL schedule (if applicable): Beginning NOL = 0 for new business, NOL increases only when EBT < 0, DTA ties to BS, NOL utilization ≤ 80% of EBT, NOL non-negative, tax expense = 0 when taxable income ≤ 0
9. Check scenario hierarchy (Upside > Base > Downside for NI, EBITDA, FCF, margins; inverted for leverage)
10. Check formula integrity: COGS, S&M, G&A, R&D, SBC driven by % of Revenue (no hardcodes), consistent formulas across projection years, no #REF!/#DIV/0!/#VALUE!/#NAME? errors
11. Check credit metric thresholds (flag Green/Yellow/Red based on covenant thresholds)
12. Aggregate all section statuses into master check: if all pass → '✓ ALL CHECKS PASS', if any fail → '✗ ERRORS DETECTED - REVIEW BELOW'

Finalize Model

1. Toggle through all scenarios (if applicable) to verify checks pass in each case
2. Review and resolve/document all #REF!, #DIV/0!, #VALUE!, #NAME? errors
3. Confirm all input cells have been populated (search for placeholder values)
4. Verify units are consistent across all tabs
5. Save a clean version before any additional modifications
6. Recalculate before delivery: python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx

Constraints and Caveats

• Every projection cell, roll-forward, linkage, and subtotal must be an Excel formula — never a pre-computed value
• Only hardcoded numbers allowed: (1) historical actuals, (2) assumption drivers in Assumptions tab
• When using Python/openpyxl, write formula strings, not computed results
• Do not populate entire model end-to-end and present it complete — break at each statement, show work, catch errors early
• After mapping template → show user tabs/sections and confirm before touching cells
• After populating historicals → show user and confirm before building IS projections
• After building IS → run subtotal checks, show user, confirm before moving to BS
• After building BS → show user balance check for every period, confirm before moving to CF
• After building CF → show user cash tie-out, confirm before finalizing
• Use professional blue/grey palette (dark blue #1F4E79 headers, light blue #D9E1F2 column headers, light grey #F2F2F2 inputs, medium blue #BDD7EE checks) unless template specifies otherwise
• Font color signals what a cell is (input/formula/link); fill color signals where you are (header/data/check)
• Margin analysis only if prompted or template explicitly requires it
• Credit metrics only if prompted or template explicitly requires it
• SEC filings extraction only if template specifically requires it
• Never fabricate data — if a value can't be sourced, flag as [UNSOURCED] and surface to user
• If using MCP for data, prefer structured financial-data MCP; otherwise fall back to web_search/web_extract against SEC EDGAR (https://www.sec.gov/cgi-bin/browse-edgar), company IR pages, or user-provided data
• Enable iterative calculation in Excel (File → Options → Formulas → Enable iterative calculation, max iterations 100, max change 0.001) for circular reference handling (interest expense circularity)
• Add a circuit breaker toggle in Assumptions tab for circular reference handling
• Follow excel-author skill conventions for cell coloring, formulas, named ranges, and sensitivity tables
• Recalculate before delivery using provided script

Failure Modes

• Hardcoded values in projection cells break downstream integrity checks silently
• Broken links to external files or deleted ranges cause #REF! errors
• Division by zero in early periods before revenue ramps causes #DIV/0! errors
• Circular reference warnings if iterative calculation not enabled
• Inconsistent formulas across projection columns (use Ctrl+\ to find differences)
• Mixed absolute/relative references cause incorrect results when copied across periods
• Balance sheet does not balance (Assets ≠ L+E) due to missing linkages
• Cash tie-out fails (CF ending cash ≠ BS cash) due to incorrect cash flow logic
• Net income link fails (IS NI ≠ CF starting NI) due to sign convention errors
• Retained earnings roll-forward incorrect due to missing SBC or dividend adjustments
• Scenario toggle does not update all statements correctly
• Scenario hierarchy violated (Upside not > Base > Downside for key metrics)
• Named ranges not updated with correct inputs
• Units mismatch across tabs
• SEC filing data extraction fails due to missing or malformed filings
• MCP unavailable and web fallback fails to find required data

Examples

Example 1: Populating a 3-statement model for a public company

Using SEC 10-K data via MCP or web extraction, populate a template for a company with $5 in revenue in 2017. The model projects forward with a 80% gross margin assumption. The tool writes formula strings for all projections, links the statements, and runs all integrity checks.

Example 2: Building a startup financial model with scenario analysis

Build a model for a startup with Base, Upside, and Downside scenarios. Include credit metrics. The tool creates a scenario toggle, sensitizes key drivers, and validates that hierarchy holds (Upside > Base > Downside for NI, EBITDA, FCF, margins).

Example 3: Validating an existing model

Run all audit checks on an existing model. The tool checks formula integrity, cross-statement links, and scenario hierarchy. It flags any #REF!, #DIV/0!, #VALUE!, or #NAME? errors and documents them for resolution.

Cell Formatting Conventions

The tool uses a professional blue/grey palette unless the template specifies otherwise:

• Dark blue #1F4E79 for headers
• Light blue #D9E1F2 for column headers
• Light grey #F2F2F2 for input cells
• Medium blue #BDD7EE for check cells

Font color signals what a cell is:

• Blue font for input cells
• Black font for formula cells
• Green font for cross-tab links

Fill color signals where you are:

• Header cells
• Data cells
• Check cells

The tool also uses specific color codes for conditional formatting or status indicators. For example, #0000FF is used for blue font on input cells, and #008000 is used for green font on cross-tab links.

Data Sourcing

When the template requires SEC filings data, the tool can extract from SEC filings via MCP or web fallback. Prefer structured financial-data MCP; otherwise fall back to web_search/web_extract against SEC EDGAR at https://www.sec.gov/cgi-bin/browse-edgar, company IR pages, or user-provided data.

Never fabricate data. If a value cannot be sourced, flag it as [UNSOUNDED] and surface it to the user.

Circular Reference Handling

Enable iterative calculation in Excel (File → Options → Formulas → Enable iterative calculation, max iterations 100, max change 0.001) for circular reference handling (interest expense circularity). Add a circuit breaker toggle in the Assumptions tab for circular reference handling.

Recalculation

Before delivery, recalculate the model using the provided script:

python /path/to/excel-author/scripts/recalc.py ./out/model.xlsx

Workflow Summary

1. Analyze template structure and confirm with user
2. Populate historical data and confirm with user
3. Build Income Statement projections and confirm with user
4. Build Balance Sheet projections and confirm with user
5. Build Cash Flow Statement projections and confirm with user
6. Implement scenario analysis (if applicable)
7. Run cross-statement integrity checks
8. Run comprehensive audit checks
9. Finalize model and recalculate

Each step requires user confirmation before proceeding to the next. This iterative approach catches errors early and ensures the model meets requirements.