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-mak...

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