Forum Discussion
Search a list of numbers
Twifoo Thank you, let me give you some context, I work for an energy company, as you may know we sometimes estimate meter readings to create a bill. we bill every 3 months - we call this a quarterly bill. If we estimate a bill and a customer calls with a meter read that is lower than we have estimated we have to amend the bill, sometimes we have to amend multiple bills if we have not taken or been given a meter reading.
For example here are the estimated readings
Q4 2019/18 - 6000
Q3 2019/18 - 5000
Q2 2019/18 - 4000
Q1 2019/18 - 3000
Q4 2018/17 - 2500
Q3 2018/17 - 2000
Q2 2018/17 - 1500
Q1 2018/17 - 1000
Q4 2017/16 - 0500
Q3 2017/16 - 0250
Q2 2017/16 - 0100
Q1 2017/16 - 0050
The customer calls and tells us the meter reading is 2200, this means we have to change the readings for all the bills that have been estimated higher than this number. in this example Q3 2018/17 is the first bill in the list that is lower than 2200 so this remains the same. We have to change all the bills above this one. We know that the Q4 2019/18 will be 2200. This means we have to change these bills
Q3 2019/18 - 6000
Q3 2019/18 - 5000
Q2 2019/18 - 4000
Q1 2019/18 - 3000
Q4 2018/17 - 2500
To calculate the estimated reads for these bills we calculate the difference between the reading for the bill that didn’t need to be changed, Q3 2018/17 and the reading the customer gave 2200. in this example the difference is 200 (2200 - 2000) and the number of bills that need to be amended is 5, giving us 40. ( 2200-2000 / 5 )
What we are doing is working out the average usage during the period we are amending the bills and adding this average usage to each amended bill. This table explains
Q4 2019/18 - 6000 to 2200 Changed
Q3 2019/18 - 5000 to 2160 Changed
Q2 2019/18 - 4000 to 2120 Changed
Q1 2019/18 - 3000 to 2080 Changed
Q4 2018/17 - 2500 to 2040 Changed
Q3 2018/17 - 2000 to 2000 No Change
Twifoo this is how far i have got , It gets a bit more complicated, if more than 4 weeks has passed since the date of the last bill we have to also include an information only reading. so using this example, the Q4 2019/18 was generated on the 1 January 2019. The customer called on the 15 February 2019 with the reading of 2200. What this means that we now have to divide the difference by 6. 5 for the amended bills and another information only read
- How did you arrive at 6.5? Please provide sample results in your attached file.
Twifoo sorry, its not 6.5. its 6 or 5. if the last bill was over 4 weeks ago, we have to add the latest read as an information only read, if the bill is less than 4 weeks we amend the bill. This means that depending on when the last bill was produced we have to divide the difference by 6 or 5 see update to attached spreadsheet
- If the last bill is more than 4 weeks old, there is no need to amend the last 5 bills. Why divide by 6, if there is nothing to amend, anyway?
