BUS1001 - Effective data analytics and visualisation using Excel

Assignment Task

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.

1. Let's do some simple exploration.

a. Turn the data in the Results sheet into a table.

b. Write formulas including functions and references to answer the questions listed in Task1 sheet.

2. 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. Let prepare data for further analysis.

a. Write formulas including functions and references to fill the Country column with the name of the country this athlete represented in a game.

b. Write formulas including functions and references to fill the Discipline column with the name of the discipline they performed in

c. Write formulas including functions and references to fill the AthletePoints column, which shows the total points they obtained so far.

d. 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.

e. Create a text box and write a short paragraph 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. The manager wants to investigate the performance of two countries, USA and Japan. In a new worksheet:

a. 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. In the Task4 sheet, for each country’s athlete points data:

i. Draw a histogram with 4 bins.

ii. Use the Data Analysis tool to display its descriptive statistics.

iii. Write a short paragraph describing your insight about its data distribution.

c. 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 to describe your insight when comparing the performance data distributions of the two countries.

This BUS1001- Data Analysis has been solved by our PhD Experts at My Uni Paper.