When AI Meets the Spreadsheet: A Finance Automation Case Study

The month-end problem hiding in plain sight

At a mid-sized UK equipment distributor, month-end reconciliation had become an endurance test. The finance team managed 18 legal entities, 42 bank accounts and roughly 95,000 monthly ledger lines across its enterprise resource planning system, banking portals and expense platform. Eight accountants spent the first six working days after close exporting spreadsheets, matching transactions and investigating variances. The work was essential, but much of it consisted of repeated searches, copy-and-paste routines and judgement calls made under deadline pressure.

The controller’s initial review found that bank, intercompany and expense reconciliations consumed about 410 staff hours per month. Another 120 hours went into variance analysis for revenue, freight, payroll and operating expenses. Despite that effort, reviewers still encountered duplicated entries, inconsistent supplier names and explanations such as “timing difference” without supporting evidence. Close was usually completed on working day seven, and late adjustments averaged £340,000 per quarter. The issue was not a shortage of diligence; it was a process in which skilled employees acted as the integration layer between incompatible systems.

Management set a deliberately narrow goal: automate repetitive matching and first-pass variance analysis without allowing artificial intelligence to post entries or approve reconciliations. Success would mean reducing close effort by at least 25 per cent, maintaining or improving error detection, and preserving a clear audit trail. That boundary mattered. The team was not buying an autonomous finance function. It was redesigning a controlled workflow in which software prepared evidence and employees remained accountable for decisions.

Cleaning the data before training the system

The first eight weeks produced no dramatic AI demonstration. Instead, finance and data staff catalogued the inputs. They found 11 date formats, five ways of representing credits, account codes stored as both numbers and text, and supplier names that varied by punctuation, branch and legal suffix. “J. Smith Logistics Ltd”, “JS Logistics” and “J Smith Log.” referred to the same carrier. Bank references were truncated differently by each institution, while intercompany invoices sometimes carried local identifiers rather than group document numbers. A matching model built directly on those exports would have automated inconsistency rather than reconciliation.

The team created a standardised data layer with defined fields for entity, account, currency, transaction date, value date, amount, counterparty and source identifier. Deterministic rules handled basic corrections: trimming spaces, normalising signs, converting currencies to ISO codes and mapping known counterparties to master records. A steward in accounts payable reviewed uncertain supplier mappings, and every transformation retained the original value. Of 780,000 historical rows used for testing, 6.4 per cent required some form of correction and 1.1 per cent could not be reliably standardised without employee input.

This cleanup exposed an important tradeoff. Adding more historical data did not automatically improve the model because older periods contained obsolete account structures and poorer descriptions. The team therefore used 12 recent months for model development, while reserving three closed months as an untouched test set. It also introduced data-quality thresholds: if required fields were missing from more than 2 per cent of a source file, the workflow stopped and alerted the process owner. Automation began with refusing bad inputs, not disguising them.

Combining rules with AI rather than choosing between them

Reconciliation was split into tasks according to risk and predictability. Exact one-to-one matches, such as identical amounts with shared references within three days, were handled by conventional rules. So were known bank fees and recurring direct debits. Machine-learning techniques were reserved for ambiguous cases: one payment covering several invoices, settlement amounts reduced by fees, counterparties with inconsistent names, and transactions posted on opposite sides of a period boundary. This hybrid design was less fashionable than an all-AI proposition, but it was easier to test and explain.

Each proposed match received a confidence score based on amount proximity, date distance, counterparty similarity, reference overlap and historical matching patterns. At launch, only proposals scoring at least 0.98 and satisfying additional control rules were marked as auto-matched. Scores between 0.75 and 0.98 entered a review queue with the model’s supporting factors displayed. Lower-scoring items remained unmatched. The threshold sacrificed coverage for reliability: only 46 per cent of transactions qualified for automatic treatment in the first month, but sampled accuracy was 99.7 per cent.

The system also avoided a common design error: treating a high confidence score as an accounting conclusion. A probable relationship between records did not prove that the underlying transaction was valid, correctly classified or authorised. A £25,000 payment could match an invoice perfectly and still be posted to the wrong cost centre. For that reason, matching, account certification and journal approval remained separate controls. AI reduced the search space; it did not collapse distinct responsibilities into one opaque decision.

Turning variance analysis into an evidence-led workflow

Variance analysis required a different approach. Previously, accountants compared actuals with budget and prior year in spreadsheets, then wrote explanations for movements above locally chosen thresholds. The quality varied. One analyst might investigate a £20,000 overspend, while another ignored a 30 per cent movement because its absolute value appeared small. The redesigned process applied agreed thresholds by account class: for example, the greater of £15,000 or 8 per cent for operating expenses, and the greater of £50,000 or 3 per cent for revenue.

The AI service assembled a first-pass narrative from ledger detail, operational drivers and approved reference data. When freight expense rose £186,000 against budget, it attributed £102,000 to higher shipment volume, £49,000 to fuel surcharges and £28,000 to adverse route mix, leaving £7,000 unexplained. The draft linked each figure to underlying records and labelled assumptions. Rather than simply stating that costs had increased, it gave the analyst a decomposition to confirm, amend or reject.

Generative capabilities were tightly constrained. The model could summarise supplied evidence but could not invent operational causes, retrieve uncontrolled internet data or silently alter values. Numerical calculations occurred in a separate, tested computation layer; the language model received the results and produced readable commentary. If components did not reconcile to the total variance within £1 or if no evidence supported a cause, the system inserted an explicit unresolved flag. This reduced polished but unsupported explanations, one of the most material risks in using generative AI for financial reporting.

Controls designed for auditors and operators

Finance built the control framework alongside the workflow rather than after deployment. Every automated match stored the source records, rule or model version, confidence score, timestamp and reviewer status. Changes to thresholds required approval from the controller and the technology risk lead. Access followed existing entity permissions, while service accounts used read-only connections except for writing proposed statuses to a staging table. No journal reached the ledger without the company’s established approval route.

Accuracy was measured through stratified sampling, not a convenient sample of easy transactions. Each month, reviewers examined auto-matches across entities, currencies, values and match types, with all items above £100,000 included. A single false match in a high-risk category could trigger suspension of that category pending investigation. During the second month, the control worked as intended: 14 card settlements were incorrectly linked because a payment processor reused short references. The affected rule was disabled within two hours, 1,860 recent matches were rechecked and no ledger misstatement was found.

The team also monitored drift. Counterparty patterns changed after acquisitions, banking references shifted during a payment-platform upgrade, and seasonal volume affected date assumptions. Dashboards tracked match rates, override rates, unresolved balances and accuracy by entity. An override rate above 5 per cent for any match type prompted review, even if overall accuracy remained high. These operational indicators were more useful than a single headline claim about model performance because they showed where the process was weakening before it became a reporting problem.

Employee review became more important, not less

The automation changed accountants’ work but did not remove review. Previously, employees spent hours finding candidate matches; afterwards, they assessed exceptions and evidence. The review interface displayed proposed links side by side, highlighted the factors behind each suggestion and required a reason when a proposal was rejected. Common rejection codes included duplicate record, incorrect entity, unsupported fee deduction and period mismatch. Those responses improved future rules and exposed upstream process failures.

Training focused on scepticism rather than software navigation. Reviewers practised cases in which the AI suggestion looked plausible but was wrong, including equal-value transactions from related counterparties and recurring invoices posted to different entities. They were told not to treat confidence as authority. Senior accountants initially worried that faster review targets would encourage superficial approval, so management avoided individual productivity rankings during the pilot and measured team-level quality instead. Average review time fell from 2.8 minutes per item to 54 seconds without a rise in overrides missed by secondary sampling.

Roles shifted at the margin. Two temporary positions previously added during quarter-end were no longer required, but no permanent employees were made redundant. Capacity moved towards supplier statement reviews, aged balance cleanup and root-cause analysis. One analyst traced repeated unmatched cash receipts to a customer portal that omitted invoice references; fixing that integration removed about 600 exceptions per month. The larger benefit came not from processing the same defects faster, but from giving employees enough time to eliminate them.

Measured savings and the limits of the business case

After six months, automated and assisted matching covered 81 per cent of in-scope transactions. Of these, 63 per cent were auto-matched and 18 per cent were resolved through the review queue. Reconciliation effort fell from 410 to 238 hours per month, while variance-analysis effort dropped from 120 to 68 hours. Close moved from working day seven to working day five in four of the final five months. Late quarterly adjustments declined from an average of £340,000 to £110,000, although management did not attribute that improvement solely to AI.

The annualised gross capacity saving was estimated at £286,000, based on 224 monthly hours released, avoided temporary labour and reduced support for spreadsheet failures. Against that stood £165,000 in first-year implementation costs and about £72,000 in annual software, hosting, monitoring and support. The first-year net benefit was therefore modest at roughly £49,000; from the second year, expected annual net benefit rose to around £214,000. The calculation excluded speculative gains such as better decisions and included internal staff time, making it less impressive but more credible.

Not every process justified automation. Low-volume pension reconciliations remained manual because the data was sensitive, irregular and already took fewer than six hours per month. Foreign-exchange revaluation used deterministic calculations rather than AI. The team also postponed balance-sheet commentary generation until it could secure consistent operational driver data. These decisions protected the business case from becoming a technology catalogue. A process entered the roadmap only when volume, control quality and repeatability supported the investment.

What responsible finance automation looks like

The case demonstrates that successful finance AI depends less on a striking model than on disciplined process design. Standardised inputs, narrow permissions, explicit thresholds and evidence-linked outputs did most of the work. The AI components added value where ambiguity made rigid rules brittle, particularly in counterparty matching and narrative drafting. Yet the strongest controls remained conventional: segregation of duties, reconciled totals, approved master data, sampling and documented exceptions.

The team’s next phase is cautious. It plans to extend matching to two acquired entities only after their charts of accounts and supplier masters are aligned. It will test whether anomaly detection can prioritise unusual manual journals, but the tool will flag entries rather than block or approve them. Management has also committed to quarterly model reviews and an annual independent control assessment. Expansion will follow demonstrated accuracy, not pressure to maximise automation rates.

For finance leaders, the practical lesson is to measure the whole operating system. Hours saved matter, but so do false matches, reviewer overrides, unexplained variances, close timing and unresolved balances. A model that automates 90 per cent of transactions can be worse than one automating 60 per cent if it obscures errors or weakens accountability. In this case, AI earned its place in the spreadsheet-heavy close by making evidence easier to assemble while leaving financial judgement visibly, and deliberately, with people.