QuickBooks^® Request for Payment Invoicing

If you want to calculate FedNow Send Transactions Outstanding in an Excel template and then upload and sync the data into QuickBooks Online (QBO) or QuickBooks Desktop, you can follow these steps:

1. Create an Excel Template:

1.     Headers:

·        Create column headers in Excel for Transaction ID, Sender's Name, Amount, Send Date, Expected Settlement Date, Transaction Status, Days Outstanding, and any other relevant information.

2.     Data Entry:

·        Enter the details of each FedNow transaction into the respective columns.

2. Calculate Outstanding Transactions:

3.     Calculate Days Outstanding:

·        Use a formula to calculate the number of days each transaction has been outstanding. For example:

excelCopy code

=IF([Transaction Status Cell] = "Outstanding", TODAY() - [Send Date Cell], 0)

3. Categorize Outstanding Transactions:

4.     Age Categories:

·        Create age categories based on the days outstanding, such as "Current," "1-30 Days," "31-60 Days," etc. Use IF statements or VLOOKUP to categorize transactions.

4. Analysis and Reporting:

5.     Total Outstanding Amount:

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

6.     Charts and Graphs:

·        Use charts or graphs to visualize the distribution of outstanding transactions based on age categories.

5. Integration with QuickBooks:

7.     Export to CSV:

·        Save your Excel template as a CSV file.

8.     QuickBooks Import:

·        In QuickBooks Online, go to the "Banking" menu or the specific area where you manage transactions.

·        Select "Upload a file" or similar, and follow the instructions to upload the CSV file.

9.     Map Fields:

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

10. Review and Confirm:

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

6. Automation and Regular Updates:

11. Automated Alerts (Optional):

·        If using Excel for real-time monitoring, set up automated alerts or notifications when transactions are overdue or meet certain criteria.

12. Regular Updates:

·        Regularly update the Excel template with new FedNow transactions and mark settled or voided transactions accordingly.

7. Documentation and Audit:

13. Documentation:

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

8. Integration (if applicable):

14. Integration with FedNow System:

·        Explore integration options with the FedNow system or any associated APIs for real-time data updates.

Ensure that your 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 FedNow transaction data and QuickBooks records to ensure accuracy. Always refer to the latest documentation for QuickBooks and the FedNow system for any specific features or updates related to data integration.