Simplify Your Processes with Our Invoice Reconciliation Template Excel for Insurance Industry

Invoice reconciliation template excel for Insurance Industry

hello i'm chris menard thank you for joining me in today's excel video also part of my watch chris work i'm going to take an accounts payable or an accounts receivable so we have an accounting exercise to do an mba actually asked me this question one time so i'm going to run through here i'm going to do a lot of excel functions including the today function average if i'm going to use the text function i'm also doing conditional formatting and i'm even going to write an if statement so there's a lot going on in this video there will be chapters down below so let's go ahead and dive in also feel free to subscribe ring the bell if you like these type of videos [Music] so i'm going to zoom in just a tad before we start probably should have done it before i started but it's all right okay i'm in cell b4 and i want to know what is the current date the keyboard shortcut to put in the current date is control semicolon today is saturday may 7th 2022 the problem with control semicolon or just typing that day is it's static it doesn't change until i change it well this is an accounting exercise so i'm going to use the today function equals today open parenth there is no arguments i'm going to do control enter there is the today function if you look above column b that is the formula bar tomorrow if i open this it'll say may 8th if i open it on monday it'll say may 9th so there you go so there's the today function so my current dates always updated by the way accounts payable or caps receivable accounts receivable for your company in case you're wondering that is money that is owed to your company from your clients or customers accounts payable that is money that your company owes to vendors so this could be used for either one not a big deal over here what could be the invoice date in column a if you notice i highlighted them all again this is just a cool tip and trick notice it says count 7 and i've got even numerical count 7 average sum that's telling me that these invoice numbers are actually numbers they need to be text so let's say that i pulled these out of some system and they came in as numbers so i'm over in h7 to do this and i want to say i want to make a7 text the text function has two arguments what cell you trying to format and how do you want to format it i'm putting the word general in double quotes control enter crosshairs pull down to row 13. i think that works perfect look down below in the bottom right on the status bar because this is text and not numbers it just says the word count i'm going to copy and this is going to be a paste values perfect the green triangle marks basically says hey this looks like numbers but you have it in here as text and again i'm still at 7. i no longer need what's in h but i do want to show you this cool trick i'm going to undo control z and i'm back here so i have numbers back in a i am making this up uh if you look at that first number it's got six numbers in it in case you're wondering how i know that the l l-e-n function will tell you how many characters are in a text string and there's seven or so i'm sure there's six i'm making this up act like all our numbers are supposed to be nine digits and the leading zeros got left out well i can still use the text function it's the same thing i just did quotes one two three four five six seven eight nine is already in there there you go crosshairs double click copy paste values i'm just showing you a couple tricks in here i have to i've had to use this before especially in the counting field a lot of gl account numbers will be x number of characters and the leading zeros get dropped some reason one more quick trick just one more quick test for me i want to make sure i got it and they are they should all be nine that's what's cool about the nine zeroes that i put in on that text function they're all nine because they had different links perfect here we go the invoice date nothing fancy here the invoice date is just the invoice date they're typed in here another tip here this drives me crazy when i'm looking at this dates and numbers will always be aligned to the right so if you look in column b those dates are to the right text is always by default a line to the left i could since they're all three letters in here but in real life your customers aren't all three letters i could put them in the center in this example but let's be realistic they're not all going to be three letters so select them in the alignment group right up here is increase indent it is totally up to you how many times you're gonna do this i'm gonna click it once i'm gonna click it a second time a third time i'm going to go back to decrease indent that looks good now if you notice i do have a little bit of space between the dates and the numbers but the text for customer name is still aligned to the left keep on going this is really simple highlight these numbers and there is the comma style if you're a keyboard person though ctrl shift 1 and ctrl shift in the number four the number four if you notice has a dollar sign on it and that made it currency style so comma accounting accounting the dollar symbols to the far left currency style from this drop down currency is right next to the number up to you what you want to see i'm just going to leave it just the way it is right here here we go with some formulas i want to know what's the invoice due date i am making this example up you take the invoice date and our terms are net 30 for our customers or vendors some will do equals b7 plus 30 control enter leaves you here crosshairs and double click so there is the invoice due date 30 days from the invoice date what is the age of the invoice here we go here comes an absolute reference i'm in cell f7 it's going to be equals the current date whatever that date is but that current date i need to use in every one of my calculations f7 f8 f9 as i'm working down i want to free cell b4 so equals b4 the f4 function key on your keyboard will put in the dollar sign that means freeze b4 minus b7 perfect control enter 36 days crosshairs double click i'm going to click on the 66 look up in my formula bar right above column b and it still equals b4 just to point this out if you just arrow down you're good here we go with some cool stuff i want to know the number of days overdue we just decided that our terms are net 30 so that first invoice is six days overdue equals f7 minus 30 control enter crosshairs and double click and there you go why do i have negative days because they're not over 30 days so 3 minus 30 is negative 27. cool feature here i want to know what is the average number of days overdue in cell g15 equals average open parenthesis highlight this range don't have to close it just press enter and i tell someone it's 12. that is incorrect well it's correct that it's the average of those numbers but i only want to know what is the average number of days overdue if they're over 30 days so i'm going to leave the 12 there here is your average if function i'm not covering it today but in excel average if sum if is available count if max if men if so average if i use this a lot open parenth three arguments two are required the first two notice the third one has got the brackets is optional i only need two for this one i want to know what range if this range right here comma i'm on the criteria argument i want to average the numbers if they're greater than zero so there is equals average if your range comma in double quotes is the number zero third argument is not required press enter 35 days cool tip in excel i'm going to highlight these you know the status bar is in the bottom right and there's the word average showing the number 12. i'm holding down the ctrl key on my keyboard and i am selecting every number that's a negative let go of the control key look down at the bottom and notice the average is 35. so you can use the control key to select you can also use the control key to unselect cells that i just did so that is the correct number right there by the way since i'm showing you how i work if someone asked me what is the average number of days overdue i'm picking this company up abc because i didn't use the third argument you know i love formula text i never used that third argument when i got the number 35. so what is the average number of days overdue for abc company equals average if open parenth what range you're looking for i'm looking for the company abc comma i am going to use the third argument here what does you want to average so for abc 24 again let's just do a quick test there is six i know that i know i did pick up the negatives this time so there was 24. all right um the age of the invoice so remember our terms are that 30. so i'm going to highlight these i'm going to do quickly three conditional formattings so if an invoice so remember let's say that um this is accounts receivable and we're wanting money from our clients in 30 days so the minute it hits 31 it's overdue so between 31 and 40 i want to see in one color between 41 and 50 i want to see in another color and over 51 in a third color so i got them highlighted i'll do it again highlighted conditional formatting on the home tab this is really simple highlight cell rules between when you put in between it wants that box i do 31 and 40. what color well 31 days i don't get too worried about and 35 maybe i'm gonna put them in green hit okay so i got one in green leave them highlighted come right back here and do another between we did 31 and 40. so 41 and 50. i'm gonna put those in yellow leave them highlighted come right back in here do the last one 31 to 40 41 to 50 over 50 so greater than 50 i'm out it be this red color right here there you go real quick test change this one here the age of the invoice to 3 18 22 there you go it's right at 50 let's say it's 3 19 22 it's 49 and it's 321 and sorry went the wrong way it's working i promise you there you go one more cool tip and then we are done if you want to see your conditional formatting rules highlight what you just did conditional formatting go to manage rules this box appears notice it says current selection you could look at all the conditional formatting for the entire worksheet also a new feature you should not be to do this you can resize this box if you have a lot of rules you should not better do that that is all i have for you today i will put this file up on my website chrismonardtraining.com you can go i'll do a blog post about it and you can go find this file and download it i'll have this worksheet the way i had it when i started i hope you enjoyed this i appreciate your time have a great day thank you [Music] you