--- name: variance-analysis description: Perform automated variance analysis comparing actuals to budget and forecast across departments, categories, and cost centers. Use when analyzing monthly P&L variances, identifying spend drivers, generating variance explanations, tracking KPIs, flagging budget overruns, or producing executive variance commentary. Triggers on phrases like "variance report", "budget vs actual", "P&L analysis", "spend variance", "explain variance", "KPI dashboard", "budget overrun", "monthly variance", "financial performance". --- # Variance Analysis & KPI Monitoring Automate financial performance analysis with AI-powered explanations and real-time monitoring. ## Monthly Variance Analysis Workflow ### Automated Analysis Pipeline Trigger at month-end (or continuous for real-time monitoring): 1. **Data Aggregation**: ``` VARIANCE DATA SOURCES: ────────────────────── Actuals: General Ledger (final or preliminary trial balance) Budget: Annual budget (version-controlled) Forecast: Latest rolling forecast Prior Year: Same period actuals (YoY comparison) Prior Month: Sequential comparison (MoM trends) ``` 2. **Variance Calculation**: - **Absolute variance**: Actual — Budget (dollar amount) - **Percentage variance**: (Actual — Budget) / Budget × 100% - **Trend variance**: Month-over-month and quarter-over-quarter - **Year-to-date variance**: Cumulative variance through current month - **Run-rate variance**: Annualized current performance vs. full-year budget 3. **Materiality Filtering**: ``` VARIANCE THRESHOLDS: ──────────────────── Revenue line items: >$50K OR >3% (whichever is lower) COGS line items: >$25K OR >5% OpEx line items: >$15K OR >5% Headcount-related: >$10K OR >5% One-time items: Always flagged regardless of amount Dimensions to analyze: - By department / cost center - By GL category - By business unit / product line - By geographic region ``` 4. **AI-Powered Root Cause Analysis**: - Analyze transaction-level data for flagged variances - Identify specific transactions driving variance - Cross-reference with operational data (headcount changes, pricing changes, volume shifts) - Distinguish between favorable/unfavorable, recurring/one-time - Classify variance drivers: Volume, Price, Mix, Cost, Timing, One-time ### Variance Explanation Generation ``` AUTO-GENERATED VARIANCE COMMENTARY: ═══════════════════════════════════ Revenue — Enterprise Segment Actual: $4.2M | Budget: $3.8M | Variance: +$400K (+10.5%) Explanation: Favorable variance driven by three factors: 1. Accelerated deal closures: 3 enterprise deals totaling $280K closed in Q1 vs. Q2 budget assumption (timing variance) 2. Upsell revenue: $95K from existing customer expansion (CloudPro Inc, DataCorp Ltd) not captured in base budget 3. FX benefit: $25K positive impact from USD strengthening vs. EUR (1.08 vs. 1.12 budget assumption) Assessment: Partially recurring (upsell); timing variance will reverse in Q2. Recommended action: Update Q2 revenue forecast to reflect forward carry. Professional Services — Consulting Actual: $890K | Budget: $780K | Variance: +$110K (+14.1%) Explanation: Higher than expected utilization rates (92% vs. 85% budget) due to strong client demand and reduced PTO in January. Assessment: Recurring if demand persists; monitor consultant burnout risk. Recommended action: Review staffing plan for Q2 capacity. Marketing — Digital Advertising Actual: $340K | Budget: $275K | Variance: +$65K (+23.6%) Explanation: Unfavorable variance due to: 1. CPA increase: Average CPA rose 18% ($45 vs. $38 budget) in Google Ads 2. Campaign expansion: Added 2 new market campaigns ($22K) approved mid-month 3. Seasonal increase: Holiday season bid competition drove up CPMs Assessment: Campaign expansion was approved; CPA increase requires optimization. Recommended action: Review campaign performance; optimize keyword bids. ``` ## Real-Time KPI Dashboard ### KPI Framework ``` CORE FINANCIAL KPIs: ════════════════════ Revenue Metrics: MRR (Monthly Recurring Revenue): $12.4M (+8% MoM) ARR (Annual Recurring Revenue): $148.8M Net Revenue Retention: 112% Gross Revenue Retention: 94% Revenue per Employee: $520K Profitability Metrics: Gross Margin: 78.2% (budget: 77.5%) Operating Margin: 12.1% (budget: 13.0%) EBITDA Margin: 18.4% (budget: 19.2%) Contribution Margin: 62.3% Cash Metrics: Cash Position: $42.1M Monthly Burn Rate: $3.2M Runway: 13.2 months DSO: 43 days (target: 35) DPO: 52 days Cash Conversion Cycle: 38 days Unit Economics: CAC: $4,200 (by channel: Paid Search $5,800 | Organic $800 | Referral $1,200) LTV: $68,000 LTV:CAC Ratio: 16.2:1 CAC Payback Period: 7.2 months Gross Margin per Customer: 78% Growth Metrics: MoM Revenue Growth: 8% QoQ Revenue Growth: 24% YoY Revenue Growth: 42% New Logos (MTD): 37 Pipeline: $28.5M (3.1x coverage) Win Rate: 22% ``` ### KPI Alert Configuration ``` KPI ALERT THRESHOLDS: ══════════════════════ CRITICAL (Immediate notification): Cash runway < 6 months → Notify CFO, CEO, Board DSO > 60 days → Notify AR Manager, CFO Gross margin < 70% → Notify CFO, COO Burn rate > $5M/month → Notify CFO WARNING (Weekly review): MRR growth < 5% MoM → Notify CFO, Head of Sales Churn rate > 3% monthly → Notify CSM, CFO CAC payback > 12 months → Notify CFO, CMO Operating cash flow negative for 2+ months → Notify CFO INFO (Monthly dashboard): All KPIs within normal range Trend analysis and commentary Benchmark comparison ``` ## Variance Trend Analysis ### Pattern Detection ``` VARIANCE TREND ANALYSIS — Q4 2024 ══════════════════════════════════ Recurring Favorable Variances (Consistently under budget): ☑ Travel & Entertainment: Average 12% under budget for 6 months → Recommendation: Reduce budget assumption or reallocate Recurring Unfavorable Variances (Consistently over budget): ☒ Cloud Infrastructure: Average 8% over budget for 4 months → Root cause: Auto-scaling costs during peak traffic not captured in budget → Recommendation: Implement cost caps; update cloud budget model ☒ Recruiting costs: Average 15% over budget for 8 months → Root cause: Higher-than-expected agency fees for technical roles → Recommendation: Reduce agency dependency; update budget assumptions Seasonal Patterns: ☑ Marketing spend peaks in Q4 (holiday campaigns) ☑ Payroll-related costs spike in January (bonus, raise cycle) ☑ Travel lowest in December (holiday freeze) Anomalous Variances (One-time): ☒ Q4 legal expense +$180K (patent litigation — non-recurring) ☑ Q4 revenue +$520K (accelerated deal close — timing shift) ``` ## Rolling Forecast Integration ### Monthly Forecast Refresh Process ``` ROLLING FORECAST UPDATE — January 2025 ═══════════════════════════════════════ Step 1: Actuals Integration - Load December 2024 actual results - Validate completeness (all entities, all accounts) - Flag any accounts with unusual activity Step 2: Variance Analysis - Actual vs. prior forecast variance by line item - Identify systematic forecast errors (consistently over/under) - Document forecast accuracy metrics by category Step 3: Assumption Updates - Revenue growth rate: 8.2% → 7.9% (market softening) - Customer churn: 2.8% → 3.1% (Q4 increase observed) - Pricing: Maintain current (no planned changes) - Headcount: +12 hires (approved), -3 attrition (observed) - FX rate: USD/EUR 1.08 → 1.10 (market update) Step 4: Model Recalculation - Re-run forecast with updated assumptions - Generate updated 12-month rolling projection - Run scenario analysis (base, upside, downside) Step 5: Distribution - Updated forecast to CFO (Day 1) - Department-level views to VPs (Day 3) - Board summary (Day 5) - Version control: Forecast v2025.01 ``` ## Output ### Executive Variance Report ``` MONTHLY VARIANCE SUMMARY — December 2024 ══════════════════════════════════════════ Revenue: $42.1M (+$1.8M vs budget, +4.5%) ✓ Enterprise: +$1.2M (deal acceleration) ✓ SMB: +$0.5M (seasonal uptick) ☐ International: +$0.1M (FX impact) Gross Profit: $32.9M (78.1% margin, +0.4pp vs budget) ✓ Volume-driven margin improvement Operating Expenses: $28.4M (+$0.9M vs budget, +3.3%) ☒ Technology: +$420K (cloud cost overrun) ☒ People: +$310K (accelerated hires) ✓ G&A: -$180K (travel below budget) ✓ Marketing: -$40K (campaign optimization) Operating Income: $4.5M (10.7% margin, -0.3pp vs budget) Net: Favorable revenue partially offset by OpEx increase EBITDA: $7.8M (18.5% margin, -0.2pp vs budget) Cash Position: $42.1M (Runway: 13.2 months) Operating cash flow: -$2.1M (seasonal — annual bonuses) Capex: $1.2M (data center expansion) KEY ACTIONS: 1. Address cloud cost overrun — implement FinOps controls by Feb 15 2. Update Q1 revenue forecast — incorporate deal timing shifts 3. Review headcount plan — align with actual ramp pace 4. Monitor Q1 churn trend — follow up on Q4 increase ``` ## Integration Points - ERP/GL (NetSuite, SAP, Oracle): Actual financial data - Planning tools (Adaptive Insights, Anaplan, Vena): Budget and forecast models - BI platforms (Tableau, Power BI, Looker): KPI dashboards and visualization - CRM (Salesforce): Pipeline and revenue pipeline data - HRIS (Workday): Headcount and compensation data - Expense systems (Expensify, Concur): Detailed spend data - Data warehouses (Snowflake, BigQuery): Consolidated data models - Alerting platforms (Slack, PagerDuty): KPI threshold notifications ## Edge Cases - **Restatements**: When prior periods are restated, update all comparative variances; flag restated accounts - **Foreign exchange**: Separate operational variance from FX impact; report both local currency and translated variance - **M&A activity**: Segment acquired company results; pro-forma variance analysis; integration cost tracking - **One-time items**: Clearly label and exclude from recurring variance trends; track separately - **Hypergrowth**: Adjust materiality thresholds for rapidly scaling businesses (percentage-based preferred) - **Seasonal businesses**: Use same-period prior year as primary benchmark; smooth with quarterly views - **Zero-budget accounts**: New cost centers or initiatives without baseline — compare to plan/forecast only - **Intercompany eliminations**: Ensure variance analysis on consolidated basis matches entity-level detail