Mapping Marketplace Facilitator and Payment Processors to the undeposited funds account is an essential accounting practice that serves several important purposes. Undeposited funds is a temporary (other asset) holding account within accounting software, where funds are recorded before they are deposited into the company's operating bank account (funds in transit). The undeposited funds account should be reconciled monthly to the Income Statement. As of 12/29/2024 QBO removed this account from the reconciliation window. Use reports for reconciliation. I urge you to submit feedback to the software engineers, Gear Icon > Feedback
Whether you are a restauranter, service business or an online or offline retail business. I can assist you in daily, weekly or monthly revenue and inventory reconciliation. Clean up and organize your sales into valuable, manageable data; stay in compliance with sales tax obligations, and maintain GAAP compliance.
QuickBooks Online provides the software. I provide expert guidance and support. Contact me or Schedule a 30 minute free consultation
Below is a list of reasons why this practice is important:
Accurate Cash Flow Reporting:
By using the undeposited funds account, you can accurately reflect your company's cash flow. It separates the revenue received from the time it is earned or collected from customers to the time it is deposited into the bank.
Matching Revenue with Bank Deposits: < Required for QuickBooks Online
Marketplace facilitators or merchant processors often aggregate payments from multiple days into a single deposit to the company's bank account. (This accounting practice should be avoided. Sales terminals should be batched daily and deposited by the processor to your account accordingly. If not, you should file a complaint: PCI Security Standards Council – Protect Payment Data with Industry-driven Security Standards, Training, and Programs
Mapping revenue to undeposited funds helps ensure that the accounting of sales records match the actual bank deposits, while also ensuring Sales Tax and Nexus tracking by EOM.
Transaction Clarity:
It provides clarity in your financial records by clearly showing that certain funds have been collected but not yet deposited into the bank. This distinction helps in understanding the liquidity position of the business.
Bank Reconciliation:
The undeposited funds account facilitates the reconciliation process. Reconciling the undeposited funds account with bank statements helps identify any discrepancies between recorded transactions and actual deposits. When reconciling your bank statements, it is essential to have accurate records that reflect the timing and amount of deposits. By using the undeposited funds account, you can easily reconcile your accounting records with the bank statement. Journal Entries should be used sparingly and avoided in sales tax reconciliation.
Segregation of Duties:
Separating the collection of funds from the actual deposit into the bank account is a good internal control. It allows for a segregation of duties, reducing the risk of errors or fraudulent activities.
Timing Differences:
Sometimes there may be a delay between the time a sale is made and the time funds are deposited into the bank account. The undeposited funds account helps in tracking this timing difference accurately.
Financial Reporting:
Accurate revenue recognition is crucial for financial reporting. Mapping revenue to undeposited funds ensures that revenue is recognized when it is earned, aligning with accounting principles.
Compliance:
Following best accounting practices and maintaining accurate records is important for regulatory compliance and financial transparency. Properly accounting for undeposited funds contributes to the overall accuracy and reliability of financial statements. Sales Tax obligations, and Revenue reconciliation will align your records to your 1099K at year end.
In summary, mapping revenue to the undeposited funds account is a valuable accounting practice that enhances accuracy, transparency, and control over a company's financial transactions, especially when dealing with transactions processed through marketplace facilitators or merchant processors.
Monthly Reconciliation:
What they are doing now and why?
These are not all service items, nor income and sales tax is not an expense. Deposits made to a clearing account will not match bank feed transactions!
Payment Processor (NOT LIABLE TO PAY SALES TAX) If not mapped to undeposited funds, payout deposits from these sources will be an "adjusted" gross sales; sales less fees, *plus sales tax collected and any shipping income/deductions, discounts, chargebacks and so forth.
Marketplace Facilitator (LIABLE FOR SALES TAX PAYMENTS) If deposits are not mapped to undeposited funds, deposits from these sources will include "adjusted gross sales"; sales less fees, refunds, allowances, discounts, shipping expense, as well as, sales tax, and plus shipping income.
Monthly Reconciliation Documents: Request a Monthly Transaction History Report (CSV or Excel): a detailed list of the transactions that occurred in the seller account during a specific date range which includes, gross income, net income, merchant fees, sales tax collected/paid and any shipping income/deductions, discounts, chargebacks, and allowances, along with and any other statements with a beginning and ending balance retained by the merchant, as well as, a current inventory balance to ADJ COGS on a monthly basis, if applicable and the customer data base for easy Excel Spreadsheet sync, for your records and future marketing data.
What should they be doing? This: https://www.bookkeepingbusinessonline.com/post/integrating-your-ecommerce-app-with-quickbooks-online-a-how-to-guide-for-marketplace-facilitators-a
Types of Marketplace Facilitators and Payment Processors:
Quickbooks Payments: Payment Processor Utilizes an invoice or a sales receipt, emailed to the customer. Sales tax is check marked on the document (according to your sales tax setup and products and services setup "SEE THE MATH") and categorized to the balance sheet, therefore the gross deposit is correct and no adjustment for sales tax is necessary. Fees are automatically distributed to merchant fees expense. With Autopay: available when the client sets up a recurring invoice for their customers. Autopay isn’t available for invoices that recur daily or are over $5,000. Changes made to the amount on the recurring invoice cancels the Autopay feature and a new recurring invoice must be created and accepted by the client.
GoPayment: Payment Processor An invoice or sales receipt is generated from client input and sales tax selected, no adjustment for sales tax to the gross deposit is necessary. Fees are automatically distributed to merchant fees expense.
Z tape Daily Register Sales: Payment Processor
Sales tax is check marked and categorized to the balance sheet on a Daily Sales Receipt, therefore the gross deposit is correct, and no adjustment is necessary, except for processing fees and any other deductions which can be added to the Z tape checkout. *Consider defining name of payment processor and using tagging or class distinction rather than generic MC, VISA, Discover
Amazon: Marketplace Facilitator Amazon Seller Central, Create a Date Range Report, Create an Order History Report, Business Analytics, Marketplace Tax Collection, Custom Report Builder
Ebay: Marketplace Facilitator
Tax Invoice: The Tax invoice shows you the applicable taxes on fees already deducted from your Available, Processing, and On hold funds. In other words this is the report that will give you the amount of fees charged and taxed.
Financial Summary: Is a summary of selling activity and a detailed view of transactions, including information on claims, refunds, payment disputes, payouts and more for the month.
Transaction report: (CSV) shows details of your transactions, including transaction date, order number, buyer name, payout date, fees, and other information.
1099-K detailed report: provides a detailed breakdown of all unadjusted gross payment transactions.
In jurisdictions where eBay is required to collect Internet Sales Tax from buyers, order totals sent for processing will reflect the gross order amount inclusive of tax.
Etsy Marketplace Facilitator: https://www.etsy.com/seller-handbook/article/321914904041
Platform Purple: Video Streaming https://go.platformpurple.com/support/i_1654338684156_3nmGJveo_reindeer
Afterpay: Merchant Facilitator https://help.business.afterpay.com/hc/en-us
PayPal:Payment Processor is an American multinational financial technology company operating an online payments system in the majority of countries that support online money transfers Bank Statement and Transaction History
Shopify: Payment Processor Shopify is a specialized ecommerce platform and not considered a marketplace facilitator Shopify Reports
Stripe: Payment Processor primarily offers payment processing software and application programming interfaces for e-commerce websites and mobile applications Stripe Reports and https://support.stripe.com/questions/tax-filing-and-remittance Tax Configuration https://stripe.com/legal/tax
Square (SquareUp):Payment Processor is a payments system developed by Block, Inc. Square is a payments platform Square Reports
**NEW** https://squareup.com/us/en/banking https://squareup.com/us/en/app-marketplace/app/quickbooksonline
Affirm: Payment Processor https://businesshub.affirm.com/hc/en-us/articles/4418064935188-Settlement-Reports the company operates as a financial lender of installment loans for consumers to use at the point of sale to finance a purchase.
Walmart: Marketplace Facilitator https://sellerhelp.walmart.com/s/guide?language=en_US&article=000006444
Snyder: Payment Processor https://synder.com/help/ https://synder.com/industry/synderbooks/
Uber Eats: Marketplace Facilitator https://www.uber.com/us/en/drive/tax-information/
Door Dash: Marketplace Facilitator: https://help.doordash.com/dashers/s/article/Common-Dasher-Tax-Questions?language=en_US
Instacart: Marketplace Facilitator Instacart Company | Tax Policy
Cash App: Payment Processor https://cash.app/help/5640-tax-reporting-cash-app
Venmo: Payment Processor https://venmo.com/
AirBnB: Marketplace Facilitator https://www.airbnb.com/host/homes?from_footer=1
Xero: Payment Processor
VRBO: Marketplace Facilitator https://help.vrbo.com/articles/How-do-taxes-work-on-HomeAway Stax: Payment Processor https://support.fattmerchant.com/stax-pay-sales-tax
Apple Pay: Payment Processors are First Data, TSYS, Chase Paymentech, Elavon, Heartland Payment Systems, Vantiv
Helcim: Payment Processor: Sales Tax Simplified With Helcim Global Settings
Zelle: Payment Custodian (Money Transmitter) No 1099K required. Remember: just because Zelle isn't required to send 1099-K forms, that doesn't mean you don’t have to pay taxes on your business income.
It is important to note: The Seller is always obligated to transmit sales tax to the state on behalf of the buyer. Don't let your obligation to the accounting slip through the cracks. drop shipping, Tax Exempt sales are all factored in the Sellers obligations. Know your position, what has or has not been paid! Prevent sales tax audits or be prepared with accurate data. Reconcile your income and sales tax! While economic nexus is important, make sure you don’t forget about physical nexus. A physical presence is often thought of as an office location, but hiring employees out-of-state, traveling into another state for business meetings or events, or exhibiting at trade shows can establish a physical presence where you previously didn’t have one. Nexus should be examined on a monthly basis to minimize your sales tax obligation. Suggestion: Obtain a Certified Legal Determination Letter from an Attorney or Sales Tax Expert.
*Sales receipts are cash transactions and sales tax on these transactions are due and payable even if the funds are undeposited, regardless of filing method.
The process is similar for all Accounts Receivable (A/R) Transactions. I can assist you in automating this process on a daily, weekly or monthly basis to connect the automated sales tax module in QuickBooks Online and calculate accurate gross income on the profit and loss statement. Sales Tax will be accurately reported on the state tax filing form, and the sales tax liability on the balance sheet will reflect correct data. Contact Bookkeeping Business Online for assistance
See also: A/R Workflow and Undeposited Funds Managing Accounts Receivable
Customization of this process depends on the operating standards and procedures of the business. Careful planning is required to correctly determine the products and services delivered and their respective income and expense mapping, along with timing of deferral of revenue and prepaid bills or expenses and sales tax obligations.
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 Indirect Overhead Expenses.
Sales, Inventory and COGS should be reconciled EOM.
Category line items are reserved for indirect overhead expenses.
Preparation Steps:
if you have multiple sales channels, multiple job phases, or multiple service segments, map each to the respective (class), then to 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.
Sort the sales documents by class setup from Accounts Settings, per document or each line of a transaction. For large projects it is wise to choose the per document selection.
Add custom fields to your forms from Gear Icon > Custom Fields to further specify meaningful information on the document such as Project Manager, Sales Rep, et...
Location is utilized for NEXUS ship from (origin) information (business or job address, seller office, warehouse, inventory location, employee location, fair or tradeshow, drop shipper, et...) The location field specifies where and how you will submit sales taxes.
Chart of Accounts setup should follow business type and products and services mapped to the income and COGS fields appropriately.
Refunds, Discounts, Shipping, Fees and Sales Tax require special consideration depending on how the sales is processed, and if the item is taxable. You will need to set up sales tax for each of your products and services items. Default tax to location (origin-Ship from) or search for the specific item you are selling, and you will get the message, "this item might have special tax considerations. Select Edit sales tax to view our recommendation and we'll make sure it's taxed correctly. We'll apply sales tax based on product or service and location." Because sales tax liabilities are always the seller's legal obligation, I recommend confirming your licensing and other obligations by checking with the Department of Revenue and State and Local Statutes
Add any price rules that will apply to specific customers for specific products and services.
Add vendors and customers, with at least an email address. If necessary, mark as exempt from the vendor or customer list and upload a certificate of exemption to their profile. Mark all vendors 1099 eligible, even if they are not, they can complete their vendor profile. If not eligible upload confirmation documentation to their profile.
Item Mapping:
if you have multiple sales channels map each item to the respective sales channel income and expense on the profit and loss.
Sort to the sales channel by 'class' setup from Accounts Settings, by line or transaction.
Add custom fields to your forms from Gear Icon > Custom Fields to further specify meaningful information on the documents such as Project Mgr, Sales Rep, et...
Locations are utilized for NEXUS ship from information (origin of sale)
Chart of Accounts setup should follow business type
Refunds, Discounts, Shipping, Fees and Sales Tax require special consideration depending on how the sales is processed, and if the item is taxable. You will set up sales tax in the Products and Services items individually. *Including sales tax according to laws in your jurisdiction. Note this should follow (destination) ship to. If not select default.
Tax Exemptions are set up in the Customer setup. Attach exemption certificates.
GAAP Transactions (Income = COGS or Billable Expense matching)
COGS will not calculate properly on the Income statement without a sales price and purchase cost listed for the items on the QBO products and services list.
If the items are for:
COS Purchases (Non-Inventory), These items would be mapped to (income account=Billable Income for Invoices, expense account=COS Billable Purchases Bills/Expenses)
If the items are:
Inventory Items (Inventory) These items would be mapped to (income=Sales for Invoices, expense=COGS Inventory for Bills/Expenses) Must have cost and sales price for each inventory item, update with Spreadsheet Sync. Don't overwrite inventory (the most current bill for the inventory sold is the correct COGS) despite FIFO
If the items are:
COS (Service) Item These items would be mapped to (income account=Billable Service for Invoices, expense account=COS Billable Service Bills/Expenses)
If the items are:
Service Vendor Items (Service) These items would be mapped to (income=Billable labor income, expense=Billable labor expense COS Labor) (use for subcontractors)
If the items are:
Time Item Setup (Service) These items would be mapped to (income=Billable time, expense=Billable time COS Labor) *turn on in Accounts and Settings > Time (use for employees)
If the items are:
Bundle Items (Multi-Inventory) These items would be mapped to (income=Sales for Invoices, expense=COGS for Bills/Expenses add cost and sales price for each inventory item) *Estimated sales prices must match bundle prices for client specific estimated/invoicing. Bundles can be used for estimating common projects (eg: kitchens, bathrooms, et...) If you are tracking inventory movement (eg: manufacturing; use finished price)
If these items are prepaid the following applies:
Prepaid Expenses (any billable product or service): Prepaids are normally expenses carrying over for 12 months. However, if the business is prepaying expenses on behalf a customer several months in advance set up the Prepaid Other Current Asset account. This process will follow GAAP matching principles
(1) Create a Bill to the vendor being paid, categorized to the Prepaid Expense Current Asset (increase Prepaid) and pay the Bill. (enter Client/Customer name in the appropriate field and note the Expense Item in the description line, but do NOT mark as billable)
(2) Immediately after payment is made, create an opposing Bill dated to the revenue deferral date or invoice date, with the actual expense or COGS Item, previously prepaid, and mark as billable LESS (-) Prepaid Expense Current Asset category (decrease Prepaid NOT marked billable.)
This net zero transaction does not affect A/P since the Bill has been paid. It reduces the Prepaid Expense Current Asset on the revenue deferral date, or invoice date and marks the expense to be billed creating a GAAP compliant transaction.
If the expenses are billable the following applies:
Billable Expenses (Any billable product or service): If you are using markup and have turned on markup in Accounts and Settings there are two ways to proceed:
(1) items can be marked as Billable on an Estimate, and markup calculated. If Invoicing directly from an estimate, with already marked up items, when the bills arrive in the bank feed, the field for the client is entered on a bill or expense but they are NOT marked billable. When earned revenue is realized directly from the estimate/invoice, the markup is already calculated and the difference on the Income Statement is the Income less the bill fielded for the client = Gross Profit/Net Income.
(2) If the items are marked up directly from the actual expense or bill, and subsequently invoiced, markup will appear as a separate Income line item on the Income Statement. Billable Income and Billable Expense line items should match EOM. (typical of Cost-Plus Billing)
Do not duplicate items from the original estimate, that have already been converted to an invoice. Billing from the actual bills and expenses are reserved for Un-Invoiced Charges or Change Orders when working from an Estimate with markup included.
(****Caution should be taken! Failure to mark an expense transaction billable or duplications can be tedious to reconcile EOM****)
Enter a baseline sales prices and purchase prices (markup/margin) for each item. Spreadsheet sync can help with the management of large products and services lists.
Caution should be taken when overwriting inventory quantities or when updating the purchase price and sales price. The most recent Bill or Invoice for a particular product is the correct COGS calculation in relation to the earned revenue. (GAAP income=expense or COGS matching)
Comments