Mapping Square Invoices to QBO

[StephaniePrima]

I've asked Accountant Support for help on this issue and they were unable to come up with solutions - told me to ask the Pro Advisors.

I'm doing current year clean-up in QBO for a CASH reporting non-profit Grange. Part of their member benefits is for members to sell their foods and hand-made goods at the Grange's Farmers Market booth. Some of their products are taxable, some not. The Grange charges the members a percentage of the sale, then pays them the net. But the Grange is covering the Square and Market fees and wants the sellers to cover them. Also, the Grange has never been paying sales tax, and somehow all those vendors' sales will need to be tracked in QBO, especially so they can be billed for the Sales Tax that the Grange didn't include in their vendor refund calculations. All sales are created in Square at the market, but Square has never been connected to QBO so the revenue is not recorded properly.

I will be using Dext e-commerce for the connection but need to set up the mapping. Currently in Square these 'member sellers' are listed as ITEMS and show as items on the sales receipts/invoices.  Should I set up each member/seller as a sales item in QBO? Then also set them up as vendors? Then export the item sales summary report from Square and upload to the Vendors in order to track the sales? And how would I track the portions of the Market Fees and payment processor fees for each Vendor?

What is the best way to go about this mess?

Thank you for any suggestions,

Stephanie Prima

[qbteachmt]

"told me to ask the Pro Advisors"

You posted under Intuit Tax Advisor. That is a tool provided by Intuit for income tax preparers to use for projections for the upcoming tax year for clients.

You seem to be lost on the internet.

You’ve come to a Peer User community for Intuit Income Tax Preparation products supporting Lacerte, ProSeries, or ProConnect Tax Online products. Please visit the QuickBooks Help site for the answer to your question.

Your sign in user info here is the same one you can use over at the QuickBooks forum.

Thanks.

[StephaniePrima]

Thank you. I tried getting into the other community but no matter how many times I signed in, it kept rejecting my login.

[qbteachmt]

It's supposed to be one Intuit ID login for everything. Perhaps you made a new one here, and that is not the one you also use for QBO, so use your QBO signin.

[StephaniePrima]

That's what was using - the Login/PW for when I sign in to QBO. The whole experience working with Intuit has been pretty frustrating.

Thank you for trying to help. I've pretty much decided what I'm going to do, as soon as I get full access to their Square account. 

Wishing you well....

[qbteachmt]

Okay, this is very dated, but here's the concept for summarizing the Farmer's Market as one sales receipt:

https://www.youtube.com/watch?v=NxYliKN0fxE

If you are summarizing, you want a named item that is reportable By Vendor. Or, you could use each Vendor Name as Customer (that's a lot more data entry) and generic items, if you need that level of reporting in QBO. In other words, you either report by Item or by Sale. Or you don't need that level of detail in QBO at all and can generically summarize by Sales Date. The points would be, don't double-track something (because who has time for that) and you can't report something you don't track. It also matters if you use QBO for the built-in sales tax functionality or do it outside and just need reporting.

You don't have to map or connect things; it isn't always the easiest way to get what you need.That's why Michelle shows a POS summary concept (Z-out). It can be X-out, too. It's just a summary tool that's better than JE.

I can't speak to the specifics of that software mapping, but many of my clients are the programmers of those types of integrated tools you are trying to use (I'm their QuickBooks expert and have a programming background) and other clients are Pro Advisors (I somehow became expert at IL sales taxes for one of them). If using Items for each vendor, then you will need to create Taxable and Nontaxable "generic sales" per vendor, at a minimum. You're also going to need discount (or negative value) items, for instance, such as one for Fees your grange hall keeps.

You also need their names as Vendor Names if you are using QBO for issuing 1099, and for the payments to them.

I would track the Square/cc terminal fee as a typical operating expense. You won't "bill" them for the sales tax or fees. It's going to be accounted for in the net you pay them. If you are not using the QBO sales tax function, then this simply is another item, such as:

Taxable Sales <== puts that tax amount into liability, sales into liability

Nontaxable Sales <== sales into liability

You send Tax liability to tax agency

You pay the Vendor from Sales liability:

Sales liability Gross for that vendor, that market date

Grange Market fee as a Negative value (credits income) posting to Revenue

Square fee recapture also is Negative (credits income) to Revenue (because Square will report it on 1099-K)

= net check to vendor

You often can assign numbers to vendors as part of your payment terminals, like Department # or Sales Register, so it depends on how many vendors you get, I suppose, as to if that is crazy or not.

I hope this review helps. I like to describe, "If you can map up, you can set it up." It might not be exactly what you need, but it gives you some ideas of how to use the tools.

You should still be able to sign in at the QB help with your sign in used here, so try a different browser. I use FF with Intuit, not Chrome, for example.

[StephaniePrima]

Thank you so much for this detail!

I must admit, I watched the video probably 4 times and reread your post at least a dozen times, to make sure I'm following what you are saying.

I did set up all the accounts you suggested, set up 2 vendor files for each - one taxable, one non-taxable. I created a sample one-day Sale Receipt to try out this process, but I must use the detailed line-by-line Sales Receipt. Since those lines were set up in Square as ITEMS, I needed to also set those up in Revenue. I created Farmers Market Sales Taxable and Farmers Market Sales Non-Taxable revenue accounts and coded those items as SERVICE to these revenue accounts. When I tried to set them up it would only let me put in a revenue account, not a Sales Liability account. 

Should I have set the vendors up as Non-Inventory instead of Service? I think I still would have to code them to only a revenue account in QBO.

At this point, would I then export the 2 Revenue account report to an Excel file, and upload to the appropriate Vendor files as BILLS with negative amounts? Or create a JE? I'm still perplexed as to how I will get the individual sales to each vendor.... 

Thank you for your kindness,,,,

[qbteachmt]

It really depends on if you intend to use liability accounting (the same concept as an antique mall or a hair salon with booth rentals for the stylists) or put everything to revenue and consider the payments to the vendors as expense. The items all either link to one liability account ("owed to other" essentially) or to one revenue account for Sales. But you are right; some integrators don't provide much flexibility in their mapping. I worked with a motorcycle shop, that software only allowed service type items, not noninventory parts or other charge items, which really limited the reporting. So, service is fine and income is fine, but now your vendors' Gross sales amount (when you list it on their payments) is the grange's Cost of Goods Sold.

I avoid JE in QB.

As for individual sales per vendor: Your summary sales receipt can be per vendor name (as Customer, in that case) with their specific items listed. Or truly one Summary, and you list all the vendor-named "items" that were at that day's market. The point is to report on the items, to know the sales data, the taxable sales, total sales for fees, etc. You shouldn't need Excel; you should find all the reporting is in QBO. Are you using the Accountant Portal? It has more reporting functionality.

Did you ever get logged into the QBO forum?