aurelienclere-365/bpa-cowork-plugin/bpa-spending-behavior

bpa-spending-behavior

Analyses spending behaviour for a cost line or department over a defined period, comparing it month by month against the approved budget. Goes beyond the numbers: explains probable causes of deviations (internal and external factors) and provides corrective recommendations if the trend risks over-executing the annual budget.

Designed for FP&A Directors and operational unit directors who need to argue variance causes in front of the management committee — not just report the numbers.

When to use this skill

Use when asked for:

• Month-by-month actual spend vs approved budget for a cost category or department
• Which months had the greatest favourable or unfavourable variance
• Why variances occurred (delivery volume, routes, suppliers, fuel prices, seasonality,

macroeconomic context)

• Corrective recommendations if the annual budget is at risk of over-execution
• Spending trend analysis with risk assessment

Workflow

Step 1 — Retrieve schema

Call get_bpa_dataset_schema to identify:

• FactGeneralLedger — actual posted amounts (Amount, AccountId, FiscalPeriod, CompanyId)
• FactBudget — approved budget amounts (BudgetAmount, AccountId, FiscalPeriod)
• DimAccount — account names and categories (use to filter the cost line requested)
• DimDepartment / DimCostCentre — department dimension
• DimFiscalCalendar — period-to-month mapping

Step 2 — Monthly actuals for the cost line

Replace ACCOUNT_FILTER and YEAR with the values from the user's request:

EVALUATE
SUMMARIZECOLUMNS(
    DimFiscalCalendar[FiscalPeriod],
    DimFiscalCalendar[MonthName],
    FILTER(DimAccount, DimAccount[AccountCategory] = "Transportation"),
    "ActualSpend", CALCULATE(SUM(FactGeneralLedger[Amount]))
)
ORDER BY DimFiscalCalendar[FiscalPeriod]

Step 3 — Monthly budget for the same cost line

EVALUATE
SUMMARIZECOLUMNS(
    DimFiscalCalendar[FiscalPeriod],
    DimFiscalCalendar[MonthName],
    FILTER(DimAccount, DimAccount[AccountCategory] = "Transportation"),
    "BudgetAmount", CALCULATE(SUM(FactBudget[BudgetAmount]))
)
ORDER BY DimFiscalCalendar[FiscalPeriod]

Step 4 — Compute variance and rank months

For each month, calculate:

• Variance (USD) = Actual − Budget
• Variance (%) = (Actual − Budget) / Budget × 100
• Direction = Over budget (positive) or Under budget (negative)

Identify the top 3 months with the greatest unfavourable variance and the top 3 with the greatest favourable variance.

Step 5 — Causal interpretation

For each high-variance month, reason over likely causes:

• Internal factors: delivery volume spike/drop, route changes, supplier substitution,

contract renegotiation, one-off extraordinary costs

• External factors: fuel price movements, seasonal demand patterns, macroeconomic

events (inflation, supply chain disruptions), regulatory changes

Note: the AI assistant combines BPA transactional data with general knowledge of market context. The Director should validate with their operational team before presenting to the management committee.

Step 6 — Risk assessment and corrective recommendations

Calculate the YTD spend rate vs budget:

EVALUATE
VAR YTDActual = CALCULATE(SUM(FactGeneralLedger[Amount]),
    DimFiscalCalendar[FiscalYear] = 2026,
    DimAccount[AccountCategory] = "Transportation")
VAR YTDBudget = CALCULATE(SUM(FactBudget[BudgetAmount]),
    DimFiscalCalendar[FiscalYear] = 2026,
    DimAccount[AccountCategory] = "Transportation")
VAR AnnualBudget = CALCULATE(SUM(FactBudget[BudgetAmount]),
    DimFiscalCalendar[FiscalYear] = 2026,
    DimAccount[AccountCategory] = "Transportation")
RETURN ROW(
    "YTDActual", YTDActual,
    "YTDBudget", YTDBudget,
    "Variance", YTDActual - YTDBudget,
    "RemainingBudget", AnnualBudget - YTDActual,
    "ProjectedAnnual", YTDActual / DIVIDE(MONTH(TODAY()), 12)
)

If the projected annual spend exceeds the annual budget, surface corrective options:

• Renegotiate rates with top freight carriers
• Review route optimisation opportunities
• Defer discretionary shipments to Q4
• Request a budget revision for the impacted line

Step 7 — Present results

Return:

1. Month-by-month variance table (actual, budget, variance USD, variance %)
2. Top variance months (best and worst) with causal commentary
3. Annual risk summary: projected year-end spend vs budget
4. Corrective recommendations (if over-budget risk detected)

Sample result

Transportation Spend — Jan to Jun 2026 vs Budget

YTD: $1,914,400 actual vs $1,830,000 budget — +$84,400 (+4.6% over)

March spike analysis (+22.7%):

Likely causes: (1) fuel price spike (+8% in March per market data), (2) seasonal

peak in outbound shipments consistent with Q1 close rush, (3) possible unplanned

carrier substitution — validate with logistics team.

Annual risk: Projected year-end spend: $3,828,800 vs annual budget $3,720,000. Risk of over-execution: +$108,800 (+2.9%) — manageable but requires monitoring.

Recommendation: Renegotiate fuel surcharge clauses with top 3 carriers before Q3.