S
srands
Hi folks here is my greatest creation yet, an absolutely amazing excel spreadsheet {1[SUP]st[/SUP] tab, UTILITY USE 2011 (T2)} to AUTO CALCULATE the COST per MONTH of WATER, GAS and ELEC (This is the most current version, it’s only 0.7 MB's, free to download/use and edit to suit. Looks similar to older versions, but now with TIER2, instead of just TIER1. 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
LIGHTs, RING & RADIAL wiring diagrams
AUTOCALC WEBSITE
Cheers
Stephan
www.srands.co.uk
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
LIGHTs, RING & RADIAL wiring diagrams
AUTOCALC WEBSITE
Cheers
Stephan
www.srands.co.uk
