Locating discrepancies between QuickBooks Profit and Loss and QuickBooks Point of Sale sales

What can cause discrepancies between the reports?
Any time there is a difference between the Profit and Loss and a  Sales by Item Summary report it means that the two software are reporting different data.  This can be due to transactions that are missing either in QuickBooks Point of Sale of QuickBooks Financial.  Another cause is transactions that do not report to income or an unexpectedly mapped account.
Example:

These steps should be performed on the QuickBooks Point of Sale Server and using Excel:

How to locate the Discrepancies:

In QuickBooks Point of Sale run the Sales detail Report.

1. Go to the Reports drop down on the top of the screen.
2. Click on Sales.
3. Click Detail.
4. Make sure only the columns for Date, Receipt #, Receipt Type, Full Name, QTY Sold, Total and Payment are visible,
   1. If you need to add or remove columns click the Modify button on the top of the report.
   2. Click Add or Remove Columns
   3. Ensure only the above columns have a check mark next to it. and in the same order as above.  (you can drag and drop the names to the correct order if need be)
   4. Click Save
   5. Click Run
5. Once the report shows you all the columns you want click the button that says Excel.  An excel spread sheet will open up with the data from the report.
6. Delete Rows 1 through 5 so that the data headers start on row 1.
7. 
8. Highlight all the cells in this spreadsheet.  To do this clicking the gray triangle Icon at the top left of the spread sheet.    
9. Choose the Sort and Filter Icon at the right of the screen.
10. Select Custom Sort
11. Make sure there is a Check in My data has headers
12. In the Sort by drop down choose Receipt #. leave all other fields as defaulted
13. Click OK
14. Minimize this and open QuickBooks Financial.

In QuickBooks Financial (Pro, Premier or Enterprise) open a Customer Transaction Detail report.

1. Go to Reports at the top of the screen.
2. Scroll down and choose Custom Reports.
3. Select Transaction Detail.
4. The Modify Report screen will be displayed.
   • Set the date range to match the report date range that you selected in Point of Sale.
   • Click the Filters tab.  *See animation below
   • Under Choose Filter select Detail Level.
   • Put a mark next to Summary Only.
   • Choose the next filter Transaction type.
   • Choose Multiple Transaction Types.
   • Place check marks next to Sales Receipt and Invoice.
   • Click OK to take you back to the Filters tab.
   •                   
5. Click OK again to take you to the report.
6. Click the Excel button at the top of the screen.
7. Choose Create New Worksheet.
8. Make sure the option is Create a new worksheet and in new workbook are selected
9. Click Export.

Prepare the exported Excel file to be compared.

1. ​Open the Excel File that was exported out of QuickBooks Financial.
2. Delete line 2, which is a spacer by clicking on the Number 2 on that row, right click and choose Delete. All the cells should shift up.
3. 
4. Go to the Column Labeled Memo and highlight the whole column.
5. On your keyboard hit the CTRL + F key to bring up the Find Window.
6. Click on the Tab that says Replace.
7. In the Find What box type in POS Receipt#.
8. Leave the Replace With box blank.
9. Click Replace All
10. Click Close on the Find and Replace screen.
11. The Memo column's numbers should match the receipt # column from the Excel exported from Point of Sale
12. Highlight Columns D through X.    
13. Choose the Sort and Filter Icon at the right of the screen.
14. Select Custom Sort.
15. Make sure there is a Check in My data has headers.
16. In the Sort by drop down choose Memo.
17. Click OK
18. Copy all the highlighted columns by right clicking and choosing Copy.
19. Open up the Excel file that was exported from QuickBooks Point of Sale.

Comparing the Data:
By moving the data exported out of QuickBooks and pasting into the data exported from Point of Sales you will be able to compare the Memo Column to the receipt # column to locate missing transactions.

1. The Data exported out from Point of Sale should fill Columns A through N
2. Click on the Cell at Column U row 1 (U1).
3. Right click and paste. This should fill in the data from the QuickBooks export.
4. Once this has been done, Column D should be Receipt # and Column AC should be Memo. 
5. Click into the Cell at Column R row 2 (R2)
6. In the R2 cell Type in =IF(D2=AC2, "Match", "False") 
7. Hit Enter 
8. If the Cell D2 matches Cell AC R2 will say Match, if it does not it will say False
9. Highlight the R2 cell again.
10. In the bottom right hand corner of the cell there is a small square call a Handle move your mouse pointer above that click and drag the handle down until the bottom of the list.  * See animation below to see using handles
11. Once you reach the bottom of your list and release Cells in the R column will say either  Match or False.
12. Return to the top of the report then scroll down until you see the first False
13. Make note of the receipt number in Column D and AC and locate if the data to the right or left of column R needs to be moved down to make the next Match.
14. Highlight the Data on the Row to the right or left of R then right click and Choose insert.
15. If it asks, choose the option to move the Data down this will leave a blank line and the Number in AC or D should line up with the next line which hopefully has a matching number.  If not you may need to insert multiple lines until they match up.
16. Once you can verify the Cells D and AC match highlight the last cell in column R that still says match to get a handle back
17. Double click the handle, which should fill down the formula again and allow you to scroll through for any more missing receipt numbers.
18. Repeat these steps until both sides of the report are lined up and the only lines that say false are ones where one side of the report has a blank line.

Using Handles:

Missing transactions:

1. ​Each blank line in the report indicates a transaction that is in one software report but not the other.
2. Locate a receipt # on a row that has a False on it.
3. Take that receipt number, locate it in the Sales History in QuickBooks Point of Sale and highlight it.
4. Click I Want To
5. Select Show Financial Detail. 
6. A screen will display showing the transactions that Point of Sale sent to QuickBooks financial.
   1. If the text of these transactions are black that means that transaction was deleted in QuickBooks financial and Point of Sale  can't locate it.
   2. If the text is a blue hyperlink that means the transaction has been found, you can double click on the link to open in QuickBooks.
   3. If there is no text that means the receipt was not sent to QuickBooks, this is either because it was a reversed transaction, a new transaction that has not had a chance to be sent yet, or a $0.00 transaction that has no effect on accounts in QuickBooks.
7. If the transaction is in QuickBooks Financial but not in Point of Sale that indicates the Receipt was put manually into QuickBooks or came from a different Point of Sale file.
8. After determining the reason for the difference between the two programs then a decision can be made to determine what needs to done to fix.
   1. Either send or re-send the financial exchange by marking the  Resend this document option during the next financial exchange on the Financial detail screen
   2. Manually adjust in QuickBooks financial.
   3. Note the difference and reason so in future it can be explained if no updates are preferred.