Erroneous Excel: Part III

In Part I, we touched on some Excel plusses and minuses, its usefulness and the associated risks. In Part II, we learned why, despite its faults, so many banks continue to rely on Excel for their most critical calculations.

It is clear that the financial industry, business people in general, educators and students are not about to ditch the electronic spreadsheet – no matter the evidence of error rates. So how do we improve the accuracy of Excel results? Our option is one of three: testing, parallel systems and automation.1. Testing – develop a thorough review process for all spreadsheets used to manage your institution. That probably means a documented set of activities to be followed by an individual or group not directly associated with creating the spreadsheets. It also means spreadsheet creators must document their work, including the purpose of the spreadsheet, where each set of data originates, what the outcomes will depict, etc. The requirements on both creation and testing will likely be much more demanding than what currently exists in institutions that rely on Excel for management level financial data.

However, Excel itself renders this option difficult. Following a data trail in the software is impossible without additional documentation. Calculations in cells are easily altered, either intentionally or accidently. As well, testing requires considerable expertise, which means that the individual or group to do the testing is likely already busy with other activities in the institution.

While following this path means electronic spreadsheets could become more reliable, the required sophistication and expense of testing erodes Excel’s value as simpler and less expensive than other tools or approaches.

2. Parallel Systems – employ a parallel system to check against Excel, an additional piece of software or a system specifically developed to check on Excel. The steps required for either can provide necessary documentation and understanding of your current process. Conducting a set of calculations and estimations on a monthly or quarterly basis can become second nature – that in itself can be dangerous – and documenting each step of a process in a way that explains it to someone unfamiliar with the way it is done can reveal errors, or at least where they might be easily made.

A parallel system that employs your specific methodology and can’t be altered except by a software engineer will ensure a much lower error rate. Programmed processes can be locked down, unlike with Excel, which doesn’t require programming skills to make process changes – one of the tool’s selling features but also one of its most dangerous attributes.

It is expensive to run a parallel system. Excel continues to require personnel to conduct manual calculations, gather data and perform other labor-intensive tasks. The parallel system requires the purchase and operation of additional software. Outcomes from the two systems must be compared, and exceptions tracked to determine which system is in error and why. In our experience, parallel systems are most advantageously used in transition, that is, as a bank converts from Excel to a more sophisticated automated solution.

3. Automate – software that automates a technical process requires an initial investment of time. Experts within an institution must be able to explain the methodology, the data that needs to be gathered, the outcomes that are expected and other steps required to commit a process to software.

Of course, there is the cost of the software itself, most often determined by the cost of the research and development required to build it, the time and money savings provided by the tool, and the complexity of the process it will automate. Still, it is an iffy proposition to compare the cost of an existing process to an automated solution. It is difficult to determine the expense in manually estimating something as data intensive as the Allowance for Loan and Lease Losses, for example, or the cost of an Excel-driven mistake.

According to economist Daniel Khannmen’s theory, “What You See Is All There Is,” if all you see is a quarter-end estimation that fits nicely within your expectations, why invest in software or change? We suggest, that ignorance is no longer bliss.

Application software exposes errors and uncovers opportunities for growth, profitability and advancement. Is it infallible? No. Yet the mistakes are much easier to uncover than with Excel. Data sources can be followed to their origin. Methodologies are repeatable, giving much better comparative analysis. Audit trails reveal who altered code and when.

Still loathe to abandon your Excel? A happy medium could be to use an automated tool to gather and crunch data that is then released to your spreadsheets. You might then gainfully employ Excel to conduce “what-if” scenarios.

If you are relying on Excel or another spreadsheet for decision-making, remember that there is only a 12 percent chance that the information you are basing those decisions on is accurate. Would you accept this level of accuracy from any employee or department in your bank?

Sources

Thinking Fast and Slow, Daniel Kahneman; Farrar, Straus and Giroux (2011)