EXCEL 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. See Exercise 2 from your document Using Microsoft Excel as Audit Software for information on generating working papers.
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

Additional Notes
Students are expected to submit a good quality audit memorandum, suitable for submission to the partner-in-charge. They should address each issue, referring to their findings (e.g. number of items, total book value, materiality of findings) and working papers.
Working papers should be consistently labelled, identifying the client, year-end, student name(s) and report title. Columns should be formatted (currency). Reports should be sorted in descending book value sequence where relevant.
Materiality is 5 per cent of the total balance. Students should comment on the materiality of their findings, individually and in combination.
The memorandum should end with a summary of findings and an overall conclusion.
If any fields have faulty data, comment on this and the controls in the application.

3102AFE Assignment Criteria Sheet
Student Names: …………………………………………………. Student Nos.: …………….
(Please write in block letters here.)
Criteria Unsatisfactory
Low Pass – Pass
Above Average
Excellent
Marks
Analysis and Judgment Provided information from the case.
No logical argument was presented leading to a satisfactory conclusion. Identified relevant information from the case.
There was some evidence of logical argument leading to a conclusion. Identified and analysed relevant information from the case.
There was good evidence of logical argument leading to and supporting a conclusion. Relevant information from the case was identified and analysed comprehensively.
Strong evidence was shown of a logical argument that led to and supported a conclusion. /6
Communication Failed to submit an audit memorandum.
Wrote in an unclear and unstructured manner with unsatisfactory spelling, punctuation, and grammar.
Did not acknowledge sources. Submitted an audit memorandum and wrote in a clear and structured manner with satisfactory spelling, punctuation, and grammar.
Acknowledged sources. Submitted an audit memorandum with Introduction, appropriate sections, and overall summary and conclusions. Wrote in a clear, logical and structured manner with good spelling, punctuation, grammar and referencing. Submitted an audit memorandum with Introduction, appropriate sections, and overall summary and conclusions.
Wrote in a clear, logical and structured manner with correct spelling, punctuation, grammar and referencing. /4
Technology Application Demonstrated little competence in applying ACL software to produce professional quality, consistently presented and correct output to meet project objectives. Demonstrated some competence in applying ACL software to produce professional quality, consistently presented and correct output to meet project objectives. Demonstrated good competence in applying ACL software to produce professional quality, consistently presented and correct output to meet project objectives. Demonstrated a high level of competence in applying ACL software to produce professional quality, consistently presented and correct output to meet project objectives. /10
Total Marks /20