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.