From Excel to a water billing and collections system
The water utility of a municipality in Durango billed and collected payments for 18,400 service connections using Excel spreadsheets. We developed a web system with a customer registry, contracts, metered and flat rates, cashier stations, bills and past-due reports.
- Industry
- Government & public utilities
- Location
- Durango
- Year
- 2023
- Timeline
- 16 weeks
- Services
- Custom software
For the drinking water utility of a municipality in Durango, we developed a web system in PHP and MySQL that replaced the Excel files: customer registry, contracts, metered and flat rates, billing, cashier payments, bills, reports and accounts receivable aging. Issuing bills went from 6 days to 1, and debt more than 90 days past due fell 23%.
Where they started
The utility serves the county seat and 14 smaller towns. Its customer registry lived in one Excel file per sector, with formulas only one person understood. Every two months, three data-entry clerks spent six days matching meter readings, applying rates and putting together bills in Word.
At the counter, payments were taken with printed receipts and a notebook; the daily cash closeout took two hours and sometimes didn't balance. There was no reliable way to know who owed money, since when or how much, so debt-relief programs were designed blind.
What we built
We developed a custom web system in PHP 8 and MySQL, installed on the utility's own server with an encrypted off-site backup. The registry brings together customers, service connections, contracts, sectors, meter-reading routes and service type: residential, commercial, industrial and public.
The rate engine handles metered service by consumption tiers and flat rates, plus sewer, wastewater treatment, late fees and discounts for retirees and people with disabilities, all configurable by the utility itself whenever the municipal revenue law changes. Readings are entered by route or imported from the meter readers' spreadsheet.
At the cashier station, staff search by account number or name, take partial or full payments and the system prints a numbered receipt; closeout is generated automatically by cashier and by day. Reports include revenue by period, the registry by sector and accounts receivable aging at 30, 60, 90 and over 180 days, which is the basis for collection programs.
- Customer and connection registry migrated from Excel
- Contracts and new-service module
- Engine for metered and flat rates
- Meter-reading entry and import by route
- Billing and bill issuance by period
- Cashier module with automatic closeout
- Revenue and accounts receivable aging reports
- Change log by user
- Training for cashiers and data-entry staff
How it came together
- 01
Requirements
Interviews with billing, cashiers and management; review of the revenue law and the Excel files.
Weeks 1–2 - 02
Registry and migration
Cleanup of 18,400 records, duplicates and connections with no sector.
Weeks 3–6 - 03
Rates and billing
Rate engine, readings, bills and validation against previous periods.
Weeks 7–11 - 04
Cashiers and reports
Payments, closeout, numbered receipts and receivables aging.
Weeks 12–14 - 05
Parallel go-live
One full billing period running alongside Excel before retiring it.
Weeks 15–16
The numbers
Issuing each period's bills went from six days to one, and the daily cash closeout from two hours to 15 minutes. Cleaning up the registry turned up 612 active connections that weren't being billed.
With the receivables aging report, the utility built a debt-relief program aimed at accounts more than 90 days behind; in the first year that debt fell from 34% to 26% of total receivables.
“Closing each billing period used to mean a week of overtime. Now we do it in one day, and anyone in the department can use the system, not just one person.”
Questions about this project
What modules does a billing system for a water utility need?
Can the customer registry be migrated from Excel without losing data?
Should the new system run in parallel with the old one?
Does your business need something similar?
Tell us where you are today and we'll tell you what we'd build, how long it would take and how we'd measure it.
We reply within one business day.