Spreadsheet Modeling

A critical review of spreadsheet modelling The usage of spreadsheet applications, while incredulously recent-sounding, has been perhaps one of the most archetypal examples of computerization of business processes. Conceived by Dr. Richard Mattessich in 1961, an implausible half a century prior, electronic spreadsheets have outphased conventional paper forms of bookkeeping by promising accountants the privilege of partial accounting automation and in turn a revolutionary reduction in time consumption and human error. In spite of grandiose ideals of its further developers, the spreadsheet successfully commanded explosive attention only 20 years later.

Pioneered by IBM’s marketing strategy to bundle a spreadsheet derivation (the obsolete Lotus 1-2-3) into its commercial computer, the IBM PC, it enabled the application to reach segments beyond the niche industrial end users (Power, 2002). The decade between 1980 and 1990 bore witness to various media exalting the novelty spreadsheet application as the bane of many common accounting and auditing problems. Spreadsheets soon became indispensable in most firms and its functions have since then extended to encompass financial analyses and modeling.

However, as usage increased and took hold of the market share ubiquitously, it inevitably became what reviewers (Hendry et al, 1994) described as the main component of the tangled mass of problems it was originally conceived to solve. Ease of Usage While it is undeniable that spreadsheets offer simplification of accounting processes by eliminating repetition, which in turn reduces the possibility of errors, it does not omit the fact that the application highly relies on human input, which is the primary cause of errors (Brown and Gould, 1987). Spreadsheets shorten the learning curve for users who intend to conduct financial analyses, especially for those who do not come from a conventional financier or accountancy background. The absence of software in the past meant every calculation had to be manually performed, which not only limited financial analyses to those who possessed adequate technical knowledge, but also discouraged frequent executions of these analyses despite increasingly rapid commercial shifts that warranted such. It may be difficult to visualize a large conglomerate having to perform what-if analyses on paper for every change in financially relevant variable throughout its subsidiaries and their individual branches.

The omnipresence of low cost spreadsheet applications in office software bundles imply two very important transitions; small firms had access to analytical services previously available to firms that had financial means, and larger firms were able to astronomically shorten calculation times and increase frequency of running analyses. The current edition of Microsoft Excel provides various internalized functions such as the ‘What-If’ and ‘Goal Seek’ function (‘What if’ and ‘Goal Seek’, in layman terms, are comparable to logical deduction and adduction processes) complemented by automated mathematical precision and swiftness.

However, this does not change the fact that input error committed at primary levels will lead to inaccurate results, and more often than not these mishaps can result in millions of dollars in deviation from the actual value. While the impact of spreadsheet errors has been largely anecdotal, the European Spreadsheet Risks Interest Group (EUSRIG) documented several of these cases ranging from significant to colossal (O’Beirne, 2004). This is attributable to cross referencing errors that cause any inaccuracy in primary values to become exponentially inaccurate end-figures when referenced by other functions. Empirical studies(Panko, 2008) have pointed out that errors do not reduce significantly despite increase in usage and training, with their recent observations highlighting an 86% of spreadsheets produced by undergraduates containing at least one error, to findings involving spreadsheet developers which report a less, but still a significant error margin of 27%.

The increase in training may reduce incidences of formula misrepresentation, but does not reduce input error, which happens to be a cognitive ability. The reason behind this paradox is that the error probability from manual calculations is mitigated to the spreadsheet input process. Hence, errors do not diminish significantly, but are manifested in another form. While it can be argued that the pliable nature of cell-entry allows users to detect and correct errors more easily, the deceptively easy learning curve of spreadsheet appliances and accessibility have led to widespread outphasing of nearly every other alternative, and hence commercial users can be blindly led to a false sense of security by overestimating spreadsheet’s computational functions (McKie, 1998). However, it cannot be denied that spreadsheets have allowed models and simulations to be executed from normal PCs, and provide a less intimidating interface to users who may find themselves uneasy with the technical jargon that advanced statistical software may unnecessarily imply.

Model audit services are available in the market to troubleshoot modeling flaws, although they come at a price which may outweigh the benefits of using spreadsheets (Croll, 2007). Security and Control The mitigation of physical accounting to computerized accounting meant that documents could no longer be physically locked away from intrusion. With digital spreadsheets, the locus of control becomes loosened and cell data can be amended and overwritten easily if access is granted. This is exasperated by the transferability of digital files and therefore firms may underestimate the risks it poses when these files are kept in unprotected computers or networks. This means anyone who has access to the computer; accountant or technician can replicate the file or make unpermitted changes, further anyone who has access to the firm’s network can remotely obtain the file. The ease of replication also means that employees can privately store sensitive data.

While this argument can be countered by the fact that firms should only reasonably allow accountants or auditors access to them, the ease of use of spreadsheets has led to careless delegation of lower, menial accounting tasks to unrelated employees; a foolhardy attempt at convenience (Howard, 2005). Similarly, there is a tradeoff between file exclusivity and risk of file loss. It is sensible to keep backup copies of important data, but as copies multiply, the risk of breach reverberates and companies may not only find traces of unauthorized editing, but also the problem of significantly different copies that have diverged from the same original file(McDonough, 2005)

This happens when the spreadsheet goes through many hands at an attempt to facilitate multilevel data entry. However, keeping one copy can mean that the impact of its loss is irreversible. There can be a tendency for users to lump periods (months) of vital information in one large spreadsheet for ease of referral. Advances in information technology have led to development of security measures and integration of such measures into spreadsheet handling (for example, Enterprise Resource Planning) thus lowering security risks by a large extent, though not entirely.

Compliance with Financial Reporting Standards

Basic formats of spreadsheets do comply with the fundamental values of FRS, the enacting of the Sarbanes-Oxley Act (Hecht, 2002)requires companies to formally document and test their internal control mechanisms (as per Sections 302 and 404), which is extremely costly to adhere to. Many companies have been advised by external auditors to use spreadsheets as their application of choice to document these controls due to familiarity and cost effectiveness (Landers, 2008). However, limitations on spreadsheet software have caused problems in complying with the act, with Landers noting the usual drawbacks such as security concerns and lack of customized functions solely for stipulated documentation.

The mass of data generally means it has to be stored in hundreds or thousands of spreadsheets, thus limiting analysis or risk and controls between data, such as links between disclosure accounts. Due to its error-prone nature, firms employ a group of employees to manually check spreadsheets to ensure accuracy which depending on the size of the firm, is often expensive and time consuming. An average sized company with multiple cost-centres may need 6-8 weeks to work out all errors (Landers, 2008). The process of “cell-auditing” generally leads to large hidden costs when employing spreadsheets for documentation; although licensing fees for spreadsheets may pale in comparison to specialized documenting software, few firms have tracked down all costs related to spreadsheet usage for producing reliable documentation .

There have been recommendations to automate the manual aspect of managing spreadsheets, but interestingly these features have already been embraced by ERP software (Panorama Consulting Solutions, 2012) Usefulness of Information Provided Spreadsheets are traditionally useful in a sense that it provides a simple, structured platform to store multitudes of values and execute multiple formulas simultaneously. Reasonably, this implies that spreadsheets are probably more useful in smaller, personal context, where the users’ contextual understanding of the primary values is optimal. Critics (Burns et. al, 2012) have spoken out against the enterprise-wide use of spreadsheets, pointing out various numerical bugs found even in Excel 2007 and limited dimensions (256 columns)rendering it inappropriate for statistical usage.

Additionally, support for many of these spreadsheets is fairly lacking; there are no existing bug databases to inform end users regarding program irregularities. Spreadsheets also lack the contextual forte of advanced statistical software which is innately programmed to translate numerical values into statistically significant information. Similarly, the issues above have been reiterated by many industrial observers (Schwartz 2005, Almiron et al., 2010, Cragg and King, 1993) and recent changes in regulations have led to further intensification of existing criticism, urging change.

Companies which did switch to other software recorded noticeable improvement in processes and reduction in spreadsheet-related costs. However, the transition has been ploddingly slow. Industrial uptake of the spreadsheet software has created an illusion of blind conventionality where firms are reluctant to change due to cost factors or simply unwilling to take the risk.(Flack and Lynn, 2004). Conclusion In a nutshell, spreadsheets pioneered the computerization of commercial data, both accounting and non accounting,

Undeniably its sparkle lies in its capability of speeding up many manual processes during the era of its inception. However, increasingly harsh commercial situations and rapid changes have led spreadsheets to become somewhat irrelevant for usage at advanced planning levels and more appropriate for smaller-scale applications. Risks associated with spreadsheets, as discussed, are positively correlated to complexity of intended usage and the probability of the firm sustaining spreadsheet-related losses. Commercial industries are still suffering from “spreadsheetinertia”(Putnam et al., 1992), but many are transitioning to more sophisticated software such as Quantrix, in which the iconic spreadsheet still stands as the predecessor to nearly all of them. (1667 words excluding references)

References

Almiron, M. et al. (2010) On the Numerical Accuracy of Spreadsheets. Journal of Statistical Software, 34 (4), p.29. Brown, P. and J. Gould (1987) “An Experimental Study of People Creating spreadsheets.” ACM Transactions on Office Information Systems 5, 258-272. Burns, P. (2007) Burns Statistics — Spreadsheet Addiction. [online] Available at:
http://www.burnsstat.com/pages/Tutor/spreadsheet_addiction.html [Accessed: 24 Apr 2012]. Cragg, P. and M. King (1993) “Spreadsheet Modelling Abuse: An Opportunity for OR?” Journal of the Operational Research Society 44, 743-752. Croll, G. (2007) Spreadsheet Audit Methodologies in the City of London. EuSpRIG – European Spreadsheet Risks Interest Group. Flack,P and Lynn,B, (2004)” Who Relies on Spreadsheets- Too Many”, Financial Executive, September 2004. Hecht, C. (2002) Four Significant Accounting Changes of the Sarbanes-Oxley Act. [online] Available at: http://accounting.smartpros.com/x34948.xml [Accessed: 24 Apr 2012]. Hendry, D. and Green, T. (1994) Creating, Comprehending and Explaining Spreadsheets: A Cognitive Interpretation of what Discretionary Users Think of the Spreadsheet Model. International Journal Human-Computer Studies, (40), p.1033-1065. Howard, P. (2005) Managing spreadsheet fraud • The Register. [online] Available at: http://www.theregister.co.uk/2005/04/22/managing_spreadsheet_fraud/ [Accessed: 24 Apr 2012]. Landers, G. (2009) Compliance: What about the Spreadsheets?. [online] Available at: http://www.sox.com/dsp_getFeaturesDetails.cfm?CID=1944 [Accessed: 24 Apr 2012]. McDonough, D. (2005) Spreadsheet Security – – Why spreadsheets fall between the cracks – Part 3. [online] Available at: http://it.toolbox.com/blogs/spreadsheets/spreadsheet-security-whyspreadsheets-fall-between-the-cracks-part-3-5513 [Accessed: 24 Apr 2012]. McKie, S., (1998) Budgeting Beyond the Spreadsheet [online] available at http://businessfinancemag.com/article/budgeting-beyond-spreadsheet-1201 [Accessed: 26th March 2011] O’Bierne, P. (2004) European Spreadsheet Risks Interest Group – spreadsheet risk management and solutions conference. [online] Available at: http://www.eusprig.org/horror-stories.htm [Accessed: 24 Apr 2012]. Panko, R. (1997) Errors Found in Spreadsheet Development Experiments. [online] Available at: http://panko.shidler.hawaii.edu/SSR/devexpt.htm [Accessed: 24 Apr 2012]. Panorama-consulting.com (2000) ERP Software Selection | ERP Implementation | Organizational Change Management. [online] Available at: http://panorama-consulting.com/ [Accessed: 24 Apr 2012].

Power, D. (1978) Brief History of Spreadsheets, v. 3.6. [online]