Effective data analytics and visualisation using Excel

Assignment 1: 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.

Academic Integrity

By submitting* this piece of work and signing this document, I declare that:

1. Thework is my own 
2. I have not previously submitted all or part of this work for assessment in any subject, unlessthe 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 theUniversity 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 academicmisconduct 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 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. (4marks) Let's do some simple  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 ofdifferent 

• (2marks) In the Sales sheet: Write formulas to fill the Customer City column with the customer city of the corresponding 
• (2marks) In the Sales sheet: Write formulas to fill the Customer State using the information provided in the City worksheet
• (2marks) In the Sales sheet: Write formulas to fill the Total Sales column from each customer.
• (8 marks) In a new worksheet: Create a pivot table and chart showing the numberof 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.
• (2 marks) Write a short paragraph (max 150 words) to describe your insight whencomparing sales performance of these five states. Identify any interesting observations/patterns.

3. (14 marks) The manager wants to investigate further into the performance of twostates, Bahia and Rio de janeiro. In a new worksheet:
   • (2 marks) Extract or copy the Total Sales data of these two states from the Salessheet 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).
   • (8marks) For each state’s total sale data:
     1. (1mark) draw a 
     2. (1mark) use the Data Analysis tool to display its descriptive 

• (2 marks) write a short paragraph (max 150 words) describing your insightabout its data distribution.

1. (2 marks) Draw boxplots to compare the sales distributions of the two states. Hideoutliers in the plots.
2. (2 marks) Write a short paragraph (max 150 words) to describe your insight whencomparing the sales data distributions of the two states.

4. (16marks) The manager wants to get an insight into the sales performance of the watches gifts 

1. (2 marks) In the Order sheet: You can find that the Product Category column isshown 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 
2. (10 marks) Create a pivot table and chart in a new sheet showing the annualsales and total number of sold items of the watches gifts category. Choose appropriate chart to make both measures visible on the 
3. (4 marks) Show the trend line (or regression model) and its equation on Q 4.b’s Use the equation to forecast the annual sales of the next three years (2019- 2021). How accurate is the created regression model? Based on which indicator?

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.

• Fullmarks for the correct and well-presented  Half of the mark for something close.
• Toanswer questions that require writing a formula, you MUST have the formula as the  No mark will be given without the formula.
• Forall visualisations (tables and charts), well-presented means
  • Havingclear and meaningful titles, headers, labels, legends, and
  • Datais formatted according to their 
  • Tables and charts are formatted nicely to see the pattern and support understandingthe insights immediately.
• Forshort answers, well-presented means visible, comprehensive, and