In this article, we go through steps on how you can keep track of your Walmart sales in Quickbooks. We use Walmart as an example, but the technique can be used to book other channels as well (eBay, Amazon, Wayfair, etc.)
How to read this guide – Complex vs. Simplified Bookkeeping
- If you are looking for very comprehensive bookkeeping, review our Steps 1 to 5. Those steps will show you how to create products, track inventory, and send purchase orders to your suppliers. It will show you how to add each marketplace order separately to Quickbooks.
- Start with Step 1, but then go straight to the Simplified Accounting section if you are looking for simplified bookkeeping. This technique shows how you add transactions in bulk, keeping your earnings from marketplaces but without details about each transaction added to Quickbooks.
Please note this article was not written by an accountant and should not be considered tax advice. It is only to show one possible way to do bookkeeping using Quickbooks. Please make sure to work with a tax professional to collect and calculate your taxes correctly.
Step 1 – Create your customers/sales channels.
Keeping each buyer as a separate customer in your Quickbooks is not practical. It is best to create separate customers to be the sales channels. For example, you will create three customers: Walmart, Amazon, and eBay. Then each order from those channels will be listed together under the customer. Thanks to that, you will be able to track sales, cost of goods sold (COGS), gross margin, and net profit by sales channel.
Step 2 – Vendors/suppliers.
We also suggest creating profiles for your vendors/suppliers. This will be helpful when you send Purchase Orders to your suppliers and when you run reports to see your margins.
Step 3 – Products.
Set up your products.
If you are adding your products for the first time, you will need to activate inventory management.
This is crucial to provide SKUs that match your SKUs on marketplaces. This is an example of why you need to make sure you have consistent matching SKUs on all your marketplaces:
Step 4 – Creating a purchase order.
When you order products from your suppliers, you create a purchase order.
In this example, we order 100 units of the SKU created in the previous step. This SKU costs us $10 to purchase for this supplier.
After creating the PO, you may want to send your purchase order to the supplier. Go to the Vendor list, and click on the open PO link.
Click Send.
You will want to create a bill, click Copy to bill in your PO view.
You should not need to make any adjustments to the bill. Just save it and then click schedule online payment to use methods of payments provided by Quickbooks, or mark this bill as paid if you already paid.
As soon as you mark the bill as paid, you will see the inventory of your products increase by the number you ordered.
Step 5 – Creating an invoice.
You will insert your marketplace sales as invoices.
You may want to consult this step with your accountant, as marketplaces usually charge Sales Tax on your behalf, and Quickbooks also tries to help you with that. How you insert this information into Quickbooks is up to you, depending on how you calculate taxes. In the example below, Walmart already took care of charging taxes for the transaction, and you received $30 for selling two items.
In this case, we click save, as we do not actually send this invoice to Walmart.
You will notice that after saving this invoice, your inventory was adjusted.
Simplified Accounting
As shown above, adding each order as a separate invoice will be very time-consuming. You may want to consider adding one big invoice every two weeks using the statements generated by Walmart. You do not even need to list all the orders in such an invoice; just provide the value paid to you, taken from Walmart’s statements. Make sure to save your Walmart statements somewhere on your hard drive and neatly provide a description of your Quickbooks invoices to match them with Walmart Statements easily.
Keep in mind that you will not use Quickbooks for tracking inventory in this case, so you do not need to add your products to Quickbooks.
In the example below, we created an invoice for Sales on Walmart Marketplace for Jan 2 – Jan 20, 2021. The $129.22 is the amount sent to us from Walmart for all the sales in this period.
How to download transactions from Walmart
Data you need to insert in the Invoice creation step can be accessed from the Walmart Seller Center. Go to Payments to download your reports. Those reports are provided every two weeks, so if you decide to go with the simplified accounting, you will be adding this data to Quickbooks twice a month.
Understanding Walmart’s Reconciliation Report
Statement summary should be just enough for sellers for simplified bookkeeping. However, Reconciliation Reports are helpful for accountants and detailed bookkeeping when you insert each transaction separately to Quickbooks. Walmart reconciliation reports can be downloaded from the GeekSeller panel and from the Walmart Seller Center section Payments. Those reports contain many columns described below.
- Walmart.com Order # – Walmart Order Number
- Walmart.com Order Line # – Item number within the order
- Walmart.com PO # – Purchase Order number
- Walmart.com P.O. Line # – Item number within the Purchase Order
- Partner Order # – Partner Order Number
- Transaction Type – SALE: sale transaction REFUNDED: refund transaction ADJMNT: fees, corrections, and adjustments which will appear as credits or debits
- Transaction Date Time – Sales: when the shipment confirmation was received Refunds: when the return or refund was invoiced Adjustments: when the adjustment was issued
- Shipped Qty – Shipped quantity
- Partner Item ID – Partner’s Item ID
- Partner GTIN – Partner’s GTIN #
- Partner Item name – Partner’s item name
- Product tax code – Item’s tax code, used to calculate sales tax on the item
- Shipping tax code – Item’s tax code, used to calculate sales tax on shipping
- Gift wrap tax code – Item’s tax code, used to calculate sales tax on gift wrap
- Ship to state – 2 letter code of the state where the order was shipped; Sales transactions only
- Ship to county – Code of the county where the item was shipped
- County Code – Code of the city where the item was shipped
- Ship to city – City where the order was shipped; Sales transactions only
- Zip code – Zip code where the order was shipped
- shipping_method – Payment method used on the sale
- Total tender to / from customer – Total amount received from/refunded to the customer on sale/refund
- Payable to Partner from Sale – Total amount paid to/recouped from a partner on sale/refund
- Commission from Sale – Referral fee paid to/recouped from Walmart on sale/refund
- Commission Rate – Referral fee rate (%) on sale.
- Gross Sales Revenue – Item price + shipping charge on sale; total amount subject to referral fee
- Refunded Retail Sales – Refunded item price + shipping charge; refunds only
- Sales refund for Escalation – This column has been retired and merged into col #26 (Refunded Retail Sales)
- Gross Shipping Revenue – Shipping charge on the sale
- Gross Shipping Refunded – Shipping amount refunded
- Shipping refund for Escalation – This column has been retired and merged into col #29 (Gross Shipping Refunded)
- Net Shipping Revenue – Sum of shipping revenue on sales and refunds (col #28 + #29)
- Gross Fee Revenue – Product fee collected from customers on sale
- Gross Fee Refunded – Amount of product fee refunded
- Fee refund for Escalation – This column has been retired and merged into col #33
- Net Fee Revenue – Sum of fee revenue on sales and refunds (col #32 + #33)
- Gift Wrap Quantity – Gift-Wrap quantity
- Gross Gift-Wrap Revenue – Gift-Wrap charges collected from the customer
- Gross Gift-Wrap Refunded – The refunded amount on Gift-Wrap charges
- Gift wrap refund for Escalation – This column has been retired and merged into col #38
- Net Gift Wrap Revenue – Sum of gift-wrap revenue on sales and refunds (col #37 + #38)
- Tax on Sales Revenue – Sales tax amount on item price
- Tax on Shipping Revenue – Sales tax amount on the shipping charge
- Tax on Gift-Wrap Revenue – Sales tax amount on Gift-Wrap fee
- Tax on Fee Revenue – Sales tax amount on Product Fee
- Effective tax rate – Sales tax rate
- Tax on Refunded Sales – Refunded tax amount on item price
- Tax on Shipping Refund – Refunded tax amount on the shipping charge
- Tax on Gift-Wrap Refund – Refunded tax amount on Gift-Wrap charge
- Tax on Fee Refund – Refunded tax amount on Product Fee
- Tax on Sales refund for Escalation – This column has been retired and merged into col #46
- Tax on Shipping Refund for Escalation – This column has been retired and merged into col #47
- Tax on Gift-Wrap Refund for escalation – This column has been retired and merged into col #48
- Tax on Fee Refund for escalation – This column has been retired and merged into col #49
- Total NET Tax Collected – For sales: sum of cols #41-#44; Refunds: sum of cols #46-#49
- Tax Withheld – Y: sales tax withheld by Walmart; N: sales tax included in payment (col #22)
- Adjustment Description – Return/refund or adjustment description
- Adjustment Code – Return/refund or adjustment code
- Original Item price – Partner’s original item price before CAP program adjustment
- Original Commission Amount – Referral fee amount before CAP program adjustment
- Spec Category – Category provided by seller during ingestion
- Contract Category – Contract Category for the item sold
- Product Type – Product Type for the item sold
- Flex Commission Rule – Dynamic commission rule ID (when applicable)
- Return Reason Code – Customer given reason associated with refund/return
- Return Reason Description – Customer given reason code associated with refund/return
- Fee Withheld Flag – Y: product fee withheld by Walmart; N: product fee included in payment (col #22)
- Fulfillment Type – Walmart Fulfilled(WFS) or Seller Fulfilled
