Variance Analysis Model¶
Problem¶
gold_scenario_trial_balance unions actual + budget but provides no computed variance. Users must calculate variance in Excel. A proper EPM tool provides:
- Absolute variance (actual - budget)
- Percentage variance
- Favorable/unfavorable classification (expense under-budget = favorable)
Requirements¶
R1: New model gold_variance_analysis¶
Pivots scenario data into a columnar format per account/period:
| Column | Description |
|---|---|
| data_area_id | Legal entity |
| fiscal_year, fiscal_period | Period |
| main_account, account_name | Account |
| dim_cost_center, dim_department | Dimensions |
| actual_amount | From scenario_id = 'ACTUAL' |
| budget_amount | From scenario_id = 'BUDGET' |
| forecast_amount | From latest forecast scenario |
| variance_abs | actual_amount - budget_amount |
| variance_pct | variance_abs / nullif(budget_amount, 0) × 100 |
| variance_favorable | Boolean: true if variance is favorable |
R2: Favorable/unfavorable logic¶
- Revenue accounts (account_type in revenue categories): actual > budget = favorable
- Expense accounts (account_type in expense categories): actual < budget = favorable
- Asset/Liability: not applicable (null)
- Uses
is_pnlflag + account_type_name to determine direction
R3: Cube schema¶
New Cube model variance_analysis with:
- Dimensions: entity, year, period, account, cost_center, department
- Measures: actual_amount, budget_amount, forecast_amount, variance_abs, variance_pct
R4: Multi-scenario comparison¶
- Support comparing any two scenarios (not just actual vs budget)
- Parameterized via Cube filters:
base_scenarioandcompare_scenario - Default: base = ACTUAL, compare = BUDGET
Acceptance Tests¶
| Test | Assertion |
|---|---|
assert_variance_abs_formula |
variance_abs = actual_amount - budget_amount within 0.01 |
assert_variance_pct_formula |
variance_pct = variance_abs / budget_amount × 100 (where budget ≠ 0) |
assert_favorable_revenue |
Revenue accounts: actual > budget → variance_favorable = true |
assert_favorable_expense |
Expense accounts: actual < budget → variance_favorable = true |
assert_no_orphan_actuals |
Every account with actuals has a budget row (or null budget) |
Out of Scope¶
- Waterfall / bridge analysis (e.g., price/volume/mix decomposition)
- Trend analysis (period-over-period)
- Commentary / annotation on variances