Loading...
A Financial Software Discussion CITY OF CHANHASSEN 7700 Market Boulevard PO Bo,147 Chanhassen, MN55317 Administration Phone 952227.1100 Fax: 9522271110 Building Inspections Phone: 9522271180 Fax: 9522271190 Engineering Phone: 9522271160 Fa,952.227.1170 Finance Phone 9522271140 Fax 952227.1110 Park & Recreation Phone 952.2271120 Fa,: 9522271110 Recreation Center 2310CoullerBoulevard Phone: 952227.1400 Fax: 952227.1404 Planning & Natural Resources Phone 952.2271130 Fax: 952.227.1110 PubiicWorks 1591 Park Road Phone: 952227.1300 Fa,: 952227.1310 Senior Center Phone 952.227.1125 Fax: 952227.1110 WebSile www.ci.chanhassen.mn.us A MEMORANDUM TO: Mayor and City Council FROM: Greg Sticha, Finance Directo~ . May 16, 2005 DATE: SUBJ: Softw~eP~chMeUpdme HISTORY As you ~e aw~e the City has been planning for the p~chase of an integrated softw~e package for finance and conununity development that will minimize duplication of effort in data entry, eMe reporting, and require less support. The city had issued $265,000 in equipment certificates in 2003 for the p~chMe of financial, conununity development, and web enabled softw~e. A cost benefit analysis prep~ed by the former [mance director in February of 2004, indicated that a new softw~e p~chase could save the city approximately 700-1,200 people hours per ye~ and printing and professional services costs of about $4,000-$5,000. This could be a potential cost savings of about $34,000 to $50,000 on an annual bMis. In addition the software would provide decentralized data entry, better management of funds by dep~ent managers, and most importantly increMed citizen service levels. CURRENT STATUS Proposals were requested from three vendors in November 2004. I reviewed those proposals in February and March 2005, and found all three vendors appe~ed to provide the product and service that the City is requiring for the integrated softw~e p~chMe outlined above. In M~ch and April 2005 the three vendors were Mked and provided a demonstration of the softw~e at city hall. BMed on those demonstrations a "Preferred Vendor" WM selected to rese~ch in further detail, this vendor WM ACS/Springbrook. The ACS/Springbrook softw~e WM chosen by the "Softw~e Selection Conunittee" bMed on the demonstrations, the integration abilities with GIS and LMerfiche, and WM also the lowest cost of the three products. In April 2005, the finance staff visited the City ofRoseville who is currently using the Springbrook softw~e. D~g that visit we inquired about the satisfaction they have with the softw~e and what improvements or The City of Chanhassen . A growing community with clean lakes, Quality schools, a charming downtown, thriving businesses, winding trails, and beaulilul parks. A great place to live, work. and play Softw~e P~chMe Update May 16, 2005 Page 2 of3 enhancements they would like to see in the softw~e. Also in April 2005, the finance staff visited the ACS/Springbrook regional site in Waite P~k, Minnesota. During this visit the staffWM able to use the product in a test mode to see how some ofthe city's data and information would be processed and used in the softw~e. BMed on those two visits the [mance staffwM comfortable with proceeding with ACS/Springbrook M the "Preferred Vendor". Also during April 2005, inspections division staff participated in a in depth hands-on demonstration of the ACS/Springbrook building permit softw~e package. BMed on this demonstration the softw~e appe~s to meet the needs of the city for processing all types of permits and licenses in coordination with the financial softw~e, which will be very beneficial to both dep~ents. They have also conunitted to developing a scheduling module tailored to o~ specifications. We have begun preliminary contract discussions with ACS/Springbrook in May, and we ~e estimating a total p~chMe cost of about $200,000, which would include all application softw~e, licenses, and conversion costs for the financial applications M well M those related to conununity development. In addition, we ~e anticipating annual maintenance costs to be about $23,000/ye~. A breakdown of each of the vendor's quotes is included at the bottom of this memo. The quote does not include licensing for web bMed technology to pay utility bills or building permits on-line. It is o~ belief, and the vendors reconunendation, that we need to get the base systems running properly before implementing some of the web bMed technologies. We plan on introducing some of these technologies one ye~ after the initial implementation. The additional costs for these systems will be in the range of $20,000 to $25,000 in one time licensing and softw~e fees plus annual maintenance costs (current maintenance costs ~e approximately $lO,OOO/ye~). FUTURE DATES The contract will be reviewed by staff and the City attoruey in May and e~ly June of2005. It is o~ hope to aw~d the bid of the contract at the June 13th city council meeting. Once that is accomplished, conversion and training will take place this summer and fall, with a "go-live" date of January 1, 2006. iòit, . . . Softw~e P~chMe Update May 16, 2005 Page 3 of3 VENDOR OUOTES LicensinglSoftw~e Annual Maintenance New World Systems GEMS ACS/Springbrook $263,000 $205,000 $200,000 $27,000/ye~ $17 ,OOO/ye~ $23,000/ye~ (current maintenance costs for these systems is about $lO,OOO/ye~) SUMMARY OF BUDGETED DOLLARS Total Equipment Certificates issued $265,000 Expenses: Financial Application Softw~e Conununity Development Softw~e Web Enabling Softw~e (P~chMe 2006/2007) $150,000 $50,000 $25,000 RECOMMENDATION It is our reconunendation that we proceed with the purchMe of an integrated softw~e package with ACS/Springbrook. This softw~e when all applications ~e p~chased and installed will include the integration of all financial accounting applications, permitting, complaint tracking, inspections, GIS, and lMerfiche. In addition we will be providing the ability to o~ residents to pay for their utility bills through ACH in 2006, and in 2007 provide them the ability to pay utility bills on-line, M well M look at a history of their accounts and request service for their accounts. We will also be providing the ability to pay for permits and licenses on-line sometime in 2007 or 2008. ATTACHMENTS 1. Softw~e p~chMe cost benefit analysis from 2-18-04. MEMORANDUM TO: Mayor City Council FROM: Bruce M. DeJong, Finance Director DATE: February 18, 2004 SUBJECT: Approval ofSoftw~e Cost Benefit Analysis Executive Summary Finance Dep~ent staffhM been rese~ching softw~e packages for several months in anticipation ofp~chMing 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 softw~e. Through discussions with potential vendors, we believe that this will be sufficient for the softw~e 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 softw~e solution. We found that we can reduce efforts in both the Finance Dep~ent and city-wide by between 740 and 1,280 hours per ye~ and have other avoided costs of $4,000 - $6,000 by p~chMing new softw~e. These are tremendous savings! Assuming an average total cost of $40 per ho~ (salary, benefits, overhead) for the employees involved in these processes means that we will be saving between $30,000 and $50,000 each ye~ after implementation. Staff reconunends that the City Council approve this cost benefit analysis and authorize advertisement for bids. Current Environment The City of ChanhMsen hM 22,000 citizens and has annual operations that exceed $15 million dollars. Over the past decade its population hM increMed by more than 65% and the City expects that growth to continue with the opening of the 2005 MUSA ~ea. The Finance Dep~ent consists of 4 staff with responsibilities broken down M 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, p~chMing, accounts payable, accounts receivable, cMh 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 ye~. Approval of Software Cost Benefit Analysis February 23, 2004 Page 2 Purchasing Currently the entire P~chMing process, from Requisition to P~chMe Order to Vendor Check, is a manual process with multiple points of duplicate data entry occurring both in the procuring dep~ent and the Finance Dep~ent. The procuring dep~ent creates a spreadsheet or paper-bMed p~chMe order and routes that to the Finance Dep~ent following its approval by the Dep~ent Head. The Finance Director must manually confirm that there ~e sufficient funds in the procuring Dep~ent's account and must individually approve all Requisitions. Those that exceed the p~chMing policy limits must be routed to the City Manager manually to be signed or placed on the City Council agenda for approval. The P~chMe Order is then sent to the vendor. Once the procuring dep~ent has matched the P~chMe Order with a Packing Slip and a Vendor Invoice it keeps one copy of the P~chMe Order for its records and sends another to the Finance Dep~ent with the Packing Slip and the Vendor Invoice. The Finance Dep~ent 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 p~chMed for placement in inventory ~e not monitored or accounted for until the end-of- the-ye~ inventory ofthe City's stockrooms. There is no acc~ate information pertaining to the loss or shrinkage of inventory items M no accounting occurs between p~chasing, inventory utilization, and ending inventory. Budgeting Currently budgets ~e prep~ed through the production of a combination of financial system and spreadsheet-bMed reports. Budget reports ~e printed out of the financial system and sent to each dep~ent. The Dep~ent Head then enters their request for next ye~'s budget on the form and returns the form to the Finance Dep~ent. The Finance Dep~ent then manually enters the fi~es 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 ye~. Payroll Each Dep~ent hM a payroll function. Someone manually tracks staffho~s, enters the information onto a paper-based form, hM the dep~ent or work group supervisor sign off on each form and then sends that form to the Finance Dep~ent at the end of every two weeks. The Finance Dep~ent must manually enter the time-sheet information into the Payroll system. Calculations for deductions (benefits, withholdings, etc.) ~e performed manually. Approval of Softw~e Cost Benefit Analysis February 23, 2004 Page 3 Utilities This operation hM a stand-alone billing package. Monies collected from utility bills ~e 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 dep~ents. This allows revenues and expenses to be tracked for each program, but the budgets ~e prep~ed on a dep~ent bMis. There is currently no way to get those programs summmzed in a dep~ent automatically. The Finance Dep~ent prints out reports and summmzes them in spreadsheets for distribution to the City Council and Dep~ent directors. Year End Close The ye~-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 CMh bMis instead of an accrual basis. This effort lMts 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 proced~al efficiencies: o Complete integration of the P~chMing process, from the creation of a Requisition, to a ~chMe Order, to a Vendor Check, to a Fixed Asset Record or an Inventory entry. This capability would enable the procuring dep~ent 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 p~chase order - a savings of approximately 250 to 500 ho~s of effort per ye~. The additional elimination of paper forms for the approval process will increMe acc~acy, ens~e compliance with the p~chMing policy, provide a detailed audit trail, and reduce errors and the potential for fraud. o Complete integration of the Payroll process from time entry, to creation ofthe Payroll Check to entry of the General Ledger Jo~al Entry and the creation of the Accounts Payable Checks for all withholdings and deductions. This capability would enable the Finance Dep~ent payroll clerk to spend less time on data entry. This would increase the acc~acy of payroll records and related transactions. This could result in a savings ofthree to five ho~s for each payroll cycle - a cumulative savings of approximately 100 to 150 hours per ye~. Approval of Softw~e Cost Benefit Analysis February 23, 2004 Page 4 ¡J Complete integration ofthe P~chMing with Inventory and Fixed Assets. This capability will result in maintenance of a more acc~ate relationship between p~chasing and inventory, a more acc~ate accounting of fixed Msets and infrMtrucMe depreciation. This would reduce the amount of Msistance required from HLB Tautges, Redpath and potentially save approximately $2,000 to $3,000 per year. ¡J 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 ho~s per billing cycle - a cumulative savings of approximately 60 to 120 ho~s per ye~. ¡J Real-time, on-line budget checking for the Dep~ental Managers. This would provide each dep~ent with inunediate confirmation of the availability of funds for encumbrance for Requisitions and P~chMe Orders. This ability would also eliminate the need for the Finance Dep~ent to print and distribute monthly Fund Balance reports to each Dep~ent. ¡J Comprehensive and flexible reporting options, which include multiple pre-defined "stand~d" reports, CAFR reporting tools, pre-defined "management" reports for Dep~ent 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 inunense flexibility in reporting and would simplify the distribution of those reports, possibly eliminating most printed copies of reports. This could save twenty to thirty ho~s per month - 240 to 360 hours per ye~. 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 increMed 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 ye~. Additional avoided costs include the inordinate amount of system maintenance currently provided by Rick Rice and Corey Gruenhagen. Softw~e that hM a simpler process for enhancements and bug fixes will save about 100 to 150 ho~s that they currently spend patching o~ system each ye~. These efficiencies will result in savings of$3,500 - $5,200 per ye~. The City will save in several miscellaneous ~eM such as printing costs and reduction in the amount of paper used for distribution of reporting information. These ~eM will result in a cumulative savings of approximately $2,000 to $3,000 per ye~. . . Approval ofSoftw~e Cost Benefit Analysis February 23, 2004 Page 5 The City will also benefit from the potential for more acc~ate, professional management of each Department's budget. The ability to utilize real-time information d~g the proc~ement process could result in reducing the need for budget adjustments and transfers. Value Justification Elements What will be measured? Time Savin s Miscellaneous Savin s Created throu rocedural efficiencies Printin, a er, softw~e maintenance and su ort Who is responsible? Finance Director and D ~ent Heads Finance Director and D ~ent Heads Finance Director and D ~ent Heads Finance Director and MIS Coordinator How much is possible? . Time Savings (minimum cumulative/city wide) 15-30 minutes per PO x 1,000 per ye~ = 250 to 500 hours 3-5 ho~s per payroll processing x 31 per ye~ = 93 to 155 ho~s 5-10 hours per utility bill x 12 cycles per ye~ = 60-120 ho~s 20-30 ho~s x 12 reporting cycles per ye~ = 240-360 hours 8-12 hours softw~e support x 12 months = 96-144 hours Total Time Savings = 739 - 1,279 hours saved per ye~ available for other activities . Miscellaneous Savings Avoided accounting Msistance costs $2-3,000 per ye~ PrintinglDistribution $2-3,000 per ye~ Intangible Benefits Decentralized Data Entry More efficient utilization of Finance Dep~ent personnel IncreMed accuracy in requisition and p~chMe order data entry Better management of funds by dep~ent managers Fewer budget amendments Simple, detailed, flexible report generation and distribution IncreMed citizen service levels '1 . Approval of Softw~e Cost Benefit Analysis February 23, 2004 Page 6 Will this investment pay for itself? This type of softw~e solution has been traditionally viewed M a cost of doing business. That is no longer necess~ly the situation. Because of to day's move tow~d more professional government management and the use of higher quality management tools (i.e. softw~e), Enterprise Reso~ce Planning solutions have been identified M valuable investments in the growth and infrastrucwe of the organization. The time savings, proced~al 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 ye~s. Recommendation Staff reconunends that the City Council approve the costlbenefit analysis and authorize the Finance Director to prep~e bid specifications and solicit bids for an integrated financial softw~e package.