BUS1001 Assignment 1: Effective data analytics and visualisation using Excel Marks: 40 (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-S2-Ass1.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

Collusion is a form of cheating which occurs when people work together in a deceitful way to develop a submission for an assessment which has been restricted to individual effort.

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 BUS1001-S2-Ass1.xlsx workbook comprises four worksheets: Results, Task1, Task3 and Task4. ActiveYouth is an investment company that is specialized in sponsoring and supporting young individuals in their pro-athletic endeavors. To decide which future talents to invest in, they analyse past Olympic data. The Results worksheet is the main sheet containing outcome data of Olympic games from 2018 to 2022, and details of young participating athletes (born after 2000). Each row in the Results sheet includes outcome for each player in a particular game. Therefore, discipline title, event title and other details 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. (5 marks) Let's do some simple exploration.

1. Turn the data in the Results sheet into a table.
2. Write formulas including functions and references to answer the questions listed in Task1 sheet.

2. (5 marks) The manager wants to know the total points each country obtained so far for each discipline. Based on the given data in the Results sheet, let create a pivot table and chart to answer the question. Provide a filter to allow users to select a country of interest. Rename the new worksheet with the answer to Task2 Pivot. Write a short paragraph to describe the disciplines that Australian athletes are excelled. 3. (16 marks) Let prepare data for further analysis.

1. (2 marks) In the Task3 sheet: Write formulas including functions and references to fill the Country column with the name of the country this athlete represented in a game.
2. (2 marks) In the Task3 sheet: Write formulas including functions and references to fill the Discipline column with the name of the discipline they performed in.
3. (2 marks) In the Task3 sheet: Write formulas including functions and references to fill the AthletePoints column, which shows the total points they obtained so far.
4. (8 marks) Based on the data in Task3 sheet, create a pivot table and chart showing the number of athletes and the average athlete points of each country in the Freestyle Skiing discipline. Filter the top five countries with the highest average athlete points. Choose appropriate chart to make both measures visible on one chart. Rename the worksheet to Task3Pivot.
5. (2 marks) In the Task3Pivot sheet, create a text box and write a short paragraph (max 150 words) to describe your insight when comparing the athletic performance of these top five countries in the Freestyle Skiing discipline. Identify any interesting observations/patterns.

4. (14 marks) The manager wants to investigate the performance of two countries, USA and Japan. In a new worksheet: a. (2 marks) Extract or copy the Athlete Points data of these two countries from the Task3 sheet into Task4 sheet. Data from each country should be in one new column. (Hint: Apply filter and copy data of each group from the Task2 sheet into a new column in the new sheet). b. (8 marks) In the Task4 sheet, for each countrys athlete points data:

1. (1 mark) draw a histogram with 4 bins.
2. (1 mark) use the Data Analysis tool to display its descriptive statistics.
3. (2 marks) write a short paragraph (max 150 words) describing your insight about its data distribution.

c. (4 marks) In the Task4 sheet, draw boxplots to compare the performance distributions of the two countries. Hide outliers in the plots. Create a text box next to the chart and write a short paragraph (max 150 words) to describe your insight when comparing the performance data distributions of the two countries. 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 axes 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.

Attachments:

BUS1001-Ass1-....pdf

BUS1001-S2-As....xlsx