Quick answer
To build a useful ERP budget-versus-actual report, compare the same entity, fiscal periods, accounts, departments, and currency against an explicitly selected budget baseline. Define the variance formula and favorable/unfavorable convention before publishing. Then reconcile both columns to their source records and investigate significant differences through account, dimension, and transaction detail.
Recommended starting layout: expense category, budget, actual, dollar variance, percentage variance, explanation, owner, and next action.
This guide proposes a reporting workflow for accountants, controllers, and ERP implementation teams. Product examples refer to Microsoft Dynamics 365 Finance and Sage Intacct documentation; they are not interchangeable configuration instructions. The worked example uses fictional figures, not customer results.
1. Define the comparison before building the report
Start with a short report specification. Use it as the acceptance criteria for the first report and every subsequent change.
| Decision | What to record |
|---|---|
| Purpose | Departmental expense review, revenue review, or another defined use |
| Organizational scope | Legal entity, department, cost center, and consolidation scope |
| Time basis | Current fiscal period, quarter, or fiscal year to date |
| Actuals basis | Posted ledger activity through a stated cutoff |
| Budget baseline | Original approved budget, revised budget, or forecast |
| Currency | Presentation currency and translation assumptions |
| Calculation rules | Dollar variance, percentage denominator, and sign convention |
| Accountability | Report preparer, reviewer, and explanation owners |
For an expense report, specify period activity, rather than inadvertently comparing budgeted expenditure with an account’s closing balance.
Keep the first version narrow: one entity, one department, one currency, and one completed period. Prove that comparison before adding consolidated views.
If commitments are useful, add them as separately labeled columns. Do not silently combine purchase commitments with posted expenses and call the result “actual.”
Practical check: Ask another accountant to describe the comparison using only the report header. If they cannot identify the baseline and period, improve the header before distributing it.
2. Select the budget baseline and verify its status
Avoid a column labeled simply “Budget” when readers could reasonably interpret it in different ways.
A useful management report can distinguish:
- Original approved budget: the initial performance baseline.
- Revised approved budget: the baseline after authorized changes.
- Forecast: the current expectation, kept distinct from the approved target.
Record the exact system model, version, or scenario behind each column. Do not assume those terms mean the same thing across ERP products.
In Dynamics 365 Finance, budget models classify budget entries, while budget codes identify transaction types such as original budget, transfer, and revision. Updating budget balances changes a budget register entry to Completed; Microsoft states that completed entries cannot be reopened for editing, so adjustments require another entry. A model name alone therefore does not describe the entire reporting baseline. (learn.microsoft.com)
For your report specification, document:
- The selected model or scenario.
- Which approved changes belong in the comparison.
- The treatment of incomplete entries.
- The effective date or reporting snapshot.
Common mistake: Replacing the original budget with a forecast and presenting the reduced variance as improved performance. Preserve separate columns when both comparisons are needed.
3. Align fiscal periods, account mappings, and dimensions
Build both sides at a common level of detail—for example:
Entity × fiscal period × main account × department × currency
If the budget exists only by department, aggregate actuals to that level. Do not create project-level budget comparisons unless an approved allocation supports them.
Check dates explicitly. In Dynamics 365 Finance, the budget account entry date determines its budget period, and period allocation keys work with the ledger’s fiscal calendar. An annual amount needs deliberate period allocation before it becomes a useful monthly comparison. (learn.microsoft.com)
For multiple entities, matching period numbers may not mean matching dates. Microsoft’s consolidation example shows June as period 12 for a July–June fiscal year but period 6 for a calendar-year entity. The reporting configuration must align the intended periods. (learn.microsoft.com)
Create a mapping register with:
- Source account or dimension value.
- Reporting category.
- Effective dates, where relevant.
- Mapping owner.
- Exception status.
Send unmatched values to a visible Unmapped category rather than dropping them. Test for overlapping mappings as well as missing ones: assigning an account to two additive detail rows can inflate totals.
Practical checks:
- Does every in-scope account have a destination?
- Are blank department values visible?
- Do budget and actual filters select equivalent populations?
- Did a new account, department, or reorganization change the mapping?
Keep historical comparison rules explicit when departments merge or responsibilities move.
4. Make variance formulas and signs unambiguous
For this guide’s expense example, display budget and actual expenses as positive amounts and use:
Dollar variance = Actual − Budget
Percentage variance = (Actual − Budget) ÷ Budget × 100
Under this convention:
- Positive expense variance = over budget, labeled unfavorable.
- Negative expense variance = under budget, labeled favorable.
Revenue requires a different interpretation: actual revenue above budget is generally favorable. Use explicit F/U labels rather than expecting readers to infer meaning from a minus sign.
Check the report writer’s behavior before applying another sign reversal. Sage Intacct documents separate “actual minus budget” and “budget minus actual” difference columns, plus normalized options. Its normalization uses the account group’s normal balance type. Do not assume a built-in normalized column behaves like a simple subtraction. (intacct.com)
Set these additional rules:
- Zero budget: show percentage variance as “N/A”; retain the dollar variance and flag unbudgeted activity.
- Missing budget: flag a data exception rather than silently converting it to zero.
- Negative budget: document the interpretation or suppress the percentage pending review.
- Totals: calculate percentage variance from total actual and total budget, not the average of row percentages.
Finally, test one known expense overspend and one known revenue shortfall. Confirm both the number and the F/U label.
5. Work through a departmental expense example
The following fictional example uses USD, one department, and one fiscal month. All expenses are displayed as positive amounts. These figures illustrate arithmetic and investigation steps; they are not benchmarks.
The first report appears to show an underspend:
| Expense category | Budget | Actual shown | Actual − budget |
|---|---|---|---|
| Software | $10,000 | $8,000 | −$2,000 |
| Travel | $5,000 | $3,000 | −$2,000 |
| Contractors | $15,000 | $18,000 | +$3,000 |
| Total | $30,000 | $29,000 | −$1,000 |
However, an independent ledger extract for the same department and period totals $33,000.
The $4,000 reconciliation difference leads to a newly created software expense account missing from the reporting map. Adding that account to the report changes the software actual to $12,000.
| Expense category | Budget | Complete actual | Variance | Variance % |
|---|---|---|---|---|
| Software | $10,000 | $12,000 | +$2,000 U | +20.0% |
| Travel | $5,000 | $3,000 | −$2,000 F | −40.0% |
| Contractors | $15,000 | $18,000 | +$3,000 U | +20.0% |
| Total | $30,000 | $33,000 | +$3,000 U | +10.0% |
The mapping correction changes the report—not the ledger.
Now investigate the complete figures. Assume supporting records establish that:
- Software includes a $2,000 charge budgeted for the following month.
- Travel is $2,000 below budget because a planned trip moved to the following month.
- Contractor spending includes $3,000 of additional work not in the budget.
The explanation reconciles:
$2,000 software timing − $2,000 travel timing + $3,000 additional contractor work = $3,000 unfavorable
The two timing items offset in the department total but still matter to category owners and next month’s forecast.
Do not move a posted expense merely to match budget timing. First assess whether its accounting date and treatment are correct under the organization’s applicable policies. If correct, explain the timing difference and monitor its expected reversal.
6. Drill from the variance to evidence and action
Use a repeatable investigation path:
Report category → main account → department or other dimension → ledger transaction → supporting document
For the budget, trace the comparison amount back to its budget entries. Dynamics 365 Finance documents inquiry paths to fiscal-period balances, budget account entries, and the general journal entries included in actuals. (learn.microsoft.com)
Set review thresholds according to your organization’s needs. Consider using both an absolute amount and a percentage, with separate review of unbudgeted activity and data-quality exceptions. No single threshold is appropriate for every business.
For each item selected for review, ask:
- Is the comparison complete and correctly mapped?
- Is the difference timing, scope, quantity, price, allocation, or another identified cause?
- What evidence supports that explanation?
- Will it reverse, recur, or change the forecast?
- Who will act, and by when?
Where quantities and rates are reliable, a simple bridge can separate their effects:
Quantity effect = (Actual quantity − Budget quantity) × Budget rate
Rate effect = Actual quantity × (Actual rate − Budget rate)
Use that bridge only where the underlying expense genuinely follows quantity × rate. Do not force fixed fees or mixed service arrangements into an unsupported model.
Replace “contractors over budget” with an actionable explanation: “Additional approved work caused the $3,000 unfavorable variance; the department owner will assess the remaining-period forecast before the next review.”
7. Validate and release a reproducible report
Use this release checklist:
- Actuals reconcile: Match report activity to an independent ledger inquiry with equivalent filters.
- Budget reconciles: Match the comparison column to the selected budget source and included changes.
- Scope matches: Confirm entity, accounts, dimensions, currency, and period boundaries.
- Mappings are complete: Review unmatched and overlapping assignments.
- Formulas pass tests: Recalculate representative rows, totals, zero budgets, and sign labels.
- Detail agrees: Confirm transaction detail adds back to selected report amounts.
- Explanations reconcile: Quantified causes add to the variance without unexplained residuals.
- Access is appropriate: Test the intended manager’s view, not only an administrator’s.
- The run is reproducible: Retain report parameters, generation time, mapping version, and reviewer approval.
For Dynamics 365 Finance currency comparisons, also check translation settings: Microsoft documents different exchange-rate-type setup locations for actual data and budget data. A common display currency does not by itself prove an equivalent rate basis. (learn.microsoft.com)
When validation fails, separate a report-definition correction from a ledger correction. Test mapping or formula changes in a copy, retain the prior definition, and rerun the reconciliation. Route accounting corrections through normal review and approval.
Release the report when it can answer three questions clearly: What changed, why did it change, and what happens next?