EZ-R Stats, LLC

The "dirty dozen" tests which can be performed on excel worksheets are summarized below. More information on each of the tests is available as shown in the sidebar links. (It started as a dozen, then grew...)

1. Statistics - obtain basic statistics for the numeric values selected. Uses include sample planning, analytic review, reasonableness testing, etc.
2. Round Numbers - summarize the number and type of round numbers - generally used to to check for estimates or unusual amounts among purchases orders, invoices, etc.
3. Trend Line - determine a trend line using "best fit" for a series of data points. Used for checking trends over time, unusual data points, etc.
4. Stratification - classify numeric amounts into bucket ranges. Generally used for reasonableness testing, sample planning, etc.
5. Linear regression - test the relationship between to variables. Used to identify unusual amounts in situations where there should be a relationship between two amounts, e.g. sales and cost of goods sold.
6. Weekday classification - tallies the counts of each date in the range selected by day of the week. used for checking for unusual situations or simply to see where transactions fall.
7. Holiday checking - checks for dates in the transaction range falling on federal holidays. used to spot potentially unusual transactions.
8. Benford's Law - used to test amounts using Benford's law. Generally associated with testing for "made up" numbers.
9. Duplicates - a test for any duplicate values can be performed. If any are found, a report shows the values and counts for all such items identified.
10. Summary - numeric values can be summarized by a specified value. It is not necessary to "pre-sort" the data. The summary report includes not only sub-totals, but counts, minimum and maximum values, average, etc.
11. TopN - for numeric values, a report can be prepared of the largest 5, 10, 20 etc. items. The auditor specifies the number of items to be obtained. It is not necessary to "pre-sort" the data.
12. BottomN - for numeric values, a report can be prepared of the smallest 5, 10, 20 etc. items. The auditor specifies the number of items to be obtained. It is not necessary to "pre-sort" the data.

13. Ageing - Classify amounts based upon a date column. Amounts are placed into "buckets" of 30 days.

14. Tiny Difference - identify values whose difference from that of a specified value is "tiny". Used in searches for anomalies..

15. Fuzzy Matching - sometimes you're trying to match a value using "wild cards". This technique uses the "regular expression" function built into Excel.

16. Day of Week - allows extraction of data based upon a date column falling on one or more of the specified days of the week.

17. Time of Day - allows for data extraction of transactions falling within specified hours of the day. Useful in looking for anomalies..

18. Pareto Analysis - Uses the classic "80/20" rule to classify and check data. Useful in isolating the data where the focus should be...

19. Gaps - Checking for missing document (or other) numbers which should be complete and in numercal order. Useful for fraud investigations.