QuickBooks^® Request for Payment Invoicing

Determining outstanding Request for Payments (RFP) invoices using bank statements in QuickBooks Online (QBO) and then organizing the information in Excel or Google Sheets involves several steps. Here's a general guide:

In QuickBooks Online:

1. Review Transactions:

• Log in to your QuickBooks Online account.
• Navigate to the "Banking" or "Transactions" section.
• Review transactions to identify those related to Request for Payments or invoice payments.

2. Categorize Transactions:

• Categorize transactions appropriately, marking those related to Request for Payments or invoice payments.

3. Generate Reports:

• Use QBO to generate reports such as "Transaction List by Vendor" or "Sales by Customer Detail" to get a detailed view of invoice payments.

4. Export Reports:

• Export the relevant reports to Excel or Google Sheets for further analysis.

In Excel or Google Sheets:

1. Import Data:

• Open the exported report in Excel or Google Sheets.

2. Organize Data:

• Organize the data to focus on the necessary information, including transaction date, transaction description, payment amount, and customer/vendor details.

3. Link Transactions to Invoices:

• Use functions like VLOOKUP or INDEX-MATCH to link each bank transaction to the corresponding invoice in your QuickBooks data.

4. Calculate Outstanding Amounts:

• Calculate the outstanding amounts by subtracting the amount paid from the total invoice amount. Adjust the formula based on your data structure.

excelCopy code

=Total_Invoice_Amount - Amount_Paid

5. Filter and Summarize:

• Use filters to identify outstanding transactions. Summarize the total outstanding amount using the SUM function.

Additional Tips:

• Automation (QBO Apps):

• Consider using QuickBooks Online apps or integrations that can automate the process of reconciling transactions and identifying outstanding invoices.

• Regular Reconciliation:

• Regularly reconcile your bank transactions with QuickBooks Online to ensure accuracy.

• Data Consistency:

• Ensure consistency in data entry practices, such as using standard invoice numbers or references.

• Detailed Documentation:

• Document any specific criteria or procedures used for linking transactions to invoices and calculating outstanding amounts.

Remember that the exact steps may vary based on your specific business processes and the version of QuickBooks Online you are using. If you encounter challenges, it's recommended to consult the QBO help center or seek assistance from QuickBooks Online support for the most accurate and up-to-date guidance.