Detailed utility bill data is—or should be—the foundation of a comprehensive energy management information solution (EMIS). There is a lot of valuable data in utility bills. But how do you get all that data into the EMIS in a manner that meets your utility bill accounting and energy management objectives?
For the purposes of this primer, we’re assuming the following are your objectives:
The good news is that you can achieve the objectives in a relatively pain-free manner, and you don’t have to dedicate hours of valuable employee time to boring, repetitive, error-prone manual data entry. The information below explains how you can get data into an EMIS without manual data entry. Let’s start with a review of utility bill formats.
Paper bills received in the mail are still the most common, but utility bills are typically available in other formats too, such as PDF, flat files (e.g., XLSX, CSV, TXT), XML, and EDI 810. Generally, the electronic formats are accessible through vendor websites, via downloads, file exchanges, or email. Let’s take a detailed look at several of these formats in terms of data richness, processing speed, automation ability, vendor availability, and processing cost.
Paper is like a security blanket for most accounting and energy management departments. In times of disagreement, it’s comforting to point to something you can hold in your hand and read in black and white. We get it. A paper bill is rich in data, with the full listing of charges at one’s disposal, and paper is available from every vendor and probably will be for the foreseeable future.
That said, “paperless” is a highly marketable feature for good reason. The physical page has to make it through the mail to be opened, sorted, entered, and passed among different departments. You hope it’s not lost or left to linger on anyone’s desk for too long. Slow bill processing keeps late fees alive and well.
The PDF (Portable Document Format) bill is an electronic replica of the paper bill, so it shares the advantage of providing rich data. And, since most vendors now provide PDF bills online, this format is readily available for easy viewing and download.
There are two types of PDFs: searchable (vector-based) and image-based. A searchable PDF file has raw data encoded within it. Conversion software can transform that data to word processing or spreadsheet documents for maintaining an energy database. A scanned, or image-based, PDF file is essentially a picture of your utility bill. The image file is more challenging to convert, requiring interpretation/translation by optical character recognition (OCR). Bill processing operations prefer the searchable PDF format.
Organizations that receive a high volume of utility bills often prefer electronic bills over paper or PDF formats. There are several advantages to receiving electronic invoices:
Organizations that receive a high volume of utility bills often prefer electronic bills over paper or PDF formats. There are several advantages to receiving electronic invoices:
Of course, there are also potential gotchas with electronic bills:
Several factors increase the odds of a favorable ROI for receiving utility bills electronically:
Regardless of the electronic bill transfer process or the file format(s) shared, each data field (due date, service dates, cost, and use details, taxes, etc.) must be carefully mapped to ensure that the data is accurately and consistently recorded month after month. Good data leads to good reporting and productive energy analysis.
Electronic bills are typically provided in one of two formats: flat-file or EDI 810 facilities.
Flat files—Comma-Separated Value (CSV), Text (TXT)— and Excel (XLSX) are popular because of their compatibility with many database and spreadsheet applications. Flat files are essentially text files “punctuated” by commas, tabs, or spaces which delineate the data into discrete bundles. Each record consists of a line of text. The next line begins the next record. A database record for an individual’s contact information might look like this:
John,Doe,111 First Street,Wilmington,DE,19804,888-571-9090,firstname.lastname@example.org
The information would correspond to the contact first name, last name, address, city, state, ZIP code, phone number and email address. For utility bill information, the database record might include account number, meter code, commodity, unit of measure, bill start date, bill end date, use, demand, and cost:
Many West Coast utility companies, for example, will provide electronic billing data to customers in a spreadsheet-ready format. The advantage for the consumer is that there is no per-bill fee for receiving the data in this form. Disadvantages include the lack of a hard copy “paper trail,” the need for a software system to interpret and archive the billing data, and in some cases, the inability of the accounting department to process and pay from the provided electronic bill file. More to come on that topic.
EDI stands for Electronic Data Interchange. EDI 810 is the specific standard for transmitting utility bills. EDI 820 is the Payment or Order Remittance Advice typically used by the buyer to respond to the electronic invoice. Both standards are maintained by the American National Standards Institute (ANSI), the governing body for defining EDI formats in the United States.
There are many kinds of specialized EDI file formats, and they are not really “human-readable.” The image below is a portion of an actual EDI 810 utility bill invoice with client information redacted.
The first part of the invoice process is transmission of the EDI 810 invoice by the utility vendor. The typical EDI process has several requirements. One is the EDI 997 Functional Acknowledgment. After the vendor transmits the EDI 810 invoice, the recipient is required to post an EDI 997 as a response. The 997 confirms that the file was received and was intact. This is the vendor’s proof that the bill was received.
These sorts of transmissions will often occur over a Value-Added Network (VAN), which is a kind of commercial internet site that provides a higher level of security and control than what might be available on the public internet.
Once the bill has been received, the actual funds transfer is often coordinated through the Automated Clearinghouse (ACH) system, and an EDI 820 Payment Order/Remittance Advice (an electronic document that specifies all pertinent elements of the electronic transaction) may be effectively wrapped in the ACH banking transaction. The EDI 820 closes the loop on the transaction, providing verification of the completed electronic exchange.
Here is a typical EDI workflow:
Each utility company has an implementation guide that spells out exactly how the 810 transaction will be implemented through their billing system. The guide might easily be a hundred pages long. It will spell out exactly what each of the EDI codes means and what the billing conventions are.
There are some challenges related to transition from paper billing to EDI. The first is that it is usually all or nothing. Most utilities that use EDI for invoicing require EDI for payments. This may require buy-in from your company’s finance department, which may be difficult to achieve if the transition would involve significant changes to established accounting procedures. Some people are uncomfortable not having a paper bill. It’s usually easy to print out a bill likeness from the EMIS, but for some people it just isn’t the same.
Another challenge is the compressed time period typically allowed for the transition from paper to EDI 810. Most vendors do not provide a period of parallel processing, where both paper and electronic bills are delivered. This is unfortunate, because without a significant time period to compare the paper and EDI bill versions, it can be challenging to ensure/verify that the process has been completed satisfactorily for all accounts and meters.
How can you find out if your vendor uses EDI or at least offers EDI? First, browse the vendor website. Search for EDI or electronic billing, and see what information is available. You may find that your vendor(s) have some kind of electronic billing, but what they really might mean is that electronic payment option is available to residential customers only. Make sure that they offer EDI for commercial customers with multiple accounts.
If you don’t see any EDI articles or instructions when searching the vendor website, it is probably an indication that the vendor does not offer EDI. Generally, if the vendor does not have an EDI contact phone number and/or an easily accessible implementation guide, then their EDI program is probably not active or is non-existent. You will find that most of the investor-owned utilities (IOUs) have well-defined EDI processes and offerings.
A capable EMIS can play a valuable role in the EDI process. For example, our EnergyCAP utility bill accounting & energy management software will audit the bills automatically before the data is transmitted to the A/P department for payment. The software can be configured to integrate with existing departmental approval processes. When the bills have been audited and approved for payment, they can be exported from EnergyCAP to the accounts payable system with any required file formatting and general ledger data for each transaction. In this case, EnergyCAP essentially becomes a smart front-end for the customer accounting system.
One of the first questions to address when considering whether to modify your bill processing workflow concerns how bills will make it from your utility vendor to the EMIS. If you receive a relatively small number of invoices from just a few vendors, it may make sense to implement a do-it-yourself (DIY) process. However, most organizations that have or plan to implement an EMIS do so because they have a high volume of data to analyze. When there’s a high volume of bills to process or limited data entry resources, you may choose to remain hands-off, never touching a bill. Many organizations benefit from the peace of mind that comes with an outsourced bill processing solution, like our Bill CAPtureSM service.
Manual Input – If hand-keying is your thing, go for it. A highly functional EMIS, like EnergyCAP, will let you configure your bill entry template to include as much or as little information as you need. Make sure your EMIS is adaptable to your needs and allows you to track an unlimited number of bill line items or simply cost and consumption.
Manual Upload – Do your utility vendors make utility bill data available in spreadsheet files? Uploading those spreadsheets into your EMIS can be fast and convenient, assuming the vendor-provided data is provided in a format that is accepted by your EMIS. EnergyCAP is bill import format flexible, allowing users to map columns in the spreadsheet to fields in the EnergyCAP database. Once mapped, users can simply browse to and import files.
If you prefer to let someone else do the data entry while you and your staff focus on higher value energy analysis tasks, outsourcing may be the ideal solution for you.
With outsourcing, a third-party vendor will assume varying degrees of responsibility for processing your utility bills. Our Bill CAPture service processes thousands of utility bills each month, so we’ll use the Bill CAPture service as an example of how an outsource service provider might work. We’ll start with how your bills are delivered to Bill CAPture:
Once the bills are in EnergyCAP, then certain audits and reports are automatically run on your bills to help your organization identify and address anomalies.
Outsourced utility bill processing services typically offer multiple levels of service. Some focus strictly on obtaining the data and entering it into the EMIS. Others go beyond data entry and provide utility bill auditing, issue resolution, and even bill payment. Our Bill CAPture Processing & Account Management level of service is the ideal solution for organizations that prefer to place utility bill entry, auditing, and issue resolution in the hands of third-party utility bill, accounting, and energy management experts.
We assign a Bill CAPture Specialist to collaborate with you during service implementation to establish and document a best practices workflow that covers the follow-up, escalation, and resolution of flagged bill audit issues, including contacting utility vendors to get to the bottom of billing issues.
The Specialist is tasked with the ongoing monitoring of utility bill data audits and utility vendor follow-up and resolution. The Specialist also works to identify and resolve audit flags and anomalies in the following areas…
Flagged bills will be a key part of your regular Bill CAPture Processing & Management meeting. Anomalies in data that do not require vendor follow-up will be provided to your designated staff via flag notifications or EnergyCAP reports. EnergyCAP’s report distribution functionality will be leveraged to automate the process of communicating information, which brings us to our next topic: Workflow.
An alternative approach is to outsource the entire utility bill workflow, including bill payment. In this scenario, the third-party vendor receives and processes your utility bills and then makes payments via ACH, credit card, paper checks, or other means on your behalf. After a batch of bills is processed, funds are requested to draw payments from your organization’s bank account. Once funds are posted, payments for all utility bills are made, and payment details are confirmed. Transactions can be posted to your accounting system—as described below—to ensure all accounts have been paid.
Utility bill entry, whether done internally or outsourced, is typically the first step in a broader workflow that includes bill auditing, analysis, and payment. In a shameless plug, we’ll note again that a large part of EnergyCAP’s value lies in the streamlined workflow it enables.
In order to unlock the most value from the EnergyCAP application, we recommend a workflow wherein the EnergyCAP application acts as the front end to your utility bill payment workflow. The two primary post-data entry components include bill auditing and interfacing with your organization’s accounting (AP and GL) systems.
EnergyCAP’s utility bill audits automatically and immediately check each bill upon entry or import to verify accuracy and flag potential problems. Audit exceptions are highlighted on the bill to help your team identify and resolve issues quickly. During manual bill entry, possible bill problems are highlighted as you type within the bill entry screen to help you catch errors before bills are saved to your database.
You can also manually flag bills that warrant additional analysis or verification, insert notes or questions to be answered, and assign the bills to any authorized EnergyCAP user in your organization for resolution. Actions taken are recorded, and savings realized can be documented in the Cost Recovery field.
The cost recovery amount represents the potential funds to be recovered through the resolution of the bill issue. EnergyCAP includes numerous reports and dashboards that bring visibility to items that need to be resolved by system users.
If you’re evaluating multiple EMIS, we recommend that you dedicate an appropriate amount of time to evaluate each solution’s bill auditing functionality. Failing to identify billing errors or issues prior to payment (or ever) can result in substantial financial losses that are difficult and time-consuming to recoup months or years after the bills are paid.
It’s common procedure in many organizations for the Accounting department to receive and automatically pay utility bills prior to review or approval from the energy or facility manager. That process may be fast, but how does Accounting know if the bill was correct?
Most Accounting departments don’t look at usage values on the utility bills and, therefore, are not able to compare unit costs or confirm appropriate rate schedules. A/P may or may not know when a utility account is deactivated and should no longer be paid, or that a malfunctioning meter produced an inaccurate bill.
There is a better way to audit, process, and manage utility bills. Incorporate your EMIS (e.g., EnergyCAP) as the smart "front-end" of your accounts payable or general ledger system. In the diagram below, utility bills are entered or imported into EnergyCAP when received, verified through audits, and presented for review and approval by the appropriate staff member(s). Upon approval, bill details are exported from EnergyCAP in a format that is import-ready for your specific accounting system.
When EnergyCAP is the EMIS, we develop a custom accounting interface for each organization and for any financial system, including CAS, PeopleSoft, SAP, Oracle, MR, QuickBooks, Great Plains, Banner, and Performance. If your system isn't on the list, no worries, we can create interfaces to any A/P and G/L system, even proprietary applications.
The streamlined EnergyCAP-centered workflow below eliminates redundant data entry—ideally, all manual data entry—and ensures that your utility bills are audited and approved before they are paid.
Accounts Payable Data Export
Many campus-type organizations—universities, hospitals, prisons, industrial complexes, military bases —generate internal utility “bills” to accurately account for energy distributed to the various departments or divisions within the organization. These are monthly allocations based on submeter readings, percentage splits, or formulas. Your organization may refer to the self-generate bills as chargebacks, cost or usage allocations, re-billings, or distributions.
Regardless of chosen terminology, chargeback calculations can be extremely complex and time-consuming. Our implementation engineers have worked with a number of organizations that were using elaborate homegrown chargeback processes, including an impressive and dizzying array of Excel spreadsheets. These spreadsheet systems had in many cases been developed over a period of years and were not flexible enough to accommodate all the needs of a comprehensive chargeback process. Homegrown systems also tend to be labor-intensive, and they often rely on a single individual’s know-how and expertise. If the individual who has put together all the spreadsheets is out sick, then the organization may have a problem.
Ultimately, chargebacks should flow smoothly to the accounting system, just like bills received from utility vendors. Without an automated interface for transferring chargeback data to and from the Accounting office, then someone is going to have to key that utility billing information all over again. As discussed above, the data entry process can be very cumbersome, inefficient, and error prone.
So what are the best ways to manage campus chargebacks?
The most important way to maintain an effective chargeback system is to use submeters to track energy use in the most granular fashion possible. Allocations based on floor area and an agreed-upon weighting factor may be acceptable, but submetering will be the best by far.
Secondly, invest in an EMIS that includes chargeback functionality. The EMIS should be capable of producing all required energy management reports, and data must be portable to the organization’s accounting system. The result of a quality chargeback process should be that the energy data is processed in a timely fashion, with a full accounting for all related expenses. Chargeback data should be transparent, auditable, and shareable.
We encourage you to read our eBook, Making the Most of Your Campus Energy Data, for a more in-depth discussion of campus chargebacks.