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.
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.
How to Reconcile Credit Card Statements in Excel: 4 Suitable Steps
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.
Read More: How to Do Bank Reconciliation in Excel
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.
Read More: How to Reconcile Vendor Statements in Excel
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.
Read More: Automation of Bank Reconciliation with Excel Macros
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 Reconcile Data in Excel
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
Download Practice Workbook
Download this workbook and practice while going through the article.
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.
Related Articles
- How to Do Intercompany Reconciliation in Excel
- How to Do Reconciliation in Excel
- How to Reconcile Two Sets of Data in Excel
- How to Reconcile Data in 2 Excel Sheets
- How to Perform Bank Reconciliation Using VLOOKUP in Excel
<< Go Back to Excel Reconciliation Formula | Excel for Accounting | Learn Excel