Thread: ELEC, GAS & WATER Cumulative Totals

Hi, attached is my working examples of CUMULATIVE TOTALS with different PRICES with QUANTITY ranges for ELEC, GAS & WATER. The worksheets work great.

The formula is a bit longer (row G then row E) then I wanted, has anybody got a "simpler formula" that is easier to copy & paste?

Cheers Stephan.

OK alot of revisions to the above, here are my most current version TIER spreadsheets:

TIER SPREADSHEET: CATEGORY TYPE
DATA LIST (L/H) with SUMMARY TABLE (R/H) for MANY ROWS: 1ST TAB most accurate version, 2 examples following, followed by previous versions (To show progression!)
http://www.srands.co.uk/4+Tier_CATEGORY_TYPE.xls


TIER SPREADSHEET: MONEY TYPE
SUMMARY TABLE of MIN/MAX/RATES for ONE ROW.
http://www.srands.co.uk/4+Tier_MONEY_TYPE.xls

Let me know if you think thumbs up for these cool spreadsheets!

Cheers

Stephan

www.srands.co.uk

EXAMPLE OF FUSEBOX MCB CALCULATOR for LIGHTING CIRCUTS ON CATEGORY TIER SPREADSHEET 2ND & 3RD TAB

EXAMPLE OF SIMPLE ELEC/WATER/GAS CALCULATOR ON MONEY TIER SPREADSHEET 2ND/3RD/4TH TAB

MY VERY COMPREHENSIVE ELECTRICITY WATER GAS ENERGY UTILITY METER COST CALCULATOR, 1ST TAB, SCROLL DOWN

This UPDATED file calculates:
~ UNITS USED from your WATER, GAS & METER READINGS
~ COST of UNITS consumed of WATER, GAS & METER from PRICE RATES you enter.

The latest version of this file, has a number of significant features/upgrades, the most useful for the vast majority of circumstances will be TIER 2 (T2):

~ TIER 2 (T2) with full statistics table, discounts & standing charge options, control panel to edit tier maxes/rates per mth, and Graph comparisons of cost and consumption, cumulative and not cumulative

~ TIER 4 (T4) with full statistics table, discounts & standing charge options, control panel to edit tier maxes/rates per mth , and Graph comparisons of cost and consumption, cumulative and not cumulative

~ TIER 1 (T1) with full statistics table, discounts & standing charge options and Graph comparisons of cost and consumption, cumulative and not cumulative

FILE DOWNLOAD: http://www.srands.co.uk/Elec_Gas&WaterMEGACALC.xls
INSTRUCTIONS DOWNLOAD: http://www.srands.co.uk/T2instructions(TIER2).doc

To make it immediately obvious which figures can be changed (Rates/prices/discounts/standing charges/meter readings) these are in RED.
As I know when looking at other peoples worked spreadsheets it might not be clear the difference between designated/set formulas, AND figures that can be edited/changed. Hence I made it easier to differentiate.

If I make any other upgrades I will upload the updated file, to the above hyperlink. As the file is it is very comprehensive, currently I do NOT think many other developments will follow (Well probably only my monthly returns of consumption/cost of Water, Gas & Elec, as & when I make a sporadic ftp upload).

The XL tabs within the workbook are currently in the following order:

(T2) Tier 2
T2 GRAPHs Tier 2 Graphs
CALC (Manual, How to) Simple Water, Gas & Elec calcs explained
(T1) Tier 1
T1 GRAPHs Tier 1 Graphs
ELEC&GAS price compare Elec & Gas price compare to different supplier(s)
(T4) Tier 4
T4 GRAPHs Tier 4 Graphs
MY APPLIANCES with WATTs CALCs: All appliances in building calc total watts
WATER USAGE & APPLIANCE TIMES: Calc total cost of appliances that use water/gas/elec
etc: More spreadsheets of some use

It is an excellent XL workbook with many fantastic spreadsheets (Approx 1MB) you can edit and use for your own purposes and will save you countless hours with TIER formulas (You could say that is the really rare bit). Few workbooks like this exist, hence prior to this workbook it was difficult for persons to have a simple method to calculate all in one their WATER, GAS & ELEC usage/cost. As many people have found there was few options to calculate this on their own as TIER FORMULAS are a complicated matter for most, and when most people searched the interest for solutions all information that could be found was energy suppliers online quote systems (Dictating prices & rates, without showing workings).

The only real "anomaly", or anomalous factors here is the different pricing structures of water/gas/elec suppliers, as many perceive correctly working out the cost isn’t as simple as UNITS X PRICE RATE = COST.
To elaborate further:
~ There are many many energy suppliers offering multiple Duel Fuel discounts, depending how the energy suppliers calculate the discount will vary the actual cost, however bearing in mind businesses are always thinking what makes them the most profit, hence they are likely to make DISCOUNTS of your TOTAL without STANDING CHARGES (If any), hence so the £ amount subtracted as a discount will be less.

Hence the spreadsheet calculates in the following order:
~ DUEL FUEL DISCOUNT subtraction
~ ELEC/GAS DISCOUNT subtraction
~ STANDING CHARGE addition

Also, the other anomalies/variables are as follows:
~ WATER has charges included PER QUARTER and only calculated as a total at the YEAR END (As quoted by regional Water Supplier in England, North East. Maybe different elsewhere Nationally or Internationally).
~ GAS/ELEC charges/discounts are only calculated as a total at the YEAR END (As quoted by the majority of ELEC/GAS Suppliers in England. Maybe different elsewhere Internationally).

The only reason why water/gas/elec suppliers have overcomplicated simple calculations, is just to confuse customers from working out the real actual cost of their bill by themselves, hence this makes comparisons to competing water/energy businesses, instead of an educated change to a cheaper supplier, it would be on a guess/whim, or suggestion from a sales rep, etc.

Hence I created the spreadsheet to calculate COST, from RATES and CONSUMPTION (Current meter reading per month, difference from previous monthly meter reading).

My spreadsheet simplifies it all into a 1 page summary in landscape, statistics follow (Upto 5 pages inc 1st page), and graphs of cost/consumption cumulative, not cumulative (Upto 26 pages).

ALSO here are my most current version TIER spreadsheets:

TIER SPREADSHEET: CATEGORY TYPE
DATA LIST (L/H) with SUMMARY TABLE (R/H) for MANY ROWS: 1ST TAB most accurate version, examples included, followed by previous versions (To show progression!)
http://www.srands.co.uk/4+Tier_CATEGORY_TYPE.xls

TIER SPREADSHEET: MONEY TYPE
SUMMARY TABLE of MIN/MAX/RATES for ONE ROW.
http://www.srands.co.uk/4+Tier_MONEY_TYPE.xls

Cheers

Stephan

ELEC GAS WATER autocalc: 1st tab, scroll down

TIER2 INSTRUCTIONS (of above file: ELEC GAS WATER autocalc)

TIER 4+ MONEY TYPE

4 TIER+ CATEGORY TYPE


OTHER FORUM CROSS THREADS:
http://www.ozgrid.com/forum/showthre...350#post583350