Author: cquantWeb

November 20, 2019

Excel Spreadsheet Analytics vs. SaaS Analytics

When does is make sense to upgrade your analytics?

Most corporations use Excel spreadsheets for a variety of business requirements, and for good reason. Excel is inexpensive, easy to learn, easy to customize and has useful computational features. However, as companies and their analytic needs grow, the strengths of Excel can quickly become its faults. Excel spreadsheet flexibility and customizability can enable unseen mistakes and financial errors, especially when being used by multiple individuals.

Software-as-a-service (SaaS) analytics, as compared to Excel analytics, are not only more robust, but are also highly secure and scalable. SaaS typically means a software solution that is cloud-based and accessible via a web browser.  SaaS analytics are generally superior to local (downloaded) software as they eliminate any associated hardware, costly implementation headaches and IT costs. Below, we summarize specifics as to why SaaS analytics, in some cases, should replace Excel spreadsheets.

Spreadsheets are vulnerable to human error.

The flexibility and customizability of Excel spreadsheets makes them easy to use.  However, these characteristics also make spreadsheets more likely to contain errors as complexity increases. The use of inter-cell and inter-tab formulas makes error tracking time-consuming and difficult within spreadsheets. With every cell subjected to manual manipulation, one miscalculation can compromise further calculations and results. This article, “The 7 Biggest Excel Mistakes of All Time”, describes common human errors in corporate spreadsheets which resulted in millions of dollars in losses for those organizations.   

SaaS analytics are less susceptible to these errors since typical end-users of SaaS analytics do not adjust the formulas or the underlying analytical code.  There are three main steps in order to run a SaaS analytics model:

  1. Company data is uploaded
  2. Model is run
  3. Results are downloaded

The analytic models are usually hosted in a cloud-based platform, significantly decreasing the risk of introducing unforeseen human errors. Model formulas can be adjusted, but this is reserved for specific personnel trained for that purpose.

SaaS analytics facilitates collaboration and control.

The fastest and most common way to share spreadsheets internally is through email.  Not only does this compromise data security, but it also can also make keeping track of multiple file versions and edits difficult. Here are a few common and potentially damaging mistakes:

  • Sally sends John a spreadsheet.  John makes 10 edits but only tells Sally about 7 changes. How does Sally find and validate the other changes?
  • Sally is updating the corporate spreadsheet and has asked other team members to review different tabs.  As the other team members make changes, there are several versions of the spreadsheet in circulation.  Bringing all changes together into a single updated spreadsheet is time-consuming and error prone. 
  • Jenny accidentally mis-titled a spreadsheet. Ben needs to share this spreadsheet with the accountant but sends the wrong version resulting in several days of confusion.
  • John has been managing the corporate spreadsheet for years and suddenly retires.   A new analyst may take weeks to fully understand John’s complex spreadsheet.  Often the new analyst finds mistakes that John had missed for years.

SaaS analytics can solve these problems.  These analytic tools are often accessed within a standard, easy to use platform that is accessed in a web-browser.  Multiple users or groups can use these tools simultaneously without the need to share models via email.  An intuitive user interface means that new or junior staff can successfully manage complex analytic processes.  Finally, if model changes are required, this can be managed in a systematic way.  Spreadsheet fans will point out that incorporating new changes may require days for SaaS analytics, rather than minutes for a spreadsheet.  That is true.  But if your corporate model is mission-critical, you probably want to manage these changes more systematically anyway. 

SaaS analytics are more secure.

Excel does allow users to password protect many elements, hide formulas and lock cells.  When using spreadsheets this is a good first line of defense but does not solve many underlying issues.  Complex spreadsheets are still prone to human error and locking or hiding those errors compounds the problem.  Also, there are many ‘un-protect’ programs available to unlock a spreadsheet and gain access to its data.  If a hacker does get a hold of a confidential spreadsheet, Excel can’t stop them from copying information and pasting it to another workbook.

SaaS analytics are able to leverage many cloud security features to ensure that sensitive data remains secure.  At cQuant.io, we use numerous security features in our SaaS platform that ensure our customer’s data is protected. These features include:

  • Secure communication (HTTPS)
  • Encryption of user and company data
  • 2-factor authentication
  • User session time outs/lock outs
  • Password complexity & change management

Like many SaaS providers, cQuant.io deals with sensitive customer data every day and works hard to ensure that we are employing the latest security tools and protocols.

SaaS analytics provides a more robust workflow.

A large downside to Excel is that if calculations are complex enough, Excel has difficulty managing the workflow.  Some corporate spreadsheets have become so large that they take tens of minutes to open and hours to calculate when new data is introduced.  This is due to Microsoft Excel’s software being relatively slow, even when the calculations are simplistic.    

For most spreadsheet models, the computations are single-threaded and deterministic.  Most companies would benefit from seeing scenarios and simulations of future outcomes.  This is especially true in energy and commodities organizations where market prices and weather are key drivers of risk and are quite volatile.   The company CFO and/or Risk Manager may like to see a “gross-margin-at-risk” report which provides an expected gross margin with useful distributions around the mean.  However, this is very difficult to enable and manage in Excel, so the risk analysis is often not performed.   

In the analytics world we call this “model limited choice”.  Your spreadsheet model cannot manage a robust analytical process, so that process is simply not performed. In this case, the company has less information on which to make decisions because of its choice of model. 

SaaS analytics are typically built using robust statistical analysis software. cQuant.io’s models are built in R, Python, C++, and other high-performance programming languages.  These languages are extremely fast, flexible and powerful.   The models are managed by our cloud platform, providing many user tools and data visualizations.  Multiple users can run the same models at the same time and share results.  cQuant’s analytics also preserve your workflow through time. Because cQuant’s platform saves every model run, the user(s) can look back to historical runs, model settings, and results.

Our Conclusion

Overall, there are many upsides to using Excel spreadsheets and not all corporate models should be converted to SaaS analytics. But organizations should consider converting spreadsheets to SaaS analytics when those models become more complex, mission-critical, multi-user and especially when mistakes can cost the company millions.

Are you ready to improve your energy analytics? Request your free demo today.

SCHEDULE YOUR DEMO TODAY

January 7, 2018

“Bomb Cyclone” Highlights Need for Energy Risk Management

For many on the east coast, the end of 2017 brought more than Yuletide cheer and presents. A weather event called a “bomb cyclone” brought over a foot of snow followed by weeks of cold weather that ticked new record low temperatures in many of the northeastern United States. Many states experienced temperatures 20-30 degrees below normal and strong winds plunged apparent temperatures even lower.

As icy conditions lingered, power outages struck tens of thousands of people and heating demand for natural gas strained the distribution system to its limit. The result was record high natural gas demand, record high natural gas prices at several eastern trading hubs, and correspondingly high electricity prices in areas reliant on natural gas as a primary fuel for generation.

The severe weather event harkens back to early 2014, when a downward shift in the “polar vortex” brought similar weather conditions to the U.S. and similar turmoil to eastern energy markets. Then too, the natural gas system was stressed due to prolonged above-normal heating demand, gas prices spiked, and energy prices followed suit. The 2014 event left many unsuspecting energy companies financially wounded or bankrupt.  This year will be no different.

Companies with unprotected short positions in natural gas or power can be bankrupted in a matter of days when these severe weather events occur. To put the extreme prices in perspective, late December 2017 forward contracts showed the expected price of January gas at the Transco Z6 hub in NY to be around $6. However, on January 4, 2018 prices traded as high as $175, an almost 3000% increase above the prior expectation! Imagine if, just for one month, your mortgage payment suddenly increased by 3000%, turning a $2000 monthly payment into $60,000.

For energy companies exposed to volatile natural gas and electricity spot markets, the way to protect against “bomb cyclones” and “polar vortexes” is by savvy financial hedging. This means taking financial positions in the market to offset some or all of their expected market exposure, locking in rates or providing optionality to protect them when prices take a turn for the worse. In turn, understanding “expected future market exposure” can be complex and requires rigorous analysis accounting for a company’s unique portfolio of contractual commitments, physical assets, and in-place financial positions.

At cQuant.io, we’ve built an energy analytics platform with easy-to-use models that can help companies protect themselves from adverse market events. Our web-based interface is always available and provides access to powerful analytic models that let you understand your portfolio’s market exposure and take steps to mitigate your risk. With both a “polar vortex” and a “bomb cyclone” in just three years, don’t let the next buzzword-worthy weather event destroy your company’s future. Contact us today to learn more, or visit us on the web at www.cquant.io

Request Your Demo Today

December 20, 2017

Mark-to-Market (MtM)

Compute mark-to-market value for a portfolio of financial contracts and report option Greeks.

December 20, 2017

Storage Dispatch Optimization

Optimize storage technology dispatch to real time power and ancillary services prices while maintaining operational constraints.

December 20, 2017

Load Forecasting

Forecast hourly or sub-hourly load over a user-defined time horizon by parameterizing a stochastic simulation model against historical data.

December 15, 2017

ReAssure Renewable Energy Valuation

Compute fair market value, forecast future energy production, and understand risk for renewable energy contracts and production facilities.

December 15, 2017

Demand Response Optimization

Optimizes dispatch of demand response programs based on fuel and power price signals.

December 15, 2017

Cash Flow at Risk (CFaR)

Generate a cash flow at risk (CFaR) report for a portfolio of generation assets and financial positions or for the individual assets themselves.

December 15, 2017

Plant Dispatch Optimization

Optimize hourly plant dispatch against fuel and power price signals to maximize profit.

December 15, 2017

Fair Weather Risk Management

I learned a valuable lesson about risk management the summer after I moved to Boulder, Colorado. Coloradans love to remind others (and each other) they get over 300+days of sunshine each year, and my first year in the state seemed to bear out this anecdotal wisdom. However, what all the jolly weather-snobs fail to mention is that Boulder is also prone to monsoons during the summer months that can produce some of the fiercest rain you’ve ever seen.

The warm summer days melt snow and heat pools of water across the Rocky Mountains, causing moisture to rise into the atmosphere and be carried eastward by turbulent mountain winds. When the moist air abruptly stabilizes after hitting the plains to the east, it can no longer support all the moisture it picked up along the way and it sends it crashing back to earth in a violent downpour. Long story short: the clear blue sky for my bike ride to the office each morning was a very poor indicator of what I would face on the ride home.

So, “Why the weather lesson?” you ask. Because I learned that it’s very hard to remember your rain jacket when the weather outside is beautiful. Humans have a notoriously bad memory for unfavorable events and a curiously good memory for favorable ones. Perhaps this evolved as a coping mechanism, perhaps it’s plain denial, or perhaps we just want to believe that something good can last forever. In any case, it’s terrible risk management.

Today’s natural gas market may just be the energy equivalent of a sunny summer morning in Colorado. Natural gas supporters have been fueled by almost a decade of steady production increases and sub-five-dollar prices. New and efficient combined cycle generation facilities are quickly replacing a fleet of aging coal units and a new federal administration is unabashedly supportive of fossil fuel generation. The U.S. is going long on natural gas in a big way.

While the present forecast may be fair for natural gas, the consolidation of so much thermal generation into this single fuel type is something that should make risk managers cringe. The reduced fuel diversification is exposing power markets to more and more natural gas price risk. This means that small gas price moves will have a larger effect on the bottom line for generation, and larger moves can be catastrophic; look no further than the infamous Polar Vortex of 2012 for a prime example. What’s more, natural gas has historically been an immensely volatile commodity. There are 4 examples in the last 20 years where the price of natural gas has doubled or worse within a 12-month span (source: EIA).

How confident are we that such a price event will not precipitate in the near future? What do we have to protect us if it does?

Sometimes the sunniest days are the best to carry an umbrella. At cQuant, we help energy companies and utilities prepare for the unexpected with advanced analytics and risk management software. Contact us to learn how.

Scroll to top