Ask a Question Sign up for Free 129 Experts currently online. Ask Questions for Free!

Spreadsheet for utilities - Excel Setup

Does anyone have an Excel spreadsheet they use to track utilities usage? Are you willing to share? Any tricks for setting up a simple one? Thanks....

Results 1 to 5 of 5

  1. #1

    Spreadsheet for utilities

    Does anyone have an Excel spreadsheet they use to track utilities usage? Are
    you willing to share? Any tricks for setting up a simple one?

    Thanks.
    Stumped
  2. #2

    Re: Spreadsheet for utilities

    Do you mean electricity, gas, water? Is this for domestic or
    commercial/industrial usage?

    It is relatively easy to do - you need a column for date, from which
    days can be derived, and a column for meter reading. Another column can
    evaluate the difference between the current and the previous meter
    reading. Is this the kind of thing you wanted?

    Pete

    Pete_UK
  3. #3

    Re: Spreadsheet for utilities

    Thanks Pete. Yes it would be for electricity and water primarily and just for
    my own use. Just seems it would be easier than keeping all the monthly
    statements in a paper file.

    "Pete_UK" wrote:
     
    Stumped
  4. #4

    Re: Spreadsheet for utilities

    OK, try this as a template. Use row 1 for headers, and enter the
    following:

    A1: Date
    B1: Days
    C1: Meter Reading
    D1: Reading Type
    E1: Previous Meter
    F1: Units kWh
    G1: Unit Charge
    H1: Standing Charge
    I1: Total Charge
    J1: Tax
    K1: Overall Cost

    This assumes a single-rate meter, rather than day/night meter. In row 2
    put your prices:

    G2: pence/kWh (or cents/kWh)
    H2: pence/day (or cents/day)

    Then in row3 you can enter the starting dates and initial meter
    readings:

    A3: starting date, formatted as dd/mm/yy (or mm/dd/yy if that's what
    you use)
    C3: initial meter reading

    You only need to enter the date and the meter reading from each bill as
    it arrives, so highlight A4 and give the cell a yellow background and
    format as date. Highlight C4 to D4 and also give them a yellow
    background.

    You can enter these formulae on row 4:

    B4: =IF(OR(A4=0,A3=0),0,A4-A3) format as number with 0 dp
    E4: =IF(C4=0,0,C3)
    F4: =IF(C4=0,0,C4-E4)
    G4: =F4*G$2/100 format as currency with 2 dp
    H4: =B4*H$2/100 format as currency with 2 dp
    I4: =G4+H4 format as currency with 2 dp
    J4: this depends on how you are taxed in your country.
    In the UK there is a 5% tax added on to domestic supplies,
    so J2 would contain 5% and the formula here would be:
    =I4*J$2 format as currency with 2 dp
    K4: =J4+I4 format as currency with 2 dp

    You can then highlight the cells A4 to K4, click <copy> and then paste
    them down into the rows below from row 5 onwards.

    When you get a new statement you enter the date in the next available
    cell in column A after A3 and the meter reading in column C of the same
    row. You can check that the bill agrees with the calculations in the
    sheet. You may need to wrap some of the formulae in a ROUND( )
    function.

    You might like to insert a few blank rows at the top of the sheet to
    enable you to record your account number, or the meter ID etc from the
    bills, before you throw them away.

    Water can be treated in the same way if your supply is metered, but you
    may get other charges on your bill, eg. to carry away waste water for
    treatment - you can insert other columns between H and I to take
    account of this, adjusting other formulae as necessary.

    Well, a long post, but hopefully it will start you on your way - let me
    know how you get on.

    Pete

    Pete_UK
  5. #5

    Re: Spreadsheet for utilities

    I didn't mention what column D is for - you can use this to record the
    type of meter reading, e.g. "E" for estimated, "C" for customer, "A"
    for actual reading etc.

    Hope this helps.

    Pete

    Pete_UK

Similar Threads

  1. Adapter not seen by most utilities
    By Guillaume in forum Windows 7 / Vista / XP Networking
    Replies: 3
    Last Post: 04-04, 08:08 PM
  2. Utilities
    By Timmie in forum Software Tools, Tips & Tricks
    Replies: 0
    Last Post: 10-15, 01:31 PM
  3. Disc Utilities didn't help
    By Tom in forum Mac OS
    Replies: 7
    Last Post: 08-05, 09:08 PM
  4. Advice on Mac utilities
    By mark@markscoleman.com in forum Mac OS
    Replies: 11
    Last Post: 07-30, 05:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100