Loading...
HomeMy WebLinkAboutIR 8356 INFORMAL REPORT TO CITY COUNCIL MEMBERS No. 8356 October 2, 2001 To the Mayor and Members of the City Council Y073 SUBJECT: MANAGEMENT LETTER COMMENTS Each year, in conjunction with the annual -financial audit, the City's external auditor (KPMG) notes certain matters involving the City's internal control structure they consider to be "reportable conditions" under standards established by the American Institute of Certified Public Accountants. "Reportable Conditions" are matters that, in the judgment of the auditor, relate to significant deficiencies in the design or operation of internal controls and could adversely affect the City's ability to record, process, summarize, and report financial data in the City's general purpose financial statements. According to the auditor, a "material weakness" is a reportable condition in which the design or operation of one or more internal control components does not reduce to a relatively low level the risk that error or fraud in amounts that would be material (e.g., 5% to 10% of a grant program or fund) in relation to the general purpose financial statements being audited may occur and not be detected within a timely manner by employees in the normal course of performing their assigned functions. Reportable conditions noted during the audit, as well as lesser "additional items", are discussed with members of management, and a Management Letter is provided to the City Council — typically in March - with auditor's recommendations and management responses. The Governmental Relations Committee reviews in detail the recommendations and responses and discusses the Management Letter findings with the staff. Attached are the Management Letter for FY2000, an Expanded Response on two items, and a mid-year Status Report on Recommendations. The Management Letter and staff responses and updates have been discussed with the Governmental Relations Committee on -three occasions since March. The Management Letter includes one "Material Weakness" which is strongly disputed by staff in the attached responses, two "Reportable Conditions", and 14 "Additional Items". Of the 17 recommendations, 12 have been fully implemented and the conditions "fixed". Five of the recommendations are in process of implementation and are "mostly fixed". Also attached is an IOC from the Finance Director which summarizes Management Letter comments over the past 11 years. There have been two "Material Weaknesses" over this period, one of which, as noted above, staff considers a non-issue. The number of"Reportable Conditions" has ranged from a high of 27 in FY1 992 to a low of 1 in FY1 998. Excluding the 00 high and low years, the average number of"Reportable Conditions" in a year is 5. ISSUED BY THE CITY MANAGER FORT WORTH, TEXAS INFORMAL REPORT TO CITY COUNCIL MEMBERS NO. 8356 October 2, 2001 0 To the Mayor and Members of the City Council PIP x 19 SUBJECT: MANAGEMENT LETTER COMMENTS If you have any questions about Management Letter comments, feel free to contact Charles Boswell or Jim Keyes. gGary W. Jackson City Manager ISSUED BY THE CITY MANAGER FORT WORTH, TEXAS CITY OF FORT WORTH, TEXAS Comments and Recommendations Regarding Internal Accounting Control and Operating Procedures September 30, 2000 Expanded Response April 25, 2001 Status Report on Recommendations July 31, 2001 January 19,2001 The Honorable Members of City Council City of Fort Worth,Texas: We have audited the general purpose financial statements of the City of Fort Worth, Texas (the City), for the year ended September 30, 2000, and have issued our report thereon dated January 19, 2001. In planning and performing our audit of the general purpose financial statements of the City, we considered its internal control structure in order to determine our auditing procedures for the purpose of expressing our opinion on the general purpose financial statements and not to provide assurance on the internal control structure. The maintenance of adequate internal control designed to fulfill control objectives is the responsibility of management. Because of inherent limitations in any internal control structure, errors, irregularities, or instances of noncompliance may nevertheless occur and not be detected. Also, controls found to be functioning at a point in time may later be found deficient because of the performance of those responsible for applying them, and there can be no assurance that controls currently in existence will prove to be adequate in the future as changes take place in the organization. We noted certain matters involving the internal control structure and its operation that we consider to be a reportable conditions under standards established by the American Institute of Certified Public Accountants. Reportable conditions are matters coming to our attention that, In our judgment, relate to significant deficiencies in the design or operation of internal control and could adversely affect the City's ability to record, process, summarize, and report financial data consistent with the assertions of management in the general purpose financial statements. Our consideration of the internal control structure would not necessarily disclose all matters in the internal control structure that might be reportable conditions. A material weakness is a reportable condition in which the design or operation of one or more internal control components does not reduce to a relatively low level the risk that errors or fraud in amounts that would be material in relation to the general purpose financial statements being audited may occur and not be detected within a timely period by employers in the normal course of performing their assigned functions. The matter involving internal control and its operation that we consider to be a material weakness is summarized in Exhibit 1. The reportable conditions noted during our audit, which have been discussed with the appropriate members of management, are summarized in Exhibit 11. Although not considered to be reportable conditions, we also noted additional items during our audit which we would like to bring to your attention. These other issues are summarized in Exhibit III. The Honorable Members of City Council City of Fort Worth, Texas January 19,2001 Page 2 These conditions were considered in determining the nature, timing and extent of the audit tests applied in our audit of the 2000 general purpose financial statements, and this report does not affect our report on these general purpose financial statements dated January 19, 2001. We have not considered internal control since the date of our report. This report is intended for the information of the mayor and city council members, management, others within the organization and officials of the appropriate federal and state agencies. However, this report is a matter of public record and its distribution is not limited. Very truly yours, [Original signed by KPMG LLP] The Honorable Members uf City Council OP"' City of Fort Worth, Texas � January l9, 20Ol Page Exhibit I-Material Weakness We consider the matter described below, involving the internal control stnicture and its operations, to be a material weakness. Local Law Enforcement Grant Program The City does not adequately monitor its oubrcoipicotu. The )uok of monitoring resulted in the following conditions: • The Program's two suhrccipico{s nubnnd0od invoices requesting acnomoto in excess of actual expenditures submitted to the City. The City reimbursed the requested amounts, therefore the excess amount over expenditures represents unsupported costs. • Subreoipicutodid not deposit advances made bythe City in interest bearing accounts. • The City's coob-uoto with the aubrecipieob du not identify all federal award imtbrmodno required and compliance requirements that must be followed by the subrecipients. • The City does not appear to monitor the financial activities ofmuhreoipieuto to provide ccauooub}e usmucuocc that the federal unundo are administered in ounop\iuuoc with federal requirements. • The City does not review the reports on required audits performed on the uuhrcoipieutu and does not monitor the uuhrociyiooto` corrective action no any audit findin-gs. During our testing of ezpeodiboua` our review ofsubruoiyicut invoices revealed that invoices from the xuhreoiyinnto indicated an amount requested for payment which differed from the expenses documented on the invoices uaexpended. This difference between the requested amount and the expended amount mothe invoice represents the unsupported amount of payments and is considered an advance of federal funds. Recommendation: Tbe City uboo)d establish policies and procedures for monitoring its ouhreoipieuts and for reviewing and approving payments to subrecipients for only amounts supported by actual expenditures. Response: The City added the two I.uoul Law Enforcement Grant yubrcuipinoiuto its monitoring list inthe aunzoucrof 2000. The City went out to each uf these sub/eoipicuto in September 2000 and reviewed expenditures through August 3l, 2000. The City did not find any reportable conditions in its monitoring visits other than the fact that one nfthe mubrocipicn1s had not auhndUcdmonthly expenditure reports since August uf 1999 and did receive funds in advance of expenditures. The City will continue 10 000doo1 monitoring reviews of these two muhrcoipieuta annually. The Honorable Members of City Council rFll City of Fort Worth, Texas January 19, 2001 Page 4 Our review performed by the City's Internal Audit Department also showed that one of the other subrecipients did receive amounts other than the expenses shown on the monthly expenditure reports. However, during the period reviewed (October 1999 through August 2000) it is important to understand that the City did not provide any funds to this subrecipient until February 15, 2000, although the amount paid was for the amount requested which exceeded the expenses reported. The City will review its process for approving payments to subrecipients. We do agree that the City's contracts with subrecipients should specify that any advance funds be kept in interest bearing accounts with any interest earned to be classed as program income and expended in accordance with grant guidelines, Expanded Response: April 25,2001 —See Attachment Status as of July 31,2001: The subrecipient for the Comin' Up program requested and received funds in advance due to the large amount of monthly expenditures which created a severe drain on the agency's finances. 'ne total amount of funds requested and paid does match the actual expenditures at the end of the year. The subrecipient that did not submit reports was the Adult Drug Treatment Court (DIRECT). These reports had been put into abeyance because the agency had exhausted all funds from the Local Law Enforcement Grant and its state grant and had a limited amount of judicial discretionary funds remaining. This was rectified as soon as the Auditor's Office was named as"receiver"by the County. The programs of the subrecipients are monitored by representatives of the Police Department and the Parks and Community Services Department. An annual review of these two programs has already been completed for this year. Exhibit H—Reportable Conditions We consider the matters described below, involving the internal control structure and its operations, to be reportable conditions: Financial Reporting(Continuing Comment) The preparation of a comprehensive annual financial report (CATR) is an enormous task which requires numerous adjustments to the City's general ledger to convert the general ledger from a budget or cash basis to a basis in conformity with generally accepted accounting principles (GAAP). Recent turnover and various reconciliations were not reconciled on a timely basis which contributes to the department of finance experiencing difficulty in preparing accurate and timely GAAP financial statements. During the course of our audit, we proposed in excess of fifty adjustments necessary to present the general purpose financial statements in conformity with GAAP. Significant audit entries made were necessary to reconcile transfers in and out and due to and from other funds, property tax and hoteUmotel tax receivables, accrued compensation payable, claims and premiums expenditures/payables, deferred revenues, accounts payables, proceeds from and purchases of equipment related to fire equipment notes payable, street improvement contributions receivable, special revenue fund and municipal airport fund federal grant transactions and depreciation on federal government capital contributions. The Honorable Members of City Council City of Fort Worth, Texas January 19, 2001 Page 5 Significant balances in the City's general ledger not reconciled to subledgers and other supporting records on a timely basis included cash and investments and capital outlays to the current year fixed assets additions. The accurate and timely preparation of the general purpose financial statements is essential to the timely completion of the City's CAFR. Recommendation: We recommend that management review the current process used to close the City's accounting records and to prepare appropriate financial statements and related schedules necessary to meet the City's reporting requirements. This process should provide for timely reconciliations, review of the various trial balances, and overall analytical review of the financial statements. Also, we recommend that staffing levels be reviewed to determine whether the City has the appropriate number of personnel in place to provide for accurate and timely closing of the City's accounting records and preparation of annual financial statements. Response: Concur. Accounting will review the current process and implement procedures to ensure timely and accurate financial statements and schedules in the future. A complete, timely set of prior year audit schedules and adjustments will be crucial in the accomplishment of this recommendation. Status as of July 31, 2001: Accounting has completed 50% of the FY2000 audit adjustments and is scheduled to complete the rest of the audit adjustments and reconcile all fund balances by August 31, 2001. The process used for FY2000's audit has been reviewed and adjusted to provide for more timely reconciliations, reviews of trial balances, and overall analytical review of the financial statements. The Finance Department did submit an improvement package for an additional Senior Accountant position in the FY2002 budget to address the financial statement impact of GASB 34. General Fixed Asset Account Group (Continuing Comment) The amounts recorded in the general ledger, general fixed assets account group, the Municipal Parking Fund and the Airport Enterprise Fund, continue to disagree with the fixed asset detail provided by the fixed asset tracking system(FATS)_ The discrepancy is primarily due to capitalized construction projects not being recorded into FATS until the project is complete. Recommendation: We recommend the City reconcile the FATS detail to the general ledger at least on a quarterly basis. Any differences should be investigated and resolved. Furthermore, we recommend the City review its policy of retaining completed projects as construction-in-process rather than recording as an asset in FATS. Accomplishing this will result in improved safeguarding of fixed assets and improved overall accountability. The Honorable Members of City Council City of Fort Worth, Texas January 19, 2001 Page 6 The importance of completing the reconciliation and identifying all differences will take an even greater importance in the near future, as GASB Statement 34 is implemented, which will require depreciation being recorded for all general fixed assets. Response: Concur. A Senior Accountant has been approved as an "overage" position to begin to address this issue. This position will do the following: • Reconcile the detail recorded in FATS to all completed projects on the general ledger. • Close all completed,reconciled projects on the general ledger. • Record asset detail in FATS for all projects more than 90% complete. Status as of July 31,2001: A Senior Accountant was hired in June as an overage position and has reconciled FATS to the general ledger for two of the capital improvement funds. This position is working with other Accounting personnel to close all completed projects in the capital improvement funds and record the asset detail of any project more than 90% complete. The success of this project is dependent on retaining the Senior Accountant position which was submitted as an improvement package for the FY2002 budget year. Exhibit III—Other Matters Although the following items are not regarded as reportable conditions, we believe our recommendations will strengthen internal and operational controls and improve financial reporting: Outstanding Checks During the performance of procedures to test the cash balances, we noted that the City's outstanding check list generated by the City's systems, included many old checks. In fact, certain checks were dated as far back as 1993. In addition, KPMG found one check written for $399,620 dated November 22, 1999, that was erroneously included on the outstanding check list even though this check had actually cleared the bank in November 1999. These problems are attributable to the fact that the City does not have effective policies and procedures in place regarding the cash reconciliation process. Recommendation: We recommend that the City research all checks on the outstanding check list that are over six months old to obtain a more accurate listing of outstanding checks and consider writing off old stale items. Also, we recommend the City consider establishing the practice of voiding all checks that have been outstanding for six months. Response: Concur. Accounting is currently researching all checks on the outstanding check list to ensure an accurate listing of outstanding checks. The procedures for the handling of outstanding checks over six months old will be reviewed. The Honorable Members of City Council City of Fort Worth,Texas January 19, 2001 Page 7 Status as of JuIy 31, 2001: Accounting has researched all of the checks on the outstanding check list and is in the process of voiding all checks over six months old. Information must be maintained on all voided checks over six months old for unclaimed property reporting. Separate data files must be maintained on information on checks over $100 (which must be remitted to the State after three years as unclaimed) and checks for $100 or less (which must be retained in a reserve by the City as unclaimed property). Bank Reconciliation We noted that the City has not been reconciling its cash on the general ledger to the bank balance correctly. Also, the City has made numerous errors when posting cash transactions to the clearing accounts(either double posting entries or making one sided entries) due to a lack of training. These errors result in the general ledger being unreliable for bank reconciliation purposes. The combination of these problems over time has resulted in large differences on the bank reconciliation. Recommendation: We recommend that the City develop written policies and procedures and distribute them to all the- employees involved in the bank reconciliation process. These employees should receive training related to their specific duties during the cash reconciliation process. Response: Concur. Written policies and procedures will be distributed as needed, but employees will for sure get the training and supervisory direction needed to perform these functions correctly. Status as of July 31, 2001: The incumbent employees have received training and one new employee is currently being trained on the bank reconciliation procedures. The bank reconciliation format has been revised to make it easier to read and comprehend. Investments The Investment Portfolio system used by the Treasurer does not agree to the general ledger system (MARS) and is not reconciled on a regular basis. Proper reconciliation each month would facilitate the year-end reconciliation between the general ledger and the Investment system and would certainly aid in correctly reporting cash and cash equivalents in the comprehensive annual financial report. Recommendation: We recommend that the Investment Portfolio system be reconciled to the General Ledger system on a monthly basis. The cost, discount and premium, book value, and unrealized gain or loss should be specifically reconciled and any differences resolved on a monthly basis. Response: Concur. The Investment Portfolio system and the General Ledger system have been reconciled and will continue to be reconciled on a monthly basis. The Honorable Members of City Council City of Fort Worth, Texas January 19, 2001 Page 8 Status as of July 31, 2001: The Investment Portfolio system and the General Ledger system continue to be reconciled on a monthly basis. Investment Portfolio System The Investment Portfolio system does not report accurate market value information when the month-end falls on a weekend. In this instance, market values revert to the book value. As a result, in 2000, the Treasurer needed to prepare a schedule manually using the book value of the investments on Saturday, September 30, 2000 and the market value on Friday, September 29, 2000, so that the correct amortizations and market values were reported. An investment system that accurately reports market values even on weekends would reduce the time involved to report to accounting the essential investment information such as cost, book value, market value, and unrealized gain and loss since it would all be detailed on a single report. A single report would also reduce the chance of entry errors inherent in the preparation of manual reports, Recommendation: We recommend that the City consider modifying the Treasurer's Investment Portfolio system to allow the system to report the market values as of the last day of the month even if it falls on a weekend. This would allow for the Treasurer to systematically produce one report which has the correct book value, market value, and unrealized gain or loss information. Response: Concur. The vendor has supplied an upgrade to the software that resolves this problem and the upgrade has been implemented. Status as of July 31, 2001: The upgrade of the software has been implemented and the problem resolved. Payroll.Processing During our review of internal control procedures over payroll processing, we noted an exception which resulted from insufficient balancing procedures. An error in the processing of FLEX 125 earnings adjustments caused a difference between payments and net pay on the GENESYS Super Company Report. The error was immaterial to the City's payroll as a whole; however, it did result in certain employees being underpaid. This error may have been detected if the payroll process was verified by performing a reconciliation between the systems and agreeing report totals. A reconciliation between the systems and reports provides a means of verifying that the systems are processing. payroll data completely and accurately and that the system interfaces are operating properly. Recommendation: We recommend that all payroll report totals be verified and reconciled for each of the City's payroll processing systems. P-timer system hours should be balanced to the GENESYS system to verify that hours The Honorable Members of City Council �— City �� Worth, ~ January |9` 20Ol Page were correctly transferred between systems. GBNESY3 dollar amounts should hcreconciled &n the Labor Cost File Report 10 verify that payroll iu posted tothe general ledger correctly. GENB3Y8 system net pay should be balanced to payments to employees and the number of checks paid. Response: Concur. The FLEX 125 adjustment in the temporary employee poygrnup occurred because temporary employees do not generally have insurance deducted from their checks and FLEX 125 was not set up for thatpuy87oup. FLEX 125 has been noi up in all pay@roups and all payroll report totals are now being verified. Status muwf July 31, 2001: Flex 125 has been set up in all paygroups, and all payroll report totals are being verified. Alliance Airport The City could not locate a copy of one reimbursement request that was submitted to the Federal Aviation Administration io February 2O00 through the ECHO system. Recommendation: VVo recommend the City retain copies of all appropriate fiounoiu|reports and supporting documentation. Response: The City will request a copy of the report submitted from the FAA and will ensure that copies of all federal fiououia) reports are retained in the future. Status awof July 3l, %OUl: /\ocouutinp,has obtained u copy of the missing report and ia retaining u copy of each financial report and supporting documentation. Alliance Airport—High Speed The udmioiabudvm assistant prepares the reimbursement requests that are submitted to the FAA. [nuo prepared, the reports are mcot to the Grant Accountant for electronic ouhodsaiun to the FAA through their ]8CI{[} system. nc Aviation Department of the City does not have a 000bo| in place to have the reimbursement reports reviewed and approved before they are submitted to the FAA. They only require that the fiou) reimbursement requests be reviewed and approved by the Airport Director. The reimbursement reports prepared by the administrative assistant were not reviewed by anyone before they were sent to the Grant Accountant for submission to the FAA. Recommendation: ne City should establish controls that will assure that all reimbursement requests are reviewed and approved for accuracy and compliance with federal requirements. The Honorable Members of City Council City of Fort Worth,Texas c January 19, 2001 Page 10 Response: Future reimbursement requests will be submitted based on expenditures shown on the City's monthly financial reports. The reimbursement request will be reviewed for accuracy by the Finance Department before being submitted to the FAA. Status as of July 31,2001: Reimbursements are being requested based on the City's monthly financial reports and are reviewed for accuracy by the Finance Department before being submitted to the FAA. Community Development Block Grant During the test of controls, we noted that there was not sufficient documentation of controls to ensure that the requirements of the Davis-Bacon Act were being monitored by the City. The City's lack of documentation or review could imply that they are not in compliance with the grant requirements. The contract compliance specialist was aware that he was to review the payroll reports and clear up any discrepancies, but was not aware that he had to document his review of the reports on the actual report. Recommendation: We recommend the City create a checklist to be filled out with every payroll report reviewed in order to ` document that the City has reviewed the reports and is in compliance with the Davis-Bacon Act requirements. Response: Concur. The contract compliance specialist reviewed the report but did not document the review. A checklist has been created to document the review of each payroll. Status as of July 31,2001: Each payroll is being reviewed and documented by the contract compliance specialist. Internal Service Funds KPMG noted that current year net losses increased the deficit retained earnings in the following internal services funds: • Equipment Services • Temporary Labor • Information Systems • Engineering Services These fimds are to account for the financing of goods or services provided by one department to other departments of the City on a cost reimbursement basis. Failure to provide these services on a cost reimbursement basis will more than likely result in the deficit being covered by the general fund. The Honorable Members of City Council City of Fort Worth,Texas January 19,2001 Page 11 Recommendation: We recommend the City evaluate the fees for services currently being charged and develop a plan to reduce the deficit retained earnings. Response: Concur. The Engineering Services Fund and the Equipment Services Fund are in the process of evaluating the fee structure for each fund. The Information Systems concluded a study of their fees in fiscal year 2000 and the new fees will be implemented in the upcoming budget year. Recovery of the deficit retained earnings in these funds is expected to occur over a period of three to five years. The City will review the Temporary Labor Fund to develop a plan to reduce the deficit retained earnings in that fund. Expanded Response: April 25,2001 —See Attachment Status as of July 31,2001: A comprehensive study of the Equipment Services Fund is being conducted at this time by Transportation Consultants, Inc. and will include a financial recovery plan. This study and a consultant-led implementation of recommendations are projected to conclude later in the year. The Engineering Fund study has been placed on hold,pending the replacement of the Assistant Finance Director who will manage the study. The Information Systems Fund concluded a study of its fees last fiscal year and will implement � new fees for FY2002. The Temporary Labor Fund staff reviewed their fee structure during the budget formation, and a savings is projected based on the current rate, which will help reduce the current deficit in the fund. Information Technology(IT) Policies and Procedures The City does not have complete documented system administration and I'I' operating policies and procedures, steps for appropriate system operations, and designated personnel responsible for completion. Lack of fully documented system administration/operating procedures creates the risk of incomplete, inadequate or unauthorized processing or modification of data. Recommendation: We recommend that the City designate individuals within the IT group to prepare formal written policies and procedures for all key activities within the area of functional technical operations to ensure complete and accurate system processing, as well as for all key control activities for access administration, including: • Security administration organization structure and responsibilities • Assignment of data owners and administrators • Creation and maintenance of user Ids • Administration of user access privileges • Creation and maintenance of ad-hoc queries and reports • Security monitoring and incident escalation • Physical access&environmental control r The Honorable Members of City Council City of Fort Worth, Texas January 19, 2001 Page 12 Response: City of Fort Worth Administrative Regulation D-5 (AR D-5) was issued in July 1999 and established formal security policies that address security responsibilities for data owners at department level, as well as other responsibilities such as granting access to data files and isolating users according to their level of need. User identification and password authentication are currently used to control individual user access to the City network and/or mainframe computer applications. AR D-5 also establishes the policy for access to Information Technology (IT) resources and that adequate physical security controls must be provided for IT resources. IT computer room access is controlled by an electronic locking key card system and that access is authorized, as needed, by the IT Solutions Director. An access list, including access level, is maintained by the City Services Department for all CFW employees and vendors. An IT Security Manager was hired in July 2000 and is in the process of implementing the security policy and procedures of the security regulation; conducting compliance audits; and investigating computer security incidents. Status as of July 31, 2001: • Data owners were notified, by letter, of their responsibilities in backing up their data or requesting the IT Solutions Department perform the back-up operations. Data owners were also requested to identify critical applications required for disaster recovery and business continuity operations. Negotiations are under way with a "hot site" vendor to load those programs on removable hard drives so that disaster recovery/business continuity operations can more easily be implemented. • The Security Manager has improved access control to the computer room and other sensitive locations. Local records checks are being conducted for all contractor/vendor personnel requiring physical access to the City of Fort Worth(CFW) computer room and other sensitive locations such as the communications facility, backup Public Safety Answering Point (PSAP), Emergency Operations Center, and related equipment rooms. Contractor personnel are escorted and observed by CFW technicians until the contractor personnel are cleared for CFW ID cards. • Password security has been increased so that users must change their passwords on a more frequent basis than was required at the time of this audit. • The IT Security Manager investigates computer security incidents and other security violations as those incidents occur. The IT Solutions Network Group provides technical support, as needed. • Projects are underway to install Internet content filtering software and an Intrusion Detection system to detect hacker/unauthorized persons attempting to gain access to CFW IT resources. Change Management KPMG noted that City programmers routinely access the production environment to execute program changes. If an untested program change is made in the production environment, routine transactions could be processed inaccurately and errors could result that affect management's assertions regarding financial statements. This presents a control risk to the processing of current and future financial transactions. Recommendation: We recommend the City consider restricting programmers from production environments, and instructing someone, other than programmers, to move approved and tested changes into production. If this is not possible, then appropriate monitoring procedures should be established to monitor all changes made to the production environment. The Honorable Members of City Council Or r City of Fort Worth, Texas January 19, 2001 Page 13 Response: The Information Technology Solutions Employee Handbook states the project management process, but will be updated to address the procedures for processing program changes and assigning responsibilities for testing and documenting the changes. Programming standards are also stated in the Employee Handbook in the Software Development Policy Standards Section. Appropriate action will be implemented to enforce the separation of duties/functions, as well as ensuring application changes are developed and tested outside of the actual application. Status as of July 31,2001: Internal Audit is currently auditing IT Solutions' Change Management processes. When the audit is completed, IT Solutions will evaluate the recommendations and take appropriate action. Backup & Recovery The backup storage location for the primary Fort Worth IT resources is located approximately one mile from the Municipal building, which house the IT department. In addition, the AS400 on which utility data is maintained has a separate backup storage location approximately two miles from its operations building. Lack of an adequate backup procedure including prudent location of backup storage facilities creates the risk of full or partial loss of critical data and related IT components without any means of recovery. A formalized disaster recovery plan (DRP) has not been updated since December 1999 for either the primary Fort Worth IT facility or the utility water billing IT facility, housed across the street from the primary facility and housed on its own AS400 at the primary facility. If a disaster occurs that renders the City's systems unusable, significant downtime for the City's operations could result. This could affect the ability to process payroll for employees or meet the processing needs of the City's customers. Recommendation: We recommend the City consider selecting a backup site located a minimum of five miles from its premises to further protect its critical data in the event of a disaster. Also, we recommend the City consider undertaking the effort to develop a disaster recovery plan, and it should be maintained and tested for all critical IT resources. To ensure that the DRP addresses all critical IT resources, we recommend performing a comprehensive, enterprise-wide assessment of the business impact of information systems and critical business functions being unavailable for an extended period of time. The DRP should be enhanced and integrated to address threats and vulnerabilities, recovery requirements and procedures for all IT resources and business functions prioritized by the level of criticality represented by those operations. Training for recovery teams and user organizations should be conducted, the plan should be kept current and tested regularly. In addition, we recommend the City develop and document a comprehensive disaster recovery and business continuity plan that includes procedures for recovering the IT infrastructure, as well as overall business activities of the City. The Honorable Members of City Council City of Fort Worth, Texas January 19, 2001 Page 14 Response: The potential problem of the close proximity of backup data to City Hall is acknowledged and appropriate action has been initiated to relocate backup data to a suitable location that has already been identified. Status as of July 31,2001: • A "hot site" contractor has been identified and negotiations for their services are being conducted. This site is located within the CFW city limits and is 20 miles away from City Hall. The backup tape library will be stored in fire and waterproof storage containers at the "hot site"and will be protected by fire and burglar alarms. • As mentioned previously, data owners were notified, by letter, of their responsibilities in backing up their data or requesting the IT Solutions Department perform the back-up operations. Data owners were also requested to identify critical applications required for disaster recovery and business continuity operations in addition to any commercial/stand alone programs required for operations. • Plans include loading critical programs on removable hard drives that will be stored at the "hot site" so that time will be saved by not having to manually load programs and disaster recoveryibusiness continuity operations can be implemented faster. • DRP testing will be conducted as soon as the "hot site" contract is finalized. �J Water Billing Data Interface KPMG noted that while a report summarizing total number of billing records uploaded from bill usage handheld computers to the water utility billing system is generated after each upload, no review and comparison of those uploaded records is executed by water billing personnel. In addition, KPMG noted that while billing department personnel review and reconcile total billing transaction data uploaded from the billing system to the general ledger application, they do not currently review posted data at the individual account level. Lack of adequate review and reconciliation of subsystem feeds to primary transaction and reporting systems creates a risk of incomplete or inaccurate financial information on which strategic and business decisions can be based. Recommendation: We recommend the City designate individuals within the utility billing group to review all critical data interface reports in order to verify that all business transaction activity has been completely and accurately uploaded and that any interface errors are reported,tracked and resolved. Response: Individuals within the utility billing group have been designated to ensure all critical data interface reports have been completely and accurately uploaded and that all interface errors are reported, tracked and resolved. Status as of July 31, 2001: Designated individuals within the utility billing group are verifying that all critical data interface reports are completely and accurately uploaded and that all interface errors are reported, tracked and resolved. The Honorable Members of City Council r/ City of Fort Worth, Texas January 19, 2001 Page 15 Employees' Retirement Fund Investment Portfolio KPMG noted that the investment structure of the portfolio of the Employees' Retirement Fund of the City of Fort Worth is not in compliance with the Statement of Objectives and Guidelines as noted by the Board of Directors. Recommendation: We recommend that the Retirement Office frequently monitor the investment structure and have the Trustee periodically report to the Board about compliance with the Statement of Objectives and Guidelines. Response: The performance measurement report has been modified to show the Domestic Equity Large Cap and Domestic Equity Mid Cap and Small Cap Allocations in order to be in compliance with the Statement of Investments Objectives and Guidelines. Status as of July 31, 2001: /� , The performance measurement report is being monitored on a quarterly basis to ensure compliance with the Statement of Objectives and Guidelines. rffrF The Honorable Members of City Council City of Fort Worth,Texas January 19, 2001 Page 1 EXPANDED RESPONSE APRIL,25, 2001 Exhibit I—Material Weakness We consider the matter described below, involving the internal control structure and its operations, to be a material weakness. Local Law Enforcement Grant Program The City does not adequately monitor its subrecipients. The lack of monitoring resulted in the following conditions: • The Program's two subrecipients submitted invoices requesting amounts in excess of actual expenditures submitted to the City. The City reimbursed the requested amounts, therefore the excess amount over expenditures represents unsupported costs. • Subrecipients did not deposit advances made by the City in interest bearing accounts. • The City's contracts with the subrecipients do not identify all federal award information required and compliance requirements that must be followed by the subrecipients. • The City does not appear to monitor the financial activities of subrecipients to provide reasonable assurance that the federal awards are administered in compliance with federal requirements. • The City does not review the reports on required audits performed on the subrecipients and does not monitor the subrecipients' corrective action on any audit findings. During our testing of expenditures, our review of subrecipient invoices revealed that invoices from the subrecipients indicated an amount requested for payment which differed from the expenses documented on the invoices as expended. This difference between the requested amount and the expended amount on the invoice represents the unsupported amount of payments and is considered an advance of federal funds. Recommendation: The City should establish policies and procedures for monitoring its subrecipients and for reviewing and approving payments to subrecipients for only amounts supported by actual expenditures. Response: The City added the two Local Law Enforcement Grant subrecipients to its monitoring list in the summer of 2000. The City went out to each of these subrecipients in September 2000 and reviewed expenditures through August 31, 2000. The City did not find any reportable conditions in its monitoring visits other than the fact that one of the subrecipients had not submitted monthly expenditure reports since August of 1999 and did receive ids in advance of expenditures. The City will continue to conduct monitoring reviews of these two subrecipients annually. The Honorable Members of City Council Oj City of Fort Worth,Texas January 19, 2001 Page 2 Our review performed by the City's Internal Audit Department also showed that one of the other subrecipients did receive amounts other than the expenses shown on the monthly expenditure reports. However, during the period reviewed (October 1999 through August 2000) it is important to understand that the City did not provide any funds to this subrecipient until February 15, 2000, although the amount paid was for the amount requested which exceeded the expenses reported. The City will review its process for approving payments to subrecipients. We do agree that the City's contracts with subrecipients should specify that any advance funds be kept in interest bearing accounts with any interest earned to be classed as program income and expended in accordance with grant guidelines. Response to Government Relations Committee: This finding is currently a non-issue. Normally a contractor is reimbursed after the expenditures have been incurred, but per City Secretary Contract No. 25344, the City did agree that the funding shall be made on an "on request basis in advance following receipt from the Contractor of an itemized statement of anticipated expenses". Expenditures for the contract. period were not reconciled by the time KPMG completed their field work but have since been reconciled to the total amount of the advances and no money is at risk. The City is committed to reconciling the detail statement of costs on a timely basis in the future. The total contract amount with the Boys & Girls Club of Greater Fort Worth was $1,068,035 per City Secretary Contract No. 25344. Total funds requested, expenditures and payment dates are as follows: For Month End Expenditures Funds Requested Date Paid 10/31/99 0.00 89,000.00 02/15/00 11/30/99 84,75 8.00 81,000.00 02/15/00 12/31/99 74,762.00 64,000.00 02/15/00 01/31/00 68,873.00 65,000.00 02/15/00 02/29/00 69,828.00 67,000.00 03/21/00 03/31/00 79,112.00 80,000.00 04/11/00 04/30/00 77,660.00 90,000.00 05/15/00 05/31/00 74,892.00 70,000.00 06/16/00 06/30/00 80,483.00 85,000.00 07/11/00 07/31/00 95,953.00 125,000.00 08/14/00 08/31/00 91,512.00 125,000.00 10/02/00 09/30/00 109,227.00 60,000.00 10/20/00 10/31/00 85,439.00 67,035.00 11/21/00 11/30/00 75,537.00 0.00 - $1,068,036.00 $1,068,035.00 The City's Internal Audit Department did review the monthly expenditures and funds request from the Boys&Girls Clubs of Greater Fort Worth for the period of October 1999 through August 2000. The Boys & Girls Clubs did receive amounts other than the expenses shown on the monthly expenditure reports in accordance with the aforementioned funding clause in the contract. However, the City did not provide The Honorable Members of City Council City of Fort Worth, Texas January 19,2001 Page 3 any funds to the Boys & Girls Club for the program period of October 1999 through January 2000 until February 15, 2000. Thus, in the opinion of the Internal Audit Department, the issue of advance funding did not apply during this period. Expenditure reports were submitted to the City for the total amount of funding received by the Boys &Girls Clubs. Tarrant County also received funds in advance of expenditures, However, the review performed by the Internal Audit Department did show that Tarrant County did earn interest on the funds and included that interest as program income and expended it for grant eligible activities. We do agree that the City's contracts with subrecipients should specify that any advance funds be kept in interest bearing accounts with any interest earned to be classed as program income and expended in accordance with grant guidelines. The Internal Audit Department is currently drafting procedural recommendations for the preceding grants. Internal Service Funds KPMG noted that current year net losses increased the deficit retained earnings in the following internal services funds: * Equipment Services a Temporary Labor 0 Information Systems • Engineering Services These funds are to account for the financing of goods or services provided by one department to other departments of the City on a cost reimbursement basis. Failure to provide these services on a cost reimbursement basis will more than likely result in the deficit being covered by the general fund. Recommendation: We recommend the City evaluate the fees for services currently being charged and develop a plan to reduce the deficit retained earnings. Response: Concur. The Engineering Services Fund and the Equipment Services Fund are in the process of evaluating the fee structure for each fund. The Information Systems concluded a study of their fees in fiscal year 2000 and the new fees will be implemented in the upcoming budget year. Recovery of the deficit retained earnings in these funds is expected to occur over a period of three to five years. The City will review the Temporary Labor Fund to develop a plan to reduce the deficit retained earnings in that fund. The Honorable Members of City Council City of Fort Worth,Texas January 19, 2001 Page 4 Response to Government Relations Committee: The deficit retained earnings in the four internal services funds may be best detailed starting at the beginning of fiscal year 1994 when all the funds had a positive retained earnings. (000's omitted) Equipment Temporary Information Engineering Services Labor Systems Services Retained Earnings, Beginning of FY 1994 $ 17 $ 117 $ 162 $ 0 Operating Income (Loss), FY 1994 (564) 12 (229) 209 FY 1995 572 24 (1,424) (7) FY 1996 (649) 17 (1,150) 329 FY 1997 (667) (41) (1,376) 134 FY 1998 (2,331) (17) (533) (385) FY 1999 (2,014) 16 (771) (296) FY 2000 (11,857) (60) 218 (670) Total Operating Income(Loss) (7,510) (49) (5,701) (686) Transfers In(Out), FY 1994 (52) 0 (150) (72) FY 1995 250 (58) 364 180 FY 1996 (1,011) (58) 670 (374) FY 1997 (36) (58) 725 (34) FY 1998 447 0 401 99 FY 1999 0 0 315 3 FY 2000 87 0 0 89 Total Transfers In(Out) (315) (174) 2,325 (109) Gain (Loss) on Sale of Property and Equipment FY 1994 0 0 (22) 0 FY 1995 97 0 (62) 25 FY 1996 9 0 (228) (60) FY 1997 (13) 0 (400) (11) FY 1998 (59) 0 (56) (69) FY 1999 1 0 (387) (18) FY 2000 4 0 325 (12) Total Gain(Loss) on Sale of Property and �" Equipment 39 0 (1,480) (145) The Honorable Members of City Council ' City of Fort Worth, Texas January 19,2001 Page 5 Nonoperating Revenues(Expenses) FY 1994 239 6 195 11 FY 1995 162 7 271 93 FY 1996 190 5 119 70 FY 1997 326 1 101 79 FY 1998 217 0 123 79 FY 1999 87 0 108 56 FY 2000 288 0 58 58 Total Nonoperating Revenue (Expenses) 1,509 18 975 446 Restatement of Retained Earnings Implementation of GASB 31 —FY 1998 5 0 0 3 Retained Earnings September 30, 2000 ($ 6,255) ($ 87) ($ 3 ,719) ($ 491) �r Included in the Operating Income (Loss) of each fund are two contributing expenses to the current retained deficit. These items are not budgeted but are required to convert from a cash basis to a GAAP basis. Equipment Temporary Information Engineering Services Labor Systems Services (000's omitted) Totals for FY 94 -00 Depreciation $ 2,933 $ 0 $ 3,457 $ 1,319 Compensated Absences 724 1 725 953 Total $ 3,657 $ 1 $ 4,182 $ 2,272 It is important to note that the implementation of GASB No. 34 will combine the Contributions and Retained Earnings (Deficit) in the Fund Equity of the Balance Sheet to become one number. If this change had been implemented at the end of FY 2000, the deficit in two of the funds would have been eliminated and the deficit in a third fund would have been greatly reduced. The before and after picture of the change GASB 34 would have on the Fund Equity as of September 30, 2000 is as follows: The Honorable Members of City Council City of Fort Worth,Texas January 19, 2001 Page 6 Equipment Temporary Information Engineering Services Labor Systems Services (000's omitted) Before GASB 34: Fund Equity: Contributions $ 10,210 $ 0 3,358 585 Retained Deficit (6,255) (87) (3,719) (491) Total Fund Equity $ 3,955 $ (87) $ (361) $ 94 After GASB 34: Total Fund Equity $ 3,955 $ (87) $ (361) 94 FORT WORTH INTER-OFFICE CORRESPONDENCE City of Fort Worth FINANCE DEPARTMENT TO: Charles Boswell, Assistant City Manager FROM: Jim Keyes, Finance Directo DATE: August 2, 2001 SUBJECT: MANAGEMENT LETTER COMMENTS Attached is a summary of the number of Management Letter comments over the past 11 years. The "material weakness" in 1993 related to a finding that the subsidiary ledger of accounts receivable for industrial waste charges and the environmental protection fee had not been maintained or reconciled to the general ledger for several years. This situation related only to the Water and Sewer Fund and was corrected at the time. The "material weakness" in 2000 related to a finding that the City did not adequately monitor its Local Law Enforcement Grant sub-recipients. As noted in my expanded response on April 25, the City contends this finding is a non-issue and disputes the nature of this "material weakness". -'' Also attached is a summary of the number of repeated Management Letter comments by year. A comment on Fixed Assets has been consistently repeated year-after-year. The manner in which general fixed assets are reported under the current accounting rules and the lack of staff in the Accounting Division made correcting these comments a low priority. However, the implementation of GASB 34 in the next year will require these findings to be finally and completely addressed. Attachments a k CIJ 2 } � a 0 o � � r � � 00 o � _ ® k � � � = w 2 \ / - § cr) o r m � � � CY) o 0 2 § « - ) � � § r a 2 j 3 � . a \ ® $ a / -j 0 / u \ � w & u n _ o Q & 7 \ \ 2 § \ \ o ® UJ C) - 2 2 \ j / c4 § C/) 0 f o ? 2 w w § . • . . � / $ k < � o L 9 h E e C E o 3 � 7 $ / / 0 N rn rn rn r r r a) Q�j tt) T ti to tr to If) C" r T N } } [0 N m Z r r T r r try W r O U K W 0) k T W Ir Z W N W r r N r r N r � r d Z d T T r T r T T T r � N N N � T T NWN it 'o 0) r r r r T r T fr T V1 C E to W a C p U L z a CD c ° � o c m U a y fUx C c W ° tD c U SD m d y N 0) y V) CL tD d U O d LL c ° ccoD o � m 1m Q y U) c d o v m to a c V) J y O Cl m rn E E c c m c ° O m c C d °c t x O LL LL d U C7 LY L� iL S LL F- ...................