7.23 Spreadsheet Programs

Spreadsheets are among the most widely used of office tools for the analysis and presentation of financial data. The most popular are Microsoft's Excel and Lotus's Symphony, but many cheaper alternatives exist (e.g. Softmaker, Quatro Pro, iWork, Ability) and some free programs are excellent. Spreadsheet programs are more versatile than is generally realized, but can suffer limitations in use and design.

Features

Spreadsheets will be familiar to all in business and business studies. The application displays cells in a two-dimensional matrix of rows and columns. Each cell can contain alphanumeric text, numeric values or formulas. A formula defines how the content of that cell is to be calculated from the contents of any other cell, or combination of cells, and is updated each time any other cell is updated. Spreadsheets are invaluable for financial assessments and modeling, and commonly incorporate a third dimension by some linking of spreadsheet layers.

Spreadsheets are an example of the '80 : 20 rule', where some 80% of users customarily employ only 20% of the functions. Spreadsheets can in fact be made to serve as programming languages, and will generate the sophisticated graphs and visual displays of data needed in the natural and biological sciences. Many statistical packages use a spreadsheet program like Excel for data entry, and most spreadsheet packages accept comma delimited files (CSV {3}) exported from shopping cart and other programs.

History

The concept derives from a 1961 paper "Budgeting Models and System Simulation" by Richard Mattessich, and was implemented on several IBM mainframe computers in the years following. The program was further developed as LANPAR ( LANguage for Programming Arrays at Random), and used by Bell Canada, AT&T and the 18 operating telcoms nationwide for their budgeting purposes. There were several modifications subsequently, most notably in a program marketed by Capex that ran on General Electric's time share services, but the electronic spreadsheet attained its present form as Visicalc, a 'killer' application developed by Dan Bricklin and Bob Frankston that helped make the Apple II computer popular. Lotus 1-2-3 appeared in January 1983, and became the comparable application for the PC, soon overtaking Visicalc by virtue of its better speed, graphics, layout and and macros.

Microsoft, meanwhile, had been developing its own Excel program for the Mackintosh platform, and with the appearance of Windows 3x operating system were able to include a spreadsheet in their Office Suite and dominate the commercial electronic spreadsheet market from the mid 1990s. In the late 1980s and early 1990s appeared programs built on objects called variables, which could be displayed with branches and logical roots, a godsend for complex financial modeling but later used for more diverse purposes: college chemistry courses, economic modeling and even by the military in the early Star Wars project. Improved web technologies again changed the picture from 2005, when spreadsheet programs became available as online applications, some indeed offering multi-user collaboration features and real-time stock prices and currency exchange rates.

Limitations

Spreadsheet modeling can be unreliable. Research estimates that roughly 94% of spreadsheets as deployed contain errors, and that 5.2% of cells in unaudited spreadsheets also contain errors. {5} The main problems are: shifts of meaning in spreadsheets terms, auditing of complex, undocumented spreadsheets by third parties, spreadsheet redesign that severs previous links, collaborative efforts that do not limit access properly, memory limitations in software or computers that result in overflow errors, and the simple accumulation of human errors in spreadsheet systems that are not systematically checked. Some 55% of Capital market professionals profess not to know how their spreadsheets are audited, and only 6% invest in third-party solutions. {5} Errors can cost millions. {8}

Marketing Strategies

Lotus purchased the original Visicalc program, developing it further with named cells, cell ranges and spreadsheet macros. Microsoft displaced Lotus by bundling a free trial of their Office Suite with each computer sold running the Windows operating system. That financial muscle is now being challenged by the tools that come free with Google Docs, a big-company marketing tactic that Microsoft has itself complained about.

Questions

1. What are spreadsheet programs, and how are they employed?
2. Give a brief history of their development and marketing.
3. Explain their limitations, and give some examples of costly errors.

Sources and Further Reading

1. Spreadsheet Charting with Excel. Csub.edu. Many simple exercises detailed.
2. Creating Graphs in Google Spreadsheets. Brighthub. Simple step-by-step guide.
3. How Do I Create a New CSV File? eHow. One of many simple 'how to' guides on this site.
4. The First Spreadsheet — VisiCalc — Dan Bricklin and Bob Frankston by Mary Bellis. About.Com Brief article.
5. Spreadsheet. Wikipedia. Description, history, chart-making facilities, shortcomings of spreadsheets and good references.
6. The Spreadsheet Page. John Walkenbach's tips, resources and books: mostly for Excel.
7. Special Spreadsheet Issue: May 2005. AINewsletter. Takes the concept further, with examples and extensive references.
8. European Spreadsheet Risks Interests Group. Eusprig. Aims, best practices, horror stories and more.
9. Excel 2007 Charts by John Walkenbach. Wiley. October 2007.
10. Microsoft Excel 2010 In Depth by Bill Jelen. Que. July 2010.