Forecast vs Actual Budget Variance: Favorable (F) vs Unfavorable (U)

Choose and document the reporting convention

This workbook uses positive variance for a favorable arithmetic contribution to profit and negative variance for an unfavorable one. Other reports use actual minus budget for every line and interpret signs separately; neither display convention is universal.

Expense amounts must be entered as positive costs here. Expenses use budget minus actual; Revenue and Profit use actual minus budget. If your ledger exports costs as negative signed amounts, normalize them first or use a deliberately different formula. Do not invert them twice.

Compare the same period, currency and accounting scope. These are simple total-line differences, not price/volume decompositions or a flexed-budget performance assessment. Spending less can reflect delayed work or lower output; a favorable label alone does not establish good management.

Amount variance with validated account type

E2 accepts Expense, Revenue or Profit after trimming spaces and ignoring case. Unknown types and missing/non-numeric amounts produce messages instead of silently being treated as revenue. Copy the formula down for the other rows.

=IF(COUNT(C2:D2)<>2,"Enter numeric amounts",IF(UPPER(TRIM(B2))="EXPENSE",IF(OR(C2<0,D2<0),"Use positive expense amounts",C2-D2),IF(OR(UPPER(TRIM(B2))="REVENUE",UPPER(TRIM(B2))="PROFIT"),D2-C2,"Unknown account type")))

Percentage relative to the magnitude of the budget

F2 divides the amount variance by ABS(budget). It is a fraction: 0.07 displays as 7% with percentage formatting. Outputs retain precision. A zero budget has no defined relative percentage, even when actual also equals zero; the amount and status remain available.

For Profit moving from -50000 to 10000, the improvement is 60000, or 120% of the absolute budget magnitude. This is an explicitly defined relative-improvement metric, not a conventional growth rate or profit margin. Near-zero budgets can create very large percentages.

=IF(NOT(ISNUMBER(E2)),E2,IF(C2=0,"Zero Budget Base",E2/ABS(C2)))

Separate favorable, unfavorable and on-budget labels

G2 uses the unrounded amount difference to classify the row. Exact zero is On budget. If the report needs a materiality threshold or currency rounding, define that policy explicitly before labeling rows.

The three worked rows show Expense -18000 and -9%; Revenue +35000 and +7%; Profit +60000 and +120%. Do not sum Profit subtotals with their underlying revenue/expense lines: that double-counts the same activity.

=IF(NOT(ISNUMBER(E2)),E2,IF(E2>0,"Favorable",IF(E2<0,"Unfavorable","On budget")))

Further reading

• Microsoft Support — ABS function
• ACCA — Performance analysis and variance interpretation