Using Excel to test controls in PO Data Obtain data file "AnyCO-C13 Excel Problem" from "name of site". Do any of the following activities to test the controls you have learned in this chapter 1. Test the PO number sequence for out of sequence PO numbers (use sheet 1: "AnyCO- POs"). Hint: First make sure data is sorted in PO number order, add a column and use the Excel IF function (e.g., If b2-b1=1, Ok, GAP). If there are any gaps, how many? Use Excel CountIF function. Is it inherently a control failure to have a GAP in sequence number? What could reasonably account for a gap? Why might a GAP indicate a control problem? Test whether all of the vendors that were used on the POs were from the authorized vendor list (the authorized vendor list is on spreadsheet 2 titled "Any CO-Vendors". Hint: use VLOOKUP on vendor number to the vendor table and return the vendor name: example formula: =VLOOKUP(F2, AnyCO-Vendors'IS1:$1048576,2,FALSE), search for N/A. Were any POs issued to vendors not on the vendor master list? If so how many and what are the potential problems? 3. Are there any open POs (e.g., those not received against)? If yes, how many? Hint: use VLOOKUP on PO number to the Receipt table and return the Quantity received. How would you determine if the open PO is a problem? Assume we are at calendar year end, which, if any, PO numbers should be investigated? 4. Are there any POs where the Quantity received is not equal to the quantity ordered? If So, how many? Hint: Subtract quantity ordered from quantity received look for value not equal to 0. Why is this a problem? 5. Search for Duplicate vendors, e.g. are there two different vendor numbers at same address? Hint: use pivot table. What might two vendors at the same address indicate? 6. Are there any products that are bought from more than I vendor? If so, compare the vendors for price? Are we using the lowest priced vendor most frequently? Hint: use pivot table. Using Excel to test controls in PO Data Obtain data file "AnyCO-C13 Excel Problem" from "name of site". Do any of the following activities to test the controls you have learned in this chapter 1. Test the PO number sequence for out of sequence PO numbers (use sheet 1: "AnyCO- POs"). Hint: First make sure data is sorted in PO number order, add a column and use the Excel IF function (e.g., If b2-b1=1, Ok, GAP). If there are any gaps, how many? Use Excel CountIF function. Is it inherently a control failure to have a GAP in sequence number? What could reasonably account for a gap? Why might a GAP indicate a control problem? Test whether all of the vendors that were used on the POs were from the authorized vendor list (the authorized vendor list is on spreadsheet 2 titled "Any CO-Vendors". Hint: use VLOOKUP on vendor number to the vendor table and return the vendor name: example formula: =VLOOKUP(F2, AnyCO-Vendors'IS1:$1048576,2,FALSE), search for N/A. Were any POs issued to vendors not on the vendor master list? If so how many and what are the potential problems? 3. Are there any open POs (e.g., those not received against)? If yes, how many? Hint: use VLOOKUP on PO number to the Receipt table and return the Quantity received. How would you determine if the open PO is a problem? Assume we are at calendar year end, which, if any, PO numbers should be investigated? 4. Are there any POs where the Quantity received is not equal to the quantity ordered? If So, how many? Hint: Subtract quantity ordered from quantity received look for value not equal to 0. Why is this a problem? 5. Search for Duplicate vendors, e.g. are there two different vendor numbers at same address? Hint: use pivot table. What might two vendors at the same address indicate? 6. Are there any products that are bought from more than I vendor? If so, compare the vendors for price? Are we using the lowest priced vendor most frequently? Hint: use pivot table