Integrating Business Processes: Modeling financial settlements in deregulated electricity markets

To successfully participate in the North American power market, the front office must be provided with timely feedback on traders’ financial positions and the back office must be able to reconcile the financial settlements from market operators. In the deregulated electricity delivery market of California, this is an enormously complicated task, with complex and constantly changing calculations.

A toolset that offers flexibility, transparency and dimensionality in implementing highly complex business rules is necessary to accurately perform these calculations. Multidimensional business modeling technology can help companies integrate these business processes across front and back offices–something that’s especially important for the small to mid-sized firms that cannot afford to deploy enterprise-wide systems and rely mostly on spreadsheets to meet their settlement needs.

The supply chain puzzle

Because electricity cannot be stored, it must be generated upon demand, delivered to the right buyer at the right time, and meet transmission constraints. This creates a gigantic supply chain puzzle. All of the transactions must be scheduled with the power grid operator. In California, that’s the California Independent System Operator, CAISO, which also operates the spot markets for power and settles the transactions.

After delivery of the power, the front office needs to estimate all the charges and payments to evaluate its financial positions on a daily basis. Once the actual generation and usage data are available, the financial settlement process requires accurate and timely calculations by the back office–essential because market participants have a limited time to reconcile their transactions and dispute their invoices.

Add constantly changing business rules, and the task becomes even more daunting. Power market participants working in the California market are preparing for a complete market redesign and technology upgrade that includes an entirely new set of business rules, part of the aftermath of the Enron-related energy crisis. The major market redesign effort underway is the CAISO’s Market Redesign and Technology Upgrade (MRTU). The ambitious project is scheduled to go live in the latter part of 2008.

Market redesign in California

A notable feature of the new market design is the use of locational marginal prices (LMPs) at approximately 3,000 points (or nodes) to balance the system supply and demand at the least total system cost while meeting operational and security constraints. Based on this nodal design, CAISO will add a forward market (Day-Ahead Market) and transmission rights (Congestion Revenue Rights) markets, and redesign the Real-Time and Ancillary Services markets that it currently operates.

Significant changes to the technology have been made to support the additional complexities and expanded financial products. “Bid-to-bill” processes to complete an energy transaction in the California market have become more complex in this new environment. These changes add up to a whole new ball game for market participants with respect to trading, scheduling and settling energy transactions, creating special challenges for small to mid-sized market participants that may not have the resources for an enterprise-wide energy transaction infrastructure. (See chart.)

Spreadsheets inadequate for new settlement process

A typical “bid-to-bill” process in the deregulated energy market involves submitting bids to sell or buy spot energy and ancillary service products, scheduling all market and contract transactions, and finally settling financially with the market operator. The front office often maintains daily estimates of its financial positions (usually only a small subset of charge codes) and provides monthly estimates to the accounting group for month-end closings. The back office has the responsibility of validating the settlements from the market operator and disputing any discrepancies. It is also often responsible for settling internally between different business units or generation facilities, requiring further allocations and assignments of charges and payments. Once the settlement statements are available from the market operator, the estimates the front office sent to the accounting group are replaced with the actual settlements from the back office.

Market participants need accurate settlement information to maximize the value of their energy portfolio and to manage transaction risks. Settlement data can be used to develop bidding strategies and back-test them after energy has been delivered. They are also essential to minimize avoidable financial penalties levied by the market operator. Given the number of market data changes and settlement reruns the California market has experienced, a robust settlement validation and disputes capability certainly offers a compelling value proposition for any market participant.

Despite the value that accurate settlement capabilities can add to an organization, integrating them in the entire chain of the “bid-to-bill” process is difficult for many organizations. Some market participants have implemented a class of enterprise-wide tools called Energy Transaction and Risk Management (ETRM) systems, but small to mid-sized market participants frequently rely mostly on spreadsheets to meet their settlement needs.

Click here to enlarge image

Financial settlement of CAISO market transactions typically involves allocations and assignments of market costs and revenues using billing determinants that are based on cost-causation principles. Calculating all the billing determinants that go into final billable quantities for each charge code is often very complex, involving numerous variables with different combinations of dimensions such as time, resource type, transaction type, location and other attributes. Due to the complex data relationships, two-dimensional spreadsheet-based templates are inadequate to meet the demands of CAISO’s new settlement processes. Plus, auditing spreadsheet calculations to ensure conformance with CAISO specifications and updating formulas as business rules change is a formidable challenge for many MRTU market participants.

Transitioning to the new CAISO settlement design presents another challenge: adapting existing technologies to handle the new file format based on eXtensible Markup Language (XML). The XML-based settlement files represent a great improvement in terms of computers exchanging information efficiently but the new files are virtually impossible to decipher for business analysts because of complex data relationships and volume of the data. Without significant investments in information technology infrastructure and capabilities, adapting existing technologies may not be a viable option.

CAISO will be providing an enormous volume of data for market participants. The new settlement design will have at minimum three issuances of settlement statements for each trade date along with a bill determinant file. The latest test version of just one bill determinant file exceeded 80 megabytes, with 1.2 million records. Spreadsheets will not be able to manage such a large volume of data.

Databases can easily accommodate these settlement files but the development and maintenance of databases to perform settlement calculations may exceed the IT resources of many small and mid-size market participants. Today, new modeling software offers the technology that companies need to manage these complex settlement demands. Power Market Consulting Inc., for example, has developed an analytical tool designed to validate CAISO’s new charge codes based on exact formulas specified in CAISO’s Settlements Business Practice Manual (BPM). The settlement solution, Rozetta, is available either as a standalone desktop application or is used as a front-end to an XML database that contains CAISO MRTU settlement files in their native format.

Multidimensional modeling software directly addresses the shortcomings that most common settlements methods and tools currently have with CAISO’s new Settlements and Market Clearing (SaMC) process design. Modeling software:

  • provides the ease of use of spreadsheet technology with the power of database technology.
  • provides the optimal balance for a dynamic market with lots of data.
  • is very robust and can handle virtually any amount of data.
  • is limited only by the operating system and hardware constraints of the user’s equipment.

Spreadsheet technology relies on physical cell locations in an x-y grid. It is extremely difficult to alter the data structure in a complex model without breaking the dependencies, a nightmare in terms of implementing any changes in the model. With as many formulas as the number of calculated cells, it is very hard to get a clear picture of what each “cell” represents, leading to potential audit issues. This approach is particularly limiting when determining energy settlements, as companies need to review multiple data points from a variety of perspectives, quickly and easily.

Multidimensional modeling technology enables companies to represent all the variables–inputs, intermediate billing determinants, and ultimately the settlement outputs–in matrices by their attributes. The data can be presented in a different layout by simply moving the tiles (attribute labels) around the matrix. Spreadsheets simply do not have this kind of capability or the flexibility to capture and work with multi-dimensional data. Complex settlements formulas that define the mathematical relationship between input and output variables are modeled with “write-once” formulas. Unlike formulas in spreadsheets, the modeling software’s formulas are not tied to physical cells and need not be repeated as copied formulas. The robust approach to computations makes complex settlements formulas much easier to develop, audit and maintain.

Proprietary database applications can perform complex calculations, but constant programming and maintenance means they are costly and often aren’t nimble enough for changing market rules. Spreadsheets, on the other hand, can’t handle the vast quantities of data and multiple dimensions, and become unmanageable, prone to formula error, and unworkable when incorporating new business rules and functions. A settlement solution using multidimensional modeling technology fills the gap between databases and spreadsheets, especially for small and mid-sized organizations. Low cost, ease of use, power and flexibility make multidimensional modeling software the tool of choice.


Henry Shin is the founder of Power Market Consulting Inc., an authorized partner of business modeling and analytics software provider Quantrix. He has been providing regulatory and business systems consulting services since the start-up of California’s deregulated power market. Shin has extensive experience in the development and implementation of energy settlement systems with transaction values in multi-billion dollars. Dr. Larry Conn has been closely involved in the settlement of energy in the deregulated markets of California for the past 10 years. Currently he oversees the complex re-settlement of energy transactions from prior years in various FERC proceedings, You may contact them at and respectively.

Previous articleELP Volume 86 Issue 3
Next articleStudy: Utility companies fared well for an internal agreement of contact center measurement

No posts to display