| name | excel-variance-analyzer |
| description | Automate budget vs actual variance analysis in Excel with flagging, commentary, and executive summaries for financial reporting and FP&A teams Activates when you request "excel variance analyzer" functionality.
|
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash |
| version | 1.0.0 |
Excel Variance Analyzer
Automates variance analysis for monthly/quarterly financial reporting and budget reviews.
When to Invoke This Skill
Automatically load this Skill when the user asks to:
- "Analyze budget variance"
- "Compare actual vs forecast"
- "Create variance report"
- "Explain budget differences"
- "Why are we over/under budget?"
- "Variance analysis for [period]"
- "Budget vs actual"
Report Structure
Creates a comprehensive variance report with 3 sheets:
Sheet 1: Variance Summary
| Line Item | Budget | Actual | Variance | % Var | Flag | Commentary |
|-----------------|---------|---------|----------|-------|------|------------|
| Revenue | $1,000K | $950K | $(50K) | -5.0% | ⚠️ | Below plan |
| COGS | $600K | $580K | $(20K) | -3.3% | ✅ | Favorable |
| Gross Profit | $400K | $370K | $(30K) | -7.5% | 🔴 | Investigate|
| Operating Exp | $250K | $280K | $30K | 12.0% | 🔴 | Over budget|
| EBITDA | $150K | $90K | $(60K) | -40.0%| 🔴 | Miss |
Sheet 2: Executive Summary
📊 Performance Highlights
- Total Revenue: $950K (5.0% below budget)
- EBITDA: $90K (40.0% below budget)
- Key Driver: Operating expenses 12% over budget
🔴 Top 5 Unfavorable Variances:
1. EBITDA: $(60K) / -40.0%
2. Revenue: $(50K) / -5.0%
3. Operating Expenses: $30K / 12.0%
4. Gross Profit: $(30K) / -7.5%
5. Marketing: $25K / 25.0%
✅ Top 5 Favorable Variances:
1. COGS: $(20K) / -3.3%
2. Rent: $(5K) / -10.0%
3. Utilities: $(2K) / -8.0%
Sheet 3: Trend Analysis (if multiple periods)
| Line Item | Jan Var% | Feb Var% | Mar Var% | Q1 Var% | Trend |
|-----------|----------|----------|----------|---------|-------|
| Revenue | -3% | -5% | -7% | -5% | ⬇️ |
| COGS | -2% | -4% | -3% | -3% | ➡️ |
Step-by-Step Workflow
1. Load Data
Ask the user for:
- Budget data: Can be Excel file, CSV, or pasted table
- Actual data: Same format as budget
- Period: Month, quarter, YTD
- Threshold settings (or use defaults):
- Percentage threshold: 10% (flag items >10% variance)
- Dollar threshold: $50K (flag items >$50K absolute variance)
- Categories to exclude: (e.g., non-cash items like depreciation)
2. Validate Data
Before analysis, check:
- Budget and actual have matching line items
- All values are numeric
- No missing data for key categories (revenue, expenses, profit)
- Budget data is reasonable (no zeros where there should be values)
3. Calculate Variances
For each line item:
Absolute Variance = Actual - Budget
Percentage Variance = (Actual - Budget) / Budget × 100%
Sign Convention:
- Positive variance for revenue/profit = Favorable (✅)
- Negative variance for revenue/profit = Unfavorable (🔴)
- Positive variance for expenses = Unfavorable (🔴)
- Negative variance for expenses = Favorable (✅)
4. Flag Material Items
Apply flagging rules:
🔴 Red Flag (Critical):
- Revenue/profit >10% below budget
- Expenses >10% over budget
- Absolute variance >$100K
⚠️ Yellow Flag (Warning):
- Revenue/profit 5-10% below budget
- Expenses 5-10% over budget
- Absolute variance $50K-$100K
✅ Green (On Track):
- Variance within ±5%
- Absolute variance <$50K
5. Generate Commentary
For each flagged item, provide automated commentary:
Revenue Below Budget:
Revenue is $50K (5%) below budget. Possible drivers:
- Lower than expected sales volume
- Pricing pressure
- Delayed revenue recognition
- Seasonal factors
Recommend: Review sales pipeline and backlog
Expenses Over Budget:
Operating expenses are $30K (12%) over budget. Breakdown:
- Marketing: $25K over (new campaign)
- Travel: $8K over
- Utilities: $2K under
Recommend: Review Q2 marketing spend and ROI
Favorable Variances:
COGS is $20K (3.3%) under budget. Drivers:
- Lower raw material costs
- Improved operational efficiency
- Volume discounts from suppliers
6. Create Executive Summary
Summarize for leadership:
📊 [Period] Financial Performance
Bottom Line:
- Revenue: [X%] vs budget ([favorable/unfavorable])
- EBITDA: [X%] vs budget ([favorable/unfavorable])
- Net Variance Impact: $[XXX]K
Key Takeaways:
1. [Primary driver of miss/beat]
2. [Secondary factor]
3. [Outlook for next period]
Action Items:
1. [Specific action needed]
2. [Follow-up required]
Example Interaction
User: "Analyze Q1 budget vs actual"
Skill Response:
I'll analyze your Q1 variance. Let me load the data.
Do you have budget and actual data files, or would you like to paste them?
User: *pastes Excel data*
Perfect! I see data for Q1 2025. Setting analysis parameters:
- Period: Q1 2025 (Jan-Mar)
- Line items: 15 categories detected
- Materiality threshold: ±10% or ±$50K
Running variance analysis...
✅ Variance Analysis Complete!
📊 Q1 2025 PERFORMANCE SUMMARY
🎯 Overall Results:
- Revenue: $2,850K vs budget $3,000K (-5.0%) 🔴
- Gross Profit: $1,110K vs budget $1,200K (-7.5%) 🔴
- EBITDA: $270K vs budget $450K (-40.0%) 🔴
📉 Key Drivers of Underperformance:
1. Revenue Miss: $(150K) - 5% below plan
→ Slower than expected customer acquisition
→ Delayed Q1 enterprise deals to Q2
2. Operating Expenses Over: $90K - 12% above plan
→ Marketing campaign: $75K incremental spend
→ Headcount additions brought forward to Q1
🔍 Detailed Findings:
🔴 CRITICAL VARIANCES (>10%):
- Operating Expenses: $840K vs $750K (+12.0%) - $90K over
- EBITDA: $270K vs $450K (-40.0%) - $180K under
- Marketing: $325K vs $260K (+25.0%) - $65K over
⚠️ WARNING VARIANCES (5-10%):
- Revenue: $2,850K vs $3,000K (-5.0%) - $150K under
- Gross Profit: $1,110K vs $1,200K (-7.5%) - $90K under
✅ FAVORABLE VARIANCES:
- COGS: $1,740K vs $1,800K (-3.3%) - $60K favorable
- Facilities: $45K vs $50K (-10.0%) - $5K favorable
💡 RECOMMENDATIONS:
Immediate Actions:
1. Review Q2 sales pipeline - need $150K catch-up + Q2 plan
2. Evaluate marketing campaign ROI (Q1 spend +$65K)
3. Assess if Q1 revenue timing shift impacts full-year plan
Strategic Questions:
1. Is Q1 revenue miss timing (shifts to Q2) or lost?
2. Will marketing investment drive Q2-Q4 revenue?
3. Should we adjust full-year budget guidance?
📁 Report saved to: Q1_2025_Variance_Analysis.xlsx
Would you like me to:
- Add trend analysis vs Q4 2024?
- Create forecast scenarios for Q2?
- Drill into specific expense categories?
Common Variations
Request: "Why is marketing over budget?"
Response:
Let me drill into marketing expenses.
Marketing Variance Detail:
Budget: $260K
Actual: $325K
Variance: $65K (25% over)
Breakdown by subcategory:
- Digital Ads: $180K vs $150K (+$30K)
→ Google Ads campaign expanded
- Events: $75K vs $60K (+$15K)
→ Added trade show booth
- Content: $45K vs $40K (+$5K)
→ Video production
- Agencies: $25K vs $10K (+$15K)
→ New creative agency retainer
Primary driver: Digital ads campaign expansion (+$30K)
Was this spend planned but not budgeted, or truly incremental?
Would you like me to analyze marketing ROI?
Request: "Compare last 3 months"
Response:
I'll create a 3-month trend analysis.
Revenue Trend (Budget vs Actual):
| Month | Budget | Actual | Variance | Var% | Trend |
|-------|--------|--------|----------|------|-------|
| Jan | $1,000K| $970K | $(30K) | -3% | ⬇️ |
| Feb | $1,000K| $950K | $(50K) | -5% | ⬇️ |
| Mar | $1,000K| $930K | $(70K) | -7% | ⬇️ |
| Q1 | $3,000K| $2,850K| $(150K) | -5% | ⬇️ |
⚠️ ALERT: Revenue variance is worsening month-over-month
This suggests a structural issue, not timing:
- Jan: 97% of budget
- Feb: 95% of budget
- Mar: 93% of budget
Recommendation: Immediate deep-dive on sales execution
Formatting Rules
The Skill applies professional formatting:
Conditional Formatting
- Green cells: Favorable variances (>5% better than budget)
- Yellow cells: Variances within ±5%
- Red cells: Unfavorable variances (>5% worse than budget)
Number Formatting
- Currency: $1,000K or $1.0M (use K for thousands, M for millions)
- Percentages: 1 decimal place (5.0%)
- Variance: Show sign ($(50K) or $50K)
Icons
✅ = On track / Favorable
⚠️ = Warning / Needs attention
🔴 = Critical / Unfavorable
⬆️ = Improving trend
⬇️ = Worsening trend
➡️ = Flat trend
Best Practices Embedded
- Materiality Thresholds: Don't flag every small variance
- Commentary Not Just Numbers: Explain "why", not just "what"
- Action-Oriented: Recommend next steps
- Executive Summary: Leadership wants top 5-10 items
- Trend Analysis: Show if variance is new or ongoing
- Sign Conventions: Consistent favorable/unfavorable labeling
- Audit Trail: Show calculations and formulas
Resources
See resources folder for:
REFERENCE.md: Variance analysis best practicestemplates/: Sample variance reports
Limitations
This Skill provides automated variance analysis for:
- Standard income statement formats
- Monthly/quarterly reporting
- Budget vs actual comparisons
For more complex analysis, you may need:
- Statistical variance analysis (standard deviations)
- Multi-year trend analysis
- Driver-based variance decomposition
- Forecast vs forecast comparisons
Version History
- v1.0.0 (2025-10-27): Initial release with core variance analysis functionality
