Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / This problem is based on the JA Tires data that was first introduced in problem 6-35
This problem is based on the JA Tires data that was first introduced in problem 6-35. If you have not already accessed the data, it can be downloaded from the textbook website or from the attached Excel file. As part of risk assessment procedures, you have been asked to perform some preliminary data analytics on the sales file to assess whether individual transactions or classes of sales transactions represent increased inherent risk because they are large, unusual, or involve related parties. Please import the Excel file into IDEA, then complete items 1-3 below.
Required
Identify the five largest sales transactions. Indicate whether you believe these transactions should require an increase in inherent risk. Do you believe any of these transactions individually, or in the aggregate, should be considered a significant risk?
Identify any other transactions that appear to involve unusual product numbers, customers, or prices.
Answer :
a.Sort the invoice file by invoice amount. The five largest transactions are listed below.
|
Invoice Number |
Amount |
|
139179 |
$12,571,043.85 |
|
139607 |
$11,385,059.36 |
|
139619 |
$11,117,093.48 |
|
139039 |
$9,893,615.34 |
|
139374 |
$8,577,691.28 |
These transactions are much larger than other sales transactions (the next largest sale is $2,362,365.32). All five sales occurred in the last two months of the year, were to the same customer, and involved the same product number which has a different format (it includes a hyphen) than standard product numbers.
It is an auditor judgment, but these transactions would likely cause the auditor to increase inherent risk. Because of the combined amounts involved, they would also likely be regarded as a significant risk.
b. In addition to the transactions indicated in part a., the following transactions involve sales to potentially invalid customer numbers.
|
Invoice Number |
Customer Number |
Amount |
|
139549 |
3975 |
$395,353.71 |
|
136856 |
3495 |
$133,392.76 |
|
138839 |
3294 |
$103,217.38 |
|
135863 |
3254 |
$19,805.00 |
|
138595 |
3095 |
$413,484.52 |
|
136130 |
3095 |
$261,211.19 |
|
137909 |
3062 |
$15,663.40 |
All valid JA customer numbers start with the 1 as the first digit. Because JA has a small number of customers, most students will sort the file by customer number to identify these transactions. If a larger number of customers were involved, it would be more effective to use a VLOOKUP to identify customer numbers that are not included in the Customer_Master file or use ACL or IDEA to identify customer numbers.
To identify unusual product numbers, compare the product numbers included in the Invoice file to those in the Product_Master file using a VLOOKUP function. Navigate to the Invoice tab and include a new column name (e.g. Products) in column L. In cell L2, enter =VLOOKUP(F2,Product_Master!$A$2:$F$12,1,FALSE). This will add the product number to the cell, or return #N/A for any instances where the product number is not included on the Product_Master list. Copy the formula all the way down the column. Highlight the worksheet, filter the data, and filter for values where “Products” equals #N/A. This identifies the same 5 transactions listed in part a. for product SO-TRUALL1001, but no additional concerns.
To identify unusual prices, sort the Invoice worksheet by product number and by price to identify the range of prices. Most ranges are reasonable, with the exception again of the 5 transactions identified in part a. and b. above.
