USA E-Commerce Bookkeeping Workflows

[stephaniecrocker]

I am in the process of setting up a better bookkeeping workflow for my client who has a shopify retail store and am in need of some technical guidance. QBO has very little support on this!
- two sales channels
1. shopify (99% retail, 1% wholesale) the wholesale is extractable through a customer tag in shopify. client chose this path because they did not want to manage two inventory systems. this system is for their smaller wholesale clients and they are fine with higher merchant fees in exchange for not having to handle these sales transactions in QBO since the client travels often.
2. quickbooks online. to manage transactions for larger wholesale clients.

We are trying to connect their essential data points more efficiently so we can have more accurate reporting, namely
1. sales
2. discounts
3. sales tax (done fairly well in Shopify)
4. shipping costs
5. merchant fees
6. cost of goods sold
7. inventory

Their previous bookkeeper was only capturing sales. For 2022, we were able to get closer to our goal by entering a month end journal entry, but for 2023, we are trying to do better. The client has not been tracking their inventory assets (everything has been posting to COGS which understates their inventory and overstates their COGS for a period!) The client has also been filing on a cash basis which does not make sense for their scale and complexity. I have been researching and trying multiple solutions and would love the guidance of someone who really understands e-commerce.

Here's what we've tried so far:
1. manual journal entry. too complicated to capture the timing difference between sales and payouts.
2. A2X to move shopify sales data into QBO. this system works pretty well; however inventory and COGS would have to be adjusted manually at month end and inventory would be managed by a spreadsheet which would not "talk" to Shopify. I also don't necessarily like how daily transactions happen in a journal entry.
3. Quickbooks built in commerce feature. Ugh. don't get me started on this one. QB has ZERO support on this feature in their own software. A few months ago, I did try to integrate this feature into their QB; however it added multiple accounts to the COA so I'm hesitant to try this again without certainty it will work for our needs. There are no user guides and I was told by their tech support to "start a free trial" to tryout the functionality; however I cannot truly test this as I cannot have their bank connected to two QB files. I also tried the QBO Test drive and that does NOT allow use of their Commerce feature. I do like the "built in" aspect of this, but do not like the lack of support and "canned" sales responses from the QB tech team.
4. I am currently considering using Shopventory to manage their store inventory; however the syncing with QBO is not ideal. I may try using QBO commerce to capture sales and Shopventory to manage inventory, but again, I don't feel comfortable with the QBO commerce solution. I could try A2X for the sales info and Shopventory for the inventory.
5. Fifth option?

So, I'm left still at square one, so close and yet so far and cannot seem to find that tech guru out there who really knows their stuff!

 

[mohsinmkg23]

I think A2X is your best bet. Their limitations are still manageable. You can't do everything automatically when you are using an off-the-shelf software like QBO. If they are quite big, maybe they can invest in someone building a custom integration for this?

I also think A2X approach regarding JEs is fine, it's pretty common approach used in shopify,square, paypal sales. Otherwise your QBO would become too polluted.

 

[stephaniecrocker]

Thanks so much. After trying a few solutions, I do agree that A2X is the best approach. I began implementing A2X and although the client's workflow isn't ideal (they sometimes refund shipping in Shopify which maps to the sales account) , I think it is the most accurate and streamlined solution.

I hope to help them refine their workflow to get more accurate data moving forward and we gain a better understanding of their margins.

 

[Jessymathew]

Effective bookkeeping workflows are essential for the smooth financial management of an e-commerce business. Here's a step-by-step guide to e-commerce bookkeeping workflows:

1. Sales Recording: Immediately record online sales with customer details, products, and prices.
2. Payment Processing: Accurately track various payment methods and reconcile regularly.
3. Inventory Management: Keep precise records of physical product inventory.
4. Accounts Receivable: Manage credit sales, invoicing, and payments.
5. Expense Tracking: Record all business expenses and categorize them.
6. Bank Reconciliation: Regularly reconcile bank and payment processor statements.
7. Sales Tax Management: Collect and remit sales tax if required.
8. Financial Reporting: Generate income statements, balance sheets, and cash flow reports.
9. Tax Preparation: Keep detailed records for tax compliance.
10. Year-End Closing: Prepare year-end financial statements and tax documents.
11. Audit Trail: Maintain an audit trail for transparency and security.
12. Security and Backup: Protect data and perform regular backups.
13. Continuous Improvement: Streamline and enhance workflows for efficiency.

Consider using e-commerce-specific accounting software for automation and real-time insights, and consult professionals for accuracy and compliance.

 

[stephaniecrocker]

thanks for this. I was actually more interested in more specific technical considerations. things like creating a hub for client documentation. I can certainly google those, and get a great sales pitch from the company, but I find it helpful to get feedback from those actually using the tools.

 

[MMDub]

I haven't worked with e-commerce much what is A2X, is it geared towards e-commerce. I've used Transaction Pro for getting data into QB quite extensively. Is A2X a similar product?

Asking because I'm starting an e-Commerce biz myself as a side hustle to my accounting job.

 

[stephaniecrocker]

It looks like Transaction Pro would import each of your transactions, which isn't necessarily ideal when using QB as your accounting software, ie, you would clutter your file with too granular of data that is better stored in the point of sale software of your channel. My client uses Shopify. Other channels would be Amazon, Ebay, etc. Of course, if Transaction pro works for you, by all means continue with it

Side note, also, importing every transaction could make it difficult to manage data points in two places. People always say "I'm not going to change my product mix," but that does not make business sense. Additionally, the customer list makes more sense to be managed at the point of the sales channel where you can engage directly with your customers or move into an email management tool. QB is for accounting, POS is for sales. In QB you would run reports to see sales versus fees versus operating costs and the like. If you wanted to dive deep and see which products or product groups are performing better, you would look to your point of sale, not quickbooks for that report.

That said, I do think it might make sense to have generalized category sales and expense data in Quickbooks. (keeping in mind if you reorganize your categories on your POS things can get sloppy fast.) I am working on getting this setup with my client for 2024, who has done a poor job of managing inventory and cost of goods sold that don't necessarily line up with their product lineup on their Shopify store. (For 2023 and previous, we'll probably have adjusting entries to bring their balance sheet up to date.)

Back to A2X. For me, there was a significant learning curve mostly because I'm not that agile with Journal Entries in Quickbooks at the moment. A2X lets you have quite a bit of control on how your sales accounts are mapped to your COA. We are able to track chargebacks which is great because I can prove to the client they don't have that significant of an impact on their bottom line. The hardest part of the import is when their customers pay with Shopify Payments which allow them to pay in installments (for a greater fee to my client). These transactions are a little trickier to match up, but I perform this task once weekly and it's getting easier every time.

The other thing A2X allows is to track COGS which would be added to the QBO file in a monthly adjusting entry. I haven't used this feature yet because we still have quite a mountain to climb with bringing their inventory up to date as well as their actual costs. In this case, cost of goods sold would be managed on a separate spreadsheet and I haven't figured out how to get the client on board with this sort of workflow. (Previously, I sent them a spreadsheet and asked they fill in the costs for products that had none, and they quickly became overwhelmed.) Inventory can be somewhat clumsily managed through Shopify, but I am also considering an inventory management software. If you are considering multi-channel sales, I would definitely recommend that in your toolbox.

Excuse the horror I am about to unveil, but currently my client makes purchases and I do not have access to the receipts! I pitched them sending me purchase orders and they rejected that stating it would slow things down. This comes back to my original question about workflows. I would like an easy way for the client and myself to have access to all supporting documentation regarding any expenses. It's fairly obvious their current workflow makes them vulnerable to an audit and many hours of unpacking the supporting documentation.

Side note. A2X primarily has chat tech support that can take FOREVER to get a question answered. Sometimes, you will have your question ALMOST answered and are just waiting for the final piece and they might drop off for several hours or DAYS! It is quite frustrating as I'm old school and prefer sometimes to just pick up the phone. Even with the higher level subscription you have to beg to get invited to their calendar and generally have to wait 3-4 days for your very short tech support meeting. Don't get me wrong, their tech support people are smart and helpful (when you can get them), but often I spend a lot of unnecessary time trying to get a simple question answered which is costing my client money. In a lot of ways, there are so many ways to communicate these days but nobody seems to be able to talk.