I have uploaded all the files needed

1 answer below »
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:
Answered 1 days AfterOct 08, 2023

Answer To: I have uploaded all the files needed

Atul answered on Oct 10 2023
13 Votes
Pivot
    Row Labels    Sum of Invoice Amount ($)
    China- Mexico     152176
    China- USA     143586
    Japan- China     133646
    Japan- USA     154021
    Mexico- China     168187
    Mexico- USA     170080
    South Africa- Mexico     1402
84
    South Africa- USA     153624
    USA- Japan     153932
    USA- South Africa    164826
    Grand Total    1534362
Pivot_PONumber
                        1. Data Risks:
    Row Labels    Count of PONumber                 - Inaccurate or incomplete data in the ShipLocation, InvoiceAmt, FlatDuty, TariffAmt, or PONumber fields.
     China     10                 - Missing data for some transactions, leading to underreporting.
     Japan     10
     Mexico     10                2. Analysis Risks:
     South Africa     10                 - Pivot table configuration errors may result in incorrect summarization.
     USA     10                 - Failure to account for potential outliers or anomalies in the data.
    Grand Total    50
                        Controls
                        3. Data Risk Controls:
                         - Regular data validation and cleaning processes to ensure accuracy and completeness.
                         - Implement data validation checks to identify and correct anomalies or missing values in the dataset.
                         - Audit and validate the data sources periodically to maintain data quality.
                        4. Analysis Risk Controls:
                         - Review and validate pivot table configurations by a second party to minimize errors.
                         - Conduct sensitivity analysis to identify and address any outliers or unusual patterns in the data that may affect the analysis results.
                         - Cross-verify the analysis results with manual calculations to ensure accuracy, especially when dealing with financial data like InvoiceAmt and FlatDuty.
                        These controls aim to mitigate the identified risks associated with data quality, pivot table configuration, and potential outliers during the analysis.
Dataset
    Vendors Data
     VendorID      VendorName      VendorLocation      PurchaseDate      InvoiceAmt      PONumber
    1     Vendor A      China      2023-01-15     5000     PO123
    2     Vendor B      Mexico      2023-02-20     3500     PO124
    3     Vendor C      USA      2023-03-10     6000     PO125
    4     Vendor D      Japan      2023-04-05     4500     PO126
    5     Vendor E      South Africa      2023-05-12     5200     PO127
    Countries Data
         CountryID      CountryName
        1     China
        2     Mexico
        3     USA
        4     Japan
        5     South Africa
        6    Egypt
        7    Israel
    Routes Data
         RouteID      StartLocation      EndLocation     Ship Location     Distance (km)...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here