Case Study: Easily Compare Actual Data with Budgeted and Planned Forecasts

Client Profile

Located just south of Boston, the City of Quincy is home to 88,000 residents. City water and sewer services are provided by Quincy’s Public Works department, which maintains 228 miles of water mains, and distributes ten million gallons each day via 23,000 service connections. Quincy has used MUNIS Utility Billing since 2006.

Challenge

For decades, Quincy’s Water and Sewer division had operated as part of the City’s general operating fund. In FY 2008 the City created a separate enterprise fund for water and sewer. Now facing greater accountability and transparency in its operations, Quincy commenced an end-to-end audit of its water and sewer operations.

Working with one of the region’s premier engineering firms, Woodard & Curran, Quincy began looking closely at consumption and billing records. The working group quickly became frustrated by the process of transferring its data from MUNIS to Excel, which was the program Quincy preferred to work with. In addition, the working group began to question the tables and selection criteria used as it attempted to gather reliable data from the database. With deadlines for several milestones looming, the engineering firm asked other MUNIS Utility Billing users to recommend an independent consultant to help with the project. Crystal Reporting Solutions was suggested.

Solution

The engineering firm contacted CRS and invited founder, Ed Bryan, to the group’s next meeting. Ed explained how MUNIS processes and stores its data and why the information they were looking for could best be generated using Crystal Reports.

As a result of that meeting, CRS was hired to produce for Quincy:

• A broad assessment of the MUNIS Utility Billing system, focusing on consumption, billing, accounts, and collections
• Monthly water and sewer commitment reports
• Reports to assist the group set rates and consider new block rate billing

With the reports produced, Quincy is now able to run the following reports on a regular basis:

• 12-month consumption and billing history for monthly commercial and quarterly residential accounts
• Hi / Low / Negative billed accounts each billing cycle — Pre- and Post-billing
• Monthly Commitment reports with data for consumption, amount billed, actual/estimated reads, adjustments, abatements, and refunds

Benefits

With the ability to export Crystal Reports to Excel, the end-to-end audit group received the data it wanted in a familiar format. This allowed Quincy to move forward with full confidence in reliable data to set rates for the new fiscal year.

The City now runs the reports regularly, accurately tracking consumption and billing throughout the year and is able to easily compare actual data with budgeted and planned forecasts. Quincy also now runs a series of reports representing the Monthly Commitment.

The City has successfully identified high usage accounts that should be billed in monthly commercial commitments, as well as problem accounts that have been estimated for too many cycles.

Quincy’s Water & Sewer division manager says, “We can access more of the information we need than ever before, and make the most of it. We can be sure the conclusions we draw are the right ones.”