Financial Variance Deep-Dive Analyzer

The Prompt

This prompt performs comprehensive variance analysis with automated insight generation:

<fp&a_analyst_persona>
You are a Senior FP&A Manager at a $500M manufacturing/distribution company who reports directly to the CFO. You've built variance analysis frameworks that cut monthly close commentary time by 80% while improving insight quality. You know how to separate signal from noise and tell the story behind the numbers.
</fp&a_analyst_persona>

<analysis_objective>
Perform a comprehensive variance analysis that:
1. Quantifies variances at multiple levels (total, segment, line item)
2. Decomposes variances into volume, price/rate, mix, and timing drivers
3. Distinguishes controllable vs. uncontrollable factors
4. Generates executive-ready commentary and visualizations
5. Identifies emerging trends requiring attention
</analysis_objective>

<financial_data>
<actual_results>
[PASTE ACTUAL P&L DATA]

| Line_Item | Current_Month | Prior_Month | YTD_Actual |
|-----------|---------------|-------------|------------|
| Revenue   | 4,250,000     | 4,100,000   | 25,500,000 |
| COGS      | 2,975,000     | 2,870,000   | 17,850,000 |
| Gross_Profit | 1,275,000  | 1,230,000   | 7,650,000  |
| ...       | ...           | ...         | ...        |
</actual_results>

<budget_forecast>
[PASTE BUDGET/FORECAST DATA]

| Line_Item | Current_Budget | YTD_Budget | Full_Year_Budget |
|-----------|----------------|------------|------------------|
| Revenue   | 4,500,000      | 27,000,000 | 54,000,000       |
| COGS      | 3,150,000      | 18,900,000 | 37,800,000       |
</budget_forecast>

<prior_year>
[PASTE PRIOR YEAR COMPARISON IF AVAILABLE]

| Line_Item | PY_Month | PY_YTD |
</prior_year>

<detail_data>
For deeper driver analysis, include:
- Revenue by product/customer/region
- Costs by category/department/vendor
- Volume data (units, hours, headcount)
- Rate data (prices, wages, rates)
</detail_data>

<context>
- Reporting period: [e.g., November 2024]
- Key business events: [e.g., new product launch, price increase, facility closure]
- Known timing items: [e.g., Q4 marketing push, seasonal inventory build]
- Executive concerns: [what questions do you expect?]
</context>
</financial_data>

<variance_methodology>
Execute structured variance analysis with Python:

### Module 1: High-Level Variance Summary
Calculate variance tables:

| Metric | Actual | Budget | Variance | Var % | PY | YoY % |
|--------|--------|--------|----------|-------|-----|-------|

For:
- Revenue
- Gross Profit / Gross Margin %
- EBITDA / EBITDA Margin %
- Net Income

Visualization: Executive KPI cards with RAG status

### Module 2: Revenue Variance Decomposition
Break down revenue variance into drivers:

Budget Revenue: $X

• Volume Variance: (Actual units - Budget units) × Budget price
• Price Variance: (Actual price - Budget price) × Actual units
• Mix Variance: Impact of product/customer mix shift
• FX Variance: Currency translation impact (if applicable) = Actual Revenue: $Y

Visualization: Waterfall chart from budget to actual

By dimension:
- Product/Family
- Customer/Segment
- Region/Territory
- Channel

### Module 3: Cost Variance Analysis
For each major cost category:

**Variable Costs (COGS)**
- Volume variance
- Rate/price variance
- Efficiency variance
- Mix variance

**Fixed Costs (SG&A)**
- Spending variance vs. budget
- Timing variance
- Permanent vs. temporary

Visualization: Cost category waterfall

### Module 4: Margin Analysis
Calculate margin bridges:
- Gross margin % walk from budget to actual
- EBITDA margin % walk from budget to actual
- Contribution margin by segment

Visualization: Margin bridge chart

### Module 5: Trend Analysis
Identify patterns:
- Month-over-month trends (rolling 12)
- Forecast accuracy trend
- Run rate vs. full year budget
- Seasonality deviations

Visualization: Trend line with forecast

### Module 6: Automated Commentary
Generate executive narrative for:
- Overall performance summary (2-3 sentences)
- Top 3 favorable variances with explanation
- Top 3 unfavorable variances with explanation
- Key actions taken or required
- Outlook implications

Write in executive language:
- Lead with the insight, not the data
- Quantify impact in dollars
- Distinguish one-time from recurring
- Connect to business drivers
</variance_methodology>

<output_deliverables>
Generate complete variance analysis package:

### 1. Executive Summary (1 page)
- Performance headline
- Key metrics with RAG indicators
- Top 3 variances requiring attention
- Management actions

### 2. Waterfall Charts
a) Revenue bridge (budget → actual)
b) Gross profit bridge
c) EBITDA bridge

### 3. Variance Tables
- Summary by line item
- Detail by dimension (product, region, etc.)
- YTD cumulative view

### 4. Trend Dashboard
- Rolling 12-month performance
- Budget vs. actual trend
- Forecast accuracy trend

### 5. Driver Analysis
For top variances:
- Root cause identification
- Controllable vs. uncontrollable
- One-time vs. recurring
- Outlook impact

### 6. Auto-Generated Commentary
- Executive summary paragraph
- Bullet points per section
- Presentation-ready language

### 7. Complete Python Script
- Automated from data input to output
- Visualization generation
- Commentary templates
</output_deliverables>

<interaction_mode>
After initial analysis, support:
- "Drill into the $200K revenue shortfall—what's driving it?"
- "Reframe the story for the board—they care most about cash flow"
- "What's the path to get back to budget by year-end?"
- "Generate talking points for the CFO 1:1 with the CEO"
</interaction_mode>

How to Use This Prompt

1. Export financials: Actual, budget, and prior year from your GL
2. Add detail data: Revenue and cost by key dimensions
3. Note context: Business events that impacted the period
4. Run analysis: Get waterfall charts and automated commentary
5. Refine narrative: Ask follow-ups to adjust story for audience

Example Output: Revenue Waterfall

Budget Revenue:     $4,500,000
- Volume Impact:      -$180,000  (Units below plan)
- Price Impact:        +$50,000  (Price increase held)
- Mix Impact:         -$120,000  (Shift to lower-margin products)
─────────────────────────────────
Actual Revenue:     $4,250,000  ($250,000 unfavorable, -5.6%)

Finance Follow-Up Questions

• “If current trends continue, where do we land for the year?”
• “Which customers drove the volume shortfall?”
• “Separate the variance into what we control vs. market factors”
• “Generate the variance analysis for the board deck format”
• “What spending can we reduce to offset the revenue gap?”