Reducing business risk when your users create solutions

End-user computer solutions, or EUCs, are applications developed by end users. These can be in Excel or Access and, though EUCs show users’ enthusiasm for their jobs, these solutions are generally made by people with no formal IT training and without reference to coding standards. End-user computing applications are also used in databases, queries, scripts, or output from reporting tools. Effectively, EUCs are developed by the business as usual (BAU) staff and not the IT department.

Banking is, of course, a highly regulated space, overseen by the Bank of England (BOE). If, for example, an EUC feeds incorrect data into a firm’s submission to the BOE’s Prudent Regulatory Authority (PRA) the consequences can be dire.

Bank of England Audit

Financial firms need to have a robust EUC Policy and make the board responsible for it. This is something that the Bank of England is looking at very closely, so if you have not already it is time to get an EUC Policy implemented in your firm, before the Bank of England does an audit.

The risk of Excel monsters

Excel spreadsheets are used in most banking and insurance businesses. Complex applications can be developed relatively quickly. However, many users left to their own devices will develop spreadsheets into Excel monsters. This normally happens where there is no EUC policy.

Excel spreadsheets that are too complex for people to understand are high risk and require remediation. For businesses, EUCs present many challenges, including:

• lack of corporate visibility
• insufficient documentation
• poor design and lack of coding standards
• poor or non-existent audit trail
• they are not fully tested or unvalidated
• financial applications require extreme diligence, particularly where they have a large corporate impact

These issues lead on to the following critical questions:

• How can you audit complex EUCs?
• How can a user use a solution they don’t understand?
• Why are we confused by large unwieldy spreadsheets?

Human limitations and Miller’s Law

Miller’s Law is concerned with our working memory, or short-term memory. It describes the capacity of the brain to hold multiple pieces of information at the same time. It also involves our ability to make decisions using those pieces of information. Miller’s Law asserts that the memory span of people is limited to seven pieces of information, and that pushing the number of bits of information above this threshold causes confusion, leading to incorrect decisions.

For you

Be part of something bigger, join BCS, The Chartered Institute for IT.

Therefore, Excel spreadsheets need to be designed in such a way to stop any confusion setting in. I have encountered many Excel monsters throughout a 25-year career, working with banks and insurance companies.

In my experience, senior managers and the IT team often panic when they encounter a rogue EUC. That’s particularly true when the work-around is business critical. Next, they tend to call in a spreadsheet expert to fix the problems. Before they make that call though, there are things that can be done to address the risk and hopefully avoid the situation happening again.

Finding your monsters

Monster one

A leading fund management company brought me in to remediate and manage a key EUC fund management model with embedded derivatives that nobody understood, except the person who developed it. It was totally over engineered and unstable — large excel models can corrupt at any time. After some analysis I re-engineered it to use fewer tabs and reduced size from around 100MB down to about 25MB. It was then out of danger. I then automated some of the manual steps. One of the critical things I found was that the results of the model were wrong. That had potentially serious implications for the business, who could have got into trouble with the PRA for incorrect reporting. After re-engineering, reducing dependencies and adding stronger calculation the results were correct.

Monster two

A leading re-insurer company brought me in to remediate and redevelop a PRA reporting Excel spreadsheet tool. After some analysis, I concluded it was not fit for purpose. Why? It used unwieldy file links, several badly written flavours of VBA code (some recorded), and it kept falling over. Ultimately it was unreliable, and its results were sometimes wrong. My solution was to develop a clean, parameter data driven tool which efficiently, and accurately, produced the required PRA reports.

Monster three

A leading insurance company brought me in to remediate and redevelop a balance sheet reconciliation tool. It was a 100MB monster with multiple tabs, with 100,000 records on each tab (with 100,000 formula rows). It also used pivot table reports over 50,000 rows long. The firm complained the solution kept falling over and running out of memory.

With almost no requirements, I converted the formula rows to values and split it into two models, with the pivot tables hived off into a separate model along with a hardcoded version of the required data. After some VBA automation I eventually got the two models working well with a drastic reduction in size and memory usage.

How errors creep in

Generally speaking, the lack of controls at the firms I have worked at is astonishing. The quality of regulatory reporting, or lack of it, is mainly down to poor controls at the data level, and a lack of willingness at management level to do anything about it. Only a small percentage of the companies I work for seem to appreciate how important good quality controls are at the lowest level: errors introduced at the source level feed through upstream to the highest-level reporting and Bank of England reporting.

EUC policy

Some banks have a well-developed and robust EUC Policy. Features of a good EUC policy include:

• An inventory of all business-critical Excel spreadsheets and Access databases
• An analysis and grading of complexity and business risk
• A record of the EUC business owner and technical owner
• Complete documentation including changes log, user guide, system guide and technical guide
• A system for checking in and out for changes

EUC remediations

You might ask: what remediations are required for a good EUC policy standard? Typically, for a critically important EUC, you would expect:

• Checks to ensure that the source data is the same as the data that is actually loaded.
• Checks to ensure that the data (field headers) in input files have not changed
• Data type checks
• A changes and version control log
• A user guide and system/technical documentation.

These would be the minimum requirement. Generally, the more complex an EUC is, the more remediation is required to make it robust.

Implementing EUC policy at senior manager level

Even where an official EUC Policy exists, there is sometimes a lack of willingness to actually implement those controls. Some managers take EUC policy very seriously — others do not. The ones who take it seriously are normally the ones who have had experience of rogue spreadsheets, for instance, and are motivated to avoid making the same mistakes.

I highlight to managers what EUC controls are required: for example, validation of data, VBA controls, a user guide and system guide, changes log/version control etc., and it is important that managers put time for this work in their plans.