Budget vs actual variance by department

Brackenfold's total stayed under budget for five months, while Marketing ran £26,202 over by May and £38,918 by June.

The company and data are fictional.

Against budget by department, January to June.
  • Marketing£38,918
  • Legal£4,861
  • Sales£4,230
  • Finance£3,968
  • Training-£3,500
  • Engineering-£5,871
  • Customer support-£15,616
  • Facilities-£17,098

Why can a budget look fine while one team overspends?

Ines Marlow is finance lead at Brackenfold Software, a firm of eight departments. A budget vs actual report compares what each one planned to spend with what it spent. The gap is the variance.

Brackenfold's spending so far this year, at each month-end. The total looked fine every time.

Month-endBudget to dateSpent to dateAgainst budget
January£257,000£253,390£3,610 under
February£514,000£504,443£9,557 under
March£771,000£767,054£3,946 under
April£1,028,000£1,026,980£1,020 under
May£1,288,500£1,285,991£2,509 under

For five month-ends the total came in under, so nobody asked. Then June closed £9,892 over, just 0.6%.

The chart above pulls that total apart. Marketing is £38,918 over. Facilities and Customer support are about £32,700 under. A total lets them cancel out, though nobody decided those savings should pay for Marketing.

So Ines stops asking how far off the total is. Who does she talk to, and about what?

The report at each month-end

Ines builds the report as a Strata flow that ends in a Vista dashboard. Move Books through to pick a month-end. Include accruals adds costs Brackenfold owes but hasn't yet entered in the ledger. A minus means under budget.

Skip the interactive dashboard

Loading the interactive dashboard. The figures below don't depend on it.The interactive dashboard didn't load. The figures below don't depend on it.

What each setting shows. Each figure is the year so far.

SettingTotalBiggest overspendWhat changes
January£3,610 underSales, £1,712Marketing isn't first yet.
May£2,509 underMarketing, £26,202Finance is only £168 over, so there's nothing to ask it.
June£9,892 overMarketing, £38,918The total flips. Finance jumps to £3,968 on one month's professional fees.
June with accruals£18,413 overMarketing, £45,331Sales, at £6,338, passes Legal for second.

How to build a budget vs actual report

The flow totals the ledger, matches it to the budget and sizes each gap. Follow Marketing in June.

  1. Keep what's in the ledger, then total by department and month.

    Accruals wait at the Filter until you turn them on. Accounts collapse into one line.

    Filter, Summarize

    Marketing, June. A further £6,413 waits as an accrual.

    SalariesPaid mediaTotal
    £12,600£42,117£54,717
  2. Match to budget and keep rows with no partner.

    Legal has spend but no budget. Training has budget but no spend. Both stay.

    Join, Union

    A one-way match loses one of them and gets the wrong total.

    MethodTotal over
    Match from the budget only£5,032
    Match from the ledger only£13,392
    Keep both sides£9,892
  3. Size the gap.

    A Formula works out each gap, then a Filter stops at the chosen month-end.

    Formula, Filter

    Marketing, June.

    BudgetActualOverOf budget
    £42,000£54,717£12,71730%

Who Ines talks to and what she asks

The order is Ines's judgment.

Talk toAskBecause
1. MarketingWhat is this spend buying?Paid media grew every month, while salaries stayed flat.
2. FinanceWas June's fee expected, and will it come back?It's one month on one account.
3. Facilities, Customer supportIs the saving real, and can the budget move?It hides most of Marketing.

If Marketing stays £12,717 over each month, that's about £76,000 more by December.

Marketing's paid media is the overspend. CAC by channel, about a different fictional company, asks what spend like that buys.

How we built it

Budget

37 budget lines, one per department and month.

Required columns: departmentmonthbudget

Ledger

73 posted rows and 2 accruals, by account, from your accounting system.

Required columns: departmentmonthaccountstatusamount

See the steps run

Select Join and look at its two side outputs: Legal leaves on one and Training on the other, and the Union brings them back.

Skip the interactive flow

Loading the interactive flow. The steps above don't depend on it.The interactive flow didn't load. The steps above don't depend on it.

Left to right: the two exports and the accruals control, the posted filter and monthly total, the join and union, then the formula and the month-end filter that feed the dashboard.

The formulas in this flow

ColumnExpression
Keep from the ledgerstatus = 'posted' OR params.accruals
Actualround(coalesce(actual, 0), 2)
Against budgetround(actual - budget, 2)
Percent of budgetround(100.0 * (actual - budget) / nullif(budget, 0), 1)
Books throughCAST(month AS DATE) <= CAST(params.through AS DATE)

What the data can't show

The ledger shows where the money went but can't say why. Paid media could be a deliberate push, and only Marketing can say.

On posted spend, Legal is the second-biggest overspend. Ines still doesn't lead with it: one unbudgeted bill isn't a growing account.

Neither accrual touches Facilities or Customer support, so accruals don't explain those underspends. Invoices nobody has accrued wouldn't show here at all.

The budget is flat by month. A budget phased for the season would change which months look like drift.

We wrote these patterns into the sample, so test the method on your own data.

Definitions

Variance
Actual minus budget, shown in money and as a percentage of budget.
Posted
Entered in the ledger.
Accrual
A cost you owe but haven't yet entered in the ledger.
Line
One department in one month, across its accounts.
Overspend and underspend
Every over-budget line added up, and every under-budget line added up, with no canceling out.
How it ties out
Budget £1,545,500 plus overspend £57,030.03 minus underspend £47,137.82 equals £1,555,392.21 actual. That includes £4,860.50 with no budget and £3,500 of budget with no spend. Adding the £8,521.15 of accruals takes the net from £9,892.21 to £18,413.36.

FAQ

What is a budget vs actual report?
A comparison of planned and real spending, usually by department and month, with the difference shown as a variance in money and as a percentage of budget.
What is a good variance threshold?
There is no standard one. Many teams use a percentage with a money floor, because a percentage alone flags small lines and misses large ones in money terms. Try several and keep the setting that gives a list people will really read.
Should accruals be included?
For the true cost of the period, yes, and most closes journal them before reporting. If yours aren't posted yet, tie out to the ledger first, then add them and say which figure you're showing. Here they nearly double the net.
What won't it do?
It shows where to ask but can't say why the money moved. It doesn't replace your ledger or your judgment about whether an overspend was worth it.

Build yours from your exports

Bring your budget and ledger exports. Local runs are free.