How to Reconcile QuickBooks Inventory Accounts to the Fishbowl Sub-ledger

I would like to save you a lot of time by sharing what I’ve learned as I’ve worked closely with Fishbowl Inventory and QuickBooks. First, unless you’ve made a copy of your database at month end, QuickBooks and Fishbowl can only be reconciled as of now. Yes, I know standard Fishbowl has a report called “On Hand by Date.” This report has never worked as a valuation of the inventory at a previous point in time. My vote would be for Fishbowl to either rename the report or remove it from their standard package. So what reports should you use?

• Average Cost – Use either the Inventory Valuation Summary or the Asset Valuation by Account
• LIFO/FIFO Cost – Use the Asset Valuation by Account
• Standard Cost – Use the Inventory Valuation Summary or the Asset Valuation by Account

Here’s the process you should follow:

• Post all transactions to QuickBooks through the Fishbowl Accounting Export
• Run the appropriate valuation report – Note the total dollars
• Run a QuickBooks balance sheet for all dates (you can have transactions dated after today’s date) – Match the total of the appropriate valuation report to the QuickBooks inventory account(s)

If there is a difference, it’s time to troubleshoot.

Troubleshooting

First, check your mappings in Fishbowl to QuickBooks. I had a client who was mapping his cycle count adjustments back to inventory, which led to the following situation: Inventory was no longer in Fishbowl but the dollars, instead of crediting inventory and debiting expense, were being debited and credited to inventory, creating a zero adjustment and leaving the QuickBooks asset account too high. Next, understand that if the mappings are correct the problem is on the QuickBooks side. There should never be a manual entry to the QuickBooks inventory account. There should also never be a bill correction or credit memo to the QuickBooks inventory account. You should never delete a bill or any other transaction affecting inventory that originated in Fishbowl. In a perfect world, Fishbowl would be the only user to ever create or modify an inventory transaction. You may not believe your discrepancy could be on the QuickBooks side. After all, QuickBooks is your friend. Fishbowl is this new kid it town, you just don’t yet trust. Recently I had a client with $2 million worth of inventory in Fishbowl and only $1 million in the QuickBooks Inventory account. I used the Controller’s Toolbox reports to do a rollback on their inventory transactions from Fishbowl. Everything (well, almost) matched the transactions posted in QuickBooks. So I re-enabled inventory in QuickBooks so that I could run a QuickBooks inventory valuation report as of the day before they went live on Fishbowl, and guess what? The QuickBooks inventory sub-ledger was $1 million higher than the Inventory Asset account before they went live. Mystery solved! In reality, QuickBooks was the problem. I have a procedure for the initial implementation of QuickBooks and Fishbowl, which would have caught the problem at initial implementation. Look for a blog on that process coming soon.

Troubleshooting Without Additional Tools

To track down the offending transactions, you have the following tools available to you in QuickBooks and Fishbowl:

Fishbowl Accounting Reports

• Adjustment Report – Fairly clean report that shows all adjustments for a period or by adjustment type.
• COGS by Account Summary – Shows everything posted to Cost of Goods Sold. Non-inventory and service parts may be mapped to this account; therefore the total is not necessarily what was retrieved from inventory.
• Export Summary – This can be run for Purchase Orders, Sales Orders and Work Orders, separately or together. The drawback here is that it is a total of the activity for each type of transaction not a breakout of what was posted to inventory. The same limitations regarding non-inventory, service, labor and other part types applies to this report as well as the COGS by Account Summary.

• Audit Trail – The audit trail details all changes to transactions in the QuickBooks database. It identifies what changed by bolding the text. This report is somewhat difficult to use, but has solved a few mysteries from time to time.
• Voided/Deleted Transactions Summary and Detail – These reports can quickly identify if a user has deleted or voided a transaction. It’s easier to use than the audit trail.
• Closing Date Exception Report – This report has a lot of potential, if you have set a closing date for your period, listing only what has changed since the closing date was set. However, if the closing date is removed, the data in this report is cleared.
• Custom Transaction Detail Reports – Modified to display LastModifiedDate and LastModifiedBy. With these reports, we are looking for inventory transactions that were modified by someone other than the Fishbowl user or where the LastModifiedDate is different than the transaction date. These date sorts can sometimes help us identify transactions posted in incorrect periods – See Receiving Quirk.