I have researched various forum posts and analysed all the reporting functions within shopify, but I still can't seem to find the correct information. I have a Shopify unlimited account so have access to all reporting features. All I want is the actual amount of funds I have received and the tax I need to remit (I am an Australian based e-commerce store - so 10% GST is applicable).
My tax settings are simple - taxes are included in my prices & I don't charge taxes on shipping. I do have international orders and I'm aware Shopify is not set up to remove the 10% GST component to overseas purchasers.
I go to Orders Report - By Month & I select from the measures menu "Total Subtotal", "Total Shipping" & "Total Taxes". "Total Subtotal" + "Total Shipping" = "Total Sales". That's OK. Then I check the taxes column. As taxes are not being calculated on shipping I take 1/11 (i.e. 10% GST) of "Total Subtotal" only. Every time, no matter which month I analyse, my tax is MORE than one eleventh of Total Subtotal? In reality, it should be less as overseas purchasers don't need to remit GST. I don't understand! I have toggled the "paid" Payment Status on and off but the tax calculations are just plain wrong. SO my first question is: why is the "Taxes" column higher than 1/11 of the "Total Subtotal" column?
OK, let's approach this more manually. Now all I want is the actual money I have received. That's it. Then I will take one eleventh of that to work out the GST owing.
So I go to the Payment Method report. It shows "Total Payments" and "Total Refunds" for each payment method. I chose the same date range and try to compare this to my orders report above. I note that the Transaction Count in the Payment Method report matches the Order Count in the Orders Report if I deduct gift card transactions from the total.... BUT, I have to INCLUDE the refunded transactions to match the order count total in the Orders Report, even though the payment status is set to "paid" in the Orders Report. What???? I thought the "paid" setting in the orders report excludes partially refunded and refunded orders! Why does the paid only orders Order Count in the Orders Report equal the transactions Count in the Payment Method Report only when you include partially refunded and refunded transactions? I assume there is a difference btween "Orders" and "Transactions"? If there is, why would git cards not be included in the Orders Count. My 2nd question: how does the transaction count in the ayment Method rport relate to the Orders count in the Orders Report?
Additonally, in the payments report it appears I have to MANUALLY deduct Total Refunds from Total Payments to arrive at a figure which tells me how much money I should have received. Even then I'm not confident this is accurate due to the Order Count issue above.
Let's make this really simple. My 3rd question: I only want 1 thing - can someone please tell me the most accurate way to determine how much money have I receveived for a given period (as opposed to going through my bank statement).
You have shared a valid concern and a real basic accounting requirement to understand your bottom line numbers. The best practice in the shopify community and in eCommerce in general is to use a top accounting software like Xero or Shopify to get to the number you asked > How much money are you actially making and how much you owe to each tax agency.
Shopify natively provides broad sales figures and as you have already experienced has a complex calculation of including/excluding gift cards, discounts, refunmds etc w.r.t closed or open orders. It is pretty frustrating.
You can make it easy to get the bottom line accounting done by using a software like Xero/QuickBooks Online. these softwares have built in templated reports to calculate Sales Tax liability, profit and loss, balance sheet, inventory valuation etc.
Here's a blog I wrote on 5 accounting reports every eCommerce business needs. I have shared report templates and how to calculate the numbers you were enquiring.
I understand from your question that it looks overwhelming at first when you look at the numbers and when it is difficult to get the actual income in Shopify reports. I do hope the above resource helps you get a better picture. Feel free to reply if you have any more specific questions
Here is the work around we have implemented without having to resort to spending more money on third parties for such a simple matter (also using unlimited plan)
- to get refunded orders out of your total sales as reported by Shopify you have to refund the order and then cancel the order. All canceled orders are removed from Shopify calcuation of Product Sales and also tracked through Order reports for reconciliation. Doing this also will adjust the tax numbers within the Order Reports to match actual sales as opposed to including the phantom sales related to returns.
- if an order is a partial refund you have to refund the entire order because only entire orders can ben canceled. You cannot cancel part of an order and without canceling an order you cannot get accurate Products Sales or Order reports. If a partial refund, refund and cancel entire order and then create a new order for what was not returned.
- Need to distinguish between same day refunds, prior day refunds and cross over report period refunds. Shopify in its infinite wisdom does not believe in reporting refunds on the day they occur. Rather, they go back in time and change historical facts. Meaning that if you run daily reports on sales, a refund involving an order that originated on a prior day will not show up as a negative sales event on the day of the refund; rather shopify will go back in time and change what already happened. This is a big issue if you pay the tax man daily and/or if a refund involves an order that strattles an audit cycle. Therefore, you need to tag all refunds with a specific notation as not same day refund so you can make adjustments to tax liability. If you dont do this then you will be overpaying your taxes. For instance, order on day 10 and you pay your GST to the governemnt. on day 20 the customer returns the item. Your tax report in Orders will not be adjusted downward for taxes due on day 20 for the refund. Instead shopify will reduce your tax liability for day 10. But you already paid those taxes. So you need to manually note this and adjust downward your tax paid on day 20 for this cross over refund event.
- gift card - we are not using because of the double counting. Sell a gift card is recorded as a sale. Redemption of gift card is recorded as a sale. I am told they are fixing this such that gift card sales will not be recorded as a sale going forward. But has not been released yet.
I am not aware that any third party accounting software fixes these issus because the shopify data shared does not lend itself for such third parties to correct these basic accounting features. I am told Shopify is fixing these basic issues, that they were originally not implemented in part because of not wanting to deal with negative numbers (a refund is a negative order event and if the only thing that happened in a day then the store would have negative sales for that day and negative taxes, etc and their database setup did not work well with negative numbers or simply an aversion to negative numbers for some strange reason)
Product Sales reports do not include any discounts no matter what (for now). This is because Shopify does not allocate discounts to specific products in their database notwithstanding the fact product discounts can be executed by merchant. It is a database management issue from when software was created and requires a major overhaul to fix. So all you can do is within the Order Reports create a report that tracks total discounts for your period and account for it in any excel generated report you make. Ofcourse all Order Reports do adjust for discounts thus any difference in Product Sales and Order Sales should be discounts if you follow the work around above with respect to refunds/exchanges (exchanges are effectively a refund followed by a new sale).