Community Sustainability ToolCommunity Sustainability Tool logo

WSU Environmental Finance Center's Community Sustainability Tool helps small communities predict the affordability of investment in water infrastructure upgrades. The Community Sustainability Tool is specifically designed for Iowa, Kansas, Missouri and Nebraska. The tool is a dynamic financial modeling program that uses information collected by the U.S. Census Bureau’s American Community Survey and other sources to predict the affordability of water and wastewater utility investments for communities using estimates of income, population, employment trends and other information.

The model was developed for the EFC by former Wichita State University professor Dr. Kenneth A. Kriz and his graduate assistant Debrapriya Banerjee.

“We believe the Community Sustainability Tool is a great planning resource for communities and others to use when faced with the challenges of ensuring safe, clean drinking water for their customers, as well as wastewater management to protect the environment for generations to come,” said Dr. Kriz, who now works at the University of Illinois-Springfield.


How does it work?

The Community Sustainability Tool is a Microsoft Excel-based tool that creates estimates of the projected rates for water, wastewater and an overall summary for both services, if applicable. Users input unique data about their community, such as average monthly water bill amounts, estimated expenditures on infrastructure, etc. The tool then creates estimates for future water utility rates and compares the community’s predicted ability to sustain the funding in the coming years. The affordability predictor feature gives the tool a unique advantage over standard models of infrastructure investment tools. Along with numerical data, a color-coded “stoplight” feature is displayed that indicates affordability risk categories of “High" (Red), “Moderate” (Yellow), and “Low” (Green). 


How to use the tool

1. When the Rural Community Sustainability Tool Excel file is opened, the user will be taken to a page with a short set of instructions. Clicking on “Begin” takes you to the "Model Inputs" tab (shown below).

screenshot of the tool model input tab

2. The top-third of the model Input tab consists of a pull-down menu for the community being modeled, and user input for:

  • The amount of the current average household drinking water and wastewater bills (which should be entered separately due to two different affordability guidelines).
  • The estimated future growth rate of drinking water and wastewater bills that is NOT dependent on the proposed infrastructure investment being modeled (e.g. ordinance driven annual rate increases).

3. The middle portion of the Models Input tab asks for information on the proposed infrastructure investment including the total planned expenditure and the number of years that the investment will be financed over.

4. The final portion asks about infrastructure finance cost parameters. If the "Yes" option is chosen for the "Default Rate" question (in cell B14), the model will use the calculated average interest rate for municipal bonds with approximately the same maturity as the number of years that the project will be financed over.

If the analyst has better information on proposed borrowing costs, s/he can choose “No” for the default rate and then enter the known rate in cell B15.

5. Clicking on “Run Model” will then take the user to the Drinking Water Outputs tab (shown below).

Screenshot of tool's projected drinking water affordability window.

The tool presents the community with:

  • Its population.
  • The calculated amount of increased water bills due to the investment.
  • The probability that the water bills will not be affordable given the forecast of household income and the forecast error.
  • A “stoplight” indicating affordability risk categories of “High”, “Moderate”, and “Low” are also presented. The categories correspond to the maximum probability that the water bills exceed the affordability indicator, following these categories:
    • High – 67% and above
    • Moderate – 33% to 66.99%
    • Low – 32.99% and below

Try the Community Sustainability Tool

The Community Sustainability Tool is available for free download from the WSU Environmental Finance Center. To download, simply click the button below. For questions and help using the tool, contact EFC Program Manager, Brian Bohnsack.

 


Questions about the Community Sustainability Tool?
Contact: Brian Bohnsack, brian.bohnsack@wichita.edu,(316) 978-6421.