I have uploaded all the files needed

I have uploaded all the files needed


Automated Transportation, Inc. is a medium-sized manufacturer of remote-controlled cars, boats, and drones. The company was established five years ago when two brothers 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 brothers serve as the president and CEO of the company, and they now have over 70 employees. They have two key customer groups, hobbyists 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 number Invoice amount Shipment date Invoice date Vendor identification number 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 number Purchase order date Receiving report number Receiving report date Quantity received Quantity purchased Invoiced quantity Treasurer Approval ShipLocation QualityRate PaymentTerms ShipTerms PayAddress PONumber PODate ReceivingNumber 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 receiving 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 receipt the receiving group documents a quality rating in the accounting information system. The owners have asked you to determine how the quality rating 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 ShipDateInvoiceDateVendorIDVendorNameProductIDShipCostShipLocationReceivingNoShipDaysReceivingDateQualityRateInvoiceAmtQtyReceivedQtyPurchasedQtyInvoiceVoucherPackNoApproved 1/21/20251/21/20251543Ambassador BlueC380924544Beijing, China3084454/21/202526,135.9024500245002450016341 1/13/20251/13/20251543Ambassador BlueC380944858Beijing, China3042854/13/2025411,214.3444800448004480016299RO 1/13/20251/13/20251543Ambassador BlueC380942060Beijing, China3034874/13/2025510,514.9442000420004200016291RO 1/11/20251/11/20251543Ambassador BlueC380930479Beijing, China3020894/11/202557,619.6930400304003040016277 1/10/20251/10/20251543Ambassador BlueC380923081Beijing, China3017874/10/202535,770.1523000230002300016274 1/2/20251/2/20251543Ambassador BlueC380923964Beijing, China3004884/2/202555,990.7823900239002390016261 1/1/20251/1/20251543Ambassador BlueC380922613Beijing, China3001464/1/202555,653.0622600226002260016258 1/21/20251/21/20251544Big Texo G39408223Mexico City, Mexico3085453/7/202546,167.1082008200820016342 1/14/20251/14/20251544Big Texo G39407942Mexico City, Mexico3043452/28/202555,955.9779007900790016300 1/13/20251/13/20251544Big Texo G39407586Mexico City, Mexico3035452/27/202555,689.2375007500750016292 1/11/20251/11/20251544Big Texo G39407628Mexico City, Mexico3021452/25/202545,720.4276007600760016278 1/11/20251/11/20251544Big Texo G39407528Mexico City, Mexico3018452/25/202545,645.8275007500750016275 1/2/20251/2/20251544Big Texo G39408149Mexico City, Mexico3005452/16/202536,111.4981008100810016262 1/1/20251/1/20251544Big Texo G39407769Mexico City, Mexico3002452/15/202545,826.6677007700770016259 1/30/20251/30/20251556Britton Parts, Inc. M394014778Sokhna, Egypt3089904/30/202546,206.4314700147001470016354 1/20/20251/20/20251556Britton Parts, Inc. M394014329Sokhna, Egypt3083904/20/202536,017.9014300143001430016340 1/18/20251/18/20251556Britton Parts, Inc. M394014033Sokhna, Egypt3073904/18/202555,893.5814000140001400016330 1/17/20251/17/20251556Britton Parts, Inc. M394016977Sokhna, Egypt3055904/17/202557,130.0716900169001690016312 1/17/20251/17/20251556Britton Parts, Inc. M394013566Sokhna, Egypt3064904/17/202545,697.3613500135001350016321 1/22/20251/22/20251551Component Parts Ltd. G39407529Rockford, Illinois - USA3096152/6/202535,646.2875007500750016349 1/20/20251/20/20251551Component Parts Ltd. G39407694Rockford, Illinois - USA3078152/4/202545,770.1576007600760016335 1/18/20251/18/20251551Component Parts Ltd. G39407611Rockford, Illinois - USA3068152/2/202555,708.2176007600760016325 1/17/20251/17/20251551Component Parts Ltd. G39408947Rockford, Illinois - USA3059152/1/202556,709.6289008900890016316 1/17/20251/17/20251551Component Parts Ltd. G39408316Rockford, Illinois - USA3050152/1/202556,236.2783008300830016307 1/12/20251/12/20251551Component Parts Ltd. G39408968Rockford, Illinois - USA3028151/27/202556,725.8989008900890016285 1/1/20251/1/20251551Component Parts Ltd. G39409533Rockford, Illinois - USA3012151/16/202557,149.5195009500950016269 1/30/20251/30/20251555Die Cast MartF39216811Texarcana, Texas - USA309152/4/202545,788.6968006800680016353 1/21/20251/21/20251555Die Cast MartF39216984Texarcana, Texas - USA308651/26/202555,936.0769006900690016343 1/20/20251/20/20251555Die Cast MartF39216942Texarcana, Texas - USA308251/25/202555,900.3669006900690016339 1/18/20251/18/20251555Die Cast MartF39216805Texarcana, Texas - USA307251/23/202545,783.7268006800680016329 1/17/20251/17/20251555Die Cast MartF39217645Texarcana, Texas - USA306351/22/202556,498.0376007600760016320 1/17/20251/17/20251555Die Cast MartF39217145Texarcana, Texas - USA305451/22/202556,073.0671007100710016311 1/14/20251/14/20251555Die Cast MartF39217218Texarcana, Texas - USA304451/19/202556,135.0072007200720016301 1/13/20251/13/20251555Die Cast MartF392110169Texarcana, Texas - USA303251/18/202548,643.2510100101001010016289 1/13/20251/13/20251555Die Cast MartF39218905Texarcana, Texas - USA304151/18/202527,569.0689008900890016298 1/13/20251/13/20251555Die Cast MartF39216841Texarcana, Texas - USA303651/18/202555,814.0168006800680016293 1/13/20251/13/20251555Die Cast MartF39216731Texarcana, Texas - USA303351/18/202535,721.3367006700670016290 1/11/20251/11/20251555Die Cast MartF39217885Texarcana, Texas - USA301951/16/202546,701.9378007800780016276 1/11/20251/11/20251555Die Cast MartF39216716Texarcana, Texas - USA302251/16/202555,707.7667006700670016279 1/9/20251/9/20251555Die Cast MartF39216701Texarcana, Texas - USA301651/14/202555,695.1067006700670016273 1/2/20251/2/20251555Die Cast MartF39217116Texarcana, Texas - USA3006851/7/202526,048.1971007100710016263 1/1/20251/1/20251555Die Cast MartF392112912Texarcana, Texas - USA3003871/6/2025510,974.7312900129001290016260RO 1/22/20251/22/20251579Hultont & Co. Q930911101Beijing, China3094894/22/202537,215.5211100111001110016346 1/15/20251/15/20251579Hultont & Co. Q93099440Beijing, China3048874/15/202546,135.4594009400940016305 1/13/20251/13/20251579Hultont & Co. Q93098782Beijing, China3040884/13/202535,707.7687008700870016297 1/11/20251/11/20251579Hultont & Co. Q93099408Beijing, China3025874/11/202526,115.1094009400940016282 1/3/20251/3/20251579Hultont & Co. Q93098902Beijing, China3009884/3/202555,785.9889008900890016266 1/21/20251/21/20251546Loyciut HoldingsH930230507St. Louis, MO - USA3087101/31/2025510,677.2530500305003050016344RO 1/15/20251/15/20251546Loyciut HoldingsH930216438St. Louis, MO - USA3045101/25/202535,752.9716400164001640016302 1/13/20251/13/20251546Loyciut HoldingsH930216151St. Louis, MO - USA3037101/23/202545,652.6116100161001610016294 1/11/20251/11/20251546Loyciut HoldingsH930225092St. Louis, MO - USA3023101/21/202558,782.0425000250002500016280 1/3/20251/3/20251546Loyciut HoldingsH930219566St. Louis, MO - USA3007851/13/202546,847.9619500195001950016264 1/30/20251/30/20251552LT DistributionF39216796Dijbouti, South Africa3092874/25/202555,776.4867006700670016350 1/20/20251/20/20251552LT DistributionF39217056Dijbouti, South Africa3079894/15/202555,997.5670007000700016336 1/18/20251/18/20251552LT DistributionF39219986Dijbouti, South Africa3069874/13/202558,487.7399009900990016326 1/17/20251/17/20251552LT DistributionF39217715Dijbouti, South Africa3060884/12/202556,557.2677007700770016317 1/17/20251/17/20251552LT DistributionF39217080Dijbouti, South Africa3051854/12/202556,017.9070007000700016308 1/13/20251/13/20251552LT DistributionF39216935Dijbouti, South Africa3029854/8/202535,894.0369006900690016286 1/7/20251/7/20251552LT DistributionF39217072Dijbouti, South Africa3013854/2/202546,010.6770007000700016270 1/22/20251/22/20251550Master Makers, Inc. C380922584Sinapore, Japan3097874/17/202545,645.8222500225002250016348 1/11/20251/11/20251550Master Makers, Inc. C380926808Sinapore, Japan3027894/6/202556,701.9326800268002680016284 1/1/20251/1/20251550Master Makers, Inc. C380922656Sinapore, Japan3011873/27/202535,663.9122600226002260016268 1/30/20251/30/20251553Purple SupplierH930217333Haifa, Israel3090884/30/202556,066.2817300173001730016351 1/20/20251/20/20251553Purple SupplierH930221998Haifa, Israel3080904/20/202547,699.2621900219002190016337 1/18/20251/18/20251553Purple SupplierH930216683Haifa, Israel3070904/18/202535,838.8716600166001660016327 1/17/20251/17/20251553Purple SupplierH930231691Haifa, Israel3061854/17/2025311,091.8231600316003160016318RO 1/17/20251/17/20251553Purple SupplierH930217195Haifa, Israel3052874/17/202556,017.9017100171001710016309 1/13/20251/13/20251553Purple SupplierH930216841Haifa, Israel3030894/13/202535,894.0316800168001680016287 1/8/20251/8/20251553Purple SupplierH930231985Haifa, Israel3014874/8/2025311,194.4531900319003190016271RO 1/30/20251/30/20251554Quality SparksL920412823Piraeus, Greece3093884/25/202535,770.1512800128001280016352 1/20/20251/20/20251554Quality SparksL920412960Piraeus, Greece3081854/15/202555,831.6412900129001290016338 1/18/20251/18/20251554Quality SparksL920416090Piraeus, Greece3071854/13/202527,240.3816000160001600016328 1/17/20251/17/20251554Quality SparksL920416228Piraeus, Greece3062854/12/202537,302.3216200162001620016319 1/17/20251/17/20251554Quality SparksL920413511Piraeus, Greece3053854/12/202546,079.8413500135001350016310 1/13/20251/13/20251554Quality SparksL920412562Piraeus, Greece3031854/8/202555,652.6112500125001250016288 1/8/20251/8/20251554Quality SparksL920413317Piraeus, Greece3015854/3/202555,992.5913300133001330016272 1/20/20251/20/20251558Snail Quality ProductionsG394012020Beijing, China3075904/20/202539,014.4212000120001200016332 1/17/20251/17/20251558Snail Quality ProductionsG394010318Beijing, China3066904/17/202557,738.1410300103001030016323 1/17/20251/17/20251558Snail Quality ProductionsG39407822Beijing, China3057904/17/202525,866.0078007800780016314 1/18/20251/18/20251557Stylk, Inc. C380933986Hong Kong Hong Kong3074904/18/202558,496.3233900339003390016331 1/17/20251/17/20251557Stylk, Inc. C380923824Hong Kong Hong Kong3065904/17/202535,955.9723800238002380016322 1/17/20251/17/20251557Stylk, Inc. C380923577Hong Kong Hong Kong3056904/17/202535,894.0323500235002350016313 1/22/20251/22/20251549T&YM394016392Tokyo, Japan3095854/17/202556,884.5816300163001630016347 1/15/20251/15/20251549T&YM394027135Tokyo, Japan3049854/10/2025311,396.5427100271002710016306RO 1/11/20251/11/20251549T&YM394013885Tokyo, Japan3026854/6/202545,831.6413800138001380016283 1/1/20251/1/20251549T&YM394021046Tokyo, Japan3010853/27/202538,839.0121000210002100016267 1/20/20251/20/20251559Texas PartsF39217544Fort Worth, Texas - USA3077152/4/202556,411.6875007500750016334 1/20/20251/20/20251559Texas PartsF39217210Fort Worth, Texas - USA3076152/4/202536,128.2272007200720016333 1/18/20251/18/20251559Texas PartsF39216813Fort Worth, Texas - USA3067152/2/202545,790.9568006800680016324 1/17/20251/17/20251559Texas PartsF39217153Fort Worth, Texas - USA3058152/1/202546,079.8471007100710016315 1/22/20251/22/20251548V LogicL920419415Sinapore, Japan3088904/22/202538,736.3819400194001940016345 1/15/20251/15/20251548V LogicL920413374Sinapore, Japan3047904/15/202556,017.9013300133001330016304 1/13/20251/13/20251548V LogicL920413608Sinapore, Japan3039904/13/202556,123.2413600136001360016296 1/11/20251/11/20251548V LogicL920412940Sinapore, Japan3024904/11/202535,822.6012900129001290016281 1/3/20251/3/20251548V LogicL920413152Sinapore, Japan3008904/3/202555,917.9913100131001310016265