ACCT20081 - Financial Data Analytics

REQUIREMENTS You are a member of an audit team of ABC Distribution Company. A computer-based inventory system is used by the company. You are provided with two data tables as follows: INVENT2019.txt Inventory Table as at 31 December 2019. (Refer to Appendix 1) STKTAKE.txt Stocktake Table as at 31 December 2019. INVENT2019 is a copy of the inventory records as at 31 December 2019, after all transactions have been posted. On 31 December 2019, 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 records 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 SAS software to analyse the INVENT2019 and STKTAKE tables (see Appendix 1 for SAS code to input these tables), and provide information to management about the accuracy and completeness of the stocktake, potential obsolescence of the stock and its saleability, and characteristics of these goods. You are to produce a management report with sections dealing with each of the issues on the next page. Your report should cover each issue and include (where appropriate) a summary of findings similar to: Issue Stocktake Completeness Number of items Book value of these items % of total book value of all items; 5% is considered significant For each issue, comment on what you found, its significance, its implications for the company and areas 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 reports generated with SAS. See Appendix 2 for information on generating reports. SAS report headers should identify the company, the year-end, your name and the title of the report. All SAS reports should be sorted in a manner which focuses attention on the higher book value items. The maximum word length for the management report is 2000 words. Descriptive Analysis of Inventory Records – INVENT2019 Minimum, maximum and average book value, and number of products, and total book value for each product group. Classify products into ‘High’ (book value > 2500), ‘Medium’ (> 1000 and <= 2500) or ‘Low’ (<= 1000) ranges. Produce a report showing, for each product group, the minimum, maximum and average book value, and number of products, and total book value, for each High/Medium/Low class. Stocktake Results – INVENT2019 v STKTAKE (a) Provide details and values of products not counted at the stocktake. NB. when joining the tables, products not counted will have a missing (.) value for COUNTQTY. Use another variable (COUNTED) to have a value of zero (0) in such cases, and otherwise the value of COUNTQTY. (b) Produce a report of significant adjustments, i.e. adjustments (positive/negative) in excess of $150. (The adjustment is calculated as BOOK – [COUNTED * AVCOST]). Total value of all adjustments to inventory book value. Potentially Obsolete Stock Reports of: (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) All products that are potentially obsolete (based on a-d), indicating which criteria are met, with separate report for each product group, showing subtotals and grand total. Valuation of Inventory Reports of: (a) Products where book value differs from book quantity times average cost by more than 50 cents. (b) Products where average cost exceeds standard cost. APPENDIX 1 INVENT2019.txt (Inventory Table) Record Layout Field Description Name Length Type Decimals 1 Product Number PRODNO1 5 ASCII 2 Description DESC 11 ASCII 3 Quantity on Hand QTY 11 NUMERIC 0 4 Average Cost AVCOST 8 NUMERIC 2 5 Book Value BOOK 9 NUMERIC 2 6 Annual Usage ANNUAL 11 NUMERIC 0 7 Product Group GROUP 2 ASCII 8 Last Moved (YYYYMM) LASTMVD 6 ASCII 9 Standard Cost STDCOST 8 NUMERIC 2 10 Selling Price SELLPRICE 8 NUMERIC 2 STKTAKE.txt (Stocktake Table) Record Layout Field Description Name Length Type Decimals 1 Product Number PRODNO2 5 ASCII 2 Count Quantity COUNTQTY 11 NUMERIC 0 data work.inventry; infile "&path/PB_Data/INVENT2019.txt"; input @1 prodno1 $5. @6 desc $11. @17 qty 11. @28 avcost 8.2 @36 book 9.2 @45 annual 11.0 @56 group $2. @58 lastmvd 7.0 @65 stdcost 8.2 @74 price 7.2 ; run; data work.stktake; infile "&path/PB_Data/STKTAKE.txt"; input @1 prodno2 $5. @6 countqty 11. ; run; APPENDIX 2 Assignment Submission Requirements This assignment requires students to produce a management report addressing specified issues. This management report should be produced as an MS Word document. SAS reports should accompany this management report. These reports should be .PDF files with filenames (reference numbers) corresponding to each issue. The management report, .PDF files and SAS programs should be submitted zipped together as one file, along with an assignment cover sheet. File naming convention: Management report, SAS reports and SAS programs: familyname_student_id_management report.docx familyname_student_id_Q1.pdf etc. familyname_student_id_Q1.SAS etc. The SAS reports should be output in each case to a .PDF file (use the reference number as the filename, e.g. Q1). When using SAS Studio, you can use the RESULTS TAB and PRINT RESULTS option, but print to PDF. If this option doesn’t work, use DOWNLOAD RESULTS AS AN .HTML FILE, open and then print to a .PDF. Use a consistent layout for your SAS reports: Header: ABC Distribution Company Year End: 31 December 2019 Inventory Review Report Q2a: Stock not Counted Footer: Unit Code, Student Number, your name Additional Notes Students are expected to submit a good quality management report, suitable for submission to the manager. They should address each issue, referring to their findings (e.g. number of items, total book value, significance of findings) and reports. Reports should be consistently labelled, identifying the client, year-end, student identification and report title. Columns should be formatted (currency). Reports should be sorted in descending book value sequence. Materiality is 5 per cent of the total balance. Students should comment on the significance of their findings, individually and in combination. The management report should end with a summary of findings and an overall conclusion. If any fields have faulty data, comment on this and the apparent effectiveness of controls in the application.