❓What are your biggest challenges in financial reconciliations and data management that you hope this tool will address? Learn more with my Excel courses: bit.ly/copilotf24course
As a CPA I really wish my team and I had access to such tools back then. It's always a struggle to reconcile bank statements to our ledgers. I'm sure this will help professionals. AI is coming in strong. I think AI is just starting, it has major inaccuracies etc. But this is a great first step. Good show on the video!
Thanks for the video. Actually a reconciliation can be made via a classic Pivot Table. Just push these two datasets to PQ, invert paid amounts, name both amounts' fields similarly and append both queries. Then load it to Pivot Table and place Invoice ID as the leftmost row field, Amount to data area, the rest of fields elsewhere .That's it. Just filter Invoice ID by values: the ones that equal to zero will mean matched. There can be more complex analysis scenario in such Pivot Table. I've been using this technique for various reconciliations and find it pretty flexible and powerful.
Yes, we do something similar in this video: ua-cam.com/video/kjeR7nZ3Clw/v-deo.html However, Copilot requires zero skills to do the reconciliation, so I can see the appeal and benefit of using it over Power Query, or even formulas.
Great video, thanks for sharing. Question 1:Do both parts of reconciled data have to be in the same file ? Question 2: With addition of new data (new sales and invoices) are you able to continue the reconciliation, or a new process should be set again ?
Wow, this new tool for finance professionals is a game-changer! Saving time on reconciliations and variance analysis is a dream come true. Great stuff!
This is an interesting Add-In for Excel, thank you! If you've seen it, it works similarly to Julius AI - which can perform analyses on data on Excel and Google Sheets. I would love to see a review on that AI tool next!
I have hope for the future, I really do. But for now I could do this IN LINE, far more useful, with some lookup formulae. I don't trust any AI that MS spits out that is trying to solve a common problem but ... isn't actually reducing time but ADDING material the human has to understand, interpret and validate. But thanks Mynda, it's not your problem, the vid is well received!
Interesting insights. As someone who used to do a lot of reconciliations, I thought this was a great tool. I agree, you could use lookup formulas, but you have the same problem when invoices aren't paid in one transaction.
Thank for giving us some fabulous insight into this feature. There is one question I have. Does this add-on upload any data to the copilot server or does all the work happen on the computer of the user? Uploading corporate data to a copilot server may be something that is prohibited by data handling policies. Thank you, cheers. 😊
Everything to do with Copilot is handled in the cloud, but Microsoft have stringent policies about data handling like this, so I recommend you check it out.
Good morning. Thank you very much for introducing this function. However, the result is not what I was expecting. I followed the instructions but it didn't work. I noticed that the 'description' does not exactly match the two Excel Tables. For example: Description DR CR PF-1234-H 100 RETURN PF-1234-H 100 Thank you!
@@MyOnlineTrainingHub actually, that's the biggest difficulty in my job: to get the data unified. One person inputs full number of the contract, another inputs only the last few digits, someone could combine manual adjustment for the couple of the contracts with the same customer in 1 line, etc. You need to go to the details of the invoice, details of the manual entry, ask people... It all takes hours to reconcile when you're dealing with the thousands of lines. That's the challenge and not to sum up perfectly organized data.
Thanks for your interest in my courses. Data analytics is such a broad topic that I could say yes, but it really depends. If you reach out via email we can chat about what you have in mind exactly and I can point you in the right direction: website at MyOnlineTrainingHub.com
I work as an accountant in a large company that records thousands of invoices and related payments, and I have the problem that at the end of each month I have to reconcile the accounting transactions with the bank transactions. These are thousands of rows of transactions, each of which may correspond to one or more accounting entries. I cannot rely on a matching key, but only on amounts and sometimes dates or descriptions. This is where AI would be really helpful but I don't see this tool coming close to those goals. Do we have any idea if something like this will ever be made available?
I don't have any information about the future plans for this tool, however you could try using the amount column as both your mapping key and monetary column.
You'd need to perform the SUMIF twice, once on each table and then summarise the results. If you had multiple columns to match, you'd need SUMIFS. All doable of course, but some people might prefer to click a button and have the transactions that require attention summarised for them.
Great material👍 I am also creating a Polish channel about data analysis on UA-cam and I found a lot of inspiration from you. I really like your style and message. Good luck!😀
Yes, of course. If immaterial differences are irrelevant in your reconciliations, then I'd create a second 'amount' column that rounds the values appropriately.
Hi Mynda, this does not work for me. I added the add in, and did a reconciliation on my small 5 item test case. I deliberately made 3 items to match, and left the other 2. Copilot found 1 match. On each of the other items, it saw the same figure in table 2. Meaning copilot assumes the matched figure as the figure for all other items in the 2nd table, though my original table has their own figure. I removed the addin, reinstalled, tried again, but still no joy
I don't do consulting, sorry. However, I can refer you to a consultant if you want to reach out via email and give me a brief outline of your project: website at MyOnlineTrainingHub.com
I've written it above with spaces. Prefer not to type it in an email address format as I get enough junk male as it is and I'd rather not make it available to bots scraping the web. Here it is again, remove the spaces and replace 'at' with @ : website at MyOnlineTrainingHub.com
❓What are your biggest challenges in financial reconciliations and data management that you hope this tool will address?
Learn more with my Excel courses: bit.ly/copilotf24course
As a CPA I really wish my team and I had access to such tools back then. It's always a struggle to reconcile bank statements to our ledgers. I'm sure this will help professionals. AI is coming in strong. I think AI is just starting, it has major inaccuracies etc. But this is a great first step. Good show on the video!
Glad you can see the benefit of this tool 🙏
Since 2007 I do daily bank recs. No more struggles
I like her voice, it relaxes me, it calms me down. And as a bonus you explain very well.
Thank you 🙏
@@MyOnlineTrainingHub sorry, I've realized that I wrote his instead of her, I've fixed it. English is not my primary language and I didn't notice.
Thanks for the video. Actually a reconciliation can be made via a classic Pivot Table. Just push these two datasets to PQ, invert paid amounts, name both amounts' fields similarly and append both queries. Then load it to Pivot Table and place Invoice ID as the leftmost row field, Amount to data area, the rest of fields elsewhere .That's it. Just filter Invoice ID by values: the ones that equal to zero will mean matched. There can be more complex analysis scenario in such Pivot Table. I've been using this technique for various reconciliations and find it pretty flexible and powerful.
Yes, we do something similar in this video: ua-cam.com/video/kjeR7nZ3Clw/v-deo.html
However, Copilot requires zero skills to do the reconciliation, so I can see the appeal and benefit of using it over Power Query, or even formulas.
As a Revenue /product accountant this is so useful. Will you be doing a video once Co-pilot for finance is up and running ?
Great to hear. It's on my to-do list 😉
Copilot seems to be lowering the barriers to entry and at the same time empowering the experts.
Yep. I'm surprised this is a free add-in TBH.
Great video, thanks for sharing.
Question 1:Do both parts of reconciled data have to be in the same file ?
Question 2: With addition of new data (new sales and invoices) are you able to continue the reconciliation, or a new process should be set again ?
My pleasure. 1. Yes. 2. you need to set it off again.
Wow, this new tool for finance professionals is a game-changer! Saving time on reconciliations and variance analysis is a dream come true. Great stuff!
Glad it'll be helpful!
A dream come true....except for the people who will inevitably end up losing jobs as companies decide they can run leaner thanks to AI.
thank you always I'm always impressed by your videos. For your information, what video editing software do you use?
Thank you! I record with Camtasia Studio and editing is done in Premiere Pro.
Yes, we all know that it's easily doable manually. But the point is, it's the beginning. Copilot will get powerful over time.
No doubt 😊
This is an interesting Add-In for Excel, thank you! If you've seen it, it works similarly to Julius AI - which can perform analyses on data on Excel and Google Sheets. I would love to see a review on that AI tool next!
I've not heard of Julius AI. Will check it out. Thanks for sharing.
I have hope for the future, I really do.
But for now I could do this IN LINE, far more useful, with some lookup formulae.
I don't trust any AI that MS spits out that is trying to solve a common problem but ... isn't actually reducing time but ADDING material the human has to understand, interpret and validate.
But thanks Mynda, it's not your problem, the vid is well received!
Interesting insights. As someone who used to do a lot of reconciliations, I thought this was a great tool. I agree, you could use lookup formulas, but you have the same problem when invoices aren't paid in one transaction.
I'm very greateful four your great explanation, free tutorials and materials! Wish you more likes and followers!
Thank you very much!
Thank for giving us some fabulous insight into this feature. There is one question I have. Does this add-on upload any data to the copilot server or does all the work happen on the computer of the user? Uploading corporate data to a copilot server may be something that is prohibited by data handling policies. Thank you, cheers. 😊
Everything to do with Copilot is handled in the cloud, but Microsoft have stringent policies about data handling like this, so I recommend you check it out.
Good morning. Thank you very much for introducing this function. However, the result is not what I was expecting. I followed the instructions but it didn't work. I noticed that the 'description' does not exactly match the two Excel Tables.
For example:
Description DR CR
PF-1234-H 100
RETURN PF-1234-H 100
Thank you!
If the text you're matching in the two tables is different, it won't match them.
@@MyOnlineTrainingHub actually, that's the biggest difficulty in my job: to get the data unified. One person inputs full number of the contract, another inputs only the last few digits, someone could combine manual adjustment for the couple of the contracts with the same customer in 1 line, etc. You need to go to the details of the invoice, details of the manual entry, ask people... It all takes hours to reconcile when you're dealing with the thousands of lines. That's the challenge and not to sum up perfectly organized data.
Sounds like the processes upstream need improving to solve this problem.
I believe that in the future, AI will conduct reconciliations more accurately by taking into account the line descriptions in bank statements.
Yes, hopefully. The inconsistency in bank transaction data makes automatic allocation to accounts tricky.
Oooh, I’m gonna win some brownie points on Monday. Thanks Mynda! ❤
😁 great to hear!
Thanks, great explanation 👍
Thanks for watching!
Do you have courses totally focused on data analytics?
Thanks for your interest in my courses. Data analytics is such a broad topic that I could say yes, but it really depends. If you reach out via email we can chat about what you have in mind exactly and I can point you in the right direction: website at MyOnlineTrainingHub.com
Power Query is dynamic and gets better results. What is the advantages over PQ?
You don't need to know how to use Power Query 😉
@@MyOnlineTrainingHub Ok😏
But you don't have the 10,000 row and 30 column restrictions with PQ.
Good point. I'm sorry if you're reconciling that much data on a regular basis 😢
I work as an accountant in a large company that records thousands of invoices and related payments, and I have the problem that at the end of each month I have to reconcile the accounting transactions with the bank transactions. These are thousands of rows of transactions, each of which may correspond to one or more accounting entries. I cannot rely on a matching key, but only on amounts and sometimes dates or descriptions. This is where AI would be really helpful but I don't see this tool coming close to those goals. Do we have any idea if something like this will ever be made available?
I don't have any information about the future plans for this tool, however you could try using the amount column as both your mapping key and monetary column.
to be honest, I solved this one with simple SUMIF
You'd need to perform the SUMIF twice, once on each table and then summarise the results. If you had multiple columns to match, you'd need SUMIFS. All doable of course, but some people might prefer to click a button and have the transactions that require attention summarised for them.
I'm sorry but you would be making (a) mistake(s). Multiple payments against one invoice and or multiple invoices against one payment?
Clean your data first with also sumif
Every ID should occure once
Wow, this will save hours upon hours of work! Thanks
Great to hear!
Thank you for amazing information. I would not have known these things if it wasn't for this channel. super helpful.
Happy to hear that! 😊🙏
Very cool Mynda!!
🙏I thought so too 😊
Does the reconciliation copilot address the data formatting differences, eg text versus numbers in invoice id example? Thanks.
No, it won't match different data types. You'd need to fix this first.
love this when our Accounting Apps fails to do the job thank you
Happy to hear that! 🙏
Awesome, simplifies the work
Glad you like it!
Thank you very much for this Mynda
You're most welcome!
Great material👍 I am also creating a Polish channel about data analysis on UA-cam and I found a lot of inspiration from you. I really like your style and message. Good luck!😀
Thanks so much. Good luck with your channel.
But in your case, if there is little difference in amount it will go in unmatched even 0.10 ... Please correct me if I misunderstood....
Yes, of course. If immaterial differences are irrelevant in your reconciliations, then I'd create a second 'amount' column that rounds the values appropriately.
Hi Mynda, this does not work for me. I added the add in, and did a reconciliation on my small 5 item test case. I deliberately made 3 items to match, and left the other 2. Copilot found 1 match. On each of the other items, it saw the same figure in table 2. Meaning copilot assumes the matched figure as the figure for all other items in the 2nd table, though my original table has their own figure. I removed the addin, reinstalled, tried again, but still no joy
❤ good work 💯
Thank you! 😊
Hi I can't find the link for the worksheets.
The link is in the video description. Here it is again for your convenience: bit.ly/copilotf24file
Wonderful & amazing
Thank you so much 😀
I get no matches at all,
I am using your template.?
Does copilot not worrk for excel 2019?
Strange. It says it's available for Excel 2016 onward.
Super interesting.
Glad you think so!
Simply amazing....
Glad you liked it 🙏
Isn't this always done in Xero (or whatever your accounting software is)? - not excel...
You'd be surprised! Also, bank reconciliations are only one type of reconciliation that's commonly performed.
Just ..... WOW!!
Glad you like it 🙏
not worked even after following steps. Not sure why
Hmm, someone else had that yesterday too. I recommend you send a frown via the Help tab > Feedback.
Great!
Cheers, Ray!
💚
🙏
You might be happy until you're not needed for any of this anymore 😂
I have a complex workbook excel of 20 tabs P&L and would like to have your consultation in private - can you contact me ?
I don't do consulting, sorry. However, I can refer you to a consultant if you want to reach out via email and give me a brief outline of your project: website at MyOnlineTrainingHub.com
@@MyOnlineTrainingHub thanks for the quick response - but what is the email Adress I should write to to adress my needs?
I've written it above with spaces. Prefer not to type it in an email address format as I get enough junk male as it is and I'd rather not make it available to bots scraping the web. Here it is again, remove the spaces and replace 'at' with @ : website at MyOnlineTrainingHub.com