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