You are part of an Internal Audit Team for Northwind Traders, Inc. As a member of the audit team, your assignment is to verify the following claims made by the client.
A. The Sales Revenue is $750,000 (as of December 31, 1997)
B. Cost of Sales is $472,142.
C. The Accounts Receivable is $85,000 (as of December 31, 1997)
D. The Allowance for bad debt is $62,000. (Any order shipped before October 1, 1997 but not paid yet by December 31, 1977 is assumed to be bad debt)
E. Total Sales commissions paid to employee for the fiscal year was $38,500
BACKGROUND INFORMATION
The underlying database for the accounting system is ODBC compliant. (Open Data Base Connectivity) made out of 8 tables.
You are given a copy of the database
The relationships between the tables can be seen in MS Access menu "Database Tools".
used by the accounting package in the Microsoft Access format
Even though the data covers a longer time period, we are interested in transactions that took place in 1997
THE DATA
- The physical count was done on December 31, 1997 and recorded accurately as "UnitsInStock" in the "Products" table
- There were 77 distinct products in 8 categories (See "Categories" and "Products" tables)
- The purchase price of each product is listed in "UnitCost" column.
- "ListPrice" in the products table is the current market price for each product.
- The firm's inventory valuation is based on "Lower of the Cost or Market Approach".
- Because of market structure, some products are discontinued. These products are indicated by "Discontinued (Y/N)" field in the products table. The firm expects to discard these items at half of their original cost.
- The firm keeps track of the minimum stock level. When the "UnitsInStock" falls below the "ReorderLevel", an order is issued to the related Supplier. Ordered items are already picked up by NorthWind's own delivery trucks on FOB origin bases. These items are listed as "UnitsinTransit" and will be paid in 30 days.
REQUIRED
Part I. Revenue Related
1) Verify the reliability of the numbers presented by the accounting department for
o Sales Revenue
o Cost of Sales
o Receivables
o Bad Debt
2) Determine the TOTAL of wages and sales commissions paid to the employees for the fiscal year 1997
Part II. Inventory Related (assume that the listprice is a good measure of the market value of a product, and the year end physical count results are given as UnitsInStock field in the Product table)
3) Determine the total value of the discontinued products (that will be sold at half of its original cost)
4) Determine the value of the items that should be valued at cost (unitcost < listprice)
5) Determine the value of the items that should be valued at market prices (i.e. ListPrice < unitcost)
6) Given the information above, determine the total value of the inventory for each category as of December 31, 1997.
Attachment:- Database.rar