News & Commentary

10:23 AM
John Nerenberg, AlixPartners
John Nerenberg, AlixPartners

Are Spreadsheets Lurking in Your Infrastructure?

Bank management teams should focus on correcting vulnerabilities within their own operations.

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?


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

Comment  | 
Print  | 
More Insights
Newest First  |  Oldest First  |  Threaded View
Register for Dark Reading Newsletters
White Papers
Cartoon Contest
Write a Caption, Win a Starbucks Card! Click Here
Latest Comment: This is a secure windows pc.
Current Issue
Security Operations and IT Operations: Finding the Path to Collaboration
A wide gulf has emerged between SOC and NOC teams that's keeping both of them from assuring the confidentiality, integrity, and availability of IT systems. Here's how experts think it should be bridged.
Flash Poll
New Best Practices for Secure App Development
New Best Practices for Secure App Development
The transition from DevOps to SecDevOps is combining with the move toward cloud computing to create new challenges - and new opportunities - for the information security team. Download this report, to learn about the new best practices for secure application development.
Twitter Feed
Dark Reading - Bug Report
Bug Report
Enterprise Vulnerabilities
From DHS/US-CERT's National Vulnerability Database
Published: 2017-05-09
NScript in mpengine in Microsoft Malware Protection Engine with Engine Version before 1.1.13704.0, as used in Windows Defender and other products, allows remote attackers to execute arbitrary code or cause a denial of service (type confusion and application crash) via crafted JavaScript code within ...

Published: 2017-05-08
unixsocket.c in lxterminal through 0.3.0 insecurely uses /tmp for a socket file, allowing a local user to cause a denial of service (preventing terminal launch), or possibly have other impact (bypassing terminal access control).

Published: 2017-05-08
A privilege escalation vulnerability in Brocade Fibre Channel SAN products running Brocade Fabric OS (FOS) releases earlier than v7.4.1d and v8.0.1b could allow an authenticated attacker to elevate the privileges of user accounts accessing the system via command line interface. With affected version...

Published: 2017-05-08
Improper checks for unusual or exceptional conditions in Brocade NetIron 05.8.00 and later releases up to and including 06.1.00, when the Management Module is continuously scanned on port 22, may allow attackers to cause a denial of service (crash and reload) of the management module.

Published: 2017-05-08
Nextcloud Server before 11.0.3 is vulnerable to an inadequate escaping leading to a XSS vulnerability in the search module. To be exploitable a user has to write or paste malicious content into the search dialogue.

Dark Reading Radio
Archived Dark Reading Radio
In past years, security researchers have discovered ways to hack cars, medical devices, automated teller machines, and many other targets. Dark Reading Executive Editor Kelly Jackson Higgins hosts researcher Samy Kamkar and Levi Gundert, vice president of threat intelligence at Recorded Future, to discuss some of 2016's most unusual and creative hacks by white hats, and what these new vulnerabilities might mean for the coming year.