QuickBooks
      Back to home
      1. Knowledge Base
      2. QuickBooks

      How to Resolve Inventory Discrepancies Between AdvancePro and QuickBooks

      Overview
       
      There are many reasons why the inventory valuation in QuickBooks may not match AdvancePro. Use the following procedure to identify the cause and correct any problems.
       
      STEP 1: Ensure AdvancePro and QuickBooks are configured correctly for syncing
       
      For QuickBooks Desktop, refer to QuickBooks Syncing Setup Guide
       
      For QuickBooks Online, refer to QuickBooks Online Sync Guide
       
       
      STEP 2: Run reports to compare inventory
       
      Download the attached Excel file (below) with database integration and follow the instructions on the first sheet to compare inventory. (If you do not have access to read from your AdvancePro database and wish to use this report, please contact support.)
       
      If the attached pre-built report does not work for your purposes, you can create a new comparison report by performing the following:
       
      In QuickBooks:
      1. Go to Reports > Inventory > Inventory Valuation Summary
      2. Ensure that “As of” date is set to today
      3. Export the report to Excel using the option above the report
       
      In AdvancePro:
      1. Go to Reports > Product Reports > Inventory Reports
      2. Without changing default settings, click Inventory Report (it must be for all warehouses)
      3. Export the report to Excel
       
      In Excel:
      1. Merge the QuickBooks report with the AdvancePro report (by adding as a second sheet in the same workbook)
      2. Use vlookup function to directly compare products between AP and QuickBooks:
        1. AP Qty In Stock = QuickBooks On Hand
        2. AP Avg. Cost = QuickBooks Avg Cost
        3. AP In Stock $ = QuickBooks Asset Value
       
      STEP 3: Identify discrepancies on the comparison report
       
      On the report (either the downloaded one or one you created), review for the following:
      • Large differences in In Stock $ / Asset Value
      • Large differences in Qty In Stock / On Hand
      • Items that exist in AP but not in QuickBooks
      • Items that exist in QuickBooks but not in AP (may require extra comparison)
       
      STEP 4: Identify reasons for discrepancies
       
      In AdvancePro:
      1. Go to Manage Inventory and click the eye icon to view adjustment / order history for an item with discrepancies.
       
      In QuickBooks:
      1. Go to Reports > Inventory > Inventory Valuation Detail
      2. Click Customize Report and go to the Filters tab
      3. Change the item filter from all items to the one you want to compare.
      4. Change the date range on the report to include all dates shown in the AdvancePro history.
       
      Review the history between AdvancePro and QuickBooks to determine the specific transactions and/or adjustments that are causing the asset value discrepancy.
       
      Common Problems and Solutions
       
      Problem
      Potential Causes
      Solution
      Current quantity on hand does not match
      There are pending bills/invoices in AdvancePro that have not exported to QuickBooks.
       
      Export errors are preventing adjustments/transactions from exporting.
       
      Adjustments have been performed directly in QuickBooks.
      Ensure all transactions and adjustments are entered directly in AdvancePro and that they have been exported to QuickBooks.
       
      Ensure the “Automatically adjust QuickBooks inventory…” setting is enabled in AdvancePro (Admin > Site Settings > QuickBooks). This will cause inventory to be adjusted in QuickBooks to match AP inventory whenever there is an adjustment performed in AP. This is based on a calculation that takes pending transactions not yet exported to QuickBooks into account.
       
      Re-export all inventory from AdvancePro (Admin > Site Settings > QuickBooks > Mark All Inventory for QuickBooks Re-export). This will trigger a comparison / adjustment for all products.
      Asset value is incorrect even though current on hand quantity matches
      Transactions entered in AP are missing in QuickBooks
       
      Transactions or adjustments were entered in QuickBooks and are missing in AP.
       
      Erroneous adjustments exist in QuickBooks (for any number of reasons).
       
      Order history in QuickBooks includes transactions prior to integration with AP and costs on those transactions differ greatly.
      Ensure any transactions missing in AP/QuickBooks are corrected by re-exporting or manually entering. (Be sure not to export any bills/invoices in AP that already exist in QuickBooks.)
       
      Modify any erroneous adjustments in QuickBooks history that are resulting in incorrect avg. cost. (Ensure that current on hand quantity still matches after all modifications.)
       
       
      Products are missing in AP or QuickBooks
      Products were missed when implementing AP.
       
      Export errors are preventing products from exporting to QuickBooks.
      Ensure any products missing in AP are added with SKUs exactly matching the item names in QuickBooks. Perform an export and re-link the records.
       
      Ensure any export errors are corrected for products missing in QuickBooks. Re-export products from AdvancePro as necessary.
      Duplicate products exist in AP or QuickBooks
      Products were imported into AdvancePro multiple times with changes to the SKUs.
       
      Products were exported to QuickBooks where the AP SKU did not exactly match the QuickBooks item name.
      Ensure SKUs in AdvancePro (and ‘QuickBooks Name’ field) match the item names in QuickBooks exactly.
       
      Inactivate any duplicate items in AP.
       
      Adjust on hand quantity to 0 for any duplicates in QuickBooks and then inactivate. (If stock is not adjusted to 0, it will cause inflated numbers on certain reports).
       
       
      Additional Considerations
      • If running QuickBooks Enterprise, are there any Advanced Inventory settings enabled? (Refer to QuickBooks Syncing Setup Guide.)
      • What is the costing method set to in QuickBooks? (The inventory report in AdvancePro uses avg. cost but there is also a FIFO/LIFO report available.)
      • Are frequent exports being performed? (Some transactions such as work orders are dated based on the export date and may cause problems if exported long after the transaction was created in AP.)
      • Are account settings configured correctly in AP/QuickBooks? (Refer to QuickBooks Syncing Setup Guide.)
      • Are there any other settings in QuickBooks/AP that may be causing issues? (Review carefully if problems persist after following above steps.)

       

      Download: Inventory Comparison Report