We recently spoke to a bank that was instituting a "no spreadsheets" policy and was systematically rooting out spreadsheets wherever they found them in their processes. That might be a bit extreme - spreadsheets can be quite useful in some situations. But there are many times when banks are relying on spreadsheets to perform tasks beyond their capabilities. In this Purposeful Banker episode we look at spreadsheet shortcomings and the ways they could be harming your bank.
Helpful Links
- 4 Ways Your Pricing Spreadsheet Is Failing You (white paper)
- Why Most Banks Hate Their Pricing Models (blog post)
- Two Painful Lessons Banks Keep Ignoring (blog post)
- How Short-Term Pricing Cures Cause Long-Term Damage (blog post)
- Commercial Loan Pricing Market Updates
Questions? Comments? Email Jim Young at jim.young@q2.com
Transcript:
Jim Young: Hi, and welcome to The Purposeful Banker, the podcast brought to you by PrecisionLender. We discuss the big topics on the minds of today's best bankers. I'm your host, Jim Young, Director of Content, PrecisionLender. I'm joined today by Dallas Wells, our EVP of Strategy.
Today, Dallas and I are going to return to an old friend, or maybe more accurately, an old foe, spreadsheets. We spent quite a bit of time detailing the shortcomings of spreadsheets. Particularly, we had content campaign, I'm sure you all remember, called, can your spreadsheet do this, and I will include the links to that content in the show notes. And, spoiler, the answer was almost always, no, your spreadsheet cannot. But to be fair, a lot of that content was really specifically around using a spreadsheet to do commercial deal pricing and structuring. And today, we want to expand the conversation a bit, about where and when banks use spreadsheets, and where or when that may be a bad idea.
And the inspiration here, is a conversation we recently had with a bank that has a motto for moving forward as, no more spreadsheets. I'm assuming it's like a 2022 campaign of no more spreadsheets, and they are basically targeting every process that contains a spreadsheet, and trying to remove the spreadsheet from the equation. So Dallas, first off, does that approach strike you as a bit extreme or do you nod your head and say, yeah, more banks should be doing that.
Dallas Wells: It sounds slightly extreme. It also sounds to me, a little bit like maybe an exam finding, of they had some really ugly disaster spreadsheets that they are just going to now hunt down all the remaining ones with extreme prejudice. But look, spreadsheets are a powerful, flexible tool, so every bank employee should have access to Excel on their computer for doing quick ad hoc, one-off kind of analysis. It's really powerful and flexible for that and I use it every single day. So this is not hating on Microsoft Excel, but yes, more banks should do this. And I think what they're saying is they are targeting spreadsheets that are a part of a repeatable process.
Jim Young: Okay.
Dallas Wells: Right, so they have something where they're trying to do something the same way every time. They're trying to do it at scale. They're trying to do it with some accuracy and they've got spreadsheets jammed in the middle of there.
Jim Young: Okay.
Dallas Wells: That is probably a bad idea and something that's worth trying to fix. Especially now that there's really good and more flexible than it used to be Enterprise SaaS software out there to solve lots of individual business needs and challenges, that are custom built for that. They're maintained for that, they're well documented, they're accurate, they get tested, all the things that are hard to do with a spreadsheet. There's better solutions now than there was a year ago, five years ago, 10 years ago. This is not like you're choosing between the like, monolith core providers, or you make something in Excel. You got a whole menu of options now that you didn't use to have and so I think that's where conversations like the one this bank is having are starting to come from, is, hey, we don't need all these spreadsheets anymore. They served a purpose, but that's like an old way of doing it and we can be more modern than this now.
Jim Young: Okay, you referenced a couple of terms that we're going to return to here because again, that software out there that you mentioned, which is great, and which obviously we sell as well, is not free. And I'm not saying spreadsheets are, but they're pretty close to it at this point. So if someone's out there saying, I hear you, that's probably better, but I got a certain amount of budget and I've got these spreadsheets, and they've gotten me this far, so let's maybe then tackle this from areas where, if you're using a spreadsheet you're going to struggle. The first one on here you listed is accuracy. So why would a spreadsheet be a problem for accuracy in a process, I guess?
Dallas Wells: Yeah, so look, if you put in two plus two in a spreadsheet, it's going to tell you the answer's four. Right, so that's not what I mean by accuracy. But, they're built by humans and there is no like, testing and validation process for most of these spreadsheets that are a part of these sometimes, really core critical functions at a bank.
I've seen different stats on this, of how many spreadsheets have errors in them. It's almost always, in all of them I can think of, it's more than 80%. It's a big number. Most spreadsheets have mistakes and errors in them. Sometimes they're small and non-critical, and other times they're wildly expensive. I'm guessing in our content, that you mentioned you were going to put links to, I'm sure we've got some examples of those because these can be disastrous and painful.
If you make a bad decision because the number came out wrong, because we got the formula transposed somewhere in the spreadsheet, and we've taken apart hundreds of spreadsheets over the years to kind of try to reverse engineer them and understand the methodology that banks are using for pricing and structuring deals, we always find errors in them. They're always really ugly, and yet, I can understand the pride that they're creators have. They're complex, there's elegant solutions sometimes to some really complicated problems. That doesn't mean that that's the right tool for that particular problem.
So accuracy, if you have to, on a repeated basis, get an accurate calculation out, and for banks this is anywhere where you're doing any sort of risk modeling. And risk modeling, by the way it's described in the regulatory language, is interest rate risk, it's if you're doing credit underwriting, it's if you're doing capital allocation, technically if you're doing pricing. Those are models and those models should not live in Microsoft Excel where they can have mistakes in them, and your math that you got board approved is actually not being carried out correctly in the spreadsheet.
Jim Young: All right, okay, yeah it actually, it's somewhat terrifying to think about if you just happened, because you have a fat thumb, and type an eight instead of a nine or something like that, and then that error keeps getting compounded every time that calculation is made.
So the next one on here is, I feel seems pretty straightforward. You mentioned that spreadsheets struggle when it comes to scale. I'm assuming, well, the first thing that pops into my head here is version control. Is that where you're going with this?
Dallas Wells: Version control is the most likely symptom here. Spreadsheets work when it's my personal spreadsheet, and I go in and make changes and updates to it occasionally, and I've got several that I use for my own personal stuff. Those are easy. I know where the stuff is. I'm the only one in there.
Jim Young: Yeah.
Dallas Wells: Once you get beyond that, and especially as some community banks have a shared spreadsheet that they'll use for making some, even sometimes just using it as a checklist, they're checking things in Excel, mark the column when you're done with it... What happens when two people need to be in there at the same time? And the online versions of spreadsheets, now make that better than it used to be, it's still not perfect, and you still have version control issues all the time, and questions of which is the latest version. Did you get the updated spreadsheet? I made a change and then saved it and you saved over the top. All those sorts of things that really break with just a handful of users. Now take that if you're a large bank and you're trying to do something across a couple thousand people, these tools, they're just not made to function that way. That's not the way they were designed. That's not what they were are intended to do, and they're pretty much impossible to roll out with any kind of scale.
Jim Young: Okay, all right. Next one up here is that spreadsheet struggle when it comes to regular updates. Does this depend on how you define regular or what do you mean here exactly?
Dallas Wells: Well, anything that just has really, a recurring update. So, do you need to put new interest rates in there, every month do you need to add new financial data? Most of the spreadsheets we see are really bad at ingesting data. And so, if something like the shape of that data changes, it used to come all in five columns and now they added an extra column in the data set. Well, Excel's generally not very good at telling that what used to be column four is now column six, or whatever. It's an idiot. It just goes where you point it and there are tools that are better at that. There are also some spreadsheets I've seen that do a pretty good job of managing data, but again, that's not what spreadsheets were really designed to do.
That's what databases are for, and you can query databases and the shape of things can change a little bit, and you still get the same answers back. So anything that takes regular updates, especially regular updates of one of two varieties, either big sizes, so big batches of data going into it, spreadsheets are not going to function well with that and you can't see when they break always, or if something has to be manually updated. I've seen so many really ugly reports where you've got, one person used to update it and now they move to a different job, and so they train their replacement and they don't do it quite exactly the same way, and what used to be formulas now just have hard coded numbers in there, and these things just sort of deteriorate over time because you've got people making manual updates. And that's assuming that there aren't any just like, fat finger keying mistakes, which there inevitably will be.
So, the updates just rarely happen very well. Again, spreadsheets are really good at getting information in there one time, doing an analysis on it, using it to make a decision, and then it's kind of a throwaway, it's a one-off, ad hoc thing. Anything that is recurring, that gets plugged into a repeated process where you do it every day, every week, every quarter, heck even once a year, that's probably not worth trying to keep that living in a spreadsheet. Again, if it's a very high impact decision at all, that's the wrong place for it.
Jim Young: Gotcha, okay, I was actually also kind of thinking about it. You were talking about sort of the updating of the spreadsheet. Part of me was thinking about using a spreadsheet to provide an update, like in the way of, information to your RM's on how they're doing in one particular thing, and that sort of thing.
Dallas Wells: I mean, spreadsheets actually work okay for that. If it's just a way to display data in a little more readable format, you can put it in tables, you can shade columns, you can make charts, it's okay for using it as a display tool. Again, there's better tools for that...
Jim Young: Yeah.
Dallas Wells: ...That are reasonably inexpensive. Throw that sucker in Tableau and you're going to be better off than trying to do it in Excel.
Jim Young: Yeah.
Dallas Wells: But I don't have a real problem with dropping things into a spreadsheet just to quickly and simply pretty them up a little bit, and just about anybody's capable of doing that. Yeah, but what I was talking about is a spreadsheet that is a dynamic spreadsheet that's going to need updates over time. [crosstalk 00:11:02] Those are not going to hold up very well.
Jim Young: Okay, your next one here is usability, and I'm curious what you mean here. Again, because I'm a writer by trade so I naturally kind of stink at spreadsheets, but I know people that just, I mean, they just love, love, love a good pivot table, and it's just second nature to them how they use this. And so, what do you mean by, when you're saying that spreadsheets have a usability issue?
Dallas Wells: Well, again, Microsoft Excel is a very usable piece of software.
Jim Young: Yeah.
Dallas Wells: Just about anybody can get in there and fiddle around with it and figure out some of the basics. The problem is, is when one person creates a spreadsheet and then sends it to someone else, and they're like, hey put your stuff in here, or make an update to this, or check out this analysis that I did. Following someone else's logic through a spreadsheet is torturous. It is really painful.
I'll pick on myself here rather than one of my coworkers. So, I made this spreadsheet where I was trying to figure out how PrecisionLender clients were faring versus non PrecisionLender clients. So, we just pulled in publicly available information about banks from the FDIC database. Its standard set of fields, you can just drop the reports into a spreadsheet and then we could just flag them, are they PrecisionLender clients or not, and see the performance difference. So I made this thing, I made it to where it made sense to me and then we plugged it into a process and we had people start using it to show to prospects, of like, hey, here's how our clients do on this metric and that metric, and so people kept asking, hey, can you add this thing, and can you show deposits in there, and can you... And so it grew over time and I just kept, kind of jamming things in there in a way that made sense to me.
Well, I think you can see where this is headed. Nobody else could follow what the heck I was doing there. It made sense in my smushy brain how everything worked, but for somebody else to get in there and follow it was impossible. And so, they would spend two days trying to figure out something. I could have made the change in five minutes, and they were wrestling with this God awful monstrosity that had grown over a couple years of changes and additions, and updates. That's where spreadsheets get ugly. And again, I'm personally guilty of it. I see
where this comes from.
But, it's not the usability of the software itself, it's the fact that somebody's making, a lot of times, a tool. Hey, I made this sheet for you to calculate your hours, even something as simple as that, and people are like, well, do I put in how many hours I worked, or do I put in the time that I started and the time that I get off, and what if I put the date in the wrong format, and it's like they're not as straightforward as whoever made it thinks it's going to be.
So again, we've seen a bunch of these where somebody in the finance group makes a spreadsheet that makes perfect sense to them and then they send it out to the 200 relationship managers at the bank to try to structure deals with it and they're like, I don't follow this. I don't know where I'm supposed to put things. What's the difference between the red column, and the blue column, and the green column? Which one am I supposed to put numbers in? And if you don't do it right, the whole thing breaks. So, usability is a real issue. No matter how elegant of a spreadsheet you think you made, it's not as good as you think. It's just not, usually.
Jim Young: So I'm glad you owned up to that one. Is that the one that when I try to open it up, it crashes all my other systems?
Dallas Wells: Yeah, we actually have very few laptops in the company that can now handle that thing. It's become like a sentient being at this point.
Jim Young: But I do know what you mean, and I have run across this too, is like once somebody has created something and it's got this process, is dependent on that thing, and dependent on that, and you say, well actually, I wanted to do this, a lot of times it's like you've got to completely undo, find that sort of thing, completely undo it, go back to this point C out of, and you're already at point Z, and then rebuild it back out, to the way you wanted to do it. It's a bit like trying to feed out a hose that's got to be a knot in it and you've got to completely undo the whole thing before you...
Dallas Wells: Exactly, all the way to the beginning, yeah.
Jim Young: Yeah, all right, and that actually kind of segues a little bit into our last one of these, which is audit trails and proper documentation. So I'll just let you take that one away. It seems like part of what we were just discussing would be part of it. If you can't figure out how somebody built the spreadsheet and you want to change it, I would think that would be a big problem when you're trying to audit things.
Dallas Wells: Yeah, I mean, this is a model risk management thing. It's a regulatory issue. Again, there's guidance out there on how to handle models and their definition of what a model is, is going to be really wide ranging. It's going to surprise you, the things that you use that they're going to consider models.
One of the small community banks from the early days of my career, we had a spreadsheet that we would create to show to the board when we would recommend quarterly dividends. And we would say, here's the capital level coming into the end of the quarter, here's our suggested dividend, this is what our capital ratios will all be afterwards to show that we were all still in compliance. It was, I don't know, six rows of numbers, really straightforward.
Our examiners came in and said, that's a capital model. You've got to do all this documentation and does this thing ever get changed? And we're like, not very often. Okay, well, how do we know that? Who would make a change? Where would we see that? Where would it be signed off on? Who would oversee the change? Who would check that it was done correctly? Those are things that, again, a purpose built piece of software, now does that one need a purpose built piece of software? Probably not, but again, you just need to be aware of like, clearly they weren't picking on the spreadsheet. They were picking on our governance for that, and the fact that Dallas made this spreadsheet and then showed it to the board, and they made a capital distribution decision based on my spreadsheet. Somebody else should probably eyeball that before that money actually goes out the door, was what they were saying, and that's a fair criticism.
So we had to create some audit trails and some documentation of that, so where does each number come from? Which cells can change, which ones don't? Make it all very transparent and probably quintuple the time we spent on it, but that's what they asked for. And that was for a super simple one. When it gets really complicated, when you're doing, again, risk modeling of some kind, who changed cell C 38 on this tab that's 38 tabs in, when did they change it and who checked it? Well, those are audit logs that you can find in other pieces of software, good luck finding that in a spreadsheet and being able to properly document it. So those sorts of controls, again, how complex is it? What's the magnitude of the decisions you're making with it? You got to kind of weigh that risk and reward.
So, no more spreadsheets. I think that's a great slogan. I doubt they're able to really pull it off. You'll be amazed at how many spreadsheets lurk within your walls, but it's the right mentality to, every time you see a spreadsheet say, is this the right tool for this? Is this okay that we're doing this this way? And just assume that it has an error in it. So just, when you see a spreadsheet, say, I bet this is wrong. How painful would that be if it's really wrong? You should look at all spreadsheets through that lens and then I think that'll help you find the ones that are problematic.
Jim Young: Yeah, all right, well great. Well, Dallas, thanks again, that will do it for this week's show.
Dallas Wells: You bet, thanks, Jim.
Jim Young: And thanks again so much for listening. And now for a few friendly reminders. If you want to listen to more podcasts, check out more of our content, you can visit the resource page, PrecisionLender.com, or head over to our homepage to learn more about the company behind the content. If you like what you've been hearing, make sure to subscribe to the feed in Apple Podcasts, Google Play, or Stitcher. We love to get ratings and feedback on any of those platforms. Until next time, this is Jim Young for Dallas Wells. You've been listening to The Purposeful Banker.
About the Author
Follow on Linkedin More Content by Jim Young
