Automated Transportation, Inc. is a medium-sized manufacturer of remote-controlled cars, boats, and drones. The company was established five years
ago when two
others decided to build and sell remote control cars to enthusiasts. As the company grew, they expanded their product offerings to
include remote control cars, boats, and drones. The
others serve as the president and CEO of the company, and they now have over 70 employees.
They have two key customer groups, ho
yists and businesses interested in incorporating drones into their business process and supply chain. These two
customer bases provide a variety of opportunities for growth. Management values internal control, but since they are busy running the company they
have employed accounting personnel to help design the company’s processes, policies, and internal controls:
eo The company is not required to have a report on internal controls and external auditors are not required to attest to the company’s internal controls.
eo However, the owners want to be sure the company employees are designing and following policies that will help them maintain efficient and
effective operations.
eo The owners are also very data-driven and make many of their business decisions only after considering the data collected and analyzed.
The following is an excerpt from the data dictionary designed by the company’s information systems personnel and accounting information systems
accountants.
Data Field Label
Invoice numbe
Invoice amount
Shipment date
Invoice date
Vendor identification
numbe
Vendor name
Product purchased
Unit cost
Shipping cost
Flat duty
Tariff
Field Name in
Database
InvoiceNO
InvoiceAmt
ShipDate
InvoiceDate
VendorID
VendorName
ProductID
UnitCost
ShipCost
FlatDuty
TariffAmt
Field Description
The invoice number, which is hand-keyed into the AIS by the AP clerk
from a manual invoice mailed to the company by the vendor.
The amount of the invoice, which is hand-keyed into the AIS by the AP clerk
from a manual invoice mailed to the company by the vendor.
The date the product was shipped from the shipping location.
The date of the invoice, which handkeyed into the AIS by the AP clerk
from a manual invoice mailed to the company by the vendor.
Unique vendor identification number.
Name of the vendor.
Product code for the product purchased from the vendor. This product code
is consistent with the catalog from the vendor.
The cost per unit of the product purchased from the vendor.
Total shipping costs.
The flat duty rate applies to article that are dutiable.
The total dollar amount of a tariff applied to the goods shipped.
Shipping location
Receiving Quality rating
Payment terms
Shipping terms
Payment address
Purchase order numbe
Purchase order date
Receiving report numbe
Receiving report date
Quantity received
Quantity purchased
Invoiced quantity
Treasurer Approval
ShipLocation
QualityRate
PaymentTerms
ShipTerms
PayAddress
PONumbe
PODate
ReceivingNumbe
ReceivingDate
QtyReceived
QtyPurchased
Qtylnvoice
Approved
The country in which the goods are shipped from.
This is a quality rating keyed into the AIS by the receiving team when they receive the goods.
The scale is 1 = poor to 5 = excellent quality. The receiving team rates the shipment
on packaging, quality of materials, and overall delivery.
The agreed-upon payment terms with the vendor. These terms are negotiated by the
purchasing manager and keyed into the vendor master file by the purchasing supervisor.
Shipping terms-typically FOB destination or FOB shipping.
The vendors address where payment is to be mailed.
The unique identifying number assigned to each purchase order issued by the company.
The date the purchase order is issued by the company.
The unique identifying number assigned to each receiving report created by the company’s
eceiving group.
The date the product is received by the company’s receiving department.
The total quantity of items received.
The total quantity of items on the Purchase Order.
The total quantity of items on the invoice, this amount is hand keyed into the
AIS by the AP clerk from the manual invoice mailed to the company by the vendor.
The initials of the Treasurer indicating their approval if the invoice was greater than $10,000.
Managerial Accounting: Rank Vendor Quality
Because the company is growing so fast, the owners want to be sure that they are partnering with the right vendors for raw materials. They have asked
the receiving team to give each receipt a rating to document the quality of the packaging, the materials in appearance, and overall delivery. For each
eceipt the receiving group documents a quality rating in the accounting information system. The owners have asked you to determine how the quality
ating by vendor has changed since the beginning of the year. Your manager has designed the following partial data analysis strategy. Complete the chart
and document the risks and related controls to your analyses.
Objective and Questions Data and Analysis Strategies Risks Controls
Objective: Understand the quality ratings of Data strategy: VendorID, VendorName, ReceivingNo, ReceivingDate, 1. Data: 3. Data:
purchases. QualityRate
Questions: What is the average quality rating by = Analysis strategy: Calculate the average QualityRating by 2. 4.
vendor? VendorName. Analysis: Analysis:
What is the highest and lowest quality rating by Calculate the minimum and maximum QualityRating by VendorName.
vendor? Visualize how QualityRating by VendorName has changed over time.
How has the quality rating by vendor changed
over time?
5. Perform the analyses suggested in the data analyses project plan.
Review the provided data and prepare the data analysis project plan in the first tab of your Excel workbook. Label that tab "PROJECT PLAN". In a separate
tab of the workbook perform the analysis and summarize the results. Upload the Excel file and provide clear comments regarding the location of each
analysis identified in the chart.
Data
ShipDate InvoiceDate VendorID VendorName ProductID ShipCost ShipLocation ReceivingNo ShipDays ReceivingDate QualityRate InvoiceAmt QtyReceived QtyPurchased QtyInvoice VoucherPackNo Approved
1/21/2025 1/21/2025 1543 Ambassador Blue C3809 24544 Beijing, China 3084 45 4/21/2025 2 6,135.90 24500 24500 24500 16341
1/13/2025 1/13/2025 1543 Ambassador Blue C3809 44858 Beijing, China 3042 85 4/13/2025 4 11,214.34 44800 44800 44800 16299 RO
1/13/2025 1/13/2025 1543 Ambassador Blue C3809 42060 Beijing, China 3034 87 4/13/2025 5 10,514.94 42000 42000 42000 16291 RO
1/11/2025 1/11/2025 1543 Ambassador Blue C3809 30479 Beijing, China 3020 89 4/11/2025 5 7,619.69 30400 30400 30400 16277
1/10/2025 1/10/2025 1543 Ambassador Blue C3809 23081 Beijing, China 3017 87 4/10/2025 3 5,770.15 23000 23000 23000 16274
1/2/2025 1/2/2025 1543 Ambassador Blue C3809 23964 Beijing, China 3004 88 4/2/2025 5 5,990.78 23900 23900 23900 16261
1/1/2025 1/1/2025 1543 Ambassador Blue C3809 22613 Beijing, China 3001 46 4/1/2025 5 5,653.06 22600 22600 22600 16258
1/21/2025 1/21/2025 1544 Big Texo G3940 8223 Mexico City, Mexico 3085 45 3/7/2025 4 6,167.10 8200 8200 8200 16342
1/14/2025 1/14/2025 1544 Big Texo G3940 7942 Mexico City, Mexico 3043 45 2/28/2025 5 5,955.97 7900 7900 7900 16300
1/13/2025 1/13/2025 1544 Big Texo G3940 7586 Mexico City, Mexico 3035 45 2/27/2025 5 5,689.23 7500 7500 7500 16292
1/11/2025 1/11/2025 1544 Big Texo G3940 7628 Mexico City, Mexico 3021 45 2/25/2025 4 5,720.42 7600 7600 7600 16278
1/11/2025 1/11/2025 1544 Big Texo G3940 7528 Mexico City, Mexico 3018 45 2/25/2025 4 5,645.82 7500 7500 7500 16275
1/2/2025 1/2/2025 1544 Big Texo G3940 8149 Mexico City, Mexico 3005 45 2/16/2025 3 6,111.49 8100 8100 8100 16262
1/1/2025 1/1/2025 1544 Big Texo G3940 7769 Mexico City, Mexico 3002 45 2/15/2025 4 5,826.66 7700 7700 7700 16259
1/30/2025 1/30/2025 1556 Britton Parts, Inc. M3940 14778 Sokhna, Egypt 3089 90 4/30/2025 4 6,206.43 14700 14700 14700 16354
1/20/2025 1/20/2025 1556 Britton Parts, Inc. M3940 14329 Sokhna, Egypt 3083 90 4/20/2025 3 6,017.90 14300 14300 14300 16340
1/18/2025 1/18/2025 1556 Britton Parts, Inc. M3940 14033 Sokhna, Egypt 3073 90 4/18/2025 5 5,893.58 14000 14000 14000 16330
1/17/2025 1/17/2025 1556 Britton Parts, Inc. M3940 16977 Sokhna, Egypt 3055 90 4/17/2025 5 7,130.07 16900 16900 16900 16312
1/17/2025 1/17/2025 1556 Britton Parts, Inc. M3940 13566 Sokhna, Egypt 3064 90 4/17/2025 4 5,697.36 13500 13500 13500 16321
1/22/2025 1/22/2025 1551 Component Parts Ltd. G3940 7529 Rockford, Illinois - USA 3096 15 2/6/2025 3 5,646.28 7500 7500 7500 16349
1/20/2025 1/20/2025 1551 Component Parts Ltd. G3940 7694 Rockford, Illinois - USA 3078 15 2/4/2025 4 5,770.15 7600 7600 7600 16335
1/18/2025 1/18/2025 1551 Component Parts Ltd. G3940 7611 Rockford, Illinois - USA 3068 15 2/2/2025 5 5,708.21 7600 7600 7600 16325
1/17/2025 1/17/2025 1551 Component Parts Ltd. G3940 8947 Rockford, Illinois - USA 3059 15 2/1/2025 5 6,709.62 8900 8900 8900 16316
1/17/2025 1/17/2025 1551 Component Parts Ltd. G3940 8316 Rockford, Illinois - USA 3050 15 2/1/2025 5 6,236.27 8300 8300 8300 16307
1/12/2025 1/12/2025 1551 Component Parts Ltd. G3940 8968 Rockford, Illinois - USA 3028 15 1/27/2025 5 6,725.89 8900 8900 8900 16285
1/1/2025 1/1/2025 1551 Component Parts Ltd. G3940 9533 Rockford, Illinois - USA 3012 15 1/16/2025 5 7,149.51 9500 9500 9500 16269
1/30/2025 1/30/2025 1555 Die Cast Mart F3921 6811 Texarcana, Texas - USA 3091 5 2/4/2025 4 5,788.69 6800 6800 6800 16353
1/21/2025 1/21/2025 1555 Die Cast Mart F3921 6984 Texarcana, Texas - USA 3086 5 1/26/2025 5 5,936.07 6900 6900 6900 16343
1/20/2025 1/20/2025 1555 Die Cast Mart F3921 6942 Texarcana, Texas - USA 3082 5 1/25/2025 5 5,900.36 6900 6900 6900 16339
1/18/2025 1/18/2025 1555 Die Cast Mart F3921 6805 Texarcana, Texas - USA 3072 5 1/23/2025 4 5,783.72 6800 6800 6800 16329
1/17/2025 1/17/2025 1555 Die Cast Mart F3921 7645 Texarcana, Texas - USA 3063 5 1/22/2025 5 6,498.03 7600 7600 7600 16320
1/17/2025 1/17/2025 1555 Die Cast Mart F3921 7145 Texarcana, Texas - USA 3054 5 1/22/2025 5 6,073.06 7100 7100 7100 16311
1/14/2025 1/14/2025 1555 Die Cast Mart F3921 7218 Texarcana, Texas - USA 3044 5 1/19/2025 5 6,135.00 7200 7200 7200 16301
1/13/2025 1/13/2025 1555 Die Cast Mart F3921 10169 Texarcana, Texas - USA 3032 5 1/18/2025 4 8,643.25 10100 10100 10100 16289
1/13/2025 1/13/2025 1555 Die Cast Mart F3921 8905 Texarcana, Texas - USA 3041 5 1/18/2025 2 7,569.06 8900 8900 8900 16298
1/13/2025 1/13/2025 1555 Die Cast Mart F3921 6841 Texarcana, Texas - USA 3036 5 1/18/2025 5 5,814.01 6800 6800 6800 16293
1/13/2025 1/13/2025 1555 Die Cast Mart F3921 6731 Texarcana, Texas - USA 3033 5 1/18/2025 3 5,721.33 6700 6700 6700 16290
1/11/2025 1/11/2025 1555 Die Cast Mart F3921 7885 Texarcana, Texas - USA 3019 5 1/16/2025 4 6,701.93 7800 7800 7800 16276
1/11/2025 1/11/2025 1555 Die Cast Mart F3921 6716 Texarcana, Texas - USA 3022 5 1/16/2025 5 5,707.76 6700 6700 6700 16279
1/9/2025 1/9/2025 1555 Die Cast Mart F3921 6701 Texarcana, Texas - USA 3016 5 1/14/2025 5 5,695.10 6700 6700 6700 16273
1/2/2025 1/2/2025 1555 Die Cast Mart F3921 7116 Texarcana, Texas - USA 3006 85 1/7/2025 2 6,048.19 7100 7100 7100 16263
1/1/2025 1/1/2025 1555 Die Cast Mart F3921 12912 Texarcana, Texas - USA 3003 87 1/6/2025 5 10,974.73 12900 12900 12900 16260 RO
1/22/2025 1/22/2025 1579 Hultont & Co. Q9309 11101 Beijing, China 3094 89 4/22/2025 3 7,215.52 11100 11100 11100 16346
1/15/2025 1/15/2025 1579 Hultont & Co. Q9309 9440 Beijing, China 3048 87 4/15/2025 4 6,135.45 9400 9400 9400 16305
1/13/2025 1/13/2025 1579 Hultont & Co. Q9309 8782 Beijing, China 3040 88 4/13/2025 3 5,707.76 8700 8700 8700 16297
1/11/2025 1/11/2025 1579 Hultont & Co. Q9309 9408 Beijing, China 3025 87 4/11/2025 2 6,115.10 9400 9400 9400 16282
1/3/2025 1/3/2025 1579 Hultont & Co. Q9309 8902 Beijing, China 3009 88 4/3/2025 5 5,785.98 8900 8900 8900 16266
1/21/2025 1/21/2025 1546 Loyciut Holdings H9302 30507 St. Louis, MO - USA 3087 10 1/31/2025 5 10,677.25 30500 30500 30500 16344 RO
1/15/2025 1/15/2025 1546 Loyciut Holdings H9302 16438 St. Louis, MO - USA 3045 10 1/25/2025 3 5,752.97 16400 16400 16400 16302
1/13/2025 1/13/2025 1546 Loyciut Holdings H9302 16151 St. Louis, MO - USA 3037 10 1/23/2025 4 5,652.61 16100 16100 16100 16294
1/11/2025 1/11/2025 1546 Loyciut Holdings H9302 25092 St. Louis, MO - USA 3023 10 1/21/2025 5 8,782.04 25000 25000 25000 16280
1/3/2025 1/3/2025 1546 Loyciut Holdings H9302 19566 St. Louis, MO - USA 3007 85 1/13/2025 4 6,847.96 19500 19500 19500 16264
1/30/2025 1/30/2025 1552 LT Distribution F3921 6796 Dijbouti, South Africa 3092 87 4/25/2025 5 5,776.48 6700 6700 6700 16350
1/20/2025 1/20/2025 1552 LT Distribution F3921 7056 Dijbouti, South Africa 3079 89 4/15/2025 5 5,997.56 7000 7000 7000 16336
1/18/2025 1/18/2025 1552 LT Distribution F3921 9986 Dijbouti, South Africa 3069 87 4/13/2025 5 8,487.73 9900 9900 9900 16326
1/17/2025 1/17/2025 1552 LT Distribution F3921 7715 Dijbouti, South Africa 3060 88 4/12/2025 5 6,557.26 7700 7700 7700 16317
1/17/2025 1/17/2025 1552 LT Distribution F3921 7080 Dijbouti, South Africa 3051 85 4/12/2025 5 6,017.90 7000 7000 7000 16308
1/13/2025 1/13/2025 1552 LT Distribution F3921 6935 Dijbouti, South Africa 3029 85 4/8/2025 3 5,894.03 6900 6900 6900 16286
1/7/2025 1/7/2025 1552 LT Distribution F3921 7072 Dijbouti, South Africa 3013 85 4/2/2025 4 6,010.67 7000 7000 7000 16270
1/22/2025 1/22/2025 1550 Master Makers, Inc. C3809 22584 Sinapore, Japan 3097 87 4/17/2025 4 5,645.82 22500 22500 22500 16348
1/11/2025 1/11/2025 1550 Master Makers, Inc. C3809 26808 Sinapore, Japan 3027 89 4/6/2025 5 6,701.93 26800 26800 26800 16284
1/1/2025 1/1/2025 1550 Master Makers, Inc. C3809 22656 Sinapore, Japan 3011 87 3/27/2025 3 5,663.91 22600 22600 22600 16268
1/30/2025 1/30/2025 1553 Purple Supplier H9302 17333 Haifa, Israel 3090 88 4/30/2025 5 6,066.28 17300 17300 17300 16351
1/20/2025 1/20/2025 1553 Purple Supplier H9302 21998 Haifa, Israel 3080 90 4/20/2025 4 7,699.26 21900 21900 21900 16337
1/18/2025 1/18/2025 1553 Purple Supplier H9302 16683 Haifa, Israel 3070 90 4/18/2025 3 5,838.87 16600 16600 16600 16327
1/17/2025 1/17/2025 1553 Purple Supplier H9302 31691 Haifa, Israel 3061 85 4/17/2025 3 11,091.82 31600 31600 31600 16318 RO
1/17/2025 1/17/2025 1553 Purple Supplier H9302 17195 Haifa, Israel 3052 87 4/17/2025 5 6,017.90 17100 17100 17100 16309
1/13/2025 1/13/2025 1553 Purple Supplier H9302 16841 Haifa, Israel 3030 89 4/13/2025 3 5,894.03 16800 16800 16800 16287
1/8/2025 1/8/2025 1553 Purple Supplier H9302 31985 Haifa, Israel 3014 87 4/8/2025 3 11,194.45 31900 31900 31900 16271 RO
1/30/2025 1/30/2025 1554 Quality Sparks L9204 12823 Piraeus, Greece 3093 88 4/25/2025 3 5,770.15 12800 12800 12800 16352
1/20/2025 1/20/2025 1554 Quality Sparks L9204 12960 Piraeus, Greece 3081 85 4/15/2025 5 5,831.64 12900 12900 12900 16338
1/18/2025 1/18/2025 1554 Quality Sparks L9204 16090 Piraeus, Greece 3071 85 4/13/2025 2 7,240.38 16000 16000 16000 16328
1/17/2025 1/17/2025 1554 Quality Sparks L9204 16228 Piraeus, Greece 3062 85 4/12/2025 3 7,302.32 16200 16200 16200 16319
1/17/2025 1/17/2025 1554 Quality Sparks L9204 13511 Piraeus, Greece 3053 85 4/12/2025 4 6,079.84 13500 13500 13500 16310
1/13/2025 1/13/2025 1554 Quality Sparks L9204 12562 Piraeus, Greece 3031 85 4/8/2025 5 5,652.61 12500 12500 12500 16288
1/8/2025 1/8/2025 1554 Quality Sparks L9204 13317 Piraeus, Greece 3015 85 4/3/2025 5 5,992.59 13300 13300 13300 16272
1/20/2025 1/20/2025 1558 Snail Quality Productions G3940 12020 Beijing, China 3075 90 4/20/2025 3 9,014.42 12000 12000 12000 16332
1/17/2025 1/17/2025 1558 Snail Quality Productions G3940 10318 Beijing, China 3066 90 4/17/2025 5 7,738.14 10300 10300 10300 16323
1/17/2025 1/17/2025 1558 Snail Quality Productions G3940 7822 Beijing, China 3057 90 4/17/2025 2 5,866.00 7800 7800 7800 16314
1/18/2025 1/18/2025 1557 Stylk, Inc. C3809 33986 Hong Kong Hong Kong 3074 90 4/18/2025 5 8,496.32 33900 33900 33900 16331
1/17/2025 1/17/2025 1557 Stylk, Inc. C3809 23824 Hong Kong Hong Kong 3065 90 4/17/2025 3 5,955.97 23800 23800 23800 16322
1/17/2025 1/17/2025 1557 Stylk, Inc. C3809 23577 Hong Kong Hong Kong 3056 90 4/17/2025 3 5,894.03 23500 23500 23500 16313
1/22/2025 1/22/2025 1549 T&Y M3940 16392 Tokyo, Japan 3095 85 4/17/2025 5 6,884.58 16300 16300 16300 16347
1/15/2025 1/15/2025 1549 T&Y M3940 27135 Tokyo, Japan 3049 85 4/10/2025 3 11,396.54 27100 27100 27100 16306 RO
1/11/2025 1/11/2025 1549 T&Y M3940 13885 Tokyo, Japan 3026 85 4/6/2025 4 5,831.64 13800 13800 13800 16283
1/1/2025 1/1/2025 1549 T&Y M3940 21046 Tokyo, Japan 3010 85 3/27/2025 3 8,839.01 21000 21000 21000 16267
1/20/2025 1/20/2025 1559 Texas Parts F3921 7544 Fort Worth, Texas - USA 3077 15 2/4/2025 5 6,411.68 7500 7500 7500 16334
1/20/2025 1/20/2025 1559 Texas Parts F3921 7210 Fort Worth, Texas - USA 3076 15 2/4/2025 3 6,128.22 7200 7200 7200 16333
1/18/2025 1/18/2025 1559 Texas Parts F3921 6813 Fort Worth, Texas - USA 3067 15 2/2/2025 4 5,790.95 6800 6800 6800 16324
1/17/2025 1/17/2025 1559 Texas Parts F3921 7153 Fort Worth, Texas - USA 3058 15 2/1/2025 4 6,079.84 7100 7100 7100 16315
1/22/2025 1/22/2025 1548 V Logic L9204 19415 Sinapore, Japan 3088 90 4/22/2025 3 8,736.38 19400 19400 19400 16345
1/15/2025 1/15/2025 1548 V Logic L9204 13374 Sinapore, Japan 3047 90 4/15/2025 5 6,017.90 13300 13300 13300 16304
1/13/2025 1/13/2025 1548 V Logic L9204 13608 Sinapore, Japan 3039 90 4/13/2025 5 6,123.24 13600 13600 13600 16296
1/11/2025 1/11/2025 1548 V Logic L9204 12940 Sinapore, Japan 3024 90 4/11/2025 3 5,822.60 12900 12900 12900 16281
1/3/2025 1/3/2025 1548 V Logic L9204 13152 Sinapore, Japan 3008 90 4/3/2025 5 5,917.99 13100 13100 13100 16265