EUC’s are applications developed by ‘end users’ in Excel or Access but not necessarily using coding standards or have formal IT training. The operations/BAU staff are developing the EUC’s and not the IT department. End-user computing applications are also used in databases, queries, scripts, or output from reporting tools. Financial firms have a high number of complex EUC’s. Unmanaged EUC’s represent a big risk for businesses, especially those in the financial sector such as banks and insurance companies. This post looks at Reducing Business Risk by having an EUC Policy, and how to implement it. Also Importantly how to perform EUC Remediations.
Central Bank Model Risk and EUC’s
The Bank of England have now issued Supervisory Statement SS1/23 – Model risk management principles for banks setting out the PRA’s expectations for banks’ management of model risk. It covers a wide variety of quantitative calculation methods, systems, approaches, end-user computing (EUCs) applications and calculators used in firms’ daily operations, i.e. output supports decisions made in relation to the general business activities, strategic decisions, pricing, financial, risk, capital and liquidity management or reports, and other operational banking activities. The deadline for meeting these standards was 17th May 2024. This is a wakeup call for firms to Reduce Business Risk by having an EUC Policy
Monster Excel Spreadsheets Are a Major Risk for Your Business
Excel Spreadsheets are used in most Banking and Insurance businesses these days. Complex applications can be developed relatively quickly. However, many users left to their own devices can develop spreadsheets into Excel ‘monsters.’ This normally happens where there is no EUC policy, and the users ‘do their own thing.’ These tools can bring huge risks as they can have a large impact on operational and financial exposure for the firm in question. If things go wrong these tools can also cause reputational damage. My article discusses how to reduce those risks, and how to perform EUC remediations.
Excel Spreadsheets that are too complex for people to understand are high risk and require EUC remediation (see below). Some features of EUC’s include:
lack of corporate visibility
- insufficient documentation
- poor design and lack of coding standards
- poor or non-existent audit trail
- not fully tested / unvalidated
- financial applications require extreme diligence / have a large corporate impact
- lack of corporate visibility
Questions for thought:
- How can you audit complex EUC’s?
- How can a BAU user use it for everyday tasks if they don’t understand it?
- Why are we confused by large unwieldy spreadsheets?
Human limitations – Millers Threshold
Millers Threshold is concerned with our “working memory,” or short-term memory. It is 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. Miller says that pushing the number of “bits” of information above this threshold caused confusion, leading to incorrect decisions being made.
Therefore, Excel spreadsheets need to be designed in such a way to stop any confusion setting in. I have encountered many Excel monsters throughout my 25+ years working in Banking and Insurance Companies. The usual reaction of senior management and IT when they find out about these ‘monsters’ is one of panic and fright, especially if it is a business critical EUC. Then they would normally call in a spreadsheet expert, to sort it out.
In this article I will outline real-life case studies, Techniques for spreadsheet remediation, EUC policy and briefly discuss Office 365. These ticking time-bombs could be found anywhere in your business.
Do you know where the Monster Spreadsheets are in your business?
Case Study 1
A leading fund management company brought me in to remediate and manage a key EUC fund management Excel 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, and then it is gone.
After some analysis I re-engineered it to use less 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’ with serious implications for the business, who could have got into trouble with the PRA for incorrect reporting. After re-engineering and adding stronger calculation the results were then correct. The issue was that Excel ‘gave up‘ on calculation as there were too many dependencies.
Case Study 2
A leading re-insurance 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. Also, the results were sometimes wrong/could not be relied upon (not surprising really). My solution was to develop a clean, data validated, parameter data driven tool which efficiently and accurately produced the required PRA reports with the click of a few buttons.
Case Study 3
A leading insurance company brought me in to remediate and redevelop an Excel based balance sheet reconciliation tool. It was a 100MB ‘monster’ with multiple tabs, with 100,000 records on each tab, (with 100,000 formula rows), pivot table reports over 50,000 rows long and they complained it kept falling over, and running out of memory. With almost no requirements, I converted the formula rows to values, split it into two models, with the pivot tables hived off into a separate model, along with a hardcoded version of the required data. My EUC Remediations included automating the manual processes using Visual Basic Applications with large reductions in memory and file size, and an increase in speed. Left unattended I’m sure it would have corrupted and the model become unrecoverable.
How Errors creep in
The quality of regulatory reporting, is compromised by poor controls at the source data level, and a lack of willingness at management level to do anything about it. Businesses need to appreciate how important good quality controls are at the lowest level. Errors introduced in source data feed upwards to the higher-level management and BOE reporting.
Hint: Capture errors at the lowest level possible.
An example in an assumption management system I worked on, errors were appearing in the SQL Server system higher up the chain due to source data errors. To fix this I wrote data validation on EUC source files to pick up errors and fix them. Another example on a Solvency II system was to add controls that ensured that allocation between asset classes added to 100% and report exceptions.
Where one is processing half a million records these checks are critical.
When the results of error prone EUC’s are submitted to the PRA/BOE it is a serious matter if there are mistakes in reporting.
Useful EUC Remediation Techniques for fixing Excel ‘Monsters’ that are at risk of corruption
- Save the model as binary format (compresses the file into an xml format)
- On large data set tabs with embedded formulas, retain top row formula, copy down and paste as values (don’t forget to recalculate first!)
- On pivot tables clear the data cache (can use a huge amount of memory)
- Split into two or more models (split data from calculations)
- Upgrade to Excel/Office 365 64 bit (can address up to 8 terabytes (TB) of memory)
- For corrupted models copy sheets to an empty workbook and rebuild from scratch (not for the faint-hearted)
- Remove redundant tabs, name ranges, orphan file links, data connections
- Clear used range for each tab (done via the tab class module and debug window)
- Remove or replace ‘volatile’ excel formulas such as now(), rand() etc.
- Review any custom worksheet functions
- Review which VBA libraries are being used
Office 365 and 32bit to 65 bit etc
Excel 365 comes with a bunch of useful new functions such as concat, textjoin, ifs and xlookup which can replace the index/match functionality (can do a lookup on the left hand side). Things to watch out for in Excel 365 are the API calls which now require the prtsafe / prtlong syntax to manage 32 and 64 bit. I also noticed the behaviour of workbook_open had changed so had to do a workaround for that. I shall do another article on Excel 365 which goes into more depth.
EUC policy
Banks tend to have a more disciplined approach than insurance companies, with regards to EUC policy, but the insurance companies are slowly improving. Banks such as Lloyds Banking Group are reducing Business Risk by having an EUC Policy – and now 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 / grading of Complexity
- An analysis / grading of Business Risk
- The EUC business owner
- The EUC technical owner
- Complete documentation, including changes log, user guide, system guide, technical guide
- System for checking in / out for changes e.g. from Sharepoint
What is a Critical EUC?
- Is it of sufficient complexity in terms of vba code, formulas and other functionality?
- Is it operationally important, and/or have a large financial reporting impact?
If the answer is Yes then it’s a critical EUC and needs to be EUC compliant.
EUC Policy – How to get a ‘Pass’
I have been tasked at the insurance firm where I am currently working, to implement EUC Governance Policy, for a group of EUC’s, and Implement the additional controls, VBA automation and Documentation for EUC’s to get a ‘Pass.’ Also, to review and advise other EUC users what they need to do to get a pass. An EUC Inventory register of Critical EUC’s is a key component of EUC policy. The correct remediations are the method to get a pass.
EUC Remediations
You might ask what remediations are required to an EUC meet a good EUC Policy standard? Typically a critically important Excel EUC would need checks that the source data is the same that is actually loaded. Checks to ensure that the data (field headers) in input files have not changed – a big red flag, data type checks, a changes/version control log, protection, tab design on make it simpler and a User Guide and System/Technical documentation. This would be the minimum required. Generally, the more complex an EUC is, the more remediation is required to make it ‘robust’ enough to get a pass. The checklist I introduced in the Audit Model identifies weaknesses and gaps in the EUC, that need remediation.
EUC Policy Audit Model and Checklist
I developed an Excel Model to determine EUC Complexity and ‘Business Criticality’ in other words ‘Is it in scope as a critical EUC?’ The model importantly determines the amount of remediation required to bring it up to EUC Policy standard, and importantly identify weaknesses. The tool is aimed at medium to highly complex EUC’s which have a substantial operational, functional or external/PRA Impact and need to go on the EUC Register. It is a very useful checklist, which I scan looking for appropriate remediations to make an EUC robust, understandable, and have good controls with documentation. This makes it safe for BAU to use.
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 the normally the ones who have had experience of ‘rogue’ spreadsheets, for instance, had a call from the PRA/BoE, and are ‘motivated’ not to make the same mistakes.
I highlight to managers what EUC controls are required, for example, validation of data, VBA controls, User Guide and System Guide, Changes log/Version control etc and it is important that managers need to put time for this work in their plans.
Bank of England Audit and SS1/23
Financial firms need to have a ‘robust’ EUC Policy to comply with SS1/23, and make the board responsible for it. This is something that the Bank of England is looking at very closely, so if you do not already have one, it is time to get an EUC Policy implemented in your firm, before the Bank of England/PRA do an audit. Get your critical EUC’s checked and upgraded by us now.
Richard G Mann FMAAT, MIAP, MBCS is an EUC developer and EUC Policy expert and has worked in the financial services sector, in the Regulatory and Risk space, for over 25 years. He specialises in EUC development, mainly Excel VBA, Access database development and SQL Server. He is a professionally qualified accountant and also a member of the British Computer Society and the Institute of Analysts and programmers.
Get in touch now for help with Reducing Business Risk by having an EUC Policy
#EUCRemediation #ExcelHorrorStories #ExcelRemediation #ReduceRiskEUCRemediation #EUCRemediationCost
#EUCRemediationsoftware #EUCRemediationExamples #EUCRemediationpdf
