Water Meter Billing 3

[controlnovice]

I volunteer for a water utility system in the mountains of Colombia, S America. All of us are volunteers. It's community of about 190 houses/farms that gets water from a mountain stream.

The government has asked us to install water meters on each house, which we are doing now. But, they don't help with setting anything up for recording or billing.

I've created an Excel spreadsheet to capture the current readings and subtract the previous reading to calculate the consumption, but I'm not sure how to roll the following concerns into the formula.

[ul]
[li]The homes are spread out and some are very difficult to access in the mountains. Some of the clients we will be unable to read each month. I know I can check if the current reading is blank, to check the reading from 2 months ago, and calculate an average consumption from the previous two readings. But what if it's been 2, 3 or more months since we've been able to do a reading?
[/li]

[li]Due to the difficulty in reading some of these each month, we also may have to use the average of the other homes in the area. How would this be done in Excel? Would each home need a different formula, based on which homes we would take the average consumption?
[/li]

[li]Due to the difficulty in accessing some of the homes, we may not be able to read the meters exactly every 30-31 days. Some months might be 25 days, or 35 days. Is the billing supposed to calculate for 30 or 31 days (depending on the month), so if the consumption for the month was 3500 liters and was read 35 days after the previous reading, would the bill be shown as 3000 liters for 30 days of the month?
[/li]
[/ul]

Any help would be greatly appreciated. This is a poor community but we are trying to teach them about conservation of water.

______________________________________________________________________________
This is normally the space where people post something insightful.

 

[GregLocock]

Hmm. The first year they monitor it. The second year they bill it. The third year they restrict it. The fourth year people end up in court.

I think that was one of Orwell's laws.

Frankly, based on my own situation, you are safe to assume extrapolation from a 13 month moving average to cover missing readings, at least until the next physical reading comes in, when everything gets corrected.

Your second point is dangerous, how can you ethically charge someone for their neighbor's water usage (think about it)?

Third point,I suggest you ask your customers. Most people would prefer a monthly bill even if it was slightly inaccurate.

My water company works on estimated usage between quarterly readings but if it gets wildly out of whack (last time the meter reading made a significant mistake) you just fill in a form with draconian penalties for lying. I think we can comfortably assume the meter reader was not subject to those penalties.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[3DDave]

First, this is a problem better solved with a database, if you can find someone to create one. The database front end/user interface can be developed to try to prevent stupid things from happening, mainly mis-entered data. You can create forms in Excel to do this, but there is a reason databases are the core of financial management.

I think it would be better to extrapolate based on season of the year, if there is much consumption variation from that, and number of occupants or square feet of house or number of bedrooms rather than proximity.

If you have a new customer who you can't get to for 3 months, but you want to bill anyway, check for the consumption of similar homes in a similar season. It may also be worthwhile to ask, as utilities used to do, for the owner to send the reading on the meter along with the payment for the estimate with the encouragement that getting that value correct will prevent a future large catch-up bill when the company does get to read the meter.

Extrapolation on an existing stream of data would just be the daily use based on the reading and number of days since the previous reading. If the billing is on a monthly basis from recorded readings then the extrapolation might be for a couple of days.

I am unsure there is an ethical problem - every extrapolation will produce an error and, as long as a particular customer isn't surrounded by mansions versus their cottage, the chance that an average will be too far is not a problem as long as at the next actual reading the difference between what has been paid and what should have been billed is made up for.

Example: If my neighbors used 1000 gallons of water each last month, and I only used 800, then when the next reading is made and it shows 1600, I should be billed for 600 gallons as I had already paid for 1000 gallons from the estimate, no different than if someone in the family left for college or a job in a different city and the water use decreased, and so extrapolations overestimate the use. This assumes limited history for that customer. If there is, then that history is all that is needed.

But suppose they all used 100,000 gallons last month because they are filling Olympic swimming pools? That's where an understanding complaints department should respond and pick, as mentioned above, a more comparable estimate basis.

I think there are a number of weird "_______IFS" functions that only return results if there is a match to the criteria. Like SUMIFS() where an average could be based on house size within a range that matches the particular house size. See

https://support.microsoft.com/en-us...nditions-e610ae0f-4d27-480c-9119-eb644f1e847e

for how SUMIFS() works.

As an adaptation to the "can't get there" problem:

There are meters that use mesh network technology to send readings back and even though the signal cannot make it directly in one hop, it can be transferred from meter to meter back to the central site. Any meter that doesn't can be investigated and a repeater put in place to link it up. If that's not possible the others will reduce the need for meter readers to go out to inspect. The use lithium primary cells and probably last a decade or two before needing replacement; someone needs to look sometime to see the meter isn't being bypassed.

Be very wary of anything that requires the meter company to do anything but sell the meters. There have been several instances where they promise to do all the work for a percentage of the "savings" that is predicted by their contract and the savings don't happen, the meters don't report correctly, and they have a huge pile of cash and no interest in discussing the failure.

 

[MintJulep]

I agree with 3DDave that this needs a database. With only 190 customers you might get away with an Excel database surrogate for a few years.

I agree with Greg that you can't bill someone based on someone else's use.

It's easy to calculate average daily use between any two readings, but if you use the following suggestions you won't need to. It might be "nice" information to include on the bill.

Could you ask people to self-report readings by phone?

You can't allow a "blank" reading. You need a reading for every user every month. Every reading will be either "actual" or "estimated" (or "self reported" if you can do that).

You need to keep track of what readings are used for each bill.

You need to bill everyone on a set schedule. Each bill must be based on the delta use between the most recent and prior bill's reading.

Make a rule: If you don't have an actual (or self-reported) reading by some target (3rd day of the month, 1st Wednesday of the month, whatever) then you create an estimated reading.

Bill for whatever number of days are between readings, don't try to normalize to a "month".

You'll need to eventually correct for any estimated use and billing based on an actual reading.

Eventually you'll be able to create a historical use pattern for each house, in the first years you'll just need to guess.

For the first few years you probably should make the effort to get all the readings every month. Later you can rely on that data for estimated readings.

Regardless, you should set and adhere to a limit of no more than 2 consecutive estimated readings for any house. So, whenever you create a bill based on two estimated readings you need to plan for an actual reading to support the next bill.

 

[1503-44]

Bill every end of month, either 30, 31, 28 or 29 days. Whatever number of days that month has.

Determine a default liters/day consumption rate to use when estimating consumption. Keep a default value for each customer. You can probably start with using the same value for all customers, at least for a limited time. As you get more data on each customer, you can simply update each customer's default value to a new value based on their particular 3 month moving average.

You will need
A separate database of
customer name, customer number, address, NIE, or whatever ID is convenient.

A separate database of customer and meter number,

A separate running database for each meter.
meter number, "estimated read", estimated consumption, and
"actual read" dates, actual meter readings

The customer accounts database
Billing database
Customer number,
billing date, consumption, cost/unit, amount of bill, estimated or actual

Payment database
payment date, payment amount, previous balance, current balance

current balance,

Wait! This sounds like the perfect job for ChatGPT
see my conversation here,

https://chatgpt.com/share/1ac107b4-a5a8-4e74-b7bb-7b7f390a639a

--Einstein gave the same test to students every year. When asked why he would do something like that, "Because the answers had changed."

 

[controlnovice]

I actually created a Google AppSheet, which is a very basic database that keeps the information stored in Google Sheets.

I was trying to simplify my request to only that of the formulas needed to estimate for missing data, or data not taken with a common frequency.

I have the following tables:

Users: "Name", "Address", "Meter ID", etc...
Meter Readings: "Meter ID", "Date", "Current Reading", "Previous Reading", "Consumption", "Days between readings"

The billing is done with other software (per the Colombian equivalent of the IRS), so the above consumption data will be imported into the invoicing software.

I'll have to add a column to check if it's an estimated reading, and calculate as such.



______________________________________________________________________________
This is normally the space where people post something insightful.

 

[SkipVought]

I'd suggest starting to work on a design for your database before creating a spreadsheet or formal database. Get all your rules established regarding readings, estimates and billing and all your data identities identified and table relationships documented. You have already received many good tips.

Another great resource is Engineering Tips sister site www.tek-tips.com. There are forums there that have members that can guide you in database and spreadsheet design. I have often used an Excel Workbook as a bread-board for database development, where a concept can be more quickly implemented and tested before more costly and time-consuming database development and programming begins. The ultimate goal is to work toward a stable and sustainable database environment, only using a spreadsheet as a temporary measure. It is possible to simulate a database in an Excel Workbook and use SQL to access those tables. The tables could even be created as MS Access tables.

Bottom line, you can start small and temporary in Excel (or any other spreadsheet app) and use your hopefully well designed Excel database and well designed process as a template for a more permanent and persistent database system. You just don't want your spreadsheet efforts to "grow like Topsy."

https://www.tek-tips.com/threadminder.cfm?pid=68:

MS Office forum that includes Excel

https://www.tek-tips.com/threadminder.cfm?pid=700:

MS Access Tables and Relationships

https://www.tek-tips.com/threadminder.cfm?pid=707:

VBA Visual Basic for Applications (MS) if you want to automate your application


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

It would also aid members here or at Tek-Tips if you would upload a workable copy of any spreadsheet in question, because many of us would be ready to jump on a useable model of your problem an offer a workable solution or other alternatives for you evaluate directly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

Used to be pretty common for water companies to estimate usage and bill accordingly, even in Orange County, Ca.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[1503-44]

If you are thinking of writing some code yourself, here is some sql help.

https://www.codeproject.com/Articles/5383889/SQLite-Helper-A-Micro-ORM-for-SQLite-Database

--Einstein gave the same test to students every year. When asked why he would do something like that, "Because the answers had changed."

 

[stevenal]

You may not need to deal with it for some time, but I suggest that any solution you adopt account for meter register rollover.