Hi,
I author the CartSpan ecommerce integration for Shopify and a number of other systems. For the last 3 years, I've been integrating Shopify with QB POS and Shopify directly to QuickBooks Desktop. Only in the last 6 months have I begun to integrate Shopify POS to QuickBooks desktop accounting. These accounts have been QB Desktop users that began using Shopify POS and had not previously used the QB POS system. Some tweaking of the CartSpan integration has been necessary to accommodate the lack of billing and shipping information that is collected during the POS checkout, but everything has been working well. More recently, I've been getting calls from those of you using the QB POS system which is being shut down. My initial assessment was that, it shouldn't be a problem, but there are underlying issues that need to be resolved before any system like mine can be helpful. These are my findings from interacting with a number of QB POS users regarding the current state of connecting with Shopify.
The QB POS system has been responsible for managing the purchasing/receiving process and associated transaction detail. In its 'financial exchange' with QB Desktop, the POS system has performs a general journal entry (JE) to record the value of goods received (inventory asset) and updates the standard cost on affected item masters (typically Non-Inventory) with the last purchase price. In some configurations, I think there are no items at all, just high-level JEs. In the same 'financial exchange', POS performs general JEs (journal entries) to transfer value from the Inventory Asset account to the COGS account. These journal entries are required given that there are no underlying costs maintained for Non-Inventory parts in QuickBooks as invoicing a Non-Inventory part results in zero change of the COGS account on the P&L. The cost value that you see on the item master only serves to populate purchase orders for the item.
As QB POS has always been the 'master' of stock, there has been no reason to maintain items in QuickBooks as anything other than 'Non-Inventory' item types. This is due to the POS system taking ownership COGS transactions via general journal entries as previously described. But in the absence of a viable integration that can reliably perform the same journal entries, it is necessary to use QuickBooks in a conventional manner for tracking item-specific inventory quantity and valuation. This implies conversion of all 'Non-Inventory' parts to a type of 'Inventory Part' and associating the appropriate amount of the Inventory Asset account to each individual product. This then allows QuickBooks to naturally manage the underlying COGS transactions during import process without having to rely on general journal entries. By necessity, QuickBooks then becomes the master of inventory...requiring the use its own mature purchasing/receiving functionality.
The downside of the Intuit/Shopify connector trying to manage supporting journal entries can be high. For starters, the journal entry process must be perfect...there can't be any mistakes as the transaction is a multi-step process for the connector; An invoice is imported which registers sales, followed by JE for the COGS transfer. Any disconnect can result in unrecorded COGS and result in overstated Net Income. If for any reason, a QuickBooks user decides to delete a transaction (Invoice) in QuickBooks (which is allowed), they must also manually perform multiple journal entries to revert the underlying COGS value back to the Inventory Asset account. These original JEs may or may not be easy to find if the user even remembers to perform the action. Even worse, the JE could be aggregated (I'm not sure how it is intended to work). This circumstance was never allowed to happen with the historically tight integration between QuickBooks Desktop Accounting and the QuickBooks POS systems. The mechanism that prevented such orphaned transactions was the fact that one could not simply delete a transaction in the POS system, it had to be reversed. The underlying transactions were then appropriately handled in the next 'financial exchange'. Such fail-safe mechanisms cannot be guaranteed with systems that are more loosely coupled. With QuickBooks Desktop managing stock, when an invoice is deleted, stock transfers from COGS back to Inventory Asset happen automatically...integrity guaranteed.
In the absence of a reliable/comprehensive connector to perform these transactions, it becomes necessary to repatriate the purchasing/receiving process back into accounting and allow QuickBooks to be the 'master' of inventory. This completely eliminates any chance of the disconnect described above and ensures the integrity of the financials.
This business process requires that all 'Non-Stock' items be converted to 'Inventory Part' type items for QuickBooks to start managing underlying cost movement as part of the order import process. In some cases, you may need to create them altogether. This involves not only conversion to a 'Inventory Part' type, but also initializing inventory quantity and associating item-specific value that has, up to now, lived in the global pool of the Inventory Asset account.
These are the specific steps I'm taking with a customer currently in this predicament:
Note that these steps can only be performed after you have your items converted to the type of 'Inventory Part'
1. Perform a physical inventory if you are not confident of your current quantity-on-hand for all of your items. Arrange this in a spreadsheet with item, quantity, cost, and total_value columns.
2. In your spreadsheet, begin plugging what you believe to be appropriate cost numbers for each item. Your spreadsheet should begin reflecting a grand total value from the total_value column.
3. Your final grand total value should reflect exactly the amount currently in your 'Inventory Asset' account. If not, you need to begin massaging the numbers so that they do. You may even discover a significant disparity between what your books reflected and what you actually have.
4. Create a new account called 'Inventory Write-Down'. This has the same type as your COGS account, which is of the account type 'Cost of Goods Sold'. Don't panic, were not scrapping all of your inventory.
5. Perform a general journal entry that DEBITS 'Inventory Asset' for the total amount of that account and CREDITS the new 'Inventory Write-Down' account. Your 'Inventory Asset' account should now have a balance of zero.
6. Use the 'Adjust Quantity/Value on Hand' utility in QB to register item quantity/value back into the 'Inventory Asset' account. This assumes that 'Inventory Asset' account is designated as the 'Asset Account' on the QB item master. Select 'Quantity and Total Value' as the adjustment type. Select the 'Inventory Write-Down' account as the adjustment account. Acknowledge to QuickBooks that the adjustment account is not what it is expecting. Enter your item quantity/value from your spreadsheet and save.
7. When you have finished your last item, the 'Inventory Asset' account should be back at it's original value and the 'Inventory Write-Down' account should be back to zero.
This process has zero impact on the balance sheet or income statement and puts you on a path to using QuickBooks to manage inventory using FIFO valuation method (or which ever method you prefer). Note though, that, out of necessity, this process initializes costing as an average value given the absence historical detailed purchasing activity. Going forward, your desired costing method is recognized.
At this point, you can use CartSpan, or any other integration tool to import POS sales from Shopify and push current inventory availability back to Shopify. There will inevitably be some limitations encountered. For instance, CartSpan only pushes stock from QuickBooks to a single site in the Shopify system. This is only an issue for those of you with multiple stores. I'm not sure if Shopify can be instructed to point multiple stores at a common stock location. One thing will be guaranteed though, your books will be accurate.
Other issues that I've discovered, but may not yet be apparent to some, include the item count limitation in QB versions other than Enterprise. I've been offering integration of QB POS with many ecommerce systems and have encountered catalogs of up to 200,000 items. These are generally folks that have been running POS since the stone-age and never cleaned anything up. While QB POS supported what seems like an infinite number of items, Non-Enterprise QB systems only support a maximum of 14,500 items. Hence, if you are cataloging more than 14,500 items in POS, and are not keen on updating to Enterprise, the solution I have proposed is not going to work for you.
If you have a mind to test this process on a small scale to determine if it will work for you remember to BACK-UP, BACK-UP, BACK-UP! I'm a paranoid sort of fellow and that's the first thing I do with any new development and testing.
I hope these observations add a little clarity to this issue and are helpful to your finding a solution.
Scott Wheeler
Developer of CartSpan eCommerce integration
