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.

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

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:

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

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

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