top 

Home

Publications

Newsletter

 Advertising on themanager.org

About themanager

Electronic Spreadsheets: The Good, the Bad & the Ugly.

Rick Hesse, DSc, Chair and Professor of Decision Sciences

Graziadio Business Report, 2007, Vol. 10, Issue 2
This article is copyrighted and has been reprinted with permission from Pepperdine University

In this Editor's Note we invited Professor Rick Hesse to contribute an article on using spreadsheets. Dr. Hesse has taught management science using spreadsheets since 1982 in both engineering and business schools, and at both the graduate and undergraduate levels. Spreadsheet analysis is a very visual technique to help improve business operations and profitability.

On the 25th anniversary of the development of electronic spreadsheets, the good news is that spreadsheets have had a positive impact on the way businesses operate behind the scenes—from finance and accounting to operations and marketing and human resources—and they save time. The bad news is that training is woefully inadequate and haphazard. The ugly news is that spreadsheets are full of errors, with a majority of firms using them in some way for financial reporting that falls under the Sarbanes Oxley act.

The Good

History

The real breakthrough for business use of personal computers (PCs) was the development of VisiCalc, the first electronic spreadsheet, and its successors: Multiplan, Lotus 1-2-3, Excel, and Quattro Pro. Such programming has meant that results can be shown on the PC screen in a spatial, visual way that the average person can easily view and understand. There is no need for a compiler—results are immediate.

Programming is no longer linear, relegated to the realm of an elitist functioning behind the scenes, or to an arcane way of thinking (computing) "behind closed doors." It is now an open, common-sense, real-time, and spatial way of looking at calculations. In addition, the spreadsheet allows for a collaborative learning approach, since templates are shared within and across companies versus taking the individualistic approach to computer programming.  Users can now design their own layouts using a few simple rules, and thus employees become rule-makers themselves.[1]

Benefits

In their January 2006 cover story, Business Week highlighted Excel as one of the necessary tools for business analysts.[2]

Excel, the de facto standard, is an essential tool in business today. Wayne Winston, an MBA professor at Indiana University, has trained thousands of middle managers in industry in using Excel. He estimates conservatively that there are a million analysts in Fortune 500 companies who could benefit from such training. Professor Winston estimates that analysts do 90 percent of their analysis with Excel.[3]

For example, numerous students in my MBA Quantitative Analysis class have reported success with models or Excel skills they learned:

·         After demonstrating how he could use LOOKUP tables and data analysis tools, one student working at an aerospace company received a promotion even though he was not yet eligible in terms of length of service.

·         After learning how to setup a Capital Budgeting modeling using the Solver in Excel, one student, who was also a manager, was able to have an assistant come in early each morning to allocate funds for a non-profit fund, then the student, as the manager, could just check the results during the day.

·         Another student, after completing her MBA, received a bonus from her company, but got an unsolicited employment offer from another firm. Because her company did not want to lose her Excel and Access skills, it matched and increased that offer.

·         Another student changed jobs in the field of reliability testing. In his new job he did not have specialized software, but knew that Excel should be able to do a curve fit for the Weibull distribution. He emailed for help, and I was able to retrofit one of my curve-fitting templates to give the correct results. A few months later the student was finally able to get the expensive software which confirmed the Excel results.

·         Another student found the Data Sort option in Excel saved her over two hours a day of work.

·         In her work for the Army Corp of Engineers, my daughter taught a colleague how to use a pivot table and thereby reduced eight hours of work on a single spreadsheet to two minutes.

The Bad

In business, spreadsheets are more "caught" than "taught." Because spreadsheets look so simple, it is often thought that ordinary, non-programming business people will just grasp how to use them. Employees often share among themselves little tricks and traps along with their spreadsheet templates, but formal training is not offered in most businesses.

As college graduates entered the workforce, it was thought that they would infuse each organization with these new skills and obviate the need for training in using spreadsheets. As a result, training businesses faltered, and it was left to Human Resource (HR) departments to provide any necessary training internally. However, such departments did not (and do not) provide instruction in the functional skills of finance, accounting, marketing or management science, nor do such programs offer very effective training in fully utilizing spreadsheets. Consequently, HR was ignoring the hidden costs of not training for these skills.[4]

One of the main issues is that even though employees and employers know that spreadsheets save time and increase efficiency, there is no way to measure the cost-benefit.

With automated drawing and drafting, there are benchmarks on how long it takes to develop a drawing and how many revisions are necessary to be able to show the worth of programs like AutoCad. However, spreadsheets do not have such benchmarks or measures for how much time is saved. Research has shown that logical reasoning skills significantly increase after just six weeks of training on spreadsheets.[5] However, how do you quantify the financial benefit of that skill?

My experience in 10 years at Pepperdine teaching almost 1,000 fully-employed MBA students has not shown any noticeable improvement overall in computer or spreadsheet skills among entering students. While some students are quite adept at learning the skills, others are totally clueless.

Employers are usually amazed at what these students learn to do with spreadsheets, and some are dubbed by their employers as being "queen" or "king" of spreadsheets. However, the danger then becomes that the employee spends more time helping other employees than in getting their own work done.

Only a small percentage of learners can pick up a manual, watch a video, or attend a training session and become proficient in using spreadsheets. The majority of learners often need someone to coach them through the different functions or aspects they need for a particular task. Over time they then accumulate enough familiarity to competently handle spreadsheets. (Even I learn something new every semester from my students, which I in turn incorporate into the quantitative courses.)

Perhaps one reason that training companies and their materials failed to help people learn to use spreadsheets is because of a computer mainframe, linear mentality. They thought that by teaching several commands or functions, a trainee should then know how to program. Rather, it is a matter of taking an employee's work and laying it out to see where the connections are.

At Pepperdine, dedicated staff members of academic computing tried to develop training materials and seminars for our graduate students in business, education, and psychology. However, since most of the IT staff do not know these fields, they were ineffective in applying their computer programming knowledge to practical applications. Students often comment they learn much more about using spreadsheets in a quantitative, finance, or accounting class because it applies to their work.

What we have been left with is training by business and engineering schools, mostly for undergraduates. Even in MBA programs, the assumption is that students know spreadsheets or will pick up the knowledge from their classmates or at an introductory session. Textbooks that use spreadsheets generally do not spend much time teaching the basics and count on the fact that using them in an application will teach more than the traditional approach to teaching programming.

The Ugly

Ray Panko, at the University of Hawaii, has done extensive research in the area of spreadsheet errors and has made some startling discoveries.[6] His research shows that:

·         One percent of formula cells have errors

·         Ninety-five percent of U.S. firms (and 80 percent in Europe) use spreadsheets in some form for financial reporting, and thus are subject to the Sarbanes-Oxley Act

·         Ninety-four percent of 88 spreadsheets audited in seven studies contained errors

Additionally, in 2003, Fannie Mae admitted to making a $1.2 billion error in calculating third quarter earnings on a spreadsheet, while a former vice-president of HealthSouth admitted to inflating earnings by $3.5 billion with a false spreadsheet made up for auditors.[7]

There have been numerous articles warning of the dangers of spreadsheet errors.[8] Most companies have no error checking procedures, unlike computer programming, which counts errors per thousands of lines of code. Many organizations have no procedures for developing and then updating spreadsheets.

Further compounding this problem are the built-in errors in spreadsheets such as Excel, which concern many statisticians.[9] In a previous article I have already warned about the improper trend fits provided by Excel for nonlinear models.[10]

It is clear that employees need enough knowledge of spreadsheets to:

·         Be able to use a template correctly,

·         Develop a useful working template, and/or

·         Change a working template[11]

In addition, it is clear that spreadsheets should be checked for errors, either through built-in checks or by peer review.

Recommendations

Seek out the best of your company's spreadsheet developers to form an ad hoc committee and appoint them to do the following:

·         Design training materials that are specific to your business to illustrate functions and commands in Excel

·         Set standards for developing, documenting, and changing spreadsheets

·         Require at least two people to check spreadsheets for errors of omission, commission, and transmission

·         Use the each one/teach one method (Make sure that each person who is taught passes on the knowledge to at least one coworker)

·         Take spreadsheets seriously—when spreadsheets are used effectively, they offer tremendous efficiencies to your workforce

---------------------------

Dr. Rick Hesse is currently a professor and Chair of Decision Sciences at the Graziadio School. He has taught in engineering schools at Mercer, West Point, and Georgia Tech, and in business schools at Wake Forest, San Diego, and Southern California. Since 1982 he has been writing a quarterly column in Decision Line, "In the Classroom." The column has featured teaching tips mainly about spreadsheet use for solving quantitative models used in business and industry. Dr. Hesse has written articles in Interfaces, Operations Research, Decision Sciences, and several textbooks. He has won numerous teaching awards, including the national Decision Sciences Innovative Instructional Award, the Outstanding Civilian Service Medal from the Department of the Army, and Teacher of the Year award at the San Diego State School of Business. rick.hesse@pepperdine.edu

---------------------------

[1] J.E. Baker, S.J. Sugden, "Spreadsheets in Education: The First 25 Years," Spreadsheets in Education, (2003).

[2] S. Baker with B. Leak. "Math Will Rock Your World," BusinessWeek, (January 23, 2006): 54-60.

[3] Wayne L. Winston. "Executive Education Opportunities," OR/MS Today, August (2001).

[4] A. Bellinger. "Tackling the Hidden Cost of Spreadsheets," IT Training (July 2005): 14.

[5] S. E. Kruck, J. J. Maher, and R. Barkhi. "Framework for Cognitive Skill Acquisition and Spreadsheet Training." Journal of End User Computing, 15, no. 1, (2003): 20-38.

[6] Ray Panko. "Facing the Problem of Spreadsheet Errors," Decision Line, (October 2006): 8-10.

[7] J. P. Caulkins, E. L. Morrison, and T. Weiderman. "Are Spreadsheet Errors Undermining Decision-Making in Your Organization?" Nonprofit World, 24, no. 3,(May-June, 2006): 26-28.

[8] D. A. Hicks. "The Value of Graphics in Communication," IIE Solutions, 30, no. 7,(July 1998): 18-20. H. Howe, M.G. Simkin. "Factors Affecting the Ability to Detect Spreadsheet Errors," Decision Sciences Journal of Innovative Education, 4, no. 1, (2006): 101-122. S.E. Kruck. "Testing Spreadsheet Accuracy Theory," Information & Software Technology, 48, no. 3, (2006): 204-213. C. Randles. "Spreadsheets: Often Don't Add Up," Design News, 60, no. 4, (2005):24.

[9] B. D. McCullough, B. Wilson. "On the Accuracy of Statistical Procedures in Microsoft Excel 2003," Computational Statistics & Data Analysis, 49, no. 4, (2005):1244-1252.

[10] Rick Hesse. "Incorrect Non-Linear Trend Curves in Excel," Foresight, 1, no. 3, (2006).

[11] T. J. McGill, J. E. Klobas. "The Role of Spreadsheet Knowledge in User-Developed Application Success," Decision Support Systems, 39, no. 3, (2005): 355-369.

---------------------------

This article first appeared in Graziadio Business Report, 2007, Vol. 10, Issue 2

Management Books worth reading now