QuickBooks^® Request for Payment Invoicing

Creating an Accounts Receivable (AR) Aging report for FedNow and RTP (Real-Time Payments) using Excel software and then uploading and syncing the data into QuickBooks Online (QBO) and QuickBooks involves several steps. Here's a guide on how you might achieve this:

1. Excel Template Setup:

1.     Headers:

·        Create column headers in Excel for essential information such as Customer Name, Invoice Number, Invoice Date, Due Date, Amount, Payment Status, and any other relevant details.

2.     Data Entry:

·        Enter the details of each receivable into the respective columns.

2. Status Tracking:

3.     Payment Status:

·        Include a "Payment Status" column to track whether the invoice is outstanding, partially paid, or fully paid.

3. Days Outstanding Calculation:

4.     Days Outstanding Column:

·        Create a column to calculate the number of days each invoice is overdue. Use a formula like:

excelCopy code

=IF([Due Date Cell] < TODAY(), TODAY() - [Due Date Cell], 0)

4. Categorize Outstanding Invoices:

5.     Age Categories:

·        Create aging categories (e.g., "Current," "1-30 Days," "31-60 Days," etc.) based on the days overdue.

6.     Status Column:

·        Create a column to categorize the status of each invoice, such as "Outstanding," "Partially Paid," "Fully Paid," or "Void."

5. Analysis and Reporting:

7.     Total Outstanding Amount:

·        Create a formula to calculate the total outstanding amount. Use the SUMIFS function to sum amounts based on aging categories.

8.     Charts and Graphs:

·        Utilize charts or graphs to visually represent the distribution of outstanding invoices.

6. Integration with QBO and QuickBooks:

9.     Export Data from Excel:

·        Save the Excel template as a CSV file.

10. QuickBooks Online Integration:

·        In QBO, go to the relevant section (e.g., Banking) and select "Upload a file" or use the appropriate option to import data.

11. Map Fields:

·        Map the fields in your CSV file to the corresponding fields in QuickBooks during the import process.

12. Review and Confirm:

·        Review the imported data in QuickBooks and confirm the transactions.

7. Regular Updates:

13. Data Entry Updates:

·        Regularly update the Excel template with new invoices, payments, or changes in payment status.

8. Automation (if applicable):

14. Automated Alerts (Optional):

·        Set up automated alerts or notifications for specific conditions (e.g., overdue invoices).

9. Documentation and Audit:

15. Documentation:

·        Keep a record of transactions, updates, and any adjustments made for audit and reference purposes.

10. FedNow and RTP Specifics:

16. Include FedNow and RTP Details:

·        Depending on the integration, include columns for FedNow and RTP-specific details, such as Transaction IDs, Sender's Name, and relevant information.

Notes:

• Ensure that the CSV file is properly formatted, and the data aligns with the fields in QuickBooks during the import process.
• Regularly reconcile your Excel template with the actual transaction data from FedNow and RTP for accuracy.
• Always refer to the latest documentation for QuickBooks Online for any specific features or updates related to data integration.

This process involves a combination of Excel for initial data management and QuickBooks Online for further accounting and financial management. If you encounter difficulties, consider reaching out to QuickBooks support for assistance.