Investigating the use of Software Agents to Reduce The Risk of Undetected Errors in Strategic Spreadsheet Applications

There is an overlooked iceberg of problems in end user computing. Spreadsheets are developed by people who are very skilled in their main job function, be it finance, procurement, or production planning, but often have had no formal training in spreadsheet use. IT auditors focus on mainstream information systems but regard spreadsheets as user problems, outside their concerns. Internal auditors review processes, but not the tools that support decision making in these processes. This paper highlights the gaps between risk management and end user awareness in spreadsheet research. In addition the potential benefits of software agent technologies to the management of risk in spreadsheets are explored. This paper discusses the current research into end user computing and spreadsheet use awareness.

💡 Research Summary

The paper “Investigating the use of Software Agents to Reduce the Risk of Undetected Errors in Strategic Spreadsheet Applications” addresses a critical yet often overlooked source of organizational risk: the widespread use of end‑user‑developed spreadsheets. The authors begin by highlighting that spreadsheets are typically built by domain experts—finance, procurement, production planning—who lack formal training in spreadsheet design. Consequently, spreadsheets become “shadow IT” components that escape the scrutiny of traditional IT auditors, who focus on mainstream information systems, and internal auditors, who concentrate on processes rather than the tools that support decision‑making.

A review of empirical studies (KPMG 2002, Coopers & Lybrand 1997, Panko 1997, among others) reveals that an overwhelming majority of spreadsheets contain errors: 90‑95 % of audited models exhibit mistakes that could affect business decisions, with many errors being of a magnitude that could lead to significant financial loss. Errors are especially prevalent in large spreadsheets (over 150 rows) and in those that incorporate macros or complex formulas. The authors argue that because spreadsheets often serve as templates that are repeatedly updated, any error in the original model propagates and amplifies over time.

The paper then turns to risk management, noting that the “missing link” is awareness. Organizations rarely recognize spreadsheets as a risk vector, which prevents the establishment of standards, testing procedures, or control mechanisms. The authors cite a real‑world incident where a spreadsheet error caused a multi‑million‑dollar loss in an oil‑and‑gas acquisition, underscoring the potential impact.

To address this gap, the authors propose the deployment of software agents, specifically a Multi‑Agent System (MAS), as an autonomous, continuous audit and remediation platform. They trace the origins of agent technology to the late 1970s and explain that modern computing power now makes MAS feasible for enterprise environments. An agent is defined by three core attributes—delegacy (autonomous authority), competency (ability to perform tasks), and amenability (adaptability). In a MAS, agents communicate via an Agent Communication Language (ACL) and can collaborate to locate, classify, and correct spreadsheet errors across a distributed network.

The proposed architecture envisions agents installed on the corporate intranet with full access to servers, network drives, and individual workstations. The agents would continuously scan for spreadsheet files, extract metadata (e.g., presence of macros, hidden cells, formula complexity), and apply an inference‑based risk model to assign a risk rating. High‑risk spreadsheets would be subjected to automated analysis: detection of formula inconsistencies, broken links, hidden data, and macro security issues. Where possible, agents would autonomously apply corrective actions—unhiding cells, fixing references, removing unsafe macros—and generate a detailed change log that is sent to the responsible staff member.

Beyond passive monitoring, agents would act proactively during spreadsheet creation. When a user attempts to save a new workbook, the agent would intercept the operation, verify compliance with organizational standards (template usage, documentation, version control), and flag any deviations before the file is stored. This “rolling audit” approach eliminates the need for periodic, costly manual audits and ensures that spreadsheet governance is enforced in real time.

The authors support their argument with two case studies derived from Masters dissertations conducted in 2003. The first study examined a large NHS Trust and found that only 35 % of respondents reported using any formal development methodology for spreadsheets, while 70 % of spreadsheets contained macros. The second study at Revlon International revealed a similar lack of methodological rigor and a pervasive belief among users that their spreadsheets were not critical to the organization’s IT infrastructure. Both studies highlight a cultural and procedural disconnect that leaves organizations vulnerable to spreadsheet‑related failures.

In conclusion, the paper asserts that spreadsheet errors constitute a hidden iceberg of risk that current audit practices fail to address. Software agents, organized as a MAS, offer a promising solution by providing continuous, autonomous detection, classification, and remediation of spreadsheet errors, while simultaneously raising awareness among users and managers. The authors recommend further research into improving agent accuracy, integrating agents with existing IT governance frameworks, and evaluating user acceptance of agent‑driven controls.