UNIT 5: More on Functions and Accounting.

The two principal aims of this unit are a) to round off the study of functions and b) to advance further along the route of the financial programs, drawn up in Unit 4, to enable the small business user to prepare accounts at the end of the financial year. Although the small-scale accounting programs set out here will be of interest chiefly to the sole trader, they nevertheless provide an introduction to the elements of spreadsheet design. This will in turn provide insight into the way spreadsheets work, so that the student can both go on to design more complex accounting programs, and also use commercial spreadsheets with greater understanding of the programming principles on which they are built.

If you are a veteran of the BBC Micro, you may remember using string functions as a tool for creating artistic patterns of letters which were perhaps more decorative than useful. However, now that the Internet has become the key medium of communication in the present era, such patterns of letters can acquire a new lease of life as a tool of password security. Your i.s.p. no doubt advises you to change your password frequently. If you wish to do this on a regular basis, string functions can help to vary the encoding of your password. You could take, for example, your grandmother's maiden name and turn it into a palindrome, as follows:

Let us assume the name in question was von Habsburg. The following program will reverse it:

5 REM keywords STR$;LEN;MID
10 REM string function for palindrome
20 INPUT "Enter the word to be reversed: ",A$
30 PRINT FNpalindrome(A$)
40 END
50 DEF FNpalindrome(A$)
60 LOCAL P$,J%
70 FOR J%=1 TO LEN(A$)
80 P$=MID$(A$,J%,1)+P$
90 NEXT J%
100 =P$

How does it work? With a little help to get you started, this is your first assignment for this unit. But don't worry - the full answer will be given in the next unit! The learning strategy for solving this one involves two apparently opposite - but in reality complementary - approaches to programming. The received wisdom is to work "top- down", i.e. to structure your program into clearly defined procedures (and in this case functions!), instead of using a plethora of "GOTO" statements which turn the lines of logic into a heap of spaghetti. On the other hand, the learner is in a very different position from the techie, and can only advance by building up concepts incrementally, from the bottom, brick by brick.

We will start with revision of the keyword MID$, and to illustrate what we are doing we need to INPUT a string with at least four characters:

10 INPUT A$
20 P$=MID$(A$,3,2)
30 PRINT P$

If we INPUT "Helena", P$ will be returned as the string constituting the middle portion of A$, starting on the third character in from the left and continuing for two characters, which returns "le". But as we shall see, the representation of the concept "middle" by the keyword "MID$" can in fact be stretched to any point on the string, whereas "LEFT$" and "RIGHT$" can only operate on those sections of the string beginning, respectively, at the left and right ends.

Now let us put a loop into this, as follows:

10 INPUT A$
20 FOR J%=1 TO 2
30 P$=MID$(A$,2,J%)
40 PRINT P$
50 NEXT

The next steps, for you to carry out, are a) to set the loop to the number of characters in the string A$, and b) to concatenate P$ itself at the end of your definition of P$ (i.e. the final term in your statement P$= will be +P$). And don't forget that the term on the left hand of the equals sign is the result of what happens on the right!

More Accounting

At the time of writing, we have just reached the end of the financial year in the UK, and students of BBC BASIC - particularly those running a small business - will be beginning the task of preparing accounts for the Inland Revenue. However, the tabulated calculation strategies outlined below will be of universal interest to anyone who has to manage accounts. Many people in the UK will be racking their brains as to how to update their software to cope with the new requirements of self-assessment, or in default of updating it, what new package to buy. The great advantage of writing your own software is that you can update it yourself!

In Unit 3 we devised a program to keep a running total of a bank account balance, in between statements. This is always a useful way of making a mental check, in order to give you direction in deciding which documents to access and process. But it would naturally also be useful to have both written backup to your on-the-spot assessment, and a more secure way of bridging the gap between the printed balance of the last bank statement and the daily update. The solution is to tabulate text and numbers into a simulated bank statement. This will involve both automatic totalling of input figures and the spacing of text and numbers in columns. Herewith a specimen program:

10 MODE 3
20 REM TITLE "STATMNT"
30 REM BANK STATEMENT
40 :
50 T=0
60 PROCheaders
70 PROCselect
80 END
90 DEFPROCheaders
100 VDU2
110 PRINTTAB(5) "DATE","TRANSACTION";SPC(30-LEN("TRANSACTION"))"DEBIT","CREDIT","BALANCE"
120 PRINTTAB(5)STRING$(68,"_")
130 VDU3
140 ENDPROC
150:
160:
170 DEFPROCselect
180 @%=&2020A
190 CLS:PRINT'''"Select credit or debit C/D. Type 0 to finish.":INPUT transaction$
200 IF transaction$="C" OR transaction$="c" THEN PROCcredit ELSE IF transaction$="D" OR transaction$="d" THEN PROCdebit ELSE IF tr ansaction$="0" THEN END
210 ENDPROC
220:
230 DEFPROCcredit
240 PRINT''"Enter date (5 characters, eg 01APR 25MAY)"
250 PRINT " *****"
260 INPUT D$
270 IF LEN D$<>5 THEN CLS:SOUND 0,-15,52,20:PRINT'''"ERROR!"'"INCORRECT DATE FORMAT"'"PRESS ANY KEY TO CONTINUE ":G=GET:GOTO 240
280 INPUT"Transaction details ",A$
290 INPUT"Amount ",amount
300:
310 total=total+amount
320 N=35-LEN(A$)
330 VDU2
340 PRINTTAB(5) D$;" ";A$;SPC(N),amount,total
350 VDU3
360 G=GET
370 PROCselect
380 ENDPROC
390:
400 DEFPROCdebit
410 PRINT''"Enter date (5 characters, eg 01APR 25MAY)"
420 PRINT " *****"
430 INPUT D$
440 IF LEN D$<>5 THEN CLS:SOUND 0,-15,52,20:PRINT'''"ERROR!"'"INCORRECT DATE FORMAT"'"PRESS ANY KEY TO CONTINUE ":G=GET:GOTO 410
450 INPUT"Transaction details ",A$
460 INPUT "Amount ",amount
470 :
480 total=total-amount
490 N=25-LEN(A$)
500 VDU2
510 PRINTTAB(5) D$;" ";A$;SPC(N),amount,STRING$(10," "),total
520 VDU3
530 G=GET
540 PROCselect
550 ENDPROC

Since this program is largely a matter of theoretical revision, applied to a practical purpose, only a few technical remarks will be necessary at this point. Lines 110, 340 and 510 take a simple approach to tabulation, by using the keyword SPC for spacing the columns. For a small-scale job such as the one in hand, SPC is adequate, but in the subsequent cash book program we will use a tabulating function.

Line 270 contains an introduction to error-trapping with the use of sound. The date has to be entered in a string of five characters, or the whole line in which it appears will be askew. Provided we keep to five characters, there is no need for the extra labour of setting up a more complicated date entry routine for this short program. However, should a false entry be made, line 270 notifies you of the error, complete with sound effects, and sends you round in a loop do enter the date again! More about the sound potential of BBC BASIC on a future occasion.

A Cash Analysis Book: Using functions to tabulate columns

Having got your bank balance up to date by means of the statement program above, the next task is to draw up a tabulated account of profit and loss for the last financial year. And since we carried out the first task by simulating a bank statement, the most obvious transition from manual to electronic accounting is to simulate a cash analysis book. The one supplied to me by my accountant some years ago was the Collins Cathedral AnalysisBook. In the spread of two pages for receipts and payments, each page is divided up into columns suitable for the following entries:

Date Payee/Creditor Cheque number

-after which follow six columns on the receipts page and twelve columns on the payments page, respectively, so that the receipts and payments can each be grouped together in the totals columns. The program below simulates such a cashbook, and would best be used for the first time by "shadowing" the manual entries. You can then surprise your accountant by submitting your own miniature spreadsheet as a backup to the cashbook - and in future years, your spreadsheet will become the norm.

10 MODE 3
20 CLS
30 REM ANALYSIS BOOK
40 :
50 PROCheaders
60 PROCentries
70 PROCprint
80 :
90 END
100 :
110 DEFPROCheaders
120 VDU2
130 PRINT'''
140 PRINT "DATE CHQ","PAYEE","TOTAL","PRINTING","ADVERTS &","TOOLS &","TRAVEL &","SUNDRIES"
150 PRINT " NO "," "," ","POSTAGE","PUBLICITY","MATERIALS","TRANSPORT"
160 PRINTTAB(30)"STATIONERY"
170 PRINT STRING$(80,"_")
180 VDU3
190 ENDPROC
200 :
210 DEFPROCentries
220 total=0
230 CLS:PRINT'''"ENTER SETTLED BILLS IN ORDER OF DATE, GIVING DATE,"
240 PRINT "TOTAL PAID AND CATEGORY "
250 PRINT'"PRESS ANY KEY TO START":G=GET
260 REPEAT
270 CLS
280 PRINT "Enter the transaction data. Type 0 to finish"
290 PRINT'''"Enter the date of the payment, eg 01APR, 30SEP "
300 INPUT date$: IF date$="0"THEN PROCrule:END
310 CLS:PRINT''"Enter the sum paid ":INPUT sum
320 IF sum=0 THEN PROCrule
330 total=total+sum
340 CLS: PRINT'"Enter the last three digits of the cheque number "
350 INPUT CHQ
360 CLS PRINT''"Enter the name of the payee ":INPUT payee$
370 CLS
380 PRINT''"Select the category of the payment: "
390 PRINT'"STA=printing, postage, stationery"
400 PRINT'"PUB=advertising & publicity"
410 PRINT'"MAT=tools and materials"
420 PRINT'"TRA=travel and transport"
430 PRINT'"SUN=sundries"
440 INPUT CAT$
450 :
460 VDU2
470 @%=&2020A
480 PRINT date$;" ";STR$(CHQ),payee$,sum,TAB(FNTAB(CAT$))sum
490 @%=&90A
500 VDU3
510 UNTIL FALSE
520 ENDPROC
530 :
540 END
550 :
560 DEF FNTAB(CAT$):LOCAL C
570 IF CAT$="STA" THEN C=30 ELSE IF CAT$="PUB" THEN C=40 ELSE IF CAT$="MAT" THEN C=50 ELSE IF CAT$="TRA" THEN C=60 ELSE IF CAT$="SUN" THEN C=70
580 =C
590 DEFPROCrule
600 VDU2
610 @%=&2020A
620 PRINT STRING$(80,"_"):PRINTTAB(20)total
630 @%=&90A
640 VDU3
650 ENDPROC

By and large, this program constitutes revision and consolidation of theoretical work which has been completed already. That said, it illustrates "top-down" structuring in dividing the tasks up into procedures. One special feature, to back up your recent studies in functions, which are admittedly difficult, is the tab function in line 480, defined in lines 560-580. This tabulates each individual payment under its specific heading, as well in the column of all payments. Note that the tab stop is set by the keyword TAB, qualified by the function FNTAB. This name is not itself a keyword, but an optional name given for identification purposes. The CAT$, in brackets, makes the tab stop dependent on the category of payment chosen, which is defined in the conditional branching in line 570.

These headings have been arranged to fit on to a page of A4 in portrait format. If you have a wide-carriage printer, or can persuade your own printer to use landscape format, then the number of columns can be extended to include more categories.

Admittedly this is a very elementary spreadsheet for a very small business. If it is too small for your needs, it can be expanded by the addition of extra columns. These will exceed the width of a sheet of A4, printed @ 10 cpi in portrait format, but if you can either print out in landscape format or use a condensed print option, then you can use the same number of columns as a cashbook. You could also add a procedure to total each column every quarter -as well as employing the function for averages from Unit 4. You could make all of this automatic by setting a condition dependent on the last date of each quarter.

In conclusion, I must thank Richard Russell for many helpful suggestions with regard to the use of programming syntax in this Unit. Any shortcomings are entirely my own.

Left UNIT 4

UNIT 6Right


Best viewed with Any Browser Valid HTML 3.2!
© Edmund Burke 2000