Excel Variance Analyzer

Overview — Prerequisites — Instructions — Output — Examples — Error Handling — Resources

Overview

Automates variance analysis for monthly/quarterly financial reporting and budget reviews. Generates flagged variance reports with automated commentary and executive summaries so FP&A teams can produce board-ready variance packages from raw budget and actual data without manual spreadsheet work.

Prerequisites

Node.js 18+
@negokaz/excel-mcp-server MCP server configured
Claude Code 1.0+
Budget and actual data in .xlsx or .csv format

Instructions

Step 1: Load Data

Use AskUserQuestion to collect:

Budget data: Excel file, CSV, or pasted table
Actual data: Same format as budget
Period: Month, quarter, or YTD
Threshold settings (or use defaults):
- Percentage threshold: 10% (flag items >10% variance)
- Dollar threshold: $50K (flag items >$50K absolute variance)

Step 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)

If mismatches found, report them and ask user to clarify.

Step 3: Calculate Variances

For each line item:

Absolute Variance = Actual - Budget
Percentage Variance = (Actual - Budget) / Budget x 100%

Sign Convention: Revenue/profit: positive = Favorable. Expenses: positive = Unfavorable.

Step 4: Flag Material Items

Flag	Criteria
Critical (red)	Revenue/profit >10% below budget, expenses >10% over, or absolute >$100K
Warning (yellow)	Revenue/profit 5-10% below, expenses 5-10% over, or absolute $50K-$100K
On Track (green)	Variance within +/-5% and absolute <$50K

Step 5: Generate Commentary

For each flagged item, provide automated commentary explaining what happened (variance in dollars and percentage), possible drivers (2-3 likely causes), and recommended action.

Step 6: Build 3-Sheet Report

Use the Excel MCP server to create:

Sheet 1 - Variance Summary: Table with columns: Line Item, Budget, Actual, Variance, % Var, Flag, Commentary. Conditional formatting (green/yellow/red) based on flagging rules.

Sheet 2 - Executive Summary: Performance highlights, top 5 unfavorable variances, top 5 favorable variances, key action items.

Sheet 3 - Trend Analysis (if multiple periods provided): Variance % by month/quarter with trend indicators (improving/worsening/flat).

Step 7: Format Professionally

Currency: $1,000K or $1.0M
Percentages: 1 decimal place
Variance: $(50K) for unfavorable, $50K for favorable
Conditional formatting: green (favorable >5%), yellow (within +/-5%), red (unfavorable >5%)
Bold headers, freeze top row and left column

Step 8: Return Results

Report overall performance summary, top 3 critical variances with drivers, recommended actions, and offer follow-up (trend analysis, drill-down, forecast scenarios).

Output

.xlsx file with 3 sheets: Variance Summary, Executive Summary, Trend Analysis
Summary text with performance highlights and critical variances
Actionable recommendations for each flagged item

Examples

Standard Variance Analysis

User: "Analyze Q1 budget vs actual"

Results:
- Revenue: $2,850K vs $3,000K budget (-5.0%)
- EBITDA: $270K vs $450K budget (-40.0%)
- Key driver: OpEx 12% over budget ($90K)
- Action: Review Q2 pipeline, evaluate marketing ROI

Saved to: Q1_2025_Variance_Analysis.xlsx

Drill-Down Request

User: "Why is marketing over budget?"

Response: Breaks down marketing by subcategory:
- Digital Ads: +$30K (campaign expansion)
- Events: +$15K (added trade show)
- Agencies: +$15K (new retainer)
Primary driver: Digital ads campaign expansion.

Error Handling

Scenario	Response
Budget and actual don't match	List mismatches, ask user to reconcile
Division by zero (budget = $0)	Show absolute variance only, flag for review
All variances within threshold	Report clean bill of health, suggest lowering thresholds
Negative budget values	Ask user to confirm sign convention

Edge Cases

If no period specified, ask user or default to most recent month
If user provides only one dataset, ask for the comparison dataset
If data includes non-financial line items, exclude from variance calculations
If user wants forecast comparison instead of budget, same workflow applies

Resources

${CLAUDE_SKILL_DIR}/references/REFERENCE.md - Variance analysis best practices, sign conventions

excel-variance-analyzer