ACCT20081 Practical Assessment

REQUIREMENTS

You are a member of an audit team of ABC Distribution Company. A computer-based sales system is used by the company. You are provided with three SAS data tables as follows:

Orion.order_fact Table of customer orders (1 record per product). (Refer to Appendix 1)

Orion.customer_dim Table of customer details

Orion.product_list Table of products sold by the company.

You have been asked to use SAS software to analyse these SAS tables, and provide information to management about product sales and customer orders. You are to produce a management report with sections dealing with each of the issues on the next page.

For each issue, comment on what you found, its significance, its implications for the company and areas that may need addressing.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 and footers should identify the company, your name and student number, and the title of the report. All SAS reports should be sorted in a manner which focuses attention on the higher value items.

You are provided with some SAS templates to get you started. The first one (Orders Project Data Prep.sas) you should use to prepare the data from the tables. This SAS code includes the correction of a data error in the orion.customer_dim table (creating a new table [mycustomers] for the joins). It also performs the join of the 3 source tables to create work.orders_cust_prod. The new dataset work.orders is generated with the opportunity to add new columns as needed. NB. Use the new column name customername for your reports. The second template demonstrates production of a report (Orders Project Sample Report.sas). A copy of these templates is provided in Appendix 3.

The maximum word length for the management report .

1. Product and Customer Data Analytics

1. Explain useful applications of data analytics which can be applied to products. How do they benefit management?
2. Explain useful applications focused on customers' buying patterns and demographics. How do they benefit management?

2. Product Sales Analysis

1. Report number of orders, and total quantity sold by product in descending quantity order, with grand totals (and total number of distinct products). Include product name and supplier ID.
2. Report number of orders, and total sales value by product in descending value order, with grand totals.
3. Report the top 10 products based on sales value, and report products without sales in descending sales value.

3. Customer Sales Analysis

NB The customer record for CUSTOMER_ID 19 contains invalid data, preventing production of these reports. We use work.mycustomers instead, with the new customername column.

1. Report total number of orders and total sales value by customer, in descending value order. Include customer name and customer group.
2. Stratify customers with orders into 5 ranges by total sales value, and produce the above customer order and sales report for each range. Include subtotals and grand totals. Ignore customers that are dormant (see below).
3. Produce the order and sales report for Orion Gold Club Members in descending value order.
4. Report the top 10 customers based on sales value, and report the dormant customers in customer_id order within customer_group.

4. Product Profitability Analysis

Calculate unit sale price and gross margin per unit.

Report the orders for the top product, showing for each order:

Order_ID

Product_ID

Quantity

Total_Retail_Price

Costprice_Per_Unit

Saleprice_Per_Unit

Total_Order_Cost

Gross_Margin (Total_Retail_Price - Total_Order_Cost)

Gross_Margin/Total_Retail_Price

Show totals for Quantity, Total_Retail_Price, Total_Order_Cost, Gross_Margin.

PS. Can you reconcile the number of records in the 3 tables??

APPENDIX 1

Customer Orders File Specs

ORION.ORDER_FACT

Field	Name	Type	Format	Notes
1	Customer_ID	Numeric	12.	Link to CUSTOMER_DIM
2	Employee_ID	Numeric	12.
3	Street_ID	Numeric	12.
4	Order_Date	Numeric	Date9.
5	Delivery_Date	Numeric	Date9.
6	Order_ID	Numeric	12.
7	Order_Type	Numeric
8	Product_ID	Numeric	12.	Link to PRODUCT_LIST
9	Quantity	Numeric
10	Total_Retail_Price	Numeric	Dollar13.2	Total sales value of order
11	CostPrice_Per_Unit	Numeric	Dollar13.2
12	Discount	Numeric	Percent

ORION.CUSTOMER_DIM

Field	Name	Type	Format	Notes
1	Customer_ID	Numeric	12.
2	Customer_Country	Char
3	Customer_Gender	Char
4	Customer_Name	Char		NB. Data error customer 19
5	Customer_FirstName	Char
6	Customer_LastName	Char
7	Customer_BirthDate	Numeric	Date9.
8	Customer_Age_Group	Char
9	Customer_Type	Char
10	Customer_Group	Char
11	Customer_Age	Numeric

ORION.PRODUCT_LIST

Field	Name	Type	Format	Notes
1	Product_ID	Numeric	12.
2	Product_Name	Char
3	Supplier_ID	Numeric	12.
4	Product_Level	Numeric	12.
5	Product_Ref_ID	Numeric	12.

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_Q1a.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. Q1a). 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

Sales Orders Analysis

Report Q1a: Product Quantities Sold

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 value, significance of findings) and SAS reports. Be specific about items of interest.

Reports should be consistently labelled, identifying the client, year-end, student identification and report title. Currency columns should be formatted (dollar13.2). Reports should be sorted in descending value sequence.

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.

APPENDIX 3

************************************************;

* Program: Orders Project Data Prep.sas *;

************************************************;

* Join Order_Fact with Customer_Dim ;

* Left-join with Order_Fact as Primary ;

* Excludes customers without orders ;

* Orion.Customer_Dim has a faulty record for Customer_ID 19 ;

* We will take a copy of Orion.Customer_Dim, and fix the error ;

* Our project will use work.mycustomers instead with a new customername column ;

data work.mycustomers ;

set orion.customer_dim ;

select;

when (customer_ID = 19) customername = 'Peter J. Best' ;

otherwise customername = customer_name ;

end;

label customername = 'Customer Name' ;

run ;

proc sql;

create table orders_cust as

select o.*, c.customername, c.customer_age_group, c.customer_type,

c.customer_group

from orion.order_fact as o

left join work.mycustomers as c

on o.customer_ID = c.customer_ID

order by o.customer_ID, o.order_ID;

quit;

* Now join Orders_Cust with Product_List ;

proc sql;

create table orders_cust_prod as

select r.*, p.product_name, p.supplier_ID

from orders_cust as r

left join orion.product_list as p

on r.product_ID = p.product_ID

order by r.customer_ID, r.order_ID;

quit;

* Specify any additional fields needed ;

* Specify any field labels ;

* Specify any formatting, e.g. dollar13.2 ;

data work.orders ;

set orders_cust_prod ;

saleprice_per_unit = total_retail_price / quantity ;

gross_margin_per_unit = saleprice_per_unit - costprice_per_unit ;

format saleprice_per_unit gross_margin_per_unit dollar13.2 ;

run ;

* Note: You may need to create further tables from this source data ;

***************************************************;

* Program: Orders Project Sample Report.sas *;

***************************************************;

* Get data for your report ;

* Create a new table ;

proc sql;

create table report1 as

select product_ID, product_name, supplier_ID, sum(quantity) as sumqty label 'Sum Qty'

from orders

group by product_ID, product_name, supplier_ID

order by sumqty descending;

quit;

* Print Report 1 ;

proc print data=report1 noobs label n ;

title3 'ABC Distribution Company' ;

title4 'Sales Orders Analyses' ;

title7 'Report Q1a: xxxxx' ;

footnote5 'ACCT20081, Student Number, your name' ;

* by ;

* where ;

var ;

sum ;

format;

run;

Criteria Sheet

Student Name: ..........................................................Student No.:................

(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 a management report. Wrote in an unclear and unstructured manner with unsatisfactory spelling, punctuation, and grammar. Did not acknowledge sources.	Submitted a management report and wrote in a clear and structured manner with satisfactory spelling, punctuation, and grammar. Acknowledged sources.	Submitted a management report 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 a management report 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 SAS software to produce professional quality, consistently presented and correct output to meet project objectives.	Demonstrated some competence in applying SAS software to produce professional quality, consistently presented and correct output to meet project objectives.	Demonstrated good competence in applying SAS software to produce professional quality, consistently presented and correct output to meet project objectives.	Demonstrated a high level of competence in applying SAS software to produce professional quality, consistently presented and correct output to meet project objectives.	/10
Total Marks					/20