Strategies and visualization tools for enhancing user auditing of spreadsheet models

Spreadsheets are very widely used at various levels of the organization. Studies have shown that errors do occur frequently during the development of spreadsheet models. Empirical studies of operational spreadsheet models show that a large percentage of them contain errors. However, the identification of errors is difficult and tedious, and errors have led to drastically wrong decisions. It is thus important to develop new strategies and auditing tools for detecting errors. A suite of new auditing visualization tools have been designed and implemented in Visual Basic for Application (VBA), as an add-in module for easy inclusion in any Excel 97 or Excel 2000 installation. Furthermore, four strategies are proposed for detecting errors. These range from an overview strategy to identify logical components of the spreadsheet model, to specific strategies targeted at specific types of error. Illustrations show how these strategies can be supported with the new visualization tools.     

Modeling Spreadsheet Audit: A Rigorous Approach to Automatic Visualization

Computations in spreadsheets are hard to grasp and consequently many errors remain unnoticed. The problem with the hidden errors lies in the invisibility of the structure of calculations. As a result, auditing and visualization tools are required to make spreadsheets easier to comprehend and to make errors easier to detect. This paper presents a theoretical model of spreadsheets and a technique to describe spreadsheet auditing tools. These are then employed to describe and compare various tools. Moreover, two new visualization mechanisms are introduced.The spreadsheet model reflects not only current spreadsheet systems but also the way people actually use spreadsheets. Theoretically, it is impossible to check the correctness of a spreadsheet without a formal definition of its computations, but our hope is to find visualizations that point out parts of spreadsheets that contain anomalies, i.e. potential locations of errors. The model helps us to understand how such anomalies can be defined.     

Hitting the wall: errors in developing and code inspecting a ‘simple' spreadsheet model

Field audits and experiments have found substantial error rates when students and professionals have built spreadsheet models. In this study, 102 undergraduate MIS majors and 50 MBA students developed a model from a word problem that was relatively simple and free of domain knowledge. Even so, 35% of their 152 models were incorrect. There was no significant difference in errors per model between undergraduates and MBAs. Even among the 17 MBAs with 250 h or more of experience, 24% of the models contained errors. The cell error rate (CER)—the percentage of cells with errors—was 2.0%. When 23 undergraduates attempted to audit their models through code inspection, only three with incorrect spreadsheets (15%) produced clean spreadsheets when they finished the audit.     

Hitting the wall: errors in developing and code inspecting a `simple' spreadsheet model

Field audits and experiments have found substantial error rates when students and professionals have built spreadsheet models. In this study, 102 undergraduate MIS majors and 50 MBA students developed a model from a word problem that was relatively simple and free of domain knowledge. Even so, 35% of their 152 models were incorrect. There was no significant difference in errors per model between undergraduates and MBAs. Even among the 17 MBAs with 250 h or more of experience, 24% of the models contained errors. The cell error rate (CER)—the percentage of cells with errors—was 2.0%. When 23 undergraduates attempted to audit their models through code inspection, only three with incorrect spreadsheets (15%) produced clean spreadsheets when they finished the audit.     

基于聚类方法的审计分层抽样算法研究

针对审计抽样中最复杂的抽样算法一分层抽样,从数据挖掘中“聚类”的角度出发,较好地运用了聚类思想于审计抽样的分层抽样算法之中,为该算法的实现提供了一种新的解决方案。AICPA39没有为分层抽样提供具体的实现方式,国内的学者曾从统计学角度有过实现,将从计算机科学角度实现方法与统计学实现方法进行分析比较,这是对分层抽样算法实现的有益新探索。     

Mutation Operators for Spreadsheets

Based on (1) research into mutation testing for general purpose programming languages, and (2) spreadsheet errors that have been reported in the literature, we have developed a suite of mutation operators for spreadsheets. We present an evaluation of the mutation adequacy of du-adequate test suites generated by a constraint-based automatic test-case generation system we have developed in previous work. The results of the evaluation suggest additional constraints that can be incorporated into the system to target mutation adequacy. In addition to being useful in mutation testing of spreadsheets, the operators can be used in the evaluation of error-detection tools and also for seeding spreadsheets with errors for empirical studies. We describe two case studies where the suite of mutation operators helped us carry out such empirical evaluations. The main contribution of this paper is a suite of mutation operators for spreadsheets that can be used for carrying out empirical evaluations of spreadsheet tools to indicate ways in which the tools can be improved.     

Database audit and control strategies

Database management systems are the primary tools of automated record keeping, reporting, auditing, and control. Although they have significantly improved the efficiency and speed of record keeping, the ability to detect errors and maintain quality has not kept pace. There are three major strategies of error detection, auditing, and control to maintain integrity. The three strategies are introduced, and compared in terms of efficiency and effectiveness in eliminating errors. The optimum timing of audits under each strategy is computed. One strategy is shown to be completely dominated by the others. Hybrid strategies involving various combinations of the three pure strategies are introduced, and their performance computed. Hybrid strategies are shown to outperform the pure strategies under most realistic conditions. Finally, optimum audit strategies are devised, and their parameters computed.     

Reasoning about spreadsheets with labels and dimensions

Labels in spreadsheets can be exploited for finding formula errors in two principally different ways. First, the spatial relationships between labels and other cells express simple constraints on the cells usage in formulas. Second, labels can be interpreted as units of measurements to provide semantic information about the data being combined in formulas, which results in different kinds of constraints.In this paper we demonstrate how both approaches can be combined into an integrated analysis, which is able to find significantly more errors in spreadsheets than each of the individual approaches. In particular, the integrated system is able to detect errors that cannot be found by either of the individual approaches alone, which shows that the integrated system provides an added value beyond the mere combination of its parts. We also compare the effectiveness of this combined approach with several other conceivable combinations of the involved components and identify a system that seems most effective to find spreadsheet formula errors based on label and unit-of-measurement information.     

The EDP auditor's role in computer security

EDP auditors are playing an increasingly crucial role in the computer security process. They are a new type of technical specialists. They work at the point where management of computing and the achievement of traditional auditing objectives converge. However, they use methods different from those long employed by conventional auditors. EDP auditors basically assure that management effectively controls the way in which computing is done and that records related to that data processing are current and accurate. However, they do not seek, primarily, to find fraud.     

Spreadsheet development and ‘what-if’ analysis: quantitative versus qualitative errors

Past research has shown that errors are relatively common in all types of spreadsheets. As spreadsheets are used widely by executives in analyzing and supporting their decision making, especially in financial analysis, budgeting and forecasting applications, it is important for spreadsheets to be accurate. Errors undetected in spreadsheets may have undesirable consequences. For example, errors may adversely impact the firm's competitiveness or profitability when the costing of projects is prone to incorrect computation. For this purpose, we investigate the types of errors that may occur even for simple domain-free spreadsheet problems. In addition, we also show that spreadsheet errors are difficult to detect during ‘what-if’ analysis (i.e. when some design parameters are changed) when spreadsheets are not properly designed. The results show that most students do not take due care in designing spreadsheets. It appears that the techniques in teaching spreadsheets should really focus on how to design a comprehensive spreadsheet that is both easy to maintain and debug rather than just demonstrating the many features of spreadsheets.     

Secure authentication in motion: A novel online payment framework for drive-thru Internet

The security and privacy issues have been well investigated in typical vehicle ad hoc networks. However, considering the drive-thru Internet properties, in particular for a secure and in-motion payment services case, merely implementing the existing online payment schemes may be either infeasible or inefficient. In this paper, we propose an advanced online payment framework, which integrates three main features, including the novel pairing-free certificateless encryption, signature and semi-honest RSU-aided verification, and the CA-aided tracking and batch auditing, and providing following properties independently, e.g., achieving a higher trust level and supporting primary security services, introducing a semi-honest RSU to indicate more practicality, and optimizing the verifying and auditing efficiency for a large number of authentication requests case. Performance evaluations such as security analysis, efficiency analysis, and simulation evaluation show the security and feasibility of the proposed framework.     

UCheck: A spreadsheet type checker for end users

Spreadsheets are widely used, and studies have shown that most end-user spreadsheets contain non-trivial errors. Most of the currently available tools that try to mitigate this problem require varying levels of user intervention. This paper presents a system, called UCheck, that detects errors in spreadsheets automatically. UCheck carries out automatic header and unit inference, and reports unit errors to the users. UCheck is based on two static analyses phases that infer header and unit information for all cells in a spreadsheet.We have tested UCheck on a wide variety of spreadsheets and found that it works accurately and reliably. The system was also used in a continuing education course for high school teachers, conducted through Oregon State University, aimed at making the participants aware of the need for quality control in the creation of spreadsheets.     

Automatic detection of dimension errors in spreadsheets

We present a reasoning system for inferring dimension information in spreadsheets. This system can be used to check the consistency of spreadsheet formulas and thus is able to detect errors in spreadsheets.Our approach is based on three static analysis components. First, the spatial structure of the spreadsheet is analyzed to infer a labeling relationship among cells. Second, cells that are used as labels are lexically analyzed and mapped to potential dimensions. Finally, dimension information is propagated through spreadsheet formulas. An important aspect of the rule system defining dimension inference is that it works bi-directionally, that is, not only “downstream” from referenced arguments to the current cell, but also “upstream” in the reverse direction. This flexibility makes the system robust and turns out to be particularly useful in cases when the initial dimension information that can be inferred from headers is incomplete or ambiguous.We have implemented a prototype system as an add-in to Excel. In an evaluation of this implementation we were able to detect dimension errors in almost 50% of the investigated spreadsheets, which shows (i) that the system works reliably in practice and (ii) that dimension information can be well exploited to uncover errors in spreadsheets.     

Systematic evolution of model-based spreadsheet applications

Using spreadsheets is the preferred method to calculate, display or store anything that fits into a table-like structure. They are often used by end users to create applications, although they have one critical drawback—spreadsheets are very error-prone. Recent research has developed methods to reduce this error-proneness by introducing a new way of object-oriented modeling of spreadsheets before using them. These spreadsheet models, termed ClassSheets, are used to generate concrete spreadsheets on the instance level. By this approach sources of errors are reduced and spreadsheet applications become easier to understand.As usual for almost every other application, requirements on spreadsheets change due to the changing environment. Thus, the problem of evolution of spreadsheets arises. The update and evolution of spreadsheets is the uttermost source of errors that may have severe impact.In this paper, we will introduce a model-based approach to spreadsheet evolution by propagating updates on spreadsheet models (i.e. ClassSheets) to spreadsheets. To this end, update commands for the ClassSheet layer are automatically transformed to those for the spreadsheet layer. We describe spreadsheet model update propagation using a formal framework and present an integrated tool suite that allows the easy creation and safe update of spreadsheet models. The presented approach greatly contributes to the problem of software evolution and maintenance for spreadsheets and thus avoids many errors that might have severe impacts.     

Model inference for spreadsheets

Many errors in spreadsheet formulas can be avoided if spreadsheets are built automatically from higher-level models that can encode and enforce consistency constraints in the generated spreadsheets. Employing this strategy for legacy spreadsheets is difficult, because the model has to be reverse engineered from an existing spreadsheet and existing data must be transferred into the new model-generated spreadsheet. We have developed and implemented a technique that automatically infers relational schemas from spreadsheets. This technique uses particularities from the spreadsheet realm to create better schemas. We have evaluated this technique in two ways: first, we have demonstrated its applicability by using it on a set of real-world spreadsheets. Second, we have run an empirical study with users. The study has shown that the results produced by our technique are comparable to the ones developed by experts starting from the same (legacy) spreadsheet data. Although relational schemas are very useful to model data, they do not fit spreadsheets well, as they do not allow expressing layout. Thus, we have also introduced a mapping between relational schemas and ClassSheets. A ClassSheet controls further changes to the spreadsheet and safeguards it against a large class of formula errors. The developed tool is a contribution to spreadsheet (reverse) engineering, because it fills an important gap and allows a promising design method (ClassSheets) to be applied to a huge collection of legacy spreadsheets with minimal effort.     

Spreadsheet quality assurance: a literature review

Spreadsheets are very common for information processing to support decision making by both professional developers and non-technical end users. Moreover, business intelligence and artificial intelligence are increasingly popular in the industry nowadays, where spreadsheets have been used as, or integrated into, intelligent or expert systems in various application domains. However, it has been repeatedly reported that faults often exist in operational spreadsheets, which could severely compromise the quality of conclusions and decisions based on the spreadsheets. With a view to systematically examining this problem via survey of existing work, we have conducted a comprehensive literature review on the quality issues and related techniques of spreadsheets over a 35.5-year period (from January 1987 to June 2022) for target journals and a 10.5-year period (from January 2012 to June 2022) for target conferences. Among other findings, two major ones are: (a) Spreadsheet quality is best addressed throughout the whole spreadsheet life cycle, rather than just focusing on a few specific stages of the life cycle. (b) Relatively more studies focus on spreadsheet testing and debugging (related to fault detection and removal) when compared with spreadsheet specification, modeling, and design (related to development). As prevention is better than cure, more research should be performed on the early stages of the spreadsheet life cycle. Enlightened by our comprehensive review, we have identified the major research gaps as well as highlighted key research directions for future work in the area.     

Computational complexity of auditing finite attributes in statistical databases

We study the computational complexity of auditing finite attributes in databases allowing statistical queries. Given a database that supports statistical queries, the auditing problem is to check whether an attribute can be completely determined or not from a given set of statistical information. Some restricted cases of this problem have been investigated earlier, e.g. the complexity of statistical sum queries is known by the work of Kleinberg et al. (J. Comput. System Sci. 66 (2003) 244–253). We characterize all classes of statistical queries such that the auditing problem is polynomial-time solvable. We also prove that the problem is coNP-complete in all other cases under a plausible conjecture on the complexity of constraint satisfaction problems (CSP). The characterization is based on the complexity of certain CSP problems; the exact complexity for such problems is known in many cases. This result is obtained by exploiting connections between auditing and constraint satisfaction, and using certain algebraic techniques. We also study a generalization of the auditing problem where one asks if a set of statistical information imply that an attribute is restricted to K or less different values. We characterize all classes of polynomial-time solvable problems in this case, too.     

ST-Audit: guideline-based automatic auditing of electronic patient records

This work presents the ST-Audit system that audits a patient record for conformance to a particular clinical guideline. The system uses ST-Guide which models a guideline as a set of states and transitions. The audit system tries to find a path in the state/transition diagram that corresponds to the actions taken by the physician, taking into consideration issues such as the unavailability of all the data needed to evaluate the transitions. The system was used to audit an outpatient clinic regarding their procedure for hypertension treatment (using the VI JNC guideline for hypertension), and the results of the number of non-compliant actions were presented and discussed. A follow up auditing showed a small but statistically significant reduction on the number of non-compliant actions for patients treated after the first audit.     

基于代理技术的ERP审计模型及研究

实施ERP系统已成为企业信息化的重要标志之一,一个良好的审计系统是ERP系统能够顺利运转的保障。文章剖析了现有ERP审计系统软件的主要优点及其不足,提出了一种基于代理的ERP审计方法设计思路及模型结构,并在最后总结了该方法的优点。     

一个柔性化物料管理系统的设计与实现

从物料管理系统中的柔性需求开始,紧紧围绕如何使业务对象和业务流程柔性化的问题,采用了单据驱动、面向对象抽象、审批引擎等手段设计并实现了一个具有柔性特征的物料管理系统.设计与实现过程中采用的思想、方法和技术对其他柔性化管理系统具有一定的借鉴意义.