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

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:

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

Edge Cases