Description

ACCT*1240: Applied Financial Accounting

Winter 2020

Professor Connie Zavitz

Data Analytics Assignment

Question 1 [20 marks]

NOTE: A version of Excel which supports macros and has the Analysis ToolPak installed is required.

Please download the “ACCT1240 Data Analytics Assignment - Question 1 (Winter 2020).xlsm” spreadsheet from Courselink. When you open this spreadsheet, please enable macros when prompted and follow the instructions on the “Instructions” sheet carefully.

Use the “Sheet for work” sheet for any rough work you perform. Do not erase your rough work. Please write clean final answers in the “Answers” sheet.

This spreadsheet contains information related to the sales and inventory of products in a fictional company for 2017.

Part (a) [5 marks]

Using the Filter tool in the data tab, identify any rows in the “DataAssign” sheet which have an error (or errors). Copy each row with an error into the appropriate space in “Answers” sheet, highlight the error (or errors) and write a short description of the error (or errors) for each row.

Remove each row with an error from the “DataAssign” sheet.

Parts (b), (c) and (d) which follow depend on the work that you have done in part (a).

Part (b) [4 marks]

Create an appropriate pivot table and determine the 5 best-selling products by sales volume (i.e. largest quantity of items sold).

Part (c) [4 marks]

Create an appropriate pivot table and determine the 5 best-selling products by total sales amount (i.e. largest total dollar amount gained).

Part (d) [7 marks]

Create an appropriate pivot table and determine the total sales by month. Use this pivot table to create an appropriate graphical visualization with this data. Display the graphic in the “Answer” sheet. Please ensure that your graphic has an appropriate title, axes, colours, etc.

Write a short sentence describing any trend you observe in the sales and provide a possible explanation for your observation.
[HINT: Examining the types of items being sold by this store might help with your explanation.]

Question 2 [10 marks]

Please download the “ACCT1240 Data Analytics Assignment - Question 2 (Winter 2020).xlsx” spreadsheet from Courselink for question 2.

Lube Co. supplies specialized lubricants to over 300 manufacturing and trucking customers in 22 Canadian regions. Lube sells 275 different products, some of which are more in demand the others. At the last C-suite meeting, the Chief Operating Officer and the CFO discussed stopping sales of high demand products to customers with balances outstanding for more than 90 days. The CFO has asked you to prepare a pivot table that shows the number of customers in 12 different categories:

The CFO also wants to “filter” this analysis by region. It is January 31, 2019. The CFO provides you with a file that shows customer number, region, product number account balance and the last payment date. The task is simplified in that each customer buys only one type of lubricant. The CFO has also provided you with a list that shows the “demand” for each of the 275 products.

Part (a) [4 marks]

Prepare a pivot table that meets the CFO’s requirements: Pretend that you are preparing this pivot table on January 31, 2019 when computing the number of days that the accounts receivable balance is outstanding. You can use functions in Excel to perform arithmetic operations with dates. Create a pivot table that resembles the table above in order to display the required information.

Part (b) [6 marks]

Create a pivot table which displays the accounts receivable by region. Prepare one visualization using this pivot table that will help the CFO better understand accounts receivable by region. Place this visualization below the pivot table and write a sentence summarizing the main insight gained below the visualization.