Excel: When Good Models Go Bad

Microsoft Excel is virtually ubiquitous. It is everywhere in the business world and, more significantly, used by almost everyone. While some Excel spreadsheets do nothing more than handle simple calculations, others can deliver complex modeling and computational skills to managers who have little or no understanding of math.

Unfortunately, Excel has proven to be a double-edged sword. On the one hand, the program’s widespread adoption, intuitive nature and familiarity to managers have given businesses much wider access to a valuable management tool. On the other hand, because of its ease of use, Excel users may not understand the complexity and sensitivity of a spreadsheet’s underlying calculations. This lack of understanding paves the way for user errors, which, in turn, produce badly skewed data for decision making.

Nothing illustrates this duality of Excel more clearly than optimization modeling, states Larry LeBlanc, Professor of Operations Management and Information Technology at Vanderbilt Owen Graduate School of Management and a noted speaker and consultant on this topic. Simply put, an optimization model uses mathematical equations to seek the best possible choice out of a set of alternatives given constraints or limitations. Companies of all sizes and industries—manufacturers, airlines, financial services firms and hospitals, to name a few—use optimization models to manage their operations more cost effectively.

Traditionally, LeBlanc notes, optimization modeling was the exclusive preserve of an elite group of experts. They used their knowledge of advanced mathematics and mainframe computer programming to maintain their exclusivity. However, the models—expressed as algebraic equations—were so complex that they were beyond the abilities of most managers even to understand, much less to produce. Consequently, managers were reluctant to authorize their use.

Times have changed. With significant advances in computing power and software, a model that just a few years ago would have taken two months to solve can now be solved in about one second. Concurrently, the ever-increasing complexity of the global marketplace is making optimization even more important to organizations. With easier access to such powerful tools, more companies are turning to optimization models to help make crucial decisions including tasks such as locating facilities, routing traffic or calls, allocating inventory, assigning vehicles, planning production, blending chemicals, allocating advertising dollars, optimizing portfolios and planning and scheduling personnel.

Additionally, the familiarity of the Excel spreadsheet plays a critical role in the widespread adoption of optimization models. The simple “column” and “row” format has helped make optimization modeling understandable to and more intuitive for senior managers who lack expertise in algebraic models. And the ease of creating and assigning mathematical tasks to cells within a spreadsheet allows even nonspecialists to develop their own “end-user” models. As a result, spreadsheet-based optimization models are taking hold in companies of all sizes, from small and medium-size businesses to large organizations such as American Express, Ford, DuPont, International Paper, Wells Fargo, Microsoft, Boeing and IBM.

As the widespread use of optimization models accelerates, so does the potential for error, warns LeBlanc.

Spreadsheet errors go far beyond simple mistakes in data entry, which can never be eliminated completely. Many mistakes arise from the inherent complexity of optimization modeling. For example, a technical expert within a company may develop a spreadsheet and then turn it over to managers who—because the formulas developed by the expert are opaque to them—do not understand how to update the model correctly as business conditions change.

“Only recently,” says LeBlanc, “have companies begun to recognize spreadsheet errors as a significant problem.” That problem, moreover, may be even more widespread than many businesses think. A 2007 study by Stephen Powell, Kenneth Baker and Barry Lawson of Dartmouth College of 25 operational spreadsheets from five different organizations confirmed 117 errors, one of which had a $100 million impact on the model’s results. The European Spreadsheet Risk Interest Group (EuSpRIG), an organization dedicated to promoting best practices in the field, has studied the prevalence of spreadsheet errors.
“One error in a formula cell can proliferate and magnify the impact of the mistake,” explains LeBlanc. “Complex spreadsheets often have at least 100 formulas, and it wouldn’t surprise me to find 1,000. Even a 1 percent error rate would mean a very high chance of at least one error in many spreadsheets.”  

With Professor Michael Galbreth of the University of South Carolina, LeBlanc has studied ways to reduce or eliminate some of the most common types of spreadsheet errors in optimization modeling. Overwriting formulas, for example, can set in motion a chain of miscalculations. Often, LeBlanc says, companies will repeatedly run a supply chain optimization model as business variables change, as when customers are gained or lost or unit costs rise. Spreadsheets make it easy to enter new data directly into existing models—a stark contrast with algebraic models, which nontechnical managers would generally not dare to modify. However, if a user enters new data into a cell containing a formula (such as a row sum or column sum), the entire optimization model will be corrupted. This corruption may not be apparent to the user, creating a false sense of confidence in results that are erroneous.

How can businesses mitigate the risk of spreadsheet errors? LeBlanc offers a few best-practice suggestions for an organization’s essential spreadsheets.

Build in details and prompts. Developers can create a macro (a series of instructions to the computer that is triggered by a single keystroke) to automate the process of inserting detailed comments for cells containing key formulas in the model. The comments help reduce errors by providing all users of the model with key prompts—such as reminders not to overwrite information in particular cells.

Centralize version control.
Companies should implement some form of “version control” to make sure that all users have the current model. For example, instead of allowing users to maintain their own versions of a spreadsheet model to which they can make changes and additions, companies can maintain a central spreadsheet to which users have no direct access. Individual users pass data to this central spreadsheet, which automatically calculates the optimized solution and places it back into the users’ worksheets.

Increase awareness of the impact of errors. The critical first step toward managing these errors, LeBlanc states, is to identify them and raise awareness of their impact. Understanding what can go wrong, and why, is essential to ensuring that today’s abundant flow of information does not inadvertently lead to ill-informed, costly decisions.
Published Apr 1, 2009 in Vanderbilt Business Intelligence
Contact: vbintelligence@owen.vanderbilt.edu
Copyright 2009 Vanderbilt Owen Graduate School of Management