QuickBooks^® Request for Payment Invoicing

If you are primarily using spreadsheet solutions for reconciling invoicing and funding for merchants, particularly focusing on Request for Payments (RfP) aging, you can structure your spreadsheet to manage both the "Sent" and "Received" funds efficiently. Here's a guide on how you might set up your spreadsheet:

1. General 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.

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. 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.

5. 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.

6. Communication and Transparency:

1.     Communication Log:

·        Include a section to log any communication with merchants regarding outstanding RfPs.

2.     Shared Access:

·        If multiple team members are involved, ensure that the spreadsheet is accessible and editable by authorized personnel.

Note:

While spreadsheets can be effective for small to medium-sized businesses, it's important to consider the limitations they might have in handling large volumes of transactions. Additionally, as your business grows, you may want to evaluate the benefits of using dedicated financial software or systems that integrate with QuickBooks or other accounting platforms to streamline processes and enhance accuracy.