The URL can be used to link to this page

Home My WebLink AboutFIN-05-034 - Project Proposal - Corporate Integrated Financial System ) db KITCHENER Financial Services ~ Report To: Date of Meeting: Submitted By: Prepared By: Ward(s) Involved: Date of Report: Report No.: Subject: Councillor B. Vrbanovic, Chair, and Members of the Finance and Corporate Services Committee October 31 , 2005 Pauline Houston, General Manager of Financial Services & City Treasurer Rosemary Upfold, Director of Accounting (Ext. 2353) Jim Taylor, Business Systems Analyst (Ext. 2246) N/A October 14, 2005 FIN-05-034 RECOMMENDATION: Project Proposal-Corporate Integrated Financial System THAT the Project Proposal-Corporate Integrated Financial System as detailed in Report Fin-05-034 be approved and the related estimated project funding of $ 3,200,000 be included in the 2006 Capital Forecast for consideration during the 2006 Capital Budget Review Process. EXECUTIVE SUMMARY: The City of Kitchener's existing Financial System (FAMIS) was implemented in 1985. The system was updated in 1999 for Y2K and the Data Mart was developed in 2000 to provide better reporting for users. FAMIS software is old mainframe based technology and presents many issues: · large number of manual processes · duplicate data entry · timeliness of processing data · timeliness of availability of financial data · inability to improve business processes · difficulty changing or improving system · availability, flexibility of Financial detail · lack of financial system integration · duplicate financial record keeping · internal and external support issues · City of Kitchener is the only Canadian client The 2005 Strategic Plan for Financial Services identified the need to develop a Business Plan for new computerized financial systems. A Corporate Integrated Financial System would provide many benefits including: · optimize and improve business and financial processes · provision of real time financial information · improved, flexible Financial reporting · provision of fully integrated financial services · compliance with current technological standards at the City of Kitchener · fully integrated system designed for Canadian market · system compatible with other municipal systems · business tools that are compliant with current legislation The capital cost of the entire project is estimated to be $2.5 million to $3.2 million. Using City of Kitchener estimating criteria the current estimate is Class B (+/-20-25%). These costs include software, hardware, license fees, configuration, implementation, training, and project staffing to backfill positions. Upon approval, Phase 1 of the Corporate Integrated Financial System project will begin in 2006 and will focus on identifying and gathering current business processes for improvement as well as gathering business and user requirements that will be used to prepare a Business Case for Council and to prepare a Request for Proposal (RFP). Capital funding of $150,000 will be required in 2006 for project staffing for this phase. Additional phases of the project will begin upon approval of the Business Case. An RFP will be issued in 2007, followed by the vendor selection and implementation process. The implementation process is projected to be completed by mid 2009. The additional capital funding for these phases is estimated to be $1.4 million in 2007, $1.5 million in 2008 and $150,000 in 2009. The Corporate Integrated Financial System Project Proposal, version V2-1, dated October 12, 2005, has been attached to this report to provide additional details regarding the project process to date. REPORT: In May 2005, a Steering Committee was created to oversee the preparation of the project proposal. Interviews were conducted, by staff from Information Technology, with key personnel in the Financial Services and Information Technology Departments to identify issues with the current financial systems. Several alternatives were examined: · Status Quo · Upgrade FAMIS to Version 5.1 · Purchase a New Corporate Integrated Financial System · Develop a New Corporate Integrated Financial System · Joint Project with Other Area Municipalities Having reviewed all the alternatives, staff recommend, that a New Corporate Integrated Financial System will best address the City's current and future technological requirements for many years. A new fully integrated financial system will be a powerful, flexible solution that: · provides timely information · is designed by industry experts to meet industry needs · is written for the Canadian Market · can be supported internally and externally · provides product updates and conversion programs · allows for the improvement of both financial and business processes · provides opportunity to share ideas and services with other municipalities · allows for future integration of other purchased products The following systems were identified to be included in the scope of the project: · General ledger · Accounts payable · Accounts receivable · Purchasing · Financial reporting · Budgets · Inventory · Investments · Activity based costing · Performance measurement A review of the municipal marketplace for large Ontario municipalities found only two major vendors, Oracle and SAP. Oracle recently purchased JD Edwards and PeopleSoft, eliminating two of the larger vendors. The Region of Waterloo is currently installing Oracle Financials and the City of Waterloo currently uses PeopleSoft Financials. Discussions with these two vendors provided the basis for estimating the capital cost of the project. Cost estimates are based on 2005 dollars and include PST and an inflation factor. The entire project is estimated to be between $2.5 million and $3.2 million. In 2006, Phase 1 of the Corporate Integrated Financial System Project would begin and will utilize the Corporate Business Case and Information Technology Project Management methodologies. This phase will focus on identifying and gathering current financial and business processes for improvement as well as gathering business and user requirements that will be used to prepare a Business Case for Council and to prepare a Request for Proposal (RFP). Capital funding of $150,000 will be required in 2006 to backfill positions for this phase. Additional phases of the project will begin upon approval of the Business Case. An RFP will be issued in 2007, followed by the vendor selection and implementation process. The implementation process is projected to be completed by mid 2009. The additional capital funding is estimated to be $1.4 million in 2007, $1.5 million in 2008 and $150,000 in 2009. FINANCIAL IMPLICATIONS: Phase 1 of the Corporate Integrated Financial System Project requires $150,000 of capital funding for project staffing. The capital funding that will be required for the RFP, vendor selection and implementation phases are estimated to be $1.4 million in 2007, $1.5 million in 2008 and $150,000 in 2009. CONCLUSION: A Corporate Integrated Financial System should be purchased. Phase 1 of the project should commence in early 2006 and should focus on identifying and gathering current financial and business processes for improvement as well as gathering business and user requirements that will be used to prepare a Business Case for Council and to prepare a Request for Proposal (RFP). Upon Council approval of the Business case, the remaining phases of the project will commence with an estimated completion in 2009. Estimated project funding of $ 3,200,000 should be included in the 2006 Capital Forecast for consideration during the 2006 Capital Budget Review Rosemary Upfold, CA Director of Accounting Pauline Houston, CA General Manager of Financial Services & City Treasurer Jim Taylor Business System Analyst Financial Services Depa1ment Project Proposal Corporate Integrated Financial System INTEGRA TED FINANCIAL SYSTEM PAGE 1 DOCUMENT: C:IDOCUMENTS AND SETTINGSIBECKYMILOCAL SETTINGSI TEMPORARY INTERNET FILESIOLK39IFIN-05-034 A TTCH FINANCE PROJECT PROPOSAL V2-11.DOC LAST UPDA TE: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Version History Version Date Author(s} Description V1-0 September 6, 2005 Jim Taylor Initial draft Jeff Jarram V1-1 September 13 2005 Jim Taylor Changes from September Jeff Jarram 8, 2005 Steering Committee V1-2 September 16, 2005 Jim Taylor Revisions from Rosemary Jeff Jarram Upfold V1-3 September 27,2005 Jim Taylor Revisions from Rosemary Jeff Jarram Upfold, Steering Committee, and Hans Gross V1-4 September 30,2005 Jim Taylor Revisions from Rosemary Jeff Jarram Upfold V1-5 October 3, 2005 Jim Taylor Revisions from Hans Jeff Jarram Gross, and Rosemary Upfold V2-0 October 4, 2005 Jim Taylor Final Version Jeff Jarram V2-1 October 12, 2005 Jim Taylor Changes from CMT- Jeff Jarram October 11 , 2005 CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 2 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Executive Su mmary .................................................................................................................... 4 Backgrou nd............................................................................................................................ ...... 6 Project Description ...................................................................................................................... 8 Strategic Alignment................................................................................................................... 12 Mu nicipal Marketplace Review................................................................................................. 15 Alternatives.................................................................................................................... ............ 16 Business & Operational Impacts............................................................................................... 19 Project Risk Assessment.......................................................................................................... 20 CosVBenefit Analysis................................................................................................................ 23 Conclusions & Recom mendations............................................................................................ 24 I mplementation Strategy........................................................................................................... 26 Review & Approval Process...................................................................................................... 27 CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 3 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Executive Summary The 2005 Strategic Plan for Financial Services identified the need to "Develop a Business Plan for new computerized financial systems to allow for more timely and accurate reporting of financial results". Recommendation The City of Kitchener Financial Services Department recommends the purchase of a new Corporate Integrated Financial System. The project will utilize the Corporate Business Case and Information Technology Project Management methodologies. The capital cost of the project including implementation is estimated to be $2.5 million to $3.2 million. These costs include project staffing, hardware, software and licensing fees. In addition, the annual operating costs for the new system are estimated to be $110,000 to $140,000, starting in 2008 resulting in an increase of $60,000 to $90,000 over the current operating costs of $50,000. In 2006, the first phase of the project will focus on gathering business requirements to prepare a Business Case for Council and prepare for the issuing of a Request for Proposal. Capital funding of $150,000 will be required for 2006 to backfill staffing positions for this phase. Additional phases, which will begin after Council approval of the Business Case, will require capital funding of $1.4 million in 2007, $1.5 million in 2008 and $150,000 in 2009. The preliminary 2006-2015 ten year capital forecast has been adjusted to include these projected costs. Recommendation: Approve $150,000 in 2006 capital funding to complete phase 1 of the Corporate Integrated Financial Systems project. This project will generate the following benefits to the City of Kitchener: · Existing Financial processes within the City of Kitchener departments will be optimized and improved, increasing the timeliness of information and reducing the possibility of errors. · Higher levels of service with regards to retrieval time of information providing staff and council up-to-date information. · The new system will be technologically compliant with the current technological standards at the City of Kitchener. Support and maintenance of old mainframe technology will no longer be required allowing easier updates and improvements to the system. · A new system will resolve issues surrounding the cost of not having information available when needed. · A new system offers the best opportunity for putting an industry compliant, efficient and reliable application into place at the City of Kitchener. · Reduction of manual interfaces, increasing reliability and traceability of information. The City of Kitchener's existing Financial System was implemented in 1985. The product was purchased from KPMG. The product name is Famis; Famis was later sold to Tier Systems who provide ongoing support of the product. The City of Kitchener is the only Canadian customer of Tier, a United States based company. The updates and enhancements to the existing system are directed to the U.S. market. The existing system was updated in 1999 for Y2K and Datamart was added to provide better reporting for end users in 2000. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 4 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Major Issues with the current finance systems. · A large number of manual processes within Finance. The manual processes create duplicate entry of information, and impact the timeliness of information · Accounts Payable - Invoices are faxed or scanned and e-mailed to other city departments for approval · Purchasing - Purchase orders/requisitions are hard copies that are forwarded to Purchasing Department for processing · Inability to improve business process because of the existing software. The current software does not allow for innovations such as automated workflow · Financial Reports- Lack of detail and traceability · Lack of System Integration · Manual interfaces to Financial Systems are required in many city departments · Duplicate data entry occurs (Data entry in originating department and data entry required in Finance Department) · Some departments keep their own financial records because current system can not always provide current information · Timeliness of payment information. Up to 6 weeks from creation of a field order (purchases less than $2,500) until information appears in reports (1 % of annual expenditures) · Support issues of a twenty year old software and mainframe based system. The existing system is difficult to improve and support. Many processes within Finance are dependent on one or two key individuals with the process knowledge which they acquired with several years of experience. Many of the current business processes are old, primarily manual, and geared to the current Financial System. These processes would be rewritten and improved as part of a new Corporate Integrated Financial System. This will provide up-to-date information with less likelihood of errors. This project will primarily impact internal staff at the City of Kitchener. The staff that will experience the most impact will be the Finance Department staff. Other internal staff such as the CAO's Office, Community Services, Corporate Services and Development and Technical Services that regularly use the Accounts Payable, Accounts Receivable, Purchasing and Financial Reports will also be affected as well as the Information Technology staff that will be supporting the new Corporate Integrated Financial System. Most systems at the City interface with the current General Ledger. A new system will improve the process of interfacing data between these systems. The benefits of the project identified above are expected to justify the cost. Therefore, the City of Kitchener should begin the process that will lead to the purchase of a computerized automated Corporate Integrated Financial System and its deployment throughout the City of Kitchener. The next phase of the project is to identify and define current business process for improvement as well as gather business and user requirements. This information will be used to prepare a Request for Proposal (RFP). Cost Summary Timeframe Amount Phase Requirements GatherinQ 2006 $150,000 Purchase, Implementation, and 2007 $1.4 Million Traininq Purchase, Implementation, and 2008 $1.5 Million Training Purchase, Implementation, and 2009 $150,000 Traininq CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 5 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Background Corporate Integrated Financial System The City of Kitchener's Financial System was implemented in 1985. The product was purchased from KPMG. The product name is Famis; Famis was later sold to Tier Systems who provide ongoing support of the product. The City of Kitchener is the only Canadian customer of Tier a United States based company. The updates and enhancements to the existing system are directed to the U.S. market. The existing system was updated in 1999 for Y2K and Datamart was added to provide better reporting for end users in 2000. In the spring of 1997, with the direction to replace Famis, an internal project team was assembled to determine the client server application necessary to replace Famis. The recommendation was to select KPMG's Performance Series software package. Ultimately, the Performance Series software system was unable to operate reliably with adequate performance. Implementation of KPMG's Performance Series system was aborted in favour of replacing the existing mainframe hardware and upgrading to the next version of Famis on a newer mainframe. The following observations were identified in the Pre-Business Case review process. Interviews were conducted with key personnel in the Financial Services and Information Technology Departments to identify issues with the current Financial systems. Observations · There are a large number of manual processes within Finance · Accounts Payable - Invoices are faxed or scanned and e-mailed to other city departments for approval · Purchasing - Purchase orders/requisitions are hard copies that are forwarded to Purchasing Department for processing · Inability to improve business process because of the existing software. The current software does not allow for innovations such as automated workflow · Financial Reports - Lack of detail and traceability · Home grown systems such as: o Cheque Writer o Cheque Reconciliation o Deposit system o Purchase Order Writer o Budget o Investment system · Lack of System Integration with other City of Kitchener Financial Systems and other Departments within the City of Kitchener · Manual interfaces to Finance are required in many city departments · Duplicate data entry occurs (Data entry in originating department and data entry required in Finance Department) · Some departments keep their own financial records because of timeliness of information · Timeliness of some payment information: o Up to 6 weeks from creation of a field order until information appears in reports · Support issues of old software and mainframe maintenance · City of Kitchener is the only Canadian client. The current system was written for U.S. clients who have different accounting rules than Canada CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 6 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL · Future direction: o Online payment of vendors and invoicing of customers o Automated approval process for Purchasing and Accounts Payable (workflow) o Existing segregated software makes implementation of these improvements costly and difficult if not impossible Current Situation The current financial systems are very reliant on manual processes. A significant amount of the data is uploaded into the General Ledger using Excel spreadsheets. Many manual processes within Finance are dependent on one or two key individuals with the process knowledge which they acquired with several years of experience. Many of the current business processes are geared to the current Financial System and are primarily manual. These processes must be reviewed and improved for the introduction of a new Financial System. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 7 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL III Project Description The Financial Services Department has determined in their 2005 Corporate Strategic Direction the need to replace the existing financial systems with a fully Corporate Integrated Financial System. The system when implemented will impact all areas of the corporation. Objectives · Obtain and implement a Corporate Integrated Financial System that is designed for the Canadian market and will stay current with Canadian requirements · Implement a financial package that is in use by other Ontario municipalities · Implement a financial package that is compatible with other area municipalities to allow for the possibility of joint upgrades, training etc. · Eliminate the duplication of data, reports, effort and extra book keeping · Identify efficiencies for Financial Services staff · Improve the integration of services within other areas of the City of Kitchener · Business Process improvements, in particular within the Accounts Payable, Accounts Receivable, and Purchasing · Reduce task turnaround times within the Accounts Payable and Account Receivable departments · Improve detail of financial reporting · Provide real time information to all user areas · Increase the ability to audit financial information · Ensure business tools are compliant with current legislation and techniques In Scope The Finance Department uses many systems in the course of their daily work. The following systems will be reviewed to determine their involvement in the project. · General Ledger - Ledger in which all the assets, liabilities, net assets, revenue and expenses are posted and from which financial statements and reports are prepared · Accounts Payable - Process to allow payments to suppliers for their goods and services · Accounts Receivable - Process to generate and track outstanding invoices · Purchasing - Process to generate and track purchase orders, field orders and requisitions. Generate the appropriate encumbrances within the General Ledger · Financial Reporting - Reports to communicate a wide range of financial data to interested parties · Outside Agencies; Centre in the Square, Kitchener Housing, Waterloo Region Municipalities Insurance Pool, and the Kitchener Public Library · Budget - Process for individual sections or departments to identify expenditures and receipts with the consolidation of them into the master budget · Activity Based Costing - A methodology that measures the cost and performance of activities and resources · Performance Measurement - Involves ongoing data collection to determine if a program or project is implementing activities and achieving objectives · Investments - The Investment application is used to maintain the city's short-term and long term investments · Inventory - Tracking of city stockrooms and their inventory including Utilities storeroom CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 8 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Other Agency Involvement Several agencies currently use the City of Kitchener's General Ledger and Accounts Payable systems. Centre in the Scuare The Centre in the Square staff are users of the existing financial systems. Two staff members and the Manager of Financial Services currently have access to the Financial Systems. The Centre in the Square uses the General Ledger and Accounts Payable modules. All cheques are printed at City Hall. The Centre receives FAMIS reports from Finance at the close of each month and use this information to manually prepare their monthly Financial Statements. They do their own Budgeting, and Purchasing. Largest source of revenue is tickets which are uploaded to the General Ledger by the deposit slip system. The Centre in the Square can only claim 50% of their GST payments. The City does not charge a fee to the Centre in the Square for use of the financial systems. The Centre in the Square intends to continue using the City's financial systems. Kitchener Housinc The Kitchener Housing staff are users of the existing financial systems. Kitchener Housing uses the General Ledger and Accounts Payable modules. All cheques are printed at City Hall. They receive reports from FAMIS on a monthly basis. They do their own Budgeting, and Purchasing. Accounts Receivable is handled by their own system (YARDI). An Excel spreadsheet containing revenue (rental revenue and taxes) totals is sent to City Hall finance department monthly for uploading into FAMIS. Includes rental revenue, and taxes. The City charges a fee to Kitchener Housing for use of the financial systems. Kitchener Housing intends to continue using the City's financial systems. Waterloo Recion Municioalities Insurance Pool The Waterloo Region Municipalities Insurance Pool are users of the existing Financial systems. Unlike Centre in the Square and Kitchener Housing they do not directly access the systems but submit the information to the Finance area for data entry. They use City of Kitchener Budgeting, and Purchasing. The Insurance Pool has a year end of May 31 st which is different from all other agencies and the city that have a December 31 st year end. The City charges a fee to the Waterloo Region Municipalities Insurance Pool for the services provided. Waterloo Region Municipalities Insurance Pool intends to continue using the City's financial systems. Kitchener Public Librarv The Kitchener Public Library uses ACCPAC for their financial system. They pay approximately $2,000 per year for support and upgrades to the system. The Library is not on the City of Kitchener network. The KPL has three staff members who are the primary users of their financial system. Preliminary evaluations indicate that for the Library to use a new city financial system will be considerably more expensive then their current system. Kitchener Public Library wants to monitor the progress of a new financial system for the city. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 9 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Other Agency Involvement Summary The requirements for the new Corporate Integrated Financial System will be determined by the City of Kitchener. Other agencies using the new Corporate Integrated Financial System will not impact the requirements of the new system. Out of Scope · Customer Information System (CIS) · Payroll (PeopleSoft) · Fleet System (Work Orders) Anticipated Outcomes The Corporate Integrated Financial System will provide the following results; · Improved processing of Purchase Orders, Accounts Payable, and Accounts Receivable. Process will be more timely · Duplicate record keeping will be eliminated. · Improved reporting as a result of more timely data Next Steps The next step of the project will identify current business processes for optimization as well as gather detailed user and business requirements for the new system. A Request for Proposal (RFP) will be developed from the requirements. Based on responses to the RFP a vendor(s) will be selected to provide the city with a new financial system. Prior to issuing the RFP, a business case will be prepared and presented to City Council for their approval. Based on responses to the RFP a vendor(s) will be selected to provide the city with a Corporate Integrated Financial System. The Finance Project will follow a Phased Implementation methodology. The implementation will begin in January 2008 with the General Ledger. The additional modules will follow at three to four month intervals over the next two years (2008 and 2009). CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 10 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Users of the Financial System Chief Administrator's Office, Community Services, Corporate Services, Development and Technical Services Kitchener Housing (Current User) Accounts Receivable Accounts Payable Budgets Financial Re ortin General Ledger Accounts Payable Financial Re ortin General Ledger Accounts Payable Financial Re ortin General Ledger Accounts Payable Financial Re ortin Centre in the Square (Current User) Kitchener Public Library (Potential User) CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 11 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL !II Strategic Alignment Corporate Business Plan Alignment Business Plan Goal Service First Community and Business Relations Predictability and Results Corporate Strategy Plan Alignment Strategic Goal Level of Impact Explanation We strive to ensue long High Ability to provide current and accurate financial information term corporate financial for improved corporate decision making. stability and fiscal accountability to our taxpayers Departmental Plan Alignment Departmental Goal Level of Impact Explanation Invest and manage assets Medium A long term funding plan is in place to maintain and replace strategically infrastructure. Accelerated Infrastructure Program. Equipment Replacement Reserve Program. Asset Management Program. Effective management of existing and future needs for real estate holdings. Long term business plans for all major enterprises are prepared and updated annually. Cash Investment Policy. Economic Development Investment Fund for downtown and employment lands to generate sustainable assessment growth. Ensure the effective and Medium Economic conditions are factored into project timing responsible stewardship of decisions. public funds within a All major investment decisions are supported by solid supportive policy framework Project Proposals, including thorough cost studies. 10 and 20 year Capital Forecasting. Debt policy. Reserve Funds Policy. Collection Policies. Purchasing Policy. Vendor Payment Policy. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 12 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Departmental Goal Level of Impact Explanation Strive for competitive, Medium All input factors are taken into consideration when setting tax rational and affordable rates. Such as: taxation levels Consumer Price Index. Inflationary increases unique to municipalities. Comparison to other municipalities. Balance levels of service provided with taxpayers' willingness to pay. Ensure basket of services continues to meet taxpayers' needs. Legislative service standards. Assessment growth versus cost to service growth. 3 Year Budget Targets - forecasting of tax increases considering both operating and capital impacts. Comprehensive Budqet Review Process. Ensure corporate fiscal High Timely, accurate and meaningful reporting is provided to targets are consistently met allow for quick reaction to significant deviations from budgeted financial results. Quarterly Financial Reporting to Council, CAO and General Managers. Improvement to monthly Financial Reporting for Departments. Business Plan for new computerized financial systems to ensure more timely and accurate reporting of financial results. Overtime Reports for review by General Managers. Monthly reporting and follow-up for Energy Consumption. Use of forecasting techniques and implementation of contingency plans. Municipal Finance Course for Operating Management. Factor for Senior Management Performance evaluation of achievement of Financial Results. Capital Project ManaQement StrateQY. Maximize value through Medium Comprehensive service level assessment. cost effective service A corporate fee philosophy to reflect an appropriate level of delivery cost recovery for various services. A comprehensive costing system in place for various services as one factor to determine service level and service mix. A comprehensive performance measurement system, as another factor to determine service level and service mix priorities. Use process mapping as a technique to unearth cost saving opportunities. Development CharQe Study. Explore new sources of Low Lobby for share of Gas Tax from Federal and Provincial sustainable revenue Governments. Investigate all opportunities for Grants from Federal and Provincial Governments. Ensure openness and Medium Publication of Annual Corporate Report. transparency of City Communication Plans and public process for all Major finances Financial Decisions. Publication of Key Performance Indicators. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 13 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Corporate Technology Alignment Goal Explanation Proposed solution is Full interfaces with other City systems. compliant with existing and future corporate technology CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 14 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Municipal Marketplace Review The Marketplace A review of the municipal marketplace for financial systems for large Ontario municipalities finds two major vendors, Oracle and SAP. Over the last several years a consolidation has taken place in the vendor community. Oracle Corporation has purchased JDEdwards and PeopleSoft eliminating two of the major players. Oracle Corporation continues to provide and support PeopleSoft and JDEdwards products however Oracle has indicated to current users that these products must be migrated to the Oracle product line by 2012. Other Municipalities A review of several Ontario municipalities shows various commercially purchased software packages being used. PeopleSoft (acquired by Oracle in 2004) seems to be the most popular commercially packaged software of the municipalities canvassed. Other packages include Oracle, SAP and JDEdwards (now owned by Oracle). The initial cost of the projects has ranged from $850,000 to $5.5 million. The Region of Waterloo is installing Oracle Financials which includes General Ledger, Accounts Payable, Accounts Receivable and Purchasing. The previous Financial System had been installed in 1992. For budgeting, the region uses RAC Software, a Mississauga based company. RAC Software is installed for budget systems in a large number of Ontario municipalities. The City of Windsor uses PeopleSoft Financials which includes General Ledger, Accounts Payable, Accounts Receivable, Purchasing, and Inventory. The system was installed in 2003. Halton Region has used SAP Financials since 1995. Additional modules and upgrades have occurred over the last ten years. Hamilton, Kingston, and Peel Region are using PeopleSoft Financials installed in 1998 and 1999. These projects were the result of Y2K. Hamilton and Kingston have also installed PeopleSoft payroll and HR modules. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 15 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Alternatives Five alternatives have been examined to meet the identified needs and objectives. Alternative 1 - Status Quo The City of Kitchener Financial Services Department can continue to use the current Financial System - Famis. Why this option has not been selected In order to meet its legal and business requirements, it is essential that the City of Kitchener has an accurate, efficient and reliable Financial System. The following deficiencies have been identified: 1. The vendor has indicated that within the next few years they will no longer be able to support this version of Famis. 2. The City of Kitchener will be dependant on a 20 year old Financial System. 3. The current system is using old mainframe technology and very difficult to continue supporting. 4. Virtually no integration of the various components. The existing components such as Purchasing and Accounts Payable operate independently of each other rather then being integrated which would reduce data entry and improve accuracy. 5. Reporting issues-Lack of detail, traceability and timeliness of information. 6. Inability to improve business process because of the existing software. The current software does not allow for innovations such as automated workflow. 7. Lack of System Integration with other Finance Systems and other Departments within the City of Kitchener. 8. Some departments would still maintain their own records because of the timeliness of information. 9. Financial Systems are very reliant on manual processes. 10. A large amount of data is uploaded into the General Ledger using Excel Spreadsheets. 11. Many of the processes within Finance are dependant on one or two key individuals. 12. These deficiencies lead to three major concerns that need to be addressed by another solution: · Reliability: Can information be identified and retrieved when needed? · Efficiency: How much time is being wasted searching for information? · Cost: What is the cost of not having accurate information when needed? There is no capital cost for this option and annual operating costs would continue to be $50,000 (currently included in the operating budget). CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 16 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Alternative 2 - Upgrade Famis to Version 5.1 The City of Kitchener Financial Services Department could upgrade the current modules of Famis to Version 5.1. This alternative would allow for future support of the system. Why this option has not been selected The following deficiencies would continue to be present: 1. The system would still be a mainframe technology based system and very difficult to continue supporting. 2. Virtually no integration to other components. 3. Reporting issues-lack of detail, traceability and timeliness of information. 4. Inability to improve business process because of the existing software. The current software does not allow for innovations such as automated workflow. 5. Lack of System Integration with other Finance Systems and other Departments within the City of Kitchener. 6. Some departments would still maintain their own records because of the timeliness of information. 7. Financial Systems are very reliant on manual processes. 8. A large amount of data is uploaded into the General Ledger using Excel Spreadsheets. 9. Many of the processes within Finance are dependant on one or two key individuals. 10. These deficiencies lead to three major concerns that need to be addressed by another solution: · Reliability: Can information be identified and retrieved when needed? · Efficiency: How much time is being wasted searching for information? · Cost: What is the cost of not having accurate information when needed? The capital cost of this option would be $340,000, including vendor and conversion support, hardware, system software, additional IT and Finance staff, and annual operating costs would be $50,000. As well, existing staff resources would be required from Finance (3 months) and IT (6 months) for the conversion. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 17 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Alternative 3 - Purchase a New Corporate Integrated Financial System The City of Kitchener Financial Services Department has the option to purchase a new Corporate Integrated Financial System. This option involves using the Information Technology Project Management Methodology for each phase of the project. Why this option has been selected This option offers the best opportunity to implement an industry compliant, efficient and reliable application at the City of Kitchener. It also offers the best solution for system sustainability, keeping the application up to date, and providing integration to all components and departments. Taking into consideration software costs, license structure and maintenance fees, this option will not be the lowest cost alternative. The main benefits of this approach are: 1. A Corporate Integrated Financial System that will provide timely information. 2. Powerful and flexible solution designed by industry experts to meet industry standards. 3. The solution is written for the Canadian market. 4. Ongoing support and product updates. 5. Chosen solution would meet current and future technology requirements. 6. Implementation of the new system will lead to better business processes and new best practices. 7. Canvassing other municipalities has shown they have had the most success with this option. 8. Will provide opportunities to share ideas with other municipalities that use the same software for increased effectiveness. 9. Provides for future integration of purchased products. 10. Elimination of duplication of data, reports, effort and extra bookkeeping. The capital cost of this alternative will be $2,500,000 to $3,200,000 incurred primarily in 2007 and 2008. The operating cost for this alternative beginning in 2008 will be $110,000 to $140,000($50,000 is currently included in the operating budget). Alternative 4 - Develop a New Corporate Integrated Financial System In House Due to the size of the project, the complexity of the scope of the project and the resources needed, developing an in house solution is not being considered as an option. This approach would require a large number of additional resources in the Finance Department (to create detailed requirements) and Information Technology (to design the new system, develop software, and to support the software). Alternative 5 - Joint Project with Other Municipalities The three area municipalities have indicated they do not see any current opportunities for joining/sharing in a joint Financial Project. The City of Waterloo has replaced their Financial System in the last several years and are continuing to upgrade that system The Region of Waterloo are currently replacing their Financial System and have already implemented several phases. The City of Cambridge intends to continue using their current Financial System. Throughout the life of this project the project team will maintain contact and open discussions with the municipalities we canvassed. They will be a valuable resource with regards to implementation problems, issues, general questions and concerns. The selection of a system compatible with the systems used by the other area municipalities will also provide the possibility of future joint upgrades and training. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 18 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL III Business & Operational Impacts A new system will primarily benefit internal staff at the City of Kitchener. The staff that will experience the most benefit will be the Finance Department staff. Other internal staff such as the CAO's Office, Community Services, Corporate Services and Development and Technical Services that regularly use the Accounts Payable, Accounts Receivable, Purchasing and Financial Reports will also be affected as well as the Information Technology staff that will be supporting the new Corporate Integrated Financial System. The implementation of the new Corporate Integrated Financial System will lead to significant Business Process improvements as each department synchronizes its processes to interact with the Financial Services Department. The greatest impact will be with the Accounts Payable and Purchasing Departments by optimizing the purchasing and account payable functions. The following describes the impact on specific users of the Financial System: Citv Council The Council will experience higher levels of service by receiving timely and accurate reporting of information. Internal Staff Internal staff will also experience higher levels of service with the new Corporate Integrated Financial System. Information will be readily available in a real time environment leading to quicker retrieval time of information. Automated interfaces between existing systems and the new Financial System will eliminate the need for duplicate data entry. Information Technoloav The Corporate Integrated Financial System would be installed on a server connected to our Network. Information Technology Business Systems Analyst (BSA) would be involved in the process of selecting the software and managing future upgrades. Information Technology Technical Support staff would playa key role in the initial installation of the product and future upgrades. They would also provide ongoing support for the server, network, and database associated with the Corporate Integrated Financial System. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 19 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Project Risk Assessment The major risks associated with the recommended alternative, Alternative 3 purchase a new Corporate Integrated Financial System are identified below. A strategy to mitigate each risk is also presented. The following project risks have been considered: Continue to closely monitor existing systems to minimize probability of a failure, Customer Stakeholder Acceptance - Resistance to Chan e: Risk 4 Mitigation Strategy departments. Impact - Medium Communication plan will be part of the project to keep all staff informed about the new Corporate Integrated Financial System. City staff will be kept fully informed of the project. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 20 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Client Sever and Intranet components must comply with the City of Kitchener's regular security standards. Impact - Medium Information Technology requirements are already discussed and included as part of the needs requirements for the system. Security requirements include security and audit functionalities. The system will not put any strain on our network. Impact - High Identifying licensing costs of the proposed solution as early as possible. Define database requirements early in the project. The system Impact - High includes requirements identified by key stakeholders. Key stakeholders will be involved throughout the process. The requirements for the new system will be fully defined, documented and approved. Users will require training on how to use the system. Some Information Technology staff will also require training on setting up and administering the system. Impact - Medium Provides users with software specific training. Training requirements will be a key component of the new system. The key staff will be identified and trained prior to the implementation of a new CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 21 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Information Technology, technology requirements are included in the needs requirements for the system. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 22 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL III Cost/Benefit Analysis Quantitative Analysis - Financial Cost & Benefit for Alternative 3 (Purchase a new Corporate Integrated Financial System): Aooroved Fundino Capital Costs: Operating Costs: No funds currently approved Currently $50,000 for annual Maintenance fees for Famis Summarv of Caoital Costs The cost estimates, determined through discussion with two vendors (SAP and ORACLE), are detailed below. The cost estimates based on 2005 figures include PST and an inflation factor. Using City of Kitchener estimating criteria the current estimate for this project is Class B (+/-20-25%). The IT Resources and Finance Resources are the costs for backfilling positions. The backfilling will allow current staff to contribute to requirements gathering, implementation, and training for the new Corporate Integrated Financial System. Configuration, Implementation, Trainin Database Licensin Hardware Conversion Su ort IT Resources Summarv of Ooeratino Costs The operating cost of the new system is estimated at $11 0,000 to $140,000 per year an increase of $60,000 to $90,000 over the current cost of $50,000. The estimate is for support and upgrade costs. Existino Information Technoloov and Finance Staff The project will also require 5.5 years of existing Information Technology staff (3 years Business Systems Analyst (4 years of time at 75%), 1 year Database Administrator, 1.5 years of Development staff) and 2 years of existing Finance Department staff time. Benefits The major benefits of the new system are the increased timeliness and details of financial information. Staff and City Council will be able to make better decisions because of the additional information. Analvsis Based on the estimates provided, the total estimated capital cost for the purchase of a new Corporate Integrated Financial System would be between $2,500,000 and $3,200,000. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 23 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Conclusions & Recommendations Conclusions The current blend of manual and computerized financial systems at the City of Kitchener does not meet with the City's Financial needs. The Corporate Financial Strategic Initiative is to develop a Business Plan for a new computerized Corporate Integrated Financial System to ensure a more timely reporting of financial results The new Corporate Integrated Financial System, when implemented will benefit all areas of the City of Kitchener; the integration of all financial components will ensure an accurate, efficient and reliable Financial System for many years to come. By purchasing a new Corporate Integrated Financial System the City of Kitchener will realize a number of benefits including: Flexibility · Tailor charts of accounts to fit your needs. · Create summary, statistical and suspense accounts. · Define any type of calendar. · Add custom data fields without programming. · Make high volume changes to charts of accounts. Complete Journal Processing · Import journals from old manual systems. · Automatically balance journals. · Require approval for journals prior to posting. · Perform statistic based cost-accounting. · Drill down to sub-ledger details. Advanced Budgeting · Perform top-down and bottom-up budgeting. · Set budgetary controls. · Unload and download budgets to spreadsheets. Financial Inquiry and Reporting · Define reports for multiple users. · Submit ad hoc reports and exception reports. · Define and submit reports from a spreadsheet. · Publish web-based reports. · Drill down to sub-ledger transactions. Business Process · Tailor your processes using business events. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 24 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL Recommendations The City of Kitchener Financial Services Department recommends the purchase of a new Corporate Integrated Financial System. The project will utilize the Corporate Business Case and Information Technology Project Management methodologies. The capital cost of the project including implementation is estimated to be $2.5 million to $3.2 million. These costs include project staffing, hardware, software and licensing fees. In addition, the annual operating costs for the new system are estimated to be $110,000 to $140,000, starting in 2008 resulting in an increase of $60,000 to $90,000 over the current operating costs of $50,000. In 2006, the first phase of the project will focus on gathering business requirements to prepare a Business Case for Council and prepare for the issuing of a Request for Proposal. Capital funding of $150,000 will be required for 2006 to backfill staffing positions for this phase. Additional phases, which will begin after Council approval of the Business Case, will require capital funding of $1.4 million in 2007, $1.5 million in 2008 and $150,000 in 2009. The preliminary 2006-2015 ten year capital forecast has been adjusted to include these projected costs. Recommendation: Approve $150,000 in 2006 capital funding to complete phase 1 of the Corporate Integrated Financial Systems project. Project Responsibility The Steering Committee, composed of 4 Directors from the Finance Department and the Director of Information Technology will be responsible for the project. Project Accountability Financial Services is the sponsoring department. Pauline Houston the General Manager of Finance is the project sponsor and will act as the subject matter expert responsible for the sign-off of any project related documents. The Deliverables of the project will be completed by the Project Manager, Business Systems Analyst and the Vendor. Major Issues and Approvals will be addressed by the Project Sponsor and the Project Steering Committee. Project Management Jim Taylor is the Project Manager for this project. He will direct the project based on the Information Technology Project Methodology for each phase of the project including implementation. CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 25 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL III Implementation Strategy The Finance Project will follow a Phased Implementation methodology. The Phases will consist of dividing the Core components such as General Ledger, Account Payable, Accounts Receivable, Purchasing, Financial Reporting and the Outside Agencies (Centre in the Square, Kitchener Housing, Waterloo Region Municipalities Insurance Pool) into separate manageable components. The actual order and timing of the implementation may vary from the expected schedule. The following schedule shows the Finance Project Deliverables and Expected Completion times. Deliverable Expected Completion Project Proposal January 2006 Prepare Proiect Proposal Present Project Proposal to CMT Present Proiect Proposal to Council Council Approval of Project Proposal Requirements Phase October 2006 Gather User Requirements and Identify Business Process Reengineering Opportunities Prepare Concept of Operations Document Business Case December 2006 Prepare Business Case Council Approval of Business Case Request For Proposal March 2007 RFP Preparation RFP Completion RFP Approval and Siqnoff Issue RFP March 2007 Vendor Selection Process June 2007 Review RFP Responses Score RFP's Select Vendor and Product Steerinq Committee Approves Product and Vendor Selection Vendor Report to Council Finalize Contract September 2007 Enter into Contract Negotiations with Vendor Contract Siqnoff Implementation Process and Implementation Milestones Installation & Confiquration of Product Testing Traininq Conversion of Data General Ledqer Implemented January 2008 Account Payable Implemented April 2008 Purchasinq Implemented June 2008 Accounts Receivable Implemented September 2008 BudQet Implemented September 2008 Investments Implemented December 2008 Other modules Implemented 2009 CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 26 LAST UPDA TED: 10/17/2005 12:04:00 PM CORPORATE INTEGRATED FINANCIAL SYSTEM PROJECT PROPOSAL II Review & Approval Process Review Process The new Finance Project has a high priority, based on the age of the existing system and the benefits that can be realized from a fully Corporate Integrated Financial System. Approval Process In order for this project to proceed funding must be secured. Project Proposal Signoff Pauline Houston Project Sponsor General Manager of Financial Services Rosemary Upfold Chair of Steering Committee Director of Accounting Larry Gordon Director of Purchasing Joyce Evans Director of Revenue Roger LeBrun Acting Director of Budgets Mike Grummett Director of Information Technology Hans Gross Director of Project Administration and Economic Investment CORPORA TE INTEGRA TED FINANCIAL SYSTEM PAGE 27 LAST UPDA TED: 10/17/2005 12:04:00 PM