Bank Reconciliation Spreadsheet is a handy tool intended to simplify the verification process of the bank’s statement with the company’s cash account. Reconciliation is necessary on specified intervals to tally both accounts, as certain transactions do not reflect in bank’s statement as well as in the company’s cash account respectively.
This tool offers a simple platform to make entries and arrive to a conclusion. Using such spreadsheet also helps in identifying and reporting discrepancies if any to the bank. In addition, such tool offers substantial help when reconciling hundreds and thousands of transactions every month is necessary.
However, you must enter values and other details in grey colored fields as this spreadsheet is programmed to deliver results once the values are entered in these fields. You may choose to enter values, save, or print this spreadsheet; however, do not modify its content or programmed cells for maintaining accuracy during the reconciliation. Please ensure to keep the records like company’s cash ledger, bank statement, and any other information handy before starting the reconciliation as partly entered values and information will not help you in arriving to a conclusion. Reconciliation process is complex as certain entries reflect in different months in bank statement and company ledger; however, using such pre-programmed spreadsheet helps in faster and accurate reconciliation.
Steps to prepare Bank Reconciliation Spreadsheet
- Various inputs are required in the Bank Reconciliation Spreadsheet. For an example, values and dates for all deposits in transit or amount and date of each outstanding check are required.
- Please make entries in the grey colored cells only as and when required.
- Enter the month and year for reconciliation in the topmost cell on the left.
- Please enter date of the statement issued by the bank.
- Then enter the statement closing balance below the statement date.
- Use fields on line 1 through 8 to enter value and dates of all deposits in transit. Please use separate line for each entry. These boxes have values that do not reflect in the bank’s statement due to partial processing. You will receive the sum of all values below the line 8 once you complete entering values.
- Then enter the date and amount of each outstanding check on a separate line. Ensure that each entry in this field does not reflect in the bank statement. The Bank Reconciliation Spreadsheet calculates the total of all outstanding checks automatically once you enter all amounts on the respective lines.
- Calculated account balance is then displayed on the last line of the Bank Reconciliation Spreadsheet. You can compare this value with the bank statement during the reconciliation.
- Please save this spreadsheet and use it in the similar manner for the next reconciliation. It serves you every time unless you alter the programmed cells in some or the other way.