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

accounting

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:

 

 

High Credit Risk (A/R balance o/s over 90 days)

Medium Credit Risk (A/R balance o/s over 60 days)

Low Credit Risk (A/R balance o/s over 30 days)

Normal Credit Risk (A/R balance paid within 30 days)

High demand product

 

 

 

 

Medium demand product

 

 

 

 

Low demand product

 

 

 

 

 

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. 

Instruction Files

Related Questions in accounting category


Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.