www.allegrodev.com
The Hidden Cost of Spreadsheets
An Allegro White Paper
Energy companies need to decide if they are willing to expose themselves to the risks inherent in spreadsheet-based methods, or are ready to consider the more advanced, automated, accurate approach of an energy trading and risk management (ETRM) software platform.
The Status Quo
Most energy companies rely on spreadsheets to manage at least a few aspects of their business, and in some cases, spreadsheets get the job done. But, at what cost? How much time is wasted, how many opportunities are missed, and how long is trouble brewing before a decision maker becomes aware?
This paper explores the types of exposure spreadsheets can cause a business and the competitive advantage your company could gain with a more sophisticated approach.
Allegro recently completed a market research study regarding the preparedness of the energy sector.
Over 250 survey responses were received from risk managers, traders, finance, logistics, and marketers in energy companies. Three quarters of them were in trading, risk, and finance departments. The fascinating result of the study is that the majority of energy companies today still rely on spreadsheets or in-house developed systems to run their trading and risk management business. That’s 2 out of 3!
Companies using spreadsheets are without doubt experiencing any number of problems that ultimately hinder their ability to be the most successful organization they can be.
Time-Consuming, Cumbersome, & Manual
Capturing and managing all necessary commodity trading and risk data in spreadsheets will consume a trading organization’s valuable time. Collecting, maintaining, and the inevitable rekeying of data using spreadsheets is a very manual, and often painful endeavor. Spreadsheets lack the automation of trade entry, lead to redundant efforts, and demand a great deal of time to aggregate data and create reports. Often by the time all necessary data is compiled, reviewed, and sent to traders and risk managers, the data is no longer the most up-to-date information, which can greatly compromise decision making.
Lack Necessary Reporting & Business Intelligence
Spreadsheets also lack the critical transparency into enterprise-wide operations. With today’s increasingly stringent hedge and regulatory reporting requirements, organizations need accurate, timely documentation of all activities. Generating reports from spreadsheets can be difficult and error-prone, which is simply not acceptable when companies must achieve compliance with regulations. Spreadsheets also provide limited visibility into a portfolio and potential risk, and making business decisions based on unreliable reports can be a detriment to the company’s bottom line.
Organizations need the ability to view mark-to-market (MtM), Value at Risk (VaR), Profit & Loss, and exposure, and doing so in spreadsheets is extremely cumbersome. Additionally, it is very difficult to modify these risk reports to see different levels of granularity or change the way the information is viewed in order to properly interrogate the data.
Impact to Performance and Productivity
Spreadsheets
An in-house developed system A commercial system
Inaccurate & Lack Version Control
It’s no secret that spreadsheets are prone to inaccuracy -- manual processes are bound to introduce errors. When using spreadsheets in energy and commodity markets, inaccuracies can be propagated as spreadsheets move through multiple departments and processes. If a price is entered into a spread- sheet with one decimal point off, all subsequent calculations and processes - valuation, hedging, settlement, invoicing – are impacted by this inaccurate information. Additionally, if a mistake is made when the spreadsheet is created and then passed to another member of the organization, they will make decisions based on this inaccurate data. Spreadsheets lack the controls, approvals, and messaging that would help detect these errors earlier in the transaction lifecycle. Using spread- sheets to manage the highly-detailed and complex nature of energy pricing and transactions can impact data integrity and ultimately, decision making. For many companies, the sheer volume of deals and contracts is problematic for a spreadsheet based approach.
Without a central repository for all trades and related data, companies can have hundreds or even thousands of spreadsheets circulating at any given time. Often different versions of a spreadsheet are used by different traders, managers, and executives. Individuals like to see data differently – a different view or a new report. Because that isn’t easily accomplished with spreadsheets, they will often need to create an entirely new spreadsheet, which requires time and can lead to even more versions of the same document. Spreadsheets don’t support collaboration between different departments as it is very difficult to ensure that every member of the company is working from the most up-to- date, accurate data.
Unable to Scale
Spreadsheets will not support a company looking to scale – in either volume of transactions, or entrance in to new markets or geographies. They simply do not have the ability to capture and maintain the enormous amount of data that a large operation demands. Managing entrance into new markets or products with Excel also limits overall transparency into positions as it is difficult to combine and analyze physical and financial positions across multiple spreadsheets. Inevitably, this has an impact on the organization’s ability to make the most informed decisions across existing and new positions. Spreadsheets don’t provide the flexibility a company needs to grow without adding substantial resources, such as increasing staff.
Lack Auditability & Risk Control
Spreadsheets are impossible to audit and lack the controls a company needs to adhere to internal and governmental policies. When trades are captured in spreadsheets there are no limits or approvals on traders, tradebooks, positions, or credit exposure. Spreadsheets lack the tools to validate and document calculations and processes, and monitor user activity, which can be especially significant in the case of an audit. Without the ability to ensure business process efficiency and provide all necessary documentation of activities, organizations face costly risks around trading, exposure, contracts, cash flow, and compliance.
Case Study: A North American Utility Retires the Spreadsheet
SourceGas is a natural gas local distribution utility headquartered in Lakewood, Colorado, with operations in Arkansas, Colorado, Nebraska, and Wyoming. SourceGas serves nearly 420,000 customers and operates 17,700 miles of distribution, gathering and transmission pipeline, as well as storage facilities.
With its business expanding, and the rising costs of supporting its existing applications, SourceGas was looking for a new gas operations software solution to replace their current systems. The company had been using a combination of spreadsheets, third party software, and fragmented proprietary systems that lacked integration and automation, and was difficult to support.
SourceGas turned to Allegro to replace multiple legacy applications and spreadsheets with a platform to manage and provide transparency into front, middle and back office business activities. The Allegro 8 ETRM platform provides SourceGas with a single integrated toolset for all gas operations activities. The company now has a central repository for all trades and related data as well as full integration across functions and business processes.
Allegro was able to standardize SourceGas’ processes quickly and provide a single system that could effectively manage complex, segmented business processes. SourceGas now has the ability to capture physical and financial natural gas trades, manage transportation, actualization, and invoicing.
By eliminating the disparate systems and spreadsheets, SourceGas now experiences more efficient trade capture, elimination of redundant tasks, and greater position visibility. This visibility supports enhanced market decision making, which allows SourceGas to optimize their portfolio and their transportation activities.
SourceGas has also realized a very significant improvement in trader productivity with the Allegro platform. The elimination of manual data entry and error-prone spreadsheets saves time, improves accuracy of data, and increases mid-office controls.
Additionally, as SourceGas continues to expand its operations, the Allegro platform is flexible and extensible to easily integrate new components and processes.
“SourceGas has realized a very significant
improvement in trader productivity with the Allegro platform. “
Spreadsheets vs. Software
in Energy Trading & Risk Management
Gaining Market Insight with Software Solutions
One very important business objective in the energy and commodity trading markets – improving market insight and decision making - can be achieved by replacing spreadsheets with a more sophisticated approach like an energy trading risk management (ETRM) software solution.
When moving trade and position data between spreadsheets it is very difficult to get an accurate, transparent view of an organization’s activities. If the necessary data is not right at your fingertips, how can managers possibly make timely, optimal decisions that will improve the company’s bottom line? Spreadsheets hinder a company’s ability to efficiently evaluate the market, a trade or a price, the exposure they may be facing, contract deadlines, invoice quantities and statuses, and other information.
Unlike spreadsheets, an integrated software solution links the activities for all commodities and geographies across front, middle and back office operations in real-time. With a holistic view and the ability to easily drill down into more granular data, traders can make the most profitable decisions with the best available position information. Risk managers can aggressively work to mitigate risk and executives can evaluate which parts of the business are thriving or struggling.
Spreadsheets also stand in the way of effectively valuing a company’s assets. Performing position valuation manually with spreadsheets is extremely time consuming and puts companies at risk when calculations aren’t fully documented. When transactions don’t reflect the accurate value of assets or positions, companies face losses, reduced profits, and challenges when dealing with audit committees and restating earnings.
An enterprise software system can effectively capture every detail of even the most complex transactions, and reduces the time necessary to value these assets and positions. Equipped with accurate valuation and real-time transparency at every step in the energy value chain, decision makers have the tools to develop better strategies when participating in the market. Additionally, fully documented calculations and complete audit trails facilitate necessary reporting and regulatory compliance.
Transparency across the enterprise Accurate position analysis Accurate position valuation
Improving Risk Management with Software Solutions
In the business of trading energy and commodities, and the myriad of activities associated with that business, companies face risk in many different forms. Liquidity risk, counterparty credit risk, price risk, and market risk, just to name a few. Organizations need a clear understanding of what they’re up against and the tools to develop strategies to effectively mitigate these risks. Replacing spreadsheets with an integrated software system offers extensive business benefits when it comes to risk management.
Counterparty Credit Risk –
» An ETRM system can help a company limit losses to counterparty default by providing real-time access to internal and external credit rating changes. Integrating data from third-party rating agencies with a counterparty’s published proprietary information gives a company the ability to maintain, develop and generate reliable, timely internal credit scoring models, which helps organizations define effective credit limits. Software also gives risk managers the ability to manage collateral, and calculate and report on available credit in order to respond quickly to credit and collateral requests.
Liquidity Risk –
» Many companies gather and evaluate trading, equity and treasury information on spreadsheets, but when replaced with a single software platform, companies can drastically improve their ability to manage cash, collections and borrowing capacity. Software offers a transparent view into company and counterparty liquidity which helps to optimize overall capital management.
By calculating cash flows – settled, as well as projected – and combining these positions with a company’s capital and debt structure, they can better manage liquidity risk.
Basis Risk –
» Managing basis risk in a spreadsheet is extremely difficult and therefore is commonly overlooked. Basis exposure can be actively mitigated by replacing spreadsheets with an ETRM solution. Through disaggregation of forward curves into their hub, basis, premium and differential components, users can more effectively hedge those components. Knowing where the exposure exists, traders can then hedge that risk via basis swaps or forward contracts.
Commodity Cost Risk –
» To manage commodity cost risk, companies need complete transparency into their commodity needs, and the pricing and availability of commodities in the market. Software allows these companies to manage contracts, capture real-time pricing, implement hedging strategies, and perform simulation and what-if scenarios. Organizations can effectively minimize total commodity costs with real-time position-valuation and forward-looking position visibility.
Operational Risk –
» Software solutions can equip a company with numerous tools to minimize operational risk, including automating workflows with trader templates, triggering email alerts, and setting credit or position limits. A comprehensive ETRM system allows users to implement an audit framework that tracks every change that is made in the system; who made the change, when it occurred, who changed it, what changed, etc. Organizations can also set limits that prohibit users from taking certain actions and implement workflow processes that require approvals before a transaction can move forward. For example, if a trader exceeds a limit set on the volume he is allowed to trade, or he’s reached his Value at Risk (VaR), Mark-to-market (MtM), or exposure limits, he will automatically be prevented from confirming that trade and may require management approval. Additionally, mangers can be alerted to user changes and limit breaches Software offers a transparent view
into company and counterparty liquidity which helps to optimize overall capital management.
Rapid trade entry Information accuracy Optimal decision making
Increasing Efficiency with Software Solutions
Integrated software solutions have changed the way information is disseminated across an organization.
Instead of sending around multiple spreadsheets in order to enter and confirm trades, perform analysis or create reports, ETRM users can work from a system that automatically updates activity in real-time. No matter what part of the world a user is in, or what time of day they are entering data, all other users across the enterprise can see that information and make trading and risk decisions based on that up-to-date data. Values like Profit & Loss, Value at Risk (VaR), mark-to- market (MtM), and others risk metrics can also be automatically recalculated with the latest infor- mation.
Software can help eliminate many of the manual, time-consuming processes that traders must complete when working with spreadsheets. Anything that can be done to automate the trade entry process will result in more time for seizing profitable opportunities. Trade templates and trader defaults help streamline trading, improve accuracy, and enable rapid trade entry. Software can be used to customize trade capture views, pre-populate templates based on trader profiles, and handle even the most complex trades. Improving trade velocity and accuracy will allow traders more time to evaluate the market, make optimal decisions, and ultimately affect profitability.
Integration of all processes across an enterprise also allows more rigorous analysis and helps strategy development. An effective ETRM solution can connect to a wide variety of pricing providers, exchanges, ERP systems, and other third-party applications. There’s no need to manually transfer pricing or exchange data between multiple systems and spreadsheets because those processes are automated with the use of software.
Achieving Compliance with Software Solutions
An integrated software solution provides a more efficient approach by embedding compliance into every business process across the organization. Using a system capable of integrating multiple commodities and geographies, physical and financial assets, and front, middle, and back office operations, allows an organization the ability to automate and track activities to minimize overall exposure. A single data repository, end-to-end tracking and archiving, and the ability to set limits and approvals, gives organizations visibility and tighter control around compliance requirements.
An ETRM solution reduces errors and standardizes business processes by tracking every user action, message and document associated with the compliance process. Management can define and maintain a compliance infrastructure that meets their specific regulatory needs and helps enforce established policies.
Software automates hedge management processes with tools to account for the hedging of physical trades with derivative trades and the ability to measure and report hedge effectiveness for governmental compliance. Effective ETRM software can also simplify the processes around generating, documenting, and exporting necessary reports. Managers can rely on the accuracy of the reports and reference detailed documentation that supports their financial statements and filings.
Regulatory environments can be very dynamic, in addition to the fact that as business goals change, companies may find they need to meet new regulatory requirements. In the case of Dodd-Frank Reform, this means sweeping changes with significant impact on how energy companies transact in derivative markets. In order to effectively meet Dodd-Frank requirements, and other changing regulatory demands, organizations need to have tools in place that will provide the necessary documentation and reporting. ETRM software supports this compliance with real-time position Business controls
Efficient audit trails Effective reporting
visibility, audit trails, workflow tools to enforce processes, and the ability to measure risk based on a variety of factors. With a compliance infrastructure in place, companies can readily adjust to meet new and changing regulations.
Whether around hedge accounting, financial accounting, FERC, emissions compliance, or derivatives regulation, implementing an enterprise-wide platform can take much of the pain out of achieving compliance.
Growing Your Business with Software Solutions
If a company is relying on various people maintaining and manipulating hundreds of spreadsheets and are looking to double their trading activities, it is not practical to think they’ll have to add staff and create and maintain twice as many spreadsheets. That simply isn’t the most efficient way to manage that growth.
A standardized software system can provide the framework to support all aspects of the new market, geography, or product without adding resources. Software built with .NET architecture and using XML web services enables full integration across all business activities and the flexibility to scale as a company pursues growth or business needs change. Web services provide fast, reliable connections that enable incremental addition of functionality. These connections allow integration between functions inside the software as well as the ability to connect to external applications and data sources. Therefore, adding more pipelines or refineries, performing more analytics, complying with new regulations, introducing new hedging strategies, or accessing external pricing data can be done efficiently without adding resources or disrupting business.
Effective ETRM software is powered by grid computing which allows the system to manage numerically intensive processes without degrading performance. With this type of software in place, organizations can easily scale their solution to meet changing needs and business growth. No matter the volume, as data points change, such as physical quantity, price or volatility, calculations occur automatically and the new information is instantly distributed across the enterprise.
Replacing spreadsheets with an ETRM platform companies can support growth by automating and streamlining business processes to capture data, manage logistics, perform advanced analysis and improve accuracy. Instead of bogging employees down with more spreadsheets, more prices, and more time-consuming calculations, companies can rely on software for what can be automated, and focus instead on company growth, decision making, and market opportunities.
Scalability .NET architecture Grid computing
Planning for Growth
Your business needs to be prepared for the inevitable growth in its future. Are you ready? Let’s take a minute to think about the size of your energy business today, and then estimate the scope of your business in 18 months.
Today In 18 Months
The Number of Trades
Trade Volume
The Value of Your Energy Portfolio The Value of a 1% Increase in Trade Profitability
Number of Counterparties
Number of Regulatory Reports Needed for Compliance in All Jurisdictions Staff who support Front, Middle, and Back Office Activities
For most energy companies, transaction volumes, regulatory requirements, and business complexity are likely to increase over the next 18 months. This might be due to acquisitions, entry into new commodity markets, new geographies, or the organic growth of your business.
However, while the value of your energy portfolio is growing, it is likely that the size of your staff will stay the same and may even be expected to decrease. As your business grows, the cost of missed opportunities is likely to increase as well. The difficult truth is that spreadsheets don’t have the ability to scale with your business and manage the complexity associated with large portfolios, and they lack the speed and market insight that the best performing companies need to outperform their peers.
Increasing regulatory requirements alone (such as Dodd Frank in the U.S.) are reason enough to make the safe prediction that spreadsheets are on their way out. Energy companies need to decide if they are willing to expose themselves to the risks inherent in spreadsheet-based methods, or are ready to consider the more advanced, automated, accurate approach of an energy trading and risk management (ETRM) software platform.
We invite you to learn more about the proven tools that are available today to achieve your business objectives and to most profitably manage your energy portfolio.
As your business grows, the cost of missed opportunities is likely to increase as well.
About Allegro
Allegro is a global leader in energy trading & risk management solutions for power and gas utilities, refiners, producers, traders, and commodity consumers. With more than 27 years of deep industry expertise, Allegro’s enterprise platform drives profitability and efficiency across front, middle, and back offices, while managing the complex logistics associated with physical commodities.
Allegro provides customers with agile solutions to manage risk across natural gas, power, coal, crude oil, petroleum products, emissions, and other commodity markets, allowing decision makers to hedge and execute with confidence. Headquartered in Dallas, Texas, Allegro has offices in Calgary, Houston, London, Singapore, Sydney, and Zurich, along with a global network of partners.
For More Information Contact: Christie Lindstrom; Media & Analyst Relations Manager, Allegro Development at media@allegrodev.com or call +1.214.237.8117.
© 2012 Allegro Development Corporation. All rights reserved. All trademarks and copyrights are the property of the respective owners. This material cannot be duplicated or distributed without express written permission of Allegro Development Corporation. Allegro® is a registered trademark of Allegro Development Corporation.
For more information go to www.allegrodev.com or call us at +1.888.239.6850 North America +1.214.237.8000 Europe +44(0)20.7382.4310 Asia Pacific +65.6236.5730
