Spreadsheet Annual Calculator, feedback?



Show first post

129 replies

Userlevel 7
Badge +5

Is that billed or payments? I like monthly payments. 
 

 

edit payments are still monthly 

Userlevel 7
Badge +5


 

from shells website. Does imply statement period is quarterly 

Userlevel 7
Badge +9

@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.

Think you’ve answered your own question, stick to what you are doing no matter what the supplier.

Full Fibre, not sure about energy saving...we have full fibre...its basic Virgin and works well. But I understand that Virgin controls certain bulk area usage and can spin out during some peak times. Re booting the box usually sorts out the signal and I guess this is something you should do from time to time anyway.

Think that problems can arise because fibre connects to non fibre hubs...as you can tell I’m not speaking in technical terms. What I can remember is that both our late mothers had basic BT packages with lesser bandwidth, no fibre and  worked just as good. But thats East London and Greater London so I guess good connectivity.

Userlevel 7
Badge +11

Watch this space (but not too carefully) 

My next spreadsheet will cover Annual, monthly, daily or wheneverly readings. 

However there is a however,  is there any point in writing a separate bill checking pane, on the grounds that the calculation  on the bills will always be correct so unless you happen to have readings on exactly the dates of the bills there seems little point in back checking...hmmm one to be pondered over.

 

Badge +5

@Strutt G Fortunately the fibre being installed is fibre all the way. At present it is only to the cabinet and then copper wire to the house. The link installed yesterday brings the full fibre from the cabinet to the house. More importantly it will save me nearly £100 p.a.. Which will go toward the support of Shell!

Badge +5

@woz Why not? Add a back check provision? I have added extra lines to put in the actual quarterly charge as it arises so that I can double check. 
 

Needed to add the formula to convert gas to kWhs. For those that want it:

kWhs=m3x1.02264x39.8/3.6

 

Userlevel 7
Badge +9

@woz Why not? Add a back check provision? I have added extra lines to put in the actual quarterly charge as it arises so that I can double check. 
 

Needed to add the formula to convert gas to kWhs. For those that want it:

kWhs=m3x1.02264x39.8/3.6

 

I’m not trying to be clever @G4RHL  and my spread has this calc. The CV has to tweaked as the CV alters each period. Well most periods..and affects the statement by a few pennies or even a tad more. 

Badge +5

@Strutt G ...plus never mind the decimal points to which the calculation is effected! I never needed it before but will if I record monthly readings from hereon in.

Userlevel 7
Badge +11

@G4RHL and @Strutt G 

My spreadsheet already includes it and I’m not trying to be clever either. (no point in floggin’ a ded ‘orse)

The formula is incorrect by the way as it’s 1.02264/3.6 x CV where CV = Calorific Value in  MJ/m3 and which is quoted on your statement. Hence as said you do have to tweak it afterwards to get a true value for kWh thus it has to be a variable (as it is on my sheets)

The range of CV quoted by NG is 37.4 to 43 MJ/m3, in practice it varies by less - I chose 39.4 as a default but 39.5 or 6 would still be OK.

For all practical calculation purposes it can be simplified to

kWh =m3 x 0.28407 x CV

 

unless you’re into industrial quantities of gas the error is absolutely negligible even over 30,000 kWh (less than ½ a kWh but if you’re really anal about 0.2 of a kW in 20000 use 0.284067)

back soon

...happy calculating

 

 

Badge +5

The formula is simply that currently used by PP. The best I have to follow at present. Next month it may well be different.

Userlevel 7
Badge +11

The formula is simply that currently used by PP. The best I have to follow at present. Next month it may well be different.

Yes it may well be (different), the figures are supplied by NG to all of the energy companies. You only find out what figure was used for calculation in retrospect.

(they are a fudge anyway because the CV varies with altitude, but they use an average)

I wasn’t rubbishing the formula just offering a spreadsheet alternative.

Badge +5

@woz but easier,  perhaps better, more accurate, to apply what we are advised is being applied.

Userlevel 7
Badge +8

This seems to be turning into a my spreadsheet is better than yours battle. Everyone has their own requirements when it comes to creating them, with some wanting a basic plan, others more inclusive, it's a whatever works for you scenario.

I only wish I could create one, I did try to create one on Google's sheets I think it was, failed miserably and gave up, back to pen and paper for me

Badge +5

@Duppy Its not difficult to do. I use Excel. The key is simplicity. Give it another go.

Userlevel 7
Badge +9

Just a thought my later mother at the age of 78 went on a MS Office Course after we went out and she purchased her first computer and would now be online for the first time. We had some magical moments even with all the frustration of her phoning me at work with all the problems that come with MS constantly updating, then me popping round attempting to explain stuff - it was a lovely shared experience. Anyway I’ve diverged, think she managed to get the course from age concern.

Maybe consider getting an online excel beginners course or excel of dummies book as all you really need to know is some basics, unless of course you get fixated and want to go on course.

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)

 

I’ve had gander this morning and would say this is brilliant and perhaps too brilliant for many. I can see how much thought has gone into this with those well thought out formulas.  Big up for @woz 

I’ve sent you a DM

Badge +5

Just a thought my later mother at the age of 78 went on a MS Office Course after we went out and she purchased her first computer and would now be online for the first time. We had some magical moments even with all the frustration of her phoning me at work with all the problems that come with MS constantly updating, then me popping round attempting to explain stuff - it was a lovely shared experience. Anyway I’ve diverged, think she managed to get the course from age concern.

Maybe consider getting an online excel beginners course or excel of dummies book as all you really need to know is some basics, unless of course you get fixated and want to go on course.

My late mother did something similar. At the age of 80 she decided to buy a computer and learn how to use it. Took herself off to her local technical college to learn what to do. Then decided to research the family and ancestors. Made notes of her experiences and research but she would print them out and then delete the file on the computer and did not proof read as she progressed. I was left with 100 + documents to scan, convert to Word, edit format etc. and then save digitally. Am nearly there with it all. She made the effort to learn though, amongst other things she did. Some of the notes she left are of a visit to Germany in November 1938, the conditions there and what she saw. Of course we all realise after the event that we ought to have sat down with our elders and got their story.

Userlevel 7
Badge +11

 

 

I’ve had gander this morning and would say this is brilliant and perhaps too brilliant for many. I can see how much thought has gone into this with those well thought out formulas.  Big up for @woz 

I’ve sent you a DM

@Strutt G Thanks.

I may have to produce a simpler interface - I accept the fact that some people don’t need or want to see the granular breakdown of costs.I thought they could look past the detail and just enter the numbers, I shall give it a coat of thinking about (available in tins at B&Q).

Userlevel 7
Badge +9

Its not easy for us mere mortals or is it morsels - may be one and the same.

 

Userlevel 7
Badge +11

@Strutt G 

I’ll take that as an insult then...

If I was immortal
to look back in anger
would then last forever
Oh, that’s a headbanger!

So I’d open a portal
and through it you’d pass
first your face,
then your ass.

With a grimace and chortle
the eighth deadly sin
would then be invented
You’d be out
when you’re in.

© wozco 2021
 

 

 

 

 

Userlevel 7
Badge +9

Ha Ha oh no - It was a compliment @woz 

Was a compliment for thy fine work that came out wrong from a jerk

It sounded ok in my wee mind and I was not endeavouring to be unkind.

It seems thee a twee bit miffed lets not fall out and and have a rift.

Strutt Inc (G)

 

 

 

Userlevel 7
Badge +11

LOL

I didn’t really think it was an insult, I just felt poetic..

I think I’ll have to add that one to the poetry thread.

Progress is sooooo slooooowwwwww on the new sheet.

Would thinking about what’s most important help?

Inputs

Electricity 

  1. Annual usage (unit selector)
  2. Current cost per kWh
  3. Current standing charge (unit selector)
  4. Alternate supplier cost per kWh
  5. Alternate supplier standing charge

Gas 

  1. Annual usage (unit selector)
  2. Current cost per kWh
  3. Standing charge (unit selector)
  4. Alternate supplier cost per kWh
  5. Alternate supplier standing charge

If you have two (or multiple) rates, how about just duplicating the whole sheet? Keeps it simple and is extendable. In fact, could you do Gas and Electric on their own sheet and duplicate sheets as necessary for other rates/tariffs? 

General

Dual fuel discount amount. Could be shown on the Outputs sheet.

Outputs

  1. Current electricity cost (unit selector)
  2. Current gas cost (unit selector)
  3. Alternate electricity cost
  4. Alternate gas cost
  5. Difference

 

Oh I’ve created a monster. Good luck @woz! Not sure I’ve helped at all...

Userlevel 7
Badge +11

@25 quid 

 compare up to 7 tariffs either single or dual rate and single or dual fuel. Names of tariffs are not related to any company. I only chose 7 because I felt like it, could be any number.

This addresses all you mentioned as did the first one but it’s less cluttered.

All extraneous info removed just enter rates and percentage if dual rate and out pops the total.

It’s error trapped to prevent dual rate errors creeping in those are the red squares (for example)

consumption readings chart is in progress, it will however take weeks, I may not even bother

Userlevel 7
Badge +11

Loving the names our Wozeeta 👍👏🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣🤣

Reply