Bank Reconciliation Simplified: Managing Multiple Transactions with Identical References

Get it from Microsoft Store: 👉 ​ ​appsource.microsoft.com/en-us... 👈​
00:00 How to match multiple transactions in bank reconciliation
00:43 The business goal - match multiple transactions in orders table and payments table
01:55 Use Power Reconcile add-in to automate this task in 3 steps
03:16 Use manual reconcile to follow up and improve matching results
03:37 Filter the unmatched records for further processing by the AP Team
Content:
Welcome to the Power Reconcile case study! In this video, we'll walk you through the process of reconciling multiple customer transactions.
To get started, let's consider a scenario where you have two sheets in your Excel workbook. The orders sheet and the payments sheet.
The orders sheet contains the company orders, while the payments sheet lists the customer payments.
The goal is to match the orders with the corresponding payment. Sometimes, customers may combine payments for multiple orders, leading to fewer payments than orders, but we'll show you how to solve this issue using Power Reconcile add-in for Microsoft Excel.
Before we dive into the technical aspect, let's take a step back and understand the business goal.
Let's say we have a customer named SpaceX and we want to reconcile their orders and payments.
To do this, you'll need to filter the orders sheet by company name, which is SpaceX in this case, and calculate the sum of the amount column, which should be 160,460.
Similarly, we’ll filter the payments sheet to only show SpaceX and calculate the sum of the payments, which is also 160,460, the same as the sum from the orders sheet.
Awesome, that’s a perfect match! And just like that, the reconciliation of the first customer's orders and payments is complete.
However, repeating this process for each customer can be time-consuming, tedious, and error prone, especially if the list of customers is long.
Let’s see how we can simplify the reconciliation process using the Power Reconcile Add-In for Microsoft Excel with 3 easy steps.
First of all, Open Power Reconcile Add-in by clicking the icon in the Excel Ribbon.
Step 1, you'll need to specify where the customer names can be found. Select the range from the orders sheet, then set the data table (a) range. Set the reference column to "Customer" and the amount column to "Amount".
Step 2, repeat the same steps for the payments sheet, selecting the range from the payments sheet, setting the data table (b) range, and setting the reference column to "Payer" and the amount column to "Payment Amount".
Step 3, after completing the configurations, you'll see the report button become enabled. With just one click, you'll generate a report that provides a detailed analysis of the reconciliation.
For example, you can view all the perfectly matched records by clicking Status "1" on the slicer.
You can click “2” to find out all the customers whose orders and payment amounts don't match.
You can click “3” to find out all the customers with unpaid orders.
Finally, you can click “4” to find out all the customers who have paid but have no orders.
In the event that you come across a discrepancy, such as Netflix and NF potentially referring to the same customer, you can perform a manual reconciliation by clicking the "Manual Reconcile" button in the report.
For the unmatched records, you can share the report with the accounts payable team to ensure that payments are collected in a timely manner.
We hope you enjoyed this case study and found it helpful. Please subscribe and comment if you have any questions. Thank you for watching!

[@friendbook168]

Good lesson, I like content that you have made. Stay connect. Thanks

[@syedaneesdurez8766]

Thank you.Hiw to add in this tool

[@power-reconcile]

Hi Syed, Thanks for your interest! You can get it from Microsoft Store: 👉 ​ ​appsource.microsoft.com/en-us/product/office/WA200002531??mktcmpid=youtube_3&src=website 👈​