Collaborate on Excel Invoice for Legal with Ease Using airSlate SignNow

Excel invoice for Legal

howdy everybody welcome back to auditing in our video today we're going to be having a little fun with data analytics and I'm going to take a moment to show you how to apply binford's law to a data set in Excel now you may not be familiar with benford's law and our book only mentions it in passing in a paragraph or two and I've tried to give you some background information in the assignment link and I'd like you to make sure you read through the background on benford's law here the basic idea behind benford's law if we look at a data set of naturally occurring data like payment data and you see I've got Clinton County Ohio payment date up here fourth fiscal year 2022 in the spreadsheet are all the payments that they made in Clinton County to different vendors and different people and you can see we've got the check amounts right here check number right there but I'm looking at the amounts and if we think about the first digit in these amounts you can see 37 the first digit will be three 116 48 the first digit would be one if we just kind of think about that first digit you might think okay well the odds of that first digit being one would be one and nine and obviously the first digit can only be number one through number nine if you look at the number three you might think okay well the odds of the first digit being through be one and nine and now the odds would be the same for every digit one through nine as far as being the first digit of a number what Benford found out though the mathematician he figured out that that's not the case in a randomly occurring set of natural numbers he found that the first digit would be one about 30.1 percent of the time the first digit would be a 2 17.6 percent of time all the way down to the number nine only showing up as the first digit 4.6 percent of the time so the distribution isn't even in that first digit in a naturally occurring set of numbers it's going to follow that distribution that Benford came up with that may not be exact but it's going to be in that ballpark now that is for a naturally occurring set of numbers non-natural set of numbers would be things that men kind of just made up to organize information like zip codes all right uh maybe area codes and those are limited data sets another example might be if we took everybody in Ohio and uh we put their height in a spreadsheet okay well obviously nobody is going to be nine foot tall and probably not many people gonna be one foot tall so that's not a naturally occurring set of data same with weight if we put everybody's weight in the state of Ohio in a spreadsheet and try to do benford's law it wouldn't work because nobody's going to be 900 pounds right most of your digits you want to start with a one or a two okay so it works great on payment data because it is a naturally occurring set of numbers and that doesn't follow a uh kind of predefined numbering scheme like a zip code where there's only five numbers in it also isn't kind of made up by humans payments are what they are so the idea here from a fraud uh examination perspective is we can take a payment data set do a Benford analysis on it and it should follow this pattern right here if we have someone maybe in the financial office who's embezzling money or making up payments to vendors chances are they won't follow this distribution they're not going to write a bunch of 100 checks they're going to write eight nine hundred dollar checks right so we could take a month we can take a year run a Benford analysis on it and hopefully maybe see if there's anything funny going on now it's not foolproof it's just another tool in our Arsenal here but with that being it's got our goal for today is to show you all how to conduct a Benford analysis because that's going to be your assignment I'm going to give you all uh some data sets here and you're going to pick one and you're going to run a Benford on it and see if the data matches up with what Benford said it would alrighty so here we have our data from Clinton County uh fiscal year 2022 payment data I did not break into the Clinton County Courthouse in Wilmington under the darkness of night with a black ski mask at two in the morning and steal the data and some kind of a daring coup no I just downloaded it off the internet a lot of municipalities and governmental entities will have basically what they call checkbooks online where you can see everything that they've spent you can download the data you can play around with it and that's where I got the Clinton County information right off their website from their kind of online checkbook government transparency initiative thing and this is every transaction every payment that was made by Clinton County and fiscal year 2022. now you can see I've got the payment amounts right there and you can see in the beginning with 37 that was 37 bucks 25 bucks 116. the first digits you know they're different as we go down through here and our goal step number one is to to try to isolate that first digit all right so I want to basically create a formula that pulls that three out that pulls the one that pulls the two that pulls the seven that pulls the eight you get the idea I want that first digit it's not a very difficult formula in fact it's pretty simple to make that happen what I'm going to do is zoom in here a little bit so we can see what we're doing and I'm going to start right here and I give a little uh buffer there before column I and I'm going to make my formula my form is going to be equal left literally lift I want it to return the left most number all right and I only want one number I just want the first number so my formula is going to be equal left put up my bracket and I'm going to be using that column column G and I'm going to do a little trick here because when you do your Benford analysis in Excel cases are you going to have thousands maybe even hundreds of thousands of lines of data and if you use your mouse and scroll down that way it's going to take forever so I've clicked on it now I'm going to hold two keys I'm going to hold control and shift on my keyboard control and shift and I'm gonna hit the down arrow and what that's going to do is take me to the bottom of my data now if I had a gap in there like I had a blank row it would stop at that and you would have to kind of Hit the down arrow twice but all my data's together so I can hold Ctrl and shift and hit my down arrow and it will highlight that entire column now I can just go back up to the top and complete my formula left and there's my reference right there for where I'm looking and I want it to return one digit if I did two it would return the three and this done where would you return the one and the two I just want one digit I want the very first digit on the far left there we go Isn't that cool get that so here we go I've isolated the first digit in every single payment now we can run Benford all we have to do is kind of have Excel count up how many have a digit of one how many have a digit or two and so on so I'm just going to go ahead and put that in one two three four five six seven eight nine right on so now we need a little formula here to count I need to count all the ones that have a one I need to count all the ones that have a two so our formula is going to be equal count if count it if it's a one count it if it's a two so here we go again I'm going to go back to my first digit here use control and shift and go all the way down to the bottom now I'm going to copy the formula all right so I need that to be relative I'm sorry I need that to be absolute I should say make it absolute I'm going to hit F4 my keyboard shortcut you'll notice it'll put a dollar sign in front of the two uh parameters there and then I'm going to hit comma oh I messed it up didn't it did it erase my reference that it did didn't it oh dang you Excel we got to do that again so let's try it one more time here a highlight come back up and then we'll do these are absolute Reference last I'm going to click on I2 there we go now we're looking good I'm not going to make L2 absolute because I want it to be relative I'm going to copy the formula down through number nine then so I'm going to go ahead and hit enter lock that in Excel found 5230 instances of number one being the first digit I'm going to go ahead and copy my formula as I'm into it there we go we can dress it up a little bit if we want to perfect so in Benford Benford says that the number one digit being the leading digit would occur about 30 percent of the time right as we can see the times that number one occurs is digit number one number two occurs is digit number one and so forth so now we need to figure out some percentages here I'm going to go ahead and do a little math and add up my totals seventeen thousand one hundred and forty one so now the final step of our Benford is going to be to take each one of these and divide it by the total number of payments here we'll say equal divided by and make that an absolute reference so I can copy my formula down and there we go and if you thought Benford was a an idiot well check this out oh I don't need all that math let's not do that let's do that there we go perfect so there it is if I did a Benford here I've found that the digit one the number one occurs is the first digit 30.5 percent of the time in these 17 000 payments the digit number two occurs eighteen point five percent of the time let's compare what we ran with the Benford distribution go back to our assignment instructions here now I've got the breakdown in Benford look at that 30.5 compared to 30.1 18.5 compared to 17.6 11.9 very very close it's wild look at the number nine 4.5 compared to 4.6 almost exactly fit benford's law that data set completely random natural occurring numbers 17 000 of them and it fits the pattern that Benford said it would so what we're looking for in Benford analysis is we're looking for when we do the math and we do the work that it pretty much approximates the breakdown that Benford predicts if we had somebody in the Clinton County Fiscal offices there that maybe had vendors set up when was cutting checks and then embezzling that money chances are the pattern wouldn't fall that way because they're making up uh non-random numbers right chances are maybe the eight would take a lot bigger percentage of the data okay so that's basically the idea behind benford's law what we're doing for the assignment is I want you to download a data set I've provided a few for you from Cincinnati from Clinton County don't use the data set that I used even though I provided it you can play with it uh to kind of practice but these are the data sets I'd like you to use run up benford's analysis and see does the payday get paid it does the payment data I tried to combine payment and data into one word PETA it's so efficient but does the payment data match up with benford's law and you know maybe you have a data set at work you could download it and try to run it on a data set at work if you want to I'm just giving you a few examples here so you didn't have to dig around you're more than welcome if you can find payment data that has thousands of lines in it to go use that data do a benford's law kind of interesting like I said just another tool in the auditor or fraud examiner's tool belt to see if there is potential embezzlement going on it's not foolproof but it's a great way to quickly get some information on the potential for malfeasance to be a current all right that's it gang benford's analysis I enjoy uh showing you that and kind of walking you through benford's analysis hope you find that tool interesting and can learn how to use it and maybe one day you'll be able to bust a fraudster with it who knows that's it for our video now and uh we'll come back next time talk more about sampling and audit sampling and how that works in the field I hope to see you back here for those videos until next time take care everybody