How to Automate Sales Tax Journal Entries in Xero #AutomateInPublic

Hello, hello, this is Isaac and we're starting a video series called Automate Live where we're going to be taking some use cases for our accounting firm clients and we're going to be sharing how we automate certain processes for them while we do it. So in this particular case, I do want to say this is the first one and I'll probably have a link in the comments or in the description where you would be able to request the particular process that maybe you're currently doing manually for us to consider actually automating live for you. So I'll take you the context of this particular process that we recently did for a client where she had a database where she keeps track of her clients, and it's a sales tax journal entry. Automation. So she has some of her team members download the POS reports from different providers, Square, etc. They upload that in, and then they see that sales tax line item and add it in. So then the owner of the firm goes in, I believe on a monthly basis, goes through all of the clients she has and has to manually one by one add those entries by putting the amount as a debit to an income account and a credit to a sales tax account. So in this particular instance, we're going to be replicating that in just a trial account I created here. And let's get going. The way I always like to start is With the end in mind, so we know we want to create a journal entry eventually. That's kind of like the ultimate goal. So we want to see what are the platforms that are going to allow us to do that. This particular client already had a Zapier and a Make account, and as such, we didn't—we were not kind of like boxed into one of them. We could use either. So we just went and looked for what are the actions that are. Available on each one and see if they have what we want natively because it's a little easier to do. So if you look for Zapier Xero on Google or you look into their apps database in zapier.com/apps, you'll be able to find this page very easily. And if you scroll down, you'll see the triggers and actions you have available. So here I will just look for journal. I don't see anything that says journal entry. And even though I see an API request that effectively allows me to do anything the API has available, it takes a little bit more. It's a little bit more of an advanced method of doing it. We can get it done if the client only has access to Zapier, but this particular client had access to both. So we went, okay, let's go into zero. And in this case, if we go into the actions, you'll see right here. Where we have the ability to create a manual journal, which is exactly what we want. Okay, so let's go into our Make Account here and let's add a module. Uh, I'm not going to add it as the trigger because it's not— this is one of the actions that we want to create as a result of certain triggers that we'll talk about in a bit. But I'm going to create a manual journal, and in this case I have a couple of accounts here. One's connected to a demo account I created, which is the one I'm going to be using. And just as a way to test it and see if it works as expected, I just like to put all of the variables here in a static— as static variables, meaning I just put these things the way I would like them to be. But eventually, I'll have to put them dynamically so that this can work for other organizations, for other amounts, for other accounts, for other dates, etc. This is going to make sense in a bit. So for the time being, let's just say here sales tax collection. I'm going to add two items. Let's assume in this case it's $100 on a sales account. And then we add another item, minus $100 on a sales tax account. And if I go and click here, uh, show advanced settings, I'll be able to see a couple of other things that I need, like the date, which let's just select the end of the month. And I can also see the status. So here it says draft, posted, deleted, or avoided. In this case, we want avoided. We want to post it. We want to make sure that goes live as soon as it goes through. Technically, you could do draft if you're testing, just as a way for you to actually double-check and post manually, but it defeats the purpose of the automation. So let's just do posted in this case, and let's just say here that we do want to show it on a cash basis because that's actually one of the requirements that this particular client had. So I'm just going to click here, OK, and I can test this manually by right-click and only running this module. And I can see here that it went through. So let's actually look at that here on the account itself. One second. Okay, so if I go to journal reports here, I should be able to look at the manual journals, and I can see here sales tax collection with a date of January 31st, and I see a debit of $100, a credit of $100. If I click into in, I'll see that the debit is on the sales account and the credit is on the sales tax account as the client wants them. Okay, so at least we know this works. Now the challenge comes— we have to make this work for all clients, for all amounts, regardless of the account and regardless of the date. So the truly the only variables here that I've— I selected that are static is the status, which will always be posted, and this, uh, yes on show on cash basis report, which we always want that to be the case for this particular client. But we need a date, we're gonna need, uh, the amount, different accounts, and the organization of this particular client. Now the client already had a database with some of these variables. So I'll try to quickly replicate that in a new base for you to see what that setup looked like. I'm not going to bore you with all those details there, so I'm going to pause it. I'm going to cheat here, and I'll be back with just a replica roughly of what that looked like. All right, so I'm back, and as you can see, very simple, a client database that has certain information about the client, but more importantly has a Xero map that we're going to create so that we can map that client to their Xero organization. Maybe we should even call it like Xero org map. And then here in the download POS, we have a lookup field. And this is— I mean, if you don't understand any of this, don't worry, we might have other videos where we go dive Dive deep into Airtable. Just know that effectively, I want to make sure that I have access to that number, which I'm going to get in a second. And here is what the team members would use to say, hey, I will work for Stripe, download a sales tax report, upload it here, put the sales tax here, and then just mark this as done. That will trigger the addition of that journal entry automatically. So again, the data we need here is this Xero organization map. The way to get it really easily is just simply here. If the organization is selected and I click on map, it will show up what the specific, you know, ID is for this particular organization. I just put that in and And I'll do that too for the other company that we have here as an example. Okay, um, what else do we need? Just let's just run an example, um, so that we can start setting up the triggers. This would be the example. I downloaded a random report on the internet that has like a POS with some sales tax line items. I'll just upload it. This is a random, uh, image I found online, but just as an example would be okay. Here we see the sales tax, which is $195.46, and we will simply insert that manually here and mark it as Done. And this I will use as the trigger to go into this Z organization and create the, the thing, the, you know, the journal entries. Now, something we failed to add, we haven't added yet, is the accounts. As you can see here, the accounts can also be mapped and should also be mapped to their respective numbers. So here, sales, I can see it's 400. Sales tax is 20, 20, 40. The easiest way to do that is simply adding a value here. Let me just pause the video and add that real quick. Okay, so we add in a debit account field as a number, which is by default 400. So if I actually add a new one, you'll see that it The number 400 comes up automatically. For this time being, I'll just add manually to these examples that I had already created. And I'll create also a credit account, which is also a number. It's a full number. And let's just say the default is 20, 20, 40, which is the, um, number of the sales tax account that we have here. Now, the reason we have this here, again, if I create a record, the idea is that that ID, that account number, will come up automatically so that we won't need to add it. But we want to have them there so that we can adjust them if necessary. So this particular client actually has a standard, a standard set of accounts for all the clients. But if there's a couple of exceptions, she can still come in here and before she clicks it as done, she can select the specific account that relates to them. Now let's pull all this data from Airtable to Make and let's see what manipulations we have to do to actually make that happen automatically. And then we will be testing with these other accounts. Okay, so I'll create here. I'll look for Airtable. Watch records. Create connection via key. This is actually not being deprecated soon, so let's go with Airtable OAuth. Let's add a base. We just created this self-stacks POS base as an example. We're going to grant access. Resource not found. Let's try again. Things happen, although I see it connected it, so I don't know why it said that. Well, Let's keep going. Here, download POS is the table I want to look at. And here, this is actually a requirement, unfortunately. I have to have a field that lets me know when a particular record is modified. So, we'll just go back to the base and look for last modified time. And I'll leave that as all editable fields just so that we can come back in and select that. The important here is to actually have a formula that allows us to only run this for status equals done. So if I take the exact name of the field, which is status equals done. That should be enough for us to actually run this. Well, label field, let's just say record. Let's choose manually, see if what we already had shows up. You can see it here. The record one, it's available, which is this one right here. We'll just we'll just click OK. We'll select it manually. Click OK. And now we should have the ability to run this module only and see the data that comes up. So here we'll see the zero map which we need. We'll see the sales tax amount, the debit and the credit account. And we forgot another thing, which is the date. So let's add a month and date. There are ways to actually populate this automatically as well with an automation. We could simply say the date they are created. We can have to simply automatically populate the end date here. Let's just put the end of the month as an example. And let's actually run this again. So now we see the date, which we need, the debit and the credit account, the sales tax account, and the mapping to the particular organization that we want. Now, there are some calculations that need to be done and some filters that need to be put in place. So I'm gonna unlink this to create a step in between where, where we're gonna be setting multiple variables. And now I'm gonna have 3 steps. Something that would allow us to avoid some errors is just making sure that we actually have the data we need to move forward. So, in this particular example, I'll simply set up a filter that says, hey, if the zero map actually doesn't exist, don't move forward. Or said in other ways, make sure the zero map exists to move on. Also, the month and date should also exist, and we should have an amount as well, and that amount should be greater than 0, right? We cannot have a 0 amount in this particular case. I believe there might be ways to do it directly here. For example, here we're not allowing negative numbers, so that's already somewhat being accounted for, but we do need the— to, you know, filter out zeros. And then there are a couple of variables we want to create. We want to have a positive amount, which is the exact amount here, but we also want to have a negative amount. Which is gonna be -1 times the sales tax amount we have right here. Um, yeah, let's move on with this for now. Let's just run this particular module. Well, I'll run it entirely in a second. But now what we can do, it's rather than having— let's actually try to run this entirely. Save, run. So let's just quickly just move this to to-do and done again so that it shows up again. And here you can see it actually went through, um, but my guess is it took the $100 again because I had not mapped that out yet. So what I'm gonna do here is rather than having a fixed organization I'm going to have the organization come from the, you know, record we had on Airtable. The positive amount we'll have here, but rather than having the sales account directly, we're going to map it to the debit account that came from Airtable. Remember that, that I will want to allow that flexibility to change that. Um, here we got to have the negative amount, and here we'll have similarly the credit account from our table. Uh, we do have a date here. Let's just put month and date that comes from there, and that should be it. If we save this And let's actually test with a different amount. I'll just put here $245.60. Let's test with a different date. Let's just say it's the end of March. And now let's just put again done. If we go ahead and we run this It should go through successfully, and if we go back to the manual journals here, we should be able to see sales tax collection for the month of March of $245.6 to the respective $420.40 accounts. What that means is from now on, this client doesn't need to enter zero again. To make those manual journal entries. She should just stay here on Airtable, and if she has, you know, 100 clients here, she will just sales tax, done, sales tax, done, sales tax, done, and that will happen in the backend. So I hope that was useful. I know there was a couple of things here on the Airtable base that I didn't want to cover because it's going to extend the video a lot. But as I said, I will have a link in the comments below so that you can request any process you want that you might be doing manually in your firm today, and we will consider automating it live. I hope this video was useful, um, and thanks for watching.