Why it’s time you give up spreadsheets in the ALLL calculation
It’s time for a foundational shift in the resources bankers use to calculate the ALLL. Years ago, using spreadsheets was by far and away the preferred option – primarily because it was the only option. To those in the early 20th century, the typewriter was the most efficient way to write letters, and the Model T was the latest and greatest in transportation.
Although I would be highly esteemed by Ford enthusiasts if I drove to work in my Model T, these resources do not help me execute my tasks in the most efficient way possible. My modern car does a vastly better job getting me to work quickly and with the least amount of risk, and my recently-new computer allows me to execute the various tasks required by my job in a much more productive and wholesome fashion than a typewriter ever could.
Just as there have been improvements in transportation and word processing, there have also been improvements in technology for the ALLL. We will delve into those improvements, but let’s start with a few, high-level concerns bankers have with spreadsheets.
Conducted in May of 2013, this poll from one of Sageworks’ webinars, Concerns Examiners Have About Your Spreadsheets and How to Respond, shows what bankers see as the fallibility of using spreadsheets in the ALLL process (this poll result can be found at 19:52). One error can make the whole calculation inaccurate, various users in the calculation may perform tasks differently, skewing the results, and the data is not secure or aggregated through time. This illustrates a few points where spreadsheets can be inherently more risky than other methods, but the poll falls short of capturing why the process of using spreadsheets is becoming antiquated.
Excel spreadsheets can be used to perform the ALLL, and some bankers who are very savvy with Excel can develop a fairly sophisticated model for the allowance calculation. I would also argue that an extremely skilled engineer could contrive a blueprint for a bridge using a calculator designed for simple arithmetic. That same engineer, however, could do much more if you provided him tools designed specifically for building bridges, and the fact of the matter remains that Microsoft Excel as a tool was not created for the allowance calculation.
As the ALLL has evolved, regulators have put increasingly more significance on the comprehensiveness and soundness of reserve methodologies. Their demands, coupled with the often times complex nature of the ALLL calculation itself, can prove to be a massive burden on CFOs, CCOs and others trying to defend a methodology that consists of dozens of cross-referenced tabs and data pulled from multiple, disparate sources.
Fortunately, financial professionals are not subject to using a generic tool that was not designed for the task they must undertake. Other resources – superior resources – that are designed for the sole purpose of calculating the ALLL are available to them.
Those that have taken on these solutions reap a slew of benefits, from the data aggregation process necessary to begin the calculation all the way to backtesting the results to validate the accuracy of their allowance methodology. When using spreadsheets, the bulk of the time spent managing the ALLL focuses on the actual calculation: readying the necessary data, performing the calculation, scrambling to provide documentation for qualitative factors and praying that examiners do not uncover an error that renders the entire process invalid.
Using an automated solution turns that process on its head. Many of these solutions integrate with core systems, so the data is readily available. Instead of manually performing the calculation using spreadsheets, it is automated.
What’s more, the calculation itself is superior. Perhaps you want to segment the data by call code, risk rating or days past due, or you’d like to see the impact of changing one variable in the calculation, or want to see how utilizing migration analysis changes your overall reserve levels. Not only can this be done inside these solutions, it can be done in a matter of minutes. Employing an automated ALLL solution allows bankers to take control of the process instead of being at the mercy of it. Institutions can spend their time analyzing the results of the calculation and deploy that insight into other areas of the bank as opposed to simply scrambling to piece the calculation together.
These solutions can make the allowance more robust today and better prepared for regulatory changes. For one, they archive loan-level detail which will be required by impending regulatory changes such as FASB’s CECL model. They also “institutionalize” the process, so if an employee leaves that role, he/she does not leave behind an ALLL Black Box. This goes back to the purpose of the tool. Because these tools are designed to be a resource for the allowance, they cover every component of the calculation. Excel, albeit a fabulous tool for myriads of purposes, is simply not designed for the reserve calculation and falls short of providing the most robust methodology possible.
The Model T may get me to work, but it is not up to today’s safety requirements, nor does it allow me to do 80 MPH on the freeway with air conditioning, cruise control, power steering and GPS. Archiving loan-level detail, performing migration analysis in a click, segmenting the portfolio in a multitude of ways, incorporating what-if scenarios and completing the calculation in a fraction of the time is truly only a short list of the benefits of using an automated solution.
When the soundness of an institution’s ALLL and the ability to defend it to regulators lies in the mix, bankers must act. If the village is relying on the bounty of your hunt, do you opt for the boomerang, or the rifle?
