QuickBooks^® Request for Payment Invoicing

To sync up an "Outstanding Checks" Excel template with QuickBooks Online (QBO) and QuickBooks Desktop for updating bank reconciliation, you can follow these steps:

1. Excel Template Setup:

1. Create Excel Template:

• Set up an Excel template with columns for Check Number, Check Date, Payee, Amount, Status, Reconciliation Date, Reconciliation Status, and any other relevant details.

2. Data Entry:

• Enter details for each outstanding check into the respective columns.

2. Status Tracking and Reconciliation Process:

3. Status and Reconciliation Columns:

• Include a "Status" column to track the check's status (Outstanding, Voided, Cleared) and a "Reconciliation Status" column to mark whether it's reconciled.

4. Days Outstanding Calculation:

• Create a column to calculate the number of days each check has been outstanding.

3. Categorize Outstanding Checks:

5. Age Categories:

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

4. Analysis and Reporting:

6. Total Outstanding Amount:

• Use formulas to calculate the total outstanding amount and represent the data visually.

5. Integration with QuickBooks:

7. Export Data from Excel:

• Save the Excel template as a CSV file.

8. QuickBooks Online Integration:

• In QBO, go to the "Banking" menu and select "Upload a file" or use the appropriate option to import data.

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):

• Set up alerts or notifications for specific conditions (e.g., overdue checks) if you're using Excel for real-time monitoring.

12. Regular Updates:

• Regularly update the Excel template with new checks and reconcile the template with actual bank statements.

7. Documentation and Audit:

13. Documentation:

• Keep a record of checks, updates, and any adjustments for audit and reference purposes.

8. Integration (if applicable):

14. Integration with Bank:

• Explore integration options with your bank or any associated financial software for real-time updates.

Notes:

• Ensure that the CSV file is properly formatted and the data aligns with the fields in QuickBooks during the import process.
• Check for any updates or new features in QuickBooks that may enhance the import functionality.
• Regularly reconcile the Excel template with the actual bank statements and QuickBooks records to ensure accuracy.

Always refer to the latest documentation for QuickBooks Online and QuickBooks Desktop for any specific features or updates related to data integration and reconciliation.