Audit and Change Analysis of Spreadsheets

Because spreadsheets have a large and growing importance in real-world work, their contents need to be controlled and validated. Generally spreadsheets have been difficult to verify, since data and executable information are stored together. Spreadsheet applications with multiple authors are especially difficult to verify, since controls over access are difficult to enforce. Facing similar problems, traditional software engineering has developed numerous tools and methodologies to control, verify and audit large applications with multiple developers. We present some tools we have developed to enable 1) the audit of selected, filtered, or all changes in a spreadsheet, that is, when a cell was changed, its original and new contents and who made the change, and 2) control of access to the spreadsheet file(s) so that auditing is trustworthy. Our tools apply to OpenOffice.org calc spreadsheets, which can generally be exchanged with Microsoft Excel.Comment: 10 pages, 3 figure

Managing Critical Spreadsheets in a Compliant Environment

The use of uncontrolled financial spreadsheets can expose organizations to unacceptable business and compliance risks, including errors in the financial reporting process, spreadsheet misuse and fraud, or even significant operational errors. These risks have been well documented and thoroughly researched. With the advent of regulatory mandates such as SOX 404 and FDICIA in the U.S., and MiFID, Basel II and Combined Code in the UK and Europe, leading tax and audit firms are now recommending that organizations automate their internal controls over critical spreadsheets and other end-user computing applications, including Microsoft Access databases. At a minimum, auditors mandate version control, change control and access control for operational spreadsheets, with more advanced controls for critical financial spreadsheets. This paper summarises the key issues regarding the establishment and maintenance of control of Business Critical spreadsheets.Comment: 4 Page

TellTable Spreadsheet Audit: from Technical Possibility to Operating Prototype

At the 2003 EuSpRIG meeting, we presented a framework and software infrastructure to generate and analyse an audit trail for a spreadsheet file. This report describes the results of a pilot implementation of this software (now called TellTable; see www.telltable.com), along with developments in the server infrastructure and availability, extensions to other "Office Suite" files, integration of the audit tool into the server interface, and related developments, licensing and reports. We continue to seek collaborators and partners in what is primarily an open-source project with some shared-source components.Comment: 11 page

Spreadsheet Risk - A New Direction for HMRC?

Her Majestys Revenue & Customs (HMRC) was born out of the need to create a UK tax authority by merging both the Inland Revenue and HM Customs & Excise into one department. HMRC encounters spreadsheets in tax-payers systems on a very regular basis as well as being a heavy user of spreadsheets internally. The approach to spreadsheet risk assessment and spreadsheet audit is by the use of trained computer auditors and data handlers. This, by definition, limits the use of our specialist spreadsheet audit tool to such trained staff. In order to tackle the growing use of spreadsheets, a new way of approaching the problem has been piloted. The aim is to issue all staff who come across spreadsheets with a simple to use analysis and risk assessment tool, based on the departmental software SpACE (Spreadsheet Audit & Compliance Examination).Comment: 5 Page

A Spreadsheet Auditing Tool Evaluated in an Industrial Context

Amongst the large number of write-and-throw-away spreadsheets developed for one-time use there is a rather neglected proportion of spreadsheets that are huge, periodically used, and submitted to regular update-cycles like any conventionally evolving valuable legacy application software. However, due to the very nature of spreadsheets, their evolution is particularly tricky and therefore error-prone. In our strive to develop tools and methodologies to improve spreadsheet quality, we analysed consolidation spreadsheets of an internationally operating company for the errors they contain. The paper presents the results of the field audit, involving 78 spreadsheets with 60,446 non-empty cells. As a by-product, the study performed was also to validate our analysis tools in an industrial context. The evaluated auditing tool offers the auditor a new view on the formula structure of the spreadsheet by grouping similar formulas into equivalence classes. Our auditing approach defines three similarity criteria between formulae, namely copy, logical and structural equivalence. To improve the visualization of large spreadsheets, equivalences and data dependencies are displayed in separated windows that are interlinked with the spreadsheet. The auditing approach helps to find irregularities in the geometrical pattern of similar formulas.Comment: 12 Pages, 2 Figures, 4 Table

Teaching spreadsheet development using peer audit and self-audit methods for reducing error

Recent research has highlighted the high incidence of errors in spreadsheet models used in industry. In an attempt to reduce the incidence of such errors, a teaching approach has been devised which aids students to reduce their likelihood of making common errors during development. The approach comprises of spreadsheet checking methods based on the commonly accepted educational paradigms of peer assessment and self-assessment. However, these paradigms are here based upon practical techniques commonly used by the internal audit function such as peer audit and control and risk self-assessment. The result of this symbiosis between educational assessment and professional audit is a method that educates students in a set of structured, transferable skills for spreadsheet error-checking which are useful for increasing error-awareness in the classroom and for reducing business risk in the workplace.Comment: 9 Pages, includes reference

Ensuring Spreadsheet Integrity with Model Master

We have developed the Model Master (MM) language for describing spreadsheets, and tools for converting MM programs to and from spreadsheets. The MM decompiler translates a spreadsheet into an MM program which gives a concise summary of its calculations, layout, and styling. This is valuable when trying to understand spreadsheets one has not seen before, and when checking for errors. The MM compiler goes the other way, translating an MM program into a spreadsheet. This makes possible a new style of development, in which spreadsheets are generated from textual specifications. This can reduce error rates compared to working directly with the raw spreadsheet, and gives important facilities for code reuse. MM programs also offer advantages over Excel files for the interchange of spreadsheets.Comment: 15 pages; substantive references; code example

Sarbanes-Oxley: What About all the Spreadsheets?

The Sarbanes-Oxley Act of 2002 has finally forced corporations to examine the validity of their spreadsheets. They are beginning to understand the spreadsheet error literature, including what it tells them about the need for comprehensive spreadsheet testing. However, controlling for fraud will require a completely new set of capabilities, and a great deal of new research will be needed to develop fraud control capabilities. This paper discusses the riskiness of spreadsheets, which can now be quantified to a considerable degree. It then discusses how to use control frameworks to reduce the dangers created by spreadsheets. It focuses especially on testing, which appears to be the most crucial element in spreadsheet controls.Comment: 45 pages, 7 figure

An approach for the automated risk assessment of structural differences between spreadsheets (DiffXL)

This paper outlines an approach to manage and quantify the risks associated with changes made to spreadsheets. The methodology focuses on structural differences between spreadsheets and suggests a technique by which a risk analysis can be achieved in an automated environment. The paper offers an example that demonstrates how contiguous ranges of data can be mapped into a generic list of formulae, data and metadata. The example then shows that comparison of these generic lists can establish the structural differences between spreadsheets and quantify the level of risk that each change has introduced. Lastly the benefits, drawbacks and limitations of the technique are discussed in a commercial context.Comment: 10 Pages, Numerous Colour Diagrams & Screenshot

Qtier-Rapor: Managing Spreadsheet Systems & Improving Corporate Performance, Compliance and Governance

Much of what EuSpRIG discusses is concerned with the integrity of individual spreadsheets. In businesses, interlocking spreadsheets are regularly used to fill functional gaps in core administrative systems. The growth and deployment of such integrated spreadsheet SYSTEMS raises the scale of issues to a whole new level. The correct management of spreadsheet systems is necessary to ensure that the business achieves its goals of improved performance and good corporate governance, within the constraints of legislative compliance - poor management will deliver the opposite. This paper is an anatomy of the real-life issues of the commercial use of spreadsheets in business, and demonstrates how Qtier-Rapor has been used to instil best practice in the use of integrated commercial spreadsheet systems.Comment: 12 Pages, 6 Colour Figure