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, or sellers' location) Origin defines how, when and where sales tax is due. 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:  ecommerce app integration with QuickBooks Online

Products and Services Items in QuickBooks Online are:

1. Inventory Products ecommerce app integration with QuickBooks Online

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) Predetermined by item being bundled.

   • 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:

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

2. Service Expenses: Cost of Sales with average cost (COS)

3. COGS/Inventory Expense Item: with average cost (COGS) *optional Vendor Name and SKU

4. Income-Sales with average sales price (taxable or non-taxable)

   add 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.

   add Quantity and Price of items in stock (*NEW import from Sales Channel or Spreadsheet Sync: Do not overwrite inventory in stock. It could constitute fraudulent asset manipulation, update prices only) For price updates use (Average Costing) for inventory updates use (inventory valuation report worksheet to compare with sales channel reports or conduct a physical count) Enter at least an average sales price and purchase/expense cost to each item to maintain GAAP matching principles

   if no bill is attached to the invoice or sales receipt

   Attach bill to invoice (from unbilled charges report) (COGS does not populate from the sales document to the income statement without it) Reconcile from a BOM (Bill of Materials) report monthly and file to My Documents > Shared folder to maintain historical records.

   I suggest that you request QBO engineers to complete the system, updating the (cost) in products and services with each successive new bill. Gear Icon > Feedback

Category Other Fees: 

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) = Credit Memos and Refund Receipts *credits (decrease) to checking, income, sales tax and inventory.

12. Vendor Credits *debit (increase) checking and apply to original bill (A/P) and correct vendor and inventory item if applicable.

13. et......

Customer Name: A critical component of any business!

NEXUS tracking

(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)

   See: Revenue Recognition for Ecommerce and Store Front Retail

4. Crypto Currency and Bitcoin

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 (undeposited funds) 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 obligation, and increase inventory on hand and is a credit (decreases) your checking balance)

Credit Memo-Return of Products and Service Items

Refund Receipt-Return of Products and Services Items

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 and produce accurate financial statements.

The accounting process is:

Estimate (optional) to PO (optional) to Bill Item or Expense Item (marked billable, attach receipt) to bank feed (transaction) matched to bill or expense, will populate the Unbilled Charges Report, adding Inventory (quantity) and COGS (amount) to your financial statements. When invoiced (add billable items 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.

My accounting preference is to GAAP accounting (matching purchase with revenue to calculate accurate Gross Profit) following correct workflows.

If you are pre-entering vendor or store receipts that will be billable to a client, use a Bill (marked billable/see unbilled charges report), then match in the bank feed (bank transactions) These bills will remain on A/P until paid, from a bank feed transaction or otherwise. (from the receipts menu upload receipt or vendor invoice and create a bill from there, mark billable if applicable)

Pre-entered bills will dictate whether the expense/purchase is for:

• Item dropdown lines for products and services purchased for resale, typically billable items sold to a client and matched to an invoice and will remain in Unbilled Charges until sold out OR

• Category dropdown lines for indirect overhead expenses, asset purchases, liability payments, and are typically non-billable. You can enter a bill or expense for COGS directly to the Income Statement, using the category field, for cash accounting transactions only   <not recommended, especially where inventory is concerned, and valuations are necessary.

• Always add, customer or job, class and location to each document for NEXUS tracking.

• The rule of COGS calculations:

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

  • An invoice (income) + bill attachment (Item: 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, and your tax liability.

  • An Invoice with only a receipt attachment may not calculate the true COGS, or COGS at all, to your income statement (from the receipt) if the price and cost are pulling from the products and services and no billable expense item is attached. Increasing gross profit income and your tax liability.

  • Sales, Inventory and COGS should be reconciled EOM.

  • Create a (BOM) monthly and save to My Accountant > Shared Documents, maintaining a historical record of Inventory, COGS and Sales.

Use caution with Excel spreadsheet sync or import from sale channel. 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 12/30/2024 Gear Icon > Feedback. You should too!

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 process with income, inventory 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 (optional) to Bill marked billable to Bank feed (bank transaction) 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 (decrease) 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 should be posted to the customer's account. especially with concern for ecommerce

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

• 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

See Also: Fifo vs Lifo vs Average Costing

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