Spreadsheets for Quality Risk Management?

Still Popular, after all these years…

Although data scientists and statisticians might groan at data automation and analysis using spreadsheet platforms, such as Excel^TM, Google Sheets^TM or others, the long-standing popularity of spreadsheet tools remains indisputable. Beginning with simple monochrome tables using Visicalc in 1979, the spreadsheet has evolved to a user-friendly interface for accounting tables, diverse analyses and dashboards for summaries of multiple lines sources of data. This fact and widespread availability in several formats keep the software popular for personal, business team and even enterprise IT solutions. Some reasons for the popularity of spreadsheets include their

• accessibility to non-data scientists and non-programmers;

• availability on most platforms including Windows, iOS, Android and cloud servers;

• portability–the same spreadsheet can be run independently on a laptop or launched on the internet;

• cell formulas, many of which are intuitive for basic math needs; and

• support from a large online community of spreadsheet users, consultants and software vendors.

A full-featured application, such as Excel, can also bridge many organizational needs from simple maintenance of a contacts list to an informative dashboard backed by automated data analysis (e.g., TheSmallman.com). It is apparent that some industries see a positive trend in using Excel dashboards to monitor key process indicators (KPIs) for the organization even if the dashboard is only a GUI for data from more sophisticated databases and statistical software.

Given the wide utility of (programmable) spreadsheets, motivated spreadsheet users can expect to find both room for growth and ample support developing deeper expertise using such tools. The remainder of this post will some limitations and uses of Excel for quality risk management (QRM) and QRM decision-making.

Excel is not going anywhere, and businesses will continue to use Excel as a primary tool for diverse functions and applications ranging from IT projects to company picnics. Investopedia June 25, 2019

Limitations to spreadsheet software

Given all the positives, why are data scientists, engineers and other “quants” so negative about spreadsheet tools? Many of the blog posts compare Excel or other spreadsheets with statistical software such as R, SAS, Tableau or MiniTab. But these comparisons are misleading because they are “apples-to-oranges” comparisons that do not often reflect the different types and needs of users. Although spreadsheets have impressive lists of built-in and add-in statistical functions, the spreadsheet platforms are far from being pre-programmed for descriptive, inferential and other statistical modeling that is found in advanced statistical packages. Second, rigorous quantitative solutions for business analysis and forecasting can be programmed in Visual Basic for Applications, C# or C⁺⁺, Javascript, or Typescript, all launched “behind” Excel. But compilation and deployment of programs in these languages tools can be daunting for the typical spreadsheet user and macro programmer. Third, program modules are difficult to manage in an enterprise IT organization and, (albeit in a shrinking number of cases), unsafe to share across the internet. Finally, spreadsheet programs typically run in a size-limited container making them unwieldy or even useless for a “big data” project. For example, the worksheet limit is 1,048,576 rows, although there are work-arounds using Excel’s data model tools (e.g, see: How can You Analyze 1 mn Rows of Data - Excel Interview Question - 02 and below).

Spreadsheets in Quality Risk Management

Although QRM is a relatively new concept and practice, already there are numerous Excel and other spreadsheet apps, templates and add-ins that are applicable to QRM functions. For example, a Google search for “quality risk management” AND “Excel template” yields about 19,400 results!¹ Given that QRM is a multidisciplinary blend from quality assurance, risk management and decision analysis, it is likely that most QRM spreadsheet apps come from modifications of the spreadsheets apps and templates in those disciplines. In essence, QRM spreadsheet apps and templates are a small subset of a vast set for more general quality management purposes.²

Comparing Spreadsheets to R and Python

Perhaps the two most popular tools for data science are the open-source languages R and Python. R is a statistical language program that is both free and easy to learn. Python is a free programming language that has a significant set of packages for data analysis and scientific computing. Both programs can “run anywhere” and they can be used with user-friendly integrated development environments (IDEs) such as RStudio for R and Idle for python. For example, this blog and website are developed in RStudio using the R package, blogdown.

In spite of apparent unpopularity for statistical programming and data chores, desktop spreadsheet software remains popular due to its broad accessibility to users. Not only are spreadsheet programs available on almost every desktop, laptop and tablet produced, but the documentation and training opportunities for beginning to advance users are ubiquitous. re

Others’ opinions:

https://martinctc.github.io/blog/my-favourite-alternative-to-excel-dashboards/#fn1

vs Python Excel vs python

Use python not excel :https://www.dataquest.io/blog/excel-vs-python/

R versus Excel: What’s the Difference

Understanding R programming over Excel for Data Analysis

Alternatives:

DT DataTables in the DT package

flexDashboard R package flexDashboard

excel not even mentioned in survey of BI tools: https://bi-survey.com/business-intelligence-software-comparison