Budget versus actual spending

Every month, a department head and two case officers prepare a variance report comparing budgeted spending against actual spending from the general ledger. The report is published to councillors ahead of the monthly council meeting. The team extracts budget and actual figures from the ledger, reconciles data across multiple registers (some of which publish open data), calculates variances, and identifies exceptions that require explanation. Unusual cases and data quality issues demand judgment calls. Once the variances are calculated and exceptions resolved, the team assembles the report in Excel, reviews it for accuracy, and publishes it to councillors. The entire process currently takes around seven hours each month.

The work is governed by administrative procedure law and publication duties. Numbers go public, so accuracy is non-negotiable. Decisions must be justifiable on file and able to survive appeal. The organisation operates under centrally provided IT and public procurement rules, which constrain the speed of change and the adoption of new systems. Some registers publish open data, but integration options are limited by procurement constraints and the cautious pace of regional IT.

Traditional code is the right fit for this process because the work is deterministic calculation on structured data with a fixed monthly schedule. A Python script or similar tool can extract budget and actual spending from the ledger, calculate variances, apply thresholds to flag exceptions, and generate a draft report in the Excel format the team already uses. The logic is arithmetic, not pattern recognition, so there is no role for AI. The script can run on a schedule a few days before the council meeting deadline, giving the case officers time to review flagged exceptions, investigate data quality issues, and apply the judgment that only humans can provide. Final approval before publication remains a human gate, preserving compliance and accountability.

The client has no stated tech stack preference but faces real constraints. Centrally provided IT and public procurement rules mean that adopting a new platform or vendor tool introduces delay and bureaucracy. A simple script developed in-house or by a trusted regional IT partner avoids procurement altogether and integrates naturally with the Excel outputs councillors already receive. The script can be scheduled using cron or Windows Task Scheduler, requiring no new infrastructure. Maintenance is straightforward because the logic mirrors the existing manual process, and budget structures in the public sector change slowly and predictably.

RPA and AI-powered workflows score poorly because they add cost and complexity without corresponding benefit. RPA mimics screen interactions and is brittle when layouts change. This process needs reliable data extraction and calculation, not screen scraping. AI workflow platforms are designed for unstructured inputs and adaptive routing, but variance calculation is pure arithmetic. An autonomous AI agent is completely inappropriate in a compliance-sensitive environment where numbers go public and decisions must survive appeal. Agent unpredictability and lack of explainability are deal-breakers here.

Hybrid automation with workflow orchestration is viable if the organisation already has that infrastructure, but for twelve runs per year it introduces overhead that a simple script avoids. The case officers will still review exceptions and approve publication, so the workflow gates would formalise what is already happening informally. If the organisation later invests in workflow tooling for other processes, this one can migrate, but a script is the pragmatic starting point. Staying manual wastes eighty-four hours per year on repetitive calculation and data wrangling, leaving less capacity for the judgment calls that actually matter. Automation is justified, but it should be simple, transparent, and maintainable by someone who understands both the code and the domain.

Traditional Code wins here because the process is arithmetic, not interpretation. You're extracting structured ledger data, calculating variances, and flagging exceptions above thresholds. That's a job for a Python script, not a language model or agent. The calculation is deterministic, the data is tabular, and the logic is stable. A scheduled script can run the numbers in minutes, generate a draft Excel report in the format your team already uses, and email it with exceptions highlighted. You still review the exceptions and approve publication, but the routine work disappears.

Hybrid automation could add value if you already have a workflow platform like Power Automate or n8n in place. It would give you a visual audit trail and structured gates for exception review and approval. But for twelve runs per year, the overhead of configuring and maintaining a workflow tool is hard to justify. You'd be managing a platform for a task that a cron job and an email can handle. If this were one of twenty monthly reports, hybrid might make sense. For a single process, it's overengineering.

RPA and AI paths score lower because they solve problems you don't have. RPA mimics clicking through a UI, which makes it fragile when screens or layouts change. You need reliable extraction and calculation, not screen scraping. AI Workflow and AI Agent bring language understanding and adaptive reasoning, but your process needs arithmetic and rules. AI also introduces explainability risk in a compliance-sensitive public sector context where every number must be defensible. The wrong tool for the job, and expensive to boot.

The implementation starts with data access. Your ledger and any open data registers need to be accessible programmatically, either via API, database query, or scheduled export. If your regional IT controls ledger access, coordinate early to confirm what's possible and whether any security or procurement review is required. Build a prototype script in Python or similar that extracts budget and actual data for one period and writes it to CSV or Excel. Validate the output line by line with your case officers against a recent manual report to confirm completeness and accuracy. This stage flushes out any data quality issues or missing fields before you automate the calculation.

Once extraction is reliable, implement the variance calculation and exception flagging logic. The script reads the extracted data, calculates variances by budget code or category, and applies thresholds to flag exceptions. Work with the case officers to define what counts as an exception, whether that's a percentage variance, an absolute amount, or a combination. Generate a draft report in the existing Excel format with exceptions highlighted in a separate tab or column. Test the script against three to six historical months where you have both the source data and the final manual report, and compare the outputs to confirm accuracy. Document the calculation logic and thresholds for audit purposes and future maintenance.

The final stage is scheduling and workflow. Set up a cron job or Windows Task Scheduler to run the script a few days before the monthly council meeting deadline. The script extracts data, calculates variances, flags exceptions, and emails the draft report and exception list to the case officers. They review the exceptions, apply judgment where needed, correct any data quality issues upstream if possible, and approve the final report for publication. Run the script in parallel with the manual process for two cycles to build confidence before you cut over completely. Provide simple documentation and a short training session so the team understands what the script does, how to review its output, and who to call if something breaks.

The tooling can be minimal. Python with pandas for data manipulation and openpyxl for Excel output is a common and maintainable stack. If your IT environment prefers PowerShell or R, those work too. The key is picking something your regional IT or an in-house developer can support long-term. Hosting can be as simple as a scheduled task on a departmental server or a small cloud VM if your procurement rules allow it. The script's output is a draft, not a final publication, so you're not automating decision-making. You're automating the tedious part and preserving human judgment where it matters.