The quarterly spreadsheet
Every quarter, the RFO exports a trial balance or a detailed transaction list from the accounts package. Then the real work begins: pasting it into last quarter's spreadsheet, fixing formulas that broke because a new budget code was added, working out what has been ordered but not yet invoiced, and adding notes for anything over or under. Councillors want it by cost centre, with a column for the annual budget, spend to date, the percentage, and a comment.
The finance committee then asks why the grounds maintenance line is so high. The RFO knows it is because the contractor's invoice covers two quarters, but that is not visible on the sheet. Someone asks how much is left in the earmarked reserve for the pavilion roof, which is on another spreadsheet.
By the time the answers have gone round by email, the next quarter is half over. The same thing happens in January when the budget for next year is being drafted and the precept has to be agreed: the RFO produces a forecast for the rest of the year from the same spreadsheet, adjusting by hand for known invoices still to come, and councillors debate figures that are already several weeks old.
Why the report is rebuilt every time
The accounts package holds accurate figures but does not lay them out the way councillors want to see them. The spreadsheet adds the layout, but it has to be refilled by hand, and its formulas are fragile. Committed spending, timing differences and earmarked reserves live outside both.
- Exports from the accounts package are pasted in and reformatted.
- New budget codes break the spreadsheet's formulas.
- Orders placed but not invoiced are tracked separately, if at all.
- Earmarked reserves are on a different sheet.
- Explanations for variances are typed fresh each quarter.
What the manual report costs
The RFO spends hours on reformatting that adds nothing, time a part-time officer can rarely spare. Mistakes in pasted figures are easy to make and hard to spot. Councillors see spending late, so the budget-setting discussion for next year starts from partial information. And because the report is a snapshot, questions at the meeting often need a follow-up email.
How the council budgets, reserves and reports is for your RFO, councillors and auditor. We build the report around the way you already work.
A budget report that fills itself
- Your budget is loaded by cost centre and code, including any profile across the year if you use one.
- Actual income and spending are read from your accounts package, by export or by connection where the package allows.
- Committed spending is pulled from purchase orders or approved quotes that have not yet been invoiced.
- Earmarked reserves are shown with their purpose, opening balance, movements and remaining amount.
- The report lays this out by cost centre: budget, actual to date, committed, remaining, and a comparison with where you would expect to be by now.
- Lines outside a tolerance you set are highlighted, and the RFO adds a short note that carries forward until it is cleared.
- The report is produced as a PDF for the agenda pack, and councillors can open a live view to drill into a line and see its transactions.
| Question from the committee | Where the answer is |
|---|---|
| Why is grounds maintenance high? | Drill into the line: two quarterly invoices, with the RFO's note |
| How much is left for the pavilion roof? | The earmarked reserve with its movements |
| What have we committed but not paid? | The committed column, from orders |
| Are we on track overall? | Totals against the profiled budget |
What the quarter looks like now
The RFO refreshes the report, reads the highlighted lines, adds two notes and sends the PDF for the agenda. At the meeting, when the grounds maintenance question comes up, the answer is in the note. The pavilion reserve is on the same page. When budget setting starts, councillors are looking at up-to-date actuals rather than figures from the last export.
The notes the RFO writes stay attached to their lines until cleared, so the story of a variance carries across meetings instead of being rewritten each time. If the RFO is away, the chair or clerk can produce the same report from the same screen.
Does this sound like your budget reports?
- The quarterly report is rebuilt in a spreadsheet by hand.
- Formulas break when budget codes change.
- Committed spending is not shown.
- Earmarked reserves are on a separate sheet.
- Committee questions need follow-up emails.