Budget vs. Actual Variance Analysis in a Custom ERP: Real-Time Financial Control

Budget variance analysis is the process of comparing what you planned to spend or earn against what actually happened — and it's one of the highest-value features in a financial ERP module. If your operating expenses are projected at Rp 5 billion for the year and you identify a Rp 250 million unfavorable variance in Q1, immediate corrective action can prevent a Rp 1 billion full-year shortfall. ERP systems turn this from a monthly manual exercise into a continuous, real-time monitoring capability. At Commsult, I built a budget variance module for a construction company client where cost overruns are a critical business risk. Here's how it works.

Budget Entry and Versioning

The budget module starts with budget creation: the finance team enters the annual budget by cost center and account code, broken down by month. Our system supports budget versioning — the original budget (Version 1), the Q1 revised forecast (Version 2), and so on. Variance reports can compare actuals against any version: 'vs. original budget' or 'vs. latest forecast.' This versioning is important in Indonesian businesses where annual budgets are frequently revised mid-year in response to market conditions — Rupiah depreciation, commodity price changes, or new government contracts.

The Budget Data Model

The budget table stores: budget_version_id, cost_center_id, account_id, fiscal_year, month (1-12), and budgeted_amount. Actuals come from the journal_lines table filtered by the same cost_center_id, account_id, and month. The variance is computed as: actuals minus budget (for expenses, a positive variance is unfavorable; for revenue, a positive variance is favorable). Variance percentage = (actuals - budget) / budget × 100. These calculations are views in PostgreSQL, not application-layer computations — they stay in sync automatically as new journal entries are posted.

Budget vs. Actual vs. Commitment Dashboard

  budget_lines (versioned)          journal_lines (actuals)
       │  budget_version_id               │  cost_center_id
       │  cost_center_id                  │  account_id
       │  account_id                      │  (debit - credit)
       │  month, budgeted_amount          │
       └──────────────┬───────────────────┘
                      │  PostgreSQL View (budget_variance_view)
                      │  + budget_commitments (open POs)
                      ▼
  ┌────────────────────────────────────────────────────────────┐
  │  Budget Variance Dashboard (React + Recharts)              │
  │                                                            │
  │  Cost Center    Budget    Actual  Commit  Avail  Status   │
  │  ─────────────────────────────────────────────────────── │
  │  IT Dept        500M      420M    60M     20M    🟡 96%  │
  │  Operations     800M      620M    50M    130M    🟢 77%  │
  │  Marketing      200M      240M     0M    -40M    🔴 120% │
  │  HR             300M      190M    20M     90M    🟢 63%  │
  │  ─────────────────────────────────────────────────────── │
  │  TOTAL          1.8B      1.47B  130M    200M   🟡 89%  │
  │                                                            │
  │  Available = Budget − Actual − Commitments                 │
  │  (preventive control: stops over-budget POs)              │
  └────────────────────────────────────────────────────────────┘

  Drill-down: Cost Center → Account → Journal Line

From my experience building ERP systems at Commsult: implement budget entry by department manager, not just by finance. The budget becomes more accurate and more adopted when department heads own their own budget lines. We built a self-service budget entry screen where each department manager enters their own budget with a submission workflow — the finance manager reviews and consolidates. Department managers who set their own budget are also more accountable for variances against it. This participatory budgeting approach consistently produces better forecast accuracy than top-down budget dictation.

Real-Time Variance Dashboard

The variance dashboard shows all cost centers side by side with traffic-light indicators: green (actuals within 5% of budget), amber (5-15% variance), red (>15% variance). Clicking any cost center drills down to account-level variances. Clicking any account shows the individual journal entries that make up the actual. This three-level drill-down — cost center → account → transaction — gives finance managers everything they need to investigate a variance without leaving the dashboard.

Automated Variance Alerts

A NestJS cron job runs weekly on Monday morning and checks all cost center accounts for variances above configured thresholds. Exceeding the threshold triggers an email to the cost center owner and their manager with: the account in question, budget amount, actual amount, variance amount and %, and a one-click link to the drill-down view. The cost center owner is expected to respond with an explanation within 5 business days. We store variance explanations in the system — this creates an accountability record useful for management review meetings.

-- PostgreSQL: Budget variance view (with commitments)
CREATE MATERIALIZED VIEW budget_variance_mv AS
SELECT
  cc.id            AS cost_center_id,
  cc.name          AS cost_center_name,
  coa.id           AS account_id,
  coa.account_name,
  coa.category,
  bl.fiscal_year,
  bl.month,
  -- Budget (latest approved version)
  COALESCE(bl.budgeted_amount, 0)                   AS budget,
  -- Actuals from journal
  COALESCE(SUM(jl.debit - jl.credit), 0)            AS actual,
  -- Commitments: approved POs not yet invoiced
  COALESCE(SUM(pol.amount - pol.invoiced_amount), 0) AS commitment,
  -- Computed columns
  COALESCE(bl.budgeted_amount, 0)
    - COALESCE(SUM(jl.debit - jl.credit), 0)
    - COALESCE(SUM(pol.amount - pol.invoiced_amount), 0)
    AS available,
  ROUND(
    COALESCE(SUM(jl.debit - jl.credit), 0)
      / NULLIF(bl.budgeted_amount, 0) * 100, 1
  ) AS pct_used
FROM cost_centers cc
JOIN budget_lines bl ON bl.cost_center_id = cc.id
  AND bl.budget_version_id = (
    SELECT id FROM budget_versions
    WHERE status = 'approved'
    ORDER BY version_number DESC LIMIT 1
  )
JOIN chart_of_accounts coa ON bl.account_id = coa.id
LEFT JOIN journal_lines jl ON jl.cost_center_id = cc.id
  AND jl.account_id = coa.id
  AND EXTRACT(YEAR FROM jl.posted_date) = bl.fiscal_year
  AND EXTRACT(MONTH FROM jl.posted_date) = bl.month
LEFT JOIN purchase_order_lines pol ON pol.cost_center_id = cc.id
  AND pol.account_id = coa.id
  AND pol.status = 'approved'
GROUP BY cc.id, cc.name, coa.id, coa.account_name,
         coa.category, bl.fiscal_year, bl.month, bl.budgeted_amount;

-- Refresh every 30 minutes
CREATE UNIQUE INDEX ON budget_variance_mv
  (cost_center_id, account_id, fiscal_year, month);

How We Built This at Commsult

The variance module is built on PostgreSQL views for the core computation, NestJS services for the API, and Recharts in React for visualization. The key PostgreSQL view joins budget_lines (budget) with journal_lines (actuals) and purchase_order_lines (commitments) on cost_center_id and account_id. The view is wrapped in a materialized view refreshed every 30 minutes for dashboard performance. On-demand refresh is available for when the CFO needs the latest numbers before a board meeting.

A common misunderstanding is showing only actuals vs. budget and missing committed spending. If your department has a Rp 100M budget, has spent Rp 60M (actuals), but has Rp 50M in approved but unpaid POs (commitments), the real available budget is Rp -10M — not Rp 40M. Failing to track commitments leads to over-spending. Our system maintains a budget_commitments table fed by approved Purchase Orders and unapproved AP invoices. The dashboard shows Budget - Actuals - Commitments = Available, not just Budget - Actuals. This prevented a serious over-budget situation at our client in the third month of use.

Period Comparison and Trending

Beyond current-period variance, management needs period-over-period comparison: Is the variance getting better or worse? We provide a 12-month variance trend view per cost center, showing the variance percentage for each month. A cost center with consistently 8-10% favorable variance on materials costs is likely under-budgeted — a useful insight for the next year's budget planning. A cost center with variance that was 3% in Q1 and 15% in Q3 suggests a structural cost increase that should be investigated.

Integration with Approval Workflows

The most impactful integration is between the budget module and the purchase order approval workflow. When a Purchase Order is submitted for approval, the system checks the available budget (budget minus actuals minus commitments) for the relevant cost center. If the PO would cause the cost center to exceed its budget, the approval workflow automatically adds the Finance Manager as an additional approver. This preventive control catches budget overruns before they happen — at the commitment stage — rather than detecting them after the money is spent.

Sources & Further Reading