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: 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 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 Field Name in Database InvoiceNO InvoiceAmt ShipDate InvoiceDate VendorID VendorName ProductID UnitCost ShipCost FlatDuty Tariffamt ShipLocation QualityRate PaymentTerms ShipTerms PayAddress PONumber PODate ReceivingNumber ReceivingDate QtyReceived QtyPurchased QtyInvoice Approved 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. 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. Tax Accounting: Plan International Duty Cost Analysis The owners want to understand the total dollar amount of customs duty they will owe the U.S. government for the year. They have asked you to perform descriptive analytics to understand the purchases from vendors located in each country. Your company makes purchases from vendors located in several countries, such as China, Mexico, the United States, Japan, South Africa, Israel, Greece, and Egypt. You and your team have designed a data analysis strategy. You have been asked to consider the risks and related controls in the development of that strategy. Complete the following chart. Click here to view the Word Template. Download the Word template. Upload your final Word file with the table completed. PAC 4.5 Tax Accounting For the data analysis project objective of describing the total amount of purchases and custom duties paid to vendors located in different countries, complete the below table. Strategies Risks Controls Data strategy: ShipLocation InvoiceAmt FlatDuty TariffAmt PONumber Analysis strategy: Use a pivot table to drop the ShipLocation into the rows and the sum of the InvoiceAmt into values. Use a pivot table to drop the ShipLocation into the rows and the count of PONumber into Values 1. Data Risks: 2. Analysis Risks: 3. Data Risk Controls: 4. Analysis Risk Controls: Objective and Questions Data and Analysis Strategies Risks Controls Objective: Understand the purchases made from each country. Data strategy: ShipLocation, InvoiceAmt, FlatDuty, TariffAmt, PONumber 1. Data: 3. Data: Questions: What dollar amount of purchases are made from each country? How many purchases were made from vendors in each country? Analysis strategy: Use a pivot table to drop the ShipLocation into the rows and the sum of InvoiceAmt into values. Use a pivot table to drop the ShipLocation into the rows and count of PONumber into values. 2. Analysis: 4. Analysis: