Share with your friends










Submit

Analytics Magazine

Software Survey: Resources for Spreadsheet Analysts

May/June 2010

CLICK HERE TO GO TO THE DIGITAL VERSION OF THIS ARTICLE

More than 180 spreadsheet analytics products point way toward better, faster, smarter.

Thomas A. GrossmanBy Thomas A. Grossman

Spreadsheets have been an important platform for analytics ever since VisiCalc launched in 1979. Spreadsheets put the power of mathematical modeling and computer programming into the hands of anyone with a PC. Today, spreadsheets are as much a part of the modern office as a desk; it’s virtually impossible to find a knowledge worker without a copy of Excel on her PC.

To understand the true impact of the spreadsheet, think about how the Internet “disintermediated” business by getting rid of the middleman. In the world of analytics, disintermediation happened a decade before the Internet arrived. It used to be that a business analyst had to work through the gatekeepers of the information systems department and wait months or years to get a tool to execute his analysis. The spreadsheet got rid of the analytic middleman. With a spreadsheet, an analyst could do his own analytical programming at a fraction of the cost with cycle times orders of magnitude faster than previously. This was truly revolutionary.

Since 1997 I’ve been tracking the growing ecosystem of “Spreadsheet Analytics.” By this I mean tools and techniques related to using spreadsheets for business analysis. Back in 1997, there were a limited number of tools, mostly add-ins or templates. These were concentrated in operations research algorithms such as optimization and simulation. They gave Joe and Jane Analyst access to analytical power that previously was available only to technical experts.

Today, the ecosystem of Spreadsheet Analytics has expanded dramatically. With that in mind, a team of researchers at the University of San Francisco recently surveyed hundreds of spreadsheet analytic products in order to ascertain the current state of the field. We uncovered more than 180 commercial products from 150-plus vendors plus a handful of notable academic tools that we have compiled as an open, online resource for any interested analyst. For more details, see the accompanying story “Spreadsheet Analytics Web Resource” and visit “Spreadsheet Analytics: Resources for Spreadsheet Analysts” at www.usfca.edu/bps/spreadsheet-analytics. This article highlights resources that help spreadsheet analysts do their jobs better, faster and smarter while managing the complexity and risk caused by a proliferation of spreadsheet information systems.

Overview

Spreadsheet analytics is a big space. It’s easier to make sense of it if we divide it into three parts: 1. doing analytics, 2. before-and-after analytics, and 3. managing analytics.

First, we’ll discuss resources for actually doing analytics — the computations and algorithms that are the stuff of analytical work. This includes “model-driven” analytics such as optimization and simulation that are traditionally taught in the “quant course” at many North American business schools; “data-driven” analytics such as statistics, data mining and business intelligence; and functional-area analytics used in finance, six-sigma and other areas of business.

Second, we’ll look at the unglamorous before-and-after analytics activities related to data and reporting. These activities are the blocking-and-tackling skills that can derail even the best analytic concept if they’re not executed well. They include the painstaking work of moving and cleaning data so that it gets into a spreadsheet in usable form, and the often tedious work of populating the reports and dashboards that bring analytical results to decision-makers. These activities can be repeated weekly or monthly so tools to simplify or automate them can be very valuable.

Third, we’ll explore the important managerial work of making spreadsheet analytics work in organizations — doing it well and doing it right. This is the stuff that gets the attention of senior analysts and the managers accountable for analytical cost, quality and regulatory compliance. This includes spreadsheet development, spreadsheet management, spreadsheet tracking, management of spreadsheet information systems and regulatory compliance. These issues can be critically important to the analytical capability of organizations, but many analysts fail to anticipate them and suffer accordingly. In my view, these areas deserve the attention of anyone who considers himself to be a true analytical professional.

Doing Analytics: Computations and Algorithms

In our survey we determined that the tools for doing analytics fall into three distinct groups: data-driven analytics, model-driven analytics and business function analytics.

Data-Driven Analytics. Data-driven analytics is for when you want to learn something from a dataset. Statisticians rejoice; according to our survey there are plenty of spreadsheet statistics add-ins at a variety of price points. Data hounds will find three commercial tools for spreadsheet data mining. One provides a variety of the usual excavating techniques, one uses something called “self-organizing modeling technologies,” and the third uses neural networks.

Although business intelligence (BI) has many server-based products, our survey found no true spreadsheet business intelligence tools. We did find two products for tracking and visualizing BI data. Most of the action is on the server side, and I am glad to tell you that most BI publishers provide an add-in for getting data to and from Excel. For spreadsheet BI, the big news is old news: native Excel provides decent BI functionality with its powerful sort, filter, pivot table and pivot chart features. They’re not glamorous but they are effective at slicing, dicing and summarizing business data.

Spreadsheet time-series forecasting has almost a dozen add-in products. These generate time-series forecasts using smoothing, regression and neural network approaches. If you’ve been waiting for spreadsheet neural networks you will be pleased to learn our survey discovered five general-purpose products.

Model-Driven Analytics. Model-driven analytics is for when you have a model, such as a forward-looking planning model, and need to extract insight or decision guidance. This is the playground for the algorithms beloved by operations research professionals. We’ll start with the unsung workhorse of spreadsheet sensitivity analysis. Our survey found several tools to “stress test” a model by varying inputs to see the impact on a key output and presenting the results in a Tornado Chart. There are tools and techniques for quickly computing and charting the effect of one or two inputs on an output, and running scenarios through your model.

Spreadsheet goal-seeking is growing up. The clunky Excel goal seek tool is no longer the only option. Our survey found new goal seeking tools that handle multiple-goal seeks at once and can remember and repeat them. Nice!

For the operations research crowd, spreadsheet solvers that deliver spreadsheet optimization (aka “linear programming”) are thriving, with powerful replacements/upgrades to the free Excel Solver. Frontline Systems has a breakthrough product called RiskSolver Platform that handles models that made older solvers go belly-up. Long-time competitor Lindo Systems continues to upgrade and improve its solid What’s Best!. Our survey shows several new spreadsheet genetic algorithms and other spreadsheet metaheuristics at a variety of price points.

For spreadsheet Monte Carlo simulation, the old duopoly of Oracle’s Crystal Ball and Palisade’s @Risk are being challenged at the high and low end. Notable entrants are Vose Softare’s ModelRisk and Frontline Systems’ RiskSolver with innovative “simulation optimization” capability. Our survey indicates that the spreadsheet decision tree space is growing with new products to compete with the business school standard TreePlan. Palisade and Frontline have decision tree products integrated with their other tools.

Business Function Analytics. Business function analytics is about tools designed for specific functional areas of business. The survey found a half-dozen spreadsheet finance tools for pricing various types of derivatives, to support trading decisions (with integration to brokers) and even a tool for automatic data feeds. In spreadsheet marketing there is tool for spreadsheet marketing engineering, templates for spreadsheet marketing plans and a spreadsheet advertising keyword pricing tool.

If you are fan of doing things right the first time, you will be interested in spreadsheet Six Sigma and quality tools. Our survey discovered products for Six-Sigma greenbelts to focus on the process not the math, products for statistical quality control and even tools intended for use on the factory floor.

Do you hate waiting in line? To connect resources to customer waiting lines, a wonderful spreadsheet queueing add-in offers custom functions to program “queueing theory” formulas right in your spreadsheet, plus a couple of other academic tools. For more traditional spreadsheet operations management the survey discovered a set of spreadsheet project management templates as well as an add-in for spreadsheet data envelopment analysis, which is a useful technique to evaluate the performance of departments or branches based on their inputs and outputs.

Before and After Analytics: Data Management and Reporting

Before you can do analytics, you need data. It may come as a surprise to some readers that getting access to usable data is often a big task in itself. Unless your outfit has a data warehouse that actually works like the salesman promised, you’re probably going to have to clean your data to get rid of junk and odd characters and numbers-as-text, and you may have to consolidate data from different sources or multiple spreadsheets into one place. And don’t forget to remove those pesky duplicate rows — everybody’s favorite way to spend Saturday morning! If you have regular business processes, you might have to go through this monthly or even weekly.

If you don’t have an intern to do these exciting tasks, you can get some help from software. A half-dozen spreadsheet data management and cleaning tools made the cut for our survey, including a couple that already come with Excel. A half-dozen spreadsheet consolidation tools, including Excel’s often-overlooked consolidate tool, are also available.

After you’ve sorted out the data and done the analytics, you’ll need to share what you’ve achieved. If you are successful enough that your analytics are integrated into routine decision-making or pulse-taking processes, you’re going to want to regularly transfer analytic outputs from spreadsheets into reports. If your organization is up-to-date on its buzzwords, you’ll want to move spreadsheet analytic outputs from spreadsheets into dashboards that graphically summarize results. Doing this by hand gets old after a while, so utilize tools that automate posting outputs to dashboards and Web sites and that will open workbooks, print reports and close files at the push of a button. Your days of cubicle drudgery can come to an end!

Managing Analytics: Doing it Well and Doing it Right

Managers should be interested in the productivity of their spreadsheet analysts; this is the important task of “spreadsheet development.” Managers, especially senior managers, need to make sure that for certain spreadsheets adequate controls are in place to ensure compliance with government regulations such as Sarbanes-Oxley, as well as sound risk management for financial decisions.

Spreadsheet Development. Spreadsheet development is about supporting spreadsheet programmers to be productive and to work well in organizations. This is an important issue for people who spend a lot of time in spreadsheets, and it should be an important issue for the people who manage them.

The most exciting news has to do with deploying spreadsheet applications. By “spreadsheet application” I mean software written by one person for use by others. When done naively, this inevitably leads to headaches and confusion. A terrific solution is to upload your spreadsheet app to a server and provide a simple interface. Users anywhere in the world can enter their data and then, after the spreadsheet runs invisibly, see the outputs from the spreadsheet computations. This is the way of the future, and if you’re e-mailing lots of spreadsheets to lots of people, you need to learn about this. Our survey found that several vendors plus Microsoft are providing variations on this service. In addition, some vendors offer a “spreadsheet compiler” that locks away the code inside an EXE or DLL so the users can use the spreadsheet but can’t see or change the formulas.

The other big news has to do with spreadsheet development practices. Business schools do almost nothing in this realm, and in too many companies the attitude is, “Here’s your computer and your spreadsheet, you figure it out.” It doesn’t need to be this way anymore. Spreadsheet programming is professionalizing and some companies are enjoying dramatic improvements in productivity and maintainability. If you build large financial models, our survey found detailed formal development methodologies and you might want to invest in learning one of them or at least take a look at Jonathan Swan’s “Practical Financial Modelling” (Swan, 2008). For the general business modeler, there are some terrific books about how to approach the modeling/thinking as well as the spreadsheet engineering, including “Spreadsheet Modeling Best Practices” (Read and Batson, 1999) and “Modeling for Insight” (Powell and Batt, 2008).

According to the survey there are several tools, some very sophisticated, for spreadsheet checking and auditing. These tools identify anomalies in the formulas and flag things that you should be concerned about. Some of the more powerful tools provide model maps, summary data and stuff you never knew you needed until you saw it. One of these should be in the bat utility belt of every spreadsheet superhero. The survey turned up handy “diff” tools for spreadsheet comparison that tell you what’s changed in two versions of the same spreadsheet; like yellow sticky notes, these are indispensable when you need one. The survey also found a grab bag of spreadsheet productivity tools that claim to help you work smarter and faster.

Control & Compliance. For someone keen on analytics, control and compliance is not obviously exciting. But there are important things going on in this area.

Courtesy of the Sarbanes-Oxley legislation, the U.S. government requires that anything in the financial reporting loop has to be locked down. This means no spreadsheets or only spreadsheets with tight controls. Risk managers want to make sure that spreadsheets used for high-value or high-risk decisions (e.g., pricing of financial derivatives that lead to million- or billion-dollar bets) are bulletproof.

This requires a certain amount of managerial commitment and process skills, and no tool is going to help with that. It also requires getting an inventory of the spreadsheets in your organization and determining which need managerial attention or control and which don’t. That can be a challenge, as some servers have as many as 15 million (that’s not a typo: 15,000,000) spreadsheets. This requires a tool that looks at everything on a server or at every computer on a network and gets a snapshot of all the spreadsheets, as well as the spreadsheets that sneaky users may have tried to disguise as something else. Our survey found powerful tools for spreadsheet discovery and management that help with this. They’re not cheap, but they sure are impressive. They can also provide things like access control, change logs, version control and other capabilities that are routinely provided for professional software developers. But then again, if you’re using spreadsheets to run your company, shouldn’t you treat and support (and manage and control) your spreadsheet analysts as the professional software developers that they are?

For medical or pharmaceutical research, there’s another angle. The FDA has strict requirements on data integrity. Spreadsheets can still be used in medical research, but they need to be locked up inside a virtual vault. Products that can do this for you are available.

Conclusion

The quality of model-driven spreadsheet analytic tools is now very high. The new spreadsheet solvers have extraordinary capabilities to handle very large models, including spreadsheet models programmed in ways that confuse some traditional algorithms. Furthermore, if the analyst learns to design his spreadsheet to keep the solver happy, then very large and complex models can be successfully optimized. (There are no satisfactory non-techie guidelines for this yet, but you can get some insight from Frontline Systems 2009, Conway & Rags-dale 1997 or sections 10.3-10.4 of Powell & Baker 2009.)

I expect to see growing awareness and sophistication in the management of spreadsheet information systems, especially after companies finish getting their data house in order via business intelligence and data warehousing systems. It is an open question when CIOs, CFOs and other senior managers recognize the amount of highly paid time that is invested in spreadsheet model assets. If and when they do, expect a surge of interest in development processes and productivity.

We now have good tools for discovering all the spreadsheets on a server. It should not be too long before there are good tools for mapping spreadsheet information systems as a set of interlinked spreadsheets. By connecting these maps to source code management tools (which in principle could be adapted to spreadsheets) and using Web-based spreadsheet deployment services, there is clear potential for professional-quality management of spreadsheet systems without having to port them to other languages.

The big open question in the spreadsheet analytics space is, of course, Google. Where is Google going with Google Spreadsheet? Google Spreadsheet is currently an analytic wimp. Google has not released a product roadmap, so we don’t know its intentions. They are steadily rolling out new spreadsheet features. A small eco-system of add-in providers is emerging. They have the potential to provide significant analytic functionality to Google Spreadsheet. It will be fascinating to see what Google does and how Microsoft responds. In addition, the open source OpenOffice.org products might yet take off.

For more details on the categories, products, information sources and “beyond the spreadsheet,” visit www.usfca.edu/bps/spreadsheet-analytics.

Thomas A. Grossman (tagrossman@usfca.edu) is an associate professor of Business Analytics at the University of San Francisco. He teaches courses in Spreadsheet Analytics, Business Analytics and Spreadsheet Engineering & Management. He is developing a new MBA major in Business Analytics. He does research on how spreadsheets are used in organizations and how they can be used better. Web site: http://web.usfca.edu/bps/faculty/Thomas_Grossman/

References

1. Conway, D. G. and Ragsdale, C. T., 1997, “Modeling optimization problems in the unstructured world of spreadsheets,” Omega, Vol. 25. No. 3, pp. 313-322.
2. Frontline Systems, 2009, “Optimization Problem Types Overview,” www.solver.com/probtype.htm, accessed April 1, 2010.
3. Powell, S. and Baker, K., 2009, “Management Science: The Art of Modeling with Spreadsheets,” 2nd edition, John Wiley & Sons.
4. Powell, S. and R. Batt, 2008, “Modeling for Insight: A Master Class for Business Analysts,” John Wiley & Sons.
5. Read, N. and J. Batson, 1999, “Spreadsheet Modelling Best Practice,” www.eusprig.org/smbp.pdf, accessed March 29, 2010.
6. Swan, J., 2008, “Practical Financial Modelling, Second Edition: A Guide to Current Practice,” CIMA Publishing

Spreadsheet Analytics Web Site:
www.usfca.edu/bps/spreadsheet-analytics

Just what are the tools and resources available to the spreadsheet analyst who wants to be more productive? It turns out that it is a tricky question. There is an overwhelming amount of material on the Web related to spreadsheets, including addins, templates, stand-alone software, server-based software, checkers, compilers, Web-based spreadsheet deployment tools, methodologies, books, courses, conferences, Web sites and bagel slicers (well, maybe not bagel slicers). These resources address a dizzying array of problems and opportunities. There is wide range in quality, from commercial software with full tech support and detailed user manuals to academic tools that provide basic functionality but little support … and everything in between.

We launched a Web site called “Spreadsheet Analytics: Resources for Spreadsheet Analysts” to help make sense of it all. Because of the complexity of the spreadsheet analytic space, we organized the available resources into eight top-level categories and 30 detailed categories. This makes it much easier for an analyst to find what she is looking for. It can be an eye-opening experience to scan all 30 categories and see the amazing diversity of spreadsheet analytic issues. Here are the eight top-level categories:

  • Model-Driven Analytics
  • Data-Driven Analytics/Business Intelligence
  • Business Function Analytics
  • Data Management
  • Reporting
  • Development
  • Control & Compliance
  • Information Sources

The Spreadsheet Analytics Web site focuses on commercial-quality products that are backed up by a company that is likely to be around for a while, with someone to talk to if you need help. We made judgments to exclude products that didn’t look fully developed or weren’t backed up by a company. We filtered out the freeware and academic products, with exceptions for areas where there are no commercial products or where the product has something special going on. We included an open-source product if we felt there was a community standing behind it.

The Spreadsheet Analytics Web site is non-commercial with no advertising and no selling commissions. We didn’t test these products and don’t endorse any of them. Remember, your mileage will vary. The Spreadsheet Analytics Web site is provided as a public service by the Business Analytics Program of the University of San Francisco’s School of Business and Professional Studies. Go take a look at www.usfca.edu/bps/spreadsheet-analytics.

– Thomas A. Grossman

CLICK HERE TO GO TO THE DIGITAL VERSION OF THIS ARTICLE

Analytics Blog

Electoral College put to the math test


With the campaign two months behind us and the inauguration of Donald Trump two days away, isn’t it time to put the 2016 U.S. presidential election to bed and focus on issues that have yet to be decided? Of course not.




Headlines

Survey: Despite the hype, AI adoption still in early stages

The hype surrounding artificial intelligence (AI) is intense, but for most European businesses surveyed in a recent study by SAS, adoption of AI is still in the early or even planning stages. The good news is, the vast majority of organizations have begun to talk about AI, and a few have even begun to implement suitable projects. There is much optimism about the potential of AI, although fewer were confident that their organization was ready to exploit that potential. Read more →

Data professionals spend almost as much time prepping data as analyzing it

Nearly 40 percent of data professionals spend more than 20 hours per week accessing, blending and preparing data rather than performing actual analysis, according to a survey conducted by TMMData and the Digital Analytics Association. More than 800 DAA community members participated in the survey held earlier this year. The survey revealed that data access, quality and integration present persistent, interrelated roadblocks to efficient and confident analysis across industries. Read more →

UPCOMING ANALYTICS EVENTS

INFORMS-SPONSORED EVENTS

2017 Winter Simulation Conference (WSC 2017)
Dec. 3-6, 2017, Las Vegas

CAP® EXAM SCHEDULE

CAP® Exam computer-based testing sites are available in 700 locations worldwide. Take the exam close to home and on your schedule:


 
For more information, go to 
https://www.certifiedanalytics.org.