Stephen G. Powell

A critical review of the literature on spreadsheet errors

Among those who study spreadsheet use, it is widely accepted that errors are prevalent in operational spreadsheets and that errors can lead to poor decisions and cost millions of dollars. However, relatively little is known about what types of errors actually occur, how they were created, how they can be detected, and how they can be avoided or minimized. This paper summarizes and critiques the research literature on spreadsheet errors from the viewpoint of a manager who wishes to improve operational spreadsheet quality. We also offer suggestions for future research directions that can improve the state of knowledge about spreadsheet errors and mitigate spreadsheet risks.

Errors in Operational spreadsheets

Spreadsheets are used in almost all businesses, for applications ranging from the mundane to the missioncritical. Errors in the data, formulas, or manipulation of spreadsheets could be costly, even devastating. The received wisdom is that about 5% of all formulas in spreadsheets contain errors, and this rate is consistent across spreadsheets. However, this estimate is based on five studies, some of which are quite informal, and a total of only 43 spreadsheets. Our research was designed to deepen our understanding of spreadsheet errors. Specifically, we address three questions about errors in operational spreadsheets: what is the aver age cell error rate, how does it differ among spreadsheets, and what types of errors are most prevalent? We created a spreadsheet auditing protocol and applied it to 50 diverse operational spreadsheets. We found errors in 0.9% to 1.8% of all formula cells, depending on how errors are defined. We also found that the error rate differed widely from spreadsheet to spreadsheet. [Article copies are available for purchase from InfoSci-on-Demand.com]

Allocation of buffers to serial production lines with bottlenecks

The optimal placement of a predetermined amount of buffer capacity in balanced serial production lines is a well-understood problem: in lines with moderate variability, the optimal allocation involves equal numbers of buffers at each site; in lines with severe variability, the equal allocation is modified slightly to place more buffers toward the center of the line. Buffering unbalanced lines is a much less well-understood problem. We study the problem of buffering serial lines with moderate variability and a single bottleneck; i.e., a single station with a larger mean processing time than all other stations. Our analysis shows that a bottleneck station draws buffers toward it, but the optimal allocation depends on the location and severity of the bottleneck, as well as the number of buffers available. Furthermore, relatively large imbalances in mean processing times are required to shift the optimal buffer allocation away from an equal allocation. Finally, line length appears to have a relatively small e...

An algorithm for optimal buffer placement in reliable serial lines

The optimal allocation of buffer capacity in unbalanced production lines with reliable but variable workstations is a complex and little-researched topic. Analytic formulas for the throughput of these lines do not exist, so simulation is the only practical alternative for estimating throughput. Exhaustive search over all possible buffer allocations quickly becomes impractical beyond short lines and few buffers. Thus an algorithm is needed to efficiently find optimal or near-optimal allocations. We develop a simple search algorithm for determining the optimal allocation of a fixed amount of buffer capacity in an n-station serial line. The algorithm, which is an adaptation of the Spendley-Hext and Nelder-Mead simplex search algorithms, uses simulation to estimate throughput for every allocation considered. An important feature of the algorithm is that the simulation run length is adjusted during the running of the algorithm to save simulation run time when high precision in throughput estimates is not needed, and to ensure adequate precision when it is needed. We describe the algorithm and show that it can reliably find the known optimal allocation in balanced lines. Then we test the ability of the algorithm to find optimal allocations in unbalanced lines, first for cases in which the optimal allocation is known, and subsequently for cases in which the optimal allocation is not known. We focus particularly on lines with multiple imbalances in means and variances. In general, our algorithm proves highly efficient in finding a near-optimal allocation with short simulation run times. It also usually finds the true optimal allocation, but it is in the nature of this problem that many buffer allocations differ in throughput by small amounts that are difficult to resolve even with long simulation runs.

Impact of errors in operational spreadsheets

All users of spreadsheets struggle with the problem of errors. Errors are thought to be prevalent in spreadsheets, and in some instances they have cost organizations millions of dollars. In a previous study of 50 operational spreadsheets we found errors in 0.8% to 1.8% of all formula cells, depending on how errors are defined. In the current study we estimate the quantitative impacts of errors in 25 operational spreadsheets from five different organizations. Within these 25 spreadsheets we identified 381 potential errors, of which 117 (31%) were confirmed as errors by the developers of the spreadsheet. Among these confirmed errors, 47 (40%) had no quantitative impact on the results. Among the remaining 70 confirmed errors, the largest error was

Buffering unbalanced assembly systems

100 million; however, 9 of the 25 spreadsheets tested had no errors at all.

Methods for assigning students to groups: a study of alternative objective functions

Many assembly systems are required to hold minimal work-in-process inventories because of space or capital limitations. Unfortunately, in the presence of variable processing times, output from an assembly line may be severely restricted if no work-in-process inventory is held. Thus, assembly lines often should be designed to have minimal, but not zero, buffer capacity. In this paper we address the issue of how one should optimally deploy limited buffer capacity in unbalanced assembly systems, and we find that the answer is sometimes counterintuitive. We study simple asynchronous assembly systems with random processing times and develop simple heuristic rules that can be used to improve existing operations and to support line designers who are faced with increasingly rapid cycles of new product introduction. We apply these heuristics to several larger systems and discover that they perform quite well.

Throughput in Serial Lines with State-Dependent Behavior

This paper describes the problem of forming work groups. The motivating problem is often described as maximizing the diversity of members within groups, and also as minimizing the differences among groups. We review the formulations of this problem in the literature, paying particular attention to the different criteria that have been suggested. We compare the various criteria by solving a number of test problems using standard techniques and then examining the possibility that using one criterion will produce a result that is desirable on other criteria. Ultimately, we recommend a composite procedure for solving large versions of the group formation problem.

A comparison of free autologous breast reconstruction with and without the use of laser-assisted indocyanine green angiography: a cost-effectiveness analysis.

Experimental evidence suggests that production-line workers adjust their work rates in certain situations to prevent idle time. We refer to this asstate-dependent behavior, in contrast to thestate-independent behavior of machines. In this paper, we develop several models for the state-dependent behavior of production workers. We then use these models to analyze the relation of line length to throughput in these systems. We find that state-dependent behavior makes serial lines more efficient and reduces the detrimental effects that longer line lengths have on throughput. In some cases, line efficiency can actuallyincrease with length. This is a result of a higher percentage of workers having two buffers to provide feedback on the state of the line. Further, we show that workers who both speed up when they are likely to cause idle time for others and slow down when they are likely to become idle themselves improve the overall efficiency of the line.

Manufacturer incentives to improve retail service levels

Background: Laser-assisted indocyanine green angiography is a U.S. Food and Drug Administration–approved technology used to assess tissue viability and perfusion. Its use in plastic and reconstructive surgery to assess flap perfusion in autologous breast reconstruction is relatively new. There have been no previous studies evaluating the cost-effectiveness of this new technology compared with the current practice of clinical judgment in evaluating tissue perfusion and viability in free autologous breast reconstruction in patients who have undergone mastectomy. Methods: A comprehensive literature review was performed to identify the complication rate of the most common complications with and without laser-assisted indocyanine green angiography in free autologous breast reconstruction after mastectomy. These probabilities were combined with Medicare Current Procedural Terminology provider reimbursement codes (cost) and utility estimates for common complications from a survey of 10 plastic surgeons to fit into a decision model to evaluate the cost-effectiveness of laser-assisted indocyanine green angiography. Results: The decision model revealed a baseline cost difference of