Error-Free Work – Is It Time to Move Past the Spreadsheet?

What do we do with all of these spreadsheets?

The above question is being asked by financial managers at banks and credit unions as the implementation of the FASB’s current expected credit loss model (CECL) approaches. So, where did the use of all of these spreadsheets(and the dependence on them) come from?

Over the past 40 years, computation tools used by professionals in financial institutions have evolved from pencils, erasers and 20 pound electrical calculators to robust, web-based software models that nearly automate entire processes. Somewhere in the middle of that wide spectrum is Microsoft’s Excel. Excel was introduced to the market in 1987 and soon became the default spreadsheet tool.

Excel’s flexibility allowed professionals in various industries to build spreadsheet models to fit their specific needs. And, as models increased in complexity professionals became more dependent on these models, having invested considerable time in the development of calculations and formatting.

Yet, the flexibility afforded by spreadsheets also has a downside; specifically, as more individuals participate in the manipulation of the model and as formulas become more complex they are prone to have more errors. The increasing dependency that financial institutions have on spreadsheets has drawn attention to the spreadsheets penchant for error or misuse.

“Spreadsheet construction is, in reality, a software coding process, and even experienced software programmers produce a 3-5 percent error rate,” stated Darrell D. Dorrell, CPA with financialforensics.com. “Surprisingly, error detection skill precipitously declines after the fact. That is, while spreadsheet errors can be caught during construction, they are less likely to be caught after the spreadsheet is completed.” For institutions that have been using the same ALLL spreadsheet for years – a common occurrence – the risk of error is magnified.

A 2008 academic study, What We Know About Spreadsheet Errors by Raymond Panko, PhD, discovered that spreadsheet developers understand that spreadsheet composition is often informal and that few organizations implement policy statements and gate-keeping criteria to ensure the accuracy of the models.

“…there has long been ample evidence that errors in spreadsheets are a pandemic. Spreadsheets, even after careful development, contain errors in one percent or more of all formula cells,” stated Panko. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors. Even significant errors may go undetected because formal testing in spreadsheet development is rare and because even serious errors may not be apparent.”

It is critical for financial institutions to prepare for the effects that CECL will bring to their current spreadsheet based model. First, the FASB’s CECL model will require a lot more data as the focus shifts to loan-level detail to inform reserve calculation estimates. Second, the methodology choices and subsequent calculations that produce loss rate figures will be more cumbersome and complex if built in a spreadsheet model. With more complexity likely comes a higher risk of error, which puts the institution at risk for exams and audits.