AUTOMATION
When a finance spreadsheet becomes a business-critical system
The warning sign is not the number of formulas. It is whether the process can still run correctly when its creator is unavailable or the input changes.
By CA Tony Thomas · Chartered Accountant · 4 minute read
Prepared
PRACTICAL PERSPECTIVES / FINANCE · AUTOMATION · AI
Recognise the operational dependency
A monthly reporting workbook begins as a helpful shortcut. A few months later, several teams depend on it, the person who built it receives every support request and the final numbers rely on a sequence of undocumented copy-and-paste steps.
The problem is not that spreadsheets are inherently unsuitable. It is that the process has become important without acquiring the controls needed for its importance. Before replacing the workbook, identify what it does, who relies on it and what could go wrong.
Map the inputs, transformations, outputs and decisions. Include manual adjustments and approval steps. A successful migration preserves the business rules and review responsibilities, not just the appearance of the final report.
Define an input contract
Suppose a fictional monthly report receives three branch files. Each file should contain entity, period, account, currency and amount. One branch changes the heading “Amount” to “Net Amount”; another sends a prior-month file with the current month's filename.
A fragile script may import both without complaint or treat a missing column as zero. A controlled process checks required columns, types, period values, entity coverage and currency before calculation starts. It should stop with a useful explanation when a required field is missing.
Record an input manifest containing expected files, source owners, extraction dates and reconciliation totals. Do not infer the reporting period solely from the filename. Confirm the values inside the file.
Decide which changes are compatible and which require review. Adding an unused descriptive column may be harmless; changing the meaning of the amount field is not.
Separate calculation from presentation
Keep imported data, transformation rules and report formatting distinct. A formatting change should not alter the financial calculation. Store account mappings in a controlled table with an effective date and reviewer rather than scattering replacements through formulas.
Use a reproducible reconciliation at each material stage. For example, if three complete branch extracts total AED 1.2 million, AED 0.8 million and AED 0.5 million on a consistent basis, the combined total before adjustments should be AED 2.5 million. Record consolidation adjustments separately with their rationale.
Validate totals by relevant entity, currency and account grouping. A grand total alone can hide offsetting errors. Check duplicates, missing accounts and unexpected sign conventions.
Keep manual adjustments visible in an approved register. A silent overwrite of a formula cell is difficult to review and almost impossible to explain reliably later.
Make reruns and failures predictable
Give every run a unique identifier and preserve its approved input versions. Write outputs to a new run folder instead of overwriting the last successful report while calculations are still in progress.
If validation fails, do not publish a partially generated pack. Retain the last approved output with a clear status, identify the failed control and tell the owner what needs correction. A technical success message should not imply that the report was reviewed.
Make repeated processing safe. Importing the same file twice should not double the results. Use source identifiers and a documented duplicate policy; a timestamp alone may not distinguish a corrected extract from a duplicate.
Do not store passwords in workbook cells or scripts. Use approved credential handling and limit access to the source data and outputs. Automation logs must avoid exposing confidential records unnecessarily.
Test the cases that break the process
Before relying on the automation, test missing columns, duplicated files, wrong periods, blank values, negative amounts, unexpected currencies and changed mapping tables. Decide the expected result for each case with the business owner.
Compare the first automated output with a reviewed reference report and investigate differences at the source. Agreement with an old workbook is useful evidence, but the workbook itself may contain errors. Include independent control totals and selected transaction checks.
AI can help explain code or suggest test cases, using synthetic examples and approved data. Generated code still needs inspection for calculation mistakes, unintended network access and inappropriate handling of files. Keep a reviewed version history and a recovery procedure.
Choose the smallest reliable improvement
The first improvement may be a validated import and an exception report rather than a complete application. Retain spreadsheet review where it remains useful, while removing undocumented handovers and repetitive transformations.
Name a process owner and a backup operator. Record how to run the workflow, interpret exceptions, approve changes and fall back safely if a dependency fails. Revalidate after a meaningful change in file format, business rules or data source.
A finance automation has become dependable when someone other than its creator can operate it, explain its output and recognise when it should stop. That is a more useful milestone than replacing every workbook.
Personal educational perspective, independent of my employer. All business examples and figures are fictional and illustrative. Proposed workflows do not claim completed deployments or measured productivity gains. This is not entity-specific accounting, tax, legal or investment advice. Apply professional judgment, the applicable reporting framework and your organisation’s approval and data-protection policies.