Posted: February 21st, 2023

ADVANCED EXCEL

Business analysts will have occasion to download large quantities of data (many
thousands of rows of data for analysis in Excel) and to make a judgement as to the
quality of that data. These data can be sales forecasts from several locations, expense
report numbers, sales numbers, etc. A good way to examine such data is by using
Benford’s Law. Benford’s Law is a statistical technique involving the distribution of
leading digits of numbers. For example, the leading digit of “583.29” is “5.” The
leading digit of “1.99” is “1.” The leading digit of “.083” is “8” (Benford’s Law does
not recognize a 0 as being a leading digit – only the numbers 1 through 9.)
Examine a statistically large data set (about 200 or more items). This data set must
represent a human stimulus and response situation. For example, sales is a human
stimulus and response example: a person wants food (the stimulus) and the person
responds by buying groceries (sales is the response). A country’s GDP is another
stimulus and response situation. Benford’s Law states that in such a data set, the
distribution of leading digits will be as below in Figure 1.
Leading Digit % of Occurrences
1 30.10%
2 17.61%
3 12.50%
4 9.69%
5 7.92%
6 6.69%
7 5.80%
8 5.11%
9 4.58%
Figure 1
Go to the Internet and download a large number of data values regarding a human
stimulus and
response situation (like sales, GDP, etc.) Please follow the example below in Figure
2. Put all the data values that you want to examine using Benford’s Law into a
column (the data may already be in that format). In this example, the data is in
column C. Insert a blank column to the right of that column. Then go into Excel
“Formulas/Text/Left.” This is the “left string” formula. See the “Function
Arguments” dialog box. The text is in cell c2 ($130,475) and the number of
characters from the left to be shown in cell D2 is 1.
Figure 2
Copy and past this formula down the column D. Then use a pivot table to count all
of the 1, 2, 3, etc. values as in Figure 3. Convert these into percentages – for
example, the leading digit of 1 occurred 60 out of 191 times, or 31.41% of the time.
The leading digit of 2 occurred 34 times out of 191 total, of 17.80% of the time.
Figure 3
Benford’s Law states that in these human stimulus and response situations, the
leading digit of “1” should happen about 30.10% of the time, the leading digit of “2”
should happen about 17.61% of the time, etc., as shown in Figure 3’s far right
column.
So, for this case,
1.Download at least about 200 rows of Excel data which reflects human stimulus and
response.
2.Extract the leading digit using the LEFT formula in Excel.
3.Count the number of each leading digit.
4.Compare the percentage of each leading digit with that forecast by Benford’s Law.
5.Write your conclusion of your findings.