Figure It

FIGURE IT
AN OVERVIEW OF ST SPREADSHEETS

by JIM PIERSON-PERRY

Whether you manage a Fortune 500 company or a corner grocery store, you need to keep a tight rein on your money. If you do the latter, then an Atari ST and a spreadsheet program may be all you need-but which one? Here, let Jim Pierson-Perry show you the best programs to keep track of your money-be it coming or going.

Spreadsheets have been one of the major reasons for the acceptance of microcomputers in the business market. Word processing was useful, although a secretary could fulfill that role. Data analysis and financial decision making, however, required a lot of time and computational resources that were not always available. Spreadsheets suddenly put this power into an affordable desktop computer tool. Applications as varied as analyzing sales forecasts to comparing mortgage options could be done speedily by all levels of users - from business managers to home owners.

Spreadsheet Basics
In simple terms, a spreadsheet is a piece of electronic paper, a pencil and a calculator. It is laid out as a grid of rows and columns. Each cell of the grid can contain a number, text or formula for computing a value based on other cells. Changing a cell's value causes all cells dependent on it to be changed. This allows rapid evaluation of "what if" questions - leading to faster and more comprehensive decision making.

Over the past several years, a cottage industry has grown up around spreadsheets for educational support and predefined models (templates). Most of these are for Lotus 1-2-3, the standard in the business world. Programs compatible with its file format (.WKS) can draw upon these third-party resources, as well as Lotus 1-2-3 forums on various bulletin boards.

The strength of a spreadsheet is in the different kinds of models that you can develop, which depend on the number and type of built-in functions and ways to address cell data. Other features that enhance a spreadsheet's utility are graphics, database access, user definable functions, printed reports and the ability to import and export data files between other application programs.

Another feature to consider is how well the program can detect errors. All report mathematical errors, such as division by zero, but logical errors in setting up a model are another matter. Two of the most common such problems are circular and forward referencing. A circular reference is when a cell is defined by a formula containing itself (eg A10=10*A10) and the spreadsheet usually cannot resolve the problem. A forward reference is when a cell value depends on other cells that lie ahead of it in the calculation sequence. The "natural" method of recalculation was devised to take care of this problem. (With this method, if you're moving down in a column and come to a reference that's out of sequence, the spreadsheet jumps to that cell, evaluates it, then jumps back to where you left off.)

Spreadsheets for the ST range from low-cost basic feature programs to full-blown integrated systems for power users. Below are brief reviews of five spreadsheets, highlighting their major strengths and weaknesses, and a chart for side-by-side comparisons of their features.

VIP Professional
VIP Professional is a full-fledged clone of Lotus 1-2-3 with a GEM shell. It reproduces all of Lotus's features including the ability to read and write .WKS files. This gives you instant access to hundreds of existing models as well as connectivity with office PCs using Lotus 1-2-3, since the ST can directly read or write IBM 31/2-inch data files.

However, there are two drawbacks -the program takes up a lot of memory (ideally, you need a megabyte), and its GEM shell is incomplete. You select commands by going through several levels of nested menus. You can select most with either the mouse or keyboard, but mixing is not allowed during a selection sequence. There are also several commands you can access through only the keyboard and a similar set only for the mouse.

The grid design makes good use of the mouse for selecting cells and ranges. Scrolling is slow but acceptable, with a variety of ways to jump more quickly about the grid. An express icon is provided for one-step moves to the grid extremes within a range or to a neighboring range. VIP uses the natural method as its default for recalculation and it flags circular references. The Undo button is supported along with a good on-line Help dialogue, and you can use relative, absolute and mixed cell addressing freely, making model building a snap.

VIP provides an extensive set of cell formatting commands which can be applied globally, to a cell range or to a single cell. You can set individual column widths by dragging them with the mouse, and can split the grid window into two independent GEM windows.

VIP has a full repertoire of functions from math and finance to date manipulation. Anything you need that doesn't come with the package can be added easily through the powerful macro editor which lets you define your own function subroutines. It's possible to call additional macros within a macro or to create autorun macros that execute when the program is loaded. One warning: the statistical functions for computing standard deviation and variance give biased values which could be misleading.

Finally, for those who want true IBM compatibility, a text-only version (no GEM) of VIP is available (Editor's note: For an in-depth review of VIP Professional, see the Fall, 1987 issue of START.)

Masterplan
Masterplan is a stripped-down version of VIP Professional, although it may actually be the more useful program for many users. The GEM interface is significantly better and the maximum spreadsheet size has been doubled for 520 ST owners.

The modules taken out were most of the database routines, X-Y graphs and macros. The basic design, editing and calculational features were preserved. This also includes, unfortunately, the problem with the standard deviation and variance functions as noted above. Loss of the X-Y graph option hurts the program for scientific data analysis but does not affect its usefulness for business purposes.

Files created with Masterplan are fully upwardly compatible with both Lotus 1-2~3 and VIP Professional. Conversely, Masterplan will accept any .WKS file, although it will ignore macros. Graphs are now printed directly from the menu without requiring a separate output program, with a new option to save them in DEGAS format for subsequent customizing. That combination makes it easy to prepare a series of graphs into a slide show format (using a DEGAS viewer) for presentations or to incorporate them into a prospectus using Publishing Partner. (Editor's note: A review of Masterplan appeared in the Novembei 1987 issue of Antic.)

SwiftCalc ST
SwiftCalc ST is designed to work with Timeworks' other applications programs-Word Writer ST and Data Manager ST. It is not compatible with .WKS files. In SwiftCalc, you select commands primarily through the keyboard. A limited GEM shell is provided which is best suited only for cell selection, since most commands require follow-up keyboard input.

Entering cell values is straightforward-you select the cell with the mouse and type the entry. Editing, however, is awkward as you must press the F7 key before being allowed to change the cell contents. Range operations over multiple cells are also cumbersome. Rather than intuitively selecting a range by dragging the mouse across the cells, you must explicitly designate the top and bottom of the range via menu commands.

SwiftCalc ST has a large number of math and financial functions but lacks standard deviation (important for science applications), internal rate of return (an advanced financial function) and date functions. Macros are not supported. Only row- and column-oriented recalculation sequencing are supported so any forward references will require multiple recalculations to yield the correct results. SwiftCalc also doesn't detect circular reference errors.

SwiftCalc can use either relative or absolute, but not mixed, cell referencing. An advanced feature is its ability to link cells from the current spreadsheet to cells from other spreadsheets stored on disk. These links can be either temporary or permanent and will automatically update should the parent spreadsheets change.

Database operations are covered with a variety of search, sort and look-up options. You can do data series generation, but only with a positive whole number step size, and you can also load tables from Data Manager ST for analysis then send them back to SwiftCalc or to Word Writer ST. SwiftCalc lets you print out your data in various forms with the SuperGraphics program, an auxiliary graphing program included with the program. (Editor's note: For a complete review of SwiftCalc, see the Summer 1987 issue of START. For a closer look at the SuperGraphics program, see "Show it: Business Graphics" elsewhere in this issue)

A-CaIc Prime
Unlike most spreadsheets, A-Calc Prime makes extensive use of icons for its major operations. There are five of these representing disk access, printer output, the spreadsheet grid, clipboard storage and trash. Their uses, however, are not always obvious and require a familiarization period with frequent recourse to the manual. Once mastered, this approach makes for speedier operation than the typical nested command menu structure.

Another difference about A-Calc Prime is its cell edit mode. Most other programs let you change a single cell, then drop back into command mode. In this case, however, once invoked edit mode operates until canceled. Selecting a cell with the mouse while holding down the Alternate key lets you move from cell to cell and do all necessary editing. You can reference cells as the intersection of a horizontal and a vertical label, although they're not named explicitly.

While A-Calc provides most standard math functions, it has none for finance. It supports a limited form of macros as single line user-definable formulas, each containing up to nine parameters. Natural recalculation is not available, so you must sequentially recalculate the spreadsheet for each forward reference used; it doesn't detect circular references. You can open up to five independent GEM windows onto the spreadsheet. Using these with the built-in trace option lets you follow changes in target cells which is useful for debugging models. A view command can also be used to increase the size of the grid shown on screen by going to a smaller display font size.

A
spreadsheet is
a piece of electronic
paper, a pencil and
a calculator.

A variety of cell formatting options are available, such as user-definable prefixes and suffixes (up to four characters each) which you can change for different cell ranges. You can also embed printer control codes into individual cells for additional styling of the output.

A-Calc reads and writes .DIF files as well as creates text files of the output; .WKS files are not supported. The program can also print sideways, but only on Epsons and compatibles.

Unfortunately, the A-Calc Prime manual is only 52 pages long and doesn't have an index. Some of the more complicated features are only given scant mention, and the horizontal and vertical lookup features aren't documented at all. It's a shame that such a powerful program has such sketchy documentation.

While not capable of graphics itself, the program can export data files to the companion program A-Chart for plotting. (Editor's Note: For a full review of A-Chart, see "Show It: Business Graphics" elsewhere in this issue.)

Logistix
Logistix is an extremely powerful package aimed at the business market. It goes beyond even Lotus 1-2-3 by providing an integrated module for time/project management as well as spreadsheet, database and graphing capabilities. Unfortunately, Logistix was ported from the IBM and shows it- there's been no attempt to incorporate any use of GEM, and the manual comes with only a four-page addendum to cover the changes from IBM to ST.

Despite the lack of GEM, Logistix is easy to learn. It uses the familiar series of nested command menus along with arrow keys to move about the grid. Most menu items display a short explanatory note when selected and there is a good context-sensitive Help key available when needed. Mixed cell references are not supported, although about every other advanced feature is, including linking to other spreadsheets stored on disk.

The macro implementation is excellent. With the auto execute mode, you can create templates that run as standalone applications - the program operates solely in the background. Logistix also has the largest number of available functions of those surveyed but comes up short in the statistics group. A large number of new time-based functions are provided to assist in the project management operations. Natural recalculation is the default mode and the program flags circular references. You can carry out all standard database operations within the spreadsheet, including table look-up, searches and sorting. In addition, Logistix can import files created by dBase, a widely-used IBM database program. Several functions are available for operating directly on the database entries in conjunction with the search commands.

You can do graphing with Logistix by entering code terms in spreadsheet cells to define the type of plot, source of data and custom features desired. It's cumbersome, but it does work, and it can produce excellent bar, line, pie and X-Y graphs, as well as multiple pie and Gantt (timeline) charts. You can direct output either printer or a plotter, and the program includes routines allowing you to configure your files for a particular peripheral.

The project management module lets you define the steps needed to complete a job in terms of the manpower (or other resources) and time requirements for each step. These are kept together under a series of columns that define the time axis for the project (hours, weeks, etc.). Use of the time functions lets you do a critical path analysis on the overall job to maximize resource allocation, create job schedules and predict milestones. Incorporating this optimization within the spreadsheet lets you build cost models for project management that are very valuable for business planning.

A variety of different data file formats can be imported including .WKS, .DIF and text. These are translated, as necessary, with foreign functions stored as text. Output can be to the printer or a text file and a number of print formatting options are available. Only data values can be printed, not their formulas.

STart Spreadsheet Comparison Chart A

VIP
Professional Masterplan Swiftcalc ST A-Calc Prime Logistix

System Setup

Program Version 1.2 1.0 1.1 2.09 1.15

Color/Monochrome Yes/Yes Yes/Yes Yes/Yes Yes/Yes Yes/Yes

GEM Utilization Limited Good Limited Good None

Copy Protected No No No No No

Spreadsheet Design

Size (according to manual) 8192 x 256 8192 x 256 8192 x 256 8192 x 256 2048 x 1024

Maximum Cells: 520 ST 320 (960)¹ 599 4086 13912 _²

                    1040 ST 9638 (12310)¹ 12598 29824 50251 26504

Screen Page Size: Color 13 x 8 (20 x 8)¹ 13 x 8 16 x 7 13 x 6 (18 x 8)³ 20 x 8

                       Mono 15 x 8 (20 x 8)¹ 15 x 8 16 x 7 15 x 6 (32 x 6)³ 20 x 8

Number of Windows 2 2 2 5 2

Label Overflow Yes Yes No Yes Yes

Link Spreadsheets⁴ No No Yes No Yes

Max length of text or formula 240 240 69 128 254

Significant Figures 14 14 15 10 13

Macros: Command Yes No No No Yes

               Function Yes No No Yes Yes

               Auto execute Yes No No No Yes

Undo Last Command Yes Yes NO Yes No

Online Help Yes Yes Yes Yes Yes

Editing

Select Cell/Range By Mouse Yes/Yes Yes/Yes Yes/No Yes/Yes No/No

Name Cell/Range Yes/Yes Yes/Yes Yes/Yes Partial/No Yes/Yes

Varied Column Widths Yes Yes Yes Yes Yes

Protect Cell/Range Yes/Yes Yes/Yes Yes/Yes Yes/Yes Yes/Yes

Series Generation⁵ Yes No Limited No Limited

Freeze Titles Yes Yes Yes Yes Yes

Merge Spreadsheets Yes Yes Limited Yes Yes

Cell Formats⁶ 9 9 7 7 10

Addressing (Rel/Abs/Mixed) Yes/Yes/Yes Yes/Yes/Yes Yes/Yes/No Yes/Yes/No Yes/Yes/No

Calculations

Math Functions 15 15 8 13 23

Statistical Functions 7 7 5 4 5

Financial Functions 5 5 4 0 5

Logic Functions
    (IF/THEN, TRUE,etc.) 6 6 7 1 11

String Functions 0 0 0 3 0

Date Functions 5 5 0 4 15

Logical Operators
    (AND, OR, etc.) 9 9 0 9 6

Manual Recalculation Yes Yes Yes Yes Yes

Calc Order (Row/Col/Nat) Yes/Yes/Yes Yes/Yes/Yes Yes/Yes/No Yes/Yes/No Yes/Yes/Yes

Check Circular Reference Yes Yes No No Yes

STart Spreadsheet Comparison Chart A continued . . .

VIP
Professional
Masterplan
Swiftcalc ST
A-Calc Prime
Logistix

Graphics

Create Within Program Yes Yes Partial⁷ No⁸ Yes

Bar Charts (Hor/Ver/Stacked) No/Yes/Yes No/Yes/Yes Yes/Yes/Yes Yes/Yes/Yes Yes/Yes/Yes

Pie Charts Yes Yes Yes Yes Yes

Line Charts Yes Yes Yes Yes Yes

XY Charts Yes No Yes No Yes

Data Sets Per Chart 6 6 No Limit 80 No Limit

View As GEM Window Yes Yes No Partial No

Realtime Spreadsheet Link⁹ Yes Yes No No Yes

User Scaling Yes Yes Yes Partial Yes

Log Axis Scale No No Yes No Yes

Save in DEGAS Format No Yes No Yes No

Database

Support Database Functions Yes Some Yes Some Yes

Table Lookup (Hor/Ver) Yes/Yes Yes/Yes No/Yes Yes/Yes Yes/Yes

Levels of Sorts 2 2 1 1 1

Search for all in group Yes No Yes Yes Yes

Search for first in group Yes No No No Yes

Extract search group
      to another Yes No No No Yes

Delete search group Yes No Yes No Yes

Read dBase Format No No No No Yes

I/O

Read/Write WKS Format Yes/Yes Yes/Yes No/No No/No Yes/No

Read/Write DIF Format No/No No/No Yes/Yes Yes/Yes Yes/Yes

Read/Write Text Format Yes/Yes Yes/Yes No/Yes No/Yes Yes/Yes

Format Disk No No No No No

Delete Files Yes Yes Yes Yes Yes

Select Print Range Yes Yes Yes Yes Yes

Send Printer Control Codes Yes Yes Yes Yes Yes

Adjust Margins Yes Yes Yes Yes Yes

Header/Footer Yes Yes No Yes Yes

Print Sideways No No Yes Epson FX80 Yes

Print Cell Formulas Yes Yes No Yes Yes

Dump Graphs To Plotter No No Yes No Yes

Benchmarks¹⁰

Scroll Data (100 Cells) 89 (71)¹ 89 134 34 27

Copy Value (1000 Cels) 4 (3)¹ 4 104 23 3

Copy Formula (1000 Cells) 16 (34)¹ 16 110 42 7

Template Test 5( 4)¹ 5 7 6 4

Notes For Spreadsheet Charts

¹ Parentheses indicate figures for text-only version.
² Program requires minimum of one megabyte memory.
³ Using View Option.
⁴ Values in current spreadsheet may be dependent upon a spreadsheet on disk.
⁵ Will generate a series of numbers given the first number, last number and step size.
⁶ Numbers and text can be formats such as currency, number of decimal places, right or left justified, etc.
⁷ Graphing done by auxiliary program included with Swiftcalc ST.
⁸ Creates graph data file to be viewed/plotted with A-Graph program (not included with A-Calc Prime).
⁹ Changes are made on graph as they are made on spreadsheet.
¹⁰ All times are in seconds.

The Comparison Chart
The comparison chart shows a variety of possible features and how they're implemented in the different spreadsheets. No single program has all of the desirable features, so look over their various strengths and weaknesses to find the program that best meets your needs.

I determined the maximum cell count by exhaustively copying a value with the largest number of significant figures supported by the program. There is quite a disparity between the ideal maximum grid size and how much of it can actually be used due to memory limitations. The timing benchmarks are an attempt to give relative measures of speed for scrolling and common operations. The template test used a model that produced mortgage amortization schedules for a range of interest rates. Only math functions common to all programs were used.

Summary
There are two broad classes for spreadsheet use: home and business applications. The typical home user wants an easy-to-use program with moderate analysis power; graphics are desirable but not essential. A business user, however, requires more sophisticated functions, integrated modeling with graphics and database management. Other needs of the power user can be communication with other office PCs, presentation graphics and formatted summary reports.

For the home user, I recommend Masterplan. It features an intuitive user interface, built-in graphics, a good library of functions and the ability to use .WKS files. A-Calc Prime can also be satisfactory (if you're willing to master its interface) but has limited analysis capabilities and needs the companion program A-Chart for graphics. The remaining program in this class, Swiftcalc ST, is flawed by an awkward GEM implementation, inability to read .WKS files and extreme slowness.

At the professional level, VIP Professional and Logistix are good workhorse programs. Both feature a full complement of functions, extensive macro capabilities and .WKS file compatibility. Differences are in the GEM interface and easier graphics access of VIP Professional versus the time management, increased number of available cells and dBase compatibility of Logistix.

As with most software, the best spreadsheet for you is the one that meets your individual needs, not necessarily the one with the most "bells and whistles." Determine your needs, examine the program's features, then make your decision. The money you save may be your own.

If you'd like to see more articles like this, circle 173 on the Reader Service Card.

Jim Pierson-Perry is a research chemist and semiprofessional musician, living in Maryland. He is a frequent contributor to START and Antic.

Products Mentioned

A-Calc Prime, $59.95, A-Chart, $39.95. Antic Software, 544 Second Street, San Francisco, CA 94107, (415) 957-0886; (800) 234-7001.
Logistix, $149.95. Progressive Peripherals, 464 Kalamath Street, Denver, CO 80204, (302) 825-4144.
CIRCLE 174 ON READER SERVICE CARD
Masterplan, $189.95; VIP Professional, $149.95. ISD Marketing, Inc., 2651 John Street, Unit 3, Markham, Ontario, Canada DR 2W5, (416) 479-1880.
CIRCLE 175 ON READER SERVICE CARD
Swiftcalc SI, $79.95. Timeworks, Inc., 444 North Lake Cook Road, Deerfield, IL 60015, (312) 948-9208.
CIRCLE 176 ON READER SERVICE CARD