Generating a "normally distributed" list of invoice values..

[essjays]

Hi All
I need to build a huge amount of test data that does a reasonable job of emulating a fortune 500 company's accounts payable.
I just cant seem to crack it, but I admit I'm not incredibly knowledgeable of statistical methods.

Consider this..
- The data should be 200,000 lines long.
- The largest value should be say... $20,000,000
- The lowest value should be $0.75.
- 90% of the "total value" should be attributed to values greater than $50,000
- The total of all values should be no more than $5,000,000,000

Apologies for the roundabout way of explaining. Would appreciate any pointers.

cheers

[MrShorty]

Standard approach that I see to generating random numbers according to a distribution:

1) Generate a list of uniform random numbers (in Excel, the RAND() function)
2) Use the inverse function for the desired distribution to generate the random numbers along the desired distribution. (eg =NORMINV())

So, to generate a series of random numbers on the standard normal distribution (mean=0 stdev=1) =NORMINV(RAND(),0,1)

This help file lists the available functions by category. Pre-programmed distribution functions are under the statistical category: https://support.office.com/en-us/art...rs=en-US&ad=US

The difficult part of the way you have defined the problem is that you have not given the mean and standard deviation for the desired normal distribution. Using the example of a simple, symmetric normal distribution, you would need to use the "considerations" you have listed to identify the mean and standard deviation that fit those conditions. One might start with 5E9 total/2e5 invoices=$25000/invoice to get the average invoice, then work out a suitable standard deviation. Something like =NORMINV(RAND(),25000,1000) where I just pulled sd=1000 out of a hat.

The 4th condition (90% of total from invoices over 50000), however, suggests that your distribution might be skewed, so you may also need to include factors for skewness.

That's a basic overview of the problem. Generating the random numbers is relatively easy. I am assuming at this point that you know the distribution you want to use and can generate the parameters for that distribution.

This might be an interesting introduction to this sort of thing in Excel: http://www.real-statistics.com/sampl...ns/simulation/

[essjays]

Thanks for the advice.
The problem I am running into is the required spread. SD should always be larger than the mean in my scenario.... Due to the very wide range in potential values (e.g. may be 2,000 values between $1 and 500... but then a 100 values over $1,000,0000..).

So when using the function to RNG based on realistic Mean and SD, i will get a bunch of negative numbers. The calc is doing its job.. but you see my dilemma. If i crop out the negatives and 0.00 values, then its no longer really a normal distribution.

Thanks again for your insight and links