Budget vs actual: how to spot variances before the year is out

Almost every company has a budget. Far fewer know how they are performing against it – not at year end, but right now.

The reason is simple: the budget lives in Excel, the actuals sit in the accounting system, and to line them up someone has to combine two spreadsheets by hand. That job gets done at the end of the quarter, not every month. So a variance is usually discovered only when all anyone can do is note it.

Why budget tracking always runs late

Not because of laziness. Because of three technical issues:

Different structures. The budget is drawn up in broad terms – “marketing”, “transport”, “administration”. The actuals sit in the accounting system at general ledger account level, where the same costs may be spread across six lines. Until there is a mapping table, a comparison is impossible.

The budget changes. A revised version appears mid-year, while the old one stays on someone’s computer. The question “which budget are we comparing against?” has no single answer.

Manual work doesn’t repeat itself. A reconciliation done in February doesn’t redo itself in March. It has to be done again – and again.

What “comparing budget with actuals” means in practice

Not one number set against another. A useful comparison has three views:

• Total – how the company as a whole is tracking against the annual plan, year to date.
• Monthly – whether a variance is building up or was a one-off. One expensive month and a budget that is consistently overrun are different problems.
• Detailed – the specific cost category, department or project where the variance arises.

Without the third view, the report tells you there is a problem, but not where it is.

Three variances worth tracking every month

Revenue variance. Whether sales are hitting plan and, if not, whether it is down to volume or price. These are two different answers and call for two different decisions.

Cost variance by category. The total often looks respectable because one category is over plan while another is under. Added together, they mask each other.

Margin variance. Sales can be on plan while profit is not, if more discounts were given or cost of sales went up. This variance is noticed last and costs the most.

Where the data comes from

Actuals come straight from the accounting system. Rivilė reports and Finvalda reports draw on the same data source normally used for the profit and loss view: general ledger account turnover tagged by department and project.

The budget almost always stays wherever it is convenient to plan – in Excel or a Google Sheet. And that is fine: a business analytics system shouldn’t force the finance team to plan any differently from how they prefer. Only two agreements are needed:

• a fixed file structure – the same columns every month, so the link doesn’t break;
• a mapping table between budget lines and general ledger accounts – set up once.

After that, the data is combined in the model rather than by hand. Automated reports refresh along with the accounting data, so budget performance can be checked on any day of the month, not only after month-end close.

What the manager sees as a result

Once the model is set up, management reports answer questions that used to require a separate request to the accounts department:

• what percentage of the annual plan has been achieved and whether the pace is sufficient;
• which cost categories are over budget and which have headroom;
• how the variance has changed over recent months – growing or shrinking;
• which departments are sticking to plan and which are dragging it down;
• and all of this without a single spreadsheet combined by hand.

Business analytics doesn’t invent anything here. It simply stops the answer about July’s variance arriving in September.

If you would like to see what this kind of comparison looks like in practice, take a look at our Power BI report examples – the budget vs actual view is included in the revenue and cost analytics reports.