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: