Circulos AI

Finance AI Skill

Financial Modeling

Build integrated three-statement financial models with income statement, balance sheet, and cash flow projections. Use when creating financial models for fundraising, M&A analysis, strategic planning, valuation, scenario analysis, or investment decision-making. Triggers on phrases like "financial model", "three-statement model", "integrated model", "build model", "pro forma", "DCF model", "valuation model", "model assumptions", "base case model".

Financial Modeling

Build rigorous, integrated financial models for strategic decision-making, valuation, and scenario analysis.

Workflow

Model Development Process

Trigger when a new modeling engagement begins (fundraising, M&A, strategic planning, valuation):

  1. Define scope and purpose: Use case, time horizon (3/5/10-year), required outputs (IRR, NPV, equity value, cash projections), audience sophistication.
  2. Gather historical data: 3–5 years of financials, operational metrics, capital structure, working capital trends, tax history.
  3. Build income statement: Revenue by product/segment, price × volume, COGS, operating expenses (fixed vs. variable), non-operating items.
  4. Build balance sheet: Assets (cash, AR, inventory, FA), liabilities (AP, accruals, debt, deferred revenue), equity roll-forward.
  5. Build cash flow: Operating (indirect method), investing (capex, M&A), financing (debt, equity, dividends); cash tie-out.
  6. Link and balance: Verify IS → BS equity, CF → BS cash, BS balances (A = L + E), cross-check formulas, stress-test.
  7. Sensitivity/scenario: Data tables, scenario switch, tornado charts, Monte Carlo if applicable.
  8. Validate and audit: Balance checks, formula audit, extreme value testing, peer review, documentation.

Model Architecture Standards

FINANCIAL MODEL ARCHITECTURE STANDARDS
========================================

Color Coding (Industry Standard):
  Blue text:       Input cells (user-modifiable assumptions)
  Black text:      Formula/calculated cells (protected)
  Green text:      Output cells (results, read-only)
  Red text:        Error flags / balance checks
  Purple text:     Links to other worksheets / workbooks

Sheet Structure:
  Sheet 1: COVER — Model name, date, version, author, key outputs summary
  Sheet 2: ASSUMPTIONS — All input assumptions organized by category
  Sheet 3: INCOME STATEMENT — Detailed P&L build (monthly for Year 1, annual thereafter)
  Sheet 4: BALANCE SHEET — Full BS with roll-forwards
  Sheet 5: CASH FLOW — Operating, investing, financing sections
  Sheet 6: WORKING CAPITAL — DSO, DIO, DPO calculations and schedules
  Sheet 7: FIXED ASSETS — FA register, depreciation schedule, capex plan
  Sheet 8: DEBT SCHEDULE — All debt instruments with amortization
  Sheet 9: EQUITY — Cap table, raises, buybacks, dividends
  Sheet 10: SCENARIOS — Base/upside/downside with toggle switch
  Sheet 11: SENSITIVITY — Data tables, tornado charts, Monte Carlo
  Sheet 12: VALUATION — DCF, comparables, precedent transactions
  Sheet 13: CHECKS — Balance checks, formula validation, error tests

Tab Order:
  Summary → Assumptions → Detailed Builds → Supporting Schedules → Outputs
  Never place inputs in calculation sheets (clean separation)

Navigation:
  Hyperlinked table of contents on Cover sheet
  "Back to Summary" button on every sheet
  Conditional formatting for balance checks (green = balanced, red = error)

Protection:
  Input cells: Unlocked (blue)
  Formula cells: Locked and protected (password-protected if shared)
  Sheets: Protected to prevent accidental formula deletion
  Workbook: Structure protected to prevent sheet deletion/renaming

Revenue Modeling Approaches

REVENUE MODELING BY BUSINESS TYPE
===================================

SaaS Revenue Model:
  Beginning ARR
  + New Logo ARR (Pipeline × Win Rate × ACV × Ramp Factor)
  + Expansion ARR (Existing ARR × Net Expansion Rate)
  − Gross Churn ARR (Existing ARR × Gross Churn Rate)
  = Ending ARR
  Monthly Revenue = Ending ARR / 12

  Assumptions to model:
    - Pipeline coverage ratio: 3–4x quarterly quota
    - Win rate by stage: Discovery 10%, Demo 30%, Proposal 60%, Negotiation 85%
    - Sales cycle: 3–6 months (SMB), 6–12 months (Enterprise)
    - Ramp factor: New reps at 50% productivity Month 1–3, 75% Month 4–6, 100% Month 7+
    - Gross churn: 5–10% annual (B2B SaaS)
    - Net expansion: 10–30% annual (land-and-expand)

Transaction Revenue Model:
  Revenue = Units × Price × (1 − Discount Rate)
  Units = Leads × Conversion Rate × (1 + Repeat Rate)
  Price = List Price × (1 − Average Discount %)

  Assumptions:
    - Conversion rate by channel: Organic 3–5%, Paid 1–3%, Referral 5–8%
    - Average order value: $50–$5,000 (varies by product)
    - Repeat purchase rate: 20–40% within 12 months
    - Seasonality: Monthly indices (e.g., Dec = 2.5× baseline for retail)
    - Discount rate: 5–20% (trade promotions, volume discounts)

Services Revenue Model:
  Revenue = Billable Hours × Utilization Rate × Blended Rate
  Billable Hours = FTE Count × Available Hours × Target Utilization
  Available Hours = 2,080 annual − PTO − Holidays − Training ≈ 1,700 hours

  Assumptions:
    - Utilization rate: 70–85% (consultants), 60–75% (technical staff)
    - Blended rate: $150–$500/hour (by consultant level and specialty)
    - Project margin: 25–45% gross
    - Sell-through: Committed revenue / available capacity (target > 1.2×)
    - Ramp: New hires at 40% Month 1, 60% Month 2, 80% Month 3, 100% Month 4

Three-Statement Integration

Balance Sheet Build

BALANCE SHEET MODELING DETAIL
===============================

Assets:
  Cash: From cash flow statement (ending balance)
  Accounts Receivable: Revenue × DSO / 365
    - DSO assumption: 30–60 days (varies by industry and customer mix)
    - Trend: DSO may improve with collections process or deteriorate with economic conditions
  Inventory: COGS × DIO / 365
    - DIO assumption: 15–90 days (varies by product type)
    - Safety stock: 10–20% above forecasted demand
  Prepaid Expenses: 1–2 months of operating expenses (insurance, rent)
  Fixed Assets (Net):
    Opening Net FA + Capex − Depreciation − Disposals = Closing Net FA
    Depreciation: Straight-line over useful life (3–7 years for equipment, 15–39 years for buildings)
    Capex: % of revenue (2–10% for SaaS, 5–15% for manufacturing)
  Deferred Tax Assets: Net operating loss carryforwards × tax rate
  Other Assets: Deposits, lease right-of-use assets (ASC 842)

Liabilities:
  Accounts Payable: COGS + Opex × DPO / 365
    - DPO assumption: 30–60 days (varies by bargaining power)
    - Trend: DPO may extend with vendor negotiations
  Accrued Expenses: 1–2 months of operating expenses (payroll, benefits, bonuses)
  Deferred Revenue: Advance payments × months remaining / total contract months
    - SaaS: 12 months of pre-collected annual contracts
  Debt (Current): Principal payments due within 12 months
  Debt (Long-term): Remaining principal after current portion
  Lease Liabilities: Present value of remaining lease payments (ASC 842)
  Other Liabilities: Tax payables, employee benefits obligations

Equity:
  Common Stock: Par value × shares outstanding
  Additional Paid-in Capital: Cumulative equity raises − par value
  Retained Earnings: Opening RE + Net Income − Dividends
  Treasury Stock: Cost of share buybacks (negative equity)
  Accumulated Other Comprehensive Income: FX translation adjustments, unrealized gains/losses

Cash Flow Modeling

CASH FLOW STATEMENT MODELING
==============================

Operating Activities (Indirect Method):
  Net Income                                $XX.XM
  + Depreciation & Amortization             $X.XM
  + Stock-based compensation                $X.XM
  + Deferred taxes                          $X.XM
  − Changes in working capital:
      Accounts receivable                   ($X.XM)  (increase = cash outflow)
      Inventory                             ($X.XM)  (increase = cash outflow)
      Accounts payable                      $X.XM    (increase = cash inflow)
      Accrued expenses                      $X.XM    (increase = cash inflow)
      Deferred revenue                      $X.XM    (increase = cash inflow)
  = Cash from Operations                    $XX.XM

Investing Activities:
  Capital expenditures                      ($X.XM)
  Acquisitions                              ($XX.XM)
  Purchase of investments                   ($X.XM)
  Sale of investments                       $X.XM
  Proceeds from asset sales                 $X.XM
  = Cash from Investing                     ($XX.XM)

Financing Activities:
  Proceeds from debt                        $XX.XM
  Debt repayments                           ($X.XM)
  Proceeds from equity                      $XX.XM
  Share buybacks                            ($X.XM)
  Dividends paid                            ($X.XM)
  Debt issuance costs                       ($X.XM)
  = Cash from Financing                     ($XX.XM)

Net Change in Cash                          $X.XM
Cash, Beginning of Period                   $XX.XM
Cash, End of Period                         $XX.XM

Key tie-outs:
  - End of period cash MUST equal cash on balance sheet
  - Net income MUST equal bottom line of income statement
  - Capex MUST equal change in gross fixed assets + disposals
  - Deferred revenue change MUST reconcile to subscription model

Scenario and Sensitivity Analysis

Multi-Scenario Framework

SCENARIO MODELING FRAMEWORK
=============================

Scenario Toggle (single cell switch):
  0 = Base Case
  1 = Upside Case
  2 = Downside Case

Revenue Assumptions by Scenario:
  Growth Rate:    Base 20%  |  Upside 28%  |  Downside 12%
  Gross Margin:   Base 67%  |  Upside 70%  |  Downside 64%
  Operating Margin: Base 10% | Upside 14%  | Downside 6%

Expense Assumptions by Scenario:
  S&M as % of Revenue:  Base 30%  |  Upside 25%  |  Downside 35%
  R&D as % of Revenue:  Base 15%  |  Upside 14%  |  Downside 16%
  G&A as % of Revenue:  Base 12%  |  Upside 10%  |  Downside 14%

Working Capital by Scenario:
  DSO:        Base 45 days  |  Upside 38 days  |  Downside 55 days
  DIO:        Base 30 days  |  Upside 25 days  |  Downside 40 days
  DPO:        Base 45 days  |  Upside 50 days  |  Downside 35 days

Sensitivity Data Tables:
  Revenue CAGR × Gross Margin:
                65%     67%     69%
      15%     $XM     $XM     $XM   (Equity Value)
      20%     $XM     $XM     $XM   (Base case = 20% × 67%)
      25%     $XM     $XM     $XM

  WACC × Terminal Growth:
                2.0%    2.5%    3.0%
      9.0%    $XM     $XM     $XM   (Enterprise Value)
      10.5%   $XM     $XM     $XM   (Base case = 10.5% × 2.5%)
      12.0%   $XM     $XM     $XM

Edge Cases

Integration Points

Disclaimer: All rights reserved by Circulos AI. These skills are specifically designed for Claude Code, Claude Cowork, Codex, and OpenClaw. When using or referencing any skill, please provide proper attribution to Circulos AI.