Akib Bin Rashid
Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. It is possible to reconcile credit card statements in Excel. In this article, I will explain how to reconcile credit card statements in Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
Introduction to Credit Card Reconciliation
Accountants employ a process called credit card reconciliation to ensure that transactions on a credit card statement and those on the business’s general ledger match up. To maintain effective and accurate bookkeeping, businesses need to ensure that the statements reconcile.
The simplest way for accountants to do this is to compare the general ledger with company credit card statements. The ledger is correct if each payment in the book matches a payment in the statement.
4 Suitable Steps to Reconcile Credit Card Statements in Excel
This is the dataset for today’s article. We have statements from both the bank and the cash book from the company. However, there are some discrepancies in these statements. So we need to reconcile them. I will show step by step how to do that.
Step 1: Prepare Format for Credit Card Reconciliation
Our first step is to prepare a format for credit card reconciliation. The format includes all the differences between the statements from the bank and the cash book. We have to add or subtract amounts from the unadjusted balance. The format will look like this.
Step 2: Find Mismatches Between Statements
The next step is to find out the mismatches that are present in the statements. To do so, we will use the COUNTIF and IF functions.
- First of all, create a helper column in the cash book statement.
- Then, write down the following formula in F15.
=IF(COUNTIF($D$6:$D$11,D15),"","Mismatched")
Formula Breakdown:
- COUNTIF($D$6:$D$11,D15) → This is the logical test. If the value in D15 is present in the range D6:D11, the condition will be TRUE, else FALSE.
- Output: TRUE.
- IF(COUNTIF($D$6:$D$11,D15),””,”Mismatched”) → This is the formula. If the condition is TRUE, output will be a blank, else, “Mismatched”.
IF(TRUE,””,”Mismatched”) - Output: “”
- Now, use the Fill Handle to AutoFill up to F20.
Step 3: Record Mismatches in Reconciliation Statement
Now, you need to adjust the mismatches in the reconciliation statement. While doing so, you must be careful about which amount to add and which one to subtract.
In our case, the Electricity Bill and Salary Expenses are mismatched. The Electricity Bill is $20 lesser than the original payment. That means, the due was $200, but mistakenly the payment was $220. So from the cash book balance, we have to deduct $20.
Similarly, the mismatch for Salary Expense is $100. But this time you have to add it to your cash book balance.
Step 4: Calculate Adjusted Balance
Now, you have to calculate the adjusted balance. To do so,
- Go to E14 and write down the following formula
=E9+C11-C13
- Now, press ENTER. Excel will calculate the adjusted balance.
Read More: How to Calculate Credit Card Interest in Excel (3 Easy Steps)
Things to Remember
- Be careful about when to add and when to subtract an amount.
- Your cash book balance should match with the bank statement
Conclusion
In this article, I have illustrated how to reconcile credit card statements in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Please visit Exceldemy for more useful articles like this.
