Spreadsheets: Taking a “Macro” view
Understanding and managing the noisy environment
Before getting the chance to ask, “Is a spreadsheet really the right tool for this job?”, end users often find themselves instinctively crunching away within the familiar spreadsheet grids, building a model that promises marvellous benefits.
Despite all the advances in enterprise technologies and solutions for manipulating and processing data, spreadsheets continue to be the daily “go-to” for “simple quick solutions” for a wide variety of problems. Even when a company invests in an enterprise system for a well specified task, the first question users tend to ask is “Where is the Export to Excel button?”.
Simplicity, combined with a forgiving programming language (VBA) allows anyone to build a spreadsheet application. All that flexibility and familiarity, however, comes at a price. Every spreadsheet created and used implicitly commits a company to support a new process which lives on, long after the original user left the firm. If the original user of the spreadsheet didn’t ask why the file needed to be built in the first place, why would their replacement?
To understand spreadsheet risk, we need to view spreadsheets though 2 lenses
- The Micro view
- The Macro view (excuse the pun)
The Micro view involves looking at individual spreadsheets and assessing their risk. For example, the integrity of the physical file, formula errors, inefficient calculations, quality of VBA code etc. Much focus in recent years has been on the impact of these risks to organisations. We can leave the reputational risk and data security issues to one side since a simple google search will highlight what a badly designed spreadsheet can do to a company’s reputation and bottom line.
With a push for more data security in organisations, spreadsheets have come under the spotlight. Organisations are now focusing more on governance and controls around spreadsheet use. But in today’s digital world, organizations need to go further so that they understand the context of how spreadsheets are used, shared and how each spreadsheet supports their business operations.
The Macro view is as interesting but far less explored. Here we are looking at the overall risks that spreadsheets pose to an organisation. These risks come from the lack of transparency of individual files and how much (as a whole) they support business operations. The more transparent and documented these files are, who uses them and what they do overall, the less risk organisations experience as they can now mitigate potential threats.
While many organizations have employed End User Computing (EUC) governance policies and controls around the development of spreadsheets, many firms still struggle to keep track of the realities on the ground. Even for those firms that don’t have policies in place, simple steps can be taken to improve the state of the environment going forward by following a few principles:
Is a spreadsheet the right tool?
- Spreadsheets are probably the best tool available for prototyping and creating simple automation of tasks. If you want to use a spreadsheet to store data, why not just use a database instead?
What does the spreadsheet do, and will others comprehend it?
- How often have you clicked a button to generate an output without really knowing what’s happening? Spreadsheets should not be black-boxes! Simple documentation outlining the owner and operation can go a long way to preventing future risks.
Keep It Simple, Stupid!
- A spreadsheet should read like a book. Left to right, top to bottom. If you are processing a calculation, think about inputs and outputs that are clearly labelled. Before you start nesting, hard-coding or creating long formulas, think about how difficult they can be to interpret when de-bugging. Keep things consistent. Simplicity is not only elegant, it’s also efficient.
So how do you begin to understand a legacy spreadsheet environment that includes thousands of files, with no obvious structure? Many firms rely on automated discovery tools, file repositories and annual reviews to assess the criticality of spreadsheets. These are often time-consuming tasks which don’t afford the time to dig deep enough below the surface to understand the risks, yet they remain a crucial line of defence. Utilising technologies to automate and analyse spreadsheets can be a big help to achieve this objective quickly and accurately.
Peeling back the layers of a massive spreadsheet environment is enough to give any enterprise risk manager analysis paralysis. But understanding the unstructured spreadsheet environment is a valuable source of value that can help firms manage risk, improve the efficiency of operations and migrate to new technologies quicker. Most motivating of all, this macro view may help reduce the volume and reliance of spreadsheets across the entire organization. Behind every spreadsheet is a person and a process, so understanding the context of this environment will help you make more informed decisions about your business.