Visicalc check register. Michael Coffey.

Visicalc Check Register

Visicalc is a language for describing spreadsheets and other arithmetic arrays. You can write a self calculating check register in a spreadsheet language. You can use data from the register in other programs you buy or write.

To make a check register, you must lay out fields in the worksheet, fill in some input data, install formulas, test it and use it. The goal is the worksheet pictured in Figure 1. Once the worksheet starts working, you need enter only additional data as you write checks and receive statements.

Because most of our numbers should be displayed in dollars and cents format, type/GF$ in Visicalc before doing anything else. Next, place the names of the columns at the top of the sheet. Use repeated hyphens to set the column titles off from the columns. You should freeze titles to the top of the screen with a/TH command issued from the row with the hyphens.

Check numbers should be displayed as whole numbers left justified in the first column. Typing >A4:/Fl will get the column started. We must exterd this format further down the column in order to avoid retyping it many times. We can extend as far as necessary for the number of checks to be entered. The command/RA4.A4:A5.A100 will format almost 100 check number fields. This replicate command, and all others in this article, should be made fully relative by pressing R at each RELATIVE OR NO CHANGE prompt.

The input area can now accept data. Starting at A4, type a check number, press the right cursor key, type the date, and continue. The date has to be prefixed by a quote sign to prevent Visicalc from dividing the month by the year. You place the amount in column E only for checks and other debits. Column F is for the amounts of deposits and other credits. Ignore the All Clear? and Balance column until you have completed the first six columns for about 10 checks.

The first formula to install is the balance calculator. You can always add any deposit amount to and substract any check amount from the old balance to get the new balance. The best place to put the opening balance is cell H3, where a dashed line now sits. Use the >H3 command to get to this cell within the title zone. For good looks, put a hyphen line at H2 while the cursor is in the area.

Make the first Balance formula +H3+F4-E4 at location H4. Now copy that formula all the way down the column with /RH4.H4:H5.H100. Column H should now show a correct running balance based on the check and deposit amounts you typed. Test it with all sorts of numbers until you are confident that it works.

Next, the All Clear column helps match your numbers to the bank's. Make sure that cell G4 is blank, enter/F* there and copy it down the column. This gives the column a graphic format. Cells that contain 1 will each show a single star. Cells that contain O will display no stars. Cells containing higher numbers would show more stars, but we don't want that.

The last column on the sheet, not seen in any report, is the Outstanding Amounts column. Start with +(1-G4)* (F4-E4) in cell 14 and fill the column with relative copies. This formula results in 0 if the corresponding All Clear entry contains 1; otherwise, it shows the net amount, positive for a deposit, negative for a check, of the transaction.

At the bottom of the Outstanding Amounts column, put the total, >I 101: SUM(I4.I100). Substracting this number from the final figure in the Balance column gives the balance that should be on the bank statement.

You can create a reconciliation center in the lower left of the sheet. Make labels for book balance, outstanding, and bank balance. This table is created with:

>A102:BOOK BALANCE

>103:OUTSTANDING

>A104:BANK SHOULD BE

>B102:+H100

>B103:+I101

>B104:+B102-B103

For smoothest operation, recalculation should be done row by row instead of column by column. Normally, Visicalc evaluates all of column 1 before evaluating row 1 of column 2. Since the reconciliation formulas in column B refer to values calculated in later columns, we want Visicalc to finish row 101 before calculating row 102. To force row oriented calculation, enter the command/GOR.

If you set a horizontal window at the bottom of the screen to hold only these three formulas with labels, you will have a feedback window that will be very helpful when entering data. The Act of Reconciliation

The hard part is over. Scan the entries and compare to your bank statement, entering a 1 into the All Clear cell for each check and deposit reflected in the bank statement. Make little check marks on the bank statement, too. When the input data is complete, the bottom line should contain the same balance as the bank statement shows.