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