· The City Charter charges the Controller with the responsibility for establishing accounting records, procedures and internal controls necessary to facilitate sound financial management and accounting practices. In order to perform these basic duties, the Controller is responsible for establishing and maintaining a financial accounting system that can produce accurate, timely and useful financial reports, and provide for the monitoring and control over the City's finances and operations.

· The City's current financial accounting system, FAMIS, and supporting financial and reporting systems are inadequate to meet the financial reporting and fiscal management needs of City and County of San Francisco departments. In an effort to compensate for the shortcomings in the financial system, the Controller and departments have implemented an ad hoc system of databases and reporting software. However, many departmental reporting and financial management needs are still left unmet. The current system is inflexible, resulting in delays in meeting basic reporting needs and the duplication of work.

· The estimated cost of updating FAMIS is $500,000 to $1.7 million. At least one jurisdiction replaced a system similar to FAMIS for $13 million. The significant cost of updating FAMIS, or acquiring a new system, has resulted in a decision by the Controller to continue operating the current system and develop enhancements to the system in the immediate future.

· The Controller should perform an assessment of the short-term financial reporting and accounting needs of the departments, and the City and should develop a strategic plan for meeting those needs. In addition, the Controller should assess the impact to the City and County's financial management resulting from the continued long-term use of the FAMIS financial accounting system and develop a strategic plan for replacing the current system.

As stated previously in this report, it is the Controller's responsibility to establish the financial accounting systems and reporting necessary for departments to effectively manage City finances and operations.

Financial Systems

The City's primary financial systems consist of:

· FAMIS, which is the general ledger accounting system;

· BPREP, which is the budget preparation system that uploads budget data to FAMIS;

· Executive Information System (EIS), which is a reporting and analytical tool for FAMIS and includes the Oracle database, which is a data repository: and, Powerplay and Impromptu, which are reporting tools linked to FAMIS and EIS;

· ADPICS, which is the purchasing system; and,

· FAACS, which is a centralized fixed-asset database.

Current System Limitations

The current financial accounting and reporting systems do not meet departmental needs. In an effort to compensate for the weaknesses in the financial system, the Controller and departments have implemented an ad hoc system of databases and reporting software.

The City's core financial accounting system, FAMIS, was last upgraded in FY 1995-1996, when the system migrated from batch processing to an on-line, real-time system in which users enter data and the system updates immediately. Because FAMIS is a transaction based mainframe system, it is inflexible and developing reports is costly and time consuming. FAMIS does not efficiently track appropriations and expenditures in the manner that departments would like to manage expenditures. For example, it is difficult to produce reports which track expenditures by vendor. In addition, the inflexibility of the system hinders the monitoring of compliance with City policies, such as the Minority/Woman/Local Business Enterprise (M/W/LBE) Ordinance.

To address the aforementioned reporting limitations, the Controller installed EIS in 2000, as a reporting and analytical tool with Powerplay and Impromptu, which are reporting tools linked to FAMIS and EIS. The focus of EIS to date has been to develop support for the Comprehensive Annual Financial Report (CAFR). However, the CAFR format is at such a high level that the data and information is not useful for the day to day financial and operational needs of the City. Therefore, the acquisition of EIS has not resolved all of the departments' reporting needs. For example, it is still difficult to report by vendor using EIS, through either Powerplay or Impromptu. To meet this basic departmental financial reporting need, departments currently must request a special report from the Controller's Office. In addition, EIS is not currently available to all departments. A total of 13 departments are working with EIS at this time and there is no timetable for making Powerplay and Impromptu available to all departments. However, the Controller's Office states that it intends to allow departments access to and training on EIS, although no formal program has been developed.

Many departments do not have access to other applications implemented by the Controller's Office. In addition to EIS, many departments do not have online access to FAACS, which was established for the early implementation of GASB 34. While some departments can access FAACS online and provide data for capital transactions, the Controller inputs asset data centrally on behalf of the other departments.

The budget system, or BPREP, is an antiquated system with little enhancement capability. For instance, BPREP is currently incapable of recording department performance measurements to compare with budgeted amounts. The current budget system's lack of adaptability hinders policy based budgeting. In addition, departments do not have an online review capability because BPREP is still a batch processing system. Departments cannot look at specific expenditures across divisions in BPREP without creating a special report.

As discussed previously, the limitations of the current systems result in departments setting up duplicative systems requiring manual data input. These efforts by departments require considerable resources to essentially recreate financial data and information in a useable format. These duplicative systems allow departments to track appropriations and expenditures in the manner that departments manage expenditures and to create data and information they need on a timely basis. In addition to the operational inefficiencies created by the need to recreate financial data and information, the inability to obtain useful financial reporting impairs the ability to appropriately monitor and control departmental finances and operations. The limited and inflexible reporting tools make it difficult for departments to manage their financial data. The Controller's Office maintains that the system is currently stable and reliable. However the Controller's Office acknowledges that the system is inflexible and limited. In addition, the Controller's Office has begun planning for short-term solutions, as will be discussed in the following section.

Strategic Planning for the Short Term

The Controller's Office reports that it has begun a cycle of strategic planning for short-term solutions to the limitations of the current systems. For example, the Controller's Office is moving towards web-based applications for many of the systems. In addition, the Controller's Office has begun archiving files older than three years in FAMIS in order to speed-up the system for current transactions. However, the information available regarding the strategic planning process lacks specific detail and is not documented in writing.

The Controller's Office has relied on customizations of the existing software packages as short-term patches to the limitations of the City's financial systems. Such customizations can be incrementally costly without significantly enhancing the functionality of the system and without eliminating the necessity for a replacement system. Therefore, the Controller should conduct an assessment of departmental and City needs and should develop a strategic plan for meeting those needs. As previously stated, the Controller should utilize existing analytical resources, such as City Projects Division or Financial Systems and Reporting Division staff.

Long Term Planning

The Controller is responsible for establishing and maintaining a financial accounting system that can produce accurate, timely and useful financial reports, and provide for the monitoring and control over the City's finances and operations. The estimated cost of updating FAMIS is $500,000 to $1.7 million. Discussions with the Controller's Office indicated that replacing FAMIS with a new general ledger, budget and purchasing system could cost $30 million, excluding hardware and in-house implementation costs. However, the Controller's Office has not received a bid on the cost. The Budget Analyst notes that Santa Clara County has recently purchased a new general ledger system for $13 million. The significant cost of acquiring a new system has resulted in a decision by the Controller to continue operating the current system in the immediate future. However, many departmental reporting and financial management needs are still left unmet and short-term patches do not compensate for a cohesive financial accounting and reporting system.

Although the current economic outlook has delayed the decision to upgrade FAMIS, it is critical that the Controller's Office be prepared to move forward with improvements to the City's financial systems. As the general supervisor of the City's accounts, the Controller's Office has the responsibility to prepare for future City needs. A new improved system would facilitate monitoring and control over the City's finances and operations. In addition, a modernized system would be compatible with other jurisdictions and would facilitate information sharing and cooperation among municipalities and counties.

The short-term solutions being developed and implemented currently have not eliminated the need for a replacement system in the long-term. In addition to performing an assessment of the budgeting, financial accounting and reporting needs of departments and the City in the short- term, the Controller should assess the impact to the City's financial health resulting from the continued use of the FAMIS financial accounting system and BPREP budget system, over the long-term and develop a strategic plan for replacing the current system. A strategic plan should formalize the Controller's short-term and longer-term strategies for modernizing the financial systems and allow for budgeting for the cost of the replacement system.

Conclusions

The City Charter charges the Controller with the responsibility for establishing accounting records, procedures and internal controls necessary to facilitate sound financial management and accounting practices. In order to perform these basic duties, the Controller is responsible for establishing and maintaining a financial accounting system that can produce accurate, timely and useful financial reports, and provide for the monitoring and control over the City's finances and operations.

The City's current financial accounting system, FAMIS, and supporting financial and reporting systems are inadequate to meet the financial reporting and fiscal management needs of City and County of San Francisco departments. In an effort to compensate for the weaknesses in the financial system, the Controller and departments have implemented an ad hoc system of databases and reporting software. However, many departmental reporting and financial management needs are still left unmet. The current system is inflexible, resulting in delays in meeting basic reporting needs and the duplication of work.

The estimated cost of updating FAMIS is $500,000 to $1.7 million. At least one jurisdiction replaced its general ledger system for $13 million. The significant cost of updating FAMIS, or acquiring a new system, has resulted in a decision by the Controller to continue operating the current system and develop enhancements to the system in the immediate future.

The Controller should perform an assessment of the short-term financial reporting and accounting needs of the departments, and the City and should develop a strategic plan for meeting those needs. In addition, the Controller should assess the impact to the City's financial management resulting from the continued long-term use of the FAMIS financial accounting system and develop a strategic plan for replacing the current system.

Recommendations

The Controller should:

3.1 Perform an assessment of the City's financial systems needs and report back to the Board of Supervisors by June 30, 2004;

3.2 Develop a strategic plan for meeting identified departmental and City needs; and,

3.3 Be prepared to move forward with a replacement system when it becomes either necessary or financially feasible.

Costs and Benefits

The estimated cost of updating FAMIS is $500,000 to $1.7 million. The Controller has estimated that the cost to replace the City's general ledger, budget and purchasing systems would be approximately $30 million, not including hardware or in-house implementation costs. At least one jurisdiction recently replaced its general ledger system for $13 million. A new accounting system would provide long term operational efficiencies in the Controller's Office and departments.