Jason Wethe – Page 7 – Bank Recon Club

This template for bank reconciliation excel comprises two sheets with formulas for automation. The book debits sheet has sample data from the bank account of the depositor. This sheet has an excel formula to illustrate how deposits are reconciled. The Bank credits sheet has data from a bank statement. The formulas in the template can assist in the reconciliation of bank accounts. The template illustrates the steps used for reconciling transactions between depositors' records and bank statements. MS Excel is a tool so powerful, because of its advanced functions. First, its advanced functions such as SUMIF, VLOOKUP, and COUNTIF are excellent since they can automatically match data from two or more sheets with hundreds and thousands of rows. Next, the unmatched rows are sorted to list the reconciling items. The SUMIF function is useful for reconciling bank deposits because it adds all rows in a column when the range column meets a certain criterion. This is useful in tagging all book debits with all bank credits through a unique reference number, for example, an official receipt or a batch number. Another helpful function is the VLOOKUP function, and it is excellent for reconciling check disbursements because it can lookup values based on a check number. It can also help calculate differences in the amount between the book and the bank. Given the above functions, is it still recommended to buy an automated reconciliation software? To explain why it is not, the reconciliation process in Excel is still the same with paid software, that said the only difference is that a premium software automatically sorts reconciling items. However, it is already simple to sort those items with one click in excel. Hence, Excel is enough for bank reconciliation purposes because it can do what a premium app can do. Note: The accounting is not expected to buy a premium software for bank reconciliation because it is expensive, but they can, only if they have excess funds. What is manual reconciliation? Manual reconciliation is a bank reconciliation process in which the reconciler individually matches each recorded transaction in the depositor’s book with the Bank statement. It is trivial because it takes time to accomplish and it is not recommended for most businesses because of the enormous number of transactions. The manual process of bank reconciliation statements is usually not efficient and effective with multiple bank accounts since it takes a lot of time. Hence, automated reconciliation is preferred to save time and excel can also assist in the reconciliation monthly. The periodic bank reconciliation is simpler because of Excel's advanced functions such as SUMIF and VLOOKUP. Those functions are already available in an excel spreadsheet where all data lives. Reasons for reconciling a Bank statement Preparation of bank reconciliation statement ensure that the cash balances are free from human errors, material misstatements, and fraud since they prove the balances. Errors are usually unavoidable because of processing several transactions, so it is important to do periodic reconciliations. To correct all mistakes and ensure unrecorded transactions are accounted for, periodic reconciliation should be made. Monthly bank reconciliation can also detect unauthorized transactions that affect the reliability of a balance sheet since it detects unrecorded transactions. Since the depositor's record is usually not balanced with the bank statement, reconciliation between both records is necessary. (source) What do you get from a bank account reconciliation? Here are the most common reconciling items found between a bank book and a depositor’s book: Deposit in transitsUnrecorded deposits and interest incomeBank ChargesOutstanding checksBank errors Excel Formula for Reconciliation The three Excel formulas for reconciliation are the VLOOKUP, SUMIF, and COUNTIF. First of all, the VLOOKUP locates a value in a table and returns a value based on a column number. It is useful in reconciling the amounts that are recorded in two financial records. Accountants use the formula or function to look for outstanding checks in a bank reconciliation. Next, the SUMIF adds together the amounts when a value is found in a range. It is useful when calculating deposits in transits. Also, the COUNTIF counts the number of times a value exists in a range. It is helpful for detecting double posting. How to automate reconciliations in excel? Automating bank reconciliations is possible through functions such as VLOOKUP, SUMIF, and COUNTIF in excel. To illustrate VLOOKUP, all check issuance records are matched with the bank statement debits quickly, and all amounts are checked for amount variances through the identification of differences. Then, all unmatched transactions are identified as the reconciling items in both the depositor's record and the bank statement. For deposit reconciliations, the SUMIF function can match all deposits with the bank statement by using deposit batch references. All collections are usually deposited in bulk to a bank since they cannot be done individually. To match the depositor's record and a bank statement, the SUMIF function can quickly add and match all bank credits using the batch numbers. Finally, the COUNTIF function identifies duplicates or multiple records of a single cash transaction by counting all instances of criteria. This can help uncover book and bank recording errors since the double posting of entries is usually common. Why use an excel bank reconciliation template? A bank reconciliation template can help expedite the preparation of reports, especially for multiple bank accounts, because the process becomes mechanical. To expound, all data are plugged into the template directly to see immediate results. Without a template, reconciliations usually require much effort, because there is no format being followed. To increase the speed of bank reconciliation, using a free bank reconciliation template can help achieve it. How to use the template? To produce a bank reconciliation statement, follow the following steps: 1. Supply all the data, such as book debits, bank credits, book credits, and bank debits. For multiple accounts, be sure to check the account number in the book and the bank matched. 2. Use VLOOKUP, SUMIF, and COUNTIF functions to match the transaction between the cash balance of both the bank and the depositor’s record. 3. Sort the rows from the excel sheets through unmatched transaction columns and list the reconciling items. 4. Prepare the bank reconciliation statement or report. The template does not include reconciliation of book disbursements, since users can actually use the VLOOKUP function with check numbers in their reconciliation. That function matches check numbers between the depositor's record and bank statement, and unmatched items would usually be the outstanding checks. Bank Reconciliation Worksheet This bank reconciliation worksheet can quickly assist the user in the bank's preparation reconciliation report. First, it illustrates how to match all deposits recorded in the depositor's book with the bank statement, and all unmatched transactions appear to be the reconciling items. Download Now Note: We offer financial services which include reconciliations of bank transactions and depositor's records. Let our accounting team do the accounting process for you. Read Also: Does bank reconciliation affects the balance sheetRisk of not performing bank reconciliationsFunctions of bank reconciliation statementsBank reconciliation rulesTypes of transactions a bank reconciliation looks atShould a bank reconciliation match a balance sheetHow to use a bank reconciliationWhy bank reconciliation is important to the auditorThe bank reconciliation is completeUnrecorded deposits in a bank reconciliation