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. |
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 |
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: 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 |
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 |
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 |
All times are GMT +1. The time now is 01:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com