Finance AI Skill

Forecast Reconciliation

Reconcile forecasted financial results to actual outcomes, identify variances, analyze root causes, and update forecasting models. Use when performing forecast-to-actual analysis, post-mortem on forecast accuracy, adjusting forecasting assumptions, identify...

Forecast Reconciliation

Systematically compare forecasted results to actual outcomes, diagnose discrepancies, and continuously improve forecasting accuracy.

Workflow

1. Data Collection & Preparation

FORECAST RECONCILIATION DATA REQUIREMENTS
═══════════════════════════════════════

ACTUALS:
  → Monthly income statement (detailed)
  → Cash flow statement
  → Balance sheet
  → Operational metrics (units, customers, headcount)
  → Segment/product line results
  → One-time items flagged

FORECAST (prior):
  → Original forecast as issued (locked version)
  → Any interim forecast revisions
  → Assumptions documented at time of forecast
  → Scenario variants (base, upside, downside)

CONTEXT:
  → Key events during period (launches, losses, disruptions)
  → Market conditions and competitor actions
  → Macro-economic indicators
  → Internal changes (hiring, restructuring, system changes)

2. Variance Calculation

FORECAST vs ACTUAL RECONCILIATION — Q3 2024
═══════════════════════════════════════

                     Forecast    Actual     Variance   % Var   Material?
Revenue              $42.5M     $45.2M      +$2.7M    +6.3%    ✓ YES
  Product A          $25.0M     $27.1M      +$2.1M    +8.4%    ✓
  Product B          $12.0M     $12.8M      +$0.8M    +6.7%    ✓
  Services           $5.5M      $5.3M       -$0.2M    -3.6%    —

COGS                 $15.3M     $15.8M      +$0.5M    +3.3%    —
Gross Profit         $27.2M     $29.4M      +$2.2M    +8.1%    ✓

Operating Expenses
  R&D                 $4.0M      $4.2M      +$0.2M    +5.0%    —
  Sales & Marketing   $9.5M     $10.1M      +$0.6M    +6.3%    ✓
  G&A                 $3.5M      $3.4M      -$0.1M    -2.9%    —
Total OpEx           $17.0M     $17.7M      +$0.7M    +4.1%    —

Operating Income     $10.2M     $11.7M      +$1.5M   +14.7%    ✓ YES
EBITDA               $12.5M     $14.0M      +$1.5M   +12.0%    ✓
Net Income            $7.8M      $9.0M      +$1.2M   +15.4%    ✓

Cash Flow
  Operating CF        $8.0M      $9.5M      +$1.5M   +18.8%    ✓
  CapEx               $1.5M      $1.8M      +$0.3M   +20.0%    ✓
  Free Cash Flow      $6.5M      $7.7M      +$1.2M   +18.5%    ✓

Working Capital
  AR                  $12.0M     $14.5M      +$2.5M   +20.8%    🚩
  Inventory            $5.0M      $5.2M      +$0.2M    +4.0%    —
  AP                  $10.0M     $11.8M      +$1.8M   +18.0%    🚩

MATERIAL VARIANCES (>$0.5M or >5%):
  ✓ Revenue +$2.7M (+6.3%)
  ✓ Operating Income +$1.5M (+14.7%)
  🚩 AR +$2.5M (+20.8%) — working capital concern
  🚩 AP +$1.8M (+18.0%) — timing or process issue

3. Root Cause Analysis

VARIANCE ROOT CAUSE — Structured Analysis
═══════════════════════════════════════

REVENUE VARIANCE: +$2.7M (+6.3%)
─────────────────────────────────────

Product A: +$2.1M (+8.4%)
  DRIVER 1: New feature launch (Oct 15) drove 15% of incremental revenue
    → Forecast assumed gradual adoption; actual adoption was accelerated
    → Customer pipeline was stronger than pipeline conversion rate implied
  
  DRIVER 2: Pricing increase effective August (not fully reflected in forecast)
    → 5% price increase on enterprise tier, implemented August 1
    → Forecast incorporated only 2% of price impact (conservative assumption)
    → Full impact: ~$0.8M in Q3
  
  DRIVER 3: Win of 2 enterprise accounts not in pipeline at forecast date
    → Acme Corp ($300K ARR) — closed Oct 5
    → Globex Inc ($450K ARR) — closed Oct 20
    → These were "dark pipeline" (sales knew, CRM not updated)

Product B: +$0.8M (+6.7%)
  DRIVER: Stronger international sales (EMEA region +18%)
    → Currency tailwind: EUR strengthened 4% vs USD
    → New channel partner in Germany drove $350K incremental
  
Services: -$0.2M (-3.6%)
  DRIVER: Implementation projects delayed to Q4
    → 3 projects pushed due to customer readiness
    → Revenue will be recognized in Q4 instead of Q3
    → Not a true miss — timing shift only

ASSESSMENT: Revenue beat was genuine and recurring. Pipeline visibility
improvement and price increase were real. Recommend updating forecast
assumptions on price realization and pipeline conversion.

4. Forecast Assumption Review

ASSUMPTION ACCURACY REVIEW
═══════════════════════════════════════

Assumption              Forecast    Actual     Accuracy    Adjustment Needed?
─────────────────────────────────────────────────────────────────────────────
Revenue growth rate       18.0%     21.5%       LOW        ↑ Increase to 20%
Customer churn            5.2%      4.1%        HIGH       ↓ Decrease to 4.5%
ARPU increase             3.0%      5.5%        LOW        ↑ Increase to 5.0%
CAC                       $2,400    $2,200      HIGH       ↓ Decrease to $2,250
Pipeline conversion       22%       28%         LOW        ↑ Increase to 26%
Gross margin              62.0%     65.0%       MOD        ↑ Increase to 64.0%
Headcount growth          15        12           HIGH       ↓ Reduce to 13/mo
R&D as % revenue          9.4%      9.3%        HIGH       — Keep at 9.4%
Marketing efficiency      3.2x      2.8x        LOW        ↓ Decrease to 3.0x

SYSTEMATIC BIAS DETECTED:
  → Revenue assumptions consistently too conservative (3 consecutive quarters)
  → Pipeline conversion rate understated by 4-6 percentage points
  → CAC overestimated by ~8%
  → Conclusion: Company execution exceeds model assumptions
  → Action: Recalibrate base assumptions upward by 3-5%

5. Forecast Model Calibration

FORECAST MODEL UPDATE PROCESS
═══════════════════════════════════════

Step 1: Update assumptions based on latest actuals
  → Revenue growth: 18.0% → 20.0% (reflect accelerating execution)
  → Churn: 5.2% → 4.5% (improving retention)
  → ARPU growth: 3.0% → 5.0% (price increases proving successful)
  
Step 2: Recalibrate conversion factors
  → Pipeline-to-revenue: 22% → 26%
  → Lead-to-opportunity: 15% → 18%
  → Sales cycle length: 90 → 82 days

Step 3: Update operational assumptions
  → Hiring pace: 15/mo → 13/mo (tighter labor market)
  → Productivity per FTE: $180K → $200K (improving with tools/training)
  → Capex as % revenue: 3.5% → 4.0% (infrastructure investment)

Step 4: Re-run model with updated assumptions
  → Generate new base case, upside, downside scenarios
  → Re-calculate key metrics (FCF, runway, valuation)
  → Update sensitivity analysis

Step 5: Document changes
  → What changed and why
  → Impact of each assumption change on key outputs
  → Confidence level in updated forecast

Step 6: Communicate
  → Share updated forecast with stakeholders
  → Explain key assumption changes
  → Update board materials and investor communications

6. Forecast Accuracy Tracking

FORECAST ACCURACY DASHBOARD
═══════════════════════════════════════

Quarter    Revenue Var   EBITDA Var   FCF Var    Overall Score
─────────────────────────────────────────────────────────────
Q1 2023    -2.1%        -5.3%        -8.2%       7.2/10
Q2 2023    +1.5%        -3.1%        -4.5%       8.0/10
Q3 2023    -0.8%        +2.1%        -1.2%       9.0/10
Q4 2023    +3.2%        +4.5%        +3.8%       8.5/10
Q1 2024    -1.0%        +1.2%        +0.5%       9.2/10
Q2 2024    +2.1%        +3.0%        +2.5%       8.8/10
Q3 2024    +6.3%        +12.0%       +18.5%      7.5/10

TREND ANALYSIS:
  → Revenue forecast accuracy: Improving (avg absolute error: 3.1% → 3.5%)
    ⚠ Q3 over-optimistic miss in accuracy despite beat
  → EBITDA accuracy: Volatile; model not capturing OpEx dynamics well
  → FCF accuracy: Weakening; working capital forecasts need improvement
  → Consistent direction: Forecast has been conservative for 6 quarters

IMPROVEMENT AREAS:
  1. Revenue: Model is conservative; adjust growth assumptions upward
  2. EBITDA: Better model for discretionary spend timing
  3. FCF: Improve working capital forecasting (DSO trending up)
  4. Process: Require sales to update CRM before forecast cycle

7. Forecast Quality Scorecard

FORECAST QUALITY ASSESSMENT
═══════════════════════════════════════

Dimension              Score    Weight    Weighted
──────────────────────────────────────────────────
Accuracy (vs actual)     7.5      30%       2.25
Timeliness               9.0      20%       1.80
Completeness             8.5      15%       1.28
Assumption quality       7.0      15%       1.05
Documentation            8.0      10%       0.80
Stakeholder alignment    7.5      10%       0.75
──────────────────────────────────────────────────
TOTAL SCORE:                          7.93 / 10

RATING: GOOD (with room for improvement)

ACTION ITEMS:
  1. Update revenue growth assumptions to reflect actual execution (↑2%)
  2. Improve working capital forecast model — DSO increasing
  3. Add pipeline update requirement 5 days before forecast close
  4. Include sensitivity ranges on all key outputs
  5. Document all assumption changes in forecast change log

Edge Cases

Highly volatile businesses (biotech, crypto, commodities): Use wider confidence intervals; scenario-based rather than point forecasts; focus on catalysts not trends
New products/markets: Separate forecast with wider ranges; use analog benchmarking; update assumptions more frequently
Seasonal businesses: Model seasonality explicitly; compare same-quarter YoY; use moving averages to smooth
Post-M&A integration: Separate legacy forecasts; model synergy realization timeline; track integration progress separately
Force majeure events (pandemic, natural disaster): Switch to scenario-based forecasting; increase update frequency; stress-test downside

Integration Points

ERP systems: Actual financial data extraction
CRM: Pipeline data, forecast inputs from sales
Planning platforms: Adaptive Insights, Anaplan, Vena (forecast models)
BI tools: Tableau, Power BI, Looker (variance dashboards)
Spreadsheets: Excel (detailed variance analysis workbooks)
Communication: Email, Slack (forecast alerts and updates)

Output

Forecast Reconciliation Report

FORECAST RECONCILIATION REPORT — Q3 2024
═══════════════════════════════════════

EXECUTIVE SUMMARY:
  Revenue beat forecast by 6.3% (+$2.7M), driven by stronger Product A
  execution, successful price increases, and unexpected enterprise wins.
  However, working capital forecasts were significantly off — AR and AP
  both ran 18-21% above forecast, indicating process gaps.

KEY FINDINGS:
  1. Revenue model is systematically conservative by 3-5%
  2. Pipeline conversion rate underestimated (28% actual vs 22% forecast)
  3. Working capital forecasting needs recalibration
  4. Operating expense forecasting accurate within 4%

RECOMMENDATIONS:
  1. Increase revenue growth assumption from 18% to 20%
  2. Recalibrate pipeline conversion rate to 26%
  3. Implement weekly DSO/AP tracking for better WC forecasting
  4. Require CRM pipeline updates before forecast close

UPDATED FORECAST (Q4 2024):
  Revenue: $48.0M (was $46.5M)
  EBITDA: $15.5M (was $14.8M)
  FCF: $8.2M (was $7.5M)