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).
<tbody>
</tbody>
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?
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).
Date | Invoice Pmt | Avg. Rate/Day (By Invoice) | Avg. Rate/Day (By Day) |
12/3/2019 | $12,000 | $400 (30 days since last inv) | $400 |
1/1/2019 | $9,000 | $310 (29 days since last inv) | =(310-400)/COUNT(1/1/2019 - 12/3/2019) + 400 (Essentially will decrease the avg. rate per day until it reaches 310) |
2/4/2019 | $10,000 | $294 (34 days since last inv) | =(294-300)/COUNT(1/2/2019 - 2/4/2019) + 310 (Essentially will decrease the avg. rate per day until it reaches 294) |
<tbody>
</tbody>
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?