CalcResult. (evaluation) Gregory Yob.
CalcResult
Consultants get unusual jobs, and I found myself one day in a firm which is well known in Silicon Valley when my manager asked me into his office for a private conference. His bosses had requested yet another revision of the salary requirements for his employees, and since this information was considered sensitive, my role as a consulant led me to a confrontation with some ledger paper and a calculator. After about 30 minutes and 30 mistakes, I got the revisions, totals, and estimates done for five employees and 12 monthly periods.
If I had had CalcResult, the job would have taken about ten minutes, of which six or seven minutes would have been used to set up the model and the rest for entering the various numbers. Further revisions would be merely a matter of updating the numbers that required new values.
An irony of all this is that the firm in question operates a computer network, in which it uses some 1000 computers. Every employee has a computer terminal permanently logged on to the network, but the handwidth of a telephone line makes apreadsheets impractical to use.
What CalcResult Is And Does
Most businesses have tasks that are usually done on ledger sheet with a pencil and calculator. Most of these are small enough that the time and expense of writing a program, say in Basic, is too much for serious consideration. Many of these tasks are in fact one-time jobs, like projecting and comparing two bank loans at different terms and interest rates.
As a simple example, suppose you have taken a loan of $1000 at an interest rate of 12% per year. You are to pay back the loan at $1000 per month with the interest due being paid first, and the remainder being applied to the principal. Figure 1 shows what you would end up with (I cheated and did this one with CalcResult.)
To set this up, I first made a grid of rectangular cells, with columns A through M and rows 1 through 10. Column A is used for titles, so that leaves the 12 columns B through M for the various numbers, including the twelve months for one year. I then put the starting information (loan amount, interest rate, and monthly payment) into cells B3, B4, and B5 for future reference. Note that the cells can be identified by their row and column co-ordinates.
The next step is to create the row labeled Month and to number the 12 months. Though this is easily done by hand, note that the value for cell C7 is the number B7+1, D7 is equal to C7+1 and so on. Now, to get the first value for the interest, I calculated 1000*12/12/100--that is principal * yearly interest rate/12 months per year / 100 for percentage. This works out to 10. The balance is 1000+10-100 or principal+interest - payment.
The remaining months are calculated from the previous month's balance and the interest rate and payment values. For example, the interest for Month 5 is (Month 4 balance * yearly interest rate /1200) and the balance for Month 5 is (balance for Month 4 + interest for Month 5 - payment).
Figure 2 shows these relationships in terms of the cell co-ordinates. For example, the first month's interest comes out to be B3*B4/1200 placed in cell B9.
To get all of the numbers in Figure 1 requires a few hundred keystrokes on a calculator and a great deal of re-checking for mistakes. Yet, all of the monthly values are computed from the first three numbers. If the interest rate were to be changed, all of the monthly computations would have to be redone.
A spreadsheet program lets you specify the formulae (the relationships in Figure 2) for the monthly figures. Now, if the first three numbers are entered, CalcResult will automatically compute each of the 24 monthly numbers. The fear of fingers slipping on the calculator is gone forever. Even better, if you change one of the first three numbers, the automatic computation is done again, and the new monthly figures are immediately available.
To sum it up, a spreadsheet program has two advantages over ledger paper and calculators:
eliminated. lettingloans,
Data Entry And Formulae
If you examine Figure 1 closely, you will see that it has 13 columns of eight characters each, so the loan calculation is 104 characters wide which is more than the 40 characters offered by the Commodore 64 screen. When you start CalcResult, it displays a portion of the worksheet 21 rows by 4 columns.
The cursor keys are used to select the cell with which you are working (a yellow cursor filling the cell shows where you are), and if you move the cursor past the edge of the screen, all of the cells move one unit, that is, the screen scrolls so that the portion of the spreadsheet in the display always has the cursor in it. Since scrolling is nearly instantaneous, this windowing is easy to live with. The cell co-ordinates are shown on the left edge and the fourth line of the screen.
When you have the cursor in the cell you want, you can enter either a "label' or a "value.' Labels are entered by pressing Space followed by the text. (The text can be longer than the cell is wide.) If you enter a number, CalcResult sees this as a value. Entry of cell co-ordinates or mathematical operators puts a formula in the cell (CalcResult still calls this a value). If the number resulting from computing the formula can be calculated, this value appears on the screen. If not, the term NA (Not Available) appears.
You can alter text or numbers at any time and CalcResult will recompute the screen. The formulae are protected, and a special operation (blanking) must be used to re-enter formulas. This protects you from inadvertently destroying your formulae. As the cursor moves over cells, the formula for each is shown on the top line of the screen.
Main Menu Commands
Pressing the F7 key brings up an abbreviated main menu at the top of the screen. Selecting D, E, F, G, or P brings you to the second level menus. This provides access to some 50 major commands. Pressing F5 in any menu calls in from the disk a help screen which briefly describes each of the available commands. There isn't space to describe all of CalcResult commands (for these get the manual) but I will mention a few that I found especially useful.
The main menu serves mostly to select other menus or to escape from some function established from the sub menu commands. For example, L turns off the title, split screen, and window functions, and B serves to clear a cell holding a formula.
The command O serves to change the order of computation. If you look at Figure 2, cell D10 requires values from cells C10, D9, and B5. Normally, CalcResult calculates a sheet starting at A1 and going down through A2, A3 until all of the As are done. Then the B column is done and so on. Since D9 precedes D10, all of the alues for D10 are ready when the calculation arrives at D10. If I had laid out the sheet in Figure 1 in a vertical format (i.e., the months going down the page) there would have been trouble.
Each of the balances is computed from the prior balance, the interest, and the payment. If I had placed my columns with the balance preceding the interest, each calculation of a balance would have needed information from a cell to its right, but the interest column would not have been calculated yet. Upon discovering the problem, I could have changed this with O. Then CalcResult would compute moving horizontally through the rows, and the required interest value would be ready for each balance.
If your spreadsheet uses many input numbers, such a budget or tax computation, it may take CalcResult some time to recalculate all of the other values. In fact, there may be no point in doing the recalculation until the new values are in place. In the loan example, a $2500 loan at 14.5% interest with $143.45 monthly payment will merely give garbage values until the three numbers in cells B3, B4, and B5 are all entered. The R command lets you select manual or automatic recalculation. In manual mode, you have to press the up arrow key for recalculation of the spreadsheet.
Disk Commands
The disk commands are the usual disk catalog, backup, scratch, load, save, and so on. One feature of interest to users of VisiCalc is the ability to load and save files in DIF format. Though the CalcResult manual merely says you can load and save in DIF format, the implication is that a VisiCalc file saved in DIF format can be transferred to CalcResult. I have not had a chance to check if this feature works. It seems that DIF is for transferring the numbers and text only, and I am not sure if the formulae can also be transferred via DIF files.
I do have one gumble which is not really the fault of CalcResult. If you are using a separate disk for the spreadsheet data, your Commodore 1541 drive must be initialized every time you change disks. In all innocence, I removed the data disk and inserted my program disk to look at a help page. When I saved the data on the re-inserted data disk, my files vanished.
Until you are working with large spreadsheets, I solemnly suggest that you make many copies of your program disk (called backup in CalcResult which I feel is misleading) say from a friend with a 4040 drive and save the data on the same disk. And while we are at it, don't do anything really important with CalcResult until you have survived a few disasters.
Edit Commands
The Edit commands are the most powerful in the CalcResult repertory. In Figure 2, many of the cells refer to other cells in a repetitive manner. The edit command R (replicate) lets you copy the contents of a cell, cells in a row, or cells in a column to other cells. This includes both data and formulae. For example, I can replicate the formula found in D9 to cells E9 through M9.
When I do this, CalcResult lets me specify the cells either by name or by putting the cursor in the cell and pressing RETURN. Then the various cells in the formula are shown and the question Absolute or Relative? displayed. A look at the formulae in D9 and E9 shows that the first cell used, B10 is Relative, i.e., in D9 this will be cell C10, and so on. Cell B4 is the same in all of the new formulae, so this is an Absolute cell. Of all the CalcResult commands, Replicate is the most powerful and most useful. You may also copy or move data (without replication of formulae), and insert and delete rows or columns in a similar manner.
If you want to compare distant areas of your spreadsheet, the features called Split Screen and Window allow you to do so. The split screen lets you display rows or columns side-by-side without all of the in-between columns showing. For example, if I wanted to see columns A and B and L and M on the screen at once, I would use the split screen. In a similar way, I can make a window which shows a small area separately on the screen. (When pages are mentioned, note that up to four pages can be shown at once via Split Screen and Window.)
Also included in the edit screen (I am not sure why, for I think it might fit better under Formats) is the command P for printing the screen or desired parts of the spreadsheet to your printer.
Format Commands
The Format menu provides various options for the presentation of the information in a cell. This includes the color of the characters; numeric, integer, and monetary (i.e., xxx.xx) forms for numbers, and left or right justification within the cell. The formats can be replicated--quite a handy feature.
Global Commands
Two of these commands are useful for your screen display. C is used to set the columnar width for all of the cells. F is used to select the format for all of the cells. A "local' format for a cell set from the Format menu takes precedence over any global format.
About Pages
Up to now, I have talked about only one spreadsheet, a two-dimensional grid of cells from A1 to BK254. CalcResult lets you build additional spreadsheets (up to 32 total) and has a group of commands (the Pages Menu) for interrelating these pages or spreadsheets. At any time, the Commodore 64 can hold two pages in RAM. The F1 key flips from one page to another, and the Split Screen and Window functions can be set up to display different pages on the screen.)
To work with many pages, CalcResult uses a scratch area on the disk called the work area. If you call in a page from the work area, the current page is saved to the disk first, and then the new page is read in. Several of the Page menu functions are quite straightforward, such as Copy one Page to another, Delete a Page from the work area, Erase work area, Get and Put pages from/to the work area, change a page number and Negate all values in a page.
The more exotic functions in the Page menu make all of this worthwhile. The Add function sums the values for cells in a "vertical' sense, i.e. cell C3 in page 1 is added to cell C3 in page 2, and so on. The result is stored in page 32. An example of the use of this is to sum similar reports from several departments in a business. To some degree the arrangement of added spreadsheets can vary, and it will still all work. A variation, +, is less selective than Add, as Add ignores cells whose labels or formulae don't match.
The rightmost column in use in a page can be referred to by the next highest numbered page. This is done by using cells named @ 1, @ 2, and so on. The Global Recalculation command will then perform the task of redoing the entire portfolio with the data on the right of one sheet being transferred to the next one. In this way, you can build a spreadsheet that is up to 2048 (64*32) columns wide and 254 rows deep.
Evaluation and Remarks
All in all, CalcResult is a good program. Much care has been taken to make it friendly and tolerant of mistakes. It will often turn the screen red and ring the bell when an error is committed; most of the time the result is easy to fix.
I am not partial to "push this button, now push that button' style manuals, but I discovered that doing the tutorial did actually teach me enough about CalcResult to do a few things on my own without too many hasty searches through the manual.
The manual is in most cases excellent. I did find weaknesses in three aspects. First, the manual tells you to build "backup' disks, which are really your working versions of CalcResult, and to make "data' disks. All of this is fine, except to see a help screen you must remove your data disk and insert the backup disk. The insistence of the 1541 on initialization will get you after a while. I wish the manual were more specific about which disk is where in the tutorial section.
Second, in some cases the prompts on the screen didn't match the manual, so keep an eye on things. Third, in a few places you are advised to "play around' and then the narrative continues with the assumption that you didn't play at all. I had to do some hard thinking to get back to the tutorial a few times.
Many color photographs of the C-64 screen are provided. I found these essential as I went through the examples. I wish more manuals did this. By the way, some of the commands in the reference section are merely listed without any explanatory next. A bit of exploration is suggested.
CalcResult itself has two weaknesses, one major and one minor. The major one is the selection of white over blue for the cell area on the screen. I could not read certain combinations of numerals like 000 and 0689. When I rebuilt a version of my backup disk for a screen with one of the greys (I was expecting white over grey which is legible on my monitor), the result was yellow over grey, which was even more illegible. I hope the next version of CalcResult will also ask for "Data Color' to guarantee legible data.
For the present, you can survive by using one of two tricks. The first is to use the Format command to get the most legible color (I suggest light blue over the default blue screen) into cell A1. Now Replicate over the area you are going to use in the spreadsheet. This works only for small sheets, as a great deal of memory is used.
The second trick is to set the color as well as the formula for cells to be Replicated. Replicate will then carry the color along. The unused parts of the sheet will not be colored. The third option is to get a good monitor.
The minor weakness is that title protection does not transfer from the workspace when pages are being moved back and forth. I rather hope that some user-definable keystroke sequences will be included in future CalcResult releases. I could have defined a two- or three-key sequence to replace the 15 or so needed to restore the title protection I was using.
Another of the strengths of the program is a large variety of mathematical functions are available when you build formulae. This includes minimum; maximum; summing; mean and standard deviation over ranges of cells; net present value; a variety of higher math functions such as SIN, COS, ARCCOS, LOG10, and so on (useful for scientific work); nestable IF . . . THEN . . . ELSE statements; and the relational operators OR AND and NOT. All of these have precedence rules rather like Basic, so you do not have to play calculator when building formulae.
My overall evaluation is 4 1/2 stars out of a possible five. If you have to do business projections on a Commodore 64, CalcResult is a good tool.
Photo: Figure 1. Example of a loan calculation. By using a grid of cells, a simple loan calculation is performed. Each cell is identified by its row and column, i.e., the label My Loan is in cell A1. The Interest and Balance in cells L9, L10, M9, and M10 indicate that the last payment is $58.40 in Month 10.
Photo: Figure 2. Relationships between cells in loan calculation. The repetitive computations in the simple loan are shown here. These relationships are called formulae in CalcResult. Cells called Title hold strings for titling row and columns. Cells with Number hold values which are entered by the user. The remaining non-blank cells hold formulae which compute the number for a cell from values held in other cells. For example, cell C7 is calculated by adding 1 to the value of cell B7.
Products: CalcResult (computer program)