Benford’s Law … Gotcha Microsoft!
If you don’t know Benford’s Law and you like quirky little mathematical things, you need to know what it is. It’s the law of the first digit which means that the probability of number 1 being the first digit in any numerical value is 30.1%, the probability of number 2 being the second digit in that number is 17.6% or thereabouts. It’s all very predictable and that makes it interesting.
Auditors and forensic accountants use Benford’s Law these days as do scientists and engineers. I use it from time to time too.
The purpose of this page is not to teach you Benford’s law but to show you how I used it this week to demonstrate the law to my delegates and then to determine whether the dataset I had been using was suspicious! For my demonstration I presented this table:
|Net Assets||Net Income|
There were 49,999 net asset values and 49,999 net income values: perfect for what I wanted!
The first digits from the table above are, reading down then across and down:
In cell C5 in the spreadsheet you can download from here, we extract digit one from the net assets by using this formula:
And in cell D5 we use this formula to extract the first digit =IF(B5<0,MID(B5,2,1),LEFT(B5,1)) … why not just LEFT(…)? Because some of the net income values are negative and this was the most direct way of overcoming that obstacle!
That’s it, digit one done: now we create our frequency distribution and compare it to the values that Benford is expecting for net assets:
|Digit 1||Actual||Actual %||Benford|
We created a similar table for Net Income.
If the actual percentage is significantly greater or less than the Benford percentage, as is the case here for digits 1 – 6, we conclude that something funny is going on and that this data set is not behaving normally.
Well, in keeping with the purpose of this page, all we say is: someone must check the source of data now as they are either unreliable for our purpose or someone has to explain what they have done to fudge the data!
In truth, what had happened is that I had downloaded a dataset from a Microsoft site and I am sure they created that dataset using random numbers or some other creative process and never dreamed that someone like me would come along and test the veracity of their numbers.
No harm done, nothing serious happened: in fact, the lesson worked perfectly and Benford was vindicated.
Download the file here: benford_data_delegates