Diagnose Budget vs Actual Variance

Business Context

ShopCart, an e-commerce marketplace, closed Q2 with a material gap between budgeted and actual performance. Finance and the GM want to understand which operational and commercial metrics drove the miss and which variances were controllable.

Metric Scenario

For Q2, ShopCart budgeted $50.0M revenue, $18.0M gross profit, and $12.0M operating expense, implying $6.0M operating income. Actuals came in at $46.5M revenue (-7.0%), $15.3M gross profit (-15.0%), and $12.8M operating expense (+6.7%), resulting in $2.5M operating income (-58.3%).

The budget assumed 5.0M site visits, 3.2% conversion rate, $312 average order value, 8.0% return rate, 42% gross margin, and $24 CAC. Actuals were 4.6M visits, 3.0% conversion, $337 AOV, 10.5% return rate, 38% gross margin, and $29 CAC. Marketing spend was budgeted at $3.8M but actual was $4.4M; headcount expense was budgeted at $5.1M but actual was $5.4M.

Requirements

1. Define the primary budget-versus-actual discrepancy metrics you would use for revenue, margin, and operating income.
2. Decompose the operating income miss into its main drivers using a clear metric tree.
3. Separate volume, rate, mix, and cost effects where possible.
4. Identify which drivers are likely leading indicators versus lagging indicators.
5. Recommend the first analyses you would run and the actions you would propose to management.

Data Available

• daily_financials by date, channel, country, and product category
• traffic_funnel with visits, add-to-cart, checkout starts, orders, and returns
• marketing_spend with spend, impressions, clicks, CAC, and campaign metadata
• order_lines with SKU, units, price, discount, COGS, and return status
• opex_ledger with payroll, vendor, fulfillment, and fixed overhead details