I’m wondering if anyone can help me set something up; I think it should be a quick deal, but my excel knowledge is limited and I'm not sure how to create it. I am Including the data set I have, and I will explain my goals below. Thank you in advance for anyone willing to take the time to help set this up for me!
Goals to accomplish:
Factors to consider:
Goals to accomplish:
- To create a water bill calculator where I can enter the number of gallons used by any property and get an estimate of what the water bill will would be.
- Analyze the year over year % water rate increase and project what future water bills will be at the same water consumption. Currently only 3 years of data to work with.
- Estimate future $ savings based on a percentage reduction of gallons of water used. For example if I reduced my water consumption by x%, how much money would I save in future years?
Factors to consider:
- The projected rates were calculated by applying the average % increase year over year. I did these in excel but couldn't get the add on for copying my cells to work, so I just included the data in the table.
- There is a flat rate base charge added to all bills, in addition to billing per 1000 gallons used.
- There are 3 tiered billing rates in each which are based on gallons used (for example: once all tier 1 gallons are used, the rate increases to the tier 2 rate for any additional gallons above tier 1. Likewise for tier 3)
- Finally, the most challenging factor will be that Teir 1 rates are based on AWC which will be variable from property to property.
- AWC Note: Single Family AWC: A customer's average winter consumption (AWC) is used to determine the tier 1 threshold. The AWC is calculated by averaging each customer’s billed monthly water use from January through March, which is a way of determining essential indoor water use. The water company has set the tier 1 minimum threshold at 5,000 gallons, and a maximum of 15,000 gallons. For example, if the customer's AWC is less than 5,000 gallons, tier 1 is 0 to 5,000 gallons. If the AWC is over 15,000 gallons, tier 1 is 0 to 15,000 gallons. Volume rates are applied to billed monthly usage.
- If AWC < 5,000 then Tier 1 limit = 5,000
- If AWC > 5,000 and < 15,000 then Tier 1 limit = AWC
- If AWC > 15,000 then Tier 1 limit = 15,000
- Bonus: There are actually 3 service regions that all have different rates. Each region still has the same tier structure. I planned to just make separate tabs for each region, but it would be cool if the calculator could take the region as an input. For example enter region = 1 and the calculator pulls the rates for region 1.
| 2022 Rate ($) | 2023 Rate ($) | % Increase from 2022 | 2024 Rate | % Increase from 2023 | Average % Increase | Projected 2025 Rate | Projected 2026 Rate | Projected 2027 Rate | |
| Base Charge | 17.20 | 17.90 | 4.1 | 18.40 | 2.8 | 3.4 | 19.03 | 19.68 | 20.36 |
| Teir 1 - 0 to AWC (see Note) | 2.44 | 2.63 | 7.8 | 2.78 | 5.7 | 6.7 | 2.97 | 3.17 | 3.38 |
| Teir 2 - AWC + 15,000 | 4.39 | 4.73 | 7.7 | 5.00 | 5.7 | 6.7 | 5.34 | 5.70 | 6.08 |
| Teir 3 - Greater than AWC + 15,000 | 5.86 | 6.31 | 7.7 | 6.67 | 5.7 | 6.7 | 7.12 | 7.59 | 8.10 |
