Are Spreadsheets Lurking in Your Infrastructure?

The media frenzy over an error in a spreadsheet published by Reinhart/Rogoff may have touched off a timely macroeconomic debate, but bank management teams should not let this moment pass without correcting vulnerabilities within their own operations.

Thirty years ago VisiCalc set off a wave of PC based financial modeling that led to Lotus123 and the modern era of MS Excel and Google Docs. Spreadsheets liberated the accountant, financial analyst, and operations manager from the shortcomings and long enhancement queues of the company’s centralized mainframe. Now, in an era of emerging Big Data techniques, need we worry about those pesky spreadsheets?

Yes.

It’s not necessary to implement a general ban on spreadsheets, though this is done for reasons of expense reduction, internal controls, and operational efficiency in some call center locations. Instead, it’s wise to recognize what the spreadsheet is good for and to replace the spreadsheet once it has exceeded its usefulness within a risk reward framework. Spreadsheets provide immediate workarounds to shortcomings in on-line transaction processing (OLTP) systems as any employee believes they can record a transaction undertaken with a client.

Spreadsheets also provide immediate relief from shortcomings in the on-line analytical processing (OLAP) where fixed report outputs are deemed insufficient. Spreadsheets provide an empowering offline substitute for IT and database systems. In particular, spreadsheets are useful for:

-- One-off modeling and reporting and use of Pivot Tables.

-- Collaboration and iterative team based improvements without the need for IT resources.

-- A front end graphical interface to SQL databases.

-- Building a working prototype of what the user really needs IT to provide for them in a scalable secure manner.

However, danger lurks when spreadsheets become overused and involved in routinized and frequent work processes. Any repeated re-use beyond the bank’s annual planning process should be suspect. Examples include:

-- The system only tracks summary level information with details in the associated spreadsheet – e.g. collateral tracking.

-- The system is unable to compute pricing, volume discounts, or interest rate resets for large clients.

-- The system can’t implement contractual terms and conditions which are then computed in spreadsheets.

-- Critical business functions (such as debt covenant compliance) are monitored offline with spreadsheets.

-- Top side adjustments are made with spreadsheets as the backup for manual Journal Entries (e.g. portfolio level reserves).

-- Using spreadsheets as a replacement for core financial processes. Examples are when multiple BU’s, GL’s, and countries operate autonomously in their own systems environments causing manual performance of step consolidations, FX computations, inter-company and cash reconciliations.

Multiple perspectives should be used to triage your inventory of spreadsheets. Spreadsheets at the top of each of these lists should be ranked considering probability of loss and severity of loss. This should be led at an overall bank-wide level by the operating committee, its delegates, or the enterprise risk committee. Triage perspectives include ranking spreadsheets by:

-- Financial materiality – Sarbanes Oxley.

-- Disaster recovery – impeding recovery time objectives or business continuity.

-- Personal Identifying Information (PII) triggering Gramm-Leach-Bliley, HIPAA exposure, or mandated notification of customers should a breech occur (California Data Privacy Act and related statutes).

-- Operational inefficiency – by costs and hours of rekeying.

-- Operational losses due to errors, missed deadlines, revenue leakage, fraud, compliance fines. -- Also consider exposures created by MS Access and PC based databases, the three dimensional equivalents to spreadsheets.

Practical steps banks can take to remediate spreadsheets include:

-- Communicate clearly to IT – the project charter is to eliminate the following spreadsheet(s). The priority of elimination is as follows based on triage performed above.

-- Add these projects to the enhancement queue of the affected systems once an elimination strategy has been determined.

-- Expand the functionality of existing systems or add new systems including intranet based solutions.

-- Upload unique data found in the spreadsheet into user defined fields (columns) of systems with enhanced reporting from that system or operational data store.

-- Data that requires rekeying today is remediated with a system to system interface.

-- Business logic in the spreadsheet is migrated up to a transactional OLTP system.

-- Reporting from the spreadsheet is migrated to a business intelligence system or OLAP dashboard.

-- Some or most of the spreadsheet model and data is removed in several steps, reducing risk along the way until the spreadsheet is finally eliminated.

-- Surviving spreadsheets are “locked down” and “version controlled” in document management systems – such systems are likely in place for matter management in your legal department, consider expanding their use to bring order to the remnant of the spreadsheet ecosystem.

-- Adjusting entries may be needed when retiring the spreadsheet. Your bank’s internal and external auditors should be informed of your spreadsheet initiative.

Remember that clients, counter-parties, and customers will usually only perform one way Quality Assurance (QA). They can only be expected to notify your employees when an error is in the bank’s favor for immediate correction. Only rarely will they report an error in their favor as it causes them an immediate monetary loss. Thus all the errors in the client’s favor will accumulate over time resulting in losses or unrealized profits to the bank. That is but one danger that that lurks in your bank’s spreadsheets. Let’s use the air cover provided by these recent headlines to clean up our act.

John Nerenberg is a director in the Financial Services IT & Applied Analytics Practice at AlixPartners LLP