The Controller’s Toolbox is a collection of 10 useful reports sold as a package from TaraByte Solutions+Fishbowl Gurus, LLC (They may also be purchased from Fishbowl). Currently, the package includes four export reports designed to track every debit and credit from Fishbowl to its QuickBooks account. It also includes four useful costing review reports, a sales tax charged report for review, a detailed listing of individual receiving transactions, and a FIFO/LIFO valuation report by location group, not available in standard Fishbowl reports. As we develop additional cost accounting tools we will add them to the package.
The export reports are what I use to reconcile Fishbowl transactions to QuickBooks or more appropriately QuickBooks to Fishbowl Inventory, as Fishbowl sent the transactions to QuickBooks, but the transactions were altered once posted.
Below is a sample of the report “Export – Purchase Orders:”
Using these reports and the Custom Transaction Detail Report in QuickBooks, we can easily reconcile the inventory activity by transaction type.
In the above example there is obviously a problem with the bills. At this point you can run the reports for various periods to try to locate when the entry or entries were modified and then, with the scope narrowed, you can review the QuickBooks audit trail to find the offending transaction(s).
Once you have corrected your QuickBooks transactions or alternatively forced the accounts to match using a Journal Entry, I recommend using the bank reconciliation feature on your inventory account(s).
On the next screen check the box “Hide transactions after the statement’s end date.” Then hit the button Mark All. If this is the first time you have ever done this, wait until the end of the day to start it, and let it run overnight.
Using these reports and the Custom Transaction Detail Report in QuickBooks, we can easily reconcile the inventory activity by transaction type.
How to Use the Controller’s Toolbox Export Reports
Upload the export reports into your Fishbowl Reports Menu. Run each report for the desired time period. In QuickBooks, run the following reports: For Purchasing Transactions – Run Custom Transaction Detail Report filtered by account(s),inventory, and multiple transaction types > Bill, Bill Credit, and Item Receipt (you probably want to memorize this report so it is readily available next time) for the same time period. For SO, WO and Inventory Adjustments – Run Custom Transaction Detail Report filtered by account(s), inventory, and transaction type = Journal for the same time period. Export this report to Excel and sort by the memo field. This will have the effect of sorting by transaction source (Sales Order, Purchase Order, and Adjustment). Total the debits and credits based upon the prefix of the memo field. Now you have your debit and credit totals for each transaction type to compare to the Controller’s Tool Box Reports. Now create an Excel spreadsheet like the one in the example below to determine where you need to focus your efforts:
In the above example there is obviously a problem with the bills. At this point you can run the reports for various periods to try to locate when the entry or entries were modified and then, with the scope narrowed, you can review the QuickBooks audit trail to find the offending transaction(s).
Once you have corrected your QuickBooks transactions or alternatively forced the accounts to match using a Journal Entry, I recommend using the bank reconciliation feature on your inventory account(s).
Using QuickBooks Bank Reconciliation for Inventory
The Reconcile Function in QuickBooks is not limited to bank and credit card accounts. It can be used for any balance sheet account other than Accounts Receivable and Accounts Payable. Through this reconcile feature, any transaction that is changed after the reconciliation will show up on the reconciliation discrepancies report and any transaction that is backdated to prior to the reconciliation date will show up on the reconciliation screen. After you have reconciled your QuickBooks Inventory accounts to Fishbowl, I recommend you enter your final Inventory asset balance as of the reconciled date, as shown below:
On the next screen check the box “Hide transactions after the statement’s end date.” Then hit the button Mark All. If this is the first time you have ever done this, wait until the end of the day to start it, and let it run overnight.