The ﬁrst analysis of any data set need not use sophisticated mathematics or statistics. The goal of these test is to ﬁnd subsets that are highly inflated due to the error or fraud.
Largest Subset Test
The largest subsets test uses two ﬁelds, one with transaction or balance numbers (such as amount, inventory counts, vote counts, population counts) and another ﬁeld to indicate the subset (e.g., vendor number, credit card number, or branch number). Subset is a group of records that have something in common.
The data can often be divided into several subset groupings. For example, accounts payable data could be grouped by vendor or by the type of purchase (purchase order, no. purchase order) or by time. There are often a few different ways that data can be divided into subsets. For inventory data the grouping could be by location. For airline ticket refunds or retail customer refunds the groupings could be the credit card that received the refund.
In my previous article Auditing: Accounts Payable / Vendor Payments I spoke about Relative Size Factor (RSF) and how it can used to identify isolated outliers in vendor invoices. In this article I’ll try to show how RSF can be calculated in Excel.
The RSF test is an important tool for detecting errors. RSF test compares the top two amounts for each subset and calculates the RSF for each. The test identifies subsets where the largest amount is out of line with other amounts for that subset.
Spreadsheets are known to be error prone. As per one study by Raymond Panko, 86% of spreadsheets contains errors. Errors in spreadsheets can’t be eliminated completely, but steps can be taken to reduce them.
In 2003, “a cut-and-paste error in a spreadsheet cost TransAlta, a Canadian power generation company, $24 million in overpayments for hedging contracts.”
In the previous post Spreadsheets: Risk, I mentioned few of the risks associated with spreadsheets. In this post I’ll try to show some excel tools which can help in detecting errors and frauds in Excel spreadsheets.
In the late 1990’s “Poor control over spreadsheets at Jamaican indigenous banks contributed to management information and external reporting problems (i.e., P&L distortions) that contributed to the banks’ management and external regulators losing sight of the banks’ true positions and exposures. Which led to collapse of entire Jamaican Banking System.
Spreadsheets have stood the test of time because they continue to meet the analytical needs of organizations, especially for analyzing and reporting financial results and providing support for decision-making.
“…spreadsheets will always fill the void between what a business needs today and the formal installed systems…” Mel Glass et al
Benford law named after physicist Frank Benford, also called first-digit law is an observation about the frequency distribution of leading digits in natural sets of numerical data.
Benford‘s Law states that natural sets of numbers follow a predictable pattern, no matter what their origin or subject. The digit 1 will be the leading digit approximately 30% of the time. A leading digit is simply the left-most digit in a number (0 cannot be a leading digit). In fact, digits 1, 2, or 3 lead approximately 60% of the time. 9 as a leading digit appears only 4.5% of the time.