Spreadsheets: faster, smarter.

Here's what you need to know to choose one of the powerful programs available today.

Until the early 1970s, accountants prepared spreadsheets by hand--methodically punching away on calculators and filling in the columns and cells one at a time. Then, in 1974, the Visicalc spreadsheet program was introduced. Although it was clumsy, it revolutionized the profession, crimping sales of the once-ubiquitous columnar pad. Soon thereafter the more powerful SuperCalc and then Multiplan spreadsheet programs were introduced, finally followed by Lotus 1-2-3, which quickly became the standard for many in the profession.

Today, with more than a dozen powerful spreadsheet programs on the market, accountants are confronted with such an embarrassment of riches that they no longer ask whether there is a good spreadsheet product available. Now they ask, Which program is best suited for my applications? Do I have to invest the time and money to learn more than one program? This article addresses these questions, reviewing many of the features of the leading spreadsheet programs. The article focuses only on Windows-based programs because, although the DOS versions of Lotus 1-2-3 probably are still the most popular among accountants, Windows programs quickly are moving into the lead.

The new Windows spreadsheet programs sport so many features it's hard for an accountant to select among the products. While they are all good, some have features that are more useful for some applications than for others. Today's packages contain more sophisticated help systems and advanced analytical tools that guide users so they work smarter and easier.

Spreadsheets have gotten so powerful in the area of basic data management that developers now are focusing on the next level of spreadsheet use: information analysis. For example, in the current crop of spreadsheets, multidimensional analysis can be done without writing formulas; instead, a user simply clicks the mouse on the area to be analyzed, drags it to the appropriate place on the screen and presto, analysis is performed. In addition, there are new types of spreadsheets with unique and powerful ways to build models and analyze data. One product that will be discussed later doesn't even use the conventional cell structure; its screen display looks like a diagram or flowehart.

Even with exciting and powerful features, there is a nagging question: Can the increased productivity of a new or upgraded spreadsheet offset the time and expense required to make the change? That's a question users must answer for themselves. To help resolve the question, this article examines the key features of the three top-selling conventional spreadsheets for Windows--Excel, version 5.0, by Microsoft Corp., Lotus 1-2-3, version 4.01, by Lotus Development Corp. and Quattro Pro, version 5.0, by Borland International, Inc. In addition, the unique features of two unusual spreadsheets are examined--DS Lab Pro, version 2.0, by Decision Support Laboratory and Lotus Improv, version 2.1, by Lotus.

DOING THE BASICS

Excel, Lotus and Quattro Pro perform the basic spreadsheet tasks admirably. They are faithful to the Windows design of icons, menus, toolbars and on-line help, which means they all function in a similar way: By learning one program, users effectively know the basics of the others. Each has customizable icons that serve as shortcuts for frequently performed tasks. Clicking the right-hand button of the mouse when the cursor is on a screen object often displays either a context-sensitive menu or a brief description of the object. Excel goes one better: Whenever the cursor is paused over a toolbar button, a small display box pops up, showing the purpose of the button.

Excel and Quattro Pro have added help features. Excel's Wizards and Quattro Pro's Experts are sophisticated help systems that guide the user through complex procedures. In addition, Excel has a help feature called TipWizard that monitors how work is being done, and if there is a more efficient way, it suggests the better alternative. TipWizard also provides a direct link to appropriate on-line help topics. The tips can be used immediately or accumulated until they are convenient to read. If the user desires, TipWizard can be turned off.

Much of the documentation for these spreadsheets is contained in on-line help systems rather than in printed manuals. Lotus's User's Guide is noticeably thin, but its on-line help is so good that a user has no reason to complain. Of course, on-line tutorials are supplied with each spreadsheet. Quattro Pro's interactive tutorials are unique because they give users the choice of using either their own data or data supplied by the tutor. Quattro Pro's Experts and Excel's Wizards are interactive tutorials--carrying on a dialog with users and providing instructions as they go through each step of a task. For example, Excel's ChartWizard and Quattro Pro's Graph Expert help create and edit charts and graphs, which in earlier spreadsheets were at best difficult and at worst impossible to generate.

Each spreadsheet includes comprehensive product support. Lotus is the best of the three: It provides 90 days' free support and a toll-free support line that is available 24 hours a day (except on holidays).

CRUNCHING THE NUMBERS

Those who work with hierarchical data--such as budgets--know the days of simple spreadsheets are gone forever. Today, three-dimensional spreadsheets--in which data can be examined from different perspectives--and related features take the hassle out of working with such data (see the sidebar, above, "What Does 3-D Mean?").

There are several important differences between 3-D spreadsheets. Quattro Pro has a designated graph worksheet in each file that contains some useful automatic features for making presentations. Block editing (that is, editing limited to a block of data) in a 3-D environment is more flexible with Excel and Quattro Pro than with Lotus. While Lotus has a group-editing mode, in which all worksheets in a file can be changed automatically (commonly called a global replacement), there is no provision to limit changes to selected worksheets. This presents a problem when the worksheets in a file have different layouts and the data are formatted differently. Think of a file with a number of cash budget worksheets; some of the data in the cells are in one typeface and some in another; in addition, the file contains a consolidation worksheet and a documentation worksheet. It's more cumbersome to lay out and format these worksheets with Lotus unless it doesn't matter that all worksheets have the same layout and formats. In addition, Excel and Quattro Pro have the flexibility to work with 3-D blocks in much the same way blocks are handled with less sophisticated spreadsheets.

Excel has a unique Outline feature that makes it easy to work with up to eight levels of vertical and horizontal hierarchical data within a worksheet. A typical use for the feature would be setting up rows containing monthly sales by salesperson (broken down by product, sales territory and sales region) and columns containing monthly amounts with subtotals by quarter and an annual total. Excel can create an outline and subtotals with just a few clicks of the mouse. For an example of that, see exhibit 1, page 70. Once an outline has been created (as shown in the uppermost screen), subordinate data can be either hidden or displayed by collapsing rows and columns as desired--that is, by clicking on appropriate buttons the user causes the details in a sales report (shown in the middle screen) to disappear, leaving only quarterly totals by sales territory or any level of aggregation in between (see the lower screen). Outlining makes it possible to move through large amounts of row and column data quickly to create charts from similar levels of data and to rearrange parts of a spreadsheet.

WHAT'S IT ALL MEAN?

Today's spreadsheets are analytical powerhouses. They have basic data analysis tools plus many advanced features such as scenario management, goal seeking, linear and nonlinear solvers and matrix manipulation. (For more information, see the sidebar titled "Advanced Tools for Spreadsheet Data Analysis" on page 71. "Excel and Quattro Pro have over 300 @functions each; Lotus has over 200. Almost any financial and statistical function that an accountant might use has been included. Many functions formerly were found only in statistical or engineering packages. Excel's Function Wizard helps users understand and use these functions.

Analysis tools. Excel and Quattro Pro have functions that eliminate much of the complexity of data analysis. Excel's is called Analysis ToolPak and Quattro Pro's is Analysis Expert. They help with everything from building amortization schedules to selecting random samples. The Analysis Expert, for example, provides a scrolling list of its tools accompanied by a text box that describes the purpose and operation of each. A click on the desired tool brings up a dialog window. After filling the appropriate boxes, a click on the Calculate button launches the analysis.

Excel and Quattro Pro have features, called a ay functions, that work with a group of cells as a unit. They make it possible to reduce the amount of time spent entering repetitive formulas so one formula can recalculate values in multiple cells. For example, consider an inventory list with costs in column A and units on hand in column B. One array function can calculate the grand total of costs multiplied by quantities.

Databases. Working with databases and lists is one of the most common spreadsheet activities for accountants. Each of the spreadsheets can work directly with data stored in common database formats. Special tools are supplied for querying a database and isolating desired records. Excel has an Auto Filter that creates a drop-down menu of all the unique entries in a field. The user sets criteria for selection and Excel hides all the rows that don't qualify. Lotus uses a dialog box to specify selection criteria for particular records in a database. The selected records can be placed in a special query table and treated as a unit for further evaluation and reporting. Quattro Pro has a Database Desktop feature that uses similar querying tools.

Crosstabs. Each of the spreadsheets can create cross-tabulation tables that are extremely useful for summarizing data in a database. Lotus's Database Crosstab Tool creates tables and calculates totals for a group of related records. Quattro Pro's Data Modeling Desktop creates tables and lets the user view data from many different perspectives by clicking and dragging row and column data to rearrange a report (see exhibit 2, page 72). Excel's Data PivotWizard is particularly good at handling multidimensional tables. The Data PivotWizard summarizes the contents of a database into a Pivot Table that then is used as the basis for multidimensional analysis--that is, the data view of a Pivot Table can be changed easily by dragging category names from one location to another. For example, a sales report by product line for each salesperson can be changed to a total sales report by product line simply by clicking and dragging.

Several features can be used in combination to do a lot of work very easily. For example, the following features can be used to generate a report from a sales database. (1) The database is sorted using the automatic sort feature. (2) The subtotal feature is used to calculate subtotals automatically. (3) The outline feature is used to hide database details so only the desired items appear in a report.

EYE-CATCHING GRAPHS

All three spreadsheets make it easy to create impressive graphs. Quattro Pro stands out with several features. Not only can it produce many different types of graphs but it also can suggest the type of graph that suits particular data best. The suggested styles can be customized. Excel has a drag-and-plot feature that lets the user drag with a mouse a range of numerical data to a graph. When the mouse is released, Excel redraws the chart to show the new data. Lotus has fewer chart types and is not as nimble as Excel or Quattro Pro in controlling graph features. However, it has a unique feature for making tables more attractive.

Formatting, copying and moving cells and ranges of data usually are time-consuming, but each of these spreadsheets can streamline the operation. Each can select noncontiguous ranges of cells and work with them as one unit. All three programs also can name these ranges, making it possible to use them in a formula or to select them for other purposes. When it comes to copying or moving cells, all contain a direct drag-and-drop feature. Touching the keyboard, clicking the mouse and dragging are all that is necessary to move or copy a selected range to a new location.

Excel and Lotus have in-cell editing, allowing users to enter and edit information right where it is on screen rather than create a separate edit area at the top of the screen. Lotus goes the next logical step and aligns text along any angle desired. Excel can attach explanatory notes (including spoken messages--assuming the user has the proper hardware) to individual cells. This allows users to provide detailed documentation and instructions for the other users of a worksheet.

SMART DATA LINKS

Spreadsheets often are built from data gathered from many sources. All of these Windows spreadsheets support Dynamic Data Exchange (DDE) and Object Linking and Embedding (OLE). Linking is a dynamic connection between data in Windows application files: Changing a number in one file instantly and automatically changes it in the linked file. Linked data can take different forms depending on the application. For example, worksheet data and an object such as a graph can be linked. Using links to share data between applications is useful when data change frequently.

All three programs can handle data in other formats. Quattro Pro has the ability to read and write data files in most formats--a handy feature when CPAs must work with client files prepared with different programs. Quattro Pro also supports a number of graphics formats. Excel uses a tool called Microsoft Query to retrieve and organize data from a variety of sources. Lotus uses its DataLens to access data in databases--a feature not as powerful as Quattro Pro's or Excel's.

Workgroup technology allows users to share spreadsheets over a variety of networks and messaging systems. Quattro Pro's Workgroup Desktop provides a way to share data over a local area network (LAN) without requiring a separate E-mail system. Lotus can send data via E-mail with the assistance of other Lotus, Microsoft or Windows programs. Excel has a separate Workgroup edition of the software.

Customizing. Spreadsheet macros, which let users automate a series of operations, have been available for many generations of spreadsheets. With modern spreadsheets, macros are much easier to use and yet can be so sophisticated that a worksheet can be customized to look like practically any application software. This includes the layout and design of specialized buttons, dialog boxes and menus to run applications. Quattro Pro has a unique User Interface Builder that helps create applications without resorting to the technical detail of macro code. Excel and Lotus use separate dialog-editor applications. If more control is required, Excel comes with Microsoft Visual Basic, a programming language designed for writing and customizing applications.

AND NOW FOR SOMETHING REALLY NEW ...

Two new types of spreadsheets for Windows offer specialized help for accountants: multidimensional modeling and graphical spreadsheets even more sophisticated than the three just reviewed. These spreadsheets are very powerful and surpass conventional spreadsheets in their specialized areas. However, they lack the broad array of features needed for most general-purpose accounting applications. Therefore, they should be treated only as specialized tools, not spreadsheet workhorses.

Multidimensional spreadsheets are designed for users who require different, dynamic views of data. This is usually the case when there are multiple categories (dimensions) to the data. An example of multidimensional data would be monthly sales (units and dollars) by product, sales region and salesperson. Conventional spreadsheets can handle multidimensional data, but the process can become very cumbersome beyond just a few dimensions. The problem really becomes noticeable when a multidimensional report has been created and someone wants to look at another dimension or get subtotals from another point of view.

Multidimensional spreadsheets for Windows include Commander Prism by Comshare, TM/1 Perspectives by Sinper Corp., and Improv by Lotus Development. Commander Prism and TM/1 are not for the average user; while they offer powerful tools, they require a high level of spreadsheet and financial sophistication to use effectively. Lotus's Improv is included in this article because, while it lacks the power of Commander Prism and TM/1, it is representative of the new technology that is beginning to evolve in the spreadsheet market.

There are two immediate and noticeable differences between Improv and conventional spreadsheets. First, data and formulas are separated. Second, when referring to data, names are used rather than cell coordinates. For example, instead of referring to fourth-quarter sales in the Southern region by cell coordinate "C4," Improv uses a label such as "Southern:QTR3." Also, rather than refer to ranges such a "C1 ... C12," Improv uses an expression such as "JAN ... DEC." This makes Improv spreadsheets easy to understand and audit.

Formulas are handled in a "formula pane" at the bottom of the worksheet. Data labels also are used in formula expressions. For example, a formula in a conventional spreadsheet might look like "+C7-C10." With Improv, the formula might look like "GProfit=Sales-CSales."

The separation of formulas from data provides a number of advantages for multidimensional analysis, including the ability to create crosstab tables and reports. Consider a sales database that has 5 dimensions: month, region, product, transaction and amount. Improv can handle up to 12 dimensions. Data can be viewed and reports printed along any of these dimensions by dragging with a mouse the desired data label to the appropriate location on the worksheet. Improv then duplicates formulas automatically in the new configuration and gives the desired view. Data can be collapsed along any dimension so reports can be created at various levels of aggregation. Up to 16 different views, or ways to look at the data, can be designed and saved for future use.

Creating an Improv worksheet begins with the entry of row and column data labels for each dimension of data. After the data's basic structure has been developed, formulas are added to describe the logic of any relationship. Data can be entered manually or imported from a number of types of external databases. Finally, data are analyzed using the dynamic features of Improv. Adding another dimension or level of detail can be handled easily because Improv is formula-based.

Improv's approach takes some time to learn and requires a different logical orientation than conventional spreadsheets. Although Improv probably won't replace many conventional spreadsheets in the near future, its presence in the spreadsheet market indicates the strength of the trend toward specialized, easy-to-use features in Windows spreadsheets.

VISUAL SPREADSHEETS

Windows and other programs with graphical user interfaces have spawned a number of visual programs useful for accountants. For example, factory simulation software calculates cost data while it shows a graphical representation of products and processes.

DS Lab is a spreadsheet that uses a graphical format for data modeling. (See exhibit 3, above.) The screen display shows a financial model graphically without the conventional row, column or cell format. The screen looks like a diagram or flowchart. Symbols such as squares, circles and triangles represent the elements of a model. Arrows connect the symbols to show the logical relationships between them. Data and formulas are behind the visual display and can be viewed with a click of the mouse. A visual approach, which makes DS Lab worksheets easy to build and understand, also avoids the risk inherent in conventional spreadsheets of having the logic of the model buried in a cell structure.

A DS Lab spreadsheet might represent total costs with a circle with two arrows flowing into it from circles representing variable costs and fixed costs (see the exhibit). The expression (script) that defines total cost would be "Variable Cost + Fixed Cost." As costs are calculated, they are displayed on the screen with the related graphical symbols. Detailed results and results of series or table calculations can be displayed on the screen with a click of the mouse. DS Lab has a good reporting feature so that all results, at various levels of aggregation, can be included in reports.

Scripts are at the heart of DS Lab. They constitute the formulas and instructions used to calculate the values of variables. Scripts are as simple as the addition of two variables or as complex as a computer program that includes local variables and a control structure. The program has a large variety of predefined financial, logical and mathematical functions that can be included in scripts.

As software sophistication grows--made possible by more powerful hardware--spreadsheets are sure to get even more useful, helping CPAs dig deeper into the relationships between numbers and performing analyses that make complex data easier to understand.

EXECUTIVE SUMMARY

* WINDOWS SPREADSHEET software, such as Excel, Lotus 1-2-3 and Quattro Pro, has revolutionize the way accountants work, and the programs sport so many features it's hard for an accountant to select among the products.

* PROGRAMS TODAY ARE SO powerful in the area of basic data management that developers now are focusing on the next level of spreadsheet use: information analysis.

* TODAY'S SPREADSHEETS ARE analytical powerhouses. They have basic data analysis tools, plus many advanced features such as scenario management, goal seeking, linear and nonlinear solvers and matrix manipulation.

* DESPITE THE EXCITING and powerful features, there is a nagging question: Can the increased productivity of a new or up-graded spreadsheet offset the time and expense required to make the change? That's a question users must answer for themselves.

* TWO NEW TYPES OF spreadsheets for Windows offer specialized help for accountants: multidimensional modeling and visual spreadsheets. They surpass conventional spreadsheets in their specialized areas but lack the broad array of features needed for most general-purpose accounting applications.

* AS SOFTWARE POWER GROWS--made possible by more powerful computers--spreadsheets are sure to get even more useful, helping CPAs dig deeper into the relationships between numbers and performing analyses that make computer data easier to understand.

WHAT DOES 3-D MEAN?

In spreadsheet lingo, a program with three dimensions means the user can view data in multiple ways simultaneously.

To accomplish this feat, the spreadsheet accommodates up to 256 separate worksheet pages in the same file. On the screen, 3-D worksheets look like a notebook with tabs that contain the names of each worksheet in the file. Clicking on the tab of the desired worksheet makes that worksheet active (visible). Since Windows allows the user to make multiple pages active with split panes (called tiling) and cascading (the pages are fanned out like a hand of cards), several worksheets can be viewed at the same time.

Also, with 3-D it's possible to break up large worksheets into smaller but related pieces, with each piece situated in the same file. For example, a comprehensive budget's components (sales budget, production budget, cash budget, etc.) can be placed on separate worksheets, but all can be included in the same file.

The 3-D feature also makes it possible to group related worksheets in the same file. For example, cash budgets for each division of a company can be included in the same file. In addition, the file might contain a summary worksheet to show the combined results of all cash budgets. Moving from the budget of one division to the budget of another is as simple as clicking on the worksheet tab for the desired division.

ADVANCED TOOLS FOR SPREADSHEET DATA ANALYSIS

Scenario management makes it easy to enter data, view results, and create reports for different business situations scenarios). For example, different sets of cost data can be entered that represent best-case, worst-case and most-likely conditions. The program then can prepare separate budgets under each scenario.

Goal seeking is a way to specify a desired result and to identify the variable that must change to achieve it. Formulas are calculated backwards to solve for the variable that produces the desired result. For example, to arrive at a desired monthly payment, goal seeking can be used to calculate the original loan amount.

Linear and nonlinear solvers help find the curve that best fits a set of data. For example, a moving average can be calculated automatically to create a set of forecast values. Then exponential smoothing can be used to smooth each forecast value.

Matrix manipulation is useful when solving for variables subject to certain constraints. For example, matrix manipulation can be used to project the minimum labor cost of a job when different categories of labor can be used.

FOR MORE INFORMATION

Here's where to call or write for product or ordering information for the software programs mentioned in this article.

Baarns Utilities, version 5.0 Baarns Consulting Group 12807 Borden Avenue Sylmar, California 91 342 [phone] Price: $69.95

DS Lab Pro, version 2.0 Decision Support Laboratory DS Group, Inc. 474 North Street Greenwich, Connecticut 06830 [phone] Price: $995

Excel, version 5.0 Microsoft Corp. One Microsoft Way Redmond, Washington 98052 Phone: [phone] Price: $495

Lotus Improv for Windows, version 2.1 Lotus Development Corp. 55 Cambridge Parkway Cambridge, Massachusetts 02142 [phone] Price: $129

Lotus 1-2-3 for Windows, version 4.01 Lotus Development Corp. 55 Cambridge Parkway Cambridge, Massachusetts 02142 [phone] Price: $495

Quattro Pro for Windows, version 5.0 Borland International, Inc. 100 Borland Way Scotts Valley, California 95066 [phone] Price: $99.95

Note: The products' street prices usually are much lower than those listed here.

ENHANCING THE SPREADSHEET SOFTWARE

Spreadsheet add-on packages can enhance the power and usability of already sophisticated spreadsheet programs. They provide specialized analytic tools and unique functions that expand and simplify spreadsheet use. They fill a niche for users who want additional features but who don't have the time or expertise to develop them from scratch.

One of the most powerful is Baarns Utilities 5.0 from Baarns Consulting Group. It adds 23 utilities to Microsoft Excel, inserting a second toolbar below Excel's regular toolbar.

One of the functions is AutoSave, which automatically saves (backs up) any open file based on the number of cell entries made, elapsed time or a combination of the two. Up to nine incremental backups can be saved.

Another Baarns tool is File Express, which organizes files into groups and allows for the use of long descriptive names for both groups and files. Baarns also provides features for enhanced viewing (displaying documents on a full screen), easy deletion of outdated files, convenient formatting and annotating and faster printing.

DAVID H. LUTHY, CPA, is an associate dean and the Stratford Professor of Accounting at Utah State University, Logan. He is a member of the American Institute of CPAs and the American Accounting Association and a former member of the Utah, Association of CPAs board of directors.