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 QB:
        1. AP Qty In Stock = QB On Hand
        2. AP Avg. Cost = QB Avg Cost
        3. AP In Stock $ = QB 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 QB
      • Items that exist in QB 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 QB.
       
      Export errors are preventing adjustments/transactions from exporting.
       
      Adjustments have been performed directly in QB.
      Ensure all transactions and adjustments are entered directly in AdvancePro and that they have been exported to QB.
       
      Ensure the “Automatically adjust QB inventory…” setting is enabled in AdvancePro (Admin > Site Settings > QuickBooks). This will cause inventory to be adjusted in QB 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 QB into account.
       
      Re-export all inventory from AdvancePro (Admin > Site Settings > QuickBooks > Mark All Inventory for QB 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 QB.
       
      Transactions or adjustments were entered in QB and are missing in AP.
       
      Erroneous adjustments exist in QB (for any number of reasons).
       
      Order history in QB includes transactions prior to integration with AP and costs on those transactions differ greatly.
      Ensure any transactions missing in AP/QB are corrected by re-exporting or manually entering. (Be sure not to export any bills/invoices in AP that already exist in QB.)
       
      Modify any erroneous adjustments in QB 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 QB
      Products were missed when implementing AP.
       
      Export errors are preventing products from exporting to QB.
      Ensure any products missing in AP are added with SKUs exactly matching the item names in QB. Perform an export and re-link the records.
       
      Ensure any export errors are corrected for products missing in QB. Re-export products from AdvancePro as necessary.
      Duplicate products exist in AP or QB
      Products were imported into AdvancePro multiple times with changes to the SKUs.
       
      Products were exported to QB where the AP SKU did not exactly match the QB item name.
      Ensure SKUs in AdvancePro (and ‘QB Name’ field) match the item names in QB exactly.
       
      Inactivate any duplicate items in AP.
       
      Adjust on hand quantity to 0 for any duplicates in QB 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 QB? (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/QB? (Refer to QuickBooks Syncing Setup Guide.)
      • Are there any other settings in QB/AP that may be causing issues? (Review carefully if problems persist after following above steps.)

       

      Download: Inventory Comparison Report