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.
- 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-end | Budget to date | Spent to date | Against 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.
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.
| Setting | Total | Biggest overspend | What changes |
|---|---|---|---|
| January | £3,610 under | Sales, £1,712 | Marketing isn't first yet. |
| May | £2,509 under | Marketing, £26,202 | Finance is only £168 over, so there's nothing to ask it. |
| June | £9,892 over | Marketing, £38,918 | The total flips. Finance jumps to £3,968 on one month's professional fees. |
| June with accruals | £18,413 over | Marketing, £45,331 | Sales, 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.
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.
Salaries Paid media Total £12,600 £42,117 £54,717 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.
Method Total over Match from the budget only £5,032 Match from the ledger only £13,392 Keep both sides £9,892 Size the gap.
A Formula works out each gap, then a Filter stops at the chosen month-end.
Formula, Filter
Marketing, June.
Budget Actual Over Of budget £42,000 £54,717 £12,717 30%
Who Ines talks to and what she asks
The order is Ines's judgment.
| Talk to | Ask | Because |
|---|---|---|
| 1. Marketing | What is this spend buying? | Paid media grew every month, while salaries stayed flat. |
| 2. Finance | Was June's fee expected, and will it come back? | It's one month on one account. |
| 3. Facilities, Customer support | Is 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.
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
| Column | Expression |
|---|---|
| Keep from the ledger | status = 'posted' OR params.accruals |
| Actual | round(coalesce(actual, 0), 2) |
| Against budget | round(actual - budget, 2) |
| Percent of budget | round(100.0 * (actual - budget) / nullif(budget, 0), 1) |
| Books through | CAST(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.