EFC Resources: Water and Wastewater Rates Analysis Model

Water and Wastewater Rates Analysis Model

Subject: Budget, Costs, Financial Data, Rates, Reserves, Revenues
Program: Drinking Water and Wastewater
Tool type: Calculator
Date: 01/12/2017
Version: 2.8.3
File: Click to download (requires registration)   (MS Excel)

Use this tool to help set water and/or wastewater rates next year by projecting the utility's expenses, revenues from rates, and fund balance for the next few years. Uniform ("flat") and block rate structures for up to 12 rate structures can be assessed. Data inputs are minimal, and the tool has been updated to offer several new features and improvements to make it easier to test scenarios and help determine whether the utility needs to adjust rates in order to achieve financial sufficiency. This tool is designed specifically for small water/wastewater systems and should be used annually. The tool is free to download and use.


How to use the tool:

Follow the built-in instructions to input the utility's current rates and rate structure details, proposed new rates and rate structures, number of accounts, growth rate, water consumption (by block) and basic utility expenses. The model will then project the expected net profit/loss each year for the next few years under current and proposed rates, as well as the end-of-year fund balance for the next several years. Input data in the dark green cells. Results are presented in easy-to-read graphs and table showing annual revenues, expenses and fund balance projections.


Video demonstration of how to use an older version of the tool:

On February 26, 2014, we presented a national webinar demonstrating how to use version 2.6 of this tool. Although the format and some of the inputs and outputs of the tool have changed since that version (look below for a list of recent updates to the tool), the demonstration on the basic functions of the tool are still relevant. Watch the video for a visual demonstration on the basics of using the tool, if not the exact instructions. 



What's new in version 2.8.3?

  • Correction to the Compare Monthly Bills graphs to account for consumption allowances and uniform rate structures
  • Removed two graphs
  • Minor formatting changes 

What's new in version 2.8.2?

  • User can input projected end-of-year fund balance as well as start-of-year
  • Improvements to print areas and minor formatting changes

What's new in version 2.8.1?

  • Navigation buttons and labels guide the user through the tool
  • Newly-added error notifications help user recognize when rates and rate structure details are entered incorrectly
  • You may now adjust rates in each of the next five years (instead of only one year)
  • You may now enter up to 12 rate structures
  • Rate structures can now be named by the user (e.g. residential water inside, residential sewer outside, commercial water 2" meter, industrial, etc.)
  • Specify details on accounts, trends in water use, and uncollected bills for each rate structure
  • Block rates can accommodate up to 10 blocks (up from 5)
  • Consumption allowances included with base charges are easier to input
  • Use dropdown menus to generate graphs comparing the monthly bills at various consumption levels under the existing rates and new rates 
  • Add revenue needs to build-up utility's reserves
  • Add an end-of-year minimum fund balance target
  • New graphs revealing projected expenses and revenues under existing and new rates, focusing on next five years
  • Improved instructions in calculating water use (sales) by block
  • Various bug fixes and improvements to calculations and format, including updated Instructions page

Download the Spanish Version of the Tool (version 2.8.2)


Project page: Smart Management for Small Water Systems

Project page: North Carolina Water System Capacity Development Support

Project page: North Carolina Water and Wastewater Rates, Rate Structures and Connection Fees

Project page: Georgia Water and Wastewater Rates, Rate Structures and Connection Fees

projecting revenues, rates, cash flow, budget