Today, you’ll learn how to reconcile deposits in transit.
Deposits in transit (DITs) take time to reconcile. First, book and bank records have no common references. The reconciler could not use tools such as VLOOKUP function in Excel to find values on both records. In addition, the reconciliation cannot be fully automated. Each deposit is compared to the bank statement individually. Also, deposits should be supported with deposit slips for easy reconciliation. A deposit may have many deposit slips that add to its complexities.
To reconcile deposits in transit, the six (6) easy steps below will guide the reconciler for fast and reliable identification of deposits in transit. For instance, each step is explained thoroughly for maximum efficiency. The reconciliation is manual, but the steps will help increase the speed of the reconciliation. Next, all steps should be followed chronologically to reach the desired goal. Starting from the last step is not recommended. Also, the important tool used in this post is Excel. Using premium software is not ideal to reduce costs.
1. Get the previous month’s list of deposits in transit
The previous month’s deposits in transit are also reconciled in the current month. First, the bank reconciliation would be unbalanced when they are ignored. The reconciler’s job is to remove cleared deposits from the list and monitor the remaining DITs. Also, prior and current months’ in-transit deposits are combined to calculate the actual deposits in transit at the month’s end. The remaining DITs comprise all in-transit deposits from the previous and current months. In addition, the prior month’s deposits in transit may have already been cleared in the current month. So, each DIT is evaluated when preparing the bank reconciliation statement.
2. Get all the current month’s book debits
A Book debit may comprise cash and check deposits to a bank on a particular day (s). For instance, all deposits on a particular day are recorded in a journal entry. Some accountants record money deposits on a single entry to simplify the recording, but should be supported with one or more deposit slips and schedules. Also, book debits should exclude canceled deposits to focus on deposits only. In addition, book debits must include fund transfers made to a bank account. These are transfers from one bank account to another.
The book debits should be taken from the correct account number of the cash book. Indeed, some reconcilers will fail because another account number was used. The worst that can happen though is to get the data again increases the time of the reconciliation. For instance, account number 100002 was retrieved instead of account 100001. This is common even with seasoned reconcilers. For this reason, try to get data from the correct source.
3. Assign a deposit number to each book debits
The deposit batch numbers will be used later for the reconciliation of deposits in transits. For instance, they are used as the criteria for an Excel function. One example is the SUMIF function that requires a value to add data (In this case, the deposits). Also, they are for tracking. They can be dates of the cash and checks deposits. In addition, they are the key link between the book and bank credits. They are used to knowing if a deposit has cleared in a bank.
4. Get all bank credits
Bank credits are all money deposits and transfers that are already recorded by a bank, as shown in a bank statement. For instance, a cash deposit is credited by a bank since it is a payable to depositor. They store it for you and you can withdraw it anytime. Also, bank credits include fund transfers. So, the data is useful in identifying deposits in transit at the end of a month. All unrecorded deposits by the bank are obviously the deposits in transit.
The data from the bank credits are arranged, the same with the book debits. For example, do not forget to include dates, amounts and details. They are important that can be useful in identifying unknown deposits. Also, the data must be arranged chronologically. This is necessary because the reconciliation is manual. In addition, the total should equal the total of all bank credits. The bank reconciliation statement could become unbalanced just because of some incomplete data.
5. Assign deposit numbers to bank credits from the book debits
The deposit numbers should be used strategically. For instance, deposit batch numbers are taken from the book debits and prior deposits in transit. They should match the information provided by the book debits. Placing unknown numbers will render the information useless. Also, the numbers should make sense. Use years, months and days when possible. It becomes easy to identify which period a deposit was recorded in the cash book. In addition, they can be used multiple times. Some money deposits are sent to a bank on difference days, e.g. 2022720.
Deposit batch numbers should be accurate. Truly, a mistake can ruin the calculation. An incorrect number will not be picked up by the SUMIF function, as described in the next step below. However, because the reconciliation is manual, a mistake can cause redoing the reconciliation from the start. It will decrease the speed of the reconciliation. Surely, being careful while filling up the batch numbers will increase the efficiency of the reconciliation. Slow and perfect pacing is better.
6. Use the SUMIF function of Excel to determine how much deposit was found on the bank credits
In the book debits, use the SUMIF function to add all bank credits with the same deposit batch numbers. For instance, use Column B from bank credits as the range of the function. It is the column where the deposit batch number lives. Also, use Column B of book debits as the criteria. This will specifically search for a value in the range. Finally, Column D of bank credits is the Sum range. This column is summed up when a criterion is found in column B.
Next, compare book and banks credits to have a rational analysis of the data. To demonstrate, the differences are determined to calculate the under and in-transit deposits. Zero balance means that a deposit entry was completely sent to a bank. In addition, the positive balances in the difference column are likely the deposits in transit. They are already recorded in the cash book of an entity but are not yet recognized by a bank. Finally, sort all deposits in transit to create the list for the bank reconciliation.