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.