An application for duplicating electric utility bills in the deregulated energy market based on published service classifications and rate schedules. This tool was used by an energy consulting firm to audit bills for commercial real estate holdings, where errors could amount to tens of thousands of dollars, and as a basis for forecasting future billing.
Client: An innovative energy consulting firm
Budget: $35,000
Timeframe: 2 months of development time
Estimated ROI: Client took advantage of their unique competitive edge to double staff and move to a larger office, at the same time requiring less effort to service a portfolio of over 600 commercial properties. Expedited workflow freed resources to branch into other profitable lines of business.
Challenge
Many states have deregulated their energy markets, giving customers the power to choose where their energy comes from. Increased competition yields better service and improved energy efficiency. Power generation is decoupled from the utility infrastructure, with these components billed separately. Although increased transparency benefits customers, the energy market is still incredibly complex. There are opportunities for energy consulting firms to demystify opaque areas, providing a variety of informational and analytical services, in addition to tangible cost-saving efforts in budgeting and procurement.
A particular pain point during early deregulation was the analysis and validation of paper billing. Our client was using an array of manual calculators built in Excel to duplicate the bill calculations of the utility provider. These audits were used to catch costly billing errors, but had become so labor intensive that their staff could not keep up with the growth of their company. Furthermore, while tariff rates were openly published, some aspects of the bill structure remained cryptic. We were asked to automate the process and provide a streamlined tool to audit all of their customer bills, as well as provide forecasting tools for budgeting.
Solution
We initially used Microsoft Access as a front end for rapid application development, backed by Microsoft SQL Server for enterprise-level storage to handle the large quantity of data required.
Because the bill structures were constantly changing, we developed a custom scripting language in which we encapsulated bill calculation logic in simple text files. Every service classification could have multiple calculation templates, which we versioned and maintained historically in order to support a data warehouse of bill calculations. We leveraged contacts at the utility company to decipher unknown parts of the calculation logic.
At this point in time, tariff rates were published in unstructured paper tomes (distributed as PDF files). It wasn’t practical to parse the arbitrary structure of these documents electronically, so we provided a tool for end users to enter published rates manually. New rates only came out a few times per year, and we eliminated the duplicate entry required by their old system. Our tool allowed projected rates to stand in for actual rates for the purpose of budget projections.
We provided customers with transparent bill calculations comparable to the utility’s own detailed bill versions (the utility typically provided a mere summary). Any significant variance would indicate a billing error that needed to be remedied by the utility — and happened more frequently than you’d expect!
Technologies
- Microsoft SQL Server
- Microsoft Access
- VB.NET
- VBA
Conclusion
We provided our client with a solution unique in their industry. No other energy consulting company was able to duplicate the utility’s own billing calculations on a mass scale like this. In fact, we found that our calculation tool was more sophisticated than the utility’s itself! Our solution provided our client a competitive edge that fueled their early growth, and we saw them double in size and continued supporting them when they moved to a larger office space.
In future improvements, we migrated critical business logic to Windows DLLs coded in VB.NET, and migrated the front end to an ASP.NET MVC web application. Reports were served on customer-facing PHP website.