How to buy an electronic spreadsheet. David H. Ahl.

Although computers have been used for business and financial calculations almost from the day the first Univac was put into service in 1951, the concept of an electronic spreadsheet is fairly new. It did not exist until 1978 when Dan Bricklin, a student at the Harvard Business School sought a better way to perform the endless recalculations of balance sheets, income statements, and forecasts required whenever a single assumption changed. He likened his first microcomputer-based spreadsheet program to "an electronic blackboard and electronic chalk."

The first spreadsheet envisioned by Bricklin had five columns and 20 rows. To make it a marketable product, he enlistened the aid of long-time friend Bob Frankston who increased the number of cells, added some user-friendly features, and packed the resulting program into 20K for the Apple. Dan Fylstra made a deal with Bricklin and Frankston to sell the program, now called VisiCalc, through his new company. Personal Software (later renamed VisiCorp as a result of the success of the program).

Bricklin and Frankston formed a company, Software Arts, to develop additional versions of the program for other computers while Fylstra moved his company to the heart of Silicon Valley and acted primarily as marketer for VisiCalc and a growing number of ancilliary programs (VisiTrend, VisiPlot, etc).

Over the years, the two companies have grown apart, and today they are locked in ligitation. VisiCorp claims Software Arts was late in delivering Advanced VisiCalc because of their emphasis on developing TK! Solver while Software Arts faults VisiCorp for emphasizing the newer VisiOn product line at the expense of VisiCalc.

In any event, today Advanced VisiCalc is available from both companies, although VisiCorp has far better distribution. VisiCorp also markets their own VisiOn line of integrated software packages while Software Arts markets TK! Solver, an iterative problem-solving tool for performing "what if?" calculations.

The astounding success of VisiCalc spurred many other software authors and companies to market spreadsheets of their own, some with more features, some for machines not supported by VisiCorp, and some with fewer capabilities that could be sold cheaply in the home market. Today, there are approximately 20 full-featured spreadsheets at prices of $200 and up aimed primarily at business users and 12 or so in the $100 price range aimed primarily at the occasional user or home market. In addition, there are several integrated packages such as Lotus 1-2-3 which combine a spreadsheet with two or more other functions, usually database and graphics programs. features and functions

With so many spreadsheets on the market and so many claims being made by the various manufacturers, it is easy to become confused. Furthermore, most computer stores handle only two or three packages, and most salespeople are familiar with only one. While charts of features are certainly helpful, you may not need all of the features. Moreover, each feature usually comes at a cost--in dollars, memory, or speed.

In his book, How To Buy Software, Alfred Glossbrenner has devised a framework of five categories of spreadsheet features. we have added several to his list and shall use this expanded framework to discuss the various spreadsheet features so that you can evaluate packages against your particular needs--current and future. capacity

As mentioned earlier, the more features a spreadsheet has, the more memory it requires. Very few full-featured spreadsheets run in less than 48K, and the majority require 64K or more. Some of the home packages are smaller, as are the ones for notebook computers. However, because a spreadsheet can run in 48K (or 64K) does not mean that it will meet your needs. For example, if your computer has 64K and the spreadshett occupies 50K, only 14K will be left for your calculations. This might be enough for a 12-month forecast of 50 items assuming the calculations are not too complicated, but you would be in trouble if you needed 1000 or more cells.

Unfortunately, there is no good way to determine how much memory you need based on the number of cells in a spreadsheet. If you are entering only numbers and labels, you can fit far more data than you can if you are using complicated formulas throughout.

Since 8-bit computers can address only 64K, obviously that is your upper limit in a machine like an Apple; 16-bit machines do not have this restriction. Recognizing the memory problem, some spreadsheets have the ability to use virtual memory, a concept originally devised in the mid-60's which allows a mass storage device to be used as an extension of internal memory.

Thus, with virtual memory, a portion of a spreadsheet can be stored on either a floppy or a hard disk. When you update this part of the spreadsheet, it is automatically brought into RAM and the part you were working on is stored away. With a hard disk, this swapping is reasonably fast, but with a floppy disk it can be agonizingly slow.

Spreadsheet capacity also refers to the maximum number of rows and columns offered by the program. Most spreadsheets offer between 30 and 63 columns. The average number of rows tends to fall between 192 and 254. However, recalling our discussion of memory above, you cannot multiply the maximum rows by columns to get the maximum number of cells; it just doesn't work that way. Layout and Labeling

Under layout and labeling we group the factors that determine how the final spreadsheet looks. Before selecting a spreadsheet, you should write out by hand or pull from your files some examples of the types of tables or reports you would like to be able to produce.

Do your tables have titles across the top? Some spreadsheets do not permit this because they automatically put spaces between columns. Original VisiCalc is an exception; labels can continue from one column to the next without a break.

Do you have long labels on the left with shorter columns of numbers in the body of the table? If so, you want a spreadsheet that permits column widths to be set independently. Nearly all spreadsheets have variable width columns, but some require that all columns be set to the same width.

Are some of your labels actually numbers? Some spreadsheets allow you to use only letters for labels, while others permit the use of both numbers and letters, but not in the same heading. Do you want a spreadsheet that can justify labels to the left or right, center them, or underline them?

What is the format of your numbers? Some spreadsheets automatically insert leading dollar signs, plus or minus signs, credit (CR) and debit (DR) notations, and commas. Some permit scientific notation, integers, or decimal rounding to a specified number of places. Some suppress trailing zeros and display negative numbers in parentheses.

All spreadsheets permit the above cell attributes to be set for an individual cell, and most permit attributes to be specified for a row or column of cells, or even for the entire sheet.

Many spreadsheets permit a row, a column, or both to be inserted; some permit a row or column to be moved to another location; whereas others permit no insertion or movement at all. The effect on labels of moving rows and columns can be strange. Some packages tidy things up; others leave gaps in text labels. formulas and functions

The power of a spreadsheet lies in its ability to manipulate data automatically according to your directions. However, spreadsheets vary widely in the way they require you to enter these directions.

In the familiar Cartesian coordinate system, the x and y coordinates are both expressed by numbers (e.g., 1,1, or 23,7). So it is with some spreadsheets. Others, however, express cell locations as a letter and number (Al, H45, BC20), while at least one, Multiplan, precedes the row and column number with R and C (r1C1, R23C45).

Often when working with formulas you need to use a value from another cell. Generally, you must designate a particular cell using the cell coordinate as described above, but some of the newer spreadsheets permit you to name a cell (Sales, Rate, etc.) and use that name in formulas.

The number of functions available in most spreadsheets today is staggering. All spreadsheets offer the four arithmetic functions, exponenting, rounding off, and summing a series of cells. Most also calculate absolute value, averages, logarithms, and square roots.

From there on, each spreadsheet designer seems to have taken a slightly different approach. Although most spreadsheets can serve many purposes, their available functions may not suit every user. For example, someone doing financial calculations will find 15 functions in Microplan available in no other spreadsheet (discounted cash flow, interest schedule on loan, add constant, percent of total value, and so on).

Someone doing mathematical calculations that require arcsines, arctangents, pi, or modulo will find these functions in Advanced VisiCalc and SuperCalc, but not in many other packages. For statistical calculations, Microplan is attractive with its functions for variance, mean, sigma, moving average, and delta; Multiplan has several of these functions also.

Some spreadsheets have conditional and logical operators. Almost all have IF-THEN which means that you can say, for example, "If sales are are over 5000, then use a commission rate of 15%, else use 10%." Conditional operators can also be used to print different labels or text messages. Logical operators can generally be strung together with AND, OR, and NOT, but some spreadsheets offer additional logical operations as well.

While some formulas contain only a few symbols, others may stretch across the screen. In typing long formulas, it is easy to make mistakes. Some spreadsheets permit you to edit a formula a character at a time while others require that the full formula be retyped. With some packages, you can see the formula for each cell; in others the formulas, once entered, are hidden and can be recovered only by saving the worksheet and looking at the template with a utility or word processing program.

Most spreadsheets have a replicate or copy command that permits cells to be copied from one location to another. Formulas usually can be replicated with constant or relative values. Some spreadsheets even permit an entire block to be replicated or moved from one location to another.

Spreadsheets offer several levels of error protection. First is the ability to use commands such as ON ERROR in which you try to anticipate errors. Second is the option to protect a cell from being changed by accident. This is usually done by putting a marker in a cell in which you do not want the formula or label changed. Numbers can be entered into a marked cell, but labels and formulas cannot be changed until the cell is unprotected.

When all the formulas have been entered, you have constructed a template into which data may be entered. Templates for popular applications (monthly P & L statements, forecasts, portfolio analysis, etc.) are often available as commercial packages or printed in magazines and books. Entering and Viewing Data

In some spreadsheets, every number you type appears immediately in the cell, while other programs have the ability to store a series of keystrokes which are entered upon pressing RETURN. Even more friendly are those spreadsheets that let you press an arrow key both to enter data and to move to the next cell.

Movement around the spreadsheet can be accomplished with the arrow keys (one cell at a time) or with a single command that takes you to a specified cell.

Almost all spreadsheets allow you to look simultaneously at two or more portions of the spreadsheet by means of a window command. Windows can usually be brought in both horizontally or vertically. For most purposes, two windows are enough. However, occasionally you may want to look at a total as well as two other locations. Although ProCalc permits six windows and Multiplan permits eight, you must ask yourself if you need that many and if they will conveniently fit on your display. In most cases windows scroll independently of one another, however, some packages allow them to scroll together at your option.

The reason for having a spreadsheet is to do repetitive calculations, and each time you enter a new number in a cell, the entire spreadsheet is recalculated. As the sheet gets bigger and more complex, this can be quite time-consuming, particularly since you cannot enter the next number until the recalculation is complete. Thus, a welcome feature in almost all spreadsheets is the ability to turn the automatic calculation function on and off. To enter data speedily, you would turn it off. Then, when you are finished entering data, you can instruct the program to perform all the calculations at once.

Some of the newer spreadsheets employ features from the word processing world such as global search and locate, search and replace, and help menus.

Earlier we mentioned the ability to move rows and columns. An even more advanced feature, found in just a few packages, is the ability to sort and rear-range rows, columns, or both.

All spreasheets use the "normal" 40-, 64-, or 80-column screen display on your computer. However, if your computer has an alternate text density, say 132 columns, most spreadsheets are able to use this as well. Some packages utilize various other computer display capabilities and employ reverse video, blinking cells, and underlines. A few even take advantage of a color monitor (assuming one is attached) to show negative numbers, labels, and protected cells in different colors. Multiplan, for example, provides excellent support of color. Printing and Formatting

As you would expect, all spreadsheets let you print out your entire worksheet, but there are substantial differences in the ways this is done. In general, you position the cursor over the upper left cell to be printed, call the print routine, specify print parameters, and enter the lower right cell to be printed.

If your spreadsheet is wider than your printer, you must count the characters in each column and then divide the worksheet into the number of rectangles required to print the full width. If it is longer than 56 rows (about what will fit on an 11" piece of paper) you must also divide the vertical printing into segments. A few of the newer spreadsheets (and some add-on software packages) perform this calculation and print your worksheet automatically.

Some packages allow you to send directions to your printer to turn on (and off) a compressed printing mode (assuming it is available on your printer). Two of them, however, Microplan and Magic Worksheet, limit the horizontal print width to 132 characters even though you might have a compressed mode capable of handling more.

Most newer spreadsheets allow the use of embedded printer commands to produce italics, bold, double strike, and underlining. Some of these packages remember these print options and use them automatically.

Again, some of the newer spreadsheets employ print specifications similar to those found in word processing packages for such functions as setting margins, single sheet feed, automatic top of form, and page numbering. A few even print row and column titles on each page automatically. Some allow you to dress up the sheet by printing a border automatically.

Microplan offers several unique printing features such as report titles, suppressing zero cells or rows, expressing zero values as dashes, and expressing -Oas-n,n,or(n).

It is sometimes desirable to get a quick and dirty print of what is on the screen. With computers such as the IBM PC this can be done pressing PrtSc, but some spreadsheets have this feature built in, too.

The majority of spreadsheets can produce rudimentary bar grahps with asterisks which can, of course, be rpinted.

Nearly every package allows an image of the worksheet to be saved in text mode on a disk. By so doing, you can access the worksheet with a word processinf program; integrate part or all of it into a report; edit it; titles, dollar signs, percent signs, italics, boldface, superscripts and subscripts; and print it out. This is not a two-way process; you cannot edit the file with the word processing program and read it back into the spreadsheet package.

If you plan to use a word processing program to dress up your worksheets for printing, both programs must be able to read the file. Moreover, you word processor must be able to manipulate lines as long as those in your spreadsheet. This is a crucial limitatioin since most word processing packages are limited to much shorter line lengths than spreadsheet packages can produce. One spreadsheet, ProCalc, incorporates a text processor (actually a UCSD pascal editor) with the package. Saving and Loading Files Most often you will save a worksheet using the normal save command. This saves both the cell formulas and values. Files thus saved can be loaded back in and worked on again. We also mentioned saving a worksheet as a text file, but this is a one-way process used primarily for printing reports. A third method of saving produces files (with the data intact) that can be used by other packages--mainly graphics, database, and communications programs.

VisiCorp was the first to devise such a format, which they called the Data Interchange Format (DIF). By saving a worksheet in the DIF format, the data then can be used by VisiTrend, VisiPlot, TK! Solver, and even some competitive spreadsheets, such as Magicalc, that use the DIF format.

The DIF format specifies the order in which data are saved in the file. Basically, it first specifies the size of the spreadsheet, the number of columns(vectors) and rows (tuples), and then the values in each cell. Unlike text files, DIF files are two-way; in other words, you can produce data with another program, save the file in the DIF format, and load it into VisiCalc. This is especially useful if you want to download data, way statistics from CompuServe or stock prices from Dow Jones, and analyze these data with a spreadsheet.

SuperCalc, Multiplan, ProcCalc, and Microplan all have their own versios of the DIf format. If you are interested in this capability for graphics or communications, you should examine the reated software packages to make sure that they meet your needs.

Some spreadsheet packages such as Multiplan, PerfectCalc, and ProCalc, have the ability to link several worksheets together so that a change in one is automatically reflected on the others. A related feature is the ability to combine several worksheets into one. This is accomplished in several different ways, some considerably more convenient than others.

While consolidation of two or more worksheets may sound like a wonderful feature, you should ask yourself how often you would actually use it. The simplest way of consolidating worksheets is by using DIF (or similar) files. This is not true consolidation because only the data are seved in the DIF format, not the formulas. Also, if cell locations are duplicated, the new data are saved over the old. Hence, this is simply a way of combining tables of numbers by overwriting.

True consolidation is a complex process, and Microplan, with its consolidation module, is one of the few spreadsheets that offers it. There are several add-on software packages that perform true consolidation. MergeCalc from Cypher and Viz.A.Con from Abacus Associates consolidate VisiCalc worksheetes, and Con-Calc from Sunwest Software does the same thing for SuperClac and other CP/M spreadsheet packages. Documentation and Help Naturally, all spreadsheet packages come with a manual that decribes the features of the software and how to use them. The majority of the manuals are divided into two major sections, a tutorial and a reference guide. Some also include a quick reference card or chart, and a few, meant for a specific computer, include a keyboard overlay or template. as we have often commented when reviewing these packages, the quality of the manuals varies greatly. Some seem to be written by programmers for programmers while others, obviously written for rank beginners, force more experienced users to wade through many paragraphs to find the useful nuggets of information. We thought the Advanced VisiCalc manual struck just the right balance, but we may be biased, having used regular VisiCalc for years.

Moreover, no matter how good the documentation is, it generally will not show how the feature can be applied to the prblems that are of interest to you. Hence, you should look into other sources of information such as books (there are at least a dozen on using spreadsheets for different applications) slef-instruction programs, template programs, magazines, and user groups.

As mentioned earlier, some of the packages have an on-screen help fature or even a separate help menu. A few companies supply an interactive tutorial on a disk that comes with the package.

Some companies have a telephone hot line for customers with questions. This is usually not a free service, and prices for it range up to $50 per hour or more. Other Considerations Some spreadsheet programs have calendar funtions. For example, Advanced VisiCalc can convert the calendar date to an absolute date and extract the year, month, or day for use on the worksheet. It will also convert the time to fractions of a day. SuperCalc has similar capabilities, while ProcClac and Target permit the current date to be entered into the worksheet.

In the user-friendliness department, we are always astonished at the differences between packages that do essentially the same thing. It seems sensible to us that the most used commands be abbreviated to their first letter, S for save, L for load, M for move, I for insert, and so on. But many programs incorporate this logical approach? Far fewer than we would hope. And although Multiplan has many advanced features, it has the most cumbersome cell identification scheme (R34C21) of any spreadsheet. Were the Microsoft designers out to lunch that day or what?

Menus are the rage among software designers these days. Unfortunately, they are generally slower than cryptic but simple commands. If the more experienced user can by pass the menu as is possible on certain software packages, then we have no objection to their use. However, Magicalc does not offer this alternative and is thus slower than other systems.

A word about speed. We devised a nifty benchmark for spreadsheet packages. However, the packages we tested were not all for the same computer, and we found that the computer itself made far more difference than the package. As you'll note from our benchmark page, the TIProfessional is 6.8 times faster than the Apple, so we felt it was unfair to compare Magicalc on the Apple with Microplan on the TI. Neverthless, we had several packages for the TI and several for the IBM PC, and we noticed some fairly dramatic speed differences (3 to 1) among packages running on the same computer. However, other features should probably wiegh far more heavily than speed in the selection of a spreadsheet.

In the discussion above, we mentioned add-on packages in several places. These packages tend to fall in two categories. In the first group are the ones that have been written to augment the capabilities of the older spreadsheets, primarily VisCalc. Frequently, these features are included in the newer spreadsheets, so these add-on generally are not of interest to someone buying a package today. We say "generally" because with VisiCalc being so heavily discounted today, you might find that VisiCalc combinated with an add-on package will meet your needs and cost considerably less than a newer package that has additional features that you don't need.

The second category of add-ons includes templates (or programs) for commonly used applicatios. As we mentioned above, financial and statistical date are aloso available from the various communicatios utilities. Another interesting source for data on publicly held companies is Data Resources who furnish on-line data to subscrubers in the DIF format.

In addition to software add-ons, there are hardware add-ons designed to be used in conjunction with spreadsheet packages. Apple users can choose from several memory boards that use bank switching to extend memory capacity above 64K. These usually come with software to make the board invisible to the spreadsheet software. Such boards are made by Titan Technologies, Prometheus, Applied Engineering, Omega Microware, and Orbital systems. Several companies also make 80-column cards for the Apple--almost a must for any serious spreadsheet work. Integrated Packages Within the last year, several integrated sofware packages have come on the market. These combine a spreadsheet with two or more other packages--usually graphics and database management programs. Communications and word processing programs may be included as well. We will be doing a special section on itergated packages in an upcoming issue, so our discussion here is rather brief.

In an itegrated package, if you change the value of a variable in any module, it is automatically changed in all the rest. This saves disk shuffling, time, and effort. Users have been very enthusiastic about this approach; as a result, most majors software producers have intergrated packages in the works.

The two leading integrated packages, Lotus 1-2-3 and Context MBA, include full-featured spreadsheet graphics, and database management programs. Both also iclude redumentary text editing programs as well. A similar package for the Apple is The Incredible Jack by Business Solutions, Inc.

Another approach to integration is to have an umbrella program which communicates automatically with the various applications programs. Examples of this are VisiOn and DesQ. The main difference between the two is that VisiOn requires special programs in VisiCorp's "-On" family such as VisiOn Word, VisiOn calc, and VisiOn graph, whereas DesQ works with any programs on the IBM XT (DesQ requires a 5Mb hard disk).

However, if you already have a computer with a library of software packages and disks full of your own historical data, it may not make sense to jump onto the integrated software bandwagon. In that case, you may be able to achive nearly the same result with DesQ (if you have an IBM XT) or, to a lesser extent, with one or more add-on programs such as StretchCalc from MultiSoft Corp.

While we are enthusiastic about the capabilities of Lotus 1-2-3, Context MBA, DesQ, VisiOn, and The Incredible Jack, we also see them as just the tip of the integrated package iceberg. Original VisiCalc was a fabulous product when it was introduced, and it sold countless thousands of computers, yet the second generation of spreadsheet packages offered significantly more features. We think that the same thing will happen in the integrated packages arena. For now, Lotus 1-2-3 is the big success story, but the second generation of packages will probably have more features and be more user-friendly. As we mentioned earlier, we will be looking at these packages in depth a few months from now.