Streamline Your Operations with Our Invoice Reconciliation Template Excel for Administration

Invoice reconciliation template excel for Administration

if you work in Finance and Accounting you understand how tedious it is to reconcile Financial records now with cod4 Finance just released by Microsoft reconciliation with Excel just gets so much easier and faster in this video I'm going to demonstrate how to reconcile Financial records in few minutes rather than in hours or days I will also show you how you can cop for finance onto your own Excel application so you can take advantage of that now let's dive into it in this Excel file I have three o sheets one for trip budget with the dates expenditure category and budget amount and I have only 37 records in my second sheet I have the bank statement with bank transactions on the different dates for different amount and I have the transaction category and here I have 43 records given the tool table I want to reconcile those transactions for that I will use a copad for finance I go to the Home tab on the right side on my ex application I see copilot for finance which is now in preview I will click on cop for finance and on this task pen it tell us we can reconcile data to compare the final records cop for finance can do much much more but for now in the review this the only thing we can do before we proceed you have to make sure your data are in a table like this range of data is in a table when click my cursor inside it has table design Tab and this table being named as bank statement when I go to the other sheet this is also a table I go to table design tab this being named as chip budget now if your data is not in your table like in this called range here here those data are not in your table you can leave your cursor in the range go to the insert tab click on table and this range from A1 to f44 that's right range I will accept that also my table does have header in the first row so this been checked that's fine and then click okay now that make this into a table I go back to my Cod P for finance I will reconcile data now we need to select two worksheets and tables to reconcile first worksheet is a true budget so you can choose which worksheet you want to use and within the work sheeet you may have multiple table in this case we have only one which called true budget and second work sheeet we use the bank statement you can choose which worksheet you want to use and within this worksheet we have only one table which called bank statement and then we continue to Next in the Second Step you have to select at least two columns one of each type for each table if I click on ADD column you see we need to se at Le two column one for mapping Keys one for monitor column the mapping key is a column used for finding match records and this may not be only one could be multiple so in our case it won't be one because between the two table there's not any one single field will identify the record in our case in the tri budget we have to use the date and the budget amount those two fields to match the data from the bank statement also using the transaction date and the transaction category so those are two Fields want to use I go back to the add column choose mapping key and from the true budget the column will be date from the bank statement the column will be transaction date now we need to add one more column for matching which will be the category so can can add column matching keys so in the B the column for the true budget will be expenditure category for the bank statement the second column will be transaction category and then I need to add the monetary column so go to add column monetary column from the trip budget that will be the budget amount from the bank statement that will be the amount so now we have defined those column for mapping keys and the column for the amount then I will Con on next and now in less than 3 seconds it finish the reconation give us a report on the left side we have the records from Tri Budget on the right side we have the record from the banks limit and then column I will have the difference between the two tables and we have here three different type of Records which are unmatched transactions and potentially match transactions and match transactions now let me expand to see the detail here you see we have level one and two copy for finance had done the grouping help us to see the higher level or lower level if I click on level one that will give me to the very high level when click on level two that allow me to see the detail so let's start from the match transactions febr 19 for flights from the true budget we have $700 then from the bank statement we also have for February 19 for flights with $700 so they're matching perfectly and the difference is zero so all the record over here that matching the difference will be zero now for the second group are those potentially match trans actions here we have four on February 19 for the food from True budget we have $120 from the bank liement for February 19 food will also have $120 so actually difference Z so that actually the matching perfectly fine if you look at second record February 19 Transportation $150 now found the bank liement February 19 Transportation also $150 the difference is zero so looks like those are actually matching perfectly fine why this being classified as potentially match transaction rather than being match transaction so this is the difference for those are considered as match transactions those are one to one matching meaning this is a one single record from the true budget it match with all single record in the bank liement which is record over here it's one to one matching will be consider as matched now for those under the potentially match transactions those are not one to one matching those could be one to many matching or many to one matching or many to many matching now let me show you what I mean by this if you look at the first record over here from the true Budget on February 19 food is $120 now if I go to Tri budget to look at February 19 for for food which is record over here which is $120 and this is one single record over here now if you go to the bank statement look at February 19 for food which are those two records you see here will have two records for febr 19 for food the total amount is $120 but here we have two records versus in the trip budget it's one single record so this is one to many matching that's why this is being under potentially match transactions rather than being match transaction because for match transaction those are one to one for anything that's under potentially match transaction those are one to many or many to one or could be many to many so it could be here we have two records here we have three records but the total amount are matching the still for 7 date for the same category and then we have above over here are unmatch transactions like for February 19 for entertainment we have $160 but for the bank statement on February 19 for entertainment we have only $30 so we have difference of 130 now if you look at the second one for February 19 for Miss n in the true budget we have only $10 but for the bank statement on February 19 for Miss nus we have 140 you see here we have difference of negative 130 this miscellanous in the bank statement this $140 could be actually for entertainment it's just somehow it's not being classified as inentertainment because the two together is $170 and over here the two together is also $170 this is something for us to review and decide if they are matching or not this is how the C- for finance produces reconciliation within Excel but it can also produce a report for us here being in the preview it couldn't produce that report for us but now I could copy this reconation report with Excel and use chat GPT data an to produce a world document now let me copy this entire reconation report can you see to copy and let me go to the chat GPT and I'm using the data anst because data an is able to use a python code to process data and generate report in World document so I will go to this prompt bar contr V to paste one paste over here actually will paste two things one is a picture of that range I copied or other will be actually those number and the text I don't want to have the text so I can remove all of those but only keep the picture over here now my promt will be please create a detail summary in Word document based on the attached reconation report thanks so this the picture I have ping over here now data is performing the analysis and producing report for me so chat GPT data anst took a little bit time to use the picture I have copy paste over here it has done the analysis it create detail summary and generate a world document for me to download and now I can open this document so it provides this reconation report summary to tell us about the 20 un match transactions and the four potential match transactions and the 13 match transactions and what are the difference and also from the grand total perspective what's the difference if you would like to download cop4 Finance onto your own ex application you need to come to this particular Microsoft web page and I have provided this link in the description below this is free you click on get it now then it's going to prompt you to sign in to Microsoft app Source it's suggesting to use your work or school account but if you do use your Microsoft account that's still fine that's what I did and you sign in and then you need to provide your name and Country and then click on get it now then you'll become to this web page you just click on open in Excel then you'll have the copy for finance op in your ex application in the Home tab on the right side now one thing you need to know when you perform reconation with copy for finance with Excel your data cannot have more than 10,000 rows and 30 columns okay that's Code Pilot for finance with Excel I hope you have learned something new if you like this video please give a thumb up and subscribe to my channel I will keep updating you about the new capability with Excel and how co-pilot will help you to become more productive within Excel thank you