Was Srands scared off?

Nick C

Retrobike Rider
Gold Trader
Feedback
View
Despite my general annoyance with Srands, he was 'our' annoyance. Has he been banished from inane ramblings, or merely scared off posting? I miss his posts.
 
Been very very busy with EXCEL

Well I've been far too busy editing some tier formulas for an absolutely amazing excel spreadsheet {1st tab, UTILITY USE 2011 (T2)} to :
AUTO CALCULATE the COST per MONTH of WATER, GAS and ELEC


Which any forum member or guest is free to download/use/edit (If my ISP Karoo can handle many more ftp download requests!):
ELEC GAS WATER autocalc:1ST TAB

i) A basic desription of Elec_Gas&WaterMEGACALC.xls: It is an excel spreadsheet {1st tab, UTILITY USE 2011 (T2)} to AUTO CALCULATE the COST per MONTH of WATER, GAS and ELEC (TIER formula Excel spreadsheets: Water, Gas & Elec).

ii) A basic desription of 4+Tier_MONEY_TYPE.xls: This is a works file for basic tier formulas, a starting template to edit, and go from there.

You may have a potential use for these, spare a few minutes of your precious time to look at this file ELEC GAS WATER autocalc:1ST TAB

(I thought might be of interest, as the spreadsheets are very very useful, they took many hours of my time to edit, as they have some involved TIER formulas, something of an occupational interest, as an IT graduate & ex professional electrician, an odd mix which has provided some very useful insights.)

Let me know if you like these, or if you have any questions.

ELEC GAS WATER autocalc:1ST TAB(The most current version, it’s only 0.7 MB's, free to download/use and edit to suit. Surely this will be the most worthwhile excel file you will ever download, I virtually guarantee it!).

TIER example/definition:Don't know what I mean by TIER? The following example demonstrates:

ELEC T2 (2 TIER)
T1 upto 900 kwh, £0.15 per kwh.
T2 over 900, £0.12 per kwh.

or

ELEC T1 (1 TIER)
£0.14 per kwh

TIER definition: "Tiered rate pricing structure model determined by quantities bought/used, commonly two-tiered system:
~ The base rate (t1) and
~ A subsequent tier (t2) that is a different rate (Normally less)."

Understand? Good.

FILE HYPERLINK:
ELEC GAS WATER autocalc: 1st tab
UTILITY USE 2011 (T2)

Simply enter:
PRICE RATES (Tier 1 & 2 Tier)
TIER MAXES (T1 Upto, then Over T2)
METER READINGS per month


INSTRUCTIONS (If you need more explanation then the above, see step by step instructions below. Also the spreadsheet has a working example, so you can see actual real values):


Enter PRICE RATES in following cells:

WATER T1: D2
WATER T2: E2

GAS T1: G2
GAS T2: H2

ELEC T1: J2
ELEC T2: K2

(WATER NOTES, 4th tab: “WATER USAGE & APPLIANCES TIMES”
Since water calculations can be more complicated then either ELEC or GAS,
this is calculated in another tab, hence edit in the following:
Water Notes:
T1 Water Cell = F70
T2 Water Cell = F79)


TIER MAXES:
WATER: D1
GAS: G1
ELEC: J1

Note: "IF" your utility/energy bills are TIER1 then this figure will be unknown, hence a maximum has to be entered for your TIER1 utility/energy, that will be NOT exceeded:

WATER: Normally calculated per QUARTER (3 Mths), I have set this to 13 M3 (Cubic Meters), but your consumption MAY be greater then this.
GAS: Normally calculated per YEAR (12 Mths), I have set this to 1000 G KWH (Gas KiloWatts), but your consumption MAY be greater then this.
ELEC: Normally calculated per QUARTER (12 Mths), I have set this to 5000 KWH (Elec KiloWatts), but your consumption MAY be greater then this.


START OF YEAR (Enter last Years Dec reading, i.e. 1st Jan this year):
WATER: C4
GAS: F4
ELEC: I4


PER MTH METER READING, enter end of mth, i.e. 1st day of next mth):

For example in JAN:
WATER: C6
GAS: F6
ELEC: I6

For example in FEB:
WATER: C7
GAS: F7
ELEC: I7

Etc for each month in a year.


PER MONTH COSTS are AUTOMATICALLY CALCULATED, in columns COST:
WATER: Column E
GAS: Column H
ELEC: Column K


PER YEAR TOTAL CONSUMPTION & TOTAL COST are AUTOMATICALLY CALCULATED, in rows YEARLY TOTAL & Mthly Av:
Yearly Total of WATER, GAS & ELEC: Row 22
Mthly Average of WATER, GAS & ELEC: Row 23


UNITs consumed combined cost:
Column L


Units converted:
WATER: Columns N & O
GAS: Columns P & Q
ELEC: Column R


Cumulative consumption:
WATER: Column W (Per Quarter = Every 3 months)
GAS: Column X (Per Year)
ELEC: Column Y (Per Year)


PRICES changes halfway through a year:
Energy/Utility businesses have a tendency to prices when they feel like it, and NOT conviently as a new year starts.
I have antipated for this, and PRICE RATES & TIER MAXES, can be edited PER Mth, see individual control panel (Scroll to the right):
WATER Control Panels: AA4
GAS Control Panels: AO4
ELEC Control Panels: BC4

Hence EDIT per MONTH (If Tier costs or unit maxes changed part way through a year):
TIER1 from, TIER1 to, cost per Tier1
Over T2, cost per Tier
For example in Elec Control Panel, a price change only for T2 in May and onwards:
Edit Cell: BI10

PRICE RANGES follow each other, for example:

T1 from 0.
T1 to 1000

T2 over 1000


OK any questions just post a message.

The hyperlink for the Elec_Gas&WaterMEGACALC file is: ELEC GAS WATER autocalc: 1st tab

TIER1:
Also if your looking for just a T1 (TIER1) spreadsheet, see some of the last tabs there are some blank templates, the layout is similar:
UTILITY USE (T1)
or
UTILITY USE 2011 (T1)

TIER FORMULAS:
Many people are likely to be very interested in the workings of the TIER formulas, to make this more obvious, I have made another excel workbook:
TIER 4+ MONEY TYPE These are WORKS templates, I start with 1 of these then copy, make necessary changes, and edit to suit, simple enough.
There are a number of templates:
~ ELEC T4 Horiz 1R (This is a great spreadsheet, that will need little editing, then just copy&paste, when you're familiar with it, you probably won't even need to edit/change formula's just edit title wording, and perhaps moving formulas out of sight of ranges and totals, etc)
~ T2 (TIER 2: Another great speadsheet, TIER2 per 12 month, fantastic)
~ T4 (TIER 4: That is an interesting spreadsheet, if energy/utility businesses ever go beyond TIER2 rates)

Also have a look at my other useful free spreadsheets (Download & edit to suit for free), etc:

ELEC GAS WATER autocalc: 1st tab, scroll down
TIER 4+ MONEY TYPE

MPG autocalc
METRIC autocalc
PERCENTAGE autocalc
EXCEL USEFUL FORMULAS
MTB routes in/near Hull, Humberside
AMPAGE TESTING (12 Volt DC)
FUSEBOX MCB autocalc: 3rd & 4th tab
RING AMP autocalc: 1st tab
AUTOCALC WEBSITE

Cheers

Stephan

www.srands.co.uk
 
^^^^ Well you asked for it. :twisted:

personally I 'm glad he is back as I've beeen worrying myself shitless about how to figure out TIER formulas.
 
i'm glad its not just me that misses him

come back stephan ,nothing is forgiven
 
Back
Top