Spreadsheet Control Strategies and Tactics

The majority of spreadsheet users/builders never consider the methods of controlling spreadsheets as a separate entity to the functions of the spreadsheets themselves. A minority do consider controls and employ a variety of tactics (e.g. protecting cells, hiding sensitive/rows columns, hiding a sensitive worksheet, employing named elements, employing specialised structure (like Excel tables)). I have yet to see this given strategic consideration at the beginning of the construction process.

The purpose of this article is to seek to persuade readers that in addition to these elements (the primary functions and the control methodologies) the context and environment in which a spreadsheet exists is equally as important. This context should be considered separately at a strategic level. This context will have a profound impact on the way we build spreadsheets in terms of structure and the life span of any individual spreadsheet system.

Background – an auditor’s perspective

In traditional management systems, we were able to separate clearly the functional role of the system and the controls associated with the system that ensured it operated correctly. We employed simple control mechanisms such as the separation of duties and validation of calculations by cross tabulation. In extremis I came across one early computerised farm subsidy system (circa 1980s) where entries were validated by double entry; the entries were posted by two different operators and the system validated that both sets of entries were the same. Since then, cheap effective computerisation has changed both the systems and the landscape within which controls operate hugely. How should we manage this change?

In nature, the functional systems and the controls that maintain the functional systems are so closely integrated we tend to treat them as a single system. The relationship between a biochemical system and its related enzymes are such, that it is almost impossible to describe the chemistry of the pathway without incorporating a description of the role of the enzyme. Similarly the relationship between a computerised management system and the enzyme like nature of the controls need to be teased out.

When I think of a computerised management system, it’s not usually a spreadsheet or even a system of connected spreadsheets. Rather it is a combination of related activities where managers, operators and the computerised system combine together to achieve some useful outcome. It is only rarely that a (system of) spreadsheet(s) is considered part of the management system and is recorded as such.

Recently some emphasis has been given to identifying the “important” spreadsheets within an organisation, so as to ensure that they are monitored and controlled properly. In my view this is the equivalent of putting the cart before the horse. It is the horse (or the functional system) that should be doing the pulling.

The Relationship between Spreadsheets and other computerised systems

Ask yourself the question “how often do spreadsheets really exist on total isolation?” In my case the answer is never, but as a general rule an assumption of almost never is pretty safe. Does it matter then that the inputs come from somewhere else and that the outputs go to somewhere else? In my view, yes, and the rest of this section is devoted to an explanation.

Spreadsheets may indeed be ubiquitous, but that does not mean that they can or should do everything. While there are some effective comprehensive spreadsheet systems in the accounting and business control environment, these are the exception rather than the rule. However, there is one lesson I’d like to draw from the accounting environment. Every figure in the income and expenditure ledgers is verifiable to an independent source. In fact this sense of verifiability extends to every figure posted into the accounting system, albeit not necessarily from an income or expenditure voucher.

So if a spreadsheet is only part of a system, part of the discipline of creating a process/calculation that is internally consistent is to specify where the inputs came. If necessary, this means that they can be checked. In my own experience it is very useful to have hyperlinks to the original sources. It saves potentially hours of argument.

I accept that where there are a large range of sources creating connections to each source can be tedious. But the counterargument is that; the wider the range of sources the greater the opportunity for errors to creep in. Verifiability is essential. The quicker and easier it is to verify a particular figure the better.

Indexing sources of information

As a general rule, the outputs from a spreadsheet are for the owner to handle and to use as he or she sees fit. One might therefore argue that as the owner she or she is under no obligation to show how the results are used. 

As an auditor I would argue that this an unrealistically narrow perspective. The spreadsheet is part of an overall system. Of course the owner of the overall system should maintain an overview of how it operates. It is up to the individual to decide the level of detail that overview should take. My opinion is that each spreadsheet should state explicitly where the results should be used and by whom. This detail would help give information about scope of validity of the results and their durability.

Tactics for Defining Relations between Operational Systems and Spreadsheets

There is no universal method for defining the context in which any individual spreadsheet exists. Indeed from my own audit perspective, context is nearly always depended on who told me that a particular spreadsheet existed and why it had been created. It was very rare that I could go to a spreadsheet file and find out from within it why it existed. While this is certainly the generally accepted method of handling spreadsheets, I consider that it is open to question whether this has ever been satisfactory.

Spreadsheets are independent artefacts that rarely form part of an integrated system that is controlled from the centre by the “systems controller”. Therefore spreadsheet creators must develop a methodology that can be easily followed by new user as to the context in which it exists. Once again some of the more advanced software for spreadsheet accounts/financial systems provide indexes and links that help set the context, but this is far from universal.

Where next!

Typically, I have developed my own methodology for allowing spreadsheet users to set the context of a spreadsheet system within a controlled environment. But what it is needed is education of creators and users to be explicit about where their data is coming from and where the results are going to be
used.