Amazon Settlement Report
      Back to home
      1.  
      2. Connex for QuickBooks Integrations Guide
      3. Amazon Settlement Report

      Why are my Amazon settlement report orders incorrect?

      Common reasons why your Amazon settlement report in QuickBooks does not match the settlement report in Amazon.

      Problem

      In QuickBooks, your Amazon sales are incorrect. They are higher or lower than expected.

      Solutions

      Group sales and refunds by month

      If your settlement period was 11/22 - 12/8, Connex would sync the sale with a date of 12/8. This means December has November sales in QuickBooks, so your November and December sales are incorrect.

      You can instruct Connex to group sales by month. You would have two sales receipts. The first is November and the second is December:

      1. In QuickBooks, delete the summary sale.
      2. In Connex, click settings.
      3. Click order settings.
      4. Click sync orders to QuickBooks.
      5. Click summary order settings.
      6. Under group by, choose order date month: 
      7. Uncheck merge refunds with summary order.
      8. Resync your summary sale.
      9. Using your Amazon report, determine if both sales are correct.

      Here is the end result:

      Ungroup refunds and sales

      Our software combines the sales and refunds into a single order. It's easier to troubleshoot, if it's a refund versus a sales issue:

      1. In QuickBooks, delete the summary sale.
      2. Click order settings.
      3. Click sync orders to QuickBooks.
      4. Click summary order settings.
      5. As the grouping, choose payment method.
      6. Uncheck merge refunds with summary order.
      7. Resync your summary sale.
      8. Using your Amazon report, determine if both sales are correct.

      Run manual sync of one report

      Connex will combine multiple reports into one summary. If you uploaded files for the 10/27 and 11/12 deposit, then you would get one sales receipt. Here are the steps:

      1. Log into Connex.
      2. Go to settings > pending > pending transactions.
      3. Ensure the clear is clear. If not, click the clear button.
      4. Reupload your report.

      Verify you synced the right report

      Sometimes, users upload a report with a similar deposit date with the wrong amount. Here are the steps:

      1. Delete the old sale and refund, if it synced, in QuickBooks.
      2. If you use the XML file upload, right click the XML file and open with notepad.
      3. Verify the fields total amount and deposit date match the payout you are trying to sync: <TotalAmount currency="USD">39217.99</TotalAmount> <DepositDate>2020-11-13T20:15:02+00:00</DepositDate>
      4. In Connex for QuickBooks, click settings.
      5. From the drop down on the left, click Amazon.
      6. On the left settings, click pending order.
      7. Verify that pending orders is empty:
      8. Click dashboard on the top menu of Connex.
      9. Next to Amazon, click the manual upload button.
      10. Upload the XML file.
      11. Run the web connector.
         

      Grouped Items

      You use QuickBooks grouped items. The price of a grouped item is calculated by QuickBooks. If Amazon says $200 and QuickBooks says $100, then our tool will sync $100. Connex can add an amount to the sale to balance it.  To resolve this issue, click manage on our configure page. Expand orders > sync orders to QuickBooks > advanced transaction. Check fix rounding issues.

      What to do if numbers are still incorrect?

      To troubleshoot, our team must know the location of the discrepancy. We are unable to go through a report on your behalf and find the issue. Here is what we would need:

      Connex mapped quantity of 100 for SKU ABC123. We sold 98. Connex mapped sale 113-2779576-9689844 incorrectly.

      Pull the Amazon settlement report flat file. The report looks like this:

      Create a PDF copy of the QuickBooks sale. Check each section:

      1. Filter transaction-type by order, amount-type by item price and the amount-description shipping. Does the total shipping on the Connex order match Amazon? Do the same for order and refunds? Our calculation for shipping is SUM (ShippingChargeback) + SUM(Shipping).
      2. Is total tax correct? Our calculation is SUM (Tax) - SUM(Marketplace-Facilitator Tax). Tax is usually $0 on Amazon sales.
      3. Filter amount-type by ItemFees. Sum the amount column. Does the Amazon fees on the order match the QuickBooks sale? ItemFees includes Commission and FBAPerUnitFulfillmentFee.
      4. Connex groups products by SKU and amount paid. If item 123 was sold for $10 and then for $11, you would see two separate lines in QuickBooks. Filter the spreadsheet by the field transaction-type of order and filter the amount-description by the phase principal. Does the total of refunds of products match the total refunded products in QuickBooks?
      5. Filter the spreadsheet by the field transaction-type and filter the amount-description by the phase principal. Does the total of refunds of products match the total refunded products in QuickBooks? If not, ensure Connex properly calculated the quantity of items refunded. Sum all the refunds in QuickBooks versus the spreadsheet: