Spreadsheet Annual Calculator, feedback?


Userlevel 7
Badge +11
  • Rocket Scientist
  • 13428 replies

Annual Energy Calculator with which you can compare 2 tariffs one of which can be split or single rate, and it can accommodate different  or the same gas prices on each tariff. Thus if you wanted to compare a single rate and an E7 tariff and they both had different gas prices or standing charges, you can do that.

What would you like to see that I may have missed? Here is a guide screenshot, and does anyone want to have a go at breaking it? If so I will provide a link by DM

It has  stuff happening underneath, so this screenshot is only a guide.

All that is necessary is to enter your rates and standing charges and it will calculate all your costs (only including vat)

 


122 replies

@woz well done!

Userlevel 7
Badge +11

Only one caveat that under different excel versions the colours can get screwed up so you have to choose the palette as follows, (it is carefully colour coded so colours are important)

let me know if you’d like to road test it.

Userlevel 7
Badge +9

@woz Got to have colours on spread sheets, how comes your using Office 2007-2010 or am I mistaken.

Love to get my digits on your calculations and road test it.

Userlevel 7
Badge +11

@Strutt G

Dm’d you and yes older version as I don’t have a low cost later version and this works OK for me.

OOPs I lied inadvertently I have office 2016 but I started the project on an earlier version.

Good point I may spend a couple of hours migrating it as there are many conditional colours that have to work as well.

Userlevel 7
Badge +8

@woz 

I'd love to give the spreadsheet a try, but my laptop died a long while ago, I don't see any need to replace it as I do everything I need on my phone and tablet

Nice effort though

Userlevel 7
Badge +11

@woz

I'd love to give the spreadsheet a try, but my laptop died a long while ago, I don't see any need to replace it as I do everything I need on my phone and tablet

Nice effort though

Maybe a google drive version needed then...I’ll have a think..

Userlevel 7
Badge +5

Oooo fancy. My looks so basic compared to that 😂 

Userlevel 7
Badge +11

Mines pen and paper, our Laps 🤣🤣🤣🤣🤣🤣

Very impressive our Wozeeta 👍👏👏👏👏👏

Userlevel 7
Badge +5

Lol my spreadsheet has 4 months on it and that’s it ha. It’s still in baby stage and useless to work anything out on it atm 😂 

Userlevel 7
Badge +11

@Lapa 

ha! my meter reading spreadsheet is now so complicated even I don’t understand I and I wrote it!

 

 

Userlevel 7
Badge +5

@woz ill have given up on mine before it gets to that stage 😂 

Userlevel 7
Badge +5

I keep a spreadsheet, but it's nowhere near as comprehensive as that @woz  - it looks good

Perhaps what might be better for most is a simple energy cost calculator. If we know our charging rates and consumption then the following does the job. I accept no bells and whistles for those on Energy 7 tariffs etc. but such can be added. Wonder how many there are.

For this table the user puts in their annual consumption and the rates of charging and it does the rest. It shows my charge rates (Shell) plus that odd average user consumption

I should have added that the table does of course simply tell you what your cost will be. What is better is a table that shows the actual costs each month and running totals. That is not too complex to set up and makes itveasy when getting quotes. Not that we will be seeking quotes for some time to come. 
 

Wonder if I get a bill this month.  My app shows no smart readings have been taken as normal.

Userlevel 7
Badge +11

Mine looks more or less like that, Rich, but done manually yes, and by month. 

No statements going forward no, but PP meter read request, and DD taken spot on time 👍

Userlevel 7
Badge +8

I had smart readings taken on the 1st as usual, but I haven't had a statement which I usually get on the 2nd or 3rd. The balance in the app includes the payment taken on the 1st

Userlevel 7
Badge +11

Perhaps what might be better for most is a simple energy cost calculator. If we know our charging rates and consumption then the following does the job. I accept no bells and whistles for those on Energy 7 tariffs etc. but such can be added. Wonder how many there are.

For this table the user puts in their annual consumption and the rates of charging and it does the rest. It shows my charge rates (Shell) plus that odd average user consumption

@G4RHL

I feel I have to respond here since you’ve implied that my spreadsheet is somehow too complicated for mere mortals to use. It isn’t. A huge amount of though and time went into the design to  make it suitable for all, even those who aren’t  at ease with spreadsheets.

All you need for a simple cost calculator is a paper and pencil, which is basically what you’ve presented here. That’s not why I designed my spreadsheet in the way I did. If people want to work out their costs in the way you have that’s easy, but if they want to compare two tariffs one of which can be split rate and vary the percentage, or compare two single rate tariffs and see a granular breakdown of costs nicely  and thoughtfully designed and presented  then they can use mine.

Also it was a way of giving something back to the community for those that were interested, I didn’t have to offer it, I thought people might find it useful.

In essence mine is no different to yours in that all that’s required is to enter the rates, the sheet does the rest in the background. I agree that it give far more information  (aesthetically and ergonomically presented  and error trapped ) than some might need or want, but so what? Ignore the bits you aren’t interested in.

Think of someone who just wants to put the figures in and see the annual, monthly, winter/summer (if applicable) costs but also optionally  gives a complete breakdown in a subtle way of energy costs, standing charges,  for those who want it. 

If someone is going to open a spreadsheet there is no more work or understanding required in opening mine to opening yours.

So with respect, I don’t find your example useful, but for those who do that’s fine.

So as far as “Perhaps what might be better for most...” goes, my answer is “perhaps not”, but then having invested so much time and thought into the design I would say that wouldn’t I.

Perhaps you should have tried it before posting.

 

 

 

 

Userlevel 7
Badge +9

Annual Energy Calculator with which you can compare 2 tariffs one of which can be split or single rate, and it can accommodate different  or the same gas prices on each tariff. Thus if you wanted to compare a single rate and an E7 tariff and they both had different gas prices or standing charges, you can do that.

What would you like to see that I may have missed? Here is a guide screenshot, and does anyone want to have a go at breaking it? If so I will provide a link by DM

It has  stuff happening underneath, so this screenshot is only a guide.

All that is necessary is to enter your rates and standing charges and it will calculate all your costs (only including vat)

 

Simply the Best! - Will revertish

Aieght

Userlevel 7
Badge +11

I said elsewhere, you’re wasted here our Wozeeta! You would be LOL at my pen and paper effort 🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣👏👏👏👏👏👏👏👏

Userlevel 7
Badge +9

What else can way say than other than agree with yer @Bev

Userlevel 7
Badge +11

@Bev @Strutt G 

LOL I would never laugh at anyone else’s effort, however implemented. There’s something about using pencil and paper that forces you to think things through in a different way, I often have a scribble to get the logic clear in my head - it helps me to focus  (because my concentration is rubbish)... and I’ve just spent the last 2 hours trying to unravel my annual chart with the intention of making something similar for ongoing readings, so people could track readings daily or even twice daily if need be (for those poor souls who panic post…)

I might have to put it on hold for a while my brain has seized up

and even if I do finish it someone will come along and tell me it’s no use to anyone

and my toads tools are rampant…

Thanks for the support!

Am I right thinking Shell will only bill us quarterly? Am sure I have read that somewhere. Will have to change my spreadsheet and knock out 8 lines.

Userlevel 7
Badge +9

Am I right thinking Shell will only bill us quarterly? Am sure I have read that somewhere. Will have to change my spreadsheet and knock out 8 lines.

Why change for Shell... if thats the case, keep your own spread as is @G4RHL with own meter reads/usage, tariffs and SC’s. I know you know dat!

@Strutt G I did think of that as it will calculate the kWhs used for gas and electricity from readings I am taking and thus I still have a monthly comparison. Can put an extra line in for each quarter to show the actual bill so instead of loosing 8 lines I gain 4!

But then how long will I be ŵith Shell for? Although there is not going to be a lot of choice ahead!

I have still failed to find out how Ofgem reckon the average user uses 2900 kWhs for electricity and 12000 for gas.

On a different subject I now have a fibre optic cable sticking up out of the ground outside my living room window awaiting a full fibre installation! Not sure that will save energy though.

Userlevel 7
Badge +9

@Bev @Strutt G 

LOL I would never laugh at anyone else’s effort, however implemented. There’s something about using pencil and paper that forces you to think things through in a different way, I often have a scribble to get the logic clear in my head - it helps me to focus  (because my concentration is rubbish)... and I’ve just spent the last 2 hours trying to unravel my annual chart with the intention of making something similar for ongoing readings, so people could track readings daily or even twice daily if need be (for those poor souls who panic post…)

I might have to put it on hold for a while my brain has seized up

and even if I do finish it someone will come along and tell me it’s no use to anyone

and my toads tools are rampant…

Thanks for the support!

Will respond...and love the formulas @woz . I always know what I want from a spread, but struggle with the level of formulas on yours. Once worked behind an IT analyst who crunched all the companies costs etc. So could resource his formula knowledge. Struggle with “IF” logic. Is that down to my IQ - Yes I hear!!

 

Reply