When is a database not a database?

When it's better to use a database program.

It's no secret that accountants love their electronic spreadsheets. After all, the computer not only eliminates the onerous chore of manual number-crunching, it produces spreadsheets faster and better. But CPAs' romance with spreadsheet programs has not been smooth. The problem can best be explained by a Mark Twain aphorism that says, "If all you have is a hammer, everything becomes a nail."

Most accountants are quite handy with spreadsheet software - so handy, in fact, that many use their favorite programs to do more than just generate spreadsheets. The most common alternative use is to manage a database. They reason that if an electronic spreadsheet can manipulate, sort, filter and calculate numbers, why can't it do just as well with words? It can-sort of Let's focus on the "sort of" part of that answer - that is, when it is okay to use spreadsheet software to perform database functions and when it is plainly inappropriate because the software just isn't up to the task.

This article examines the advantages of using a database, presents a decision tree to determine whether a spreadsheet or database is most appropriate for a given accounting application and provides a representative list of off-the-shelf database software packages for the personal computer. (For more information on databases, see "Harnessing the Power of Databases," by Andrew D. Luzi and R. K. McCabe, JofA, July93, page 71.)

The authors conducted an informal survey of 100 CPAs working for national and regional CPA firms who provide management consulting services for clients. They were asked about their experiences with and concerns about, spreadsheets in business organizations. Overwhelmingly, the issue of spreadsheet misuse topped the list. The most frequently recommended solution was a database.

ADVANTAGES OF A DATABASE

Most spreadsheet packages include a few database functions, such as sorting, queries and limited statistical calculations. However, these functions are not as powerful as they are in database software - for these reasons:

* Data independence. Unlike in a spreadsheet, each item in a database is stored independently of any application that may access the data. It isn't necessary to anticipate just how these items might be used in the future when designing a database. In spreadsheets each item of data and the methods of manipulation (mathematical steps) are linked. That's what makes database software more flexible than spreadsheet software when managing data.

* Data redundancy. In a database, individual data items (such as a name, address and phone number) generally are stored in one place and then accessed via queries asked of the program. In a spreadsheet, data items often are duplicated many times as they are linked to other data at the user's command.

* Data consistency. Because data redundancy is controlled in a database, changes to items are updated immediately, and all users can access the same updated values. In a redundant spreadsheet environment, a customer name change may be updated to the spreadsheet in the personnel department, but accounting and marketing may not be notified of the change at the same time, if at all. Hence, a data consistency problem arises.

* Data sharing. Users throughout the organization, with appropriate permission, can access a database and make concurrent updates. If one user is updating a spreadsheet, other users are restricted to reading that same spreadsheet. This shortcoming can be alleviated if the software runs in a graphical user interface (GUI) environment or on a network.

* Data input control. The fourth generation language (4GL) provided by some databases allows users to format input screens with a host of editing and validating controls. A 4GL automatically generates programming code based on user instructions. For example, a user might create an input screen complete with appropriate controls and the 4GL will automatically generate the programming code. As a result, a common data input screen can be accessed by all users who are adding or editing records to a particular database, ensuring consistent input control. Spreadsheets provide some of these data input controls, but they require a sophisticated knowledge of spreadsheet programming. Additionally, it is difficult to ensure consistency of these controls from spreadsheet to spreadsheet throughout the organization.

* Flexible reporting. By posing queries, a database user can make logical connections among data items and produce a report that shows the connections - such as a list of customers who buy a particular product, are headquartered in a particular region and who generally take advantage of discounts for payment within 30 days. These reports may be "fixed" (the format and content are determined ahead of time) or "ad hoc" (the format and content aren't anticipated and can be developed and adjusted as the need arises). Users can quickly and easily format these reports with features such as headings, footings, column and row descriptors, subtotals, calculations and totals.

Spreadsheets also can produce quality reports, but producing ad hoe spreadsheet reports is cumbersome and often requires considerable skill in moving columns and rows, importing and exporting data and in creating the needed calculations. If the spreadsheet doesn't have some of the data needed for a given ad hoc report (because it wasn't anticipated when the spreadsheet was designed), the user either goes out the report or must obtain and enter the data into the spreadsheet.

* Fast and efficient application development. The 4GL allows users to quickly build menus (if the program is DOS-based) or icon-driven applications (if it's a GUI program) linking features such as input screens, queries and reports. For simple applications, no programming skills may be needed. For complicated applications, some programming may be required.

* User-friendly interface. A 4GL database application can be made quite easy to use with handy features such as context-sensitive help screens, look-up boxes, pulldown menus and prompting messages. Those 4GL databases designed for Windows allow for features such as mouse.

For an example of a 4GL dRiven icons, dialog boxes, still and moving pictures and sound.

For an example of a 4GL database screen designed to control data entry, see exhibit 1, at left. Each data entry box on the screen can be configured so that only specific information in a particular format can be entered.

MAKING THE DECISION

While some applications are clearly best suited for spreadsheets and others for databases, there are many applications for which either tool might be appropriate. While the decision tree in exhibit 2, page 92, can be helpful in making the choice, other organizational circumstances and task demands also may influence a decision. These four factors go into making the decision:

1. Data structure. If these relationships between the columns and rows of data are known in advance and are stable, a spreadsheet might be the answer. If the intended use of the data isn't fully known ahead of time and is subject to change from application to application, a database may be the better way to go.

2. Update frequency. This is a measure of how often data and formulas are added, changed and deleted. If the frequency is somewhat low-monthly or quarterly a spreadsheet might be sufficient. If the data and related formulas change more frequently, a database ma be easier to maintain.

3. Primary use. If the primary use is "what if" analyses involving complex formulas and myriad assumptions (interest rates, various anticipated sales volume levels), spreadsheets might be the best choice. However, if the primary purpose is to provide management with fixed and ad hoc reports generated from existing stored organizational data (sales activities, production analyses and budget analyses), a database could be the best solution. If the purpose is to analyze summary data (monthly totals of sales by product or monthly summaries of expenditures by department) and these data are rounded, for example, to the nearest hundred dollars, spreadsheets may provide adequate data control.

4. Data control. If source data (transaction data) are being captured, and control over the precise accuracy and integrity of the data is a concern, the database approach is likely the better one.

Generally, we cannot examine these decision criteria in isolation. When they are considered jointly, the decision becomes more complicated. The decision tree we have developed is an attempt to reconcile these important and sometimes conflicting criteria.

IN DEFENSE OF SPREADSHEETS

Some spreadsheet loyalists would argue that spreadsheet packages are capable of handling any number-crunching challenge. Indeed, these aficionados can and do create ingenious applications with spreadsheets. Despite their good intentions, they may be creating more long-term problems than they realize. When a spreadsheet application is first conceived - a cost accounting reporting system, for example - only a handful of people may be involved in its design and use. The application might involve several spreadsheets resulting in a cost variance analysis report. Perhaps only a few production managers rely on the reports in the beginning. In time, the application becomes larger and more sophisticated. More users become involved, creating many related spreadsheets linked in various ways, resulting in multiple spreadsheet layers. Also, more managers throughout the organization are relying on the information provided by the application.

Eventually, the application originators - if they are still around - lose control over the escalating mound of spreadsheets. Just a few formula or logic errors in the system can accumulate into a serious problem. Too many people, too many formulas, too many spreadsheets, too many layers and too few controls lead to inaccurate and unreliable information. The problem can go undetected for a long time. Although the precise cost of these errors is unknown, the polled consultants speculated such costs could be staggering.

Typically, a business recognizes the misuse problem as a result of an internal or external audit because it is difficult for those who are misusing spreadsheets either to recognize or to acknowledge they have a problem. The consultants first look to the adequacy of the accounting software.

The dependence on spreadsheets may be a result of outdated and inefficient accounting software. If so, the remedy may be to develop or purchase new software. If the accounting software is adequate or too firmly entrenched to change, the usual solution is to switch to a database. All of the surveyed respondents have helped one or more clients kick this spreadsheet misuse habit by converting to a database environment.

SELECTING A DATABASE

No single database meets every user's needs. At one end of the spectrum are databases geared to the end-user who wants to download, query and report data in a relatively simple and straightforward manner with little or no programming. For example, the end-user might want to retrieve all accounts receivable customer names and addresses from an existing master file, generate a subset of those customers from a given state or zip code and then print a listing and associated mailing labels. Such databases are designed mainly with text in mind.

On the other end of the spectrum are fully programmable databases - the 4GL databases. The 4GL software creates most of its own programming code based on what the user tells it to do. These databases are aimed at the more sophisticated developer-user, who might want more flexibility in formatting screens, queries and reports than end-users.

Some of the popular fully programmable and end-user database products are listed in exhibit 3, below. The table is a representative, not exhaustive, listing of the database software that's available. The prices shown are suggested retail; discounts typically are available from most local suppliers.

If you're using a spreadsheet for more than number-crunching, take a few minutes to work through the decision tree. You may find you are working very hard to make a spreadsheet do a database's job, and while the transition to a new computer tool is always difficult, in the long run, the time you will save and depth of reporting you can get out of the database will be well worth the trouble.

EXECUTIVE SUMMARY

* Accountants so love their electronic spreadsheets that they use them to manage databases also. In some cases that's okay, but in others, it creates all sorts of problems. * These factors determine whether it's best to use a spreadsheet or a database program:

1. Is the intended use of the data fully known in advance and subject to change from application to application? 2. Are the data and formulas added, changed and deleted frequently? 3. Is the primary use "what if" analyses involving complex formulas and myriad assumptions?

* CPA Consultants say many of their clients use spreadsheet software for the wrong chores. * Dependence on spreadsheet software may be symptomatic of outdated and inefficient accounting software.

JAMES E. HUNTON, CPA, PhD, is assistant professor of accounting at Virginia Commonwealth University, Richmond, Virginia. M. K. RAJA, PhD, is professor of information systems and management science at the University of Texas at Arlington. He is a network consultant and a member of the Association for Computing Machinery and the Institute for Management Sciences.

Last year Hunton and Raja were awarded the Lawler Award for the best article in the Journal of Accountancy in 1993.