Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 14, 2026

Budget-versus-actual variance analysis in Excel explains why a business performed differently from plan. A dependable report aggregates Budget and Actuals to the same period, entity, department and account; reconciles Actuals to the closed ledger; and labels favorable and unfavorable movements consistently.

Quick answer: Dollar variance is Actual minus Budget. For positive expense amounts, a positive dollar variance is an overspend and therefore usually unfavorable. A positive revenue variance is generally favorable. Calculate percentage variances from aggregated totals, and display N/M rather than 0% when the budget is zero.

Below are a reconciled $100,000 budget-versus-$110,000 actual revenue example, working SUMIFS formulas, and a six-sheet model structure. The examples are illustrative, not verified accounting statements.

For a tailored budget and variance workbook, review the Budget and Forecast Build service: its Standard and Advanced tiers include variance calculation and dashboard deliverables, while Essential does not include a detailed variance dashboard.

Table of Contents

What is financial variance analysis in Excel?

Financial variance analysis compares approved budget assumptions with recognized actual financial results for a specific period and reporting level. Common comparisons include Budget vs Actual, Latest Forecast vs Actual, and prior period vs current period. Label them separately so managers know which benchmark is being evaluated.

For reports where revenue and expenses are both entered as positive values, raw dollar variance = Actual − Budget. For revenue, an increase is normally favorable; for expenses, an increase is normally unfavorable. Operating profit combines these movements, with expense effects changing sign.

Accounting basis matters: For an accrual-basis income statement, match closed general-ledger balances, not cash deposits or card/bank transactions alone, to the same definitions used by the budget. Check revenue recognition, unpaid expense accruals, prepaid costs, payroll obligations, noncash expenses, cutoffs and account reclassifications.

Use consistent reporting currency, legal entity, fiscal calendar, account classifications and department mapping. Preserve the original approved budget separately from updated forecasts. For material differences, identify whether the driver was price, volume, mix, timing, FX, operational changes or a data error.

Good variance analysis produces management questions and decisions; a larger unfavorable expense number does not by itself explain the operational cause, and a lower expense number is not necessarily a healthy outcome if service quality suffered.

Build the model: six Excel worksheets and reliable matching keys

Use Guide, Budget, Actuals, Account Map, Variance Detail and Dashboard. Within this layout, the Dashboard contains a department summary and QA/reconciliation indicators. A more complex production model can add staging, exception and audit-log sheets.

  1. Guide: Define the sign convention, reporting basis, currency, latest available periods and input responsibilities.
  2. Budget: Store the approved version by month, entity, department and reporting account. Include a BudgetVersion field if multiple approved snapshots exist.
  3. Actuals: Import recognized, closed-period ledger movements with original document/transaction IDs for traceability; map raw GL accounts to management reporting accounts.
  4. Account Map: Record the approved mapping from raw GL account to reporting account and account type (Revenue or Expense); track unmapped items.
  5. Variance Detail: Aggregate both datasets to a common key (Period + Entity + Department + Reporting Account), then calculate raw, favorable and percentage differences.
  6. Dashboard: Compare budget and actual operating profit; summarize flagged variances and reconcile underlying totals before circulation.

Do not join Budget and Actuals by unique transaction Line ID. One monthly budget row can correspond to dozens of ledger transactions. A transaction ID detects duplicates; the reporting key is what aligns aggregated budgets and actuals.

Include the union of budget and actual reporting keys so that newly incurred costs and discontinued accounts do not disappear. Validate unmapped accounts and unexpected departments rather than silently dropping them.

Use Excel Tables such as tblBudget and tblActuals with consistent dimension fields. Preserve source exports and reconcile the Actuals total back to the closed accounting system before describing a workbook as traceable or audit-supporting.

Exact Excel formulas: SUMIFS, favorable movement and percentage variance

Assume a structured tblBudget table and tblActuals table with columns Period, Entity, Department, Account and Amount USD. In the downloadable SAMPLE workbook, Variance Detail!E:H holds the common reporting key, column I holds account type, J holds Budget, K holds Actual, L holds raw variance, M holds percentage variance, N holds favorable movement, and O holds the review flag.

Budget, cell J6:

=SUMIFS(tblBudget[Amount USD],tblBudget[Period],$E6,tblBudget[Entity],$F6,tblBudget[Department],$G6,tblBudget[Account],$H6)

Actual, cell K6:

=SUMIFS(tblActuals[Amount USD],tblActuals[Period],$E6,tblActuals[Entity],$F6,tblActuals[Department],$G6,tblActuals[Account],$H6)

Raw dollar variance, cell L6: =K6-J6. Percent variance, cell M6:

=IF(J6=0,"N/M",L6/ABS(J6))

The denominator uses the absolute amount of the budget as a consistent deviation convention; discuss signs separately for revenue and expenses. If the budget is zero, a percentage movement is not meaningful and is explicitly labeled N/M rather than displayed as 0%.

Favorable movement, cell N6:

=IF(I6="Revenue",L6,-L6)

These examples assume revenue and cost inputs are both positive magnitudes. If a source ledger stores credit revenue as negative, normalize its reporting signs consistently before applying them. Do not reverse the same signs twice.

Worked example: monthly operating profit variance

Suppose the company budgeted revenue of $100,000, payroll costs of $40,000 and rent of $10,000, and the actual accounting results are $110,000, $45,000 and $9,000, respectively.

Fictional monthly management P&L · USD
Account Budget Actual Raw variance Favorable movement
Revenue $100,000 $110,000 +$10,000 +$10,000
Payroll (expense) $40,000 $45,000 +$5,000 −$5,000
Rent (expense) $10,000 $9,000 −$1,000 +$1,000
Operating profit $50,000 $56,000 +$6,000 +$6,000

Reconciliation: Revenue favorable $10,000 − payroll unfavorable $5,000 + rent favorable $1,000 = +$6,000 operating profit variance. Percentage of budget profit: +$6,000 ÷ $50,000 = +12.0%. This example excludes other costs, income, tax and financing items.

Do not sum the raw Actual − Budget differences across mixed revenue and expense accounts to derive a profit movement. Sum the consistently signed favorable movement values or compute the difference between actual and budget profit directly.

PivotTable comparison caveat

Excel PivotTable Show Values As → Difference From and % Difference From compare items of an existing field and base item. They are suitable if Budget and Actual share a normalized Scenario field in a combined dataset. With separate Budget and Actual source tables, use SUMIFS or a defined data-model measure instead of assuming PivotTable period comparisons will calculate plan variances automatically.

Review rules, reconciliation and exception handling

Before a monthly variance meeting, verify that the Actuals data reconciles to the closed general ledger and that the report uses the approved budget version, reporting period, entity, department, currency and account mappings. A working SUMIFS formula does not make a report audited or audit-ready by itself.

On the Variance Detail table, include period, department, account, budget, actual, dollar variance, percentage variance, favorable amount, review flag, owner, explanation, action and status. Keep explanations and decisions linked to each exception; never replace underlying source figures with manual values in the dashboard.

Materiality: use two independently configurable thresholds. Flag a line if its absolute dollar variance reaches the dollar threshold OR its absolute percentage difference reaches the percentage threshold. Use explicit overrides for zero-budget items, unexpected new accounts, control failures and high-risk categories. An AND-only rule can miss large absolute differences on big accounts.

In the downloadable SAMPLE workbook, Dashboard!G6 contains the editable dollar review threshold and Dashboard!G7 contains the editable percentage threshold. The review-flag formula in Variance Detail!O6 is:

=IF(OR(ABS(L6)>=Dashboard!$G$6,IF(ISNUMBER(M6),ABS(M6)>=Dashboard!$G$7,FALSE)),"Review","OK")

When the budget is zero, escalate nonzero actual activity separately rather than letting a percentage formula silently return zero. When the budget is negative or profit crosses zero, use explanatory measures alongside the percent movement to prevent misleading favorable/unfavorable readings.

Use conditional formatting for the variance review status, not color alone: include a text label and meaningful owner/action notes. Retain original versions and monthly source exports, and track reconciliations, changes and approvals.

For further budgeting context, see Driver-Based Budgeting and our 13-week cash-flow guide.

What the Excel variance report should show managers

Begin with a reconciled operating-profit bridge rather than a dashboard of unrelated charts. In the worked example, actual operating profit is $56,000 compared with $50,000 budget; management should investigate the payroll overspend even though the overall profit movement is favorable.

Illustrative variance dashboard · monthly P&L · USD

Budget profit

$50,000

Actual profit

$56,000

Favorable change

+$6,000

Revenue +$10,000 favorable  ·  Payroll −$5,000 unfavorable  ·  Rent +$1,000 favorable.
Fictional example. Not a live financial system or verified client result.

Report budget/actuals by entity and department, plus the five or fewer most decision-relevant exceptions for management to review. A responsible owner should document the cause, planned action and follow-up period. If a cost is lower because necessary work was postponed, record that nuance rather than calling it an unqualified success.

What makes a variance workbook reviewable

Use source-traceable transactions, consistent reporting keys, documented formulas, account mapping, a defined close process and recorded approvals. Protect formula cells, distinguish editable inputs, and keep the variance threshold assumptions visible. Audit-ready should be reserved for a properly evidenced, controlled process; an attractive spreadsheet by itself does not establish audit compliance.

Review each material exception with the relevant department owner. Effective finance reporting is not the largest possible workbook; it is reliable numbers with a clear route from unexpected variance to management action.

Frac CFO: choose a suitable budget and reporting service

Budget and Forecast Build: The Essential tier provides a defined annual budget and monthly forecast. The Standard and Advanced tiers include budget-versus-actual structure, variance calculations and dashboards within their published line, driver and scenario limits. The Essential tier does not include a detailed variance dashboard. Confirm the chosen tier before ordering.

Separate forecasting needs: The Three-Statement Financial Model Excel Template supports multi-year projections and scenarios; it is not presented here as an automated monthly close or budget-variance system. The Inventory & Budget Tracker is for practical inventory and expense controls, not necessarily a GL variance report.

Recurring decisions: If management wants continuous forecast review, KPI interpretation and finance support, the GROWTH Plan may be appropriate. Confirm exactly which ongoing reporting and finance deliverables are included rather than assuming an unlimited build or onboarding service.

Need your own budget-versus-actual variance workbook?

Choose the published Budget and Forecast Build service or explore One-time Projects for a separately scoped model using your chart of accounts, approval process and reporting requirements.

Review budget and forecast packages

Frequently asked questions: budget-versus-actual variance analysis

How do I calculate budget versus actual variance in Excel?

First sum budget and actual financial amounts to the same period, entity, department and reporting account. Then use =Actual-Budget for raw dollar variance. For percent difference use =IF(Budget=0,"N/M",(Actual-Budget)/ABS(Budget)). The favorable direction depends on the account type.

How can I tell whether a variance is favorable or unfavorable?

With revenue and expense inputs expressed as positive figures, above-budget revenue is usually favorable and above-budget cost is usually unfavorable. A favorable movement formula is =IF(AccountType="Revenue",Actual-Budget,Budget-Actual). A favorable cost movement might still reflect delayed spending, so review the operational reason.

What do I do when the budget is zero?

The percentage variance is mathematically undefined when the denominator is zero. Display N/M or a separate “new/unbudgeted activity” status and use the actual dollar amount for exception review. Do not silently return 0% with IFERROR(...,0).

How do I reconcile actuals to the general ledger?

Compare totals by period, entity, account and department to a closed trial balance or approved management P&L; investigate missing accounts, sign differences, accruals, cutoffs, currency conversion and reclassifications. Keep source exports and approval evidence. A bank statement alone does not substitute for an accrual-basis ledger.

What is the difference between budget variance and forecast variance?

Budget variance measures actuals against the approved plan. Forecast variance measures actuals against a dated projection based on more recent assumptions. Retain both comparison versions; overwriting the original budget removes the ability to evaluate the initial plan.

How should I choose variance review thresholds?

Choose context-appropriate absolute-dollar and relative-percentage thresholds, and flag when either threshold is reached. Add mandatory exceptions for unbudgeted items, reconciliation failures or control-sensitive categories. Set ownership and an explanation deadline for each flagged line.

Do I need PivotTables to calculate variance analysis?

No. SUMIFS can roll separate Budget and Actual tables up to common keys. PivotTable Difference From features work when a suitable base field and items exist, such as Budget versus Actual in a normalized Scenario column.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources and further reading

Examples in this article are fictional and do not constitute audited financial statements or guaranteed outcomes for a particular business.

7 Responses

Leave a Reply