Back to Inspector Generals main page
Columns DCWatch
Archives Elections Government and People Budget issues Organizations |
Mitchell & Titus, LLP Certified Public Accountants and Consultants Board of Trade Building 1129 20th Street, NW, Suite 800 Washington, DC 20036 Tel (202) 293-7500 Fax (202) 822-8126 washington.office@mitchelltitus.com A PRE-AUDIT REVIEW OF THE DISTRICT'S SYSTEM OF ACCOUNTING AND REPORTING (SOAR)Prepared by: Mitchell & Titus, LLP Prepared for: Office of the Inspector General November 9, 1999 Mr. Charles Maddox Dear Mr. Maddox: This letter transmits our report on the pre-audit review of the District's System of Accounting and Reporting (SOAR). Our review was done to make a preliminary assessment of the functionality of the system and the District s ability to produce certain financial statements included in the Comprehensive Annual Financial Report. We look forward to continuing to work with you. Please do not hesitate to call us if you have any questions. Sincerely, A PRE-AUDIT REVIEW OF THE DISTRICT'S SYSTEM OF ACCOUNTING AND REPORTING (SOAR)IntroductionMitchell & Titus, LLP as the auditor of record for the fiscal year 1999 audit of the Comprehensive Annual Financial Report (CAFR) was asked to perform a pre-audit review of the District's System of Accounting and Reporting (SOAR). The SOAR system was implemented October 1, 1998 (the beginning of the fiscal year) and will be the primary accounting record used to prepare the fiscal year 1999 CAFR. Our pre-audit review was done to make a preliminary assessment of the functionality of the system and the District's ability to produce certain financial statements included in the CAFR. ScopeWe performed a mid-year review of SOAR. Our review included evaluating the functionality of SOAR and assessing the effectiveness of internal controls over transaction input, processing and system output. The context of our review was the Office of the CFO's planned interim closing as of March 31, 1999. Their goal was to produce certain financial statements included in the CAFR report from SOAR as of March 31, 1999. We attempted to perform selected tests of account balances from the closing and to perform an in-depth review of the closing process. BackgroundIn September 1997, the District of Columbia Financial Responsibility and Management Assistance Authority (Authority) awarded a contract to acquire a new financial management system (FMS). According to the Chairman of the Authority, implementing the new FMS would:
District officials stated that SOAR is a state of the art, Y2K compliant, integrated financial management system that
More specifically SOAR consists of the following: Functional Architecture
|
R*STARS | ADPICS | Performance Executive | Performance Budgeting | Fixed Asset & Inventory |
District officials stated that:
R*STARS (Relational Standard Accounting and Reporting System), the core accounting module, is a relational system of general ledger, budgetary control, cash management, encumbrances, expenditures/payables, revenue/receivables, projects/grants, performance data and cost allocation that
ADPICS (Advanced Purchasing and Inventory Control System) is an integration with R*STARS for seamless processing of requisition/purchase transactions that
Performance Executive/EIS (Executive Information System) is a high level analysis tool for program and financial management that
Performance Budgeting is a client/server based tool to develop agency budgets with MS Windows look and feel that
The proposed schedule for SOAR implementation anticipated:
To provide some level of assurance that the SOAR system is fully functional and capable of supporting a reliable CAFR for the period ended September 30, 1999.
We performed the following:
We found that the SOAR system is not fully functional in that it is not fully implemented and integrated. Thus, significant manual reconciliations are still required. The District remains challenged to "close" its books and records and produce a reliable CAFR for the year ended September 30, 1999. The March 31, 1999 interim review of the system functionality, which resulted in the preparation of certain financial statements, was completed. However, the purpose of the interim review was to determine whether information processed in SOAR as intended and not to determine the accuracy of balances, as out of balance conditions and system issues existed.
Ideally, an entity's "basic" financial statements (balance sheet and income statement) are prepared from a "direct " summarization of its general ledger accounts. The SOAR chart of accounts architecture, as implemented, does not directly summarize to all financial line items as presented in the CAFR. Certain line items are reported on a functional basis. The functional information resides in the SOAR system at a lower detailed level and must be extracted and summarized.
District of ficials originally planned to use an "off the shelf" software package, "CAFR on Micro," to summarize financial information obtained from SOAR to prepare its CAFR. However, District officials later determined CAFR on Micro did not meet its needs or provide the required flexibility for CAFR preparation. An internally developed, series of excel-based spreadsheets were developed to provide more flexibility. Manual, labor intensive efforts are required to prepare the CAFR, as was the case using the former system. These manual labor-intensive efforts increase opportunities for errors, omissions and misclassification.
Conversion of the beginning balances occurred in two phases - (1) automatic conversion of accounts receivable, accounts payable, and deferred revenue accounts and (2) the manual input of remaining balances. The District did not have adequate controls in place to ensure that final balances were properly carried forward from FMS to SOAR. Analyses of the parallel run of the two systems were not performed and reconciliations were not completed timely. As a result, District officials were forced to recreate the details of the balances, creating double work and additional burdens on District personnel.
Further, communication regarding the conversion of account balances was lacking. Periodic and consistent communication was disseminated only on those accounts which were converted automatically, while limited communication occurred on the conversion of the remaining account balances. Poor communication, chart of account differences between FMS and SOAR and the lack of participation by many key agency and department officials elevated the conversion process into a herculean reconciliation effort. In addition, agencies had the ability to change opening balances, adding to the reconciliation issues.
Emphasis for the conversion was placed on accounts receivable, accounts payable, and deferred revenue for continuity of key business operations. Accounts payable, accounts receivable, and deferred revenue were initially converted, in aggregate, using preliminary balances, as planned. Subsequent adjustments were made to change the preliminary balances to the final balances. However, the reconciliation of the detailed supporting balances were not completed as of September 30, 1999.
The detailed underlying information for accounts receivable, accounts payable, and deferred revenue was not automatically converted due to difficulties experienced in the differences in account structure between SOAR and the former system. The information needed from the former system to record it in SOAR was not readily available and in some instances information had to be re-created in the required format.
The second phase of the conversion process involved manual input for the remaining accounts. This process began in April 1999 and was still ongoing as of September 30,
Additional key actions that District officials will need to address to prepare a reliable CAFR:
The SOAR system is not fully implemented, integrated, or functional, as intended. As of the date of this report, the District did not fully implement core modules of the SOAR system, District- wide, for all intended agencies. Below, we have provided the utilization status as of the date of our report of the five core modules of SOAR. In addition, District officials identified several agency subsystems which were to be fully integrated with SOAR. As of the date of this report, many of these interfaces were not complete.
The R*STARS module, implemented in October 1998, is the core accounting module but is not utilized across the District. District officials stated that various component units are using other accounting systems as their system of record.
The ADPICS module has procurement and purchasing capabilities, although the procurement feature was not purchased for full implementation across the District. District officials purchased a separate system, OCP Express, with procurement and purchasing capabilities. Several District officials indicated that OCP Express would better meet the complete procurement requirements of the District. The implementation schedule and interface requirements for SOAR and OCP Express did not coincide. Therefore, agencies were given the choice to use ADPICS (with procurement capabilities) or OCP Express. OCP Express, ADPICS with the automated procurement capabilities, and ADPICS without automated procurement capabilities are being used by the District. In the end, the District purchased systems that had performed similar functions.
The Fixed Asset & Inventory module of SOAR was projected for implementation in February 1999 and remains undone. District officials stated that there were not enough personnel resources to implement this module by the projected implementation date.
District officials stated that the Performance Budgeting (PB) module was not implemented for most agencies in December 1998, when planned. Implementation for 90% of the agencies was done between July and September 1999. We were told by District officials that agencies developed their budgets using an MS Access database instead of the PB module but eventually transferred the information into PB module.
Further, District officials stated that the Performance Executive (EIS) module was projected for implementation in January 1999. Implementation for selected District agencies has been completed. However, EIS is under utilized due to the limited distribution across the District and unfamiliarity with its functionality and capability.
The effect of under utilization of the system results in lack of uniformity among agencies and increases the risk for incomplete transaction populations. In addition, under utilization hinders timely and useful reporting of financial performance.
See matrix of SOAR modules implemented by agency and component unit in Appendix A. [Not available online.]
Many District agencies use feeder systems that contain critical detailed transactional information. These feeder systems, in many cases, are not fully integrated with SOAR. In several instances, the interface between SOAR and the feeder system is only partially automated or not automated at all. The lack of an automated interface increases the risk for errors and omissions, and increases the timeliness in "closing" the books and records. To update the SOAR system, a batch total or some other manual process is necessary to transfer the information from the feeder system. As a result of the partial or incomplete interface with SOAR, District personnel must reconcile the differences between the two systems - a tedious manual effort.
District officials identified eighteen agency feeder systems which were scheduled to have an automatic SOAR interface built. As of the date of this report, at least five of those targeted feeder systems have not been fully implemented with SOAR.
See matrix of interfaces fully implemented, partially implemented, and not implemented in Appendix B. [Not available online.]
Whether and when full implementation and utilization of SOAR will meet original expectations is uncertain. Progress has been made on SOAR implementation but significant efforts remain. Preparation of the CAFR using the SOAR system is possible, but many challenges will need to be overcome.
See comments and status from District officials in Appendix C.
GOVERNMENT OF THE DISTRICT OF COLUMBIA
Office of the Chief Financial Officer
441 4th Street, N.W., Suite 1150N
Washington, D.C. 20001
202/727-2476 www.dccfo.com
November 8, 1999
Mr. Gregory Holloway
Mitchell & Titus, LLP
Board of Trade Building
1129 20th Street, NW, Suite 800
Washington, DC 20036
Dear Mr. Holloway:
Enclosed is the Office of the Chief Financial Officer's response to the interim close review, conducted by Mitchell & Titus, at the request of the Chief Financial Officer. If you have any questions or concerns, please feel free to contact Natalie Marshall, Associate Chief Financial Officer or me on (202) 727-2476.
Sincerely,
Valerie Holt
Chief Financial Officer
Enclosure
Fiscal year 1999 was the first year the System of Accounting and Record (SOAR), implemented during FY1998, had been available for use District-wide for accounting/financial transactions by the agencies. SOAR replaced a 20+ year-old system that was deficient in meeting the District's needs for processing and reporting accounting data and information. Also, the old system was not Y2K compliant.
SOAR has many capabilities of a state of the art system and allows the District to have available the requisite data necessary to garner knowledge for decision-making. The citywide implementation was rolled out to District agencies in approximately eight months. This accelerated timetable and the difficulty faced in implementing such a complex system continues to present on-going challenges that are being given continual attention for resolution by the District.
The Chief Financial Officer of the District requested a pre-audit review of SOAR to get an objective third party assessment of SOAR. The independent auditors were to provide some level of assurance of SOAR's functionality and capability to support a reliable CAFR for the period ended September 30, 1999.
The District's response to the independent auditor's findings follow:
We are confident of our ability to prepare the CAFR using automated spreadsheets. We recognize and agree that this method of preparation requires a significant amount of intensive man-hours and is susceptible to inaccuracies. Therefore, this awareness mandates that we incorporate the necessary mechanical and people controls together with repetitive reviews of the output. "checks and balances" logs to assure our controls and review will support our controls and review, and the total involvement of experienced senior staff.
We agree that the conversion process was inadequate and lacking the necessary controls to ensure that final balances from the prior financial system, FMS agree with opening balances in the current financial system, SOAR. Our reconciliation of these detailed supporting balances is on-going and urgent with a completion date no later than December 7, 1999.
We agree that there has been an under-utilization of the SOAR system. Some of the significant contributing factors that we have identified are user support, the size of the population, and disparate spread of locations to be interconnected. All relevant issues are being re-examined and reviewed and a District-wide plan for corrective actions is being drawn up so that meaningful and useful attributes of SOAR can be of optimum benefit to the District. R*Stars is implemented in all of the major agencies comprising the District's general fund. ADPICS is implemented in approximately 96 percent of the District agencies. Personnel resources are delaying the implementation of the Fixed Asset & Inventory module. The District is targeting January, 2000 for implementation. The implementation and training for the EIS module continues. The District plans to complete the implementation no later than April, 2000.
Data are transferred from our feeder systems that contain critical detailed transactional information by computer tapes. Automated SOAR interfaces would be preferable, but transfer of data by tapes is a reliable means of transfer. Some of our systems can never be fully integrated with SOAR because the attributes of the data required to be captured for mandated federal reporting purposes are unique and are not "pure" accounting/financial data. We do have the checks and balances in place to protect the integrity of the processing of such data transfer to SOAR to assure the consistency of data being reported in all places.
Send mail with questions or comments to webmaster@dcwatch.com
Web site copyright ©DCWatch (ISSN 1546-4296)