So, just to help anyone else dealing with these awful Shopify Tax reports and trying to file in a state like New York (where they ask you for county-level/jurisdiction-level gross sales numbers and not any of the things Shopify currently provides). Here's some easy enough to follow general instructions to take their report and make it something useable if you're struggling:
1. GET THE DATA | get the report from Shopify, download it as a CSV, and punch it into the Google Spreadsheet (or whatever spreadsheet program).
2. ORGANIZE THE DATA AND GET THE GROSS SALES | Youll now need some additional columns to get the info you actually need:
- A column to SUM the TAX OWED from items with the TAX OWED from shipping to get the TOTAL TAX by district/jurisdiction/county. With this number, you can now calculate the Gross Sales by district.
- The next column is to formula to calculate the county gross sales that you actually needed in the first place-- Take the TOTAL TAX by district, divide it by the tax rate for that district and itll give you GROSS SALES for that district. Paste the formula down the list.
- The third column is for combining gross sales for like-counties and districts. For me, at least for New York, Shopify is giving all sorts of erroneous extra rows/broken up data. For instance, I have 2 lines for every county (one with jurisdiction labeled and one without). Im fairly certain this is some database error on their end, but who knows. Anyway, those will need to be added together. Additionally, for me, places like NYC were broken into ~8 lines throughout the data (Queens, Bronx, NYC Etc), and there were also special districts that needed to be combined with their counties (things like "Putnam Transit District" which I just put into Putnam County as nothing else seemed appropriate). I moved the rows so the like-things were next to each other, and colored them to denote all part of the same jurisdiction.
Note: I did notice the tax rates may be slightly wrong/different on some of this like-jurisdictions, which made me assume I was missing something or filing/categorizing wrong-- but then I realized some had tax rates listed were ones that NO jurisdiction in New York had... which leads me to believe its another example of Shopify's data being incorrect/buggy/mistaken...
3. TOTAL GROSS SALES CALCULATION | NYS at least, requires you to enter your gross sales before you can do county by county numbers, Sum up the whole Gross Sales line to get that. But before you do: Make sure to REMOVE the state level line from your TOTAL STATE GROSS SALES calculation as it represents a double count. Because you've already calculated your county level gross sales (which should be ALL the sales you did across all the jurisdictions and add up to the total for the state), the State specific line isn't needed (for NY anyway) and is effectively a double count-- so remove the State tax line.
4. ACTUALLY FILE/FILL IN YOUR DATA | Ok, now that you've grouped what you need to, you can sum the GROSS SALES lines you already calculated to get the TOTAL GROSS SALES in any districts that shopify is dividing into multiple rows. Alright, you should be all set to begin entering things. Id strongly recommend some kind of clear notation to keep your spot as you go as it can get confusing if you have a lot of counties/jurisdictions to go through-- and Ill also note, for NYS anyway, that Shopify doesnt organize/sort things the same way as NYS, so there may be some jumping around. What I did: I made a gray row in my spreadsheet that I moved down once I had verified entering a row's data. That way I knew everything above the gray row was done, and everything below i still needed to do. NYS Tip: Def hit the little "display as a grid" icon in the top right on the data entry page, to make entering much easier.
Seem like nonsense? I agree. Complain to Shopify, this report has been beyond frustrating for those filing in in New York (and other states) since at least 2021... and this latest report somehow made things even WORSE. Its pretty obvious that whoever designed it has never filed Sales Tax. Its remarkable to me that the POS software I use at my other business from 2012 can do sales tax in minutes, and using Shopify's modern report takes me several hours to do a single state.
Goodluck, and please dont pay some app maker $100 a month or something to do this for you. It's annoying, and you shouldnt have to do this, but you can definitely get it done with some effort once you know what youre up against here.
