For the energy transition to succeed, we have to say goodbye to spreadsheets

One of the most notorious chapters in the history of energy was the epic Enron scandal.

Enron was arguably an energy innovator, leading the deregulation of energy markets in the US in the 1990s, but their eventual collapse rocked energy markets to the point of causing supply issues. This led to the roll-back of energy market deregulation and competitive retailing in states like California, as well as the introduction of new corporate regulations like Sarbanes-Oxley.

Yes there were ethical and corporate governance issues at Enron, but these issues and poor decision making generally was exacerbated by many thousands of dodgy spreadsheets. By some estimates, 24 percent of spreadsheet formulas used by Enron contained errors. This was across almost a million Excel formulas in the files released following legal proceedings.

The ‘Excel-problem’ is not unique to Enron. Errors in Excel spreadsheets have caused everything from corporate collapses to bridge collapses.

So when the challenge for every business, government and community is transitioning to a decarbonised, decentralised energy future, unfortunately the humble spreadsheet is not going to deliver a speedy and successful transition.

Here’s four reasons why you should ditch the spreadsheet and use smarter tech instead.

1. Repeatability is crucial for data integrity

We’ve all felt the fear that sets in when a spreadsheet for an important project is due and we open it to find completely a different result. Am I looking at the latest version? Who changed the data and why? Am I using the right assumptions?

When working in teams of multiple people on multiple complex projects, it’s critical to understand when and why things change, knowing who changed what, and being able to roll back changes if necessary.

Version control is the only way to professionally manage complex models, keeping track of changes and being able to safely roll-back to previous versions, which is why our software maintains a full version history of project models and input assumptions.

By centralising and managing input assumptions like project costing, market pricing, and site loads, we’re able to provide a permission-controlled and version-managed Library of reusable modelling inputs providing confidence that you are using consistent and up-to-date assumptions across models.

With something as complex as the global energy transition, the ability to re-run and reproduce the process that generated the data is crucial. Without repeatability, you can’t use the data to make any claims about the results of your project.

2. Consistency across models for complex calculations

When it comes to project planning, the only thing worse than “new project, new spreadsheet’’, is “old messy spreadsheet, half setup from the last project.” Where do you think all those errors in Enron’s spreadsheets came from?

Whether it is modelling the state of charge of a battery, the output from a solar system, or the utilisation of an EV charger, accurate energy modelling requires hundreds of different complex calculations.

(Image Source: https://edbodmer.com/modelling-battery-cost-and-operation/)

Then when you go to convert energy models to commercial assessments, you add hundreds more complex calculations, like line loss adjustments to nodal wholesale energy prices, demand charge calculations based on power factor and reactive power, and interval-level emissions factors. Not to mention considering the issue of uncertainty.

Gridcognition helps by codifying all of these complex calculations in rigorously tested software and ensures they are applied consistently across every model.

3. The energy future is dynamic and intelligently controlled

Distributed energy used to just mean ‘roof-top solar’. And modelling the output from solar systems was mostly just a matter of irradiance data, trigonometry, and tables of energy conversion factors. If the sun was shining, the system was passively yielding energy.

But the dance card of DER systems is expanding, and includes battery storage systems, smart EV chargers and fleets, backup generators, and flexible loads. And all of these require intelligent control; they don’t just passively yield energy like solar systems.

In fact even the humble roof-top solar system needs control these days.

Modelling intelligent control of energy resources isn’t possible in Excel without complex 3rd-party add-ins and moves beyond the capability of data analysts into the realm of data scientists.

Gridcognition goes further than what is possible in Excel, and enables true co-optimisation of multiple energy resources, integrating both behind-the-meter and front-of-meter value, and does this in a way that reflects the actual behaviour of real control systems available in the market.

This is all delivered ‘out of the box’ in a way that is accessible to commercial analysts and renewable energy engineers who don’t have advanced maths degrees.

4. Assessing more options, in more detail for scalability and confidence

Energy projects can be combinatorially complex.

Multiply site selection options by asset select options by asset sizing options by asset control options by asset ownership options and revenue and cost sharing options and you can quickly get to tens-of-thousands of project options.

Add in the need to assess each of these options in every trading interval over a multi-year project duration and you can end up needing to compute across lots and lots and lots of data.

We had a look at one of our simple demo projects in Gridcognition that has only nine sites and three scenarios over 10 years. This project generated over 1.2 billion rows of data! This is okay in Gridcognition because simulation and optimisation jobs run on dedicated and scalable cloud compute.

But it’s not okay in Excel, which can only hold a million rows of data in a worksheet.

If you’ve ever tried to work with big spreadsheets with complex calculations, once you go over even just a few hundreds-of-thousands of rows of data, Excel can end up really grinding the gears of your computer. It’s not uncommon for Excel analysts to have to turn off calculations so they can update big blocks of data without having to wait forever for their spreadsheet to recalculate.

In practice Excel models have to consider less options and rely more on top-down assumptions and heuristics, which means less confidence in your project outcomes. With the advent of cloud service and infrastructure, energy projects can be modelled using Gridcognition at speed and scale, all while your laptop is off.

We want to support you to scale and succeed! Click here to request a Gridcog demo and our team will reach out.