Purpose of Project

The Spreadsheet Engineering Research Project is being undertaken by a team of researchers at the Tuck School of Business at Dartmouth. The purpose of this three-year project, funded by a grant from the National Institute of Standards and Technology (NIST), is to improve the design and use of spreadsheets by individuals and organizations. Spreadsheets and the software packages that have evolved to support their use have become one of the major tools for mathematical and statistical analysis for people at all levels of sophistication.

Millions of spreadsheet models are used each day to support decisions involving billions of dollars, and new spreadsheet models are constantly being developed. In fact, spreadsheets are ubiquitous in the business world. Although spreadsheets are computer programs, most are built by business analysts, not professional programmers. Very few business analysts are trained in software engineering, yet they routinely build spreadsheet-based software tools on which their firms depend. In spite of all this programming activity, little formal attention is given to spreadsheet engineering, the process of designing, building, testing and using spreadsheets efficiently and effectively.

Some efforts have been made to evaluate the quality of existing spreadsheet designs. These studies involve both laboratory experiments and examinations of software in use, with a focus on end-user programmers—that is, professionals who build spreadsheet models (or have them built) for their own specific business purposes. The evidence from these studies suggests that the quality of existing designs is poor. By implication, current practices in spreadsheet engineering need improvement.

In parallel, some authors have attempted to compile guidelines for spreadsheet design and use. At this stage, these efforts are not yet comprehensive, and in some cases, they even provide conflicting recommendations. These guidelines are generally based on personal experience and have not been tested for broader applicability. Thus, there is no general agreement on the principles of spreadsheet engineering, as there is for software engineering.

This research is aimed at improving the practice of spreadsheet engineering. Four activities are envisioned: Evaluation of spreadsheet models and modeling processes in use; Survey of corporate training and standards; Compilation of best practices; and Design of a training program.

The project began with field studies to collect information on spreadsheet engineering as it is actually carried out. From these observations, we are distilling the lessons and practices that account for the most efficient and effective design processes. Finally, we plan to convert this information into a teachable body of knowledge, in the form of a practical training program.

Task 1. Evaluation of spreadsheet models

We are auditing a significant number of models in actual use across a variety of companies. One objective of the audits is to assess the quality of design, technical correctness, and suitability for use of these models and to compare our findings with those described in the literature. A second objective is to identify the purposes for which the models were built and to determine whether they have been used for those purposes. A third objective is to learn about the process by which these models were developed and the life cycles that they have subsequently experienced.

Initially, our sources for this set of models have primarily been companies that have taken steps to acknowledge and address problems in spreadsheet engineering. While the primary targets are large, established businesses, we are also developing secondary contacts through consulting firms and software houses. Our field data consists of individual spreadsheet models, along with results from interviews of key personnel. Since our sample is intended to be representative rather than exhaustive, we emphasize models that come from different industries.

Based on this field study, we expect to compile a data set that describes the nature of spreadsheet models in actual use. This data set will delineate the current state of the art in spreadsheet engineering.

Task 2. Survey of corporate training and standards

The second aspect of our field study is to examine formal methods by which some firms have encouraged good spreadsheet engineering practices. For example, one consulting firm (Strategic Decisions Group) is known for setting internal standards and guidelines for its entire staff. Another consulting firm (PricewaterhouseCoopers) is known for developing an internal training course for its analysts. We will attempt to describe the environment these initiatives create for modeling and contrast it with the environment present in firms that have not taken initiatives with respect to standards or training.

In selected companies the Project Team is examining training documents and conducting interviews to understand their standards and training practices. We also expect to study the standards developed by software companies Frontline Systems (for optimization) and Decisioneering (for simulation), to look for implicit guidelines governing spreadsheet design in these areas of practice. Optimization and simulation represent some of the most powerful end-user applications for spreadsheet models.

Task 3. Compilation of best practices

We plan to begin this phase of the work by reviewing the frameworks and guidelines developed for the established field of software programming. These formal approaches to software development may apply only in part to spreadsheet work, but they should provide some ideas for best practice.

Next, we will review field audits (Task 1), and we will use the results to elaborate on mechanisms for avoiding common errors. Our field study should be broad enough to allow us to perform some cross-functional and cross-industry comparisons. Thus, we will attempt to extract lessons from one function or industry that can be broadly applied in others.

At this stage, we will review our findings on the existing standards and training programs to flesh out the description of best practice even further. Here, we will focus on the generality of existing initiatives. For example, we will ask whether standards that apply to spreadsheets built for financial decision-making can be useful in analyzing manufacturing systems.

The compilation may take the form of a report establishing a framework for best practices in spreadsheet engineering. This report should be of value to the companies that participated in the field work and others like them. We plan to publish the ideas in this report in a suitable journal.

Task 4. Design of a training program

The culmination of our work on this project will be the design of elements of a training program for spreadsheet engineering. To that end, we will solicit industry feedback on our best-practices framework and determine which portions are suitable for inclusion in a training vehicle. This feedback may come partly from holding a small conference on the topic and partly from lectures, workshops, and other presentations on project findings and conclusions.

Using the ideas in our framework, as enhanced by industry feedback, we will design an industry-oriented prototype for a training program on spreadsheet engineering and deliver it to an audience of practitioners.

Return to top