Assignment
REQUIREMENTS
You are a member of a team involved in the audit of inventories of ABC Distribution Company. A computer-based inventory system is used by the company. You are provided with two data tables.
INVENT Inventory Master File as at 31 December 2015. (Refer to Appendix 1)
STKTAKE Inventory Stocktake File as at 31 December 2015.
INVENT contains the inventory records as at 31 December 2015, after all transactions have been posted. On 31 December 2015, a physical count of stock was taken ('stocktake') to determine actual stock on hand in the warehouse. STKTAKE will be used to update the inventory master file and report necessary adjustments. This means that stocktake figures will replace recorded quantities. Adjustments to book values will be written off against profit as part of Cost of Goods Sold.
You have been asked to use EXCEL as audit software to analyse the INVENT and STKTAKE tables, and provide information to the audit team on issues to be taken into account when planning the audit. You are to produce an audit memorandum to the partner-in-charge dealing with each of the issues on the next page. Your memorandum should cover each issue and include (where appropriate) a summary of findings similar to:
|
Issue
|
Audit Objective
|
|
Number of items
|
|
|
Total book value
|
|
|
% of population
|
|
|
Materiality
|
|
For each issue, comment on what you found, its materiality, its implications for the client and the audit, and controls that may need review. In most cases, you will be recommending further investigation to resolve matters.You should support your findings with appropriately labelled and totalled working papers in PDF format.
Report headers should identify the client, the year-end, your names and the title of the working paper. All reports should be sorted in a manner which focuses attention on the higher book value items. Submit your working papers along with the accompanying your memorandum.
You may assume that materiality is 5 per cent of the current inventory balance.
1. Stocktake Results
(a) Details and values of products not counted at the stocktake.
(b) Products with significant adjustments, ie. adjustments (positive/negative) in excess of $150. (The adjustment is calculated as BOOK - [COUNTQTY * AVCOST]).
(c) Total value of adjustments to inventory book value.
2. Potentially Obsolete Inventory
Consider:
(a) Products selling below average cost.
(b) Products with zero selling price.
(c) Products with excessive holdings (over 6 months' sales). Use QTY not COUNTQTY.
(d) Products that have not moved in the past 6 months.
(e) Total value of potentially obsolete inventory (produce a consolidated report with no duplicates indicating which of the above criteria were met for each product).
3. Valuation of Inventory
Consider:
(a) Products where book value differs from book quantity on hand times average cost.
(b) Total value of inventory counted at average cost and standard cost.
4. Other Issues
Consider:
(a) A summary of the different product groups in terms of quantity on hand and book values.
(b) The range of book values and the distribution of book values in the master file, including the number of products falling into particular intervals (i.e. Stratification).
APPENDIX 1
INVENT (Inventory Master File)
Record Layout
|
Field
|
Description
|
Name
|
Length
|
Type
|
Decimals
|
|
1
|
Product Number
|
PRODNO1
|
5
|
TEXT
|
|
|
2
|
Description
|
DESC
|
11
|
TEXT
|
|
|
3
|
Quantity on Hand
|
QTY
|
11
|
GENERAL
|
0
|
|
4
|
Average Cost
|
AVCOST
|
8
|
GENERAL
|
2
|
|
5
|
Book Value
|
BOOK
|
9
|
GENERAL
|
2
|
|
6
|
Annual Usage
|
ANNUAL
|
11
|
GENERAL
|
0
|
|
7
|
Product Group
|
GROUP
|
2
|
TEXT
|
|
|
8
|
Last Moved (YYYYMM)
|
LASTMVD
|
6
|
GENERAL
|
0
|
|
9
|
Standard Cost
|
STDCOST
|
8
|
GENERAL
|
2
|
|
10
|
Selling Price
|
SELLPRICE
|
8
|
GENERAL
|
2
|
STKTAKE (Inventory Stocktake File)
Record Layout
|
Field
|
Description
|
Name
|
Length
|
Type
|
Decimals
|
|
1
|
Product Number
|
PRODNO2
|
5
|
TEXT
|
|
|
2
|
Count Quantity
|
COUNTQTY
|
11
|
GENERAL
|
0
|