BUS1001
Assignment 1: Effective data analytics and visualisation using Excel
Marks: 60 (Equivalent to 25% of the final grade)
Assignment Type: Individual
Overview
Over the past few weeks, you have gained a general understanding of using spreadsheets in business. This assignment will allow you to demonstrate what you learned through a spreadsheet file named BeCommerce.xlsx.
Academic Integrity
Plagiarism occurs when you use words, ideas, or work products attributable to another identifiable person or source:
• without attributing the work to the source from which it was obtained
• in a situation in which there is a legitimate expectation of original authorship
• in order to obtain some benefit, credit, or gain which need not be monetary
Self-plagiarism refers to the re-submission of work as if it were original. You may not submit your own academic work for assessment when it has already been submitted for assessment at another time (including at another institution), without the express permission of the academic staff member who will assess it.
By submitting* this piece of work and signing this document, I declare that:
1. The work is my own work.
2. I have not previously submitted all or part of this work for assessment in any subject, unless the subject coordinator for the current subject (or my research supervisor, if applicable) has given me written permission to reuse specific material and I have correctly referenced the material taken from my own earlier work.
3. I have read and agree to be bound by the Statutes, Regulations and Policies of the University relating to Academic Integrity available at http://www.latrobe.edu.au/students/academic-integrity; and
4. I may be subject to student discipline processes in the event of an act of academic misconduct by me including an act of plagiarism or cheating.
I further grant to the University or any third party authorised by the University
(www.latrobe.edu.au/text-match) the right to reproduce and/or communicate (make available online or electronically transmit) the work I have submitted for the purpose of detecting plagiarism.
Assignment Requirements
The given BeCommerce.xlsx workbook comprises five worksheets: Data Dictionary, Order, Query, CustomerSales and ProductCategory.
The Order worksheet is the main sheet containing the business data of a Brazilian E-commerce company from 2016 to 2018. Each row in the worksheet includes information on one line in a customer order. Therefore, an order ID may appear in multiple rows.
Start by exploring the workbook’s contents to understand the meaning of the data, then apply data analytics to support decision-making.
Submit ONE Excel file, which includes all the answers to the following questions.
1. (10 marks) Let’s do some simple exploration. Turn the data in the Order sheet into a table and write formulas to answer the questions listed in the Query worksheet.
2. (14 marks) The company manager wants to get an insight into the sales performance of their sales teams.
a. (2 marks) In the Sales sheet: Write formulas to fill the Total Quantity column with the number of items sold by each seller.
b. (2 marks) In the Sales sheet: Write formulas to fill the Total Sales column with each seller’s total sales revenue (not including the shipping fee).
c. (8 marks) In a new worksheet: Create a pivot table and a pivot bar chart showing each sales team’s total quantity and revenue. Use an appropriate chart to make total quantity and sales revenue visible.
d. (2 marks) Write a short paragraph (max 150 words) to compare teams’ sales performance in terms of total quantity and sales revenue. Identify any interesting observations/patterns.
3. (20 marks) The manager wants to investigate further into the performance of the sales teams. In a new worksheet:
a. (4 marks) Extract or copy the Total Sales data of teams 1 and 3 from the Sales sheet into two columns. Use the Data Analysis tool to display the descriptive statistics of the Total Sales of these two teams. (Hint: Apply filter and copy data of each group from the Sales sheet into a new column in the new sheet).
b. (12 marks) Apply descriptive analysis and draw a histogram on each team’s sales data. Write one short paragraph (max 150 words) describing your insight about each team’s sales data distribution.
c. (4 marks) Draw boxplots to compare the sales distributions of the two teams. Hide outliers in the plots and write a short paragraph (max 150 words) to compare the two teams’ sales data distributions. Identify any interesting observations/patterns.
4. (16 marks) The manager wants to learn more about the sales performance of each product category.
a. (2 marks) In the Order sheet: You can find that the Product Category column is shown in Portuguese, so to help us understand the data for analysis, write a formula to fill the Product Category English column using the information provided in the ProductCategory sheet.
b. (10 marks) She wants to have a closer look at different product categories. You are required to create a pivot table and chart in a new sheet showing monthly total sales and total items sold for a selected product category from a filter. Provide a reasonable argument in choosing your chart type.
c. (4 marks) Filter the results of question 4.b to show the toys category only. Take a
screenshot of the chart and paste it below question 4.b’s chart. Then write a short paragraph (max 150 words) to describe your insight about sales of toys over time. Identify any interesting observations/patterns.
Submission Guide
Answers to all questions are presented in an Excel file, including the data. For answers written in short paragraphs, create a textbox next to your visualisation to write your answers.
Marking rubrics
The marker in assessing your work will use the following marking guide. Please have a look to understand what you need to cover for each question in this assignment.
• Full marks for the correct and well-presented answer. Half of the mark for something close.
• To answer questions that require writing a formula, you MUST have the formula as the answer. No mark will be given without the formula.
• For all visualisations (tables and charts), well-presented means o Having clear and meaningful titles, headers, labels, legends, and o Data is formatted according to their types.
o Tables and charts are formatted nicely to see the pattern and support understanding the insights immediately.
• For short answers, well-presented means visible, comprehensive, and compact.
