Budget versus actual spending

The monthly budget versus actual spending report is a recurring financial governance task produced by a department head and two case officers ahead of each council meeting. The process runs twelve times a year and currently takes around seven hours per cycle. Its purpose is to give councillors a clear picture of where actual spending stands against the approved budget, drawn from the general ledger and a handful of supporting data sources.

Each cycle begins when the council meeting date approaches. A case officer manually pulls data from the general ledger and fills gaps through email exchanges and separate spreadsheets. All of this material is then consolidated by hand in Excel, variances are calculated, and narrative commentary is added to explain significant deviations. The department head reviews the assembled report, approves it, and passes it to councillors before the meeting. The output must be accurate, auditable, and defensible under administrative procedure law, including the possibility of appeal.

The process is well understood and follows a consistent structure each month, but it carries real compliance weight. Decisions about which variances are material, how to handle upstream data errors, and whether anything needs escalation before publication all require human judgement. Those judgement calls will remain with the department head regardless of how the routine assembly work is automated.

The core recommendation is Traditional Code: a scheduled Python script that extracts data from the general ledger and any other programmatically accessible sources, calculates the budget versus actual variances, and pre-populates the existing Excel report template. The script handles the mechanical assembly work that currently consumes the bulk of the seven-hour cycle. A human review step for exceptions and a final sign-off by the department head are preserved as explicit handover points, not optional extras.

The case for Traditional Code over the other paths comes down to proportionality and fit. The data involved is structured and numerical. The variance calculation rules are stable and well understood. The output format does not change from month to month. These are the exact conditions under which a well-written script is more reliable, more maintainable, and far easier to audit than any AI-assisted approach. There is no unstructured text to interpret, no ambiguous intent to resolve, and no benefit to machine learning at a volume of twelve runs per year. Adding AI orchestration or an autonomous agent to this process would introduce complexity and explainability risk without solving any problem that actually exists.

The client has no stated technology preference and operates under central IT governance and public-sector procurement rules. That constraint actually makes the Traditional Code path more attractive, not less. A Python script using standard libraries such as pandas, openpyxl, and requests for open-data endpoints requires no new software procurement, no vendor relationship, and no specialist tooling. It can be scheduled on existing infrastructure and handed to central IT as a documented, maintainable asset. Any competent developer can read, modify, and extend it without needing to understand a proprietary platform.

The Hybrid path scored just below the recommended option and deserves an honest mention. In practice, what Hybrid describes is essentially the same as Traditional Code with a more formally documented review gate. The recommended script already flags exceptions for human attention and produces a clear audit trail of what was automated. If the team wants to formalise that handover as a named workflow step with its own log entry, that is a sensible addition and adds almost no build complexity. The distinction between Hybrid and Traditional Code here is mostly a matter of how explicitly the review gate is documented rather than a fundamental architectural difference.

RPA was assessed as viable but not recommended as the primary approach. It becomes relevant only if the ledger or another key source turns out to have no export capability and must be accessed through a user interface. That is a real possibility in a central IT environment where programmatic access is unconfirmed, and the implementation roadmap treats a data access audit as the critical first step for exactly this reason. If that audit reveals that screen-level automation is unavoidable for one or more sources, a targeted RPA component can be added alongside the script without changing the overall architecture. The maintenance burden of UI-based bots should not be underestimated in a slow-moving IT environment, so the goal should be to minimise rather than rely on RPA.

Traditional Code scores ahead of the other paths here for a straightforward reason: the core task is structured numerical reconciliation on a fixed monthly schedule, and that is exactly what a scheduled script does well. The data sources are known, the variance calculation rules are stable, and the output format has not changed. There is no ambiguity that would justify reaching for a more sophisticated tool, and at twelve runs per year the volume does not demand one either.

The Hybrid path scores close behind, and it is worth being honest about what that score actually reflects. A well-designed Traditional Code implementation already implies a human review step for exceptions and a department head sign-off before publication. Hybrid as a distinct path would simply formalise that handover with an explicit workflow gate, perhaps a short-form checklist or a structured email handoff, rather than leaving it as an informal step. That formalisation has real value in a compliance environment where the file must be defensible on appeal, but it adds modest complexity for a small team of three people. The recommended approach is to build that structure into the script's exception report rather than introduce a separate workflow tool, keeping the footprint small and the maintenance burden on central IT manageable.

RPA is worth mentioning because it addresses a genuine uncertainty: if the general ledger turns out not to support any form of programmatic data export, screen-level automation may be the only realistic path to removing manual effort. That makes RPA a credible fallback rather than a competing recommendation. AI Workflow and AI Agent score poorly not because AI is inappropriate in principle, but because the compliance requirement that every output be justifiable on file makes AI-generated narrative commentary a liability, and autonomous decision-making is simply incompatible with the public accountability obligations this process carries.

The first and most important step is a data access audit, and it needs to happen before any code is written. The team currently pulls data from the general ledger, supporting spreadsheets, email threads, and some open-data registers, but it is not yet confirmed which of those sources can be reached programmatically. A Python script using pandas for tabular manipulation, openpyxl for reading and writing the existing Excel template, and requests for any open-data endpoints that publish JSON or CSV will cover the majority of realistic scenarios without requiring new procurement or specialist infrastructure. The audit maps every source, confirms export formats or API availability, and identifies any stragglers that may need a manual fallback or, in the worst case, a lightweight RPA step to handle a system that only exposes a GUI.

Once data access is confirmed for the main sources, the prototype script pulls the ledger extract, joins it to the budget figures, calculates line-by-line variances, and writes the results directly into the existing Excel template. Keeping the output format unchanged matters: it means the department head and councillors see a familiar document, and it avoids any need for a separate approval process to change the report layout. The script flags rows where the variance exceeds a configurable materiality threshold and writes those flagged rows into a separate exception tab or a short companion summary. That summary is the handover document for the department head: it tells the reviewer exactly what the script has done, which lines need a narrative explanation, and which may indicate upstream data entry errors requiring correction before publication.

Two parallel runs alongside the existing manual process are the right way to build confidence before going live. Running the script in shadow mode for two consecutive monthly cycles lets the case officers compare the automated output against their own assembled figures line by line. Any discrepancies surface edge cases in the extraction logic, such as how the ledger codes certain inter-departmental transfers or how late data from a supporting system should be handled when it arrives after the script has already run. Both issues are solvable, but they are much easier to solve during a parallel run than after the manual process has been retired. A simple log file written by the script on each run, recording source file timestamps, row counts, and any rows skipped due to missing data, creates the audit trail that the compliance requirements demand.

After two successful parallel cycles, the script is packaged with a plain-language maintenance guide, a log of known edge cases and how they are handled, and clear instructions for the two case officers on how to monitor outputs and trigger the exception review. Central IT receives the package for scheduling on existing infrastructure, whether that is a cron job on a server or a scheduled task on a shared Windows environment. The script is designed to fail loudly rather than silently: if a source file is missing or a data connection is unavailable when the script runs, it sends an alert to the case officers rather than producing a partially populated report that might not be noticed until the department head review. That design principle, loud failures over silent ones, is the single most important engineering decision in a low-volume, high-stakes process like this one.