FedNow Reconciliation

If you are managing invoicing, funding, and Request for Payments (RfP) outside of QuickBooks Online (QBO) and relying on your spreadsheet solutions, including Bank Reconciliation Aging, SASF (Statement of Account and Settlement Files), and FIRD (Funds In/Remittance Detail) files, here's a guide on how you might structure your spreadsheet to efficiently handle these processes:

1. Spreadsheet Structure:

Columns:

1. Transaction Date:

• Capture the date when the transaction occurred.

2. Invoice/Transaction ID:

• Assign a unique identifier for each transaction or invoice.

3. Merchant/Payer Details:

• Include information about the merchant or payer involved in the transaction.

4. Description:

• Briefly describe the nature of the transaction.

5. Sent Amount:

• Record the amount sent in the case of outgoing payments.

6. Received Amount:

• Record the amount received in the case of incoming payments.

7. Authorization Status:

• Indicate whether the RfP has been authorized by the payer.

8. Aging Status:

• Implement an aging classification (e.g., 30 days, 60 days) to track the status of outstanding RfPs.

9. SASF File Reference:

• Include a column to reference the associated SASF file for each transaction.

10. FIRD File Reference:

• Include a column to reference the associated FIRD file for each transaction.

2. Bank Reconciliation:

Steps:

1. Bank Statements:

• Regularly import or manually enter bank statement information into the spreadsheet.

2. Match Transactions:

• Match the transactions in your spreadsheet with those on the bank statement.

3. Reconciliation Notes:

• Include a section for reconciliation notes to record any discrepancies or adjustments made during the reconciliation process.

3. Aging Analysis:

Formulas and Conditional Formatting:

1. Aging Calculation:

• Use formulas to calculate the aging of outstanding RfPs based on the difference between the transaction date and the current date.

2. Color Coding:

• Apply conditional formatting to visually highlight aging status (e.g., color cells differently based on aging categories).

4. File References and Tracking:

1. Linking Files:

• Hyperlink or create references to the specific SASF and FIRD files associated with each transaction in your spreadsheet.

2. Tracking Changes:

• Use version control or a change log to track any modifications made to SASF and FIRD files.

5. Automation and Alerts:

1. Automated Reminders:

• Set up automated reminders within the spreadsheet to notify you or relevant team members about aging RfPs.

2. Alerts for Authorization:

• Implement alerts or conditional formatting to draw attention to RfPs that haven't been authorized within a specified timeframe.

6. Documentation and Compliance:

1. Record Keeping:

• Keep detailed documentation within the spreadsheet, including any notes on authorization status, adjustments, or communications with merchants.

2. Compliance Checks:

• Regularly review and update your spreadsheet processes to ensure compliance with applicable regulations.

Note:

While spreadsheets can be effective for managing smaller volumes of transactions, you should be aware of their limitations, particularly as your business grows. Consider the benefits of dedicated financial software or systems that can provide more advanced features and better scalability. Also, ensure that your spreadsheet practices align with security and compliance standards in your industry.