After the passage of the Sarbanes-Oxley Act in 2002, many securities firms implemented financial controls to ensure the accuracy of financial statements. The new act required public companies to strengthen accounting controls and social trading oversight.
Spreadsheets are difficult to control and audit. Many companies accelerated their migration to enterprise-quality databases and resource management systems that could be audited.
But spreadsheets still haven’t gone away-and continue to pose compliance problems for securities firms.
One of them is Securities America, one of the nation’s largest independent general securities broker-dealers.
“We’re in a heavily regulated industry, audited by multiple state and federal agencies,” said David Vaughan, Jr., the firm’s first vice president and controller. “Having accurate financial statements is of the utmost importance.”
Unfortunately, Securities America’s use of spreadsheets in its accounting and human resources department wasn’t making it easy to achieve this goal.
At the end of each month, preparers filled out spreadsheets with the month’s financial accounts, printed them out, signed them, and handed them on for review by supervisors. With nearly 1,000 financial accounts that needed tracking, that was a lot of printing out and signing off by managers, he said.
“Once that was completed, we would scan it [the signed spreadsheet] into a PDF, or store it a box somewhere,” Vaughan said.
At audit time, the hard-copy signed spreadsheets had to be tracked down and photocopied for the auditors, he said. Even finding stored PDF files was time-consuming, since employees would need to go to “where we stored it on our network drive and page through a lot of documentation to find it.”
Spreadsheets can also be easily fudged, he said. There is no way to tell, for example, that the page was signed and dated on that exact date and not later. Spreadsheets can be misplaced or misfiled or deleted. Staying on top of spreadsheets costs time, and makes audits longer and more expensive.
And it’s not just Securities America-spreadsheets are endemic throughout the financial services industry, experts say, and the recent financial turmoil has only increased dependence on spreadsheets instead of hastening moves to databases where information can be input by anyone in a standardized format. And retrieved in that required form by other authorized staff.
A year ago, Securities America decided to try to round up its spreadsheets into a workflow management and account reconciliation system from hosted software provider BlackLine Systems, based in Los Angeles. Other BlackLine customers include Invesco Ltd., Schroder Investment Management , and Bank of New York Mellon Corp.
“They provide an online environment, controlled by user, with a WORM-compliant database,” he said.
WORM is the acronym for “write once, read many” technology required by regulators for some electronic document storage. “Whenever we attach a document to it, it cannot be altered in the future,” he said.
The company moved its financial review and reconciliation process to the platform.
Today, financial information for the monthly reports is pulled automatically from the firm’s Great Plains Dynamics accounting system.
“It allows us to take a faster, proactive approach during the closing process,” he said.
Financial reports are closed each month, each quarter and each year. Once closed, the numbers can’t be modified, unless, for instance, Securities America decided to issue a financial restatement.
In addition, staff can add attachments to a key spreadsheet, such as PDFs, Word documents, images, and other information that is needed to provide documentary evidence for an account. Previously, employees tracked this information manually, with binders of paperwork, and faxed the documents to each other when needed.
“We just went through an end-of-year audit in February, and the time-savings were just incredible,” he said, though specific numbers weren’t yet available, he said.
In addition, the company needed to make a lot fewer photocopies as a result. Vaughan said that Securities America hasn’t yet calculated the ROI for the platform, but the $10,000 software has already enabled the company to avoid hiring a $30,000 staffer to deal with the growing paperwork.
Today, there are 17 staff members using the BlackLine product, mostly in accounting and human resources, to control company financial spreadsheets.
But financial audits were just the beginning, Vaughan added.
“Being in the securities industry, there are a lot of other filings we have to do, such as net capital we have to report monthly to FINRA and other regulatory bodies,” he said. That’s still happening in Excel, he said.
“We have a template in Excel I inherited for calculating the net capital and it has worked well,” he said. “It seemed easier to just continue with what has been working. The time to change to a new platform may outweigh the benefit of getting away from Excel for the calculation.”
Meanwhile, he added, the BlackLine system allows him to save the final net capital calculations in the “write once, read many” form that regulators require-meaning that the spreadsheet cannot be changed once it is saved into the system.
In an ideal world, every function in an organization would take place through a centralized database. In practice however, there are always gaps.
“Most people aren’t as comfortable with databases,” Vaughan said. “They are much more comfortable with the features of Excel. It is much easier to learn, easier to visualize.”
As a result, he said, spreadsheets are used throughout the company. In his department, they are used for internal data reporting and calculations, and to track bonus payments to representatives and advisors.
“When you’re conveying something that has mathematical components to it, the spreadsheet is the number one tool you think of,” he said. “There are many spreadsheets that are created on an ad-hoc basis. We don’t know all the spreadsheets that are created inside the company.”
Creating a database that can be used by all hands instead of a personal spreadsheet can be time-consuming and costly. Creating up a database for a particular report requires programming and technology department support. Unique features or data requirements require custom coding. And, once done, the database needs debugging.
In the meantime, spreadsheets aren’t just an auditing obstacle for financial reporting, however-they can also pose operational risks.
For example, in late 2008, Lehman Brothers declared bankruptcy and Barclays Capital agreed to acquire the firm’s assets for $1.35 billion-once some of the more toxic elements were excluded.
Those 179 unwanted assets were so marked in a 1,000-row spreadsheet , and hidden from view-but when the spreadsheet was converted to a PDF for the court filing, these hidden rows of contracts that Barclays did not intent do acquire were accidentally exposed. The total financial cost of the mistake was not disclosed. Barclays was able to go back to court to get the error reversed two months later.
“The thing is that spreadsheets are no more accurate for errors than programming,” said Ray Panko, a technology professor at the University of Hawaii and an expert on the compliance implications of spreadsheets. “The one big difference is that with spreadsheets, they don’t do the extensive testing that they do in software development. At Microsoft, they have one tester for every developer and spend 40 percent of the software development on testing.”
And Microsoft programs still have bugs in them, he added.
“It’s even worse with spreadsheets because if you write a program incorrectly, it will crash,” Panko said. “With a spreadsheet, it won’t crash-it will just give the wrong answer.”
A typical business-critical spreadsheet may have hundreds-even thousands-of embedded formulas, he said. “The odds are good that you have an error. I’ve never audited a spreadsheet that didn’t have an error.”
Overall, he reported in a 2008 paper that 88 percent of audited spreadsheets have mistakes.
The recent financial crisis has exacerbated the problems that spreadsheets cause for the financial industry, said Michael Juergens, principal in the audit enterprise risk services group at Deloitte & Touche LLP.
“Given the state of the economy, there are two things happening,” he said. “One is the reduction of people, which means that a lot more people are doing multiple jobs, using workarounds for old processes. This has led to greater use of spreadsheets and more problems with existing spreadsheets, because the people who created them are laid off.”
The second problem is that the crisis has created a lot of turmoil in the industry. When business needs change, it takes time to adapt large-scale enterprise resource management systems, accounting systems, risk management tools and other database-driven platforms. Spreadsheets can come in to fill the gap, Juergens said.
Meanwhile, financial firms have cut back on their technology spending. Which means it will take longer for automation and databases to come in and replace these spreadsheets. According to Boston-based research firm Celent, global information technology spending by financial services institutions fell 2.5 percent in 2009.
How to Fix a Spreadsheet
Finding rows of information that you do not intend to include in a spreadsheet—the Barclays problem (see main story)—can be achieved with smart software, said Diane Robinette, vice president of marketing at Valley Inception, a Saratoga, Calif.-based technology investment and development firm and owner of Incisive, Inc., makes of the Xcellerator spreadsheet management software.
Xcellerator works inside Excel to identify common formula errors or other mistakes—such as the Barclays’ error, in which hidden rows were exported to a PDF file.
Another common error is when a formula is used to sum up a list of numbers, and new rows are later added that aren’t part of the sum.
This technology allows firms to set best practices for spreadsheet use, and then follow up to ensure that those policies are being followed, she said.
According to TowerGroup senior research director Rodney Nelsestuen, there are a number of vendors trying to help companies come to grips with their spreadsheets.
ClusterSeven, Cimcon Software, Prodiance and Finsbury Solutions help firms track down spreadsheets, rank them on importance or sensitivity of data, and save copies for archives. These products run in the background, and don’t interfere with computer user’s experience.
Another approach is to change the Excel interface, adding management or control functionality, he said. For example, users can see an extra menu that allows them to check a spreadsheet for common errors, or to highlight areas that need attention.
Products in this category include Risk Integrated’s Risk Integrated Enterprise Spreadsheet Platform (based in Cold Spring, New York), eFrame from SecondFloor (based in the Netherlands), Qtier-Rapor from Qtier Business Software (based in the U.K.), and the ExcelSafe product from Ofni Systems (based in Raleigh, NC )
Other vendors offer audit and control platforms for spreadsheets, he says. These products include ExpressSpreadsheet from Devexpress (based in Glendale, Calif.) and Spreadsheet Advantage (based in Australia), from the company of the same name. These platforms allow managers, controllers or auditors to scan existing spreadsheets for errors, and to analyze relationships between spreadsheets.
Companies can also use features built into Microsoft Excel and Microsoft SharePoint, he adds. For example, companies can centralize all spreadsheets onto servers, making it impossible for users to have individual copies of spreadsheets on their own machines.
The challenge, said Nelsestuen, is getting users to give up the spreadsheets they have locally on their own hard drives in favor of a centralized, managed spreadsheet repository.
Even if a firm were able to enforce such a policy, users would be dissatisfied and may see a decline in productivity, he said.
A centralized SharePoint repository allows firms to archive copies of spreadsheets and to track changes from a central location, but doesn’t provide the audit trails and other management and analysis tools. It’s an incomplete solution, he said. ?
Spread Sheet Best Practices
Guidelines for managing spreadsheets:
1. Identify the population of spreadsheets for review
2. Create a spreadsheet inventory
3. Rank each spreadsheet’s risk level
4. Develop a baseline for each spreadsheet
5. Evaluate policies and procedures for spreadsheet use
6. Review controls that protect spreadsheet baselines
Source: Deloitte & Touche