Version 1.0
Prepared by: @Lorraine Sebata
Approved by: @Feli Capron @Marcia Wilkinson
Reviewed date: 2024-07-24
Next review date: 2025-07-24
This Standard Operating Procedure (SOP) is designed to ensure accurate, timely bank account reconciliation by establishing a structured approach for importing bank statements and performing reconciliations in Google Sheets and Xero. The objective is to maintain up-to-date financial records, promptly identify and resolve discrepancies, prevent errors, and ensure compliance with internal financial controls and external reporting requirements.
This SOP applies to all Accounting Team members responsible for performing bank reconciliations and importing bank statements for all bank accounts held by Easyterms. This process is performed regularly daily to ensure continuous accuracy of cash balances.
Accounting Team Member: Responsible for downloading bank statements, preparing them for import using Google Sheets, importing statements into Xero, and performing the bank reconciliation.
Bank: Provides access to bank statements and transaction data.
Management Team: Reviews and approves reconciliation reports and authorizes necessary adjustments.
5.1 Access and Download Bank Statement
5.1.1 Log in to the bank's website (e.g., First Caribbean) using secure credentials.
5.1.2 Select the specific bank account for which the statement needs to be updated.
5.1.3 Choose the appropriate date range for the bank statement import (e.g., from the last reconciliation date to the current import date, or the entire month for efficiency).
5.1.4 Download the bank statement as a CSV (Comma Separated Values) file.
5.2 Prepare Bank Statement for Import using Google Sheets
5.2.1 Open Google Sheets.
5.2.2 Upload the downloaded CSV file into a new Google Sheets tab.
5.2.3 Open a separate new Google Sheets tab for the CSV statement import template. This template should be pre-formatted to match Xero's import requirements.
5.2.4 Carefully copy the relevant transaction information from the downloaded bank CSV statement (from Step 5.2.2) to the statement import template (from Step 5.2.3).
5.2.5 Fill in the required fields in the template, ensuring accuracy:
Description: Provide a clear and concise description for each transaction.
Instrument Number (if applicable): Enter cheque numbers or other relevant instrument identifiers.
Amount: Input the correct transaction amount. Use formulas if needed to accurately calculate amounts (e.g., for combined transactions or specific charges).
5.2.6 Cautionary Note: Ensure accuracy when copying information and double-check formulas/calculations to avoid errors. Be cautious when pasting data to prevent accidental data loss or corruption.
5.2.7 Once the template is accurately populated, download the updated Google Sheet as a CSV file.
5.3 Import Bank Statement into Xero
5.3.1 Log in to Xero.
5.3.2 Navigate to the 'Accounting' tab and select 'Bank Accounts'.
5.3.3 Locate the specific bank account and click on 'Manage Account', then select 'Import a Statement'.
5.3.4 Upload the prepared CSV file downloaded from Google Sheets (from Step 5.2.7).
5.3.5 Follow the prompts in Xero to map the columns from your CSV file to the correct Xero fields.
5.3.6 Confirm the import. Xero will process the transactions and attempt to automatically match them.
5.4 Perform Bank Reconciliation in Xero
5.4.1 After the import, go to the 'Reconcile' tab for the relevant bank account in Xero.
5.4.2 Review the transactions imported from the bank statement against the transactions recorded in Xero.
5.4.3 Match bank statement lines to existing transactions in Xero.
5.4.4 Create new transactions in Xero for any bank statement lines that do not have a corresponding entry (e.g., bank charges, interest income). Ensure proper categorization.
5.4.5 Investigate and resolve any discrepancies (e.g., unmatched transactions, differing amounts) by referring to supporting documentation (invoices, receipts, payment confirmations).
5.4.6 Ensure the reconciled balance in Xero matches the bank statement balance.
5.5 Finalization and Record-Keeping
5.5.1 Once the reconciliation is complete and the balances match, finalize the reconciliation in Xero.
5.5.2 Generate and save a 'Bank Reconciliation Report' from Xero.
5.5.3 File the downloaded bank statement CSV, the prepared Google Sheet CSV, and the final reconciliation report in a designated digital or physical archive.
5.5.4 Ensure all records are retained as per the Financial Reporting Policy for audit and compliance purposes.
Bank Account Login Credentials (securely stored)
Bank Statements (CSV files)
Xero Bank Statement Import Template
Xero Bank Reconciliation Report
Supporting Documentation for Discrepancies (e.g., invoices, receipts)
All Accounting Team members responsible for bank reconciliation and statement import will receive mandatory training on this SOP and related policies annually or upon significant revisions. Training will include efficient use of Xero and Google Sheets for this process.
This SOP will be reviewed annually by the Accounting and Finance Departments to ensure its continued effectiveness, compliance with financial policies, and alignment with business objectives. Special attention will be paid to updates in banking software or Xero functionalities.