Smart Accounting Firms Are Ditching Spreadsheets for This Tool

Hey, this is Isaac from Upstart.co, and in this video, I'll show you why so many modern accounting firms are leaving spreadsheets behind for many processes. So if you're constantly battling at the moment with like broken formulas for a particular spreadsheet, manual lookups, or honestly just fragile spreadsheets that break, I'll show you a modern system that still feels like a spreadsheet, but that can automate a lot of your grunt work and be really the foundation for internal tools of applications. That later on improve the efficiency of your firm. And all of this without needing to be a developer. Let's get into it. First, let's quickly talk about why and when you might consider upgrading from a spreadsheet to a tool like this. The reason number one, it's when you have multiple versions or conflicting copies. It's often that we see firms using processes that they have to take a spreadsheet and duplicate that multiple times or duplicate certain tabs or worksheets to be able to perform the workflow that they want. And all of a sudden, if you're duplicating this for multiple clients or for multiple versions internally for internal process, and then you update the template, all of a sudden all of those previous spreadsheets have the old version, and then it can be problematic to even assess what version they're on. And you have all of these kind of duplicates living in your systems. That's a very common thing. The other reason I have for you is that it limits simultaneous use. So for example, we were working with a firm that had like a pricing tool where they went in and they were with an Excel sheet. They had an simple template where they were able to work with and estimate a price for a particular client engagement. The challenge was as the team grew and then there are like 25 people, sometimes there are multiple people wanting to use it at the same time. And then that's really when it becomes problematic. Not only because, as I mentioned earlier, there's a bunch of copies and they'll be adding tabs and tabs and tabs. And that spreadsheet is like megabytes and megabytes in size. But as I mentioned, all of a sudden, if they want to use it at the same time, all of a sudden one person overwriting the other's data and it can be really challenging. And that's probably the third reason I have for you. It's so easy to overwrite formulas, change something that something else put there, and there's limited validation methods. There are some validation features, but more often than not, it's not as rigid in terms of ensuring that, for example, this particular column is for number values and that this is a formula that applies across the board. And there's certainly ways to accomplish that, but you have to kind of do workarounds and kind of go above and beyond to make those things happen. And the 4th reason I have for you is just formula errors, right? And often having to use this like manual lookups and weird like 10-line formulas to make something work. And by all means, you might have AI write you those complex formulas if needed, but sometimes you are really using, you're starting to feel the need to upgrade to a more sophisticated system. Okay. I'll say this, I'm not anti-spreadsheet. So by all means, if you Want to start there, it's a good starting point. Realize that there are sometimes better tools that you can upgrade to. And if you're going to use spreadsheet, for the love of God, at least use cloud versions and don't use like Excel on your own computer, because then that's going to be problematic in terms of ensuring that you can collaborate, collaborate across your team. So what's the alternative? The alternative is a tool called Airtable. And simply speaking, for those of you that have not heard of Airtable before, think about it just as a 3D spreadsheet. It's really a relational database, the kind of official term where you can have different tables. Like spreadsheets, but now you can link those tables of objects between different tables and even between different bases. What Airtable calls bases is just like the whole series of tables that you have to accomplish a particular goal. If you're a little bit more technical, do know that also Airtable, although on the front end and as you use it in their interface, can be, feel like a spreadsheet and not as intimidating, it can also be the source of more complex applications and internal tools and software development projects. We internally have an automation stack metric as we think about building digital solutions for our clients. And I think this is a good— this provides a good understanding of where Airtable fits in. We believe that every digital solution effectively comes down to 3 components. There's the data component, there's the logic component, and there's the screen or visual component. Okay. In other words, you have data that needs to be displayed. Logic is what's happening with that data. Where is it going? Once you do certain actions with it, what happens after that? And you have screens, which effectively what's the interactions the human's having and what are they seeing as they do it? We also believe that there are levels to it. Like let's just say level 1 or beginner, level 2 or intermediate, and level 3 or advanced. Okay, so for the purpose of conversation, we're going to avoid level 3, but think about level 3 of like what a software engineer in a SaaS company would use to build their general data warehouses internally, their automations, their hoster code, and so on. But if you think about the level 1 version of that, it would probably be what? Spreadsheets that we just talked about today, right? Where data lives. Budget would be more akin to like formulas. Formulas, it's kind of what has the logic in a spreadsheet environment, for example. And screen is often the ETL spreadsheet itself, which often found that it's useful to also use form solutions when you have to, for example, provide certain information that then we transfer over a spreadsheet environment. Now, as we think about the level 2, the intermediate stage, this is really where we start to think about moving from spreadsheets to Airtable for some of the benefits I described earlier, and moving from just formulas to automation tools like Zapier or Make.com, and start considering interface apps, which could be Airtable interfaces or tools like Softr, Noloco, Bubble. There's many other kind of like front-end application development tools that are more low-code and less, you know, technical that don't require code for you to build them. With that, let me dive into some accounting firm-specific use cases that we've seen. I'll discuss very briefly what was the problem that this use case solved, the overall flow of our logic, and just a quick demonstration of what's the data structure that that particular Airtable base had to accomplish that. So the first use case I have for you, it's bank feeds or reconciling statements. What's the problem that we're trying to solve here? Of course, if you want to reconcile bank accounts and credit cards, it often requires drilling into each individual layer or each account to even start to identify what are the accounts that are out of balance and need attention. So by centralizing statement data in Airtable, you can much more easily compare what the bank says versus what the book says. By centralizing statement data, like which you can provide, and the accounting software data which you can integrate with. So you ultimately have a single dashboard to spot which accounts have material variances without having to log into each individual account manually. So imagine where if you are the firm owner, the senior accountant, rather than say, hey, you have to go into 50 different accounts to know which ones are out of balance, you just see in a simple view, you know, 13 out of 50 accounts that need more attention. Let's look into what the Airtable structure of a solution like this would look like. Not surprisingly, and you'll see this very common across the board, we'll have simply a list of customers if we want to make sure that we're using this for as many clients as we possibly can and standardizing that process across the board. Another thing that can be useful is to have a list of banks or a table of banks where the banks in this particular instance, you could have the specific websites, you could have specific notes for your team to understand any particular nuances or downloading the statements for that particular bank or financial organization. And you just can have the entire list there to make sure that they are able to select it in a later process that I'll share. Here is really where the most things will be. And you can see just a couple of different views available here. One of them is pretty simply an add bank statement form where team members can come here, say for this customer, I just loaded this particular bank statement. It's from this bank. These are some of the last digits of the account number. So you can reference specifically which bank account it relates to. And as a result, Or you can kind of start to, with a very easy, simple experience that anyone can understand and that you can easily delegate to an admin, maybe even a junior admin role, you can start to make sure we have all these statements in this space, in this database, and we have all the information you need to do the following steps. One of the ones that's probably most useful to discuss, it's the accounts that need to be reconciled. As you can see here, for a particular customer, a particular statement, you'll have the end date and the attachment. And what you can have here, it's multiple things you could do. One, you could use AI to get the values of those statements, and so people don't have to. And to actually show you this, I created a super simple built-in feature that Airtable has, and I just say, hey, extract the ending balance of the PDF, and I can just click here and generate. For this particular client, we built this using ChatGPT initially because his feature didn't exist. The AI world and AI features moving very quickly. So as we have more features, we're kind of exploring what other things we could do. At the moment, Airtable has a built-in feature to be able to do this. And you can see here how it was able to get similar to how in this particular instance, this is something that ChatGPT populated via Zapier automation. But in this case, you have the built-in feature to get that particular value. So you can now have these additional columns that are the equivalent of a human being, if you like, reading those attachments and getting specific data points from it. More importantly, you also see here that we have balance in Xero. This could be QuickBooks as well, where you can now say, hey, now that I have a bank statement, a balance and a statement date, and I know which account this is in QuickBooks, go in and find what's the statement there and show me only the accounts that have a variance larger than $100. So in that way, you can see how you can avoid having to go into each individual client account to see what potential accounts need a little bit more attention and might be out of balance. The other use case I have for you, it's sales tax. Okay, so the problem here, and I know not every firm will have this issue. If you're working with potentially heavy service-based businesses, you might not have the need to do sales tax filings for them. But for the firms that do, it can also be helpful to create a base where you centralize all of the different filings you have to do so that you can make sure that your accountants are spending as little time as possible retrieving, reading, and reconciling sales tax data manually. On top of that, you could use this as a system to track the sales tax filings that are still pending, which is something that becomes very hard to track as you have more and more clients. So very similar to what you just, how we kind of the system we just shared, by centralizing this data, you can kind of simplify the report intake, you can extract the tax, the sales tax data, for example, from a sales support. And in this particular case for this firm, we actually are creating certain journal entries automatically in the accounting software as well as tracking the filing obligations in just one place to reduce errors and ensure that we can use as many non-technical team members to do the bulk of the work. Now, what does this Airtable-based and data structure would look like. A couple of things, this is a little bit more busy than the other one, but you could have, for example, the different POS systems. Just like we had the different banks, we would have the different systems here that you might use to pull those sales reports from and have specific details for your team members to know the nuances of that particular systems. You can also have jurisdictions, right? If your clients are in different states, in different jurisdictions, each one we have a different rate. So you want to make sure you have those orderly as well. You could often have sales tax vendors if you are assigning those sales tax values to a specific account in the chart of accounts or to a specific contact in your accounting software, making sure you have that data available so that then we can reference it. And probably the most involved table is the one where you really have to add every sales report so that in this particular instance, the AI or the automation can pull that sales tax value. And if you have already referenced, as we mentioned earlier, where do you want to debit and credit those journal entries to, you can simply change this to awaiting entry and have this automatically populate those sales tax journal entries the right way in your accounting software. And of course, if you also want to keep track of your various sales tax filings that are still pending and that you need to file, you can also have a Workflow here to see okay we have to do this for this client, for this particular period, for this jurisdiction, and keep track of that status and ensure your team is moving those tasks forward. The other use case I have for you is fixed assets and depreciation. So the problem we're solving here is that there is rarely a centralized view to see all the assets of all your clients, and that can make it very hard to track some of those asset details. It can also be challenging to export that data. Usually the clients. That work with fixed assets and depreciation, they might need to export those assets into a tax software later on to do some adjustments. And that export can take a lot of time if it's being done with a heavy manual process at the moment. And in some cases, there's also some depreciation journal entries, depreciation schedules that you might want to put in place. Sometimes the accounting softwares might have some built-in features for this. For example, Xero has a depreciation schedule feature, but what we found with clients have told us is that sometimes it's only working through accrual, and sometimes if they want their clients to also have access to accurate cash-based financial statements, they have to like do some manual work to go in and take that accrual-only journal entry, void it, and recreate it again. So again, here we are effectively enabling one-click exports for tax software. We're on fixed assets standpoint. We're automating the process of creating depreciation journal entries both for cash and accrual, and we're also flagging I may have failed to mention this, but we're flagging mismatches between the fixed assets register and the accounting software balance sheet. So what does that look like? And within Airtable, what you'll see is like I've mentioned multiple times already, probably a list of clients would be useful to have if you want to ensure you're doing this for a series of clients. And then you'll have, say, like a fixed assets table where you have all the fixed assets for all of those clients with the appropriate values, like what was the basis of the purchase price, what was the disposal date, What was the disposal price? What type of asset is it? Because then that is actually what's going to help you. In this case, you can see that we're in a view that's called Export for Tax. By selecting the right asset type, then you can have another base where you effectively map, okay, those asset types, what are the methods, the expense types, and the tax categories they fall into in our tax software. So that ultimately you have a downloadable, I can just hit here, download CSV. You have a downloadable version of what your tax software would want. In this particular instance, this is actually for, we built this for a client that uses CCDH AccessTax, and this is the format that their fixed asset module requires. But if there's any other software tools you have and they have the ability to have like a spreadsheet-like format to import into, then you can use a base like this for that. As I mentioned earlier too, one of the key things is now comparing what's on the balance sheet for those fixed assets versus what's on the fixed assets register or the fixed asset module of the accounting software. And this is ultimately like the outcome of that in just like a monthly email that we're sending to that particular firm where we're saying, hey, for this client, we have this amount of value of fixed assets in the fixed asset module, but this is the amount of value we have in the balance sheet. There's a difference. And then also then populating those Differences in this table so they can track and correct them. Okay. And kind of making sure that every month, rather than waiting to your end to go in and see what's wrong and because, and why these things don't tie out, they are every month ensuring that the automation goes in to try to tie those out and seeing which ones are not, don't match so that then you can come in and correct them one month at a time, making sure that When your end arrives, you're already ready to complete your work. For these other 2 use cases that I have, use case number 4, use case number 5, I won't show you a database because this is something that we're still developing and working on. But let me walk you through what's the problem we see and why we believe and we're working towards a solution and a base within Airtable to solve these issues. The number one, it's unreconciled or uncategorized transactions. Of course, uncategorized transactions is something that builds Up across multiple clients, and it's something that could go unnoticed until the month end. And then all of a sudden you're having to ping your clients to tell you what those things are about, and now they don't remember because it was weeks ago. So reviewing them on a regular basis and having one centralized place to ensure you don't have to like jump in and out of files or chase those meeting details, it's incredibly important. And effectively what we've done is having a table of the various transactions that are unreconciled and having statuses to make sure that you can adjust those as needed. If they require client input, you flag them accordingly, and then we automate the outreach to get that context from the client. And of course, that the moment you determine the category, which you could use some AI to try to do at this stage, if we feel like it's not 100% accurate. So more often than not, there's a human layer of review as well, but you could use AI to suggest the category and then come a human review, give it the check that's good to go, and then push that back to the account software. And the other use case we're currently working on, calling it bank rule intelligence, because we've noticed that a lot of firms, especially the efficiency-minded firms, highly leverage bank rules to automate at least, you know, half of the categorizations of the clients. But that often takes dozens, if not hundreds of rules, sometimes per client to do. And what we found is that that can be inconsistently applied across clients. Sometimes it's done reactively rather than done proactively. Then maybe you You know, you bring a new client and then you have to like kind of like start from scratch and can't really leverage some of the rules you've already learned might work for them based on other client experiences you've had and so on and so forth. So one of the things we're working on, and actually I'll do share a demo for this, but it's a prototype. We're working on a bank rule management application where you can come in and see all your clients. See all of the bank rules that you have for those particular clients, and not only be able to easily duplicate them to create rules for them, but rules for other, for other clients, but also potentially have templates so that if you know you have a tech company, you can come here and instantly apply this to a new client. So if you have 17, 27, 113 rules that, you know, typically apply to real estate companies or to technology companies, Or to whatever niche or industry you currently have in the roster, you can easily not have to start from scratch in that regard. And the truth is like Airtable alone typically doesn't account for a full automated system. As you've seen, you're going to eventually need an automation to power it up. So if you really want to take advantage of Airtable, I made a video for you about make.com for accounting firms, which is our favorite automation tools and one That works quite well with our table. So you can watch that right now by clicking on the video on the screen. I'll see you on the next one, and until then, stay efficient.