--- name: forecast-reconciliation description: 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, identifying systematic forecast bias, or improving prediction models. Triggers on phrases like "forecast reconciliation", "forecast vs actual", "forecast variance", "prediction accuracy", "model calibration", "forecast adjustment", "forecasting model update", "budget to forecast to actual". --- # 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) ```