How to Build a Budget vs Actuals Model in Excel

A budget versus actuals model puts the plan and the result side by side and quantifies the gap. The headline is variance, which is actual minus budget, expressed in dollars and as a percentage. The harder part is labeling each variance favorable or unfavorable correctly, since the sign flips between revenue and cost lines. It is the workhorse report of FP&A.

What a budget vs actuals model does

The model compares what you planned to what happened, line by line. For each account it shows the budget, the actual, the dollar variance, and the percent variance. The point is not just to measure the gap but to explain it, so each material variance carries driver commentary.

It fits the reporting cycle as the bridge between the forecast and reality. Revenue, cost of goods, operating expenses, and headcount each get a variance row, and the favorable or unfavorable flag tells a reader at a glance whether the gap helped or hurt the bottom line. Management reviews the largest variances and the commentary behind them.

Build it step by step

Lay out four columns per period: budget, actual, dollar variance, and percent variance, with a flag column for favorable or unfavorable. List accounts down the rows grouped into revenue, costs, and operating expenses.

Compute the dollar variance first, then derive the percent and the flag from it.

1. Dollar variance is =actual - budget for every line.
2. Percent variance is =(actual - budget) / budget, guarded for a zero budget.
3. For revenue lines, flag favorable when actual > budget: =IF(actual>=budget,"Fav","Unfav").
4. For cost lines, flip the test: =IF(actual<=budget,"Fav","Unfav").
5. Add a commentary column for the driver of each material variance.

=1100-1000 is a 100 favorable revenue variance because actual beat budget.

The formulas and the favorable logic

Variance arithmetic is simple: =actual - budget in dollars and =(actual - budget) / budget in percent. Wrap the percent in =IF(budget=0, "n/a", (actual-budget)/budget) so a zero budget does not throw a divide error.

The favorable logic is where models go wrong, because the sign reverses between revenue and cost. On revenue, beating budget (actual above budget) is favorable. On any cost or expense line, coming in below budget (actual below budget) is favorable. A single formula cannot serve both, so either drive the flag from an account-type column with =IF(account_type="Revenue", actual>=budget, actual<=budget) or keep separate revenue and cost flag formulas. Net income variance follows the revenue convention: higher actual than budget is favorable.

• Dollar variance: =actual - budget.
• Percent variance: =IF(budget=0,"n/a",(actual-budget)/budget).
• Revenue flag: =IF(actual>=budget,"Fav","Unfav").
• Cost flag: =IF(actual<=budget,"Fav","Unfav").

Pitfalls and what reviewers check

The signature error is a uniform favorable formula applied to every line, which mislabels every cost variance. A cost that runs over budget is unfavorable, but a single actual > budget test marks it favorable. Drive the flag from account type so the logic flips correctly between revenue and cost rows.

Reviewers check that the dollar variances foot to the subtotal variances, that the percent variance handles a zero or negative budget gracefully, and that favorable and unfavorable flags read correctly on a sample of revenue and cost lines. They also look for budget and actual figures hardcoded inside the variance formula instead of referenced from the source columns, because that breaks the audit trail and hides where a number came from.