Breaking down electricity usage per day/average over month span

[nosa1]

Hello,

I am trying to create a spreadsheet that will break down my average electricity costs by day to help me better estimate accruals for invoices I haven't yet received.

My columns are:

Invoice Payment - (1/1/2019 - 2/4/2019, for instance)
Average Rate/Day (By Invoice) - (The invoice payment amount divided by number of days)
Average Rate/Day (By Day) - (This month's Average Rate per day minus last month's average rate per day, divided by number of days, plus last month's average rate per day).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Invoice Pmt[/TD]
[TD]Avg. Rate/Day (By Invoice)[/TD]
[TD]Avg. Rate/Day (By Day)[/TD]
[/TR]
[TR]
[TD]12/3/2019[/TD]
[TD]$12,000[/TD]
[TD]$400
(30 days since last inv)[/TD]
[TD]$400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]$9,000[/TD]
[TD]$310
(29 days since last inv)[/TD]
[TD]=(310-400)/COUNT(1/1/2019 - 12/3/2019) + 400

(Essentially will decrease the avg. rate per day until it reaches 310)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/4/2019[/TD]
[TD]$10,000[/TD]
[TD]$294
(34 days since last inv)[/TD]
[TD]=(294-300)/COUNT(1/2/2019 - 2/4/2019) + 310

(Essentially will decrease the avg. rate per day until it reaches 294)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In theory, my Average Rate/Day (By Day) for the entire period should add up to my total Invoice Payment amount (It doesn't).

How do I do this? Am I headed in the right direction? Is there an easier way to go about this than drawing out every single day with its own calculation?

 

[bobsan42]

How accurate should your accruals be? To have accurate accrual you need to know the consumption for the not yet invoiced period then multiply by unit rate to get the expected cost. No average can compare to real data. And how hard is it to get the meter reading?
The easiest way to get such an average that you want is to sum all bills then divide by the sum of all periods.

 

[nosa1]

Pretty hard to get meter readings. I want to be able to understand the average rate per day (by day) for each meter... so in essence, if I have the latest invoice, how can I break that down per day (taking seasonality etc into account)?

 

[bobsan42]

I don't mean meter reading every day. All you need is a reading around the time the utility company does it. Then the difference with the previous invoice reading will give you the consumption you have to pay.
You don't really need a daily average. All you need is a seasonal monthly average if you can't get a reading.
So keep it simple. Especially if you have last year bills, with some arbitrary coefficient (e.g. based on last month vs. same month last year).
Whatever clever formulas we can come up with will never be too accurate.