The Honorable Mayor and Members of Council The Metropolitan Government of Nashville and Davidson County Nashville, Tennessee Ladies and Gentlemen: We have audited the financial statements of the governmental activities, the business-type activities, the aggregate discretely presented component units, each major fund, and the aggregate remaining fund information of the Metropolitan Government of Nashville and Davidson County, Tennessee (the Government) as of and for the year ended June 30, 2004, which collectively comprise the Government’s basic financial statements and have issued our report thereon dated October 31, 2004.
We also have audited the financial statements of each of the discretely presented component units of the Government, as of and for the year ended June 30, 2004, as displayed in the Government’s basic financial statements. We also have audited the financial statements of each of the Government’s nonmajor governmental, nonmajor enterprise, internal service, and fiduciary funds, as well as the financial statements of the Sports Authority Fund as of and for the year ended June 30, 2004.
We did not audit the financial statements of the following Component Units: the Nashville District Management Corporation, the Metropolitan Development and Housing Agency, the Electric Power Board, the Metropolitan Transit Authority, the Nashville Thermal Transfer Corporation, the Metropolitan Nashville Airport Authority, the Emergency Communications District, and the Industrial Development Board. In planning and performing our audit of the aforementioned financial statements, we considered internal control in order to determine our auditing procedures for the purpose of expressing our opinion on the financial statements.
An audit does not include examining the effectiveness of internal control and does not provide assurance on internal control. We have not considered internal control since the date of our report. During our audit we noted certain matters involving internal control and other operational matters that are presented for your consideration. These comments and recommendations, all of which have been discussed with the appropriate members of management, are intended to improve internal control or result in other operating efficiencies and are summarized in Appendix I.
Our audit procedures are designed primarily to enable us to form an opinion on the financial statements, and therefore may not bring to light all weaknesses in policies or procedures that may exist. We aim, however, to use our knowledge of the company’s organization gained during our work to make comments and suggestions that we hope will be useful to you. We would be pleased to discuss these comments and recommendations with you at any time. KPMG LLP, a U. S. limited liability partnership, is the U. S.
member firm of KPMG International, a Swiss cooperative. This report is intended solely for the information and use of the audit committee, management, and others within the organization and is not intended to be and should not be used by anyone other than these specified parties. Very truly yours, Appendix I RECOMMENDATIONS GENERAL GOVERNMENT TIMELINESS OF CAPITAL ASSETS PHYSICAL INVENTORY ADJUSTMENTS Observation Physical inventory listings of capital assets are being sent to departments to verify existence of all capital assets each fiscal year.
However, the responses received back from the various departments as to which assets have been disposed of, and the potential adjustments that are needed are not being adjusted and reflected in the fixed asset system on a timely basis. During fiscal year 2004, listings were sent out in January 2004, however as of the date of this letter, all adjustments to the fixed asset system still have not been made. Recommendation We suggest that Metro develop a detailed schedule that includes when each department’s physical inventory adjustments will be analyzed.
KPMG also suggests that potential adjustments are recorded within three months of when the inventory verification process began. Management Response We concur. During fiscal year 2005, we will outline a comprehensive plan which will detail when each department will have their physical inventory listings initially distributed, when each department must have these listings completed and returned, and a targeted date in which all adjustments will be finalized and entered into the fixed asset system. GENERAL GOVERNMENT METRO INVESTMENT POOL INCOME ALLOCATION
Observation During fiscal 2004, the Government made separate allocations of three investment earnings components: interest income, realized market gains/losses and unrealized market gains/losses to participants in the Metro Investment Pool on a monthly basis. In some months, the overall net investment earnings allocation was positive; in other months, the overall investment earnings allocation was negative. Prior to FY03 the allocation was made as a net of all three investment earning components and the pool kept a net asset value of $1. 00. Market values did not fluctuate as a result.
The portfolio was bifurcated in FY03 with one portfolio having a longer duration than the other two and all three portfolios were marked to market monthly. During FY03 there were no overall net negative investment earnings allocations. In FY04 the portfolios experienced negative investment returns and net negative investment earnings allocations for the first time as a result of market activity. Under the current methodology for allocation of income, all pool participants were subject to the same mathematical calculation, regardless of whether the participant was a net investor or net borrower.
In months when net investment earnings losses were incurred, the allocation performed resulted in “negative returns” being applied to borrowers’ “negative” balances, which resulted in credits, or income to their respective statements of changes in fund balances. While the mathematical calculations were accurate, the current methodology appears to be inequitable to investing or “positive” funds. Recommendation KPMG recommends that the allocation methodology be reviewed to develop a more equitable allocation to both investing and borrowing funds.
Management Response Management concurs with the recommendation to develop another investment earnings allocation method. A correcting entry to adjust FY04 has been prepared. A new allocation method has been developed and was implemented in November. BUSINESS SOLUTIONS PROGRAMMER ACCESS Observation One programmer has been granted command line access to the system so that she has access to make programming changes and migrate her own changes to the JD Edwards (JDE) production environment but cannot update production data in the system.
Recommendation Management should limit programmer access to the production environment when PeopleSoft Enterprise One v. 8. 9 (PeopleSoft Enterprise) is implemented so that programmers do not have access to make changes to code in the production environment. Management Response We concur. Developers in PeopleSoft Enterprise One 8. 9 will not have access or the ability to promote any programs to Production. There is an entire development life cycle for new code including system rules governing authority by role.
Developers are blocked from promoting anything to Production or updating production data. In FASTnet, programmers no longer have *ALL action security and the ability to promote code to production. BUSINESS SOLUTIONS APPROVAL OF USER ACCESS Observation Documented management approval is not required before a user is granted access to the JDE system. A user’s manager is notified after a user has been granted access to the system, but no documented response from the manager is required to authorize the individual’s access.
Recommendation Management should implement procedures requiring managers to provide a documented authorization of user access before users are granted access to JDE by the security administrator. Management Response We concur. The new Training Management Application in PeopleSoft Enterprise One 8. 9 will require management approval via workflow prior to security update. The current system does not provide this functionality. BUSINESS SOLUTIONS MONITORING OF POWERFUL USER ACTIVITY
Observation Monitoring of powerful user activity for individuals with powerful access to the systems (including programmers and administrators) is not currently performed for individuals with access to the JDE system. While management does have a process in place to identify terminated employees for removal from the system, periodic management reviews of access have not been implemented to increase the assurance that terminated or unauthorized access has been granted to JDE users.
Recommendation Management should implement controls, including a periodic review of access by managers, to monitor powerful user activities in the system to increase the assurance that only authorized activities are being performed by these individuals in JDE. Management Response We concur. Business Solutions just recently reviewed security for Business Solutions and Payroll users. We removed Human Resources master record access from the Central Payroll staff (excluding the two payroll processors).
Further controls are being implemented to prevent payroll processors from updating Human Resources master data manually outside the normal course of payroll processing. We also removed *ALL security from both the programmer and the HR/Benefits Business Analyst. PeopleSoft Enterprise One 8. 9 security will be setup by role level with standard Internal Audit approved access. Security will no longer be granted on an adhoc basis. We will perform no less than semi-annual reviews of user security levels with departmental contacts. BUSINESS SOLUTIONS APPROVAL OF SYSTEM CHANGES
Observation Minor system changes do not require approval before they are migrated to the production environment while the JDE security administrator approves major changes. Recommendation Management should implement a change management system in which a Lead Developer would review and approve all changes to the production environment. Management should also segregate duties so that the individual approving the change prior to the implementation to production is not the same individual that completed the change. Management Response We concur. Developers in PeopleSoft Enterprise One 8.
9 will not have access or the ability to promote any programs to Production. There is an entire development life cycle for new code including system rules governing authority by role. Developers are blocked from promoting anything to Production or updating production data. In FASTnet, programmers no longer have *ALL action security and the ability to promote code to production. GENERAL GOVERNMENT INFORMATION SYSTEMS CHANGE REQUEST FORMS Observation Metro ITS did not retain evidence of user testing for three out of the five ITS Change Request Forms inspected.
Also, management approvals were not retained for several changes prior to their implementation into the production environment. Recommendation Management should retain evidence of user testing and enforce their current policy to retain the authorization of management to migrate the change to the production environment prior to the implementation of the change in production. Management Response We concur. ITS is modifying the Change Request Form to require due diligence testing to confirm standard user functionality.
This change will be implemented in fiscal year 2005. GENERAL GOVERNMENT INFORMATION SYSTEMS AS/400 ACCESS Observation KPMG noted several AS/400 accounts that had never been used, while other accounts had access to the system, although they no longer required access. In addition, some users were allowed to sign into the system using concurrent sessions. Recommendation Management should implement procedures to identify users who no longer require access to the AS/400, and management should also limit AS/400 user access to only one session.
Management Response We concur. Unused and unneeded user accounts should be removed, and concurrent access should be disallowed by default. ITS is preparing a report of unused accounts for review by the software administrators. These changes will be implemented in fiscal year 2005. ITS believes concurrent session access is justified for a few administrative and development users who need to be able to sign back on as themselves in order to investigate the problems that may have caused their session to hang. GENERAL GOVERNMENT INFORMATION SYSTEMS AS/400 ACCESS
Observation Logging has not been implemented to track failed login attempts on one of the AS400 servers inspected. Also, procedures for monitoring audit logs on a periodic basis for the AS400 systems have not been implemented by management. Recommendation Management should implement logging for failed login attempts on their AS400 servers and implement procedures to periodically review audit logs for security violations and powerful user activity. This review should be documented and retained for reference purposes. Management Response We concur.
In fiscal year 2005, ITS will change the logs to track failed logins and implement procedures for their regular review. GENERAL GOVERNMENT INFORMATION SYSTEMS ASSESSOR AND TAX ACCOUNTING MAINFRAME ACCESS Observation Individuals with access to the Assessor’s and Tax Accounting mainframe systems are granted access through operator IDs or operator numbers. The access to specific functions within the system is permissible using operator IDs or operator numbers, but these functions have not been documented so that user access can be reviewed for reasonableness.
Access through the operator IDs or operator numbers has been established by hard code that was implemented within each mainframe program at the time the application was developed. For all ITS applications, change management procedures are the responsibility of the individual ITS development groups. For the Tax Accounting systems, documentation of the development manager’s approval to move a change to the production environment is retained, but no other documentation, including the change request, testing, etc. are retained for application changes to the Tax Accounting system.
Recommendation Management should document the access which has been assigned through operator IDs or operator numbers to determine whether access assigned to individuals is reasonable according to their job responsibilities, or management should migrate the Assessor and Tax Accounting applications off of the mainframe as soon as feasibly possible to a system that allows the administrator to evaluate the specific access granted to a user. Management should also implement standard change management procedures for all ITS applications and retain documentation related to the change management process.
Management Response We concur. ITS will comply with the above recommendation during fiscal year 2005. GENERAL GOVERNMENT INFORMATION SYSTEMS MONITORING OF SECURITY VIOLATIONS Observation Monitoring of security violations and powerful user activity is not currently performed by the Assessor’s information systems group for the AssessPro application or for the Windows 2000 server on which AssessPro runs. Although all users must be authorized by a manager before the Assessor’s information systems group will grant the user access to AssessPro, no evidence of these authorizations is retained.
Recommendation Management should implement procedures to monitor significant security violations and powerful user activity to increase the assurance that unauthorized access is not obtained to the AssessPro system. Management should retain authorizations for user access and periodically review them to increase the assurance that users are current and authorized for the access they have been granted. Management Response We concur. We have hired Patriot Properties to work on resolving the issue of monitoring significant security violations and powerful user activity.
In addition, we will implement the procedure recommended above regarding review of authorizations for user access during fiscal year 2005. METROPOLITAN NASHVILLE PUBLIC SCHOOLS PURCHASING CARD REVIEW PROCESS Observation Per the Metropolitan Nashville Public School (MNPS) policy regarding purchasing cards, purchases must comply with MNPS’ competition requirements and be within the limits authorized by the cardholder’s Department Head or Principal. When an individual is approved to receive a procurement card, certain criteria are determined based on the person’s position and business needs.
Also, it is the responsibility of supervisors (Principals, Department Heads) to access cardholder’s accounts online and approve all purchases. Cardholders may purchase unauthorized items that will go unnoticed without supervisor review and approval of all purchases. Recommendation All supervisors should be reviewing all purchases, as it is their responsibility per the MNPS purchasing card policy. Management Response We concur. The purchasing card admistrative team met to put together a strategy to promote on-line approval. This plan included
notifying princials and department managers of the importance of on-line approval and assigning a back up person for times when they cannot do this and communicating the monthly deadlines for performing this task. These changes will be implemented in fiscal year 2005. METROPOLITAN NASHVILLE PUBLIC SCHOOLS MAINTENANCE OF ORGANIZATION CHARTS Observation School organizational charts are prepared and sent out 3 times a year (usually in October, January, and May) to ascertain if there are any errors in the employee data contained in the system at MNPS.
Principals are required to compare the information presented on the organizational charts to the records held at the school and note any errors or discrepancies and also to sign off denoting approval. The organizational charts are then returned to the MNPS HR Director, and any discrepancies noted are supposed to be keyed into the system. In FY2004, KPMG noted that the approved organizational charts were not retained in HR, resulting in a lack of documentation for a key control over the accuracy of HR/payroll data.
Also, if an issue comes up during the year pertaining to an employee’s personnel information held in the system, HR would not be able to refer to the approved copy of the school organizational chart to ensure that any errors noted were, in fact, updated in the system. Recommendation Approved school organizational charts should be retained each year in a centralized location as evidence of a key control, so that they can be found if questions arise. Management Response We concur. We have taken steps to ensure that school organization charts will be kept and maintained each year.
We plan to keep them on file for two years. METROPOLITAN NASHVILLE PUBLIC SCHOOLS IMPROVE ORGANIZATION OF HUMAN RESOURCES FILE MAINTENANCE Observation KPMG noted several instances where we could not ascertain that employee information contained in the system was correct by reviewing employee personnel files. KPMG selected a sample of individuals from the school organizational charts and obtained personnel files from HR for each individual selected. In some instances, employees had transferred to other
schools, but no supporting documentation of the transfer was present in the employee’s personnel file. We also noted that the date of hire on the organizational chart differed from the date of hire found in the employee’s personnel file in one instance. Recommendation Employee information in the system should always mirror the information contained in the employee personnel files. Therefore, any changes in employee information that are made to update the system need to also be documented in the personnel files, and vice versa.
Also, an appropriate official should review employee information that is input into the system to ensure that it agrees with the information in the employee personnel file. Any discrepancies should be investigated. Management Response We concur. Steps have been put in place to be sure that data in the mainframe matches data in personnel files and vice versa. Additions/deletions/corrections in the file will be maintained by our senior secretaries and the senior clerk responsible for the the personnel files. METROPOLITAN NASHVILLE PUBLIC SCHOOLS SEGREGATION OF DUTIES
Observation The Business Manager and Internal Auditor jointly perform the reconciliation of the payroll cash account and the Business Manager then reviews the reconciliation after completion. Currently, no review of the reconciliations of the payroll direct deposit account is performed. KPMG also noted that a cash balance is not recorded on the general ledger in FASTnet for these payroll accounts; MNPS assumes the balances in the payroll accounts are always zero (all payroll accounts at MNPS are zero-balance banking arrangements) when performing reconciliations.
Errors in the reconciliation could exist and go unnoticed without proper review. Recommendation Someone independent from the reconciliation preparation process should review the reconciliations for all bank accounts. Cash accounts should be established in FASTnet for all accounts, even those with zerobalance banking arrangements, to better ensure ongoing monitoring. Evidence of review of reconciliations for all bank accounts (including Metro Investment Pool and Tennessee Teachers Credit Union) should be retained. Management Response We concur.
The Business Manager will no longer perform any reconcilation tasks and will assume responsibility of reviewing the final reconcilation statement for the main payroll account as well as the direct deposit accounts. Once payroll is transferred to FASTnet, these bank accounts will be closed. We have changed the format of the bank reconcilation template effective July 2004, to include a place for the preparer to initial and date when the reconcilation was performed and a place for the reviewer of the reconcilation to initial and date once the review has taken place.
METROPOLITAN NASHVILLE PUBLIC SCHOOLS COORDINATION OF CLOSING PROCESS Observation The closing process at MNPS is somewhat lengthy and not all entries are made on a timely basis. The different departments at MNPS are each responsible for completing their own closing process at year end and no one takes responsibility for ensuring that all entries get booked in a timely manner. Recommendation KPMG suggests that MNPS develop a detailed closing schedule that includes all entries that need to be booked and all steps that need to be taken in order to complete the closing process in a timely fashion.
Target dates should be established for completion of each task, and responsibility should be assigned at an individual level. This list should be monitored continuously to ensure appropriate progress is being made. This will require enhanced communications between departments within MNPS. The accounting department needs to take responsibility for leading the closing process and ensure that all entries across MNPS are completed on an accelerated timeframe from previous practices. Management Response We concur.
In the past, key items for the MNPS year-end closing procedures have been incorporated with Finance’s schedule. The MNPS accounting staff will work with Metro’s Division of Accounts to prepare a detailed closing schedule to ensure that all entries get booked in a timely manner for June 30, 2005. METROPOLITAN NASHVILLE PUBLIC SCHOOLS EVIDENCE OF MANAGEMENT REVIEW Observation Monthly MIP and Tennessee Credit Union statements are reconciled to Professional Employees’ Insurance Trust Fund records and documented; however, evidence of an independent review is not documented. Recommendation
KPMG suggests that the independent review should be dated and documented by signature or initials on the monthly reconciliations of MIP and Tennessee Credit Union Statements. Management Response We concur. We have changed the format of the bank reconcilation template effective July 2004, to include a place for the preparer to initial and date when the reconcilation was performed and a place for the reviewer of the reconcilation to initial and date once the review has taken place. METROPOLITAN NASHVILLE PUBLIC SCHOOLS INCURRED BUT NOT REPORTED LIABILITY FOR SCHOOL PROFESSIONAL EMPLOYEES’ INSURANCE FUND
Observation Management books estimated incurred but not reported (IBNR) liabilities for the School Professional Employees’ Insurance Fund using estimates provided verbally by external consultants at year-end. Obtaining a supporting actuarial valuation was difficult and time-consuming. Recommendation Management should request the actuarial valuation in a timely manner and test the data such as claim payment history used in the actuarial valuation in conjunction with their year-end close process. Then IBNR should be adjusted to the actuarial valuation.
Management’s Response We concur. Management will request the actuarial valuation in a timely manner and test the data such as claim payment history used in the actuarial valuation in conjunction with the fiscal year 2005 year-end close process. METRPOLITAN NASVHILLE PUBLIC SCHOOLS REPORTING OF CLAIMS EXPENSE Observation Claims expense for the School Professional Employees’ Insurance Fund was commingled with administrative expense all during the year. Recommendation Claims expense should be reported on its own financial statement line item.
The internal financial statements related to the School Professional Employees’ Insurance Fund should be reviewed by the accounting department on a monthly basis throughout the year and such reclassifications should be addressed during such financial statement reviews. Management’s Response We concur. The accounting department will review the internal financial statements related to the School Professional Employees’ Insurance Fund on a monthly basis and verify that claims expenses and administrative
expenses are reported on separate financial statement line items for fiscal year 2005. DEPARTMENT OF WATER AND SEWERAGE SERVICES CHANGE MANAGEMENT PROCEDURES Observation The Department does not have documented change management procedures implemented for the AS/400 or for application changes and/or upgrades. Also, a test environment on the AS/400 server is utilized to test any changes and/or upgrades to the operating system or to the application before migrating the change to the production environment, but testing is not documented or approved upon completion.
Recommendation Management should develop and implement formal change management procedures for changes to the operating system and/or applications. Change management procedures should include standards for documenting, testing and approving changes before they are migrated to the production environment. Management Response We concur. The AS400 Hardware and Operating System is maintained and operated by Metro ITS. There is a change management process in place in that department that documents changes and provides approvals for those changes.
Staff from both ITS and Metro Water & Sewer IS (MWS/IS) are involved in the preparation of the documentation and the documentation is retained by ITS. MWS does adhere to a systematic approach to implementing changes to the Customer Information System (CIS). Changes to the CIS Application may result from a range of circumstances, such as: • Periodic Maintenance • User Requests • Software Upgrades • New Software Acquisition • Changes/Modifications • Unforeseen Events The following tasks are performed on all changes prior to implementation to production: • Log information in Log File.
Log File contains date problem reported, description of problem, date change applied to test, testing results, date change applied to production (resolution date). • Determine if problem requires a temporary work-around. • Perform Benefit/Risk Analysis – This includes determining the impact to the system and to our customers • Develop Testing Scripts with expected results • Schedule a time frame to implement the change while considering application restrictions and upcoming events such as month-end, year-end, holiday, heavy volume days, etc.
• Coordinate on-site/on-call support to resolve any problems that may occur during implementation or subsequently thereafter. • Document and coordinate a back out plan. • Complete any internal checklist that may be required by the Manager/Process Owner. • Implement changes to the Test Environment – This includes any changes that may be applied by vendor to the HTEPTF library. • Perform Acceptance Testing based on Testing Script – This may include balancing financial/nonfinancial records. • Approve/Disapprove change based on testing results • • • • Determine if End-User training and/or documents are needed.
Implement changes to the Production Environment – This includes any changes that may be applied by vendor to the HTEPTF library. Perform Acceptance Testing based on Testing Script – This may include balancing financial/nonfinancial records. Approve/Disapprove change based on testing results Essentially there are 4 steps that occur in normal application modifications. • • • • Step 1 – Development of a requirement document defining the required changes. This step is performed by MWS/IS based on meetings with key members of the Customer Service division.
This division is the owner of the HTE CIS application. Step 2 – Based on the requirements, document modifications to the application code are made by HTE and delivered for testing to the test partition of the AS400. Step 3 – Initial testing is accomplished by MWS/IS and based on the results of that testing any additional modifications or corrections are communicated to HTE. Step 4 – Final acceptance testing is done by key people in the Customer Service division and agreement of proper function is reached prior to approval to move from test to production.
To formalize this process, forms will be developed during fiscal year 2005 that document any changes required and the testing required for these changes. These forms will be completed by MWS/IS through the process, and signatures of the appropriate key people will be placed on these forms prior to promotion from the test to the production system. These forms will be maintained by MWS/IS. This process will separate the modification process between 3 groups, HTE, MWS/IS, CSC key employees. Major upgrades to the software will be handled the same way except there will be multiple
signatures required for final approval of promotion from test to produ