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

> How to build a budget variance analysis module in a custom ERP — from budget entry and actual posting to automated variance reports, exception alerts, and drill-down analysis.

URL: https://www.matthewswong.com/en/blog/erp-budget-variance-analysis/

- Author: Matthews Wong (https://www.matthewswong.com/en/about/)
- Published: 2025-06-10
- Updated: 2026-10-17
- Category: ERP

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. In my freelance ERP consulting work, 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. The system I built 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.

> From my experience as a freelance ERP consultant: 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. I 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. I store variance explanations in the system — this creates an accountability record useful for management review meetings.

> **Warning: Budget vs. Commitment vs. Actual**

> 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. The system I built 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 my client in the third month of use.

## How I Approached This

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.

## Period Comparison and Trending

Beyond current-period variance, management needs period-over-period comparison: Is the variance getting better or worse? I built in 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

- Versa Cloud ERP — Budgeting: How ERP Enables Accurate Budget Planning with Real-Time Centralized Data — https://www.versaclouderp.com/blog/budgeting-how-erp-enables-accurate-budget-planning-with-real-time-centralized-data/
- Numeric — Complete Guide to Variance Analysis in 2025 — https://www.numeric.io/blog/variance-analysis-guide
- Financial Models Lab — Understand Budget Variance Analysis to Take Control of Your Finances — https://financialmodelslab.com/blogs/blog/budget-variance-analysis

> **Key takeaway:** Budget variance analysis compares planned spend and revenue against what actually happened, and it is among the highest-value features in a financial ERP module. Computing it continuously rather than at month end turns it from a historical report into a control that can still change the outcome.

## Source links

- https://www.versaclouderp.com/blog/budgeting-how-erp-enables-accurate-budget-planning-with-real-time-centralized-data/
- https://www.numeric.io/blog/variance-analysis-guide

## FAQ

### Why does the dashboard show Budget, Actuals, AND Commitments instead of just Budget vs. Actuals?

Showing only actuals against budget gives a false picture of available funds. If a department has a Rp 100M budget, has spent Rp 60M in actuals, but holds Rp 50M in approved but unpaid Purchase Orders, the real available budget is Rp -10M — not Rp 40M. Tracking commitments (approved POs and unapproved AP invoices) alongside actuals prevents over-spending before money is disbursed.

### Why are the variance calculations stored as PostgreSQL views rather than computed in the application layer?

Placing the variance logic in PostgreSQL views means the numbers stay automatically in sync whenever a new journal entry is posted, without any extra application code. For dashboard performance the view is also wrapped in a materialized view that refreshes every 30 minutes, with an on-demand refresh available when the CFO needs the latest numbers before a board meeting.

### How does the module support budget revisions mid-year, which are common in Indonesian businesses?

The system supports budget versioning — the original annual budget is Version 1, the Q1 revised forecast is Version 2, and so on. Variance reports can compare actuals against any stored version, giving management a clear 'vs. original budget' or 'vs. latest forecast' view. This is especially relevant in Indonesia where budgets are frequently revised in response to Rupiah depreciation, commodity price changes, or new government contracts.

### How does the purchase order approval workflow integrate with budget control?

When a Purchase Order is submitted for approval, the system automatically checks available budget (budget minus actuals minus commitments) for the relevant cost center. If the PO would push the cost center over budget, the workflow adds the Finance Manager as an additional required approver. This preventive control catches overruns at the commitment stage, before the money is actually spent.

### Why does the post recommend letting department managers enter their own budget lines rather than having finance do it centrally?

When department heads own their own budget lines through a self-service entry screen with a submission workflow, the budget becomes more accurate because it reflects operational reality. Managers who set their own budgets are also more accountable for variances against them. This participatory budgeting approach commonly produces better forecast accuracy than top-down budget dictation.

---

- HTML version: https://www.matthewswong.com/en/blog/erp-budget-variance-analysis/
- Bahasa Indonesia: https://www.matthewswong.com/id/blog/erp-budget-variance-analysis/
- Site index for agents: https://www.matthewswong.com/llms.txt
- Sitemap: https://www.matthewswong.com/sitemap.xml
- API description (OpenAPI): https://www.matthewswong.com/openapi.json