Loading...
1c. Appr Software Cost BenefitCITYOF CHANHASSEN 7700 Market Boulevard PO Box 147 Chan!~assel MN 55317 Administration Rone 9522271 tOO Fax: 952 2271110 Building Inspections Phon,~': 952 227 1180 Fax: 952 227 1190 Engineering Pho'/e 952227 1160 Fax: 952 2271170 Finance Phone: 9522271140 Fax: 9522271110 Park & Recreation Phone: 952227 1120 Fax: 952 227 1110 Recreation Center 2310 Coulter Boulevard Phgne: 952.2271400 Fax: 952 227.1404 Planning a Natural Resources Phone: 952227.1130 Fax 952 2271110 Public Works 1591 Park Road Phone: 952 2271300 Fax: 952 2271310 Senior Center Phone: 9522271125 Fax: 952 227 1110 Web Site ¢,rWW C~ ?hanhassen.mn us MEMORANDUM TO: FROM: DATE: Mayor City Council Bruce M. DeJong, Finance Director .:~ February 18, 2004 ~ SUBJECT: Approval of Software Cost Benefit Analysis Executive Summary Finance Department staff has been researching software packages for several months in anticipation of purchasing an integrated package that will minimize duplication of effort in data entry, ease reporting, and require less support. We have found several packages that can meet these requirements. The equipment certificate bonds issued in 2003 included $150,000 for financial software. Through discussions with potential vendors, we believe that this will be sufficient for the software licensing and the necessary installation support. The remainder of this report is designed to quantify the benefits that we believe will accrue with the licensing of an updated software solution. We found that we can reduce efforts in both the Finance Department and city-wide by between 740 and 1,280 hours per year and have other avoided costs of $4,000 - $6,000 by purchasing new software. These are tremendous savings! Assuming an average total cost of $40 per hour (salary, benefits, overhead) for the employees involved in these processes means that we will be saving between $30,000 and $50,000 each year after implementation. Staff recommends that the City Council approve this cost benefit analysis and authorize advertisement for bids. Current Environment The City of Chanhassen has 22,000 citizens and has annual operations that exceed $15 million dollars. Over the past decade its population has increased by more than 65% and the City expects that growth to continue with the opening of the 2005 MUSA area. The Finance Department consists of 4 staff with responsibilities broken down as follows: one Finance Director who is responsible for the budget, annual audit and financial report, bonding, and general management of the department; and three Accounting Clerks who handle payroll, purchasing, accounts payable, accounts receivable, cash receipts, escrow deposits, and utility billing. This staff processes more than 12,000 invoices, 6,500 vendor checks, 3,000 payroll checks, and 32,000 utility bills each year. The City of Chanhassen · A gruv,,'lno con/munity with clean lakes quality schools, a charrfing downtown, thriving businesses winding taiis, and beautiful parks A great place to !ire vvork and play Approval of Software Cost Benefit Analysis February 23, 2004 Page 2 Purchasing Currently the entire Purchasing process, from Requisition to Purchase Order to Vendor Check, is a manual process with multiple points of duplicate data entry occurring both in the procuring department and the Finance Department. The procuring department creates a spreadsheet or paper-based purchase order and routes that to the Finance Department following its approval by the Department Head. The Finance Director must manually confirm that there are sufficient funds in the procuring Department's account and must individually approve all Requisitions. Those that exceed the purchasing policy limits must be routed to the City Manager manually to be signed or placed on the City Council agenda for approval. The Purchase Order is then sent to the vendor. Once the procuring department has matched the Purchase Order with a Packing Slip and a Vendor Invoice it keeps one copy of the Purchase Order for its records and sends another to the Finance Department with the Packing Slip and the Vendor Invoice. The Finance Department manually enters the information into the financial system to create a check. A paper report listing all Vendor payments is presented to the Finance Director and the City Council for their review. Capital Items must be flagged after the procurement process and must be manually entered into the City's separate Fixed Assets accounting system. Items purchased for placement in inventory are not monitored or accounted for until the end-of- the-year inventory of the City's stockrooms. There is no accurate information pertaining to the loss or shrinkage of inventory items as no accounting occurs between purchasing, inventory utilization, and ending inventory. Budgeting Currently budgets are prepared through the production of a combination of financial system and spreadsheet-based reports. Budget reports are printed out of the financial system and sent to each department. The Department Head then enters their request for next year's budget on the form and returns the form to the Finance Department. The Finance Department then manually enters the figures into a Budget spreadsheet, prints the spreadsheet and presents it to the City Council for their approval. The City Council makes modifications to the proposed budget prior to establishing the final budget version. The Finance Director is then responsible for manually entering the final budget numbers into the financial system for use during the next fiscal year. Payroll Each Department has a payroll function. Someone manually tracks staff hours, enters the information onto a paper-based form, has the department or work group supervisor sign off on each form and then sends that form to the Finance Department at the end of every two weeks. The Finance Department must manually enter the time-sheet information into the Payroll system. Calculations for deductions (benefits, withholdings, etc.) are performed manually. Approval of Software Cost Benefit Analysis February 23, 2004 Page 3 Utilities This operation has a stand-alone billing package. Monies collected from utility bills are manually entered into individual accounts and a summary report is generated for manual entry into the City's General Ledger. Reconciliation of the two systems is cumbersome and prone to error. There is not a method for scanning bills rather than manual entry, Internet payment of bills, or allowing automatic deduction from a bank account. Monthly Reports The process for accounting for different programs requires having project codes in many departments. This allows revenues and expenses to be tracked for each program, but the budgets are prepared on a department basis. There is currently no way to get those programs summarized in a department automatically. The Finance Department prints out reports and summarizes them in spreadsheets for distribution to the City Council and Department directors. Year End Close The year-end close involves many manual entries into the general ledger to account for depreciation, capitalization, amortization, and accrued costs. These entries must then be manually reversed since the City is operating on a modified cash basis instead of an accrual basis. This effort lasts at least five months and involves many hours of unproductive time and significant involvement by HLB Tautges, Redpath, the City's independent auditor. The City's Vision City staff would like to see a fully integrated, user-friendly solution put in place that offers the following types of integration and procedural efficiencies: Complete integration of the Purchasing process, from the creation of a Requisition, to a Purchase Order, to a Vendor Check, to a Fixed Asset Record or an Inventory entry. This capability would enable the procuring department to create the Requisition online and would eliminate all points of duplicate data entry, saving the City 15 to 30 minutes of cumulative effort for each purchase order - a savings of approximately 250 to 500 hours of effort per year. The additional elimination of paper forms for the approval process will increase accuracy, ensure compliance with the purchasing policy, provide a detailed audit trail, and reduce errors and the potential for fraud. Complete integration of the Payroll process from time entry, to creation of the Payroll Check to entry of the General Ledger Journal Entry and the creation of the Accounts Payable Checks for all withholdings and deductions. This capability would enable the Finance Department payroll clerk to spend less time on data entry. This would increase the accuracy of payroll records and related transactions. This could result in a savings of three to five hours for each payroll cycle - a cumulative savings of approximately 100 to 150 hours per year. Approval of Software Cost Benefit Analysis February 23, 2004 Page 4 Complete integration of the Purchasing with Inventory and Fixed Assets. This capability will result in maintenance of a more accurate relationship between purchasing and inventory, a more accurate accounting of fixed assets and infrastructure depreciation. This would reduce the amount of assistance required from HLB Tautges, Redpath and potentially save approximately $2,000 to $3,000 per year. Complete integration of the Utility Management functions with the General Ledger and the Geographical Information System (GIS). This capability will result in the elimination of the manual entry of monies received into the General Ledger, resulting in time-savings of approximately five to ten hours per billing cycle - a cumulative savings of approximately 60 to 120 hours per year. Real-time, on-line budget checking for the Departmental Managers. This would provide each department with immediate confirmation of the availability of funds for encumbrance for Requisitions and Purchase Orders. This ability would also eliminate the need for the Finance Department to print and distribute monthly Fund Balance reports to each Department. Comprehensive and flexible reporting options, which include multiple pre-defined "standard" reports, CAFR reporting tools, pre-defined "management" reports for Department directors and Elected Officials. The system would have a simple, but comprehensive, ad hoc querying and reporting tool. These options should be supplemented by the ability to distribute the reports by printer, email, fax and posting to the web. These features would provide immense flexibility in reporting and would simplify the distribution of those reports, possibly eliminating most printed copies of reports. This could save twenty to thirty hours per month - 240 to 360 hours per year. This type of fully integrated solution will not only result in direct reduction of the time spent on these functions, the efficiency created will allow the city to avoid increased personnel costs due to the fact that these hours represent somewhere between one-third to one-half of a full-time position. These process improvements will result in approximately $23,000 to $35,000 in direct efficiency savings each year. Additional avoided costs include the inordinate amount of system maintenance currently provided by Rick Rice and Corey Gruenhagen. Software that has a simpler process for enhancements and bug fixes will save about 100 to 150 hours that they currently spend patching our system each year. These efficiencies will result in savings of $3,500 - $5,200 per year. The City will save in several miscellaneous areas such as printing costs and reduction in the amount of paper used for distribution of reporting information. These areas will result in a cumulative savings of approximately $2,000 to $3,000 per year. Approval of Software Cost Benefit Analysis February 23, 2004 Page 5 The City will also benefit from the potential for more accurate, professional management of each Department's budget. The ability to utilize real-time information during the procurement process could result in reducing the need for budget adjustments and transfers. Value Justification Elements What will be measured? Time Savings Created through procedural efficiencies Miscellaneous Savings Printing, paper, software maintenance and support Who is responsible ? Time Savings Finance Director and Department Heads Displaced payroll costs Finance Director and Department Heads Avoided Payroll costs Displaced Miscellaneous costs Finance Director and Department Heads Finance Director and MIS Coordinator How much is possible ? Time Savings (minimum cumulative/city wide) 15-30 minutes per PO x !,000 per year = 250 to 500 hours 3-5 hours per payroll processing x 31 per year = 93 to 155 hours 5-10 hours per utility bill x 12 cycles per year = 60-120 hours 20-30 hours x 12 reporting cycles per year = 240-360 hours 8-12 hours software support x 12 months = 96-144 hours Total Time Savings = 739 - 1,279 hours saved per year available for other activities Miscellaneous Savings Avoided accounting assistance costs $2-3,000 per year Printing/Distribution $2-3,000 per year h~tangible Be~zefits Decentralized Data Entry More efficient utilization of Finance Department personnel Increased accuracy in requisition and purchase order data entry Better management of funds by department managers Fewer budget amendments Simple, detailed, flexible report generation and distribution Increased citizen service levels Approval of Software Cost Benefit Analysis February 23, 2004 Page 6 Will this investment pay for itself? This type of software solution has been traditionally viewed as a cost of doing business. That is no longer necessarily the situation. Because of today's move toward more professional government management and the use of higher quality management tools (i.e. software), Enterprise Resource Planning solutions have been identified as valuable investments in the growth and infrastructure of the organization. The time savings, procedural efficiencies, better fund balance management, avoided and displaced payroll costs, and miscellaneous savings associated with a top-quality ERP solution should allow the City to recoup its investment within about 3-4 years. Recommendation Staff recommends that the City Council approve the cost/benefit analysis and authorize the Finance Director to prepare bid specifications and solicit bids for an integrated financial software package.