Loading...
HomeMy WebLinkAboutAgenda Report - February 19, 1997 (60)4 ` CITY OF LODI COUNCIL COMMUNICATION AGENDA TITLE: Utility Billing Software - Request for Information and Pricing MEETING DATE: February 19, 1997 SUBMITTED BY: Finance Director RECOMMENDED ACTION: City Council authorize the Utility Billing Software Team to evaluate, select, and bring back a recommendation to purchase a Utility Billing Software product. BACKGROUND INFORMATION: As a result of Electric Industry Deregulation, the Utility Billing Software must be replaced with a sophisticated system sufficiently flexible and rapidly adaptable to both present and future customized rate structures, billing calculations, and reporting requirements. The existing Utility Billing System is over 20 years old and has had no major restructuring or improved design since installation in the mid 70's. Improved functions have been added to this software throughout the years, but the basic design of data files and programs remains very outdated. In it's current form, this software is extremely difficult to modify, particularly to the extent required to process complex rate structures and calculations of the type demanded by Electric Deregulation. The Council has approved contracts and rate structures as a part of the necessary process of working effectively with the customer. It is recognized these are mandatory actions and that other such contracts and/or restructuring will be necessary. These kinds of actions, which are essential to the well being of the City of Lodi, will place a heavy demand on the most sophisticated of Software; outdated software will not stand up under this kind of pressure. Our existing Utility Billing Software has served the City very well for many years. It is a system designed for a time when requirements were not so complicated. To rewrite this system with existing staff would be an undertaking of enormous magnitude, involving several years dedicated to this task exclusively. When compared to the expected cost in the range of $200,000 to $250,000 for Utility Billing Software, the option to purchase emerges as the practical alternative. The Utility Billing Software Team consists of: Layton Bull, Maxine Cadwallader, Jim Doyle, Vicky McAthie, Tyson Mordhorst, Jack Stone, and Roy Todd (team activity has been coordinated with the Water and Sewer Utilities). It is the goal of this team to evaluate and select a Utility Billing Software product, and bring a recommendation to purchase this package to the Council on March 19, 1997. FUNDING: Capital Improvement Budget, Electric Utility, Water Utility, Sewer Utility Vicky McAthie Finance Director Prepared by Roy Todd, Data Processing Manager Attachments APPROVED: H. Dixon Flynn -- City Manager 11. CITY COUNCIL PHILLIP A. PENNINO, Mayor JACK A. SIEGLOCK Mayor Pro Tempore KEITH LAND STEPHEN J. MANN DAVID P. WARNER (date) (Vendor Name) (Address) (City State Zip) CITY OF LODI 212 WEST PINE STREET P.O. BOX 3006 LODI, CALIFORNIA 95241-1910 FAX (209) 333-6795 SUBJECT: REQUEST FOR INFORMATION & PRICING H. DIXON FLYNN City Manager JENNIFER M. PERRIN City Clerk RANDALL A. HAYS City Attorney The City of Lodi is requesting information from vendors who offer a feature rich Utility Billing software product which is designed to handle a full range of utility services, including electric, water, sewer, refuse, telecommunications, and gas. While it is extremely desirable that this product be integrated with a full suite of financial software for city governments, this is not considered mandatory at this time. This information will be used to develop a Capital Project plan and request for funding, please do not underestimate your pricing. At this time, vendors will not be excluded based on the total dollar value of their bid or the availability of other software products. Please provide detailed pricing for Utility Billing software and less specific pricing for additional applications. Utility Billing is our initial software upgrade with subsequent applications to follow as quickly as possible. Pricing should be provided by module. Identify separately any module in development or implemented solely at beta sites. For each module also include a price for future software upgrades and five years of annual maintenance, by year. Training, consulting, project management /preimplementation planning / fit analysis, etc. should be priced separately detailing rates. Attachments are provided to assist in the effort to acquire information. Our goal is to use this information in moving quickly to a software decision. While these attachments will be helpful in this process, we do not intend to rely solely on these forms. We will be in contact with vendors as well as talking with other customers and otherwise evaluating Utility Billing software. 02/10/97 E:IUBRFI-LH.DOC Subject: Request for Information & Pricing Page 2 General City Information: Population 55,000 Number of City Employees: Regular 400 Finance 35 MIS 5 Current Technical Environment: Computing Platforms: Current Application Processor System: Make / Model IBM AS/400 Model E45 RAM 80MB Disk 9.5GB Personal Computers: There are currently approximately 100 PC's in use within the organization. The majority are IBM Clones, with some IBM & HP. The City's primary PC operating environment is Windows 3.1. The City's standard PC desktop software is Microsoft Office. Estimated Concurrent User Base: With the current number of employees and level of services offered, a new Utility Billing system would be used by an estimated 40 concurrent users. Networking & Infrastructure: The City of Lodi has in place a partial infrastructure to support networking of its computing platforms. Networks are not currently connected. Limited fiber optic cabling provides some connection along with telephone leased lines. There are 5 independent PC LAN's. The primary network software is Novell Netware version 3.x. Subject: Request for Information & Pricing Page 3 Milestones: Response to RFI due no later than March 06, 1997. Target Dates: Mail RFI Requested RFI Response Council Approval UB Software Purchase February 06, 1997 February 19, 1997 March 19, 1997 March 20, 1997 Conversion To be planned with Vendor Begin Training To be planned with Vendor Implementation To be planned with Vendor "Go Live" January 1, 1998 Thank you for your participation in this effort. If you have any questions concerning this RFI, please contact Roy Todd at (209) 333-6847. Additional contacts: Layton Bull at (209) 333-6761 ext. 688 -or- Tyson Mordhorst at (209) 333-6761 ext. 654. Responses may be faxed to (209) 333-6795. Sincerely, Attachments: • Financial System Requirements • Utilty Billing Volume Indicators • Utility Billing Features and Functions • RFI: Vendor Questions City of Lodi Request for Information Requirements Financial System Requirements: Required Application: Utility Billing Desirable Integrated Applications: General Ledger (Fund Accounting, including CAFR) Budget Preparation / Cost Allocation Project Accounting Payroll & Human Resources Applicant Tracking Accounts Payable Purchasing/ Inventory Central Cashiering Accounts Receivable Business License Fixed Assets Building Permits Work Orders/Facility Management Optional Applications: Fleet Management Parks & Recreation Code Enforcement Required Utility Billing Features: (checklist included with RFI) Modular, flexible design allowing rapid modification Graphical user interface Flexible history maintenance and retention Capability to schedule batch processes (Billing, etc.) Location based Service Address independent of Customer Master Desired Utility Billing Features: Full Integration "Drill Down" to source data with graphical presentation Fully developed use of customer credit history Interactive Voice response capability for customers City of Lodi Request for Information Volume Indicators Utility Billing Volume Indicators: Annroximate Per Month Electric: Customer accounts 26.000 Residential customers 18.000 Commercial customers 3.000 Industrial customers 50 Meters installed 22,000 Meters in inventory 23.000 Bills per billing cycle 1,300 Billing cycles 19 Water: Customer accounts 26.000 Residential customers 23,500 Commercial customers 875 Industrial customers 50 Water Meters installed 925 Meters in inventory 1,000 Bills per billing cycle 1,300 Billing cycles 19 Sewer: Customer accounts 26,000 Residential customers 23.500 Commercial customers 875 Industrial customers 50 Bills per billing cycle 1,300 Billing cycles 19 Refuse: Customer accounts 26.000 Residential customers 19.000 Commercial Customers 1,200 Industrial Customers 50 Bills per Billing Cycle 1,050 Billing cycles 19 City of Lodi Utility Billing Features & Functions Vendor: Please complete the Utility Billing features and functions list. Use the "Comments" area to explain any exceptions or additional features of the category. If you need additional space, attach pages as needed for your comments. Please include the line number in your response and mark the "Additional Comments Attached" column. (Service Service Components Service Location Customer Account Summary Account Agency Aggregate 'Contract 2/10/1997 Definitions: Any 1 service (Electric, gas, water, sewer, refuse, telecommunications) Each service may have multiple components with each one provided by a different supplier (Electric: generation, transmission, distribution, ...) A master item that ties to Land/Parcel/GIS Dictionary A single person or entity (corporation, partnership) financially responsible for bill 1 or more Customers, 1 mailing address, 1 Service Location, 1 or more Services 1Group & summarize for Customer multiple Accounts 'Organization or Entity for which bill is being prepared 1Group of customers collectively using a 1 or more Service Component 1Exclusive rate schedule designed for the customer's requirements Page 1 of 10 Line No. City of Lodi Utility Billing Features & Functions Vendor: Feature & Functions Yes No Comments 102 103 104 1 105 Support CISC & RISC processors Path from CISC to RISC Do you support OS/400 V2R3 Any OS/400 V3 dependencies? 110 Securityltk4}} 111 By user, department, level of responsibility 112 Integrated user -defined custom menus 113 Are menus generated from SDA? WO, EXeattof Rna i Valli ., §2Y 121 Sophisticated functions including summary, detail, grouping, "drill -down', math & string functions, sequencing, interactive & programmable selection criteria, etc. 122 Multiple output formats including screen, line & page print, export to file, export to popular PC -Windows applications (MS Office) 123 Interactive & batch execution 124 Federal Energy Regulatory Commission (FERC) Reporting Ut lique tVumbeir %` in' gape 202 Agency Number (Multi -Agency capability)? 203 Customer Number? 204 Service Location Number? 205 Meter Number? 206 Account Number? 207 (Summary Account Number? Page 2 of 10 Additional Comments Attached' City of Lodi Utility Billing Features & Functions Vendor: Line No. Feature & Functions Yes No Comments 208 Aggregation (Group of Accounts or Customers) Number? 209 Service Component Number? 210 Service Component Vendor Number? 211 220. 221 222 223 Maintain Customer Number for other utility suppliers (Customer gets service from City of Lodi & PG&E) Customer nformationx• Account, Customer, Property Owner, Landlord/Property Manager independent profiles Customer: Name, Mailing Address, Social Security Number, Driver Classifications Deposit information (amount, date, status) by Customer per Account 224 Customer's Bank information (Bank Number, Account Number, etc.) needed for automatic bank drafting 225 Owner/Landlord/Property Manager Service Profile by Service Location: Owner/Landlord/Property Manager versus tenant 226 IDo you have a separate corporate name field? 227 'Group and reference Customers by user -defined criteria 228 'Customers in unlimited number of Groups 240;'Seniice'Location<Information 241 Service Location Information (Land/parcel/GIS): Street/Address dictionary, Parcel No., free form notes 242 Structure Information (House, Apts, Duplex, School, Commercial/Retail, Manufacturer, etc.) 243 'Multiple user -defined special districts coding (i.e. Revitalization District) 244 'Utility Zone coding, Land Zone, Land Use, Subdivision 280 Accountinfoiination. 261 Account and Summary Account Status - Active, Inactive, Final, Collections Paae3of 10 Additional Comments Attached City of Lodi Utility Billing Features & Functions Vendor: Line No. Feature & Functions Yes No Comments 262 Account and Summary Account Billing and Payment Status - Billed, Paid, Paid partial, Delinquent 263 Link and reference other information by Account: All Customers by this Service Location, All Accounts with this Customer 264 Group and reference Accounts by user -defined criteria 265 Accounts in unlimited number of Groups 302 Complex and sophisticated date -sensitive rate structuring using multiple Service Components, including tiered pricing with minimum charge. 303 Service Components billed to different Customers 304 Service Component supplier (Customers may choose to purchase Service Components or combination of Service Components from different suppliers) 305 Aggregation of Accounts 306 Real Time Pricing - the hourly price would vary with the price of the Power Exchange or some other standard 307 Aggregate Service Locations for same Customer or unlimited Accounts, while maintaining individual and aggregate history 308 Customer discounts applied to an individual Account or group of Accounts (i.e. Medical, Low Income, Senior Discount) 309 Customer discounts based on Service Location (i.e. Revitalization District) 310 Surcharges applied to Customer or groups of Customers based upon date and/or Service Location 311 Temporary Service Component Override controlled by start/stop dates applied to Customer, group of Customers and/or Service Location Districts 312 Custom contract rate for an unlimited amount of Accounts, unlimited rate schedules 313 Unlimited number of billing calculations Page 4 of 10 Additional Comments Attached City of Lodi Utility Billing Features & Functions Vendor: Line No. Feature & Functions Yes No Comments 314 Flat charges per Account and/or per quantity 315 Tiered rates for set multiple billing quantity for cycle (Le. kw, kwh) 316 Tiered rates for set multiple billing quantity for number of days in cycle 317 Tiered rates based on time of use 318 Tiered rates using declining multiple billing quantity 319 Tiered rates using inverted multiple billing quantity 320 Rebates or credits by flat rate or usage 321 All rates date sensitive, Error when dates conflict 322 User controlled application of discounts? Prioritize? Cumulative? 323 Duplicate competitors rates and show as comparison to Customer 402 Cycle billing (daily) on a monthly basis - modify cycle/routes provision 403 'Notice for Customer read (Dial cards) 404 'Interface with existing ltron hand held meter reading system 405 'Collect and translate pulse data from recorders over phone lines and convert into billing determinants by time of use periods 406 'Reading source (Itron, Estimate, Call -In), type (Normal, Re -read) 407 'History of all readings, note which reading used in Billing 408 'Multiple meters for Electric - including demand & time -of -day meters per Account Multiple meters for Water per Account Multiple meters for Gas per Account Multiple time sensitive connections for Telecommunications per Account Usage calculation includes meter changes Date -sensitive processing of multiple meter readings in a cycle Process multiple time -of -day readings (at least four) 409 410 411 412 ' 413 ' 414 Page 5 of 10 Additional Comments Attached Line No. City of Lodi Utility Billing Features & Functions Vendor: Feature & Functions Yes No Comments 415 Flexible user -defined meter readings analysis threshold values (i.e. seasonal) 416 Automatic analysis of meter readings, preparing required Service Orders (Customer "Can't Reads"/"No Access") or "Re-read"/"Check Read" /� Service Orders after Usage analysis Lt4,39 r BiRNI: 421 Change any/all service terms/rates at any time with automatic proration 422 Multiple customers (connects/disconnects) per monthly Billing Cycle per Service Location 423 Every Customer Billed each month 424 Cancel bill & re -bill 425 Cancel bill & re -bill automatically adjusts Account 426 Bill Account interactively 427 Billing Register for each Billing period 428 Multiple donations (not receivables), flexible customer selection 429 (Account & Summary Account date -sensitive billing options: budget, estimated, vacation, levelized 430 Batch Billing while users logged -on 431 Provision for voltage discount 432 Provision for power factor adjustment 433 Provision for Economic Stimulus Rate Credit 434 Provision for purchased power cost adjustment 435 Provision for seasonal rates 436 Provision for dusk -to -dawn lights 437 'Application of late charge by user -defined criteria (by amount, by age, by date) 438 'Override user -defined charges (i.e. late charge, etc.) 439 'Retroactive adjustments of incorrect billings Page 6 of 10 Additional Comments Attached' Line No. City of Lodi Utility Billing Features & Functions Vendor: Feature & Functions 440 Contract Billing: Contract charges compared to non -contract charges 450 l6.ustieilts' 451 Manual adjustment of all billing components and amounts 452 User defined adjustments (start/stop date) 453 Compute & prorate opening and closing bills on demand 454 User defined delinquency terms based on customer or group 455 Adjustment Register per Adjustment period 456 Date -sensitive and variable Adjustment Register 460 461 Bar coding: Agency, Account, Amount 462 Bills: Report prior year same period usage 463 Bilis: Provide detail of current consumption/rates/charges 464 Bills: Allow multi -line user defined customer messages - vary by group 465 Bills: Provide detail of components in billing calculation including provider of service, changes, etc. 466 Print bills on demand by individual, date, group, etc. 467 Multiple types of billing formats (Le. postcards, mailers, staffers) 468 Print multiple bills to same or different address - either in individual or "summary" form 469 Print bills by postal carrier route 470 Delinquent bills and register 471 Third party notification multiple letter/message formats Yes No Comments Additional Comments Attached -9 501 Cash drawer & batch registers and balancing (anytime and end -of -shift) 502 Automatic bank drafting 503 Allocation of payments to appropriate funds automatically or by manual override (including Summary Account) Page 7 of 10 City of Lodi Utility Billing Features & Functions Vendor: Line No. Feature & Functions Yes No Comments 504 Deferred payment arrangements including dates, amounts and details 505 Provision for guaranteed payments 506 Automatic management or override of delinquency with deferred payment arrangements 507 Multiple user -defined miscellaneous fees at different intervals (Pet Licenses once a year, Impact Fee per month) 508 Payments Register 509 Adjustment Register 510 Accounts Receivable Aging Report 511 Returned check letter handling, user defined multiple format letters, returned check charge 512 Maintain credit history - compute credit rating based upon payment history by user defined criteria 513 Forward portion of payment to Service Component supplier 601 Deposits - track receipt, refund, application to balance, transfer, etc. - maintain referenceable history & audit trail 602 Calculation deposit interest by user -defined criteria 603 Bill deposits, receive deposit with start of billing 604 User -defined deposit allocation with both automatic application to balance/transfer/refund or manual override 605 Deposit Register 606 Refund checks and Register 607 Split deposit to one or more customers for any amount 608 Split transfer of deposit to one or more customers for any amount 701 Connect/Disconnect Order at customer contact Page 8 of 10 Additional Comments Attachedb Line No. City of Lodi Utility Billing Features & Functions Vendor: Additional Comments Attached'U Feature & Functions Yes No Comments 702 Cross reference new Customer with existing Customers by name, Social Security Number, Drivers License, Telephone Number or other user - defined criteria 703 Reference credit rating on new Account for a repeat Customer 704 Access to external credit rating service 705 Automatically transfer rental service to Owner/Landlord/Property Manager 706 Account information transferred with Customer by user control (Meter Reader note of "Dog" with Customer, meter location with Service Location. aged Account balances, delinquency, deposit) 707 Ability to reference and control shutoff process - include payment arrangements, reviewable before action 708 Re -read orders: Customer inquiry, "Can't Read" /"Customer No -Access" letters 709 Service Orders Register 710 Provision for fees charged for shut-off and re -connect 711 On-line access and control of Service Orders 801 Meter Information: Number, vendor, model, type, size, phase, dials, multiplier, purchase order number & date, invoice number & date, etc. 802 Meter Service Information: Life of Meter History, install date, Service Location, inspect dates, change date, etc. 903 User & menu defined (flexible) inquiry accesses and options 904 Histories & audit trails on-line, display/print on demand with interactive and programmed selection and sequencing options Page 9 of 10 Line No. City of Lodi Utility Billing Features & Functions Vendor: Feature & Functions Yes No Comments 905 Maintenance histories: Meter readings, consumption, life of meter, customer changes, location changes, service changes, billings and corrections, etc. 906 Ability to include/exclude history and selected information 907 User controlled history with unlimited retention 920 " Other=Functions: �r f i' 4; Ira ss" s�41R:..s,, .„ fir. 921 Log all Customer contact with date, time, clerk, reason for contact, information given, free form additional notes 922 Account inquiry ability showing charges by service & date, history, credit rating, meter information, location, etc. 923 Open balance report (show unpaid balances selectively) - on-line access to open balances with selectivity 924 Prepare refuse fees register for payment to refuse contractor 925 Re -read activity Log & History 926 Third party notification Log & History 927 Archive and retrieval 928 Reports & forms designed for line and laser printers 929 Automatic Account cancellation and transfer to credit bureau by user - defined criteria Pale 10 of 10 Additional Comments Attachedb City of Lodi RFI: Vendor Questions Vendor: Please respond to the following questions. List the number of the question on a separate sheet of paper along with your response. Support Issues 1. Do you do the initial setup of the software (creating tables, user codes, loading data, etc.) or do you contract with another company? 2. Does your company schedule and do the training for our technical and user staff or do you contract with another company? 3. How do you handle technical and user support? Do we have on-line access to your staff (ability to upload and/or download code, reports, documents, etc.)? What hours are available for support (5 days per week - 8 hours per day, 7 days per week - 24 hours per day, etc.)? What is your guaranteed response time to user problems (program defects, failures, etc.)? 4. How are updates (code replacements due to failure or bugs, etc.) to utility billing handled? 5. How are upgrades (new versions or releases) to utility billing handled? 6. How often do you issue new releases to utility billing (once a year, every two years, etc.)? Is this schedule guaranteed? 7. Is the source code supplied with the application? 8. Do you support user code? If so, how? 9. What is the level of technical support you provide? Do you provide hardware and/or communications advice or consulting (LAN / Wan / Remote Access / Internet / Kiosk, etc.)? How well do you adapt to distributed processing ? 10. What are your charges for additional or supplemental user and technical staff training? Are training classes offsite, onsite? Where are your offsite training centers located? Are there discounted training packages available? What is your training philosophy (train the trainers, train the users, etc.)? Is there on-line or Internet training available? Page 1 of 4 City of Lodi RFI: Vendor Questions Vendor: 11. Where would our site be supported from? Is it just phone support or does this support location have staff that can be scheduled to visit out site? 12. Is there a user group for utility billing? Is there one in our area (Northern California) ? How many customers are active in our area? Where do they meet? Are there national as well as regional groups? How often do they meet? Does your company attend these meetings? What type of support do you provide to the user groups? Please submit a list of customers along with a contact name and phone number. 14. How is maintenance charged? Are there discounts available for purchases of multiple modules (Accounts Receivable / Account Payable, General Accounting, etc.)? What are the charges for customized code? Is there an annual fee thereafter for the customized code? Is the customized code retained in new versions of utility billing? Would the customized code be available on the same time schedule as the regular release? 15. We are currently on a CISC based AS/400. If we decide to move to a RISC based AS/400 later, are there any additional charges? 16. What other platforms (HP midrange, PC based, etc.) do you support? 17. Does your software require licenses based of the number of concurrent users, size of CPU? If we move to a larger CPU, is there a charge? Page 2 of 4 City of Lodi RFI: Vendor Questions Vendor: Technical Issues 18. Is IVR (Interactive Voice Response) supported for utility billing? What hardware is required? 19. Is customer access by the Internet supported? By Kiosk? What hardware is required? 21. Does the system maintain statistics on number of transactions handled (types of transactions, by employee, etc.)? Can the statistics be referenced (detail or in summary) by day, week, month, employee, transaction type, etc.? 22. Is OS/400 V2R3 supported for utility billing? If not, what is the level of operating system necessary to support utility billing on the AS/400? If a new level of OS/400 is released, how long does it take you to be current? Please answer the same questions concerning PC's. 23. Does your utility billing have client/server front end? If so, please explain the environment in which it works (hardware, software, supplied by whom)? 24. Does your utility billing have a GUI front end? If so, please explain the environment in which it works (hardware, software, supplied by whom)? 25. How do you prioritize customization requests? Is it first come, first served? Is it based on size of customer? Please explain. 26. How do you accommodate networked PC's? Do they all require special software or hardware to be able to access the utility billing system? 27. What is the average amount of time necessary to install, setup, and go live with utility billing? How is this timeline effected by addition vendor support? 28. Are there user defined fields available? If so, what are the limits (size, number, etc.)? 29. Does the utility billing have imaging? If so, is special hardware or software necessary to view the images? 30. Does the utility billing system use DB2/400 for its data base? If so, does it use the full functionally of the database (data dictionary, triggers, etc.)? If you do not use DB2/400, what do you use? 31. Does the utility billing system use a data warehouse? Page 3 of 4 City of Lodi RFI: Vendor Questions Vendor: 32. Did your company use a Case tool to develop utility billing? If so, which one and is it supplied with the package? 33. Please explain any built-in backup procedures (Entire database is backed up, changes only, etc.). Are journals used? 34. Please explain any built-in database restore procedures used in utility billing. 35. Does the utility billing system have built-in functions such as forecasting, projections, modeling, etc.? 36. Can the utility billing system do mass changes (grouping, rate type, business class, etc.)? What type of mass changes are possible? 37. How do you handle multiple register meters? 38. How do you account for "Lost" kilowatts (power not billed)? Page 4 of 4