This tool allows users to enter data that affect water and wastewater revenues and expenses, including rates of delinquencies and water use changes, to estimate net loss (or change) of revenue under COVID-19 pandemic working conditions over a two year period. Start with financial and water use data from the most recent year prior to the pandemic (e.g. 2019) as the baseline, and then enter the changes experienced or expected as a result of the COVID-19 working conditions. You will have the option to enter two scenarios, which can represent different possibilities and scenarios for changes in the coming months.
The tool is free to download and use. It is designed to be used by utility professionals, governing body members, government staff, and technical assistance providers working with water and wastewater utilities.
Click on the link above to register and download the tool.
Register, download and open the file. Click on the top bar to "Enable Editing". Go to the Input worksheet and fill in all of the cells in yellow. Then view the resulting graphs in Output_Graphs and projections for the next two years in the other worksheets.
You will have the option to enter data for two scenarios, which can represent different possibilities and scenarios for changes in the coming months. Start with Scenario 1 as the most likely scenario going forward, and we recommend using a worse-case scenario for Scenario 2 for more conservative financial planning.
Below is the information you will need to use this tool:
Most recent audited financial statement or financial record for a full year, prior to COVID-19 working conditions.
Water use (consumption) information by type of connection: residential and non-residential, if applicable.
Proposed and anticipated changes to rates, if applicable.
Current accumulated amounts past due (delinquencies)
And answers to these questions regarding changes since the start of the COVID-19 pandemic working conditions:
How is water use changing for different customer classes?
How has the amount and number of accounts with delinquencies changed?
Are there changes to planned or ongoing capital improvement projects or their costs?
How have personnel, chemicals, or other operating expenses changed?
On the Instructions worksheet, you can click a button to download a version of the tool with data already entered in order to view an example of the tool that is complete.