Rachel K from Atlanta, GA. When I started auditing I would get 24 months of bills from the utility and spend half my analysis time just organizing the data into a usable format. Every utility sends data differently — some in PDF, some in CSV, some in paper printouts, some through an online portal. I finally built a standardized Excel workbook that I use for every client regardless of utility or format. Wanted to share the structure in case it helps other auditors. Tab 1: Account Summary — account numbers, rate schedules, meter numbers, service addresses. Tab 2: Monthly Billing Data — one row per month per account with columns for billing period dates, days in period, kWh, kW demand, billing demand, kVAR, power factor, energy charges, demand charges, each rider/surcharge, taxes, and total. Tab 3: Rate Comparison — current rate vs alternative rates calculated from the Tab 2 usage data. Tab 4: Findings — each error identified with the monthly impact calculated.
My spreadsheet system for organizing utility billing data
Rachel, this is the kind of operational knowledge that separates efficient auditors from ones who waste half their time on data entry. The standardized workbook approach means you build the template once and populate it for every new client. After a few months you can populate the workbook from a stack of bills in about 90 minutes. That leaves more time for the actual analysis.
Rachel, I use a similar structure but I add a Tab 5 for interval data analysis. When I get 15-minute demand data, I import it into a dedicated tab that calculates the peak demand for each billing period, the TOU period allocation, and the load factor. That tab cross-references with Tab 2 to verify the demand charges on the bill match the interval data peaks. It is where I find most of my demand charge errors.
I would add a Tab 0 — a data receipt log. Every time you receive data from a utility, log the date received, what was received, what format, and what is still outstanding. When you are juggling 10 clients with data requests to 6 different utilities, the receipt log keeps you from losing track of what you have and what you are still waiting for.
Walt, the data receipt log is a great addition. I have definitely lost track of outstanding data requests when I was managing multiple clients. Adding it to my template.
Rachel, do you have a standardized process for converting PDF bills into your spreadsheet format? That is my biggest time sink. Some utilities send bills as image PDFs that cannot even be copy-pasted. I end up manually typing numbers from PDF bills which is slow and error-prone.
Marcus, for image PDFs I use a combination of Adobe Acrobat OCR and manual verification. Run OCR on the PDF to extract the text, then copy the numbers into the spreadsheet and verify each one against the original PDF. It is still manual but faster than typing from scratch. For utilities that send CSV or Excel data through their portal, it is much faster — I built import macros that map the utility column format to my standardized workbook columns. The macro for Georgia Power saves me about 45 minutes per client.