How to Read and Understand Your Shopify Sales Tax Report

0:57

So we have lots of clients that are selling on Shopify, and Shopify is a great selling platform, and we get questions from them about sales tax all the time. So before I dive into the actual report, I just want to address a couple key questions that we've encountered a lot over the past few months.

So the first one is, lots of sellers are wondering if Shopify is responsible for collecting and remitting sales tax to different states. And the answer is no. Shopify is not like Amazon. It's not a marketplace facilitator, so it doesn't automatically collect and remit sales tax to the states. You have to manually set it up yourself, and we have a video on how to do that, and you have to determine where you have nexus and that's where you will need to register and report to the states. To learn more about determining where you have nexus and whether you need a seller's permit, please refer to this other video that we've done, and leave us any questions that you might have, with Shopify, with nexus on Shopify, and whether you need a permit, how to determine nexus.

1:56 Exporting the orders report

Okay. So I'm now gonna switch over and share my computer screen so I can walk you through where to download the reports, and then I'll walk you through the reports and just a few tricks on how to understand the report, so you can determine nexus for yourself and use the information on this report to do your sales tax filings.

So this is one of the Shopify accounts that one of our customers have. Once you log in, you're gonna be on this home screen, and next you want to go to orders. And then on this top right, you want to click on export.

2:42

So depending on if you are doing a monthly filing or you want to review a whole year of nexus, this is where you would pick order by date and select the dates. Right now it's, today is January 14th, so if I am doing the return for December of 2020, I would want to look at sales from December 1st to December 31st and then I click on export orders.

For our purpose, I'm gonna look at the last quarter. So in case you are a quarter filer, then you want to look from October 1st. So you just click on first and then you click on the month and then you click on 31st and then click on export order. And we can leave it for CSV for Excel. This format is great.

3:34

If you want to do a nexus analysis on all of your sales in 2020, you would go to the same place and click on order by date. But instead of going to October 1st, you would go all the way to January 1st and then all the way over to December 31st, and then click on export order.

4:19 Reading the order export columns

And the file will get sent to the email address that's associated with your Shopify store, and it might take from a few minutes to a few hours, just depending on the volume of sales. And it might show up as like multiple folders of export.

So this one, we are gonna show it right here. This is the one that I downloaded the first time for Q4 of 2020, just for demonstration purposes and showing you guys how I utilize this report.

5:06

Okay, so this is the report you will see once you open it from your link in the email, and let's expand all of these so we can go through everything that's on this report. So we can see the header really clearly.

So name is the transaction ID number, and you can see it's duplicated because it's by item in that order. So this one, the customer Lauren, she bought 1, 2, 3, 4, 5 items. And we can see also on here on the item name, these are all different items, but it's all for this one customer. That's why the transactions are repeated this many times.

5:49

So it says whether it's paid or not, what time it was paid, whether it's fulfilled or not. If it is fulfilled, this is the date where it was fulfilled. And this part is the financial part. So I want to walk through this for sure.

The subtotal column is what the customer ended up paying for the product. And this is net of discount. So this 215 is net of the 116 discount amount. And then the subtotal plus shipping plus tax equals this total, which is 222.93. Matches right here.

6:39

And we can see, we can know that this is net of discount, because if you keep going to the right, there is the line item price for each of these item. And if we add them up, it's $332.20 minus the 116 will give you the net of 215.93.

And same with this. This one, they didn't have a discount, so everything like these two or three items they bought, sorry, that went a little too far. Okay. So they got leggings and long sleeve and gift card, which is these three. I think that's correct. Or maybe let's see, Kevin and Kevin. Okay. So just two items, these two, the leggings and the long sleeve. So these two together, it's 144 and it shows 144 plus 7 plus 10.95 will give you the total of 161.95.

7:44

And then if we keep going to the right, this describes the item they bought and the pricing for each item, fulfillment status, which corresponds with earlier, what we've seen, and then billing information, all of these.

8:03 Why the ship to address column matters

For sales tax purposes, we really want to focus on the ship to address. So shipping name, shipping street, and here ship to address one, two, and city zip.

Right here, instead of state, Shopify calls it shipping province, because if it's to an international location, lots of other countries use province instead of state and country. So this ship to column, column AP, is really, really important when it comes to sales tax filings, because this is showing you where the customers are, which determines your nexus, which determines which state you're gonna file your return to. And then just notes and other things. So these are everything out on this report.

8:59 Adding calculated columns and building the pivot table

What we usually do to try to analyze this report is to run a pivot table. And I'm gonna show you that, so you can see exactly how to analyze this report.

So a lot of these information, we want to strip them out. So we are gonna add just a few columns, and you can add more columns depending on your need, but we usually would do a column to strip out the year and month, and then we'll do a month year combined. And it all makes sense in a little bit. I promise.

9:57

And then we'll do a calculated order number, because when you want to do economic nexus analysis, you want to know how many transactions you have into each state. I call 'em AP, but you can't just count these because they're duplicated. So we want to make sure that they're not duplicated when we count them.

So year, we'll just do equal year and we'll strip out the year from the paid at information. We'll do the same with month and we'll do this one. And then month year, we'll just combine those. We'll say, we want to show that it's December and we'll want a space in between, and then we'll put the year on there. So it's just December 2020.

11:04

Then this calculated order number, we'll use the IF function. If this equals what's above it, then the count is zero, because if it's the same, then we don't want it to count again. If it's not zero, then it'll be one. But sometimes we just want to make sure this always has a value. So if this doesn't have anything, we'll say it's zero. If it does, it's one, just to be safe. Oh, and I need to put another parenthesis at the end. I always forget that. Okay.

Just because these are our formula and modified columns, so I usually want to highlight them. So I know that these are the formulas that I added, and then we'll drag them down so they apply it to every single order. And you can see, like these ones, they don't have one, so it's using this date. So we'll see that when we run the pivot table.

12:02

So then let's click on this little triangle sign and we'll insert a pivot table. We use all of this range. And then on here is when we can say, how do we want this to be structured so we can analyze it. This is how we recommend to do it, but you can feel free to change it up or whatever makes sense to your business.

But here we have month and year that we created, so we can filter by the date we want. And then we have financial status, and I'll explain more on why we want this.

13:04

And a lot of our clients want to know information by payment methods, so we can add payment methods on the filter as well. One other thing we can add is shipping country, so we can filter out lots of transactions that are not US-based, where it's shipping country right here. We'll just put it on the bottom.

Okay. So these are filters that we can use to manipulate data. And then on the row, we'll put shipping province. That's all the states. And then we need to put all the financial information so we can start analyzing them. So we'll put subtotal, shipping, because shipping is taxable in some states but not other states, and then taxes. We can put total as well, but that'll just be a sum of the previous three.

14:19 Filtering the pivot table and checking nexus thresholds

Let's close this, so we are not distracted. So this shows all the number of the subtotal, shipping, taxes, discount code, by shipping province. Let's change the format so it looks cleaner. I usually select them all, and then I just click on this, so they look cleaner.

And now we can filter this to get the information we need. So month, year, let's say we want to file for Q3. Then we wouldn't want to select 10, 11, and 12. Then we don't want to worry about anything that happened in September. And we know this 1/1/1900 is really just for all the blank orders. So we don't need to worry about that. Not blank orders, the repeated orders. So we don't need to worry about that.

Oh, sorry. One more thing with the pivot table. Let's put calculated order number on here, for sure, so we know how many. Calculated, but then this, we don't want to show all the decimal points, so move this way. Okay. Awesome.

15:45

Okay. So, well, let's go back to the filtering. So month, we'll just do the last quarter of the year. And financial status, we do not want anything that's pending. We've seen that with our clients that sometimes when it says pending, it actually never went through. So we don't want to include anything pending on our analysis or when you are filing, because those are not transactions that actually went through. So we want to filter out pending, filter out pending, just gonna write notes here.

And for the month, year, select the month you are analyzing. And if we have a whole year of data or two years of data, you can show 2020 from January to December, or you can say 2019. That just makes it easy to see. And that's why we add these columns.

16:51

And then payment method. We usually include everything. But if you know that, like for example, some of our clients will want us to exclude manual orders. So if you want to do that, you can select all, but then unselect manual. But in this scenario, we'll just leave all of them. And then shipping country. This is when we will just select US. So then we don't see all the other abbreviations that we are not familiar with. Select US.

17:21

Okay. So now it's a lot clearer to me now, all of these states. So for example, we'll talk about Arizona. So Arizona, then you can say Arizona for Q4 of 2020 sold $3,895 and collected shipping cost of this much and collected tax of this much over 414 transactions. And this $3,229.73 is what you need to remit to the Department of Revenue in Arizona.

And you can see some of them don't have tax collected and some do. And this is all depending on your tax setting, because this business has determined that they have nexus in Arizona, California, Hawaii, Illinois, Indiana, all of these states. So they turned on the tax collection setting in their Shopify settings, and that's why they are collecting tax on in these states.

18:39

So when you go in and file, Indiana is a good example because it's a flat rate state. I would just put that I have 11,000 of sales and it's 7% of that. So it's about 855. And if I over collected or under collected, I'll make up the difference on the form. But this is where you need for all the information to file your tax return.

If, instead of doing a return filing, if you are conducting a nexus analysis, then you would look at this and say, well, like California had 955 transactions, if they had a transaction threshold. If we pretend that this is the data for the whole year of 2020, then I would look at this and I would look at the subtotal and look at the order number to compare that to the economic nexus threshold for that state, to see if I've passed the threshold or not.

19:53

Let me know if you have any questions on that. I know it can be a little confusing, but this is a good way to summarize all your data.

This is a good example. So if instead of filing for Utah, I want to know if I pass the economic nexus in Utah from my Shopify sales, I look at it and I know Utah's threshold is a hundred thousand in sales and 200 transactions. It's a hundred thousand dollars in sales or 200 transactions. So, I let's pretend this is a year of data. So I know 50,000 is not passing the threshold because it's less than a hundred thousand, but 573 transactions is more than 200 transactions. So I definitely pass the threshold for Utah and I should consider register and remit in Utah.

21:00

This business, this is just for a quarter, so if we times that by four, they sell about 200K in Utah, they've passed the threshold, and that's why they're collecting tax in Utah instead of the other states.

And this just shows a total of this period, of how much you've collected. And at the end of the day, you need to remit this 27,000 to all these different states that you've collected tax. And if I want to change it, I want to say, oh, I just want to see how much I collected in December, then you can just change the filter and everything will change accordingly.

So yeah, that's how you go from this document, add some calculated columns, and then run a pivot table to give you more specific information on your sales tax.

21:48 The returns report caveat

One thing I do want to note before we exit out is to show you guys that there's a caveat to the orders report, because if you go to analytics and go to reports and then there is finances. Go to finance summary, you can see here, there's a returns.

So at LedgerGurus, we also download the returns report to net it against the orders report. So you get a more accurate number of how much you actually sold, net of returns, but this return has some limitations because it doesn't show returns by state. So we usually do some manipulation and we create custom reports to help us with that portion as well.

So if you wanted to be more accurate, reach out to us and we can for sure help you with that, and we can show you individually, because every Shopify account is different. Some can create a custom report, some can't. That's why I'm not gonna show that process on this video, but feel free to reach out to us if you want to understand the returns portion. And we're happy to show you.