To view the LINKS, right click on the link then select open in new tab.

1. INTRODUCITON.

────────────────────────────────────────────────────────────────────────────────

Bank reconciliation assures the completeness of banking activity. It is also a control for mitigating fraud and losses, so it should be the first line item on your period-ending closing process checklist for reconciliation.

2. EXECUTIVE SUMMARY.

────────────────────────────────────────────────────────────────────────────────

Reconciling the general ledger cash account with the corresponding bank account(s) is a strategic move for an efficient period-ending closing process. Bank reconciliations ensure that discrepancies are noted and also support assurances about account balances at the end of the accounting cycle for:

  • Occurrence – the existence of assets, liabilities, and interest in equity.

  • Rights & Obligations – rights to assets and obligations to the organization’s liabilities.

  • Completeness – that all assets, liabilities, and interest in equity are appropriately booked.

  • Valuation & Allocation – amounts resulting from measurements and estimates are appropriately recorded.

The bank records are your support for balances, and their activities are referred to as `Proof of Cash. ’

3. BUSINESS GOAL.

────────────────────────────────────────────────────────────────────────────────

The goal is to complete the bank reconciliation process by the end of the first business day of the accounting cycle. This will help facilitate efficient account assurances throughout the team’s period-end closing process. .

4. BUSINESS TASK.

────────────────────────────────────────────────────────────────────────────────

A bank reconciliation checklist is required for an efficient period-end closing process. To complete the bank reconciliation process on the first business day of the accounting cycle, at least once per week, you will need:

4.1. Log into the bank accounts.

4.2. Identify electronic payments for:

  • Interest Earned.
  • Deposits.
  • Bank Fees.
  • Electronic Payments.
  • Returned Checks.
  • Other errors identified by the bank.

4.3. Post the entries to the general ledger.

4.4. Update schedules (example: Loans).

5. KEY STAKEHOLDERS.

────────────────────────────────────────────────────────────────────────────────

  • The CFO.
  • The Finance Team.
  • The Accounting Team.
  • Internal Auditors.
  • External Auditors.

6. METHODOLGOY.

────────────────────────────────────────────────────────────────────────────────

6.1. A Detailed Checklist.

To prevent efficiency losses from workflow interruptions, it’s important to mitigate the restart of the bank reconciliation process, especially for complex bank reconciliations. Creating a checklist before beginning the reconciliation process is crucial to maximize efficiency. Make sure that every item listed below is included in your checklist.

6.2. Confidence.

6.2.1. Supporting the balances requires footing values, calculating differences, and tying the details to the summary page with formulas to ensure financial assurance.

6.2.2. Avoid hardcoding values as it reduces confidence.

6.3. Naming Conventions.

Please make sure to follow the checklist item for saving files. It’s important to use the pre-established naming convention for saving files, as deviations can cause confusion and should be avoided. However, I recommend using the global standard for date and time format (YYYYMMDD) when naming a file as it will help in audits due to naming conventions.

  • For example: <2024_06_31-Passbook-8907.xls> In this example, “Passbook” refers to a bank reconciliation, and “8907” represents the last four numbers of the bank account.

6.4. Bank Activity.

6.4.1. Remember to use universally accepted formats when reconciling large volumes of transactions in Excel. Start by uploading the related CSV files into Excel. These files may contain data and metadata that are not relevant to the bank reconciliation process. Cleaning and transforming the information is an essential step in the reconciliation process.

6.4.2. My preferred method for cleaning and transforming is to use Power Query. However, pivot tables, formulas, or the copy-and-paste method can achieve the same result.

6.5. Proof of Cash.

You must download the bank statements as PDFs. You will need to reference these statements to identify the account balances and provide support for bank activity.

6.6. Electronic Payments.

Aggregated business expenses include credit card and fuel card payments, as well as payroll checks. Your organization may need transaction details included in the bank reconciliation report. Otherwise, aggregated activities will have a separate reconciliation report.

  • PDFs are used to vouch the electronic payments to the activities.

  • CSV files are used to trace the activities to the payment and to create reports and dashboards for management.

6.7. Outstanding Checks

A general ledger report from accounts payable is required for the bank reconciliation to identify outstanding checks. If you can access the general ledger, get the report yourself. Otherwise, you will need to rely on AP to create the report for you, resulting in a loss of efficiency.

6.8. Update Schedules.

Not all periodic activities have monthly statements. In place of a statement, they must have a corresponding schedule. The schedules can be an Excel file or activity that lives in a database.

6.9. Bank and Book Balances.

The adjusted ending bank values from the previous period’s passbook is the beginning book balance of the current period.

6.10. Checklist.

Here’s a detailed bank reconciliation checklist that’s ready to use for up to three bank accounts. I’ve successfully applied this checklist to manage 15 bank accounts for a single entity. However, keep in mind that some organizations may have more than one bank account under a GL number, so it’s crucial to be aware of this.

6.10.1 Downloads.

Bank Account 1: Download Date: _____________ Time: _____

  1. ___CSV file Downloaded: 20240531-BOA-8907-activity.csv
  2. ___Bank Statement Downloaded: 20240531-BOA-8907-stmt.pdf

Bank Account 2: Download Date: _____________ Time: _____

  1. ___CSV file Downloaded: 20240531-BOA-8908-activity.csv
  2. ___Bank Statement Downloaded: 20240531-BOA-8908-stmt.pdf

Bank Account 3: Download Date: _____________ Time: _____

  1. ___CSV file Downloaded: 20240531-BOA-8909-activity.csv
  2. ___Bank Statement Downloaded: 20240531-BOA-8909-stmt.pdf

Credit Card(s): Download Date: _____________ Time: _____

  1. ___CSV file Downloaded: 20240531-BOA-8909-activity.csv
  2. ___Credit Card Statement(s) Downloaded: 20240531-BOA-8909-stmt.pdf

A/P Report: Download Date: _____________ Time: _____

6.10.2 The Bank Reconciliation Report.

6.10.2.1. __ Copy the previous period’s passbook. Then name it appropriately 20240531-Passbook-8907.xlsx

6.10.2.2. Do not proceed until the three line items below are checked. Else, you will lose time.

6.10.2.3. __Open the file and verify that its beginning book balance is tied to the adjusted ending bank balance of the passbook for the prior period. This provides assurances that the correct balances are being transferred forward.

6.10.2.4. __ Verify zero differences between the adjusted ending bank balance and the adjusted ending book balance.This requires that you check that the summary values are not hard coded and are linked to the corresponding tabs appropriately.

6.10.2.5. __ Verify that the bank statement (PDF) for the current period’s beginning balance is tied to the **ending balance of the previous period’s passbook. This assures that you have the correct bank statement.

6.10.2.6. ___ Copy the previous period’s adjusted ending bank balance and paste it as the beginning book balance for this bank reconciliation report. The last period ending bank balance is this period’s beginning book balance.

The adjusted ending bank and book balances should be equal. However, experience has taught me that this method has helped me identify issues expeditiously.

6.10.2.7. ___ Copy the values of the current period-ending bank balance from the PDF of the bank statement and paste the values to the ending bank balance on the reconciliation report.

6.10.2.8. ___ Banking Activity CSV file upload to the Passbook. Name the tab [Raw Data-Bank].

6.10.2.9. ___ A/P Report CSV file upload to the Passbook. Name the tab [Raw Data – GL].

6.10.2.10. ___ Tab Creation:

  • Clean and transform the raw data with new worksheets without altering the raw data.
  • A tab should be included for each aggregated line item under the beginning book balance.
  • A tab should be included for each aggregated line item under the Ending Bank Balance.
  • There are many tabs, so keep the names as short and descriptive as possible.

7. EXAMPLE OF A PASSBOOK.

7.1. Tabs.

7.1.1. Summary totals require that they be supported with the details. This is accomplished by grouping transactions, calculating totals, and then linking the totals to the summary page. Tabs will include the following or more.

  • Summary
  • DIT
  • Outstanding Checks
  • Deposits
  • Bank Fees
  • Credit Card
  • Inter-company Transactions
  • Cleared Checks
  • ACH’s
  • Issued Checks
  • Raw Data- Bank
  • Raw Data-GL
  • Voids (a required list by healthcare providers)

7.1.2. Makes sure to title and date each tab appropriately.

7.2. Outstanding Checks.

The process to identify outstanding checks is the most probable cause of differences between the adjusted ending book value and the adjusted ending bank values. Current period outstanding checks is the difference between the [previous periods outstanding checks plus current period issued checks] and [ cleared checks].

7.2.1. To calculate the outstanding checks:

  • First copy the list of outstanding checks from the previous passbook.

  • Append the list with the current period issued checks provided by the AP report.

  • Copy the cleared checks, located on the the CSV file from the banking activity, and place it next to the [appended list of previous period’s outstanding checks and current periods issued checks].

  • Use an xlookup command to identify matching check numbers. Alternatively you may use vlookup or index(match), however you need to understand the limitations to these Excel functions.

  • Unmatched checks on the appended list is your current period outstanding checks.

7.2.2. However, its always more complicated than simply using an Excel function to locate outstanding checks. Duplicate check numbers resulting posting errors (bank or AP) must be addressed.

7.2.3. Healthcare providers may carry outstanding checks for years. Do not void these checks unless the checks specifically state void after XX days. Even then, you will need to maintain a voided check list.

8. INDEX.

────────────────────────────────────────────────────────────────────────────────

Office of Washington State Auditor: (Bank Reconciliations 3.1.9.5) https://sao.wa.gov/bars-annual-filing/bars-gaap-manual/accounting/accounting-principles-and-internal-control/bank-reconciliations#:~:text=Bank%20reconciliations%20are%20a%20necessary,against%20certain%20types%20of%20fraud.

Universal CPA Review: (What assertions are included in the account balance category?) https://www.universalcpareview.com/ask-joey/what-assertions-are-included-in-the-account-balance-category

9. MY LINKS.

────────────────────────────────────────────────────────────────────────────────

Linkin.com: (Fernando Cisneros) https://www.linkedin.com/in/luis-fernando-cisneros-672494bb/

RPubs.com: (The Checklist for Period-Ending Closing) http://rpubs.com/FernandoCisneros/1187465

RPub.com: (Period-End Closing Checklist for Accounts Receivable, by Fernando Cisneros) http://rpubs.com/FernandoCisneros/1193568