Finance AI Skill

Budget Vs Actual Tracking

Compare actual financial results against budget with automated variance detection, root cause analysis, and trend tracking. Use when setting up budget vs. actual reporting, creating variance analysis dashboards, tracking departmental spend against budget, i...

Budget vs. Actual Tracking

Continuously monitor how actual spend and revenue compare to budget, identify variances, and enable corrective action before the period ends.

Workflow

Define tracking dimensions: Decide what to track — by department, cost center, project, product line, region, or any combination. Granularity should match decision-making needs.
Set up the budget baseline: Load the approved budget into the tracking system (monthly or quarterly granularity minimum). Ensure the budget is locked after approval — only changes through formal reforecast process.
Connect actuals feed: Integrate with the GL so actual expenses and revenues flow in automatically, ideally in near real-time or at minimum daily.
Run variance calculation: Compute variances at each dimension — absolute ($), relative (%), and cumulative YTD.
Apply materiality thresholds: Filter noise by focusing only on variances exceeding predefined thresholds (e.g., > 5% or > $10K, whichever is greater).
Categorize variances: Tag each material variance as favorable (F) or unfavorable (U), and as structural (trend) or timing-related (one-off).
Generate variance commentary: Prompt budget owners to explain material variances; use AI to draft initial explanations based on transaction patterns.
Publish tracking dashboard: Distribute to department heads, finance leadership, and executive team with appropriate data access controls.
Trigger alerts: Send automatic notifications when running actuals are projected to exceed budget by end of period.
Feed reforecast: Use variance insights to adjust the rolling forecast — if a budget variance is permanent, the forecast should reflect the new reality.

Variance Calculation Framework

BASIC CALCULATIONS:
  Absolute Variance = Actual - Budget
  Relative Variance = (Actual - Budget) / Budget × 100%
  YTD Variance = Σ(Monthly Actuals YTD) - Σ(Monthly Budget YTD)
  Run-Rate Variance = (MTD Actual / Days Elapsed) × Days in Month - Monthly Budget

VARIANCE INTERPRETATION:
  Revenue variance:
    Positive (Actual > Budget) = Favorable ✓
    Negative (Actual < Budget) = Unfavorable ✗
  
  Expense variance:
    Positive (Actual > Budget) = Unfavorable ✗
    Negative (Actual < Budget) = Favorable ✓

RATIOS TO TRACK:
  Budget Utilization = Actual / Budget
    > 100% = over budget
    < 100% = under budget
    ~100% = on target
  
  Pace Rate = YTD Actual / (Budget × Months Elapsed / Total Months)
    > 100% = spending faster than planned
    < 100% = spending slower than planned

Materiality Threshold Matrix

| Account Level | Threshold | Action | |--------------|-----------|--------| | GL line item | > 10% OR > $5,000 | Monthly review, commentary required | | Department total | > 5% OR > $25,000 | Manager + Finance review | | Business unit | > 3% OR > $100,000 | Executive review | | Corporate | > 1% OR > $500,000 | Board-level attention |

Rules:

Use whichever threshold is greater (% or $)
For high-velocity accounts (e.g., cost of goods), tighten thresholds
For low-volume accounts (e.g., legal fees), relax percentage but keep $ threshold
Cumulative YTD variances get lower thresholds than single-month variances

Budget Performance Dashboard

BUDGET PERFORMANCE — Marketing Department — January 2025
=========================================================

CATEGORY              MONTHLY BUDGET    ACTUAL    VARIANCE    UTILIZATION    TREND
──────────────────────────────────────────────────────────────────────────────────
Digital Advertising   $250,000          $278,000  -$28,000 U   111.2%       ↗↑
Content Creation      $80,000           $72,000   +$8,000 F    90.0%        →
Events & Trade Shows  $120,000          $0        +$120,000 F  0.0%         → (Feb)
Employee Referrals    $15,000           $22,500   -$7,500 U    150.0%       ↗↑
Market Research       $35,000           $31,000   +$4,000 F    88.6%        →
Agency Retainers      $150,000          $150,000  $0          100.0%        →

TOTAL                 $650,000          $553,500  +$96,500 F   85.2%

RUN-RATE PROJECTION:
  At current pace, Q1 total spend projected: $1,660,500
  Q1 Budget: $1,950,000
  Projected Q1 variance: +$289,500 Favorable
  Note: Events budget shifts to Feb-Mar; true utilization higher than shown

KEY VARIANCES REQUIRING COMMENTARY:
  1. Digital Advertising: +11.2% over budget — increased CPC in Q4 carried over
  2. Employee Referrals: +50% over budget — signing bonus increase took effect Jan 1

Automated Variance Alert System

Configure alerts that fire when conditions are met:

| Alert Type | Condition | Recipients | Frequency | |-----------|-----------|------------|-----------| | Over-spend Warning | Running actuals > 80% of budget but < 50% of period elapsed | Budget owner + Finance BP | Weekly | | Over-spend Breach | Actual > Budget for current month | Budget owner + Finance BP + VP | Immediate | | Year-End Projection | Run-rate projects full-year overspend > 5% | CFO + Department head | Monthly | | Under-spend Flag | Actual < 50% of budget AND > 75% of period elapsed | Budget owner (spend down risk) | Monthly | | Single-Transaction Spike | One transaction > 20% of monthly budget | Budget owner + Controller | Immediate |

Variance Commentary Template

Require budget owners to explain material variances using this structure:

VARIANCE COMMENTARY — [Department] — [Account] — [Period]

Variance: [$] / [%] [Favorable/Unfavorable]

ROOT CAUSE:
  [ ] Timing — expense/revenue shifted to/from another period
  [ ] Volume — more/fewer units, transactions, or headcount than planned
  [ ] Price — unit cost or rate higher/lower than budgeted
  [ ] Scope — new initiative or project not in original budget
  [ ] Error — data entry error, incorrect budget assumption, or coding error
  [ ] Other: [describe]

EXPLANATION:
  [1–2 sentences on why the variance occurred. Reference specific events,
   decisions, or market conditions.]

PERSISTENCE:
  [ ] One-time — will not recur in future periods
  [ ] Temporary — expected to normalize within [X] months
  [ ] Structural — budget assumption was incorrect; needs reforecast

CORRECTIVE ACTION:
  [ ] No action needed — within acceptable range
  [ ] Spending adjustment — reduce/increase in future months
  [ ] Reforecast request — submit updated forecast
  [ ] Budget transfer — move funds from underspent account

Budget Transfer Process

When a department needs to shift budget between accounts:

BUDGET TRANSFER WORKFLOW:

1. Department head identifies need:
   "We need to move $50K from Content Creation to Digital Advertising
    to cover a new campaign opportunity."

2. Transfer request submitted with:
   • Source account and amount to transfer
   • Destination account
   • Business justification
   • Expected ROI or impact

3. Approval hierarchy:
   • ≤ $25K: Finance BP approval
   • $25K–$100K: VP Finance approval
   • > $100K: CFO approval
   • Total transfers > 10% of dept budget: Executive committee

4. Rules:
   • Cannot transfer into accounts already over budget
   • Cannot transfer from accounts < 80% utilized (prevent under-spend cascade)
   • Headcount budget cannot be transferred to opex (and vice versa) without CFO sign-off
   • All transfers logged with audit trail

5. Post-approval:
   • Updated budget baseline reflects transfer
   • Tracking dashboard refreshes automatically
   • Notification sent to all stakeholders

Edge Cases

New departments or cost centers with no prior budget: Use run-rate estimates, zero-based budgeting approach, or benchmark against similar departments
Lumpy expenses (quarterly insurance, annual licenses): Budget monthly but flag that actuals will be lumpy; compare on YTD basis, not monthly
M&A activity: Separate acquired entity budget from organic budget; track acquisition-related expenses in dedicated accounts
Currency fluctuations: For multi-currency companies, separate volume variances from FX variances using constant-currency reporting
Budget not yet approved at period start: Use prior-year actuals or management estimates as provisional budget; clearly mark as "unapproved"
Reforecast mid-year: When a formal reforecast replaces the budget, maintain both for comparison — show "original budget" and "reforecast" columns
Zero-based budgeting review: Annually require departments to justify every line item from scratch, not just roll forward prior year

Output

Monthly Budget vs. Actual Report

BUDGET VS. ACTUAL — Q1 2025 — All Departments
==============================================

                              BUDGET     ACTUAL    VARIANCE   UTIL
─────────────────────────────────────────────────────────────────────
Engineering
  Salaries & Benefits        $1,200K    $1,180K   +$20K F     98.3%
  Cloud Infrastructure        $350K      $410K     -$60K U    117.1% ⚠
  Tools & Licenses           $120K      $108K      +$12K F    90.0%
  Subtotal                   $1,670K    $1,698K    -$28K U    101.7%

Sales
  Commissions                $800K      $850K      -$50K U    106.3% ⚠
  Travel & Entertainment     $150K      $95K       +$55K F    63.3%
  Marketing Collateral       $80K       $82K       -$2K U     102.5%
  Subtotal                   $1,030K    $1,027K    +$3K F     99.7%

Total Operating Expenses     $4,500K    $4,510K    -$10K U    100.2%

RUN-RATE ANALYSIS:
  If Q2 follows Q1 pace, full-year projected overspend: $40K on cloud, $200K on commissions
  Recommended action: renegotiate cloud contracts, review commission plan structure

Integration Points

ERP/GL (NetSuite, SAP, Oracle): Actual transaction data feed
Budgeting tools (Adaptive Insights, Anaplan, Planful): Budget baseline storage, variance analytics
BI platforms (Tableau, Power BI, Looker): Dashboard visualization, drill-down
Slack/Teams: Automated alert notifications
Email: Scheduled budget performance reports
Approval workflows (Approveable, native ERP tools): Budget transfer approvals
Data warehouse: Historical variance trend analysis