Step	Instructions	Points Possible
1	Open the ClothingTech Data Part 1v3 workbook.	0
2	In this step you will take a few preliminary actions to help with work done in subsequent steps. Without using the Tables feature, turn on functionality in the 2015 Data worksheet so that the first row does not roll off the screen as you scroll down past the last row of data that appears on your window. Also, without modifying any part of the workbook, use the Amount column to determine the balance in Accounts Receivable (AR). Record this amount in cell B3 in the Misc sheet. This amount should be compared to the balance in the AR account as shown on a trial balance or balance sheet to reconcile the detail from the system to the control account.	2
3	In the next three steps you are going to verify that the customer names listed on the transaction detail exist in the customer master file. This is an important control to verify that sales have been made to legitimate customers. If there is a name in the transaction detail that doesn't exist in the customer master file, then that would indicate a control issue that should be investigated further.	0
4	The customer name and other related information (e.g., address and contact person) is maintained in another part of the system. In the Misc sheet you will find a list of customer names that were copied from this source (i.e., the customer master file). To verify that we dont have any other customers that may have entered into the 2015 transactions, we need to compare this list to the names in our data. Insert another sheet to the right of the 2015 Data sheet and name it Customer Name Verification. Copy all the customer names from the 2015 Data sheet to the Customer Name Verification sheet starting at A4. Remove any duplicates and sort the names alphabetically. Enter the text "Customer Name" in cell A3, apply a Bottom Border border type, and Autofit column A.	2.5
5	In the Misc sheet, create the range name ValidCustomerNames for the customer names that came from the customer master file. In the Customer Name Verification sheet, enter the text Validation Check* (without the quotes but with the asterisk) in cell B3 and select the Bottom Border border type for the cell. Center the text within the cell and Autofit column B. In cell A1, enter the text * Names validated against customer master file. Autofit column A.	2.5
6	In cell B4, create a formula that uses the range name created in the previous step within the MATCH, IF, and IFERROR functions to verify that that the customer names in the data are valid. The formula should use relative references as appropriate, should return the result Validated for those names that are in the range name, and should return **Invalid** for those that are not. Copy this formula down to the appropriate cells below B4. Also center the results of the formula within the cell and create a conditional format all of the results of the formula that will highlight any **Invalid** results with a Yellow Fill with Dark YellowText. Hint: The order of the functions in the formula should be IFERROR, IF, and then MATCH.	13
7	Based on your results, answer the question in the Misc sheet on row 30 by highlighting the correct answer with a fill color of yellow.	1
8	In the next two steps, you are going to perform a completeness test to ensure that all sales transactions have been accounted for in the data. In this case, it will be to verify that all invoice numbers are accounted for in the sales data. This assumes that there has been a proper cutoff in the acquisition of the beginning and ending invoice numbers.	0
9	A review of sales transactions indicates that the beginning sales invoice for the year was 1001 and the ending invoice number was 1230. To verify that all of these are accounted for in the 2015 Data sheet, insert another sheet to the right of Customer Name Verification and name it Invoice Number Sequence Test. In cell A3 enter the text Invoice Number and Autofit column A. Copy the invoice numbers from the 2015 Data sheet and paste them into the Invoice Number Sequence Test sheet starting at cell A4. Sort all the invoice numbers in ascending order. Add a Bottom Border border type to cell A3 and center the data in column A.	3.5
10	In cell B3 enter the text Sequence Test* and enter the following text in cell A1: * Ok appears in the cells below if the previous invoice number + 1 equals the current invoice number; otherwise, the text Sequence Issue appears. Enter a formula in cell B5 using relative references that tests if the invoice number in A4 + 1 is equal to the invoice number in A5. If so, then return the text Ok; otherwise, return the value Sequence Issue. Copy and paste the formula down to the appropriate cells below cell A5. Center the data in B3:B233. Apply a Bottom Border to cell B3 and Autofit column B.	6.5
11	Based on your results, answer the question in the Misc sheet on row 31 by highlighting the correct answer with a fill color of yellow.	1
12	In the next step, you are going to create a list of customers and the amounts they owe that make up the AR balance. This is needed to determine if there are one or more customers that make up a large part of the AR balance. If so, then additional controls should be in place to monitor these customers to help determine the viability of cash collections and take preemptive action if collectibility issues are found.	0
13	Insert a new sheet named Customer Balance Data and move it to the right of the Invoice Number Sequence Test sheet. From the data in the 2015 Data sheet, copy the headings and data in the TransactionNumber to Amount columns to columns A:F of the new sheet, starting at row 1. Since the transaction number and the applied to transaction number are the same for all Sales transactions, copy the transaction numbers for all Sales transactions to the applied to transaction number cells. Using the Customer Balance Data sheet, create a PivotTable in a new sheet named Customer Balances PT and move it to the right of the Customer Balance Data sheet. In the PivotTable, list all customer names and invoice numbers (rows) along with their balance followed by the percent of total accounts receivable (values). Change the column headings in row 3 to Customers With Invoices, Balance Due, and % of Total, and center the "Balance Due" and "% of Total" labels within their cell. Change the format of the Balance Due amounts to use the defaults of the Accounting format. Filter the PivotTable so that invoices with zero balances are omitted. Collapse all of the customers so that there is only a list of customers with the balance due and percent of total displaying. Create a new PivotTable Style that will bold the and apply the Bottom Border style to the Header Row and that will apply a Top and Double-Line Bottom Border border style to the Grand Total Row. Use the default name for the style, and apply it to the PivotTable.	15
14	Based on your results, answer the question in the Misc sheet on row 32 by highlighting the correct answer with a fill color of yellow. In addition, list the names of any customers alphabetically that warrant additional tests in column D staring with cell D33.	3
15	This is the end of the first part to this project. Submit your work to be graded. Based on the number of attempts provided, make changes and re-submit this part of the project until you have achieved the highest score or your number of attempts has been exhausted and then proceed to the next part of this project.	0

Total Points

50

EXCEL: in comments