Blog

Blog

Microsoft Excel and BCBS 239 – 7 Steps to transform your End User Compute (EUC) capability

March 1, 2017 | Darren Harris
Darren Harris

September 15th 2008, the day that Lehman Brothers Holdings Inc. filed for Chapter 11bankruptcy protection was another significant milestone in the global financial crisis that began in 2007. For those of us working at Credit Suisse during this entire period, fire drills [urgent business requests] were an hourly occurrence as we fought to respond to ad-hoc requests from the business in the face of swinging markets and creaking models (negative interest rates anyone?).

Having spent a number of years (and significant sums of money) in building out the strategic front office platform for Interest Rate trading, as a technology team we were still ill-prepared for the cross-asset requirements the business now demanded. Quickly and accurately aggregating Counterparty, Liquidity & Concentration risk at the group level, across business lines and between legal entities became a point of competitive differentiation in the market.

This test of the bank's information technology and data architectures to support the broad management of financial risks and dynamic user requirements posed a significant challenge. Despite best intentions, IT could only keep up with limited demand from the business for change.  It was during this time that EUC (systems in which non-programmers can create working applications) - specifically Microsoft Excel (with some additions), really came into their own.

BCBS 239

Recognising this challenge was omnipresent across the entire banking industry, the Basel Committee in January 2013 passed regulation number 239 – “Principles for effective risk data aggregation and risk reporting”. BCBS 239 defines a number of principles with the objective of strengthening banks risk data aggregation and internal risk reporting practices. It is the view of the Basel committee that effective implementation of the Principles contained within BCBS 239 will enhance risk management and decision making processes at banks, in turn leading to improved market stability during a subsequent financial crisis.

As financial regulations go, BCBS 239 is surprisingly readable and although the principles are written specifically for risk management, they can be broadly applied as best practice to any problem where data is aggregated and reported.

End User Computing

EUC applications to this day, remain a critical enabler in any financial organisation. Analysts and business experts that understand the business domain far beyond the comprehension of IT (as they rightly should) require tools that support business operations and rapid decision making, even more so in times of stress and crisis.

The gamut of BCBS 239 is applicable to the use of EUC within financial institutions, specifically however Principle 6 – “Adaptability” describes the requirement to provide capabilities that support flexible, ad-hoc data requests as needed, to assess emerging risks and to respond to regulatory requests.

 “A bank should be able to generate aggregate risk data to meet a broad range of on-demand, ad hoc risk management reporting requests, including requests during stress/crisis situations, requests due to changing internal needs and requests to meet supervisory queries.”


There are a number of software applications that are classified as EUC, the more modern of which describe themselves as “self-service data analytics” tools. Microsoft Excel however remains by far the most widely used and the most dominant application not only in Finance, but across every sector.

Governance of Excel however is not without its challenges. Over the years there have been many high-profile incidents that have led to significant financial downside caused by human errors in Excel Worksheets. Most recently the investment bank Lazard’s valuation of SolarCity on behalf of Tesla Motors, discounted the value of the solar energy company by $400 million, the root cause was identified as an error in an Excel Spreadsheet.

To simply swap Excel out for a different EUC without understanding the core foundations of governing the use of EUC within finance is a naïve approach, errors still occur in other tools and consequences can be just as severe. Conversely, adding a robust layer of protection around the use of Excel (“locking it down”), without introducing new ways to simplify and reduce the complexity inherent in all Excel spreadsheets (and all EUC’s that solve complicated problems), little is done to reduce the risk of manual error.

Schematiq

With Schematiq, we have built a platform that encapsulates our vast experience of working for investment banks in making the use and governance of Microsoft Excel enterprise grade. The Schematiq platform brings an innovative DevOps inspired style of thinking to the issue of governance of Excel. Schematiq additionally introduces a number of key innovations that significantly reduce the complexity of Excel workbooks, that when combined with our alternative deployment models significantly reduce the likelihood of incorrect analysis and reduce operational risk. This combination of capabilities is what makes Schematiq unique.

7 Steps

Transforming your EUC landscape and use of Excel into something that meets the principles of BCBS 239 is a daunting and on-going challenge. Simply identifying which assets users have built, even before understanding how they are used and by whom can be a significant obstacle. Bombarding end users with questions on their use of EUC or trawling through file inventories from network drives, quickly becomes outdated and ineffectual in understanding the size of the problem that you have to tackle.

The seven steps outlined below are typical of our engagement process for improving the use and governance of Excel across an organisation. Where an EUC has already been identified as having a high degree of risk, we typically start with step three.

1. Inventory – The goal of this step is to build a complete inventory of spreadsheets that are used across the organisation. In addition to capturing a current state, the inventory must be continually maintained to identify newly generated assets that could be critical to the business. Capturing key metrics from the sheet such as active user, duration used, worksheet size, add-ins used and sheet complexity are key to informing step two of the process.

2. Ranking – During this step a risk ranking model is defined across the metric data held in the inventory to generate a score card that can be used to prioritise a remediation or governance work stream. Spreadsheets have a number of common characteristics that lend themselves to a standard complexity risk model, however the inventory is typically enriched with organisational information to focus on specific business areas or roles that are deemed high-risk.

3. EUC Controls – Based on the Ranking and business priorities, the spreadsheet can then be deemed subject to EUC controls. Without impeding the agility required by the business, controls are put in place that allow for versioning of the asset and full transparency of changes and use of the asset across the organisation.

4. Standards – Once the asset is placed under EUC control, it can be reviewed against a set of standards that are viewed as best practice in the use of Excel. These recommendations can be provided to the End User to allow them complete autonomy in improving the quality of the asset and compliance can be centrally monitored. Common functions and logic can be extracted as template functions, and then re-used across a number of different spreadsheets allowing for development of a library of assets and common models.

5. Baseline – When the asset has been brought in-line with the organisational standards and controls, the asset is deemed to be functionality correct and is baselined. The emphasis in this step is to ensure that future changes can be deployed confidently and that a full set of tests exist that confirm the correctness of the EUC.

6. Monitor – On-going monitoring of an EUC asset is essential to ensure that additional complexity or operational risk is not introduced. Governance functions require up to date spreadsheet metrics and an understanding how of the asset is in-use across the organisation. Typically when the use of the EUC becomes widespread, the asset is migrated from an EUC into a fully supported application developed and maintained by the IT organisation. The information captured in this step can be key to building the business case to justify this course of action.

7. Remediation – EUC’s do not need to be shared equally across the organisation and reducing access to an underlying asset without limiting consumption of its output is a key capability. Instead of making spreadsheets read-only, Schematiq can convert spreadsheets directly into dynamic API’s that can be executed independently of Excel. This capability is unique to Schematiq and allows for invocation of the underlying logic from a number of different technologies such as d3 or Tableau, providing an organisation with the capability is also key to accelerating an organisations dependency away from a pure Excel EUC solution.

Where next?

Since the first version of Excel was released in 1985, it has grown to an estimated user base of over 500 million users. Within every large organisation there exists a significant number of Excel EUC developed assets that perform critical business functions. Even if Excel is not used to generate the base numbers, it will almost certainly play a part in the last mile of reporting information to regulators or aggregating data from a number of different sources for presentation to executives and senior decision makers.

Simplifying and Governing these assets appropriately, is key to maintaining accuracy, correctness and adherence to the principles of BCBS 239.

“Fast is fine, but accuracy is everything.” (Wyatt Earp)

If you would like more information on the Schematiq platform and how it can be used to improve governance of Excel and adherence to the principles of BCBS 239, then please use the Contact page to request a copy of our whitepaper.

Tags: Excel Asset Manager Schematiq