From Legacy Platform to Best-in-Class Suite

OVERVIEW

This utility provides electric, water, and sewer services to thousands of customers across multiple communities. Their billing and accounting operations ran on a legacy SQL-based platform that had been in place for many years. While the system could generate accurate bills, it lacked the flexibility, integration capabilities, and modern functionality that the utility needed to grow. Critical business processes — from job costing to payroll to inventory management — were being handled through spreadsheets, paper forms, and manual data entry. Raybern was engaged to perform a comprehensive business process assessment, forensic data analysis, and vendor-neutral system selection to identify and procure a right-sized solution suite.

Raybern began with an exhaustive review of every business process across the utility's operations. Through multiple on-site workshops and interviews with staff at every level — from customer service representatives to field technicians to management — Raybern documented the current state of operations in granular detail. The findings revealed a utility that had evolved extensive workarounds to compensate for the limitations of their legacy platform. Job costing was handled entirely outside the billing system through Excel spreadsheets, with fees manually transferred into the billing platform as miscellaneous charges. Connection fees were paid in advance and tracked on spreadsheets, then tied to service locations manually — with physical records kept in three-ring binders. Payroll time entry was collected on paper timesheets, tracked and manipulated in spreadsheets, and then manually entered into the billing system against the appropriate general ledger codes. Service orders were printed from the billing platform on paper, filled out manually by field personnel, then returned to the office where data was keyed back into the system. Routine maintenance was assigned verbally with no formal work orders created against the utility's assets.

Raybern's analysis of the legacy platform's data structure revealed fundamental limitations that were constraining the utility's operations. The database used a very linear structure with separate tables for customer, premise, and meter data, but the cross-referencing between tables was limited and inconsistent. Owner IDs matched Location IDs, meaning that as customers moved from one location to another, the utility had no mechanism to track their account and payment history. Move-in and move-out dates were not recorded. Customer statuses were not tracked. The system included no standard financial reports — staff spent significant time creating required financial reports manually in Excel. Custom reports required additional development cost and specialized training. Reporting could be performed through the raw database tables, but only some team members had access, and the data required extensive manual manipulation to format appropriately.

Raybern performed a deep forensic data analysis across the utility's billing, metering, and accounting data. The analysis examined customer master records, location master records, meter master records, and the connection tables that linked them together. Across more than 11,000 customer records, Raybern found pervasive inconsistencies in how data was entered and maintained. Write-offs were notated inconsistently — some by adding 'WR' or 'WO' prefixes to customer IDs, others by inserting 'Write Off' into name fields, and others by assigning accounts to specific route numbers. There was no customer status field to properly track account states. Address data was entered inconsistently across multiple address fields, with 57 customers having no address in any field. Street names used varied abbreviations (Rd vs Road, Ave vs Avenue) with no standardization. The Location Master table had no GIS coordinates populated, no address names (only references to a separate Street Index table), and no status field. Raybern found one location ID entered as 'donotgivepower' and several others with anomalous suffixes. The Meter Master table contained more records than the connection or location tables, with no status field to indicate whether meters were in inventory, removed, out for repair, or currently installed. Meter manufacturers were misspelled inconsistently ('Sensus' vs 'Sebsus' vs 'Senses'), 334 water and sewer meters had no size listed, and dial counts included anomalous values of 0, 1, 2, 50, and other unexpected numbers that could affect billing accuracy.

With a comprehensive understanding of the utility's requirements, pain points, and data challenges, Raybern compiled the business requirements and identified multiple platform solutions that could meet the utility's goals. Rather than defaulting to a single enterprise solution, Raybern evaluated both ERP approaches and best-in-class suite approaches. Two ERP vendors were identified that could provide utility billing, customer portal, accounting, payroll, asset management, digital work orders, inventory, purchasing, and project costing in a single platform. A best-in-class suite was also assembled combining three specialized vendors: one for utility billing and customer engagement, one for financial accounting and payroll, and one for asset management, work orders, and inventory. Raybern organized and facilitated multiple rounds of demonstrations with the utility's assembled stakeholder team — including the general manager, customer service supervisor, accountant, distribution supervisors, and treatment supervisor. Over the course of several months, each vendor presented their solution multiple times, with Raybern ensuring that demonstrations addressed the utility's specific workflows and requirements rather than generic sales presentations.

After working with the utility to vet all potential vendors, Raybern recommended a best-in-class suite that was right-sized for the utility and scalable to accommodate future growth. The recommended suite included: a cloud-based financial accounting platform recognized as the preferred accounting software by the American Institute of Certified Professional Accountants (AICPA), providing flexible reporting that would eliminate manual Excel-based financial reporting; a specialized cloud-based utility billing platform focused on the water industry, enabling real-time customer portal integration, document storage tied directly to customer accounts, and the capability to handle demand billing and solar credits; a GIS-based asset management platform combining work orders, service requests, project costing, and inventory in a single tool with a native mobile application for field technicians; and a comprehensive cloud-based payroll platform with time entry, PTO tracking, W-2 processing, and a human resources benefits module. The suite was designed so that every function previously handled through spreadsheets, paper forms, and manual data entry would now be managed within integrated, cloud-based platforms with automated data transfers between systems. The total implementation cost was structured with one-time capital expenditures amortized over 7-10 years and annual subscription fees as operating expenses — replacing the utility's existing platform costs while delivering dramatically expanded functionality.