BUS1001

Assignment 1: Effective data analytics and visualisation using Excel

Marks: 50 (Equivalent to 15% 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 BUS1001-Ass1.xlsx.

Assignment Requirements

The given BUS1001-Ass1.xlsx workbook comprises six worksheets: Data Dictionary, Order, City, Query, Sales 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. Note that the total sales we mention here is calculated based on selling price and not include the shipping fee.

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.   (4 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.   (16 marks) The company manager wants to get an insight into the sales performance of different states.

a.   (2 marks) In the Sales sheet: Write formulas to fill the Customer City column with the customer city of the corresponding customer.

b.   (2 marks) In the Sales sheet: Write formulas to fill the Customer State using the information provided in the City worksheet

c.   (2 marks) In the Sales sheet: Write formulas to fill the Total Sales column from each customer.

d.   (8 marks) In a new worksheet: Create a pivot table and chart showing the number of customers and total sales of each state. Filter to the bottom five states that have the least total sales only. Choose appropriate chart to make both measures visible  on one chart.

e.   (2 marks) Write a short paragraph (max 150 words) to describe your insight when comparing sales performance of these five states. Identify any interesting observations/patterns.

3.   ( 14 marks) The manager wants to investigate further into the performance of two states, Bahia and Rio de janeiro. In a new worksheet:

a.   (2 marks) Extract or copy the Total Sales data of these two states from the Sales sheet into two columns, one for each state. (Hint: Apply filter and copy data of each group from the Sales sheet into a new column in the new sheet).

b.   (8 marks) For each state’s total sale data:

i.   (1 mark) draw a histogram.

ii.  (1 mark) use the Data Analysis tool to display its descriptive statistics.

iii. (2 marks) write a short paragraph (max 150 words) describing your insight about its data distribution.

c.   (2 marks) Draw boxplots to compare the sales distributions of the two states. Hide outliers in the plots.

d.   (2 marks) Write a short paragraph (max 150 words) to describe your insight when comparing the sales data distributions of the two states.