Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

I have uploaded all the files needed

1 answer below »
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:
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
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
Field Name in Database
InvoiceNO
InvoiceAmt
ShipDate
InvoiceDate
VendorID
VendorName
ProductID
UnitCost
ShipCost
FlatDuty
Tariffamt
ShipLocation
QualityRate
PaymentTerms
ShipTerms
PayAddress
PONumbe
PODate
ReceivingNumbe
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
PONumbe
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, PONumbe
    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 After Oct 08, 2023

Solution

Atul answered on Oct 10 2023
27 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     140284
    South Africa- USA     153624
    USA- Japan     153932
    USA- South Africa    164826
    Grand Total    1534362
Pivot_PONumbe
                        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 unde
eporting.
     Japan     10
     Mexico     10                2. Analysis Risks:
     South Africa     10                 - Pivot table configuration e
ors may result in inco
ect 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 co
ect 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 e
ors.
                         - 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