• We are pleased to announce that the winner of our Feedback Prize Draw for the Winter 2024-25 session and winning £150 of gift vouchers is Zhao Liang Tay. Congratulations to Zhao Liang. If you fancy winning £150 worth of gift vouchers (from a major UK store) for the Summer 2025 exam sitting for just a few minutes of your time throughout the session, please see our website at https://www.acted.co.uk/further-info.html?pat=feedback#feedback-prize for more information on how you can make sure your name is included in the draw at the end of the session.
  • Please be advised that the SP1, SP5 and SP7 X1 deadline is the 14th July and not the 17th June as first stated. Please accept out apologies for any confusion caused.

Irregular to Monthly Calculation Challenge

B

Bryo00

Member
A friend asked me to help him approximate monthly electricity spend/usage using data from prepaid electricity purchases over irregular dates a period of time.

The data contains:
  • Date
  • Amount spent (including tax)
  • Amount of electricity purchased
  • Price Before Tax
The purchases were made on irregular intervals based on need.

My approach using MS Excel is on this link, involves allocating the purchases to months based on the days in between the purchases to extract monthly and quarterly usage. I believe I would have to ignore the first and last purchases because:
  • The first is not from the beginning of the month
  • There is still some unused electricity from the last purchase.
I would appreciate any comments on a better approach.

Additionally, I noticed there was no purchase in Oct 2022 and this shows as no purchase (or electricity use) which is obviously wrong.

Thanks
 
You must log in or register to reply here.
Back
Top