Metaphor Industries: Assessing Inventory Risks and Planning Inventory Observation
Answer all parts of these requirements on this page through page 7-27. It is more convenient to complete the assignment if you remove pages 7-25 and 7-27 now. For each requirement, include the command you used, where applicable, and the correct answer or exception(s) found. For requirements that require a printout include the assignment number on the file you hand in to the instructor.
You are assessing risks and developing the inventory observation plan for inventory and are working with the table called Metaphor_Inventory_2014. This table contains records for all inventory items held at December 31, 2014. The assistant on the audit has already checked the validity of the table data by executing the Verify, Count, and Total commands and comparing the record count and control totals to information received from the IT department.
The headings on the Metaphor-Inventory-2014 table are not sufficiently clear for your needs. In Table Layout, in the Alternative Column Title, change SALEPR to Unit Selling Price and VALUE! to Value at Cost. The other headings are adequate.
Complete the following requirements using ACL:
Q 1. Metaphor has several inventory locations. The observation plan is to observe inventory at the two locations with the largest dollar cost, and a third location selected from the remaining locations. Last year the third location tested was location 06. Indicate the following information:
a. Number of inventory locations.
b. Determine the information below for the two locations with the largest dollar cost:
(1) Location codes ______________________________________________
(2) Total cost of each ____________________________________________
(3) Total market value of each ______________________________________
c. Indicate the third location you have chosen for testing. Explain your reason for choosing this location.
d. Print out a table summarizing (1) location code; (2) number of inventory items; (3) total cost.
Q 2. Determine the amount that the total market value of the inventory exceeds total cost.
Q 3. Prepare a list of all inventory items where total cost exceeds market value. The printout should include the inventory part number, total cost of each item, market value, excess of cost over market value, and dollar totals for these fields.
Q 4. Do a Cross-tabulation of LOCATION and PRODSTAT (product status) fields. Select the two location/product status combinations with the highest dollar total cost and prepare a printout of all inventory items over $10,000.