stuck on numbers 5 & 6!!
alance or balance sheet to reconcile the detail from the system to the control account 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. 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 don't 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 name's alphabetically. Enter the text "Customer Name" in cell A3, apply a Bottom Border border type, and Autofit column A 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. 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 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 Clothing ted On: 01/03/2019 Formulas Data Review View Help QuickBooks File Home Insert Page Layout Define Nam A Use in Form nsetAuto Sum Recently Financial Logjcaste & lekMth More. Nane Time Reference Trig Functions Manager is Create from Defined Names Used Function Library D5 3 Accounts Receivable balance: 684,491.19 5 Customer Names from Customer Master File: Bigmart Cool Threads Corner Runner Cross Country Mart Family Fit 10 Fit N Fun Neighborhood Athletic Supply Northern Lites Runners Market Southeast Regional Southern Runners Super Runners Mark Urban Runner ValueChoice 12 13 15 16 19 20 24 25 26 27 28 29 Results from analyses: 30 Are there any customers in the sales data that were not contained in the customer master 31 Are there any invoices that are not accounted for in the sales data? Yes Yes No Yes No No there any companies with A/R balances that warrant additional tests? 33If so, list their names alphabetically in column D staring in cell D33 34 38 alance or balance sheet to reconcile the detail from the system to the control account 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. 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 don't 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 name's alphabetically. Enter the text "Customer Name" in cell A3, apply a Bottom Border border type, and Autofit column A 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. 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 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 Clothing ted On: 01/03/2019 Formulas Data Review View Help QuickBooks File Home Insert Page Layout Define Nam A Use in Form nsetAuto Sum Recently Financial Logjcaste & lekMth More. Nane Time Reference Trig Functions Manager is Create from Defined Names Used Function Library D5 3 Accounts Receivable balance: 684,491.19 5 Customer Names from Customer Master File: Bigmart Cool Threads Corner Runner Cross Country Mart Family Fit 10 Fit N Fun Neighborhood Athletic Supply Northern Lites Runners Market Southeast Regional Southern Runners Super Runners Mark Urban Runner ValueChoice 12 13 15 16 19 20 24 25 26 27 28 29 Results from analyses: 30 Are there any customers in the sales data that were not contained in the customer master 31 Are there any invoices that are not accounted for in the sales data? Yes Yes No Yes No No there any companies with A/R balances that warrant additional tests? 33If so, list their names alphabetically in column D staring in cell D33 34 38
