Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Setup / March 2006

Tip: Looking for answers? Try searching our database.

Spreadsheet for utilities

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Stumped - 28 Mar 2006 23:53 GMT
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.
Pete_UK - 29 Mar 2006 01:34 GMT
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
Stumped - 30 Mar 2006 22:31 GMT
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.

> Do you mean electricity, gas, water? Is this for domestic or
> commercial/industrial usage?
[quoted text clipped - 5 lines]
>
> Pete
Pete_UK - 31 Mar 2006 01:11 GMT
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 - 31 Mar 2006 01:17 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.