QuickBooks Online 2021 sales tax while recording transactions with the help and use of bank feeds, let’s get into it with Intuit QuickBooks Online 2021. Here we are in our quickbooks online bank feed test file and prior presentations, we set up the bank feeds, we entered some of the data into the system, which is now in what I would call bank feed limbo. And the transactions tab, we’re taking that information now, including it into the financial data as we approve the information and add any necessary details. We’re thinking about the deposit side of things.
00:35
And as we looked at in prior presentations, when we think about the deposits, we’re thinking, of course, hopefully, those are going to be revenue type of items that we want to be including in some way shape or form into our system. But what happens if we have to deal with sales tax that kind of complicates the system, because then it makes it a little bit more difficult for me to just use a system where I’m going to just say, look, everything, that’s an increase to my checking account,
00:58
I’m going to record as a deposit, because then the sales tax kind of complicates things there. So let’s go over to our flowchart, which is going to be on the desktop version, you don’t need the desktop version. To follow along, I’m just going to take a look at the flowchart here because it has a nice flowchart. So let’s first just think about the concept of a sales tax or usage type of tax sale tax or usage type of tax would be in theory, the government imposing a tax not on the business, but rather on the customer, but forcing the business owner to be the tax collector, the person that has to enforce the rule, make sure that the customer is then paying the sales tax.
01:33
So the idea then being when you make the sale at that point in time, that’s when the government wants to charge the customer the sales tax, and they’re forcing us the business owner to make them do that. By collecting the sales tax and the revenue that we receive, we then take the portion that is the sales tax, and we have to then pay that in distributed then to the government. So that’s going to be the concept, you can kind of think of that logistically what has to happen when I make the sale, I got to collect the sales tax at that point in time.
02:00
And then I’ve got to track it. And then at some point in the future, I’ve got to pay it to the government. Now in the US, we typically have the sales tax, not on the federal level, but on the state level. Therefore it will differ from state to state. But the concept of the state of the sales tax will basically be the same, in that when we make the sale, we’re forced to basically collect the sales tax, so the rates might change, and so on, when we need to pay it how far after after we collect the sales, do we need to pay it every month, or you know, every quarter or whatnot, that could change, we won’t get into detail on that. But the concept will remain the same.
02:35
What we want to consider here is how do the bank feeds that fit into that type of system. So notice that when you calculate the money that you’re going to be receiving. Normally, if you’re going to set up sales tax into a full service system within QuickBooks, you would be using the Create invoice or the create the sales receipt. These are the two documents that generally typically start the recording of the sales process in a normal accounting cycle. However, with a bank feed system, remember, we kind of wanted to think about the simplest bank feed system, which would mean we’re going to bypass these forms if we could, right and just say that all deposits that we get are going to be are going to be income.
03:13
And then if we had a system where we wanted to record income, at that point in time that we get the bank feed at the point of time that deposit clears the bank, then what we have done is we’re recording income at the deposit and we’ve bypassed the sales receipt and the invoice making it a problem to do the normal kind of kind of sales tax calculation within QuickBooks now, for US companies. If you do like gig work or something like that, and there’s no inventory involved, then then very likely, you don’t have sales tax.
03:42
And you don’t have to deal with that in that case. And that might make it an easy system to use. But if you have to pay sales tax in the US possibly that would be when you sell inventory, for example. And you still want to make it as easy as possible, meaning you kind of want to wait till it clears the bank or you’d like to do as little data input, you know as possible, then how might we work that system that will be in place. Now we’ll talk more about this, this other the other problems with a full service system like creating an invoice and tracking through accounts receivable, we’ll do that in the future. So if you have to track accounts receivable, you’re deviating from a cash basis method.
04:17
And so we’ll talk about that in more depth in a future presentation. And then and then we’ll talk about the Create sales receipt in a future presentation that different kind of systems you might use that that would be you might need independent as to whether you’re using sales tax or not for other reasons, but for now, we want to basically add the sales tax to be in place. So we’ll think about a system first if you create the sales tax and you want to use one of these forms, then you can create the sales tax and then when you generate the sale, you got to use one of these forms.
04:47
And that will then charge the sales tax to the customer with the use of the form automatically which is nice track the sales tax in the accounts payable that you can then later pay. However, when That means that when you record, say a sales receipt, it’s then recording either a deposit the checking account, or undeposited funds. And then when you look at the bank feeds, you’re gonna have to match it to the sales receipt. So it’s an extra step, we’re not relying on something to clear the bank, but rather, we’re creating this form first, then the deposit will clear the bank and then we will be matching them at that point in time.
05:24
The other methods you might try to use is you might say, Okay, well, what if I just sell stuff, and I try to include the sales tax in basically the sales price when I sell the item, and then I and then I wait till it clears the bank. And then I’ll try to basically back out what was the sales tax on a monthly basis or something like that. And that would be a method where you were trying to kind of bypass these two, these two forms, but still, you know, achieve your your obligation of paying the sales tax.
05:52
So we’ll take a look at those kind of methods. So let’s go back on over to QuickBooks. And if you were to turn on the sales tax, then you got the tax tracking down below. So you can go the tax tracking down here, you’ve got to set up your sales tax. Now you’re going to need the location in the US to do that. Because again, the sales tax will be different by location. So just for my test file, I’m going to have the 9801 Eastern Dr. Beverly Hills 90210.
06:18
Yeah, it’s just a made up address, of course, do you need to collect sales tax outside of California, I’m gonna say no, I don’t that will simplify things, hopefully. And then they’re setting it up automatic sales tax is all set. So now I can I can create an invoice if I so choose at this point in time. And then I can populate it, and it’ll help me to calculate the sales tax. But before I do that, I’m closing out the invoice now, then you would need to set up an item that we would be including the sales tax, here’s the sales tax calculation, I’m going to go set up an item now.
06:50
And I’m going to do that by going to the sales tab. And let’s go to the products and services. And then if it was an inventory item that I’m going to be setting up, I can I can go to new item up top, I would set up an inventory item, that means you’re tracking the inventory within the system. So we talked a little bit about inventory in the past, you might use it, that would mean that you’re using a perpetual inventory system, you might have inventory, but you’re tracking it outside the system, in which case, you might create like a non inventory item or service item.
07:20
So I’m going to create, say a non inventory item here. And I’m going to say in so I’m going to call it item one item sales tax just that’s what we’re selling, that’s the thing we’re selling, very strange thing we sell. And then the description is going to be that and I’m going to make them the dollar amount, I’ll just make it zero for now. So I could populate the dollar amount later, it’s then going to go into sales. So it’s going to be included in the income line item of sales. And we’re just setting up our item. So I’m going to say save it and close it.
07:55
And then let’s add a plus button, we can either create an invoice, which will increase the accounts receivable, or if we made the sale at the same point in time and date like on more of a cash basis, we’re going to record the sales receipt. So for now, let’s go to the sales receipt and skip one step in the process not going through accounts receivable. But we still have to record kind of two sides of the thing if we’re going to use this process to use the bank feeds.
08:20
So let’s say this is going to be customer number, let’s say three, customer three, and then the date, I’m going to save customer three. I’ll put it in some point in the prior year. So let’s say oh 715. Let’s make it Oh 515 to zero. We’re going to create our sales item down below, I’m going to say it’s going to be inventory sales tax, what did I call it? Item sales tax. So there we have it, and then I’m going to pick up the rate. In order to do that, I’m going to right click on the tab up top, duplicate the tab, close up the hamburger. And I want to look at my transactions down here and see if we can kind of match it up to to a transaction that we have.
09:10
So we can see a deposit, see how it might might fit together. So let’s say that we had this deposit of this 691 44. So 691 44 is the amount. So I’m going to go back on over and say I’m going to say it’s going to be 650 8.51 because I’m going to try to have a 5% sales tax to see if I can if I can get this to to work out it’s going to be a taxable item. I’m going to hold down Ctrl and scroll down a bit. Now it applied out the sales tax based on the location which is great. I’m going to make it a generic problem however, and just look at and just make it a 5% sales tax just to make it generic. So I’m going to change the math here.
09:49
And I’m going to go down and say I’m going to just make it a generic five override. And I’m just going to make it a 5% and then that that calculates it. It’s gonna say why do you do that? I’m gonna say Other confirm. So now we have a generic 5%. If I close that, now we’re at the 691 44. Total, does that match my deposit here 691 44. So now we kind of tie out to that deposit. So this deposit is separate now, from the transaction, we got to record on the sales receipts so that we can record the sales tax, what will the sales receipt do, then the sales receipt will be in increasing the checking account.
10:27
Or if we put it into undeposited funds, we might put it into undeposited funds here, and then deposit it. But either way, we’re entering it on our side, and then the bank feeds are going to be used in order to match the transaction instead of to record the transaction, then we have the other side go into sales for the 651 658 51. And then we have the sales tax calculated. So now we have the total of the 691 44. So that means that we’re going to get cash of this amount, I’m sorry, cash of a full amount, and then the sales are only going to go up by the 658 51, the difference is going to go to a sales tax payable of the 3293.
11:07
So if I then say Save and save that, let’s open up our financial statements. Just to check that out, I’m going to go to the second tab, right click on it, I’m going to duplicate it, I’m going to right click again and duplicate again. So that we can then open up our balance sheet and income statement. So we’ll go to the reports on the left hand side.
11:28
Now we’ll open up the PnL the profit and loss in this one, we’re gonna do a range change up top, closing up the hamburger range, change a 101 to zero to 1231 to zero, running that report, and then go to the next tab on over and then down to the reports down below this one, we’re going to open up the balance sheet the ABS balance sheet, and then we’re going to say there it is ranging the changing up top on the dates a 101 to zero to 1231 to zero and run that report closing up the hamburger holding down control scrolling up a bit.
12:07
So now when we make when we make the sale, then if I go into the checking account, we had an increase to the checking account, this time not from the bank feeds not from a deposit form, but rather from a sales receipt type of form. So here it is. There’s the sales receipt that didn’t go through the bank feeds yet. So we haven’t kind of verified that at this point in time. Now the bank feeds would then be used as a double check to verify it if I go back up top. And that would be like that reconciliation. So now we’re using bank feeds as a reconciliation type of feature. If I go to the to the income statement, and then I take a look at the sales tab, we’re going to the sales tab, we have this amount here. For the for the sales receipt, it’s for the 658 51.
12:52
Remember that that does not include the sales tax here. So this is on our income statement, not including the sales tax, we’re only including what we charged the difference between what we deposited and what we charged, what we deposited here, what we charged to this is that 3293 and that then is being tracked by the system, not on the income statement, it shouldn’t hit the income statement, even though we’re collecting on it scrolling back up top, back to the income statement, but rather goes back to the balance sheet.
13:21
And we’re seeing it going to be down here on a payable account, like a sales tax payable type of account here that we will then pay in the future. Now this, this payable is an accrual type of thing. So that’s going to mess up our bank feed feature. That’s why we would we could use this system and we’d have to take this extra step and not just simply wait till something clears the bank but we might be able to work around that if we need to as well.
13:42
So let’s just think then, if we did this system, if we go back to the bank feeds, and then I was gonna say all right, well now let’s say this one, what was we’re gonna pretend this was the invoice are matching to the invoice instead of recording the deposit, I would need to match it, I’d need to go find a match for it at this point in time and look for there it is the match. And then when I tie it out, basically is not going to be recording the transaction when I do this bank feed. If I was to match this, it would simply be saying oh, okay, there it is.
14:14
We’re reconciling. We’re doing a reconciliation process instead of recording the transaction at this point in time. So I won’t actually do it at the end. Let’s go ahead and do it. If I if I say save that. So now we’ve we’ve matched it out. So now we’ve entered that in and we’ve matched it out so if I go back to then the financials and I run the report, we didn’t add anything new when I when I included that we just matched it out if I go back to the to the income statement, and I run the report for example. We didn’t include anything new in the sales line item here or anything like that.
14:52
There’s not a double amount to it. We simply matched out the the amount that was on there was recorded from the sales tax. Item I’m sorry, the amount that was recorded from the sales receipt. Okay, so now let’s think about a situation where I want to depend on the bank feeds, I don’t want to have to match it, I would like to wait till something clears the bank. Now if I go back to the, to the flowchart here, let’s assume we have a situation like we make sales, for example, we sell things. And, and instead of recording the sales receipt, every time we make a sale, because maybe we sell a lot of them, I just want to collect the payments on it.
15:28
And then and then record, you know, take it to the bank, deposit in the bank, and then wait till the the bank feeds come through with the deposits, and then just record the sales at the end of the day. I want to do that. But the sales tax are causing a problem. I don’t want to record the sales receipts here, but I have to pay the sales tax. So how can I how might I deal with that? Well, one way you could do that is you could say, Okay, well what if I go over here and kind of figure out what our sales tax obligation will be.
15:59
And we could do that by taking say, for example, our sales, let’s say we take our sales amount, and let’s just say our sales was we make a sale of $500. That’s our sales price, the sales tax rate, they’ll say tax rate, let’s say it’s point oh five, or 5% of all go to the Home tab percent defy that. So it’s 5%. And if we multiply that out, then this times this, that means we’d have to collect another $25 in sales tax, not the rate. But the actual tax, I’m going to format my whole worksheet here.
16:39
I’m going to go right click, I’m going to format the cells. So I could see some pennies and whatnot, I’ll make it currency, and negative numbers bracketed. And then Okay, and then all percent defy this one, again, Home Tab number percent defy. So the amount we have to collect, then would be the 500 plus the 25 500 plus the 25. So we’d have to collect 525. So that we can then have our obligation to pay the $25 to the government. So when we set our sales price, in other words, if we want to collect $500, for the sales that we’re making, we then would want to increase the the amount to 525.
17:26
So we’re also collecting the sales tax that would be in place on on the sale, if that will say for example, an individual sale. So that’s the first thing you want to think about, you want to make sure that that when you set your prices, you’re setting your prices, that would include the sales tax, meaning if you were doing that in the QuickBooks system. As we saw, if we created a sales receipt here, if I go into here, and go into my sales item, and I set my sales price, then QuickBooks figures out the sales tax for us by applying the rate down here for us, if I’m not going to use the QuickBooks system, then of course, I need to make sure that I’m including the sales tax in the price that I’m going to be going to be setting.
18:10
And then and then we’ll be then when we make our sales during the day, if I go back on over here, when we make our sales, we’re going to be collecting the sales tax when it clears the bank, then we’re going to have things that clear the bank that include both our dollar amount and the sales tax amount. When it clears the bank will simply be recording most likely we’ll include it in the bank feeds to include an increase to to the cash and increase the sales.
18:37
So now we’re in the easy method, we see it clear the bank, we include an increase the cash and an increase the sale, we don’t have the sales tax payable account being broken out here, as we saw before, but everything’s recorded the sale and then we’re gonna have to make an adjustment to break out the sales tax payable. So meaning everything would be recorded to sales. So then if I go back into my my income statement, for example.
19:04
And let’s go back on up. So let’s imagine that we’re using bank feeds. And this sales line is the item that we have to pay sales tax on. And we just recorded the sales, and we recorded all everything that was deposited into the sales line. So this includes both our sales and the portion that we owe to the government. So now of course in the form of sales tax. So now of course the government comes in and they strong arguments and they say hey, look, if you want to do business in this town, you need protection. And so we’re going to charge you 5% sales tax, so we’re going to go Alright, so now we’ve got this 958 51 and we have to basically do our sales tax calculation to kind of back into the sales tax.
19:49
So we got the 958 51. So that’s gonna be the 950 8.51. And now I got to figure out how much of that is going to be sales tax. So we’re Kind of reversing this calculation. So we’re assuming we collected revenue based on this. And now we got to figure out how much of that revenue we got to pay and sales tax. Now, you might think you would take that number times 5%. But that’s not quite right. Because because we need to kind of reverse this calculation and in order to get there, and one way you can do this is you can use a tool, that’s going to be a goal seek tool.
20:24
So let’s simplify this transaction a little bit, I’m going to say if the sales were were 500. And we’ll use the same example as our starting point. And then I multiply it times what rate Do I have to multiply it times to get up to the amount that we’re going to be collecting ad, so that would be equal to the the 105% 100 plus to 5% sales tax. So now I’m going to say 1.05, or 105%, I’ll percent defy that. And this is a this will get down to the collection line here a little bit quicker. So we got the 500 times the 105.
21:00
That gives us our 525. So now let’s imagine that we’ve we’ve collected all the revenue, so we know what we collected, meaning we know the bottom line number, let’s imagine that should be this 958. That’s what could be this number, we need to kind of back into what the sales line item is, the difference being the sales tax. So if I collected all the sales and sales tax, then I have to back into the amount that I’ve got to pay, I’ve got to pay the local, tough guy, I mean, the government, then what we have to do is then say that, we got to back into it.
21:34
So I know the bottom line number. So let’s let’s go ahead and do the same calculation, I don’t know the top number, right, this is going to be the yellow number. We know this is 105, we know the bottom number I’ll put down here should be this 950 8.51. So I’m going to kind of back into this calculation, I’ll do it this way, I’m going to make up a number up top, let’s say $100. And then we’ll multiply this out, this is going to be this times the 100. And then I’m going to use Goal Seek to tell tell Excel to kind of figure out what this amount should be. In order to get that to what we know it should be the 958 51.
22:09
So obviously, you can do algebra to do this. But I think this goal seek calculation is useful to know. So I’m going to go through then to the to the Data tab, and then the forecast and then the what if analysis, we’ll do the Goal Seek. And then we’re going to say we want to set, we want to set this cell to be the 950 8.51 we want to do so by changing this cell. So we’re going to use just trial and error within QuickBooks will just do trial and error to figure that out.
22:40
So if I say okay, do that. So there it has it. So now it figures out that if we collected 958 51, and that includes sales tax, and if we back out the portion that a sales tax that we’re going to have to pay, you know the government, then then that means our sales that we charge, not including the sales taxes, the 912 87. The difference between the two then is of course the sales tax. So that means of the 958 51, we have to pay the government the 4564. So this is a way we can kind of back into the sales tax that we owe without using, you know, the sales tax widget tool within QuickBooks.
23:24
So in other words, if I go back on over here, we’re using a system, we’re thinking about a system where we have to pay sales tax, but what I’m going to do is just include the sales tax in the sales price when I make the sales, and then I’m going to make the sales, wait till the sales, clear the bank, so that I just record the deposits with the use of the bank feeds, then when I record the deposit, they’re going to be increasing the checking account, and the other side is just going to go to sales.
23:51
But sales will be overstated, because it will be including both the amount that that we charged for sales and the amount that the government forced us to collect on their behalf, then we’re going to have to reduce the sales when we make the payment to the state for our sales tax obligation by calculating and figuring the amount that should be backed out. And we could do this calculation to do so in this case, figuring that we owe 4564 to the government, then we pay that to the government decrease in the checking account.
24:21
And the other side then is going to go to sales basically decrease in the sales amount, bringing the sales amount from 958 51 back down to what it should be 912 87, which is the amount that we in theory charged, not including the sales tax that we were forced to charge as basically a tax collector, you know, from the government. So that that’s another way that you can kind of think of to simplify the sales tax. So in summary, then if you have sales tax, it’s going to complicate the system to some degree, you could still try to do it kind of on a cash basis method and back into the sales tax in the method we talked about here.
25:00
Or you can of course set up the sales tax and use the sales, the invoice and the Create sales receipt to record the sales tax as you create the sales which will increase a payable account. And that’ll help you to then pay off the payable account payable account being an accrual type of option. Therefore, if you use this system, then you’re going to have to do some kind of matching type of system with the bank feeds to get everything to tide out