Surveying Excel Hell – a New Approach to Manage Spreadsheet Proliferation

Can we trust this Excel workbook to show the correct Numbers?

In a previous article, Cubewise Technology Executive Manny Perez explained the reasons for Excel proliferation in an organisation and why there needs to be a fine balance between flexibility on the one hand and control on the other.

In this article, I would like to tell the story of how a real organisation first qualified the specific challenges associated with Excel proliferation and then put a program in place to address the problem.

Excel is the modern-day workhorse in the decision-making processes of most organisations. Workbooks are used to report actuals, plan future development and understand the impact of changes on corporate strategy.

The nearly unlimited flexibility of Excel for these purposes is powerful, but also one of the biggest cause of concern. How can you trust that a workbook shows the latest information, is built on trusted source data and does not containing any modelling errors? Any attempt to manage these risks is hampered by the fact that sheets can be added to a workbook, formulas can be changed and copies of the workbook can be easily created.

To address these problems, Cubewise recently supported a Sydney-based customer with a unique solution to the following challenge:

How can we develop a governance framework that ensures only trusted Excel workbooks are used for our executive briefings and monitor its rollout?

For this engagement, it was decided that any workbook can be categorised as either being a managed or an unmanaged workbook.

Excel workbooks that are business critical for a wider audience in the organisation or which are part of reporting to the Senior management are required to be managed. Managed workbooks must have a clearly defined owner who is responsible for its contents well as the data sourcing and ensures that the latest metadata (e.g. business structures like the chart of accounts) is used.

However, the vast majority of workbooks fall into the category of unmanaged workbooks. These are workbooks that are generated irregularly, cater for ad-hoc analytics, support individual decision making and / or are the platform for individual modelling.

Whether managed or unmanaged, it is important that there is a clear understanding from where data is sourced and how it is prepared and extracted.

After understanding these two categories, the big challenge is to assess how many workbooks are managed vs unmanaged and finding the people that can provide the necessary business context.

A manual approach will likely only surface the most obvious workbooks in an organisation, and not uncover the hidden gems that are imperative for key business processes.

In the initial step of the project, the customer developed an internal governance framework for Information Management. This framework established a definition of the key attributes exhibited in a trusted workbook (eg. owner, scope, data sources) as well as a change process and a platform to capture and present the workbooks. SharePoint – as the internal information platform – was enhanced to support these requirements. A similar stream was initiated to create a central business KPI catalogue to document definitions for each business measure used.

The second phase of the project then focused on monitoring Excel usage in the organisation, collecting usage information of all workbooks and providing insights into the user base of Excel. In addition to collecting usage information of the known trusted workbooks, it was key to monitor all untrusted workbooks as well – automatically surfacing key workbooks that maybe should be brought into the “trusted” fold.

To do this and quantify the extent of Excel workbook proliferation in general, Cubewise helped the customer with the rollout of a monitoring solution that captures the usage of all Excel workbooks across the organisation.

This toolset, consisting of a client-side software component called Extend and a central server called Pulse, recognises when a workbook is opened and performs a high-level analysis of it. It can thus generate information about the file size, number of sheets, file location, username, etc. and capture it in the central server.

The initial trial of the monitoring solution covered 200 Finance users over a period of 5 months.

Following the data collection, Cubewise worked with the customer to analyse and evaluate their Excel usage. The first review of the data provided some surprising insights into the organisation’s workbooks.

Even though there is nothing really to which we could compare, the number of unique workbooks opened in any given month was one of the biggest surprises. The 200 users were utilising 18,000 individual workbooks per month. 5,000 of those workbooks were sent from user to user via email.

This newly gained transparency into the number and location of the organisation’s workbooks jolted the customer into action to develop a better Excel usage policy. One of the first goals of the policy was to utilise SharePoint as the central platform to sharing and collaborating on files among users. This was intended to help avoid the inefficient sharing of workbooks via email which carry with it the risks of information security and issues with duplicated versions (aka “versionitis”).

A second surprise was the discovery that many workbooks were huge in terms of size on disk and numbers of worksheets.

Excel, through its ease of use, seemed to often be a replacement for a proper database. Over time, new sheets get added to, links created and more data points stored in the workbook. The most extreme examples at this customer were individual workbooks with more than 700 sheets or more than 120 MB.

Those workbooks are closer to a database than a foundation for ad-hoc end user driven analysis. They also highlighted the importance of developing a governance framework where business requirements can be quickly translated into business intelligence solutions.

The customer reacted to this by initiating a process to review the most extreme workbooks and determine what tool is best suited to support the underlying business process. Some of the workbooks were implemented as business intelligence solutions in TM1, whereas some lead to training and coaching programs for the end users.

Coming back to the original project scope, the monitoring process helped the organisation for the first time understand their use of managed and unmanaged workbooks.

The monitoring data shared in the SharePoint portal now provides transparency on the frequency of usage and the number of users for each of the managed workbooks. Armed with this information, the organisation can now focus development efforts on business applications associated with those workbooks.

An even more powerful outcome of the monitoring is the transparency gained of unmanaged workbooks that should move to being managed workbooks. The trigger for making an unmanaged workbook a managed one is currently defined by thresholds on the frequency of usage and the number and type of users.

For example, a workbook that is used by more than 10 users in a month or opened very frequently will move under the umbrella of the governance framework. To start this move, an owner will be assigned to it and a quality control process will start. This process determines if the scope of the workbook is already covered by a managed workbook and if not it then validates the content of the workbook and its sources.

The combination of a well-defined governance framework and the ability to monitor its implementation and usage, has put the organisation into a uniquely valuable situation.

Not only does this help to ensure that only validated information is used in corporate decision making processes, but it also helps to surface those potentially important processes that were not in the initial spotlight. Further, it simplifies collaboration within the Finance team, as there is now a well-defined platform where core workbooks and reports are stored and documented.

Finally, the monitoring also helped to surface Excel “applications” that could be putting the organisation at risk of working with the wrong or outdated information. The process to review those applications and determine the correct system for its management, plus the training for key users helps the organisation use its own knowledge better with the right tools.

© 2017, Manny Perez & David Sauren