OS/2 e-Zine - The Best OS/2 Reading Anywhere!

January 16, 2004

Salesbook for Travel Agency

Salesbook for a Canadian (Ontario) Travel Agency using Mesa2

Record keeping in this industry is regulated by rules specified by the Travel Industry Council of Ontario (TICO) and the International Air Travel Authority, if you sell airline tickets. Additionally an agency must meet the accounting requirements of the Province of Ontario and the Government of Canada for income tax and sales tax purposes. This produces a very complex accounting challenge that I have met by automating the data entry and processes using the Mesa2 spreadsheet from Sundial System. Using a spreadsheet provides the flexability to meet the continually fluid environment faced in the quickly changing travel industry that could not so easily be met by a one-size-fits-all commercial accounting package. This is easily done with Mesa2 and Rexx scripts. On the other hand, the accounting person needs to be skilled in the functions and scripts available to them.

This article will describe a Mesa2 spreadsheet that meets the above challenges and provides users with examples of formulae and scripts that they may adapt to meet situations they encounter. First, I will describe the design philosopy and advantages of the sheet's layout. At this time some of the reasons for relationships in sections and cells will be explained. In the second part there are some 'exercises' that the reader is encouraged to 'do' that illustrate use of a few of the Rexx scripts. If you do not own Mesa2, every OS/2 user should, then head over to Sundial System and download and install (very easy) the trial version. You need an Addin to Mesa called Datafilter, also from Sundial. You will need it to examine the accompanying SALESBOK.M2 sheet. Once you have done this load SALEBOK.M2 and continue reading. It is recommended that you work on a copy as in COPY as executing the macros will alter the data in the sheet as they perform their functions. You may wish to restart your lesson with the original file but remember the UNDO command can be helpful.

Structure of the Workbook

(click image to get full resolution screen shot)
SALEBOK.M2 contains three layers and thirty seven scripts. I will not deal with the MON monthly layer that facilitates commissions payrol at the end of each month. It is kept 'blank' to speed calculation of the workbook. The AGT or agent sheet contains range definitions for the database functions in the MON layer. Each script starts by a comment identifying the 'macro' keys used to activate the script and a description of the pupose of the script. Code is commented to explain what is happening.

SALEBOK.M2

A travel agency provides a service between two principals the vendor and the client. It's books therefore contain sales to the customer balanced with a purchase from the vendor (credit/debit if you like). Sales must equal purchases, your basic double entry bookkeeping. I wish to credit my father-in-law Mr. Theodore Gray, a charter member of the Society of Management Accountants of Ontario for the intuitive outlay of Allison's books.

SALES:

Sales must be traced according to the Travel Industy Council of Ontario regulations, by type: cash, cheque, or card and by currency, yielding six input channels : (Bracketed Letters represent columns of the Sheet), cdncash(M), cdnchq(N), cdncard(0), usacash(P), usachq(Q), usacard(R), plus the accounts receivable(S) for unpaid balances. Accounts Receivable (S) are only kept in Canadian dollars. Translation from US dollars is done by entering the amount in US and multiplying by the value in the exchange column (U). The exchange rate on the day of sale will carry through to completion of the sale. All if this data is primary and must be keyed-in. (I tried importing a .CSV data from a file but agents are not accountants and produced unreliable quality so this was abandoned).

PURCHASE:

The income from sales is disbursed on the Purchase side of the sheet. The data for each column can be calculated with specific formula (see sheet). Basically, the amount paid to a vendor is the sale minus the commission earned or to be earned (account receivable), minus the goods and services tax on the commission if it was earned from a GSTable vendor .. usually Canadian. Account Payble (to vendors) is whatever is left. Cash flow between these accounts can be come very tricky and is not for consideration here.

DATA:

Sales and Purchase must be tied to people and companies and to receipts, invoices and purchase orders. This is done in columns A through L

A: Date of Sale
B: Date of Travel
C: GST 'trigger' Blank = no GST, 1 = Vendor is GSTable. Activates GST(AJ) if 1.
D: Vendor Code: ie. AC = Air Canada
E: Customer Name: Surname,Givename
F: Receipt Number
G: Corresponding Receipts for 'backtracking' or Comments
H: Invoice Number
I: Voucher Number
J: Purchase Order Number
K: Agent Identifier ie AS = Ann Smith (agent sale), SA = Smith Ann (sale done for office)
L: Deposit Number corresponds to a required list of receipts that generate a bank deposit.

The use of the Datafilter add-in and Rexx scripts allow search, extract and sorting or accounting records based on this data. It is this aspect that will be most interesting and useful for the reader.

There are many other columns (124 of them) that provide reporting and summaries. For example by entering a less-than and a greater-than dates sales for a period may be obtained. Similarly for sales by agents for a period. Have fun and if you have questions post them on Mesa 2 news server or email me.

Examining Scripts

Ok, So let us look at a couple of scripts. Look at the sheet and the INVOICE CUSTOMER on row 16. This has not been paid but the cheque came in the Post today. Time to clear it off the Account Receivable (AR). Here's how. Go to a blank cell ie H25. enter the digits 5555. Make sure H25 is your current cell. I have configured Mesa2 so that the enter key on the number pad 'does nothing' when I enter a number using it ... convenient. Now make sure the CAPS LOCK key is ON as all the Scripts are activated by upper case keys. Press CTRL-F12 or CTRL-F11. Mesa will take the value of the current cell and search the layer to find the next occurrence of that value. Mesa can search for values, contents, and formulas. Your cursor should now be on H16 highlighting 5555. Mesa has found the Invoice to be paid off! Beats searching a pile in a shoebox. Now press CTRL-Q. Mesa will do a bunch of things for you. First it turns off the screen display to prevent flashing while it executes and moves. It copies all the data and sales entries. It then runs down column A (Date of Sale) until it finds the first blank cell (at the bottom of the data). Then it pastes all that information into the correct columns. It copies the amount in AR to the cheque column. It changes the value in the AR to negative (writes it off). It clears some (sales) cells. It copies down the Depost #, Date of Sale, Moves the original receipt # right one column from 6 to 7 so that it is available for conjunction reference. It increments the previous receipt number by point one (.1 ... this is a convention we use to save money on-no-carbon-required receipts .. we just use a plain paper one for internal purposes and increment the previous number by .1). This little bit of code was quite difficult to get functiong as the previous cell can be blank, a string, or a number. You can't add .1 to a string! Finally, it turns on the screen so that you can see what it has done for you.

In the same mail delivery a US$ cheque has come in from the US Vendor (row 22) for the commission. We can search for this in a number of ways: Customer Name, Vendor, PO#, etc. Move to a blank cell, ie. D25 and enter "US VENDOR" ... and enter it with your number-pad entry key that does nothing! Press CTRL-F10. Mesa2 will get this string from the current cell and search the spreadsheet for another occurrence. It DOES matter how the cell is formatted for display left, right, or center. The ^,', or ", is part of the search string and a 'US VENDOR will not find "US Vendor. In this example you will have to press CTRL-F10 four times to get to the record we want to clear. Then enter CTRL-L. Mesa2 will copy the required data from this row, and remove the commission outstanding counter in Column AK. Move down to the first blank row in the sheet, enter the data and then copy down the date of sale from the previous row. Copy down the number of the previous receipt and add .1 to it. Clear the sales and purchase cells of the row. The script checks to see if the commission is zero and if not then writes it off for either/or/and the agency or the agent. Calculate the canadian value of the commission based on the rate of exchange in Column U (remains the same for the sale/purchase over time) and reversed the value of the commission in AF. Of course it is sometimes necessary to adjust the automatic entry to adjust the real value of the commission that came in from the vendor but this is much easier to do when necessary than manually executing all the above steps ... oh my aching fingers! CTRL-K similarly clears Canadian commissions.

Should one wish, it is possible to program a complete accounting system in Mesa2. This sheet is a work in progress and continually behing improved. I have shown only a couple of the scripts. As you read the scripts you may wish to consult the on-line help that comes with Mesa2.