Financial Systems Summary


Financial Systems Summary
  • Introduction
  • MSA Systems Background
  • MSA Payroll/Personnel
  • Budget System
  • MSA Budgetary Control
  • MSA Accounts Payable
  • MSA General Ledger
  • Automated Procurement System
  • Global needs

  • Financial Systems Summary

    W. David Wimberly
    January 1991

    Introduction

    This document provides background information and offers some perspective about the financial systems in use at the university. Its intent is to provide the necessary data for the development of specific plans and establishment of direction for these systems. The applications addressed are primarily the Management Sciences of America (MSA) systems, but the Budget system and Purchasing's APS have also been included due to their close relationship. Student Finance and Financial Aid are not addressed since their future will be governed by the AMS software recently purchased.

    An overview of the current capabilities and operation of each application is presented followed by development activities in process, outstanding problems and additional needs of that area. This information is based upon the personal experience and recollection of the author, input provided by Melinda Bowman, Talbert Malone, and Don McFatrich from Computing Services and information obtained during interviews with Tom Dorre, David Martinson, Dick Cottrill and staff from these areas. There has been a conscious effort made to remain objective on all issues. Additional information and corrections are welcome. An attempt will be made to document any contradictory opinions.

    MSA Systems Background

    The University's MSA packages were purchased in 1982. The Payroll/Personnel application went into production in January 1983 while the other accounting systems went production in July 1984. Vendor maintenance for all systems was dropped around October 1987. New releases and updates had been applied for the Payroll/Personnel system up through that time. MSA had made extensive changes in the Budgetary Control and Accounts Payable systems which prevented their upgrade over the years, leaving those systems and General Ledger at essentially their 1983 versions. All applications other than Budgetary Control were available in a batch and an online version. The online version was merely an online data capture or data entry facility tacked on top of the batch system. The BC system performs true online updates.

    One major maintenance facility that was provided by MSA is the Information Expert (IE) Reporting and Retrieval language. This proved to be an excellent reporting tool and has been heavily used. It replaced several old and obsolete report generators that were provided with the original products. The Labor Distribution module of Payroll/Personnel is one exception to this, the necessary interfaces for IE were never provided by MSA. Talbert Malone developed our own IE to LD interface, but most reports using this data are written with the archaic Special Report Generator.

    All of our MSA systems caused or suffer from some common problems:

    1. All applications forced extreme adjustments upon the administrative offices and basic changes in the way they conduct their business.

    2. All systems use the Data Communication Interface (DCI). This is a third party product provided by MSA which serves as an interface between the application programs and the Customer Information Control System (CICS). CICS, an industry standard IBM product, is our teleprocessor for online administrative mainframe applications (excluding Oasis which is being replaced). The source code for DCI has not been provided and Computing Services suspects that it will not operate under future operating systems or CICS releases. This could force us into the same situation we have been in with OASIS, either operating all systems on old operating system release levels or running multiple operating systems and not being able to integrate systems across those platforms.

    3. All MSA systems lack any recovery facilities. Currently file backups are taken after 5pm daily and again at the conclusion of batch processing each night. Any system failure forces a recovery to the point of the last backup. This means that a problem could occur at 4:30 and wipe out an entire days work, which has occurred with the Payroll/Personnel system.

    4. Enhancement of the systems is difficult due to the large volume and complexity of the application code, different standards and system components used across the applications, the technical nature of the system components used (like DCI), the absence of design for user modification, and the lack of adequate resources and training for this task within Computing Services. In summary the systems are not conducive to change and it is extremely difficult to add or change fields, screens or even validations.

    5. The systems are extremely difficult to test. Typically an entire test cycle, representing a full accounting or payroll cycle, must be executed. This is done after online transaction entry has been performed and involves executing entire sets of batch jobs. This process often requires days to complete but is required to verify most changes made within the system.

    6. Computing Services expertise with the systems is at an all time low. There currently are not support personnel with adequate expertise to cover all systems. Individuals with other jobs and responsibilities are often required to assist in training, problem resolution, and other ongoing activities.

    MSA Payroll/Personnel

    Overview

    The basic payroll/personnel system had performed as needed for the University (through 12/31/90) and must be considered a valuable application. It had performed payroll record maintenance and does accommodate our needs for unusual deduction requirements, check and deposit advice generation, Automatic Clearing House interface, W2 generation, federal employee processing, maintenance of labor distribution records, an accounting interface, and basic personnel records management. Implementation of the above features required a great deal of effort and experimentation on the part of the Human Resource office and Computing Services. Often times extensive modification of the system code and custom development of new code has been required as with the General Ledger interface, W2s, and federal employee retirement plans. Creative uses of standard features of the package have also been employed.

    Some of the above statements were changed to the past tense due to tax law changes effective 1991, for which there seems to be no means to address within the system. See the problems section for further details.

    Enhancements

    Extensive development has been required around this basic system in order to accommodate our many needs. This includes most of the reports that are used today and extensive data manipulation/update transaction generation. It is very common with this application to need to generate changes that affect groups of employees. This is almost always addressed by developing custom programs to access and extract existing data, manipulate that information, and generate resulting update transactions that are fed back into the system to effect the desired change. Many of these operations are performed to accommodate one time needs, but some are recurring.

    Additional areas where subsystems have been developed to meet unique needs include the following:

    1. The generation of the state payroll vouchers and corresponding data tapes required by Little Rock.

    2. The generation of reports and data tapes for the TIAA/CREF retirement system.

    3. The annual interface with the Budget System used to load and activate all employees with their new year salary and title information.

    4. An hourly time reporting and data entry system.

    5. A patched-in interface to validate payroll accounting distribution against the General Ledger Company-Cost Center at the time of entry.

    6. Development of a batch process that identifies data integrity problems that are not detected by the online entry programs.

    7. A modified interface that provides the necessary accounting transactions to the General Ledger system.

    8. Generation of reports and extract files from Labor Distribution data.

    9. Development of the interfaces required for the federal employee's FERS retirement system.

    10. The A21 certification process.

    11. The annual extraction of payroll/personnel data used to create census files and the extensive reports produced for the Arkansas Department of Higher Education and Institutional Research.

    12. The generation of the data extract for the annual directory.

    13. Programs to calculate insurance deductions and generate transactions to effect changes on the payroll master when coverage or rates change.

    14. Interface to an external and very outdated vacation/sick leave system.

    Further comments

    Some additional facts about the system should also be noted.

    Problems

    Some of the major problems that exist with the system include:

    1. Due to lack of any vendor support, there is a great risk that we will not be able to respond to some future legislated requirement. The system contains a taxing sub-system module which we have been able to manipulate to accommodate changes. However it must be recognized that changes could be dictated that would not be able to be addressed within the system as defined. Such a change could be devastating to the system and the risk involved must be recognized.

      We are currently in the throws of just such a situation. Effective 1991 there are separate caps that are in effect for the Medicare portion of the FICA wage deduction. The FICA tax has been composed of two parts, old age survivors insurance and medicare. The income cap for both of these taxes was previously the same and they were treated as one total deduction. Now the medicare component will have to be split out since it will continue to be taken beyond the point when the old age survivors insurance stops. This affects the maintenance and reporting of these tax deductions and the year end W2 reporting. As of the date of this writing, the university has not identified a means of addressing the requirements of this tax law change within our payroll system. The cost of implementing this change will apparently be significant.

    2. Our campus is responsible for paying the Cooperative Extension Service employees, most of which are federal employees as determined by their job title. There are over 700 such employees and a whole host of problems associated with this group:

      Cooperative Extension Service in Little Rock assist in the processing for their employees by remotely accessing our system and performing some data entry and maintenance functions.

    3. Hourly payroll processing involves such a high volume of data that it continues to be a headache in spite of the automation steps that have been taken to generate time sheet turnaround documents. Perhaps distributed departmental entry of data with centralized review is a solution.

    4. The system does not provide any automation feature for processing mid-period changes of pay distribution or salary. The effect of these changes must be manually computed and entered up front and then the new values properly entered following the payroll run. The alternative is to run the payroll with the old values, manually calculate and enter adjustments, and then enter the new values for the next period.

    5. The approval and processing of Personnel Action Forms (PAFs) is very fragmented and time consuming. This form serves as a source entry document for both the Budget System and Payroll.

    6. There is no real position control system and very limited integration between the Budget System and Payroll.

    7. The interface with General Ledger and Labor Distribution is handled differently at the end of the fiscal year so that the payroll expenses are reflected in the year they were accrued and not paid. This special treatment seems to always result in problems.

    8. W2 reporting has been a major problem year in and year out. Even with MSA support, W2 processing has always required special patches. For 1990 the W2 format was significantly altered and Computing Services chose to abandon the facilities provided by MSA and to write our own programs to generate the W2s and federal W2 tape.

    9. People have generally never been purged from the system, but merely moved into a terminated holding area since many return later to active status. Plans are in process to truly purge some unneeded data.

    10. The data integrity of many of the fields is not as high as it should be since the packaged system does not include all the data edits needed and some information is redundant with data carried on our Title-Occupation table. This causes Institutional Research many problems when trying to use this data. Work is in process to expand the customized data editing incorporated into the system. This will most likely take the form of after the fact batch checks due to the difficulties associated with modifying the online programs.

    11. The IE reporting facilities available to the Human Resource office do not provide access to all data. Additionally the request procedures and timeliness of getting these reports executed needs to be improved.

    Additional Needs

    There are many additional needs for automated systems within the Human Resource office. Some of these are certainly outside the scope of the "MSA Payroll/Personnel" system although they are related and therefore are listed here:

    1. Applicant tracking
    2. Non-compensation subsystem to track non-dollar taxable and non-taxable benefits
    3. Position control and management
    4. Tracking of employee life to date history for items such as titles, salaries, and positions

    Summary

    The system in place has operated effectively in spite of band aid fixes that have been applied, the batch orientation of the system, and our limited ability to modify the package. It is unknown how long this will continue to be true since we cannot anticipate tax law changes. Significant enhancements to the system and major new sub-systems are being considered by the Human Resources' office. There is also a great deal of discussion taking place regarding the Personnel Action Form and the Personnel Data Form, that they be program generated as turnaround documents or be replaced by electronic media. These should be integrated with an overall plan for this application including maintenance and support issues.

    Budget System

    Overview

    The Budget System was an online Oasis application that was rewritten verbatim in Natural when the Software AG products were acquired in 1986. The batch reporting aspects of the system were not modified at that time and continue to be Cobol programs which process sequential extract files. The system is used primarily to plan and track budget positions which include pay distribution. The position data is used in aggregate to determine a department's salary budget with fringe benefits projected from those figures. The system also maintains a department's maintenance budget and computing services allocation (tacked under bogus position numbers). The system is keyed by fiscal year which allows a new year's budget to be developed within the system while the position control function is being performed for the current year. At the beginning of the fiscal year, most positions with the associated people, salary, and pay distribution are loaded to payroll. Special forms are used for Agriculture and PAFs are required for individuals being paid from grant or contract monies.

    Some features of the system include: rolling the current year forward to create the base data for the new year, generation of appointment notice letters, and the integration of the online system with the MSA payroll (names are retrieved from that system when available).

    Problems

    Parts of the system are out of date and unused due to recent changes in the classified pay system (it no longer uses the step concept). The system also suffers from poor design in that some data is maintained redundantly when an individual is paid from more than one cost center. More significantly, the current operation creates the future year early in the fiscal year. This results in all personnel changes during a year having to be manually recorded within both fiscal years. The system also stores a Budgetary Unit Code in all records, which must be manually updated whenever a unit decides to change it's name and abbreviation (newer systems store a Budgetary Unit Number and lookup the code and name from a table). The Budget System was written before the NSM Architecture was developed, so it is not an NSM system and does not use the same standards, user interface or security as most other Natural applications. It also lacks a name search facility since the name is not stored within this system but obtained from the payroll master file.

    Miscellaneous

    The Budget Office is also using a microcomputer to track the University's use of the legislated titles, the allowed number of people in each title and how many are currently filled. This provides a quick check to ensure that a title is allowable. No interface exist to this system, data is manually verified from budget reports.

    Additional Needs

    An automated means of calculating salary savings is very desirable. This would require use of payroll as well as budget data and would be a complex task to perform.

    MSA Budgetary Control

    Overview

    The Budgetary Control (BC) module was developed by MSA for the government and education market. Its main mission was to provide encumbrance accounting and available funds checking. We are using version 5.0 which was designed to work with MSA's General Ledger (GL) and Accounts Payable (AP) systems circa 1982. BC performs many functions of the other two systems and cannot be used or even discussed without considering GL and AP. BC is the central focus of all of these systems since practically all input is made to BC and passed to and through the other systems.

    The BC system was designed to be an online system, unlike GL and AP. Its main function is to provide for the entry and maintenance of documents. These include requisitions, purchase orders, and invoices which are stored on the Encumbrance Document File (EDF). Adjustment documents may also be entered but they are not maintained in BC, instead they are passed directly along to GL. Purchase orders create encumbrances within GL. Invoices are passed along to AP, processed by AP, passed back to BC, and then forwarded on to GL. An invoice that references a purchase order will relieve the amount of the encumbrance originally established. All of this takes place at a document line item level which is where the accounting distribution (company, account and cost center) is coded. Many, many adjusting entries to the encumbrance balances are generated due to changes in accounting distribution at the time of invoice processing, variances in the dollar amount, addition of sales tax and freight (which occurs at the invoice level but must be distributed across all lines of the invoice), and the closing of purchase orders.

    The Accounts Payable office uses a Lee Data microcomputer with a 3270 terminal interface program to automate much of the processing associated with invoice payment. The PC automatically processes several MSA screens and prints coding data on the back of the invoice using a slip document printer. This data printed is determined from purchase order information obtained from the online system. This program functions like an automated operator and is required because changing the MSA online programs to function as desired is not practical. There is also a batch interface with Financial Aid that accepts financial aid recipients as vendors and financial aid awards as invoices, which are passed on to AP.

    The interface between BC and GL goes through an explosion routine which can be used to generate offseting entries in different companies (funds). Its use is critical to the operation of BC in an environment like ours where transactions for multiple funds are being processed. The explosion process is controlled by rules stored on the explosion master file (EMF). All of our external interfaces to General Ledger go through BC and this explosion process. These include the distribution of charges for physical plant and and long distance telephone services, cash receipts and non-student receivables from the Treasurer's office, accounting distribution for gifts received in the Development office, and use tax entries we generate during AP processing.

    BC's other main file is the Available Funds File. This file contains balances at the company, account and cost center level and higher levels where funds are to be checked. It is built from General Ledger but updated online as transaction activity occurs. Its purpose is to validate the accounting distribution being used and to check for funds availability. BC accesses the AP vendor file to validate vendor IDs placed on purchase orders and invoices.

    System objectives

    Two of the original goals for the MSA accounting systems were to restrict a unit from over-expending its budget and to provide agriculture with project accounting. Available funds checking has never been implemented, an issue not addressed by this paper. As a result of the lack of use of the AFF, Computing Services severed the system access to this file for validation and substituted access to the General Ledger Master file for Company-Accounts and our own Coster Center table for Company-Cost Center. This action was taken several years ago and the system resource savings associated with it are significant. It was also done with a clear understanding that we would never try to reimplement available funds checking, which would be an extremely complex and difficult task.

    Regarding project accounting for agriculture, it can only be said that it is provided at a token level. A problem emerged immediately after implementation when agriculture wanted the expenditures for an invoice to be distributed across numerous projects. An invoice with five lines to be distributed across five projects resulted in a data entry operator prorating the expenditures and entering 25 lines. This explosion of data entry volume was entirely unmanageable. It was determined early on that every line of an invoice need not be entered, but that they could be combined. However invoice lines could not be combined unless the items represented were for the same state expenditure code (object code). This is due to the requirement that a voucher be produced for each check written and that each voucher detail the object codes associated with the expenditure. With the state requiring invoices to be entered by object code and agriculture wanting the breakdown by project, the multiplicative affect could not be avoided. The result is that agriculture maintains their own books using a dBase application developed and maintained within Agriculture. This system is distributed state wide to all experiment stations and is used to prepare the required federal reports. Only salaries, wages and fringe benefits are maintained at the project level within the MSA GL system.

    Problems

    The problems which have been associated with this application are numerous. Although several have been addressed, circumvented, or are merely no longer applicable they have been included here to provide a complete perspective on the application.

    1. The BC system from the beginning has been less than impressive from both a technical and application quality standpoint. It is inefficient in operation, patchwork in its interfaces to AP and GL, and was generously endowed with bugs.

    2. MSA support for the system was poor to non-existent from the beginning, primarily because it was a new system and not widely used. The Atlanta development group was also more concerned about getting the next version of the product ready rather than fixing the current one. The field support personnel were not in position to debug the code but helped where they could.

    3. The AFF required rebuilding after every GL update, a process which was requiring 6 hours and longer. This was partly due to the need to pre-define every possible company, account, and cost center for which you might want to post. This forced us into week-end only posting to GL.

    4. MSA provided no documentation, jobs, or assistance of any form in planning and effecting the transition from one fiscal year to another. This is complicated by many factors, one of the most significant being the need to maintain life-to-date balances for research cost centers yet be able to report year-to-date activity. This remains a very cumbersome, time consuming, and error prone process.

    5. There is no means to truly balance or reconcile the entries passed between BC and AP or BC and GL. The counts and totals produced provide only a rudimentary method for checking that everything entered in one system made it to the other. For example, the system operated an entire year before it was realized that the cash entries were not being generated correctly due to the erroneous distribution of sales tax and freight across invoice line items.

    6. Version 5.1 of BC could not be implemented because the facilities provided to rebuild the AFF were rewritten and did not provide the same capabilities that existed in the previous version. This froze the university in version 5.0 in spite of the fact that we were paying maintenance for the newer versions of the software.

    7. Since the EDF is updated in real time while the GL is effective date oriented, there is no way to produce a report of purchase orders open at the end of the month that corresponds to the encumbrance balance reported on the monthly Departmental Budget Report (DBR).

    8. In general the system does little if anything to address needs of Purchasing. It does not retain adequate data to allow printing requisitions or purchase orders directly from the system, there is no vendor commodity coding provided, and vendor statistics are limited.

    9. Purchase orders are commonly closed when they should not be and left open when they should be closed. This is somewhat a procedural problem due to the accounts payable office not knowing which invoice is the last invoice that should be processed against a given PO. Currently POs are only closed upon explicit instruction to do so from the departments.

    10. There is no way to track a purchase order, with its associated activity, through its life-cycle. No inquiry facility for retrieving all invoices paid against a given purchase order is available and there is no way for Computing Services to develop such a facility because the EDF is a record typed VSAM file and an alternate index cannot effectively be defined.

    11. The AP vendor file is used for validation during document entry but the AP system is really a batch system. The result is that any document for a new vendor cannot be processed until the day after the vendor is entered online since it is not added until that night.

    12. The AP vendor file does not provide multiple addresses for a single vendor. This results in the same vendor being defined many times. The vendor must then be changed between purchase order entry and invoice processing since these interactions frequently involve different vendor locations.

    13. AP paying entity must be coded at the time of invoice entry, see the AP comments associated with paying entities for further explanations.

    14. The system has not been designed to accommodate any form of distributed processing such as direct departmental entry of requisitions and electronic authorization of documents.

    15. The system reinforces the practice of having the Purchasing department place the expenditure classification (state object code) on the purchase order versus Accounts Payable entering it at the time of invoice processing.

    16. No purge facilities were provided with the application nor was the system designed with any consideration for the purging of documents. We were forced to develop our own purge routines which result in problems when a document references a purged item (canceling an invoice that references a purged purchase order).

    17. Sales tax, freight, and use tax are not encumbered.

    18. Inter-departmental purchase orders are no longer entered to the system and therefore those funds are no longer encumbered. The adjustments associated with extending, closing or re-opening these orders resulted in excessive administrative processing. Without these encumbrances however true available funds checking can not be performed.

    MSA Accounts Payable

    Overview

    The AP system is required for use with BC. It operates entirely in the mode of online data capture, batch update. It consist primarily of a vendor master file, an item (invoice) master file, and a distribution master file. BC reads the vendor master file for validation and reporting, but maintenance of it is managed entirely by AP. Invoices are brought into AP in batch and stored in duplicate on the AP item file (they are also on BC's EDF). The Distribution master is a tape file containing detail records of the expense, payables and cash accounting entries generated by AP. The entries going to this file are also used to interface back to BC and then on to GL.

    The AP system will combine multiple invoices from the same vendor and issue one check for all charges. It will also delay payment relative to the due date or take discounts according to user specified options. Invoices are coded in AP by Paying Entity, a structure used to segregate items for processing. Payment for two invoices for the same vendor will not be combined onto the same check if the invoices are coded with separate paying entities. The entire check writing cycle can be different for each paying entity, however all (we have about 30 paying entities) are processed identically at the university. The university manages to avoid using most all of these features as discussed below.

    Enhancements

    The university is required to prepare expense vouchers for all expenditures. Paper forms and data files are required to be submitted to Little Rock. Any checks over $5000 must be approved by Little Rock before they can be issued. Vouchers must be segregated according to the fund source being expended and the total for each voucher must match the check issued to the vendor. The MSA system does nothing to assist in the generation of these vouchers. Modifications to the MSA system, custom development of a sub-system, and adoption of specific standards and procedures is required in order to accommodate our vouchering requirements. The MSA programs were modified to obtain an extract file containing invoice information for the invoices for which checks were being generated. Cobol programs process that extract and use additional tables to format the vouchers and voucher tapes. In order for the vouchers to match the checks and to be segregated according to the source of funds, the invoices must be entered on the front end with the correct paying entity. This requires the Accounts Payable office to determine up front the correct paying entity, prior to invoice entry within BC.

    The university is responsible for remitting use tax to the state. Use tax varies according the location from which goods were purchased/delivered since different city and/or county tax rates may apply. The MSA system does nothing to assist in the assessment, deduction, or reporting of use tax. Invoices for which sales tax has not been included must be coded at data entry time with a use tax code. This code identifies the appropriate use tax entity and rate. The MSA AP system has been modified to create extract records of invoices paid which have a use tax code. This extract data and external tax rate tables are used to create a cumulative use tax data base used for monthly reporting and to create adjustment transactions that go back into BC. These transactions charge the expending cost center with the associated use tax while offseting a use tax liability account. Monthly reports of the cumulative use tax are prepared, a check issued to the state for the use tax, and adjusting entries made to the use tax liability account.

    Problems

    The AP system is impressive in its ability to take discounts, delay payment to vendors, and combine multiple invoices on a single check. However the university is not able to take advantage of these features for the following reasons:

    1. Discounts are seldom taken since the processing may involve obtaining approval from Little Rock or having Little Rock prepare a state warrant, which usually takes so long that the discount is no longer earned.

    2. Holding payment to vendors until the due date is not practical for the same reason, but also because it makes it nearly impossible to match invoices with checks and vouchers (the invoices must be submitted with the vouchers to Little Rock). The Accounts Payable office is only able to manage this process by batching all invoices by day, and matching the invoices for a day with the checks and vouchers produced for a day. What gets entered on a day, gets paid 7 days later (explanation of the 7 day delay follows).

    3. Some invoices are able to be combined onto the same check. However this is rare since two invoices from the same vendor must be processed the same day. The invoices must also be able to be paid on the same voucher, otherwise they will be coded with different paying entities which results in the desired two checks and two vouchers.
    The result of the above problems is that we use this large sophisticated AP system as a big check writer.

    The reason for paying all invoices 7 days after entry is to allow the departments to approve the invoices for payment while keeping the process manageable and the vendors happy. The previous procedure was to first submit invoices to departments for approval, and only pay those that received that approval. Invoices were being lost or misplaced or just handled slowly resulting in many, many irate vendors. They were not being paid or were receiving payment extremely late (due to departmental approval cycle and the Little Rock approval cycle). The current procedure is to first enter the invoices for payment 7 days hence and then to send the invoice to the department for their optional disapproval. If the department does not contact Accounts Payable within the alloted 7 days, the invoice is automatically paid.

    Other problems associated with the AP system include:

    1. Duplicate payment is often not detectable due to the fact that an invoice may have been processed against the same vendor but with different vendor numbers. Invoices are also duplicated when they are processed under different paying entities.

    2. The same vendor is required to be defined multiple times, once for each separate remittance or purchasing address.

    3. Cancellations are extremely labor intensive, requiring the check, the voucher and all associated invoices to be cancelled. The invoices must then be re-entered to BC. Cancellations, among other reasons, are required whenever the paying entity is incorrectly coded which results in the item appearing on the wrong type voucher.

    4. The online inquiry facilities of AP are ineffective because they function within paying entity, not allowing vendor invoices to be retrieved across paying entity. All research of vendor or departmental inquiries are performed using internally developed reports which are written to microfiche.

    5. Credit memos often sit idle within the system and are not applied to reduce future payments to a vendor because they are also tied to paying entity. The system does not apply a credit memo unless it is for the same vendor and paying entity as an outstanding invoice.

    6. The interface that would normally exist from AP to GL has been butchered to go back to BC and from there to GL. This interface has been the source of many problems and incorrect posting entries, mostly due to inaccurate distribution of sales tax and freight cost to the invoice line item level.

    7. The system provides features for accumulating and reporting 1099 revenue for vendors (consultants income). Due to problems unrecalled by the author, this has never been effective and IE reports off of the EDF have been used in place of these facilities.

    8. The system does not provide an option to sort the checks as we would like, segregating those that must go to Little Rock for approval from those that do not (the $5000 limit). Therefore, checks that will be going to Little Rock cannot be signed and a yellow stickie is applied over the signature block before these go through the check signer used in the Treasurers office.

    9. The purge facilities provided are inefficient, often requiring 8 hours or longer to execute. They also purge cancelled items immediately versus using the age criteria which is applied to other items.

    10. Fiscal year end processing is extremely complex and limiting within the MSA AP/custom voucher system. There are different vouchers and voucher numbers required for new year expenditures versus old year. These also are tied to paying entities which have to be manually coded at invoice entry time. Currently there is a cut off date determined when all old year expenditures must be in to AP. After that date everything must be processed against new year funds.

    11. The voucher subsystem is prone to problems and is not well understood by anyone within Computing Services. There are currently three problem reports outstanding that deal with vouchers.

    12. The automated operator function being performed within Accounts Payable requires a Lee Data PC connected through the Lee Data network. Though no date has been set for dismantling the Lee Data network, it must be realized that there will be a limit to the length of time it can continue to operate.

    13. The existence of this automated operator function is clear evidence of the inappropriate design of the online system for use at the university.

    MSA General Ledger

    Overview

    The MSA General Ledger (GL) is similar to AP and HRMS in that it provides for online data capture and batch update. The primary function provided by GL is simply to maintain the detail accounting transactions and balances for each company, account and cost center. The online facilities provide for the entry of maintenance updates, posting transactions, and account and transaction inquiry. None of these functions are actively used other than account maintenance. The other maintenance functions have been front-ended by the Natural based TABLES system. Online entry of all posting transactions is accomplished through BC, so there is no need for those facilities. The detail transaction inquiry programs require creating a copy of the actual transactions and are limited in their functionality, for these reasons they were never implemented. Account balance inquiry is occasionally used, but most inquiry is now performed through the Balance Inquiry System (BIS) which has been developed in Natural using extracts from GL.

    The GL system came with numerous pre-defined reports, however none of these met our needs and are not used. Two report generators were also provided with the original system. The Ledger Report Generator (LRG) is used to build reports which require detail transaction data and is very limited in its capability. The Custom Report Generator (CRG) is fairly flexible although it only operates with account-center balances and requires the use of some high maintenance files. Both report generators are very inefficient. There are still a few reports that are written that use LRGs or CRGs, however the bulk of reporting has been addressed with IE. IE has proven to be a very effective tool for use with the GL files inspite of a few bugs in the Logical Interface Modules provided for GL. Computing Services would like to see all LRG and CRG reports converted to IE.

    The heart of the GL system is the GL Master file (GMP11). This file contains the company definitions (funds), account definitions, and the balances for each company-account-cost center. Balances are maintained for up to 13 periods each fiscal year and a life-to-date balance. Detail transactions are stored on sequential files, the GWP12 for the last posting, the GM331 for all open periods, and the GH792 for closed periods within the fiscal year. Cost centers are defined by company on the Custom Control File (CCF, also known as the TM700). The only other major file is the Master Control File (MCF) which is used by the Custom Report Generator and contains account and cost center reporting relationships.

    Current Use

    The GL system does a good job of maintaining the detail accounting transactions and account-center balances. From this base of data our financial information system has evolved in the form of a huge mass of reports. Practically all reports are written in IE. There are over 22,000 lines of IE code and 75 production jobs used to generate an unknown number of reports. It can almost be said that the reports are our system. There has been one or more analyst from Computing Services dedicated to GL reporting since its inception in 1983. The most significant report of all, the Departmental Budget Report, has evolved through at least three major redesigns and implementations.

    Two additional major developments have served to augment the GL system, TABLES and BIS. TABLES is a collection of files and programs that were developed to provide the necessary attributes for classifying, selecting and sorting accounting data. The MSA system provided for no attributes to be associated with a cost center other than a description. The university's operation revolves around cost centers while the MSA system is more oriented to accounts. Data needed to select, sort and process associated accounting information did not exist within the GL package. Therefore tables were defined to provide the necessary attributes, primarily related to cost centers and their organizational hierarchy. This system duplicated some information within GL and thus has had to act as a front-end to GL, feeding it information so that TABLES and GL remain in sync.

    The Balance Inquiry System (BIS) evolved as a way of providing secured and distributed online access to summary data. The data is loaded to Adabas after being summarized by account categories and numerous organizational levels, from cost center all the way up to company. Recently BIS has been expanded to include detail budget, income, and expense transactions which can be queried five different ways (including by PO). It also provides facilities for departments to question transactions and financial affairs to respond, all online - no telephone tag required.

    The online available funds checking that BC performed as a document was entered has been replaced by a report. This exception report identifies any center that is within a specified percentage of its budget.

    Much of the TABLES system and the custom reports that have been developed are the result of needs of Research Accounting. This area is the reason for maintaining life-to-date balances, active and inactive dates for centers, and a great deal of data associated with cost centers. The processing requirements of Reasearch Accounting were the primary reason for the development of TABLES. Even though many reports have been developed to meet their needs, there are many additional functions that should be automated but for which resources have not been available to address. Some of these would yield financial benefit for the university.

    Development Activity

    There are currently over 30 project requests open within Computing Services from Financial Affairs. Most of these deal with GL reporting and are not active at this time. Changes have recently been implemented to classify cost centers within the general fund as budgeted or non-budgeted. This is a new attribute that was added to the cost center file within TABLES. Its use has been incorporated into the Departmental Budget report, but also affects other reports and BIS. Likewise, BIS and general funds reports which are distributed to departments need to be modified to exclude fringe benefits and computing services allocations and reflect other changes made to the remaining account categories. These are items that have already been incorporated within the DBRs.

    A new approach is being tested to help meet the needs of Financial Affairs. David Hyatt from Financial Affairs is being trained in the use of IE so that he can write and modify IE report programs. Computing Services will monitor and provide ongoing supervision of David in this effort. Initial indications are that this arrangement is working very well, although Financial Affairs does not want it to be full time or long term.

    An item that is being considered, but which the impact is unknown, is the use of projects within the general fund. These were designed and originally used only for Agriculture.

    A project that has been requested and received some discussion is to investigate the replacement of the AP and BC systems. This project has not really gotten under way due to other pressing needs and lack of resources. The concept was to leave GL, its associated reports and our chart of accounts alone and focus on better addressing our needs in the other areas. If the requisition, purchasing and accounts payable functions could be effectively replaced, we could do without GL online functions which would only leave payroll/personnel using DCI.

    Additional Needs

    There seems to be a well recognized need for a front-end or subsystem to handle transfer vouchers. Service departments usually have internally developed automated systems to track and report charges for services provided to other departments. This accounting is usually provided in paper form to the departments billed and to Financial Affairs. Financial Affairs then enters these transfers manually into the accounting system. Physical plant has a somewhat automated interface, but other service organizations like Computing Services, Printing Services and the Book Store rely on Financial Affairs for manual reentry. A subsystem with a defined interface could accept transactions from all external systems, validate it, allow Financial Affairs to review and approve its entry into the accounting system, allow departments to interactively inquire on the detail transactions, summarize the data and post the expenses/credits to the General Ledger.

    Problems

    The most significant problems that exist within GL include:

    1. The state vouchering system requires that payment made for liabilities incurred in the previous fiscal year be identified, currently by a 'Y' in the third position of the voucher number. These transactions have become known as Y voucher expenditures. The expense for these Y vouchers is recorded within the new fiscal year. A few years back this resulted in an audit exception since these expenditures should be reported as a liability within the old year. To address this, a new report was developed to produce the same output as the year end analysis but using as its source only the detail Y voucher transactions. Financial Affairs then merges this data with the regular year end analysis results to prepare the year end financial statements. This was the immediate quick fix, however:

      This problem may be due to BC or AP, however the effect is felt in General Ledger. The issue is just coming to a head and discussions are planned to determine how it can be properly addressed. Hopefully a solution can be found so that our General Ledger will contain the correct entries for the correct time periods. A significant aspect of the problem is our procedure to use the date of the vendor's invoice as the criteria for establishing our liability. A better and more accepted method would be to use the date the goods are received or a service is rendered, however this would be difficult to do without central receiving.

    2. The GL master file is much larger than necessary due to the old BC requirement that all possible account-centers be pre-allocated. Even though we no longer build the AFF, new centers are always established with a pre-defined set of accounts. The size of the file directly affects the time required for all processing.

    3. The GL master file is organized by company-account-cost center while our operation and most reports look at the data more from the perspective of company-center-account.

    4. GL posting uses the existence of a company-account-center combination on the master file as a validation criteria for a transaction. The only alternative to this, which we use, is the "dynamic generate option" (DGO). DGO causes a maintenance transaction to be generated to establish the company-account-center if the company-account and company-center are valid. Active and inactive dates are associated with company-account-centers. It is necessary to inactivate research or grant centers, at which time all accounts associated with the company-center are inactivated. However, the dynamic generate option will establish a new account for the center if it did not previously exist. The result is a posting entry to a logically closed center.

    5. The BC system and its AFF originally imposed restrictions in the coding for our chart of accounts. Specifically the first two characters of the account number are a category classification. This provides no flexibility if we were to ever want to categorize our accounts differently.

    6. There is no place to store account attributes, such as the category just mentioned. We need to build an account table similar to what has already been done for cost centers. Other attributes are needed to provide the flexibility necessary to generate and modify reports. The alternative is to hard code account ranges and exceptions which always seem to be out of date and inaccurate.

    7. Financial Affairs would like all detail transactions for the year, with the possible exception of encumbrances, available online. Computing Services has made some commitment to provide this, however any data available online will have to be a copy of the true GL master file and will have to be constantly updated to remain in-sync.

    8. Financial Affairs is currently able to prepare the University's financial statements within two months of the fiscal year end. The basic data required is all contained within the GL, but not all associated classification and categorization data is currently present. This means that there is a great deal of manual effort required to manipulate and assemble all the information needed, although personal computers are used to assist in this effort. Also, last years data was all reentered onto a Macintosh in order to obtain high quality laser print output. It is envisioned that some day the entire financial statements will be produced directly from the GL.

    9. Older reports need to be modified to use descriptions and department codes from TABLES versus the unreliable information that is on the GL files.

    10. Practically all departments operate micro computer based systems of their own to keep up with their POs, expenditures and budget. Many of these departments employ a full-time bookkeeper with this responsibility. Financial Affairs feels that this is appropriate and that adequate information is being provided to the departments in a timely fashion (assuming departments are using the online access to detail transactions recently made available through BIS). Computing Services tends to feel that more should be done to provide departmental access to their central records (especially requisition and purchasing data) and their total budget picture, including the capability to enter and remove projected expenditures (commitments). This should eliminate the need for departments to maintain separate systems and to have to keep the data in these systems in sync with the central records.

    11. Some departmental reports are passed through several offices, often delaying access of the data to the end recipient. An electronic report distribution system would be of great assistance in this area, however BIS may also be used to expedite access to this same information.

    12. David Martinson expressed the need for special management reports to address the needs of the auxiliary services areas. Currently there are no project requests outstanding at Computing Services for these types of reports.

    13. A few LRG and CRG reports remain. These are slow to process and there is very limited expertise available about the use and operation of these report generators. The MCF file can also be eliminated when the CRGs are replaced.

    14. The only facility provided to ensure that the detail transactions balance with the GL master file is an LRG.

    15. There is no way to report detail transactions from two fiscal years with IE.

    Summary

    IE has proved to be a good a reporting tool and invaluable within the GL arena. Natural has proven to be effective for developing subsystems and replacement components of the GL package. In general, it appears that the primary needs of central accounting have been addressed by the system while more needs to be done for Research Accounting and the individual departments.

    Automated Procurement System

    Overview

    The Automated Procurement System (APS) is a PC/LAN based application which was purchased and implemented by the Purchasing office in March 1990. The system is considered a short term, stop-gap measure to address immediate needs of purchasing. Needs that were not addressed by the mainframe based MSA package. Some of the features provided by the system and used by purchasing include:

    Use of the system is also planned to be distributed to Physical Plant so that they may directly enter their own orders. Purchasing will then print, review and issue the purchase orders.

    Problems

    The APS system is actively being used by purchasing although problems exist related to its coexistence with the mainframe accounting system.

    1. Separate vendor files are maintained manually for the separate systems with no facilities for interface or reconciliation. It is recognized that the vendor address for purchasing is often different than that for invoice remittance and that vendor data needed for purchasing is different from that needed for Accounts Payable. However both offices deal with the same vendors and one common master file of vendor information is recommended.

    2. The coding structures and fields available for coding information are not compatible. Purchasing has developed a cross-reference table to relate class item codes to the state object codes (MSA account number) to partially alleviate this problem. A similar table could be developed to translate the organization unit identifier to company-cost center. However the use of two coding structures and the maintenance of translation tables is not an ideal situation.

    3. There is no interface between the two systems so purchase orders and changes must be dual entered within both systems. Computing Services has investigated the interface issues related to APS and the MSA accounting systems and found there is no good or easy way to accomplish this.

    4. The program source code for APS is not available, and therefore our ability to modify or customize the system, if desired, is limited to manipulating the data going in or coming out of the system.

    5. The system has not been designed to provide for the distributed entry of requisitions or inquiry regarding the status of purchase orders by the departments. Multiple users can access the system and their access can be restricted by function, however within a function there are no restrictions to what data might be manipulated. Security by value features are needed to restrict a department's access to only their own orders.

    Summary

    The Business Affairs office is very pleased with the operation of the APS system and the benefits it provides. They feel the system has significantly improved their productivity, provides more timely and accurate access to the status of requisitions, and greatly assisted in organizing the operation of the office. They also feel that through the use of the system they have gained significant experience with automated purchasing systems and have developed a better sense for their needs and how they can be met through automation. Even though duplicate entry is required into both APS and MSA, it is no more effort than was required previously since purchase orders are now entered to APS versus being typed. In fact, word processing capabilities are now available which assist in the process of developing the purchase orders.

    Global needs

    Throughout the discussions that have recently been held, several needs have emerged that apply to all areas. Any system development, maintenance or procurement should be made with these needs in mind.

    1. Reduce and eliminate where possible the use of paper and paper archiving requirements.

    2. Provide for the distributed entry of transactions and maintenance updates so that data is entered at or as near as possible to its source origin. This must of course be accomplished within a secure environement.

    3. Incorporate facilities that will provide for the electronic approval of transactions in a convenient manner. This should ensure the same level of security and auditability as is possible through manual forms.

    4. Continue to distribute access to data to all users with a need for that access.

    5. Automate all forms of inter-departmental transactions.

    6. Transform the communication and approval processes that involve Little Rock to an electronic format versus paper.
    The use of new technology and specifically imaging must be considered as a means of achieving these objectives. Obviously significant resources will be required to even begin to address these areas. These goals must however be established to ensure that the small steps that are taken are leading us in the right direction.