En utilisant notre site, vous consentez à ce que des cookies soient utilisés à des fins d’analyse, de pertinence, et de publicité. Modifiez vos préférences.

Spreadsheets, Risks, and Technological Revolution

Publié le 05/12/2016
Spreadsheets-Risks

Article written by Tie Cheng, CEO of Matrix Lead and responsible for the training of « Risks, modeling and technology of spreadsheets » in École polytechnique Executive Education.

 

 

1. Spreadsheets in the Business World

A spreadsheet is an interactive computer application used for the storage, organization, modeling, and analysis of data in tabular form. Since its conception in 1969, it has deeply transformed the way we do business. Spreadsheets are widely used today, Microsoft Excel being the most popular software, with more than 750 million users worldwide.

The nature of the spreadsheet makes it such an effective prototyping language that even computer scientists have suggested few alternatives to replacing it. Tables, as simple as they may seem, are a natural way for human beings to organize data; formulas allow users to easily chain calculations; and VBA macros automate manipulations and keep spreadsheets to scale. As a result, for many professionals, especially those who are not developers, the programming language of choice has become the spreadsheet.

« Our goal is to make our traders spend their life with Excel », confirmed by a Head of desk developers for a trading division in a bank; Excel continues to be the final “front-end” of everything, though there may be Bloomberg, databases and pricing libraries behind. In management consulting, according to a director of a prestigious firm, a quarter of her projects require operational Excel tools or models as deliverables to the client. Industrial supply chain operations also heavily rely upon spreadsheets as a tool, using massive spreadsheets to consolidate data from various resources including SAP to plan sales and operations. The simple truth is that spreadsheets remain at the very core of modern business and remain the fundamental carriers of critical information within companies.

Nowadays, it is not uncommon to find companies and teams running many “grown up” spreadsheets. “Grown up” spreadsheets are large, complex workbooks shared by multiple people, often connected to other software, used in different ways, and maintained over several years. Teams spend large amounts of time and resources in using and coding these complex sheets. These spreadsheets have effectively transformed from simple prototypes, into real applications.

 

2. Spreadsheets Risks

In recognizing the value of spreadsheets, it is also important to acknowledge that spreadsheets in companies are often a house of cards, ready to collapse at the first incorrect entry, formula, or assumption. The user-friendly nature, ubiquity and complexity of spreadsheets inevitably result in significant risks of errors: “fat finger” syndrome, outdated assumptions, improper copy-andpasting, fraud, misuse of functions, incorrect arguments of formulas – these blunders, among many others, can lead to severely inaccurate spreadsheet data. Spreadsheets are indeed subject to numerous defects and often produce incorrect results that do not match user intentions.

Take, for example, the 2012 report into 2012 JPMorgan Chase trading loss, a lack of control over critical spreadsheets and errors discovered in them (e.g., misuse of spreadsheet functions) were cited as a factor in the trading losses of more than 6 billion dollars. Spreadsheet errors in academia can lead to large repercussions too, for example, the erroneous spreadsheets that supported Harvard’s economists’ work establishing a correlation between indebtedness and economic growth, had a huge influence on Washington policymakers. The corrected spreadsheets later invalidated the economists’ conclusion.

In general, spreadsheet errors may cause the release of false information, the loss of money, or the taking of wrong decisions. The spreadsheet risks have been gradually recognized by managers, executives, auditors and regulators. However, they often have no solution other than replacing spreadsheets with other software – which does not solve all cases; it is time-consuming and costly, and more importantly, their team is likely to continue privately relying on spreadsheets for the sake of flexibility. An effective solution would be facing the problems directly, implementing control policies, learning best practices, embracing new automated tools and spreadsheet productivity and risk mitigating technologies.

 

3. Evolution of Spreadsheet Technologies and Tools

Microsoft Products. In the 30 years since Microsoft Excel was introduced, it has grown to be the undisputed market leader and with no major alternatives. Microsoft has been always enhancing Excel and defending this leading position. And again, Satya Nadella, CEO ofMicrosoft, confirmed that « The most strategic developer surface area for us is Office 365 »; this means that the Office 365 platform, which includes Excel, is one important area for Microsoft as part of their vision for the future.

Survey results show that today’s Excel (e.g., Excel 2013, 2016) has too many advanced features to all be known by spreadsheet users. Indeed, while “Pivot Table” used to be considered as a skill indicator for advanced Excel users, many new features (e.g., “Flash Fill”, “Tables”) introduced from Excel 2013 are not less sophisticated or powerful than Pivot Table. In particular, the “Power” set of add-ins enable report creators and data analysts to perform powerful business intelligence with Excel.

Customers can load data from various sources (by “Power Query”), build flexible and sophisticated “Data Models” (by “Power Pivot”), create interactive visual representation of data (by “Power View” and “Power Map”), and publish and share dashboards in Cloud (by “Power BI”).

Technologies to Build Excel Add-ins. Companies and teams in the business world very often have their specific needs, this is why VBA macros are created extensively. While VBA allows users to write scripts and automate tasks with buttons and forms, Microsoft has introduced much more advanced technologies to write add-ins such as “Visual Studio Tools for Office (VSTO)” and “JavaScript API for Office”.

 

These add-ins can be found either in the form of a task pane on the right hand side of the Excel content, or in free-floating windows. They can be much more scalable, robust and powerful than VBA macros. VSTO enables IT teams to develop Excel add-ins in .NET languages and therefore to leverage existing libraries (e.g., DLL) of .NET Framework; the recent JavaScript API permits creating add-ins by web technologies (e.g., JavaScript, HTML, CSS), thus allowing developers to easily connect rich web content and entire libraries with Excel.

Third-party Add-ins and Solutions. Besides Microsoft, other software companies, startups and researchers have developed their own spreadsheet technologies and innovative solutions. Many have found solutions in the form of add-ins, for which Microsoft has opened an “Office Store”, accessible from Excel. Today, around 300 third-party add-ins are available, and can be added directly into Excel. Their functionalities are varied, ranging from development assistance to bottleneck detector, which helps people work on big spreadsheets. Other solutions are in the form of desktop applications. For instance, for IT auditors and businesses that want to ensure the correctness of their spreadsheets and mitigate risks, there are desktop applications that inspect and verify complex spreadsheets containing VBA macros.

 

 

 

4. Conclusion

Spreadsheet technologies and solutions are evolving fast. However, most users still stick to outdated tools and practices, just because they do not know the existence of new solutions. While professionals of 2000s could learn Excel by himself, nowadays the amount and the complexity of spreadsheet tools requires formal training; moreover, it is important to raise the awareness of spreadsheet risks and to teach best practices to users. The necessity of training also applies to IT departments, who need to choose appropriate spreadsheet technologies, and to build tools to meet specific needs of their customers. Today, companies should take their treasury of spreadsheets seriously and learn how to manage them efficiently, because they are massive; because there are critical data and applications; and because improving spreadsheets means improving work process, productivity, and business.

 

Biography

Tie Cheng holds a Master’s degree in the “Management of Information Systems”, and became interested in spreadsheet technologies while working as a Quant Developer in UBS Investment Bank. He then pursued his PhD entitled « Static analysis of spreadsheet applications » at ENS Paris & INRIA & École polytechnique. He is now the CEO of Matrix Lead, a startup which aims to provide spreadsheet users with leading technologies and solutions. He is responsible for the training of « Risks, modeling and technology of spreadsheets » in École polytechnique Executive Education.

Rédigé par: Polytechnique Exed

Intéressé(e) par nos formations ?

Téléchargez nos catalogues
Sélectionnez le ou les catalogues.
Select the catalogs.
  1. Catalogue Complet
  2. Catalogue complet Brochures Programmes certifiants 2016
  3. Catalogue Programmes courts 2016
  4. Catalogue Programmes courts Communication, Leadership et Management
  5. Catalogue Programmes courts Innovation et Business
  6. Catalogue Programmes courts Projets Systèmes
  7. Catalogue Programmes courts Sciences et Techniques

Veuillez remplir le formulaire ci-dessous. Vous recevrez votre catalogue directement dans votre boite email.
Please fill out the form below to receive your Catalogue by email.

Télécharger / Download
Téléchargement de la plaquette / Download the booklet

    Veuillez remplir le formulaire ci-dessous. Vous recevrez votre catalogue directement dans votre boite email.
    Please fill out the form below to receive your Catalogue by email.

    Télécharger / Download

    Please fill out the form below to download the Application File.

    Download