In this video, I'll show you how to use Copilot for Excel to create a Reconciliation report. The goal is to compare Financial records in a few minutes using the Copilot for Finance add-in.
🎓 LEARN MORE in my Excel tutorials: excelkid.com/
🔔 SUBSCRIBE if you’d like more tips and tutorials like this.
🎁 SHARE this video and spread the Excel love.
Chapters:
00:00 Intro
00:09 How to use Copilot for Finance in Excel
00:34 How to Reconcile Data - Compare Financial Records
00:43 Create Mapping Keys and Monetary Column
01:30 Overview of Copilot Financial Report
01:41 Transaction types
01:52 Matched Transactions
02:15 Potentially Matched Transactions
03:12 Unmatched Transactions
03:22 Create a summary using ChatGPT
In the example, I'm using two Worksheets. On the first sheet, you can find a trip budget with dates, expenditure categories, and budget amounts.
On the second sheet, there is a bank statement with bank transactions on different dates for different amounts and we have the transaction categories.
Make sure that your data is in table format.
The next step is to click Reconcile Data to compare your financial records.
Select 2 worksheets and tables to reconcile, and click next. Now we need to select at least two columns, one of each type, for each table. First, add mapping keys to find matching records. The first key is the date.
Add the date column from the trip budget table, then add the transaction date column from the bank statement table. Click the add column to add one more mapping key. Select the Expenditure category column from the trip budget table. Then select the Transaction category from the bank statement table. Finally, add monetary columns which are used to reconcile values. Select the budget amount column from the trip budget table. Then select the Amount column from the bank statement table.
Okay, click next.
On the left side, we have the records from Trip Budget on the right side we have the records from the bank statement. In column I, the report shows the differences between the two tables. We have here three different types of Records, which are unmatched transactions, potentially matched transactions, and matched transactions. Take a look at the matched transactions.
For February 19 for flights from the trip budget, we have $700, then from the bank statement we also have for February 19 flights with $700, so they match perfectly and the difference is zero. All the records over here that matching, and the difference will be zero. The second group contains potentially matched records. Here we have four records. On February 19 for the food from Trip budget, we have $120. From the bank statement for February 19, food will also be $120. So, the difference is zero, and the records match perfectly. The question is why this is being classified as a potentially matched transaction rather than a matched transaction. One-to-one matching means this is a single record from the trip budget it matches all single records in the bank statement.
#excel #finance #exceltips #copilot