We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners. Read More


Log in

Addressing Risk in Spreadsheet Accounting – Thomas Mercieca, Joseph G Vella and Kevin Vella

2 Aug 2019 12:00 | Anonymous
The Accountant – Sustainability. Summer 2019 (MIA Publication) 
Development in technology is one of the most commonly cited drivers of change in the accounting profession. The accountant is relieved from repetitive and error-prone tasks present in bookkeeping and regulation enforcement. Unburdened, the accountant can shift focus to proactive services such as addressing the development and direction of a business, tax planning and budget planning. In the era of the Cloud, the accountant has a wider data reach, exposing professionals to more complex business perspectives for decision making and interpretation. Besides this paradigm shift, however, the accountant is also exposed to new risks which must be addressed for the successful operation of a business. 

With advance in technology, the spreadsheet remains one of the most commonly used tool in the accounts ecology, a phenomenon that can be explained thanks to the accessibility of the tool: the spreadsheet is flexible, easy to-use yet offering powerful reporting capabilities, it is supported and wellunderstood by a wide community and userbase, and has a low-priced entry point. The modern spreadsheet has evolved to support seamless integration with various data formats and data sources such as internal databases, enterprise systems and data from Application Programming Interfaces (APIs), for example, reading a Yahoo! ticker. As far as data management is concerned, the spreadsheet does not only offer a central and flexible location for data consolidation and customised reporting, but the spreadsheet user is also commonly equipped with the ability to commit and communicate manipulated data back to its data source.
Despite the spreadsheet’s appealing qualities, it has been the source of numerous and extensive failures across several domains. In this article, we surmise the state of spreadsheets and the nature of risk with regards to modern spreadsheet accounting when interacting with various data sources.
Spreadsheet risk has garnered enough interest that a group has been established with the aim of addressing risk by offering an extensive set of resources and information on the topic. This group is called the European Spreadsheet Risks Interest Group1 (EuSpRIG) and is motivated by an alarming number of corporate spreadsheet usage that led to serious consequences such as fines, damage to an entity’s reputation, and even incarceration. 
Spreadsheet Praxis
To understand the inherent problems of using a spreadsheet, one can look at the characteristics of a spreadsheet in an enterprise. A spreadsheet tends to be created and used informally – without adequate design of any requirements, specifications, structure or model, and without documentation. Yet, a spreadsheet model tends to have multiple users and a life spanning several years. As the simple and innocent spreadsheet grows to become more critical to the running of a business than first anticipated, it often fails to develop the necessary robustness in correctness and performance. Thus, one can understand why spreadsheets have become synonymous with risk and, for some, the object of contempt.
Spreadsheets have increasingly come under fire with several people advocating against their usage. Ideally, software solutions are thoroughly designed for a business case, however, the implications of putting an abrupt end to spreadsheet usage need to be considered: (a) several unique tools have to be designed and tailored for each business case in place of using a standard, well-understood and capable tool; (b) accountants have to learn new technology rather than relying on their expertise; and (c) the business is forced to pause and restructure operations in light of a multitude of spreadsheets currently residing in an enterprise - an unrealistic imposition in a competitive climate. Instead, we are motivated in adopting spreadsheet design practices that minimize the risk of this necessary evil in the accounting practice.
Spreadsheet Fraud
The Sarbanes-Oxley Act was enacted in 2002 in the US in response to financial reporting fraud. This act requires corporations to assess the effectiveness of their financial reporting systems and one requirement that follows is the timely prevention or detection of misstatements. One can expect professional bodies and governments to have similar regulations on the matter, for example, The 8th EU Company Law Directive on Disclosure and Transparency. 
A suggested way of detecting fraud and fault is through event auditing, a mechanism that can be found in restricted form in popular spreadsheet systems. As the careful development of spreadsheets becomes a legal obligation, organizations must use strong technical and operational controls to curb systems from falling short in their fight against fraud.
Data Sharing Risks
As the spreadsheet becomes a frontend to multiple data sources, it becomes a collection of multiple snapshots of corporate data. A simple scenario is that multiple spreadsheet users pull data from enterprise repositories. Once the spreadsheet user interprets this data, they can perform some manipulations and finally commit the work for approval, writing it back into the enterprise repository. 
The risks present in this method are that different people in the enterprise can end up operating on different versions of the data and, by extension, multiple versions of a spreadsheet may end up in circulation within the enterprise. What guarantees exist that when a person commits their work, they are not contradicting someone else’s work? How can the company sign off the data as an accurate picture of the company’s results?  And in a competitive setting, having correct, fresh data is not the only factor to consider – obtaining it in adequate time is also important for the business to not lag behind. 

One approach is to employ access control mechanisms. Rather than allowing everyone to manipulate data unhindered, operations can enforce a policy in which all data interactions go through the authority of one person that can be specified for each spreadsheet model. From a technical point of view, protecting a worksheet using conventional locking over data, is a common spreadsheet feature. A strategy where one user is solely responsible for data interactions at a time is a natural option to address potential conflict in data. Nonetheless, this approach is nontrivial and requires subtle programming skills in security and data sharing.
Furthermore, this approach struggles when scaling to larger organizations. As all updates of the data go through one person, the amount of effort for several users to perform a task, in general, is increased as communication becomes unavoidable and people can be temporarily blocked from their operations creating a bottleneck  in the process. The adoption of strategies must, therefore, be carefully considered. 
Another strategy is versioning by maintaining different versions of the data. This strategy generates a unique version number with each update operation and one can, for example, accept only spreadsheet manipulations with a specific version number while rejecting all stale or outdated spreadsheet data manipulation. When comparable versions are present, one must apply a conflict resolution strategy which can use fully automated procedures, or other automated procedures assisted by human intervention and judgement in the case of complex cases. While this technique protects users from working on stale data and allows for larger teams to operate quickly, users can still operate under various versions of data. 
Adoption of a technique depends on the nature of the business process and the actual data. One must consider the blending of both techniques based on the business case, the number of people using the spreadsheet, how critical or sensitive the data is, and the amount of effort which one strategy imposes on operations. One must also consider the data usage pattern, the nature of the data source (one accounting package may offer seamless version control while another does not) and the granularity of the resource (can only certain spreadsheet cells be locked?). 

Spreadsheet Engineering
User-induced error has been the cause of many spreadsheet mishaps. Users may enter data instead of a formula or use formulas to refer to the wrong spreadsheet cell by mistake. How can one create a spreadsheet that can withstand the challenges of the real world?
One aspect to consider is the structure of the spreadsheet, both in terms of its relationship with other spreadsheets and within the spreadsheet itself. In fact, spreadsheet relationships can be characterised by a network of dependencies. Such dependencies come at different levels: one cell can depend on another cell and one spreadsheet can depend on another spreadsheet. Dependencies can also be of different forms, for example formula dependencies or copy-paste dependencies. Thus, when looking at a spreadsheet, one needs to consider it in the broader context of its relationships.
It has been established that through visualization [1] of the dependency network that governs a spreadsheet, the spreadsheet user can understand whether certain design decisions are problematic. Such visualizations can be enhanced through the automatic flagging of undesirable patterns which help the user navigate through larger networks (larger spreadsheets) and detect less obvious patterns.
In this area, a number of software engineering patterns have been directly applied to spreadsheets. The term spreadsheet smells [1] has been coined, adapted from source code smells [2], where a smell is a pattern that suggests a problem at a deeper level. Ultimately, simplicity is the key goal of any design. Some patterns that can be considered are: 
  • The removal of duplicated dependency chains
  • The shortening and simplification of a dependency chain
  • Understanding what data source lies at the end of a dependency chain – one can attempt to refactor the dependency to keep the number of data sources to an absolute minimum
  • The rewriting of complex formulas in a more concise way, e.g. use of SUM(...) rather than addition (+).
  • If spreadsheet cells are excessively referred to by another spreadsheet, can they be relocated?
  • If spreadsheets have multiple back and forth references to each other, can the problem be expressed in such a way to avoid this pattern?
  • How can a cell whose changes in turn affect many other cells minimize its impact? 
Despite best efforts at spreadsheet engineering, the spreadsheet can remain prone to errors and putting the spreadsheet under test is another important consideration. Testing does not need to be the final stage of the design process And may, instead, be done with every change. This phase is similar to software testing. 
The investment in this technical control acts as a sanity check – a guarantee that certain functionality works as intended as the spreadsheet matures. One can look at the behaviour of the spreadsheet structure: does the input to the spreadsheet give the expected output data? Testing must not cover only the most common usage scenarios, but also the most abnormal ones – they can be considered normal by a (future) colleague. Due to the exhaustive nature of this activity, limiting testing to the most crucial of spreadsheets is a natural consideration.
Concluding Remarks
As the amount of data available to spreadsheet accountants grows, and as the demand for timely data becomes more relevant, exposure to risk increases, especially if spreadsheets are unprotected. Careful design of spreadsheets can no longer be an afterthought. The consequences are clear and ever-present. 
By evaluating the nature of the data in terms of its sensitivity to the business, its source and its place in the spreadsheet structure, and by employing effective data governance strategies and business procedures, a great deal of risk can be minimized. Such controls can be approached both from a technical and operational standpoint. A significant input is an accountant’s own expertise in control and business process understanding.
Many spreadsheet users have learnt to progress and structure their design while intuitively attenuating risks. Nonetheless, with data sharing and data consolidation requirements, these risks call for more sophisticated risk-aversion techniques. This article discussed such techniques and presents a way forward. Spreadsheets are killer applications, but do not let spreadsheets kill your business
[1]  F. Hermans, M. Pinzger and A. van Deursen, “Detecting and visualizing inter-worksheet smells in spreadsheets”, 34th International Conference on Software Engineering (ICSE), 2012. [2]  M. Fowler, Refactoring: improving the design of existing code. Boston, MA, USA: Addison-Wesley Longman Publishing Co., Inc., 1999
The insights in this article were gained through collaboration with Scope Solutions, a local expert in cloud-based software whose solutions are sought after by accountants. This article is part of DataDear, a project about empowering SMEs with tools to help them move to Cloud Computing. This project is financed by the Malta Council for Science and Technology through Fusion: The R&I Technology Development Programme 2017.
Public Spreadsheet Blunders
A number of spreadsheet errors have been publicly reported across various domains, including large corporations, elections, scientific analysis, and schools. Typically, they are the result of human error or fraud, and the outcome is in the form of regulatory, reputational, and commercial headaches. The following is a brief selection of such cases in the accounting world.
Spreadsheet errors cost Clallam $494,157
Rob Ollikainen, Peninsula Daily News, 2017
• Spreadsheets were emailed back and forth in the Clallam County Office’s (Washington, U.S.) operations. Due to cut and paste, not all the formulas were pasted correctly. A deficit was reported to be much larger than previously calculated. This error led to a reduction of budgeting for personnel by 12%.
• The commission was asked to reflect on the following two points: (a) could different budget decisions have been made had the true deficit been known? and (b) who is accountable for the mistakes?
M&S results hit by spreadsheet mishap
John Stokdyk, AccountingWEB, 2016
• Marks & Spencer’s (UK) chief financial officer reports that a spreadsheet summing error, a double-counting error, while compiling the quarterly statement, forced the retailer to issue a correction to its trading statement.
• A statement issued at 7AM reported sales to grow by 1.3%. But at 1:31PM, a correction was released showing that sales had in actuality fallen by 0.4%. Consumer confidence was reported to have suffered, and reporting procedures were put under review.
Trustee’s Office mistake to cost taxpayers $12,500
Mika Donila, Knox News, 2011
• Auditors discovered a $6 million accounting error by the Knox County (Tennessee, U.S.) Trustee’s Office. It occurred when one account was not correctly linked with the spreadsheet.
• Extra effort was incurred as a result of procedures at the Trustee’s Office to address the issue – about 75 hours at the cost of $12,500 out of the taxpayer’s pocket.
Thomas Mercieca graduated with a BSc (Hons.) in IT from the University of Malta. He has later worked in the telecommunications industry and is currently working as a researcher at the University of Malta’s Faculty of ICT. His main research interests include database architecture and data modelling. He can be reached at thomas.mercieca@um.edu.mt.
Dr Joseph G Vella lectures and researches in the areas of database technology at the University of Malta. His first degree was a BSc in Mathematics and Computing (UM) and a doctoral degree from the University of Sheffield Engineering Faculty. Dr Vella has participated in numerous projects at national and EU levels mainly dealing with data integration and consolidation for data warehousing and cloud services. Contact email is: joseph.g.vella@um.edu.mt.
Dr Kevin Vella is the Head of the Department of Computer Science within the University of Malta’s Faculty of ICT. His teaching and research activities focus on the scientific and technical aspects of concurrent and distributed computing, computer operating systems and programming languages. He holds a PhD in Computer Science from the University of Kent, UK, and a BSc in Mathematics and Computing from the University of Malta. His contact email address is kevin.vella@um.edu.mt.

Suite 4, Level 1, Tower Business Centre, Tower Street, Swatar, BKR 4013, Malta 

E-mail: info@miamalta.org

Tel. +356 2258 1900