[General Geekiness] Ask the Mathletes (Pt2)

Today's Specific Advice Column comes from Mathlete Mike (seen here dressed as Fidel Castro).

Right, you're about to commit fraud. To be specific, you are about to embezzle. No buts, you are. The price of petrol has finally thrown you off the edge. To keep the hounds at bay, you need to cook the books. You need to put a mound of false data into the accounting system to make it look as though nothing is wrong and you are able to buy that Ferrari 575 with money from an old, previously unknown, aunt's inheritance. And you'll be able to afford the petrol to keep it going from the interest you've accumulating from the change left over.

Step 1 to Cook the Books: Generate some false data

Let's say that the numbers you have to make up are stock prices. You
look at the paper. Hmmmm, they appear pretty random. You can't be
bothered doing a complete statistical analysis, but they look all over
the place, so you may as well make yours pretty random. I mean it's
pretty obvious that a stock price could be anything and therefore any
number is as likely as the next. You certainly can't see any pattern in
these numbers. I mean, if you could, you wouldn't be embezzling, right?

OK, how do you do this? Do you just pick numbers out of your head?
Possibly, but, well, you want to make this random, right, so that no one
looking at the data thinks, "Ah, they just made that data up from out of
their heads"? So how do you make random data? How about Excel? That does
random numbers. Technically, it's only a pseduo-random
number generator, but, ah, they will not know the difference! Cool,
exactly what you need. You need to come up with 10,000 separate numbers,
each somewhere between $0.01 and $10.00, to hide what you've been doing.

Easy. You just use the RANDBETWEEN() function to produce 10,000 amounts
between $0.01 and $10.00. You have to look it up in the Help section,
but for once Microsoft's help is helpful and you work out the exact
function to be "=RANDBETWEEN(1,1000)/100". This will give you a dollars
and cents amount. Excellent, copy that cell into another 9,999 cells and
there you have it! Couldn't be easier (assuming you know how to quickly
copy one cell into 9,999 others). Spreadsheets are a forger's best friend.

Of course, now you'd better check and make sure it is random. There
might be some pattern in Excel's random number generator that will give
it away. What's a quick check? How you use the COUNTIF() function to
count the number of times each number appears?

Thinking back to high school statistics, each dollar amount from 0 to 9
(ignoring the cents) should appear about 1000 times each in a set of
10,000 numbers (e.g. 0 should appear about 1000 times, 1 should appear
about 1000 times, etc). I mean, they're all equally likely, are they
not? Now, you should probably give or take a few here and there - I
mean, having each number appear exactly 100 times would be a bit of a
giveaway, as nature isn't always so precise.

[Note that the dollar amounts 0 and 10 are a bit of a special case, as
10 should statistically only appear ten times ($10.00 - there can't be a
$10.01, etc, as we're only capable of generating up to $10.00), and 0
would be expected to appear 990 times ($0.01 to $0.99, no $0.00) in our
sample size of 10,000. However, we can ignore these foibles in our
little scheme.]

OK, so you use the COUNTIF() function to count the appearance of each
dollar amount between 0 and 9. And to your delight, you find that every
number does indeed seem to appear about 1000 times each. Even better,
you note, some numbers are a little bit down (you note that $2 only
appears 964 times), while some are a little bit up ($4 appears 1071
times). That'll fool them, you think.

Step 2 to Cook the Books: Enter the false data

Copy and paste into your accouting system, if you're lucky. Enter by
hand if you're not.

Step 3 to Cook the Books: Kick back and enjoy your ill gotten gains

That is, until the police knock on your door. How did they know? You
data was beautifully random. It was perfect. There was no way it
could've given you away. Or was there...

As you rot in jail, your guards laugh at you and say you weren't the
first and you won't be the last, and here, read these things before
doing it next time:
- Benford's
Law (at Intuitor.com)
- Benford's Law
(at Wikipedia)