Logosm.gif (1927 bytes)
navlinks.gif (4688 bytes)
Hruler04.gif (5511 bytes)

Back to Inspector General’s main page

Pre-Audit Review of the District’s System of Accounting and Reporting (SOAR)
IG 13-32
November 9, 1999

Home

Bibliography

Calendar

Columns
Dorothy Brizill
Bonnie Cain
Jim Dougherty
Gary Imhoff
Phil Mendelson
Mark David Richards
Sandra Seegars

DCPSWatch

DCWatch Archives
Council Period 12
Council Period 13
Council Period 14

Election 1998
Election 2000
Election 2002

Elections
Election 2004
Election 2006

Government and People
ANC's
Anacostia Waterfront Corporation
Auditor
Boards and Com
BusRegRefCom
Campaign Finance
Chief Financial Officer
Chief Management Officer
City Council
Congress
Control Board
Corporation Counsel
Courts
DC2000
DC Agenda
Elections and Ethics
Fire Department
FOI Officers
Inspector General
Health
Housing and Community Dev.
Human Services
Legislation
Mayor's Office
Mental Health
Motor Vehicles
Neighborhood Action
National Capital Revitalization Corp.
Planning and Econ. Dev.
Planning, Office of
Police Department
Property Management
Public Advocate
Public Libraries
Public Schools
Public Service Commission
Public Works
Regional Mobility Panel
Sports and Entertainment Com.
Taxi Commission
Telephone Directory
University of DC
Water and Sewer Administration
Youth Rehabilitation Services
Zoning Commission

Issues in DC Politics

Budget issues
DC Flag
DC General, PBC
Gun issues
Health issues
Housing initiatives
Mayor’s mansion
Public Benefit Corporation
Regional Mobility
Reservation 13
Tax Rev Comm
Term limits repeal
Voting rights, statehood
Williams’s Fundraising Scandals

Links

Organizations
Appleseed Center
Cardozo Shaw Neigh.Assoc.
Committee of 100
Fed of Citizens Assocs
League of Women Voters
Parents United
Shaw Coalition

Photos

Search

What Is DCWatch?

themail archives

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
1129 20th Street, NW, Suite 800
Washington, DC 20036

Prepared for:

Office of the Inspector General
717 14th Street NW
Washington, DC 20005

November 9, 1999

Mr. Charles Maddox
Office of the Inspector General
717 14th Street NW, 5th Floor
Washington, DC 20005

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,
MITCHELL & TITUS, LLP
Gregory M. Holloway
Managing Partner

A PRE-AUDIT REVIEW OF THE DISTRICT'S SYSTEM OF ACCOUNTING AND REPORTING (SOAR)

Introduction

Mitchell & 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.

Scope

We 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.

Background

In 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:

  1. eliminate the principal problems that exist with the current system and ensure that all financial management guidelines were adhered to,
  2. enable managers to more effectively and efficiently monitor and control financial resources, and
  3. produce timely, accurate, and reliable information needed to make more informed decisions.

District officials stated that SOAR is a state of the art, Y2K compliant, integrated financial management system that

  • Enables accurate, accessible, automated information on budget, accounting, fixed assets/inventory, reporting aspects,
  • Supports fiscal accountability (and customer service) for all District program and financial managers,
  • Improves overall financial management performance, ease of use, and customer satisfaction,
  • Facilitates multi-dimensional queries, ad hoc reporting, trend analysis, and projections,
  • Exchanges information between independent systems, and
  • Transforms the way financial business is conducted.

More specifically SOAR consists of the following:

Functional Architecture
SOAR

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

  • Posts transactions on-line in real-time,
  • Provides ability to save transactions for later batch postings, and
  • Provides electronic data transfer capability including interface processing with external applications.

ADPICS (Advanced Purchasing and Inventory Control System) is an integration with R*STARS for seamless processing of requisition/purchase transactions that

  • Provides immediate notification of budgetary control violations, and
  • Interfaces with Office of Procurement's OCP Express (Procurement System).

Performance Executive/EIS (Executive Information System) is a high level analysis tool for program and financial management that

  • Enables data modeling,
  • Analyses to create "what if" scenarios,
  • Establishes sound business practices, and
  • Offers user flexibility to generate ad hoc reports.

Performance Budgeting is a client/server based tool to develop agency budgets with MS Windows look and feel that

  • Formulates budgets from issuing instructions to the final submission to congress,
  • Develops capital and operating budgets,
  • Provides automated link to Office of Budget and Planning (OBP),
  • Performance budgeting supports scenario analysis,
  • Incorporates a spending plan, and
  • Interfaces directly with R*STARS.

The proposed schedule for SOAR implementation anticipated:

  1. pilots in five agencies beginning in February 1998,
  2. the accounting system to be implemented by October 1998, and
  3. District-wide implementation of all modules by February 1999.

Objective

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.

Methodology

We performed the following:

  • Reviewed system documentation provided on technical specifications and functionality
  • Reviewed transaction code profiles and the new chart of accounts
  • Reviewed problem logs
  • Reviewed the preliminary year end closing schedule
  • Reviewed chart of accounts mapping to CAFR line items
  • Reviewed conversion of beginning balances
  • Determined the extent of system interfaces to subsystems
  • Prepared and summarized results from user satisfaction surveys
  • Performed an evaluation of internal controls around the system
  • Reviewed test of transactions of system functionality
  • Reviewed cash balances and bank reconciliations
  • Reviewed and monitored District officials efforts to resolve suspense accounts generated from the conversion and post implementation operations
  • Reviewed expenditure processing
  • Reviewed cash receipts processing
  • Reviewed third party reports (GAO, OIG, etc.)
  • Interviewed various agency representatives

Results in Brief

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.

CAFR PREPARATION POSSIBLE. BUT SUBSTANTIAL RECONCILIATION WORK STILL LEFT

CAFR Preparation Possible

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 Process Is Not Completed

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,

Other Actions Necessary

Additional key actions that District officials will need to address to prepare a reliable CAFR:

  • The District must successfully execute a system close. As of the date of this report, a system close had not occurred. The year-end closing will be the first opportunity to test the District's ability to execute a system close.
  • Annual year-end reclass and adjustments were not a part of the interim closing, and thus, the District's ability to determine and record these entries timely was not tested.
  • The system's internal transaction accounts (IT/suspense accounts) need to be reconciled and zeroed out. For the March 31, 1999 preliminary close, nearly $600 million of discrepancies were awaiting resolution.
  • The system clearing accounts should also be reconciled and cleared. The accounts do not equal zero. Contributing to the out-of-balance condition is the ability of agencies to make one- sided entries as checks or balances do not exist to ensure that other side of entry is made.
  • Cash and investment reconciliations are not performed timely. The cash process, in particular, continues to require a great deal- of manual effort to identify and record amount to be reconciled. Approximately $500 million in reconciling cash items existed at 3/31/99, excluding the main custodial account.
  • While many policies and procedures have been deployed, district-wide policies and procedures have not been fully developed for the modifications needed associated with the implementation of SOAR.
  • Indirect costs allocations have not been developed or implemented.
  • Payroll labor distribution has not been finalized.
  • District personnel are not familiar with modifying standard SOAR reports to make them more useful in the financial management and verification process.
  • Revenue budget information is incomplete in the SOAR system.
  • Agency personnel have the ability to make modifications and changes in the system after approval has been made by the oversight department-Office of Financial Operations and Systems (OFOS).

SOAR IS NOT FULLY FUNCTIONAL

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.

Under Utilization of SOAR System

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.]

Disjointed Subsystems

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.]

Conclusion

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.

Back to top of page


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

RESPONSE TO A PRE-AUDIT REVIEW OF THE DISTRICTS SYSTEM OF ACCOUNTING AND REPORTING

SUMMARY

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:

CAFR PREPARATION POSSIBLE

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.

CONVERSION PROCESS NOT COMPLETED

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.

OTHER ACTIONS NECESSARY

  • A system close through accounting period 12 was successfully executed on October 19, 1999. Reports to support this execution are available.
  • Reclassifications and adjustments were not included in the accounting period twelve close. However, we are currently identifying and organizing our year-end reclassifications and adjusting journal entries so that we are able to perform this task with a high degree of assurance that the requirements are complete.
  • We have been working diligently to clear all balances for FY 1999 residing in the system's internal transaction suspense file. The completion date for this activity is November 9, 1999.
  • The reconciliation and clearing of transactions that are the result of one-sided entries are scheduled to be completed by November 30, 1999. We are pursuing the implementation of a modification to prevent occurrences of transactions that result in out-of-balance amounts.
  • Cash and investment reconciliations through July, 1999 have been completed and we are aggressively working to complete September's reconciliation by December 15, 1999.
  • District-wide policies and procedures for the financial systems are being modified and revised on an on-going basis.
  • Payroll labor distribution is not finalized. We are continuing to refine its implementation as we identify inaccuracies. We are working with a sense of urgency to correct all deficiencies.
  • User support for all modules of the SOAR system is being reexamined to identify and determine deficiencies and voids that require corrective action. The significance and importance of competency issues relating to the utmost optimization of the SOAR are at the forefront of our endeavors in completing the implementation of the system.
  • A modification to prevent modifications and changes in the system to transactions after approval of the transactions was implemented on September 30, 1999.

SOAR IS NOT FULLY FUNCTIONAL

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.

DISJOINTED SUBSYSTEMS

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.

Back to top of page


Send mail with questions or comments to webmaster@dcwatch.com
Web site copyright ©DCWatch (ISSN 1546-4296)