excel-variance-analyzer

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

1. Materiality Thresholds: Don't flag every small variance
2. Commentary Not Just Numbers: Explain "why", not just "what"
3. Action-Oriented: Recommend next steps
4. Executive Summary: Leadership wants top 5-10 items
5. Trend Analysis: Show if variance is new or ongoing
6. Sign Conventions: Consistent favorable/unfavorable labeling
7. Audit Trail: Show calculations and formulas

Resources

See resources folder for:

• REFERENCE.md: Variance analysis best practices
• templates/: 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