ISU would like you to examine the sales receipts from the Student Union to see if there is the potential of fraud related to the receipts.

1) Open the Homework 5 Excel File to begin. There you will see a list of sales transactions from the ISU Student Union.

2) Using the LEFT Excel function in Column D to retrieve the first digit of each sales transaction in column B.

3) Using the COUNTIF Excel function in Column H, count the number of occurrences of each digit in Column D using the numbers 1-9 provided in Column F. You need to be able to autofill H7 through H14.

4) Use the SUM Excel function in H15 to count the numbers in H6 through H14.

5) In Cells J6 through J14, calculate the percentage of each digit in Column H as it relates to the total number of transactions. Please divide the count of first-digit numbers by the total number of transactions (H15).

Ex. =H6/$H$15 for cell J6

6) In cells K6 through K14, calculate the expected frequency based on Benford's law. An example of how to do this for cell K6 is below.

=LOG10(1/F6+1)

7) Format the appropriate cells in Columns J and K as a percentage with zero decimal places.

8) Calculate the difference in Column L for cells L6 through L14 between the percentage of first digits found in column J concerning the sales receipts and the expected percentage based on Benford's law in column K. An example of how to do this for cell L6 is below.

=J6-K6

Format column L as a percentage with zero decimal places.

9) Compare the distribution of each first-digit of the sales receipts with the expected values based on Benford's law in Column M for cells M6 through M14. An example of how to do this for cell M6 is below.

=K6*$H$15

Please format the results to show only whole numbers.

10) Insert a Combo Chart by using the Percentage and Benford's Law columns (Columns J and K). Make sure to include the Headers. Change the name of the Chart to Benford's Law.

11) We can use a statistical test called the chi-square test to test if the distribution of first digits in our sales receipts is significantly different that that expected by Benford's

law. If the p-value of the chi-square is less than or equal to .05, we reject the null hypothesis (H0) and then may require further investigation.

With that in mind, we have two opposing hypotheses:

H0: The first digit of the ISU sales receipt data conforms to Benford's Law

H1: The first digit of the ISU sales receipt data does not conform to Benford's Law

Use the CHISQ.TEST Excel function in Cell G18 to do this. You can find this formula under Formulas/More Functions/Statistical. Please use the values in Cells H6:H14 (for Actual Range) and M6:M14 (for Expected Range). Format the result to be a number with two decimals.

12) Please use the IF Excel function in cell H20 to determine whether we should investigate the sales receipts further. The options will be "Yes" to investigate further or "No" for no further investigation needed.

Hint: Use the results in cell G18 to compare to the p-value of .05 in your function. If the value in cell G18 is < , we would investigate further.