From Multiple Spreadsheets to Commodity Trading and Risk Management Technology

From Multiple Spreadsheets to Commodity Trading and Risk Management Technology

Michael Hinton

CCO and Senior VP, Product and Solutions at Allegro Development

Views 487

From Multiple Spreadsheets to Commodity Trading and Risk Management Technology

18.07.2016 12:15 pm

From oil price plunges to cheap gas, from renewables’ in-built unpredictability to baseline shifts in the global dynamics of energy market supply and demand, the risk profile of energy trades is increasing and petrochemical companies are finding themselves smack in the middle of these changeable times.

However, the level of uncertainty that defines – and will continue to define - energy markets in the near term should banish any complacency around trends that dominated during normal conditions. Petchems need to prepare for both today’s challenges and those to come, by adopting the best available risk management tools.

Take the case of the Dow Chemical Company, a global Fortune 50 petrochemical company combining the power of science and technology to innovate what is essential to human progress. The company is driving innovations that extract value from the intersection of chemical, physical and biological sciences to help address many of the world's most challenging problems, such as the need for clean water, clean energy generation and conservation, and increasing agricultural productivity.

Dow's integrated, market-driven, industry-leading portfolio of specialty chemical, advanced materials, agro sciences and plastics businesses delivers a broad range of technology-based products and solutions.

Back in 1997 Dow Chemical selected a commodity trading and risk management (CTRM) system from Allegro Development to increase visibility into daily risk and to react to change more promptly. More recently, the company undertook a comprehensive upgrade of the Allegro system to the latest version of the software.  

With the help of system integrator capSpire, the upgrade moved Dow's Hydrocarbons and Energy business, including their U.S. and Canadian Natural Gas, U.S. and European Feedstocks, and Global Risk Group, from the legacy to the new platform in under eight months, significantly improving the company's P&L reporting capabilities.

A short delivery timeframe and large project scope made the transition a challenge, but close collaboration between software provider and integrator, along with a proven implementation toolkit employed on the case, enabled Dow to accomplish its objectives.


Dow’s Hydrocarbons and Energy business has long been a world leader in the production of crude oil and styrene. It is responsible for the procurement of fuels and crude oil-based raw materials, and supplying products and power for use in the company’s global operations.

Hydrocarbons and Energy also purchase natural gas liquid feedstocks to produce products such as butadiene, styrene, ethylene, propylene, power and steam. Dow needed to manage the risk of the natural gas and natural gas liquids commodities, since the purchases of these products directly impact their profitability.

Prior to 1997, the business was executing its risk management function with multiple spreadsheets to monitor pricing, trading, transactions and risk management. Reliance on spreadsheets was limiting visibility into the daily risks affecting its portfolio, affecting its ability to react and adapt to changes quickly.

They needed to replace spreadsheets with a state-of-the-industry trading, transaction and risk management system that could manage their highly complex operations, including global transportation of raw material and risk management strategies.

Dow began the search for a CTRM platform to streamline business processes and improve operational efficiency. Specifically they required decision support tools that could provide a wide range of valuation analyses -- value at risk, mark to market, and credit value at risk – which could integrate with all business processes, ranging from deal capture to settlement.

With company growth and changes in FAS and SOX regulations, Dow also needed a system that could be flexible enough to be modified when necessary and could lower costs of regulatory, tax, and corporate compliance.

After a rigorous vendor evaluation process Dow selected Allegro and purchased the Allegro 4.2 platform to manage its feedstocks portfolio. Dow then upgraded to add treasury functions and global risk management. In 2005, Dow upgraded again to Allegro 7.5, implementing key components, including Connect, Settlement, Trade, Accounts Payable, General Ledger, and Hedge.

The company had worked with the platform successfully for years, but in 2015, new business challenges compelled another evaluation of its risk management tools and capabilities.

The challenge: a comprehensive upgrade, with the clock ticking

With near-term plans to sunset its internal IT infrastructure, Dow was facing an urgent need to upgrade its legacy Allegro CTRM system.

Internal headcount at Dow had also been reduced due to business consolidation, and many manual processes needed to be automated, especially in risk reporting, P&L reporting and tracking of daily changes across Dow's business lines.

The budget was approved for a like-system replacement, with a full review of requirements and processes to be included as part of the upgrade. All businesses were to be upgraded concurrently, with a deadline for delivery set at eight months.

After a quick review of CTRM market and given Dow’s successful 19-year track record with Allegro, the decision was made to go forward without further vendor evaluation. capSpire was called in to orchestrate the transition, with initial system reviews starting in mid-2015.

The solution

The implementation team began by establishing requirements in collaboration with Dow's internal IT and business leads. The analysis included a full process review, business scenario modeling exercise, and systems integration roadmap. During the implementation, custom class events were created in order to replace older, stored procedures and address key gaps.

capSpire formed a small group of highly skilled and experienced Allegro consultants to discern the business and technical challenges, and prioritize mission-critical tasks to meet the deadline and program objectives.

Extension Manager, capSpire’s tool for maintaining class events, was employed to reduce the complexity of managing extension changes and protecting them for future upgrades.

One area of concern that bore out during the review was Dow's existing P&L reporting process. To streamline it, data was extracted from Allegro and sent to an array of access databases to create a global risk book. By leveraging Allegro’s core functionality, combined with enhancements for calculating MTD, QTD, and YTD P&L for realized and unrealized positions, Dow is now able to produce its risk book directly out of Allegro, without going through a network of spreadsheets or data manipulation.

Measurable business benefits

Successfully completing the project ahead of time and within budget was only the first of many accomplishments the team achieved for the client. The upgrade, affecting three separate business units within Dow, proved to enhance position and P&L reporting, while eliminating much of the manual labor associated with these tasks in the past.

Class events associated with risk and data management can now be handled using Allegro's class-event architecture, simplifying the way grid-based jobs and key user interface (UI) enhancements are created and maintained.

Using the capSpire Extension Manager, UI enhancements, such as those related to scheduling, settlement and shipment details, can now be easily moved and secured going forward. The capability has allowed Dow to reconcile end-of-day business valuations in as little as 15 minutes.

The new system continues to provide Dow with sophisticated analytics on its complete portfolio, and interfaces directly to legacy SAP® applications to ensure that general ledger updates occur in real-time. Allegro also provides alignment via a common system of configuration across Dow Hydrocarbons and Energy’s three separate geographically dispersed business groups.

Basic counterparty information is still maintained, and the system still manages contract terms, schedules all physical commodities, actualizes volumes, invoices counterparties, performs inventory valuation/tracking, and settlement of all commodity-based transactions. Also, Allegro supports derivatives trading, valuation, settlement, hedge and derivative accounting.

From sinking oil prices to regulatory over-reach, today’s petchems are exposed to wave after wave of margin killing risks. The Dow Chemical journey teaches us that having the right technology to manage price volatility in essential feedstocks can make the difference between pushing ahead of the competition or watch profitability slide. 

Latest blogs

James Booth PPRO

Brave New World: A Futuristic Vision of Payments

Over the last ten years, the retail e-commerce ecosystem has undergone a wide-ranging transformation. As recently as 2010, the e-commerce and payments value chain were relatively straightforward: Any eCommerce merchant could integrate a payment Read more »

Nish Kotecha Finboot

How blockchain could potentially transform global healthcare in the wake of COVID-19

In the globalised world we live in, entities such as the World Health Organization (WHO) have been established to ensure cooperation between different governments on global health-related issues. In the face of pandemics such as the one we are Read more »

Lina Andolf-Orup Fingerprints

Dispelling biometric myths and misconceptions

Gangsters cutting off enemies’ fingers to access secret locations and spies lifting fingerprints from martini glasses - the imagination of the entertainment world has been running wild ever since biometrics entered the scene. Couple that with the Read more »

Shiran Weitzman Shield

Tackling Apparent Contradictions of Compliance versus Privacy

As technology evolves and becomes more complicated, so too do the moral and ethical dilemmas, along with the associated regulations. However, well-intentioned regulations designed to protect people and businesses alike can sometimes seemingly Read more »

Francis Leclerc Horizon Software

Just about managing: How cloud can help boost trading profits

It’s a tough environment for trading at the moment. Margins are being squeezed across the board to the extent that some major investment banks are completely withdrawing from certain asset classes upon discovering they are not making a profit. Read more »

Related Blogs

Chris Monnery ION Markets

Low Touch in Derivatives: Where do we go from here?

Over the past few months, I've shared my thoughts about the evolving space of electronic flow in derivatives. But the idea I've not explored relates to how we as an industry providing services to buy side clients actually go about making this change Read more »

Ivy Schmerken FlexTrade

MiFID II’s Trading Hereafter: Systematic Internalizers & Block Venue

MiFID II went live on Jan. 3 without much fanfare, but the EU regulation is already shifting trading behavior toward Large-in-scale block trading venues and new venues called systematic internalizers run by banks and high frequency trading firms. Read more »

Ivy Schmerken FlexTrade

Electronic Corporate Bond Trading at the Crossroads

The conversation in electronic corporate bond trading has shifted away from focusing strictly on liquidity to gathering data and analyzing relative bond pricing. “The Holy Grail in the marketplace is pre-trade transparency and it’s very hard to get Read more »

Stefan Negrila Luxoft

MiFID II: Exemptions and Trading Volumes

As expected, most institutions were only partially prepared for MiFID II. Therefore financial regulators have granted last minute exceptions to ease the pain and ensure orderly function of the markets. Futures exchanges in the UK and Germany were Read more »

Ivy Schmerken FlexTrade

Fixed Income Trading Protocols: Going with the Flow

Last week, SEC Chairman Jay Clayton said he asked the staff to develop a plan for creating a Fixed Income Market Structure Committee to advise the SEC on regulatory issues in the fixed-income markets. Clayton said the fixed income committee would be Read more »

Free Newsletter Sign-up
+44 (0) 208 819 32 53 +44 (0) 173 261 71 47
Download Our Mobile App
Financial It Youtube channel