Spreadsheets Revisited

At the ETEM conference last week, quite a lot of panel time was dedicated to a discussion of spreadsheets versus ETRM. To me, this is a long dead topic that has been done to death but…. new people and new entities come into the industry all of the time that do not have the benefit of twenty odd years talking, eating and living ETRM software. I think at one point, I told the panel that since we potentially had a room full of newbies, that we ought to revisit the discussion and I will do so now, in this article for the same reason. ETEM was marketed as “the only forum of its kind dedicated to helping high growth firms optimize their energy trading business units and transition into more efficient, automated organizations,” and with the surge in renewable generation, a lot of smaller new suppliers ave entered power markets, for example, and are now shopping for systems or building their own solutions.

The panel recognized that spreadsheets are and have been an integral part of the business yet with new regulations, significantly more oversight from lending banks and even counterparty’s, running the trading side of the business on spreadsheets probably wasn’t a great idea. We heard a number of generic and actual ‘horror stories’ regarding the use of spreadsheets including things like accidentally replacing formulas with data, the spreadsheet creator leaving the company or going on vacation, and so on. I added in the fact that spreadsheets generally have no audit trail so proving who did what to whom, when and why is impossible. The discussion very much reminded me of a report that we generated in 2015, sponsored by Gen10, called The Use of Spreadsheets. It is still available for those who may be interested.

In that study, it was apparent that convenience was the seen as the biggest factor in favor of using spreadsheets. They are easy to use, cost-effective and fast to set up. Other reasons included being unable to modify the current ETRM solution or the fact that it was expensive to do so. On the other side of the coin, the study found that lack of an audit trail, ease of making errors, lack of controls and concurrent access were also other highly ranked reasons to avoid them. What was interesting too in the survey were the horror stories! Here they are,

• A $2 million trading error was traced to the replacement of a cell formula with a fixed value,
• Numbers were transposed on data entry into a master spreadsheet and downstream spreadsheets had no visibility of that error in the master spreadsheet
• An added row or column didn’t get included in the calculations,
• Lack of audit controls including time stamp and approvals for the trade ticket,
• Different Versions of the same Spreadsheet being used simultaneously and no one knew until too late,
• A bug in VBA code, written internally, led to huge losses,
• The spreadsheet needed to be tweaked every month-end but when the owner of the spreadsheet was on holiday, reporting had to be suspended – major headache,
• Incorrect positions as a result of missing certain cells on the spreadsheet,
• An attempt to sort a column resulted in wrong values and no one noticed. The downstream spreadsheets were all impacted too,
• Copy and paste errors where formulas got copied rather then numerical values and all the results were wrong,
• Incorrect position data as some cells were omitted in the final calculation,
• Very common to find errors in trader spreadsheets being used to determine P&L. Traders will almost always put in some level of “Mark to Intent” numbers in the sheets to support larger bonuses than would be granted using Risk System numbers,
• Spreadsheet pulling in incomplete data resulting in risk mismanagement errors. 

The paper found that although spreadsheets were convenient and cost effective they posed a huge risk to the business. Of course, back in 2015, the alternatives were probably a rather pricey purchase and implementation of a commercial ETRM solution or time consuming and expensive custom build. There were some cloud-enabled solutions around back then like Gen10, the sponsor of the report, and others (Agiboo, ION Aspect, OATI, Contigo etc.) but there was also a little reluctance to move to the cloud back then too. For those attending ETEM, the fact of the matter is that they are actually spoiled for choice in 2019. There are any number of suppliers of ETRM solutions in the cloud on an affordable pay as you go basis that can allow companies to avoid using spreadsheets to a large degree anyhow. It was the panel’s consensus that spreadsheets were not such a good idea on which to manage a trading-related business and we agree!

The session reminded me that there are things like this that we need to continually readdress as analysts even though they seem like old hat because there are as I said at the beginning new entities and people entering the business all of the time.

Keep in touch and sign up to our Newsletter