Required: Download the spreadsheet for this problem from the course website and perform the following tasks:

1. Explore Excel's AutoFilter function.

a. At the bottom of the table, in cell C79 enter the words "Total (sum)" and in cell D79 enter the SUM function to total all amounts in column D.
b. At the bottom of the table, in cell C80 enter the words "Total (subtotal)"and in cell D80 enter "SUBTOTAL" function to sum all the amounts in column D.
c. Use AutoFilter to display all purchases from suppliers in Zipcode 85110. What is displayed in cells D79 and D80? Why?
d. Repeat step 3 for Zipcodes 85111 and 85116. What is the value of using the AutoFilter function?
2. Use the AutoFilter and Sort functions to identify the following warning signs of potentially fraudulent supplier invoices:
a. Suppliers that have only initials for the name
b. Different suppliers with the same PO Box for an address
c. Suppliers who submit sequentially numbered invoices
d. Invoices just below a threshold requiring approval; for example, invoices below $1,000
e. Duplicate invoice numbers from the same supplier

1. Requirement 1 – autofilter and subtotal versus sum.
a. Sum totals all rows, whether or not visible. Subtotal only sums visible rows, so when use it with autofilter, get totals for just those rows.
b. Click on Data menu and select filter to get the little arrow boxes
c. Value = can customize groupings quickly, without changing the raw data
2. Requirement 2 – fraud investigation
a. Sort by supplier names in alpha order will show many variants on DFR
b. Click "begins with" P* to find PO Boxes, then sort by PO Box number – will see several names with same PO box
c. Sort by invoice number
d. Can sort by amount in ascending order, or autofilter with that command to show just the subset.
e. Sort by invoice number