Using Functions And Formulas In Excel In Order To Answer Questions From Dataset

BUS2DAC - Data Analytics Concepts Assignment 2: Effective data analytics and visualisation using Excel Marks: 30 Assignment Type: Individual Overview Over the past few weeks, you have gained a general understanding of the use of spreadsheets in business. This essay assignment will provide you with an opportunity to demonstrate what you have learned through a spreadsheet file named DreamOffice.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 DreamOffice.xlsx workbook comprises of four worksheets, namely Order, Query, CustomerSale, and SalesRep. The Order worksheet is the main sheet containing the business data of the DreamOffice company from 2013 to 2016. Each row in the worksheet includes information of one line in a customer order. Therefore, an order ID may appear in multiple rows. http://www.latrobe.edu.au/students/academic-integrity http://www.latrobe.edu.au/students/academic-integrity Start by exploring the contents of the workbook to understand the meaning of the data, then apply data analytics to support decision making. Submit ONE Excel file, which includes all the answers for the following questions. 1. (6 marks) Let’s do some simple exploration. a. (2 mark) In the Order sheet: Turn the data into a table. Sort the table based on ascending order of Country and then Segment with a custom order of Corporate, Home Office, and then Consumer. b. (4 marks) In the Query sheet: Write formulas to answer the questions listed in the worksheet. 2. (12 marks) The company manager wants to get an insight about customers from different countries. a. (4 marks) In the CustomerSale sheet: Write formulas to: i. Fill the Customer Name column with the names recorded in the Order sheet. ii. Fill the SalePerCustomer column with the total sale amount for each customer based on the data in the Order sheet. (Hint: use SUMIF) b. (2 marks) In a new sheet: Create a pivot table and a pivot bar chart showing the total customers sales of the top ten countries. Apply appropriate fortmat and sorting to make the report comprehensive. c. (6 marks) The manager wants to learn more about Japan and Iran customers. In a new sheet: i. Extract the SalePerCustomer data of Japan and Iran customers into two columns. Use Data analysis tool to display the descriptive statistics of SalePerCustomer in each country. (Hint: Apply filter and copy data of each group from the CustomerSale sheet into a new column in the new sheet). ii. Draw boxplots to compare the sales data distributions of the two customer groups. Provide meaningful information and appropriate fortmat to make the visualisation comprehensive. iii. Write a short paragraph (max 150 words) to describe your insight about the two customer groups. 3. (12 marks) She wants to learn more about the performance of the sales representatives. a. (2 mark) In the Order sheet: Write a formula to fill the SalesEmp column with the name of the sale persons based on the Region they are in charge, which is recorded in the SalesRep sheet. (Hint: use XLOOKUP) b. (4 marks) Create a pivot table and an associate bar chart in a new sheet showing the total sales and total profits of each employee. Apply conditional formatting to highlight sales and profits values appropriately. Provide meaningful information and appropriate fortmat to make the visualisation comprehensive. c. (6 marks) She wants to have a closer look at the yearly performance of each sale person. i. Create a pivot table and pivot bar chart in a new sheet showing total sales of each sale person in each year. Provide meaningful information and appropriate fortmat to make the report comprehensive. ii. Create a slicer for sale person names. Show only the yearly performance of the three sale persons who names start with letter A. Write a short paragraph (less than 150 words) to describe your insight when comparing the performance of these three sale persons. 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. • You MUST show the formula to answer for the question that requires to write formlula. • For pivot tables and pivot charts, well-presented means o Having clear and meaningful titles, headers, labels, legends, and o Data is formatted appropriately. • For short answers (you should present below the graph in your Excel file), well-presented means comprehensive and compact.