Skip to content
allanninal.dev/build
Collaborate

Part 1 of 7 · Budget variance reporter series ~6 min read

A budget variance reporter on AWS for a few dollars a month

Every business that budgets produces a monthly pack showing budget, actual and variance across forty-odd lines, and every business that produces one knows the same thing: almost nobody reads it properly. Not because it is uninteresting, but because forty numbers with no indication of which three matter is not information, it is a search task. This post walks through a small system that does the searching.

a kitchen with stainless steel appliances
Photo by Eiliv Aceron on Unsplash

Key takeaways

  • One input: a nominal transaction export per period.
  • Most variance is timing. Separating it out is the single highest-value step.
  • Only three or four lines are reported, with the transactions behind each one.
  • It never adjusts a budget, reclassifies a transaction or explains a number away.
  • Designed on AWS for about $2 a month.

The whole system on one page

Before any code, here is the shape of what we are designing.

System: actuals and budget in, a short variance report outThree boxes across the top sit outside the AWS account. On the left, Actuals: the nominal transaction export produced monthly. In the middle, Budget: a sheet holding the budget by line and by month. On the right, Whoever reviews: the person who receives three or four lines rather than forty. Each connects by an arrow to the AWS account container below. Transactions flow down into the account. The budget feeds in as what was expected. A short report goes back out. Inside the AWS account are three components in a row. On the left, the Comparer, which works line by line for the month and year to date. In the middle, the Timing filter, which asks whether a variance is real or just a matter of when something landed. On the right, the Reporter, which surfaces the few that matter with the transactions behind them. A note at the bottom says the middle box is the whole value and everything else is subtraction.AWS ACCOUNTActualsnominal export, monthlyBudgeta sheet, by line and monthWhoever reviewsthree or four linesComparerline by line,month and year to dateTiming filteris it real, orjust when it landedReporterthe few that matter,with the transactionstransactionswhat was expecteda short reportThe middle box is the whole value. Everything else is subtraction.
Fig 1. Two inputs, three pieces. The comparer is arithmetic anyone could do; the timing filter is the part that turns forty variances into three worth discussing.

What you set up once (the outside)

  • An actuals export. A nominal transaction listing per period from whatever you use — account code, date, amount, description, source document. Every accounting package can produce this on a schedule, and the transaction level matters: a trial balance alone gives you the variance but not the reason.
  • A budget sheet. One row per account code, one column per month. Most small businesses budget annually and divide by twelve, which is exactly the practice that generates most of the false variances this system exists to filter. Part 3 is about doing better than that cheaply.
  • Two thresholds. The percentage and the absolute amount a variance has to clear before it is worth reporting. Both are needed: a forty per cent overspend on a sixty-pound line is noise, and a three per cent overspend on a payroll line is not.

What runs each period (the inside)

  • The comparer. Budget minus actual, per line, for the month and for the year to date. Both matter and they frequently disagree: a line can be twenty per cent over for the month and on budget for the year, which is a timing story, and the reverse, which is a trend nobody has noticed.
  • The timing filter. Asks whether each variance is explained by when things landed rather than by what happened. Four tests, covered in Part 4, and between them they usually remove two thirds of the lines that clear the thresholds.
  • The reporter. Takes what survives, pulls the transactions that make up each variance, and writes a short report. The transactions are the point: “repairs over by £2,400” is a question, and “repairs over by £2,400, of which £2,150 is one invoice for the compressor” is an answer.

One period, end to end

One reporting period from export to report, in five stagesA horizontal row of five boxes joined by arrows. Exported: the nominal transactions arrive. Compared: forty lines, of which eleven are over threshold. Filtered: seven of those are timing differences. Explained: the transactions behind the rest are pulled. Reported: four lines on one page. A note says forty lines in, four out, and the third stage is where most of them go.ONE PERIOD, END TO ENDExportednominal transactionsCompared40 lines, 11 overFiltered7 are timingExplainedtransactions pulledReported4 lines, one pageForty lines in, four out. The third stage is where most of them go.
Fig 2. The same system as one line. The interesting number is the drop from eleven to four, which is entirely the timing filter’s work.

In plain words

July closes. The export lands on the 4th. Forty account lines, of which eleven are outside the thresholds. The timing filter looks at each: insurance is over by the full annual premium because it was budgeted in August and paid in July — timing. Subcontractors is under by nine thousand because two invoices have not arrived yet, which the filter knows because the same two suppliers invoice every month and did not this month — timing. Seven of the eleven go the same way.

Four survive. Repairs is over by £2,400, and the transactions show £2,150 of that is one compressor invoice. Fuel is over by eleven per cent for the third consecutive month, which is the only line in the report with no single transaction behind it and is therefore the interesting one. Two others are similar. The report is one page, arrives on the 5th, and gets read — because four lines with reasons attached is a conversation and forty lines with variances is a spreadsheet.

Design rules that shaped every decision

  • Report few things. A report with forty lines on it is a spreadsheet, and spreadsheets do not get read.
  • Separate timing from real. Most variance is timing, and reporting it trains people to ignore the report.
  • Always attach the transactions. A variance without its transactions is a question; with them it is usually an answer.
  • Month and year to date, always both. They disagree constantly and the disagreement is the signal.
  • It never changes a number. No reclassification, no accrual, no budget adjustment. Those are an accountant’s decisions.
  • A line with no single transaction behind it is more interesting than one with a big invoice in it, and the report should say so.

Why this shape

The management accounts problem is not a data problem. The numbers are correct, produced on time, and complete. The problem is that a forty-line variance table has no priority in it, and constructing that priority takes an hour of somebody’s judgement every month — which means it happens for three months after somebody insists on it and then stops.

So the design spends nothing on presentation and everything on selection. It knows which of the forty lines are worth a sentence, it knows why, and it can show the transactions. That is the hour, done in four seconds, on the fifth of every month, forever.

The next four posts walk through each piece: how the period data arrives, how the budget gets shaped, how timing gets filtered out, and what the report says. One diagram per post, a cost breakdown, and an engineering reference at the end.

All posts