Accountants and bookkeepers working with law firms often discover attorneys keeping private spreadsheets for record-keeping. Learn how to audit these spreadsheets for errors like merged cells and inconsistent formulas, then migrate the data to practice-management systems like Clio and QuickBooks to ensure compliance and scalability.
Chapters
This week's video and accompanying article is about that side spreadsheet. So if you're new working with attorneys, you're going to find that attorneys tend to have a spreadsheet somewhere. It's a secret spreadsheet. You probably aren't aware that they have it, and you may go through all the action of cleaning up the records. Maybe the trust wasn't looking right. And then when you present the records to them, they'll say, I had the spreadsheet that maybe that might have helped you along the way. And then you find out too late that they had some information information that might have helped. This is something that you want to ask in the discovery call. So when you sit with the attorney and have the conversation, somewhere in that conversation you want to say, do you have any spreadsheets that will help me figure out this work and rebalance your trust, or help me understand your compensation model? That's usually the two places where they use spreadsheets, although I've seen it used in a lot of other scenarios. But you have to be the guy that kind of gently nudges the client to let you know what they're using, maybe Google Sheet, what they're using. It's usually Excel. So ask them and find out because maybe they have Clio and they've got all their trust outlined in Clio, but they're not comfortable with it because they had a spreadsheet for years. So they continue on with this redundant work of having the side spreadsheet. So having that information at your fingertips might very well help you in the scope process and also in the process of doing the work itself. Okay. So how do we start? So when we're working with an attorney, you wanna start with empathy. You want to acknowledge that this spreadsheet is their comfort zone. It's their ultimate comfort zone. They've had it from when they first started their practice and they're not really all that ready to give it up. So attorneys are generally, as a personality type, they're wired for control. If you think about it, billable hours that they work in, that kind of demands it. So that side spreadsheet is like an extension of their briefcase. So don't lead. If you get the spreadsheet and you take a look at it, start to look at the formulas and start looking at— just click the sum or the total, and then look at all the things that are included in that. A lot of times it's missing top lines or it's missing bottom lines, and you're like, okay, so you've been calculating these compensation bonuses without including some of the lines, or you're calculating this, but the amount that you're using as a baseline doesn't tie back to the books. So that's where you're looking at when you're starting to look at the spreadsheets, or maybe the trust spreadsheet that they gave you isn't correct either. Sometimes you can get good historical data out of it too, and the story may be told there, and it can definitely assist you in cleaning up the trust. But don't start your conversation with, hey, Mr. Mrs. Attorney, your Excel is broken. That'll hurt their feelings because this is truly some document that they've been carrying for maybe 5, 10, 15 years along with them. Mm-hmm. Just validate their intent behind the spreadsheet. I understand that you have this spreadsheet for XYZ, and then in your first consult with them, you might say to them, I totally get why you want this quick sheet, but when you start to juggle all these disbursements, it quickly grows out of hand. I've seen interesting ways attorneys will actually calculate and track their trust with these spreadsheets. They'll have different pages for each client. I've seen different tabs. I've seen multiple methods. So understand that it's flexible, but it's also familiar to them. So as you start to push them into the dataset of working inside like a Clio, you have to let them know that this is all being validated back with QuickBooks. It's integrated with QuickBooks and it's matching the bank. So sometimes as their caseload grows, that having that side spreadsheet can really work against them. It becomes that annoying little thing that they have to still copy and do. It's kind of like the old car that guzzles gas, same situation. So let them know without pointing fingers that you've seen this before, that's why you asked about the spreadsheet. And then recommend to them, ask them when they start doing this work in the spreadsheet, are they doing it weekends, like watching football and they're doing this work, or perhaps they're doing it at 10:30 at night when they're really tired, and wouldn't it be nice to not have to do this? We'll handle this for you. Give them that comforting, comforting, message that you'll handle this for them without the spreadsheet and let them know that you have other ways of making this work. And then start to take a look at the spreadsheet. Like I said, when it comes to the compensation models, look to see where they're getting their data from, especially if it's a bonus out where they're looking at the net income and they're dividing it out. Are those records correct? And are they picking the right line item off of the sheet? Because usually that's not a simple net profit divide by 5 or whatever. It's usually more complex than that. That's why they have the spreadsheet. They might start to deduct and different scenarios happen along the years of being in practice as they add partners. So know that if you start to look at it, you might wanna look to see, and there, this is one of the things I often see is they're in a hurry to add in. Some money comes in the very last day of the month and they're in a hurry, but they wanna bonus out and the money hasn't really hit the bank, but they're putting it in. So you wanna make sure that you're cognizant of that. And then if you find, take a look to see, is there any merged cells? If it's in Google, it's even better cuz you can really take a high level view. Look at inconsistent data formats. Look and see if they've color-coded columns. Really get to take a look at what they've been doing. If a paralegal's been working on that spreadsheet, take a look at how they've been doing it. Maybe look and see if there's the right client costs around there, if they're actually doing little workups on each client. Demonstrate that you can handle this chaos hands-on. Uh, it's really important to do that. If you have Clio, you're gonna have an audit log. That's great, but you really wanna demonstrate that you have the authority, that you know how to work in Excel or Google Sheets. And then let them know that the way they're doing it isn't scalable. It's just not. So they started out with maybe 50 clients and now they have 2,000, and it's definitely not something you wanna be handling in a spreadsheet. It's just too manual. And this is really where they need this software. Also with that, you want to realize that attorneys aren't numbers people. They're people that live with words. And let them know that you're the guardian of— guardians of their ethics and their efficiency. Lean into how they're using that spreadsheet. And let them know that it's going to protect their license to have everything tied back to the books, tied back to the bank, that 3-way bank reconciliation. And once you frame it around that way, you're going to find that your client is really going to be happier with knowing that he's got a professional doing the books. And ultimately, that is the most important piece, the most important message to convey to your potential or your current clients. If you have any questions, please give me a call— give me a call— reach out to me on social media. Also, you can reach out anywhere if you want to join our Law Lab. We have a bunch of amazing people in the Law Lab. Come on in, we'll have some great conversations around that side spreadsheet. And until next week, we'll see you then. Bye now. Bye.
