Etsy Payment Sales Report VS Orders Report CSV - Faster Tax Prep
Yesterday I spent several hours writing about my step by step to finding and reconciling the amounts reported on the 1099-K from Etsy. The primary report I used to review was the "Orders" CSV report in the settings/options/download data section of Etsy.
Today I found a workaround. It's faster with fewer problems!
From my blog yesterday, you'll still want to start with steps 1-3. However, at step 4, instead of downloading the ORDERS CSV, download the "Payment Sales" CSV. This will be applicable to those of you signed up for direct checkout (DC).
The columns most valuable to us on this report are: Column F "Fees", Column H "Posted Gross", and column Z "refunds". What makes this report a little different is the placement of the order date. The date of order is listed in column V. Insert a few line breaks to separate between months then sum-total your amounts from Column H and Column F, and Z if applicable. F should give you your order income total that matches your 1099-K, and H will give you a sum-total of the associated "payment fees" associated with those transactions.
5. Handling refunds: Your 1099-K includes all money that came into the shop and reports as "income", but it does not offset to take into account partial or full refunds. You don't want to pay taxes on these refunds, so make sure to find any refunds in Column Z and total them. ONE MORE STEP! - if you refunded any part of your order, the payment processing fee will also be reduced. Look for this alternate amount in column L. To get an accurate sum-total for your payment fees, MOVE the "adjusted fee" amount from column L to column F so that your sum-total of card processing fees is accurate and reflects your refunds.
Pro Tip! Use an excel formula! If you want to tell Excel to use the number from column L if it is there, but if it is not, use the amount from column F, insert a function. Create a new column (anywhere you'd like), and click in the empty square and "insert"/"function". The function is:
[ignore the N and G column letters above. I inserted my blank column, which meant that the letters of my columns changed. Click on whatever cell is correspondent with "adjusted fees" (orig. column L), and the value if true should also be that cell. The value if false should be the value in "fees" (orig. column F)]
At this point you should be fairly home free with this process. Remember, this seems really confusing but at the end of the day you really just need these MAIN things:
1) Income numbers (ideally, to match the 1099-K)
2) Expense numbers (in these categories)
- "refunds issued" - get a total from column Z
- payment processing fees - get an accurate total from column F (add in any totals from alt. payment processors, like Paypal)
- transaction+listing fees - get a total from your Payment Account,
- shipping expenses/shipping labels - get a total from your Payment Account and any other shipping platform you use (i.e. PirateShip, Paypal, etc)
- other business related expenses - supplies, mileage log, professional fees, etc. -- think hard about what you spent on the business this year. I sometimes like to go over my credit card statements to help jog my memory
Why are the CC processing fees not a part of the marketing, shipping, transaction and listing fees on Payment Account screen? If I’m understanding this correctly we need to get those in the Etsy payment sales report. Is that correct?
Thanks so much for this explanation! I was battling making sense of the 1099 and the payment account information. It makes sense that when I take Gross Amount minus Fees from the Direct Checkout Payments report I get the amount on the 1099. But when I add the fees amount from the Direct Checkout Payments report with transaction & listing fees the numbers don’t add up. Is the fees section of the DCP Report captured in either the transaction or listing fees? It feels like it’s off anywhere from 30-120 dollars depending on the month. Does my question make sense?