Finance AI Skill

Scenario Modeling

Build and compare financial scenarios for strategic planning including rolling forecasts, what-if analysis, sensitivity testing, Monte Carlo simulation, and multi-year strategic planning. Use when creating best/worst/base case scenarios, modeling financial impacts of strategic decisions, stress-testing assumptions, preparing board scenarios, or updating rolling forecasts. Triggers on phrases like "scenario analysis", "what-if", "sensitivity analysis", "rolling forecast", "financial model", "stress test", "best case worst case", "strategic plan", "multi-year forecast", "Monte Carlo".

Scenario Planning & Financial Modeling

Create robust financial scenarios to support strategic decision-making and risk management.

Dynamic Scenario Modeling Framework

Scenario Design Process

Trigger when strategic decision requires financial impact analysis:

Base Case Establishment:

   BASE CASE ASSUMPTIONS — 2025
   ────────────────────────────
   Revenue Growth: 15% YoY
   Gross Margin: 78%
   CAC: $4,200 (flat)
   Churn Rate: 2.5% monthly
   Headcount: 520 (+8% YoY)
   Operating Margin: 12%
   Cash Position: $42M
   Cost of Capital: 8%
   FX Rate (USD/EUR): 1.08

Key Driver Identification:

   SCENARIO DRIVERS:
   ────────────────
   Revenue Drivers:
     - New logo acquisition rate
     - Expansion revenue (upsell/cross-sell)
     - Customer churn/retention
     - Pricing changes
     - Market growth rate
     - Competitive displacement
   
   Cost Drivers:
     - Headcount growth rate
     - Compensation inflation
     - Cloud infrastructure cost growth
     - Marketing efficiency (CAC trend)
     - Vendor cost inflation
     - FX impact (multi-currency operations)
   
   External Factors:
     - Economic growth / recession risk
     - Interest rate environment
     - Regulatory changes
     - Market disruption / competition
     - Supply chain events

Scenario Definition:

   THREE-SCENARIO FRAMEWORK:
   ─────────────────────────

   BASE CASE (50% probability):
     Revenue growth: 15%
     Gross margin: 78%
     Operating margin: 12%
     Headcount growth: 8%
     Net income: $21.5M
   
   UPSCIDE CASE (25% probability):
     Revenue growth: 22%
     Gross margin: 80%
     Operating margin: 16%
     Headcount growth: 12%
     Net income: $32.8M
   
   DOWNSIDE CASE (25% probability):
     Revenue growth: 8%
     Gross margin: 74%
     Operating margin: 6%
     Headcount growth: 4%
     Net income: $9.2M

Sensitivity Analysis

SENSITIVITY TABLE — Net Income Impact
══════════════════════════════════════

Variable Changed        -10%      Base      +10%
────────────────────────────────────────────────
Revenue Growth          $17.2M   $21.5M   $25.8M
  (Revenue driver)      (-$4.3M)        (+$4.3M)

Gross Margin            $15.8M   $21.5M   $27.2M
  (Margin driver)       (-$5.7M)        (+$5.7M)

CAC                     $22.8M   $21.5M   $20.2M
  (Cost driver)         (+$1.3M)        (-$1.3M)

Churn Rate              $23.5M   $21.5M   $19.5M
  (Retention driver)    (+$2.0M)        (-$2.0M)

Headcount               $23.0M   $21.5M   $20.0M
  (Cost driver)         (+$1.5M)        (-$1.5M)

KEY INSIGHT: Gross margin has highest sensitivity (±$5.7M).
             Churn rate second highest (±$2.0M).
             Priority: Protect margins and reduce churn.

TORNADO CHART (visual representation):
  Gross Margin:    ████████████████████████ $5.7M
  Churn Rate:      ████████████████         $2.0M
  Revenue Growth:  ████████████████         $4.3M
  Headcount:       ████████████             $1.5M
  CAC:             ██████████               $1.3M

Monte Carlo Simulation

MONTE CARLO SIMULATION — 2025 Net Income
═════════════════════════════════════════

Configuration:
  Iterations: 10,000
  Input distributions:
    Revenue Growth: Normal(15%, σ=4%)
    Gross Margin: Normal(78%, σ=2%)
    Churn: Normal(2.5%, σ=0.5%)
    CAC: Normal($4,200, σ=$600)
    Headcount: Normal(520, σ=20)

Results:
  Mean Net Income: $21.3M
  Median Net Income: $21.5M
  Standard Deviation: $4.2M
  
  Confidence Intervals:
    10th percentile: $13.8M
    25th percentile: $18.2M
    50th percentile: $21.5M
    75th percentile: $24.8M
    90th percentile: $28.1M
  
  Probability of Positive Net Income: 97.2%
  Probability of >$25M Net Income: 32.4%
  Probability of <$10M Net Income: 2.1%

Distribution:
  $5M   | █
  $10M  | ██
  $15M  | ███████
  $20M  | ████████████
  $25M  | ██████████
  $30M  | ████
  $35M  | ██
  $40M  | █

KEY INSIGHT: 80% probability of net income between $18.2M and $28.1M.
             Downside protection strong (<3% probability of <$10M).

Rolling Forecast Updates

Monthly Forecast Refresh

ROLLING FORECAST — Monthly Update Process
══════════════════════════════════════════

UPDATE CYCLE: First 5 business days of each month

Step 1: Actuals Integration (Day 1)
  - Load prior month actual results
  - Validate completeness and accuracy
  - Flag unusual items requiring investigation

Step 2: Variance Review (Day 1-2)
  - Actual vs. prior forecast variance
  - Classify: structural change vs. timing shift
  - Document explanations for material variances (>5% or >$25K)

Step 3: Assumption Refresh (Day 2-3)
  - Revenue: Update pipeline conversion rates, deal velocity, win rates
  - Costs: Update headcount plan, inflation assumptions, vendor contracts
  - External: Update market conditions, economic indicators, FX rates
  - Strategic: Incorporate new initiatives, product launches, M&A

Step 4: Model Recalculation (Day 3-4)
  - Re-run forecast with updated inputs
  - Validate model integrity (balance check, ratio reasonableness)
  - Generate updated 12-month rolling projection

Step 5: Scenario Update (Day 4)
  - Refresh upside/downside scenarios
  - Update probability weights based on latest data
  - Stress test with extreme scenarios

Step 6: Distribution & Commentary (Day 5)
  - CFO review: Day 5, 9:00 AM
  - VP distribution: Day 5, 2:00 PM
  - Department-level views: Day 5, 4:00 PM
  - Version: Forecast v2025.[MM]

Forecast Accuracy Tracking

FORECAST ACCURACY METRICS — Q4 2024
════════════════════════════════════

Revenue Forecast Accuracy:
  Month 1 (current):   ±2.1%      ✓ Within target (±5%)
  Month 3:             ±4.8%      ✓ Within target (±10%)
  Month 6:             ±8.2%      ✓ Within target (±15%)
  Month 12:            ±14.5%     ✓ Within target (±20%)

OpEx Forecast Accuracy:
  Month 1 (current):   ±1.2%      ✓ Within target (±3%)
  Month 3:             ±3.1%      ✓ Within target (±5%)
  Month 6:             ±5.8%      ✓ Within target (±8%)
  Month 12:            ±9.2%      ✓ Within target (±12%)

Systematic Bias:
  Revenue: Consistently under-forecast by 1-2% (conservative bias)
  OpEx: Neutral (no systematic bias)
  
Improvement Recommendation:
  - Adjust revenue base case +1.5% to correct conservative bias
  - Maintain current OpEx assumptions
  - Increase forecast refresh frequency for volatile categories

3-Statement Financial Modeling

Model Structure

INTEGRATED 3-STATEMENT MODEL — Structure
═════════════════════════════════════════

INPUT TABS:
  1. Historical Financials (3 years actual)
  2. Assumptions & Drivers
  3. Revenue Build
  4. OpEx Build
  5. Working Capital
  6. Capex & Depreciation
  7. Debt & Equity
  8. Tax
  9. FX Rates

CALCULATION TABS:
  10. Income Statement
  11. Balance Sheet
  12. Cash Flow Statement
  13. Key Ratios & Metrics
  14. Summary Dashboard

VALIDATION TABS:
  15. Balance Check (Assets = Liabilities + Equity)
  16. Cash Tie-Out (BS Cash = CF ending cash)
  17. Ratio Reasonableness
  18. Error Log
  19. Audit Trail

OUTPUT TABS:
  20. Financial Statements (formatted)
  21. KPI Dashboard
  22. Sensitivity Tables
  23. Scenario Comparison
  24. Charts & Visualizations

Model Validation Checklist

MODEL VALIDATION CHECKLIST:
════════════════════════════

Balance Sheet Checks:
  [ ] Total Assets = Total Liabilities + Equity (every period)
  [ ] Retained earnings rolls forward correctly
  [ ] Depreciation expense = change in accumulated depreciation (plus disposals)
  [ ] Working capital changes reasonable vs. revenue growth

Cash Flow Checks:
  [ ] Ending cash (CF) = Cash (BS)
  [ ] Net Income (IS) = Starting point of CF (indirect method)
  [ ] Capex (CF) = Net change in fixed assets (plus disposals/sales)
  [ ] Debt principal payments = reduction in debt balance

Income Statement Checks:
  [ ] Revenue growth rates reasonable vs. historical
  [ ] Gross margin within expected range
  [ ] Expense ratios stable or explainable
  [ ] No hard-coded values in calculation cells (all formula-driven)

Cross-Statement Checks:
  [ ] Interest expense = Average debt balance × interest rate
  [ ] Tax expense = Taxable income × effective tax rate
  [ ] Dividends = Cash distribution specified in assumptions
  [ ] Share count = Beginning + Issuances - Buybacks

Error Checks:
  [ ] No circular references
  [ ] No #DIV/0!, #N/A, #VALUE! errors
  [ ] All hard-coded inputs clearly marked (e.g., blue font)
  [ ] Scenario switches function correctly
  [ ] Sensitivity tables update when assumptions change

Strategic Planning

Long-Range Planning Framework

3-YEAR STRATEGIC FINANCIAL PLAN — 2025-2027
════════════════════════════════════════════

STRATEGIC OBJECTIVES:
  1. Expand into European market (Q2 2025)
  2. Launch product line extension (Q4 2025)
  3. Achieve profitability by Q4 2026
  4. Build pipeline for Series B or profitability by 2027

FINANCIAL PROJECTIONS:
  ┌───────────────────┬─────────┬─────────┬─────────┐
  │ Metric            │ 2025E   │ 2026E   │ 2027E   │
  ├───────────────────┼─────────┼─────────┼─────────┤
  │ Revenue           │ $170M   │ $230M   │ $310M   │
  │ Revenue Growth    │ 22%     │ 35%     │ 35%     │
  │ Gross Margin      │ 77%     │ 79%     │ 81%     │
  │ OpEx              │ $135M   │ $165M   │ $210M   │
  │ OpEx as % Revenue │ 79%     │ 72%     │ 68%     │
  │ Operating Income  │ ($5M)   │ $15M    │ $47M    │
  │ Operating Margin  │ -3%     │ 7%      │ 15%     │
  │ Net Income        │ ($8M)   │ $9M     │ $33M    │
  │ Headcount         │ 560     │ 720     │ 900     │
  │ Cash (end of yr)  │ $32M    │ $40M    │ $68M    │
  └───────────────────┴─────────┴─────────┴─────────┘

KEY INVESTMENTS:
  - European expansion: $8M initial investment (HQ, team, marketing)
  - Product development: $15M over 2 years (R&D headcount, tools)
  - Sales infrastructure: $10M (team, CRM, enablement)
  - Technology: $5M (infrastructure, security, compliance)

FUNDING REQUIREMENTS:
  - Current runway: 13 months (sufficient through mid-2026)
  - Series B timing: H2 2026 (if not profitable by then)
  - Series B size: $50-75M (24-month runway at scale)
  - Target valuation: $800M-$1.2B (based on 2026E metrics)

Output

Scenario Dashboard View

SCENARIO COMPARISON DASHBOARD — 2025
══════════════════════════════════════

                      BASE       UPSCIDE    DOWNSIDE
                      (50%)      (25%)      (25%)
──────────────────────────────────────────────────────
Revenue               $170M      $198M      $148M
Gross Margin          78%        80%        74%
Operating Margin      12%        16%        6%
Net Income            $21.5M     $32.8M     $9.2M
Free Cash Flow        $18.2M     $29.1M     $5.8M
Headcount             520        580        490
Cash (YoE)            $38M       $52M       $22M
Runway                14 months  18 months  9 months

Probability-Weighted Expected Value:
  Expected Revenue:    $176.8M
  Expected Net Income: $22.4M
  Expected Cash (YoE): $40.1M

KEY RISKS:
  1. Revenue miss: 25% chance of <12% growth → extends unprofitability
  2. Customer concentration: Top 5 customers = 28% of revenue
  3. Competitive pressure: 2 new entrants in core market
  4. Key dependency: Single cloud provider (AWS)
  
MITIGATION:
  1. Diversified pipeline: 3.1x coverage across segments
  2. Customer success program: NRR 112%, churn 2.5%
  3. R&D investment: 18% of revenue in innovation
  4. Multi-cloud strategy planned for Q3 2025

Integration Points

Financial planning tools (Anaplan, Adaptive Insights, Vena, Planful): Scenario modeling platform
ERP/GL (NetSuite, SAP): Historical data, actuals integration
CRM (Salesforce): Pipeline data, deal forecasting
HRIS (Workday): Headcount plans, compensation modeling
Data warehouse (Snowflake, BigQuery): Consolidated data for model inputs
BI platforms (Tableau, Power BI, Looker): Visualization and dashboard
Presentation tools (PowerPoint, Google Slides): Board and executive outputs
Version control (Git, SharePoint): Model versioning and audit trail

Edge Cases

Hypergrowth companies: Non-linear growth assumptions; inflection point modeling; burn rate focus
Cyclical industries: Economic cycle modeling; downside protection emphasis
Regulated industries: Compliance cost scenario; regulatory change impact modeling
M&A scenarios: Pro-forma modeling; synergy quantification; integration cost scenarios
Pivot scenarios: Complete business model change; re-forecast from scratch; resource reallocation
Fundraising scenarios: Multiple dilution scenarios; valuation sensitivity; use of funds modeling
Distress scenarios: Liquidity stress test; cost reduction scenarios; survival runway calculation
Multi-currency operations: FX scenario layers; hedging impact modeling; translation vs. transaction
Model handoff: Standardize model structure for team collaboration; document all assumptions
Model decay: Regular review and update; retire stale assumptions; version control discipline

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.