--- name: scenario-modeling description: 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: 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 ``` 2. **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 ``` 3. **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