aurelienclere-365/bpa-cowork-plugin/bpa-budget-variance

BPA Budget vs Actuals Variance Analysis

What This Skill Does

Queries BPA to deliver FP&A-grade budget and forecast analysis:

• Budget vs Actuals: Side-by-side with variance (amount and %)
• Favorable/Unfavorable classification: Revenue over-budget = favorable; expense over-budget = unfavorable
• Budget utilization: % of budget consumed year-to-date
• Remaining budget: Budget minus actuals, projected vs remaining
• Forecast accuracy: Forecast vs actuals for closed periods
• Dimension drill-down: By cost center, department, GL account, or legal entity
• Encumbrance/commitment: Committed spend not yet invoiced vs remaining budget

Prerequisites

• BPA connector active (uses get_bpa_dataset_schema and execute_dax_query tools)
• Budget data must be loaded in D365FO Budget module and synced to BPA
• BPA User role in Power Platform environment

Workflow

Step 1 — Discover schema

Call get_bpa_dataset_schema to identify:

• Budget and actuals measure names
• Available forecast versions (original budget, revised budget, latest forecast)
• Dimension hierarchies (cost center, department, main account)
• Fiscal calendar structure

Step 2 — Clarify scope

Ask for:

• Period: YTD, specific quarter, full fiscal year, or specific month
• Dimension: All, by department, by cost center, or by GL account
• Budget version: Original budget or latest forecast
• Focus: Revenue side, expense side, or full P&L variance

Step 3 — Execute DAX queries

Full P&L Budget vs Actuals (YTD):

EVALUATE
SUMMARIZECOLUMNS(
    'MainAccount'[AccountCategory],
    'MainAccount'[AccountName],
    FILTER('Date', 'Date'[FiscalYear] = 2024 && 'Date'[FiscalPeriodNumber] <= 9),
    "Actual", [Total Actuals],
    "Budget", [Total Budget],
    "Variance Amount", [Total Actuals] - [Total Budget],
    "Variance %", DIVIDE([Total Actuals] - [Total Budget], ABS([Total Budget])),
    "Budget Utilization %", DIVIDE([Total Actuals], [Total Budget])
)
ORDER BY ABS([Total Actuals] - [Total Budget]) DESC

Department spend vs budget:

EVALUATE
SUMMARIZECOLUMNS(
    'Department'[DepartmentName],
    FILTER('Date', 'Date'[FiscalYear] = 2024),
    "YTD Actual", [Total Actuals],
    "YTD Budget", [Total Budget],
    "Variance", [Total Actuals] - [Total Budget],
    "Variance %", DIVIDE([Total Actuals] - [Total Budget], ABS([Total Budget])),
    "Remaining Budget", [Total Budget] - [Total Actuals],
    "Budget Used %", DIVIDE([Total Actuals], [Total Budget])
)
ORDER BY [Variance] DESC

Monthly actuals vs budget trend (rolling 12 months):

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[FiscalYear],
    'Date'[FiscalPeriodName],
    "Actual", [Total Actuals],
    "Budget", [Total Budget],
    "Variance", [Total Actuals] - [Total Budget],
    "Cumulative Actual", CALCULATE([Total Actuals], DATESYTD('Date'[Date])),
    "Cumulative Budget", CALCULATE([Total Budget], DATESYTD('Date'[Date]))
)
ORDER BY 'Date'[FiscalYear], 'Date'[FiscalPeriodName]

Top over-budget cost centers:

EVALUATE
TOPN(
    10,
    SUMMARIZECOLUMNS(
        'CostCenter'[CostCenterName],
        FILTER('Date', 'Date'[FiscalYear] = 2024),
        "Actual", [Total Actuals],
        "Budget", [Total Budget],
        "Over Budget Amount", [Total Actuals] - [Total Budget]
    ),
    [Over Budget Amount],
    DESC
)

If measure names differ, call get_bpa_dataset_schema and adapt accordingly.

Step 4 — Present results

Variance summary table:

Variance classification rules:

• Revenue: Actual > Budget = Favorable (+), Actual < Budget = Unfavorable (-)
• Expense: Actual < Budget = Favorable (+), Actual > Budget = Unfavorable (-)

Include summary KPIs:

Total variance: -€35K (unfavorable) | Budget utilization: 78% | Departments over budget: 3 of 12

Key FP&A Metrics

Example Prompts That Trigger This Skill

• "Show me budget vs actuals for FY2024 YTD"
• "Which departments are over budget?"
• "What is our budget utilization by cost center?"
• "Show me the variance report for Q3 2024"
• "How much budget is left for IT this year?"
• "Are we on track to hit our revenue budget?"
• "Show me favorable and unfavorable variances for this month"
• "What is the forecast vs original budget for Q4?"