Financial Services professionals love their spreadsheets, Capital Markets and Trading Platform specialists such as Epsilon are no exception, for a myriad of reasons. Consider the flexibility, ease of use, formula build capability, and portability of the perfect spreadsheet. However, spreadsheets are a regulatory target rife with opportunity to assess new fines for non-compliance. This is a daunting problem for management in a day and age where transparency is key to operational efficiency, exposure risk, and avoiding penalties. The simple fact is that anyone can make a spreadsheet but many of the basic skills are outdated and the formats lack consistency in layout or data tagging. This basic truth means that current form spreadsheets are contrary to best transparency practices.

We’ve all seen it, an analyst builds a spreadsheet model at their desk, a trader saves trade information into a spreadsheet instead of in the back office systems, or a college intern builds a spreadsheet that becomes an integral part of information flows within an organization. Add to this, and more often than not, programmers can take output of these spreadsheets and insert the result into back office systems making straightforward audits nearly impossible. This process is unfortunately ingrained into our business due to 40 years of cultural acceptance and bad habits.

While management directs these kinds of files be saved on the shared drives, they more often are saved at the employee’s desk never making it transparent to the organization as a whole, or in part. There is a lack of organizational control and data governance. This practice leads to poor disclosure and poor audit trails. In order to resolve these issues, one needs to investigate the root cause of the problem.

Skill Level: Most people learn the basics of spreadsheet construction without ever following through with skill improvement updates. Only 5% of employees receive additional training.

Standardization: Most organizations fail to have any consistent standards around spreadsheet creation from layouts to storage locations.

Spreadsheet Changes: Formulas can lose their integrity with cutting and pasting of data. If someone fails to update all the spreadsheets then the obvious resulting conclusions will be different.

Data Archives: Crashed hard drives, varied application metadata tagging, spreadsheet change management flaws all contribute to more complex normalization in following downstream systems.

Corporate Culture: Inconsistent process is driven by unclear and unenforced rules driven from the top down. Management will need to build a policy around spreadsheets.

The underlying theme of regulations such as Basel 111 (bcbs 239), Dodd Frank, Sarbanes Oxley, Solvency 2, all require that financial information is transparent and aggregated for easier risk exposure and liquidity analysis. Organizations like the EDM Council are developing metadata standardization to establish a consistency throughout the Financial Services, Banking, and Insurance industries. These standards are intended to afford firms and regulators the ability to “relatively easily” determine risk across organizations business units and silos. However, once the standardization is developed for general business applications, that same metadata and control system must be applied to the thousands of internal independent spreadsheets if the ultimate goal is to build transparency and auditability across all of an organizations functions.

For spreadsheet standardization to evolve, firms must begin the process of saving all the spreadsheet in 1 database location. They then need to analyze all the data fields to make sure they harmonize across all spreadsheets and the corresponding cells have identical meanings. Finally, any formulas exiting within spreadsheets need to be model validated and tested to independently guarantee accuracy and consistency to ensure the formulas meet general industry standards and practices. This process, in and of itself, is highly complicated needing subject matter expertise utilizing hard a to find combination of data and quantitative skills.

Epsilon Consulting Services has deep experience working with normalizing data across different trading platforms and across business silos and is a contributor to EDM Council.