How to categorize Quickbooks 'Open' Purchase Orders
Use this guide in conjunction with Evaluation to Launch workbook.
Key Tasks
There are three interrelated tasks here:
Cross-reference open Item Receipts and Bills, and their balances specifically corresponding to ‘Open’ Purchase orders. As a result, you’ll know the origins and values of balances you migrate from QuickBooks to your new system.
You’ll need this information for the Journal you’ll create to set balances related to each Supplier on the Liability (Accounts Payable, Pending Item Receipts) GL accounts.
Cross-reference Item quantities partially received to their original Purchase Orders.
You’ll need these quantities to calculate the number of remaining Items still to be received on the new Purchase Orders you will create when you import the corresponding data into your new system.
When the commensurate stock arrives, you’ll both receive it and complete the Item Receipt or Bill process in your new system.
You’ll need to refer to the original Purchase Order in Quickbooks to see what was originally purchased.
Create the appropriate data for import of ‘Open’ Purchase Orders via the ‘Upload Transactions’ template.
Identify ‘Open’ Purchase Orders
Go to: Quickbooks>Reports>Purchases>Open Purchase Orders Detail
Make sure you set the 'Dates' on this report to 'All'.
The report lists Item(s) by Purchase Order(s) by Customer(s). Only 'Open' Purchased Orders appear in this report.
Export the report to an Excel worksheet.
This will be your 'Open Purchase Order migration tracking sheet'. It's a good idea to name it as such with a date and time.
Open the Excel worksheet.
Notice the columns Item, corresponding Purchase Order 'Num', Qty, Invoiced, and Open Balance.
Now work through the list of 'Open' Purchase Orders and categorize each one.
Not Received
If you have Excel skills, you can sort the sheet by Purchase Order 'Num'. This will group all of your Items by Purchase Order 'Num'. This will make the following exercise easier, and you're preparing your Purchase Order data for import into Salesorder.com.
In the 'Qty' column, you see the total number of Items ordered per Purchase Order together with the corresponding 'Num'. If the Invoiced column contains '0', the Items on the corresponding Purchase, the Item Qty on this Purchase Order has not been Received.
Check the balances on the Amount and Open Balance columns are identical. If they are not, this means no stock has not been physically received but an upfront or pre-payment has been made equal to the Amount minus the value of the Open Balance.
Make a note of the related Purchase Order ‘num’, together with the balance paid upfront. Remember we are specifically looking for stock Not Received here, so move on to the next step.
Check the rest of the Items on this Order, if all of the 'Qty' values are '0'. One more check, before you
Record this Purchase Order in the 'Not Received' category.
Focus on collating all of your Purchase Orders that are 'Not Received'. Group them together in the sheet. These will be imported as 'Open Purchase Orders, not received.
‘Open’ Purchase Orders with Item Receipts or Bills or both?
If you think through all the possible combinations of Purchase Orders, Item Receipts, and Bills you'll come up with a list like this.
‘Billed but not received’ - Bill entered stock not received.
'Not Received - no Items have been received on this Purchase Order.
'Partially Received - Entire Bill has been received.
'Partially Received - no Bill' Some Items received but no Bill.
‘Partially Received, Partially Billed’ - Received has been Billed.
Cross-reference open Item Receipts and Bills
All Bills and Item Receipts result in a Debit and Credit on the balance sheet. Because in Launch T -3 you’re only going to be migrating balances not the actual open Item Receipts and Bills from Quickbooks to your new system, to head off confusion in the future you need to record notes about each balance’s origin.
I’m stressing this because in Quickbooks the Item Receipt and the Bills both post to the Accounts Payable. When the Supplier balances are transferred, you need to know how to apportion the balance between Accounts Payable and Pending Item Receipts in your new system.
In Launch T -3 tasks you’ll need to keep a Working Paper (your CPA should know what this is). Start your working paper now with notes about the origin and values of balances on Item Receipts and Bills related to ‘Open’ Purchase Orders.
For example, Bill from PO ‘Num’ = $2,000, or Item Receipt from PO ‘Num’ = $2,000. Make sure you record the dates as well! You’ll need it for your Aging Creditors position.
To identify your ‘Open’ Item Receipts and ‘Open Bills go to:
Quickbooks>Reports>Vendors & Payables>Unpaid Bills Detail
Export this report to Excel. Name it 'Item Receipt and Bill tracking' with a date and time.
Now find (and record) the cross-references between Purchase Orders and Item Receipts.
Item receipts in Quickbooks are not automatically recorded with a 'Num'. When you drill down in the Quickbooks 'Unpaid Bills Detail report' on the Item Receipt using (Z), the Item Receipt will be displayed.
In the bottom left-hand corner of the Item Receipt, you will see a button 'Show PO'.
Click on this button to display the corresponding Quickbooks 'Num' of the Purchase Order.
You'll need to record this cross-reference in your worksheet to link the Item Receipt to the corresponding Purchase Order in your new system.
Add an additional column to your worksheet (PO#) and record the PO number.
Cross-reference Item quantities partially received
You need to analyze, which Items and what Quantity remain not received on which Purchase Order.
Go back to your 'Open Purchase Order migration tracking’ sheet.
You originally got this from, Quickbooks>Reports>Purchases>Open Purchase Orders Detail. Make sure you set the 'Dates' on this report to 'All'.
Partially received
The remaining Purchase Orders are 'Partially Received'. Your next task is to rationalize the remaining Items on Purchase Orders that have not been Received. The Supplier for each order may have sent you the entire or a partial Bill for the stock.
You may have received the stock on these partially received orders using an Item Receipt or a Bill.
Before you do this next step, I'll explain the strategy. You need a complete record of the original partially received Purchase Order in your new system. This will include what is still to be received and what has been received.
The data in rows for the Partially Received Purchase Orders need to be edited in your 'Open Purchase Order migration tracking’ sheet.
Copy and paste all of the Partially Invoiced Purchase Order data to another sheet in your worksheet. It's critical you have a record of the original export and the categorization of each Purchase Order. Name this sheet ' Partially Received Purchase Orders'.
The original report exported the 'Qty' and 'Recv’d' values. Subtract the Received values from the 'Qty' and change the 'Qty' to the result. This will be the Qty by Line Item on the new Purchase Order in Salesorder.com.
When you've completed this for every Partially Received Purchase Order, you will have a new group of 'Open' Purchase Orders to import into your new system.
When you import these Purchase Orders into your new system, check they are correct and then upload the attachments onto each corresponding Purchase Order.
One more thing, when you import a Purchase Order with a 'Num' from Quickbooks into your Salesorder system using the 'Upload Transactions' template, the Num will appear on the 'Origin' tab in the 'External Cart Mapping' field.
If you have any questions, mail support@salesorder.com. We are here to help!
For the next task you’ll have in your working paper the following data the cross-references for open Item Receipts and Bills, and their balances specifically corresponding to ‘Open’ Purchase orders.
Last updated
Was this helpful?