EUDA / EUC risk elimination
“Businesses must do more than pay lip service to controls on spreadsheet usage”
Lisa Pollack, Financial Times
We could not have said it better ourselves.
Driving revenue assurance using data analytics
Regulators are becoming increasingly focused on EUDAs (End User Developed Applications) / EUCs (End User Computing), ie spreadsheets and smaller databases, and the risk that they present.
“We are conducting a risk based review of firms to gain comfort over how firms are managing the quality of data (accuracy, completeness and appropriateness) … The data review for systemically important firms [includes] Data and IT controls with focus on higher inherent risk areas such as manual transformation of data using EUC tools (e.g. spreadsheets)” Jothi Philip, Financial Services Authority, July 5, 2012
EUDAs often appear in key processes or reporting chains within firms, sometimes explicitly, but also implicitly. Some of the more public failures are described at eusprig, but things feel closer to home when spreadsheet tracking software is applied to a spreadsheet and the number of direct and indirect dependent links runs into tens, scores or hundreds. Many of the vulnerabilities are known (see below) but how do you solve it?
Discussions on this topic generally highlight that Excel’s a useful and flexible tool but how it is used, checked and crucially how much firms rely upon spreadsheets is key. And Excel went bigger a few years ago too, no longer are we restricted to 65,000 rows, 1 mm rows is now available that allows more data to be brought in and analysed, but the control framework has not kept up. So firms may agree that more robust control processes or alternative applications are required to address the operational risks but there is also the issue of usability: how can the processes that they depend on be made more robust without losing functionality or disrupting the user base?
Calimere Point has assisted clients in their EUDA risk elimination by addressing both audience types: a) internal audit and risk management and b) day to day users who demand flexibility. As one manager referred to our solution,
“…this may be the acceptable face of controlled processes for Excel lovers.”
EUDA elimination example
The video below shows how a couple of large spreadsheets linked by a macro can be replaced by our analytics engine. The engine shows the entire logical flow linked together in a modular fashion; has the static data centralised and the whole process can be put under change control.
A note for those who are practitioners of Excel-fu: if you are a spreadsheet user that has dust on your mouse, the Crtl key on your keyboard is rubbed out or Alt+E,S,V,I means something to you then you have a powerful, flexible tool that gets to where you need.
But you will also see some truths in the statement: “It’s difficult to debug, lacks decent revision tracking, and typically can’t be bludgeoned into a modular framework to be built upon block by verified block.” Lisa Pollack, FT.
It’s also likely you’ll be somewhat sceptical when a tool offers the flexibility and power of spreadsheets but is more robust and has a good, useable control framework.
We have die-hard Excel users, in some cases we still use Excel 2003 as we like to keep all the old shortcuts, but, yes, you can have both worlds.
Some common spreadsheet risk issues
Hard to trace data path across multiple sources, sheets and links: each cell needs to be examined
Difficult to see precedents and dependents on complicated spreadsheets especially if popular array formulas (match, vlookup and hlookup) or indirect formulas (offset, indirect) are used
Hard Coding: Not only do paste values break the audit chain, but static data can be mixed in with dynamic formulae and formulae can be replaced with static data without the user realising their spreadsheet is no longer fully dynamic.
In built version control is not often used, changing the filename is the common method: “Report_Final_V03_Final02.xls”?
Very hard to see the high level picture in one spreadsheet let alone the ones that it links to.
Perhaps the killer assumption in Excel is that all rows and columns in a given array are treated equally, but lines can be missed out and formulae can be inconsistent. And it’s easy to miss this
Input data can be changed directly and without trace.
Automation, robustness and standardisation
Macros? Great flexibility to repeat more complex tasks but it can open a further can of risk worms
Analytical libraries can often be shared but creating a common modular macro library is, from our experience, rare.
Related case Studies