Managing spreadsheets

Spreadsheets represent one of the most popular applications on the planet. This is because they are the reporting and analysis tool of choice for many professionals and because they support collaboration and information sharing. Moreover, this is not going to change, not just in terms of existing business people but, as our children are being taught how to use spreadsheets in school, this popularity is likely to continue for many years to come: spreadsheets are ubiquitous and will remain so.

However, no matter the popularity of spreadsheets they also, used improperly or incorrectly, or without sufficient control, pose a greater threat to your business than almost anything you can imagine. They can give rise to compliance issues because changes to data are not audited. They can also be used to aid and abet fraud, because security is not applied to conventional spreadsheets and, again, because there is no control over the ability to change data values. Further, it is easy to make mistakes in spreadsheets (for example, by entering an incorrect formula) that can mislead decision makers, the results of which can be very expensive. HM Customs & Excise in its Methodology for the Audit of Spreadsheet Models says that “the complexity and functionality of spreadsheets has reached levels of sophistication that few could have imagined… the consequent threat posed to businesses by such powerful ‘end user’ applications, mainly in the hands of untrained users, is immense”.

A major cause of these problems is that spreadsheets are not treated as an enterprise resource. For example, although there are (limited) security and auditing facilities in Microsoft Excel these are not usually enforced. Indeed, many users will not be aware that such facilities even exist. In the main, this is because spreadsheets are not perceived to be an IT resource but are seen to lie within the business domain. As a result, corporate security standards are not implemented for spreadsheets. On the other hand, the business is not aware of the potential dangers that the uncontrolled use of spreadsheets can cause. A major focus of this paper is therefore to make business users aware of these dangers so that they can push the task of managing spreadsheets in to the hands of the IT department. In particular, it discusses the need for spreadsheet management, precisely in order to prevent, or at least minimise, the issues just mentioned.

Having established the need for spreadsheet management solutions, this paper goes on to discuss what such a solution might look like. In fact, there are different approaches that may be taken, ranging from complete control (that is, you absolutely prevent people from doing what you don’t want them to) to complete monitoring with no control (that is, you monitor all changes but do not actively prevent any of them—rather like closed circuit TV). We will discuss the relative merits of these positions and when each of these might be most suitable (which will depend upon how spreadsheets are used and for what purpose). Unfortunately, at present there are very few vendors offering relevant solutions. However, there are many vendors promising (but not delivering) relevant solutions and we will discuss the sorts of facilities that any potential solution should provide.

However, as we shall see, there are no complete solutions available today and we will also, therefore, consider how organisations can optimally manage their spreadsheet solutions, both in terms of the management procedures that need to be implemented and with the respect to those tools that are available. One final point is that there is an inevitable congruity between the concept of spreadsheets on the one hand and Microsoft Excel on the other. Excel is, after all, the epitome of a spreadsheet application, and it is by far the most widely used. In general, where Excel is referred to in this paper it can be taken as a synonym for spreadsheet.

Free Download (subject to terms)