Integrating Your Ecommerce App with QuickBooks Online: A How-To Guide for Marketplace Facilitators and Payment Processors "Sellers Data Gold"

Ecommerce app mapping to QuickBooks Online: What every seller needs to know.

if you have multiple sales channels, multiple job phases, or multiple service segments, map each item to the respective (class), then a product and service income category type on the chart of accounts. This ensures your Income Statement is not complicated by multiple income line-item categories for each segment (class) and customer. Report filtering is utilized to break down class, location and customer.

Location: in QuickBooks Online - Origin is (warehouse, inventory, store, business office, employees, tradeshow, sellers' location)

Sales Tax NEXUS: QuickBooks first checks the sales document for Ship to (destination) buyer's location, then Ship From (origin) sellers' location

States that follow an origin-based sales tax system (charge at the rate of the seller's location) include:

Origin Based Sale Tax System	NOMAD States-No Sales Tax
Arizona	New Hampshire
Illinois	Oregon
Mississippi	Montana
Missouri	Alaska
New Mexico	Delaware
Ohio
Pennsylvania
Tennessee
Texas
Utah

Structuring Ecommerce Sales:

Items: 

Products and Services Items in QuickBooks Online are:

1. Inventory Products

2. Non-Inventory Products *for Shipping Income (check local jurisdiction laws)

3. Services

4. Bundles (multi-product)

   Some notes about bundles:

   • Bundle Item (Multi-Inventory Items) (Income = Sales for Invoices, and the Purchase/Expense = COGS for Bills/Expenses)

   • Bundles can have up to 50 items (last I checked with QBO and requested an increase).

     (The National Association of Home Builders estimates that over 3,000 components are used in constructing a house, in 6-10 Phases. Therefore, a Job Phase may include as many as 6-10 bundles.)

   • There is no additional markup or change in price for bundles. (Bundles aren’t assemblies) Rather the price of a bundle = The total price of all its finished goods inventory items. (Inventory Lot, for instance, a complete kitchen)

   • Bundles can be used for estimating projects and job phases (e.g.: kitchens, bathrooms, et...) by using Job Phase Bundles when estimating. This process is GAAP compliant and conducive to efficient and accurate Inventory and Project Management (and associated accounting collaboration) as well as planning and analysis. For a complete description of the use of Bundles to track inventory in construction and manufacturing see: (Manufacturing Assemblies and Construction Contracting)

     
     

Products and Services Items are associated with a (select one or both, income and expense/purchase)

1. Inventory Assets (populated from PO to Bill *mark billable)

2. Expense/COGS or COS-Purchase with Price (COGS or COS) *optional Vendor Name and SKU

3. Income-Sales with Price (taxable or non-taxable)

4. Sales Tax (Taxable based on location of the buyer (ship to-destination) or default to (origin-based ship from), or custom rate from the sales tax module.

5. Quantity of items in stock (Do not overwrite inventory in stock. It could constitute fraudulent asset manipulation, update prices only)

6. And enter at least a standard sales price and purchase price, attach bill to invoice. (COGS does not populate from the sales document without it) Reconcile from a BOM (Bill of Materials Report Monthly and file to My Documents > Shared folder to maintain historical records.

Category Other Fees: (or Service Expense Item: Cost of Sales)

Ecommerce Providers setup:

(Turn on per line class, or per document for your sales documents from the Accounts and Settings Menu > Company > Categories > Track Classes, assign one to each row or the entire sales document. Ecommerce Providers are set up as classes and fees will be associated with that class on your sales document for the types of products and service fees and expenses or shipping income)

1. Payment Processing Fees

2. Platform or Marketplace Fees

3. Shipping Fees Expense

4. Shipping Fees Income

5. Currency Conversion Fees

6. Return and Refund Fees

7. Subscription or Membership Fees

8. Integration Fees

9. Advertising Fees

10. Storage Fees

11. Discounts and Refunds (contra income)

12. et......

Customer Name: A critical component of any business

(Tax Exempt Requires a Tax Exempt or Resale Certificate)

1.     Revenue Generation

2.     Profitability

3.     Brand Reputation

4.     Market Feedback

5.     Brand Loyalty

6.     Business Growth

7.     Adaptability

8.     Competitive Advantage

9.     Repeat Business

10.  Customer-Centric Culture

Payment Method:

1. Cash

2. Credit Card

3. On Account (Accounts Receivable/Use an Invoice, not Sales Receipt)

4. Crypto Currency

Document Mapping:

1.    Data import to a Sales Receipt, deposited to undeposited funds, per sale

(or per day: batch close)

2.    Followed by a deposit to your bank account and bank feed matching!

*Sale receipts-sales tax is due on receipt, even if funds are undeposited. Always deposit to undeposited funds and match the bank feed transactions from the Ecommerce provider. Reconcile undeposited fund monthly.

QuickBooks Online Sales Receipt

3. If Products and Service Items are returned your payment processor should match the transaction to a Credit Memo or otherwise a Refund Receipt should be issued directly to your customer following the same steps as above on the sales receipt and applied to the correct customer transaction. A refund or credit memo issued from your bank account will appear and can be matched in the bank feed to the account specified on the refund receipt. (This will reduce any sales tax, and increase inventory on hand and is a credit to your checking)

QuickBooks Online Credit Memo-Return of Products and Service Items

Refund Receipt

Follow Consistent Accounting Procedures:

Accounting in QuickBooks follows a specific and structured process. As an accrual-based system that complies with GAAP standards, following consistent accounting procedures is imperative. Proper setup of products and services, including standard sales and purchase prices, is essential for calculating COGS when no bill is linked to an invoice. The primary goal of GAAP accounting is to align expenses and bills with the revenue generated from invoices, creating a clear audit trail to connect all transactions seamlessly.

The accounting process is:

Estimate (optional) to PO (optional) to Bill Item or Expense (marked billable, attach receipt) to bank feed (expense) matched to bill, will populate the Unbilled Charges Report adding Inventory (quantity) and COGS (amount) to your financial statements. When invoiced (add bills to the invoice from the suggested transaction pop out drawer). This process Bill + Invoice is critical for correct accounting of Sales, COGS and Inventory, is GAAP Compliant and produces accurate financial statements.

 The Importance of Establishing and Following Consistent Accounting Procedures: Fraud Prevention

• The rule of COGS calculations:

  • An invoice (income) + bill attachment (COGS) with markup = gross profit.

  • An invoice (income) + bill attachment (COGS) and no markup is a reimbursement = zero gross profit.  

  • An invoice with no bill or expense attachment will not calculate COGS (if not stated in your products and services), increasing your stated gross profit-income.

  • An Invoice with only a receipt attachment may not calculate the true COGS to your income statement (from the receipt) if the price and cost are pulling from the Products and Services and no billable expense is attached.

  • Use the Item drop down on an invoice for billable Products and Services and the Category drop down for non-billable General Direct Overhead Expenses.

  • Sales, Inventory and COGS should be reconciled EOM.

  • Category line items are reserved for direct overhead expenses.

On 12/30/2024 I requested that the sales receipts include the same (suggested transactions) drop down list, as is available to an invoice so that the Unbilled Charges can be attached for correct inventory and COGS accounting. Gear Icon > Feedback. You should too!

Use caution with Excel spreadsheet sync. Do not overwrite quantities on hand and be sure to maintain a bill of materials (BOM) production report at regular intervals to My Accountant > Shared Documents. This report is necessary for reconciliation and serves as a historical record of Sales, Inventory and COGS. (avoid the potential for fraudulent asset manipulation, and inventory theft)

My open letter to Intuit QuickBooks and Marketplace Facilitators and Providers

Journal Entry accounting for ecommerce is inefficient and inappropriate, providing very little useful information to a retailer and does not reflect appropriate accounting standards. Ecommerce mapping should follow QuickBooks accounting processing with income, and cogs recorded correctly. *See above

Please review the correct process of data import.

• Ecommerce sales should follow the same procedure: Estimate (optional) to PO to Bill marked billable to Bank feed (purchase expense) and then a received unpaid Bill, but still not Invoiced, will show on the Unbilled Charges Report to be Invoiced (in the case of Ecommerce, to a sales receipt) When the sales receipts are paid, the attached bill and sales receipt will distribute the inventory asset to cogs and the income to the Income Statement. My request to Intuit was to please provide the same (suggested transactions) drop down list to sales receipts, as is available to an invoice so that the Unbilled Charges can be attached for correct inventory accounting.

• The undeposited funds (other current asset) has recently been removed from the reconciliation screen. It is necessary to map transactions to the undeposited funds account, match in the bank feed and to reconcile EOM. Please return the undeposited funds account to the reconciliation screen.

• Customer Credit Memos and Refund Receipts. Both should be enabled to be matched in the bank feed. Typically, a seller will process the transaction, entering either a credit memo or refund receipts. Both are credits to the checking account and need to be matched and recorded in the bank feed, reducing income and sales tax liability, as well as increasing inventory on hand, and be posted to the customer's account. especially with concern for ecommerce

• Vendor Credits: These debits to the checking account need to be matched in the bank feed and applied to A/P and the appropriate vendor. Please enable bank feed matching.

• Inventory adjustment receipts need to include a customer name and an option to be marked billable, accounting for the loss of inventory.

• Sales Tax Adjustments need, class, location and customer fields.

See Also: Understanding Your Form 1099K

See Also:

Mapping Marketplace Facilitator and Payment Processors Revenue to the Undeposited Funds Account

Integrating Your Ecommerce App with QuickBooks Online: A How-To Guide for Marketplace Facilitators and Payment Processors