LIBRARY ACCOUNTING USING PARADOX:

LIBRARY ACCOUNTING USING PARADOX:
A Database Management Software Package

Finanzas y Administración de Bibliotecarias Usando la Base
de Datos PARADOX

Mary Ann Van Cura

Law Library
Hamline University
St. Paul, MN 55104, USA

The goals in implementing the electronic ledger and spreadsheets were to reduce or eliminate the time spent on manual computations of expenditures by fund, by month and by year; to provide a means of manipulating and rearranging the accounting data in the manual ledger; and to increase the amount of management information available to administrators.

We were using an automated acquisition system that did not have good fund accounting capabilities, and we had a paper ledger that did not permit manipulation of the accounting data it contained.

The ideal for us would have been to use an integrated automated system or an automated acquisition system with fund accounting capabilities. However, the stand-alone acquisition sys-tems in our price range lacked adequate fund accounting capabilities. And although we were and are a member of a consortium of nine institutions that shared an online catalog, it appeared that it would be a minimum of 3-5 years before the consortium purchased an acquisition module.

We were certain there would be added staff time required to design and maintain the data-base, but it looked as if it would not be sufficient to distract us from our other responsibilities and there was no other way to get improved data manipulation. We decided the staff time to maintain the database would not be significantly greater than the time required to maintain the manual ledger.

We wanted the benefits an electronic ledger could provide,since data could be manipulated to generate reports useful for accounting, library planning, collection development, and collection maintenance.

Fortunately for us, exactly at the point where we decided that PARADOX was the software we wished to use, the Hamline University Computer Center also decided to purchase it and sup-port its use on campus! As a result, we were able to buy Paradox at an educational and site/insti-tution rate of $200, at a time when the retail price was $695; and we would have ongoing training and support available outside our own small staff, should we need it.

Relational database management programs have the ability to share and compare information from multiple database files, and they often have a programming language as well. With flat-file programs, on the other hand, one must reenter the data in every file that will use the data. In terms of applications, "...flat-file systems are intended for fairly simple applica-tions such as managing mailing lists, client lists, telephone directories, form letters and personnel files. The relational packages, meanwhile, are best suited for creating accounting systems, inven-tory-control systems and other types of big, complex data bases" (Dunn, p.45).

In relational databases, data is stored in tables. Each cell can hold only one value. Each cell in a column must have the same type of data-- e.g. text, integers, dates. One or more columns must be designated as the "primary key". The key values of a record are used to distinguish it from other records.

Because we had decided that our primary use for the DBMS would be accounting, we wanted to buy a relational DBMS. After reviewing the available software, we chose a top-rated, state-of-the-art, relational database management software package called PARADOX. Infoworld has described PARADOX as "one of the highest-scoring products we've ever reviewed"; "a no-risk investment from novice to expert"; "best combination of speed, ease of use, and query power"; and "Paradox documentation is among the best we've seen" (Petreley, Jan. 1990, p.54, 67).

Query by Example is the method PARADOX uses to ask questions of the data that has been typed into the tables. As a side note for those who are more familiar with query languages, PARADOX is nonprocedural (based on relational calculus) and is an example-driven program (Query by Example).

or MS-DOS 2.0 or later. A hard disk is recommended (supports both expanded and extended memory). It is also available on the OS/2 operating system. And a LAN pack for 5 additional users is available (Petreley, p.67).

We ran it successfully in-house on an IBM-compatible microcomputer, Zeos, with a 286 chip and a 33MB hard disk. (10 to 14 MB of the hard disk were actually used for Paradox. To have two years of data in the system, we anticipate needing 20 to 30MB, depending on how much bibliographic data we decide to enter.)

The response time to search a 1700-record table of invoice entries, retrieving only those en-tries that pertained to the last month of the fiscal year, took 5-10 minutes. Printing a ledger report two pages long took about 5-8 minutes. Printing a spreadsheet report for the month took 2-5 minutes. Printing a 19-page list of 975 ID numbers and corresponding titles took 30 minutes. (Printing was done on an Epson, letter-quality, dot matrix printer.)

We will be moving the PARADOX database to a Tower system with a 386 chip and an 80 MB hard disk. This will increase the response time and provide computer space for both the PARADOX accounting functions and a new stand-alone acquisition system.

The essential end products for us were: 1) monthly financial statements, indicating account balances, free balance, and cash balance, and 2) a ledger or voucher type printout that could be compared with the university's monthly printout of expenditures. In order to produce these re-ports, we decided to produce the following permanent and temporary tables.

• Materials Expense Table -- one record per title or part purchased. Data is entered as invoices are processed for payment or as funds are committed by the library for future payment to others. [Note: Initially we input one record per invoice, to save data entry time. Over time we will be entering one record per title or part since this will permit us to track prices per title over the course of time and project costs more effectively.] (Hamline's Name for Table: MATEXP)

• Operating Expense Table -- one record per item purchased or per service rendered. Data is entered as invoices are processed for payment or as funds are committed by the library for future payment to others. (Name: OPREXP)

• Income Table -- one record per payment or donation received. Data is entered as income is processed for deposit or as funds are committed by others for future payment to the library. (Name: INCOME)

• Bibliographic Information Table -- one record per bibliographic entity or cataloging title. Data is entered the first time an item is entered into the materials expense account, MATEXP. (Name: BIBDATA)

(Each month the new temporary table replaces the one generated during the previous month.)

• Encumbrances for Expenses -- At the end of every accounting period (usually at the end of each month), a table containing both anticipated expenses and expenses paid after the end of the accounting period is created from the permanent expense tables. (Names: MATENC and ENCUMB are created from MATEXP and OPREXP, respectively.)

• Anticipated Income -- At the end of every accounting period (usually at the end of each month), a table containing both anticipated income and income received after the end of the accounting period is created from the permanent income table. (Name: INCENC is created from INCOME.)

• Expenses for Current Accounting Period -- At the end of every accounting period, a table containing solely the expenses paid during the accounting period is created from the permanent expense tables. (Names: MATMO and MONTH are created from MATEXP and OPREXP, respectively.)

• Income for Current Accounting Period -- At the end of every accounting period, a table con-taining solely the income received during the accounting period is created from the permanent income table. (Name: INCMO is created from INCOME.)

A general outline of steps required in the preparation of the two essential, periodic reports is given below. Table 2 gives a summary of the Table Purposes for the application.

• Use the permanent expense and income tables to create tables of outstanding encumbrances as of the day after the end of the current accounting period. (For example, if the accounting period report will cover July, search the materials expense table for items dated for payment from August forward.)

• Use the permanent expense and income tables to create tables of expenditures and income for the current accounting period. (For example, if the accounting period report will cover July, search the expense table for items paid for during July.)

• Create and printout the financial statement reports for the current accounting period. [Report Fields/Columns: FUND#, FUND NAME, EXPENDED, Y-T-D EXPENDITURES, Y-T-D CASH BALANCE, OUTSTANDING ENCUMBRANCES, FREE BALANCE, CURRENT ALLOCATION, ORIGINAL ALLOCATION

During report design for expense and income reports, codes are placed in the report that tell Paradox to retrieve data from 4 different tables. In other words, 4 tables are "linked" to generate these reports. The reports will pull information from a combination of the following tables:

- Two temporary expense or income tables containing data solely from the period under consideration (i.e. month, quarter, year) -- e.g. MATENC and MATMO

• After the financial statements have been prepared for the current accounting period, manipulate each of the temporary expense and income tables to produce tables arranged like a ledger/payment record.

• Create and printout the ledger reports for the current accounting period. [Fields/Columns: FUND#, FUND NAME, DATE PAID, VENDOR, ID#, INVOICE DATE, INVOICE#, AMOUNT]

• Cancellations list arranged by location and call number and/or by fund account

• Serials cost history (year-by-year comparison; percent increase; by fund, by title, overall)

• Serials processing workload--manipulate the daily serials piece count to tally the number of pieces handled per month by staff position

In the fall of 1989 we began to design a PARADOX database that would produce an elec-tronic ledger and spreadsheets. By June of 1990 we had entered into the database the bookkeeping data for a full fiscal year, and we had generated monthly ledgers (by account) and monthly and annual spreadsheets.

Dunn, Si, "Building Business with Data Bases", Computer User, (Nov. 1988): 1, 45-6.

Petreley, Nicholas, Zoreh Banapour and Linda Slovick, "Product Comparison: Analyzing Relational Databases," Infoworld, (January 8, 1990): 51-68.

"Relational Database Program," Software Digest Ratings Report, 4 (7): 1-12 (July 1987).

Warden, William H. and Bette M. Warden, "An Introduction to Database Management Systems," Library High Tech, Issue 7: 33-40.

Wong, Harry, "Relational Database Development: A Primer for the Business Person," C, (Aug. 1989): 10-11, 18.