You have recently transferred into the role of Senior data analyst in Marine Review Ltd. You are stationed at
their head office in the Gold Coast. Marine had appointed twelve teams from its marine researcher division to
conduct sightings of whales and sharks along the Queensland coast line. This data was collected between the
years 2001 and 2016. The data is fundamental to a study being conducted by the researchers in the company in
conjunction with researchers from the local government and an international company funding the project. An
important team goal is to write and publish newfound information on the various species’ migration and
habitation, in a renowned journal publication.
The Chair of the project was recently replaced after the resignation of the former incumbent of the position. The
new Chair wants to review the data collected thus far, as this will assist her in deciding on whether the
information collected is sufficient. While this would have otherwise been a job for your boss, you had indicated
during a previous supervision session that you are keen to take on higher responsibilities, in a bid to pave way
for a promotion. As your boss in travelling abroad, he thought it an opportune time for you to complete the
requested task with the assistance of the office clerk (this role in the current scenario will be played by Hayley
Keagan or someone she appoints).
2
You have managed to extract the sighting data files from the company’s electronic data sheets and have put
these in an excel file (Assignment 1 Excel file.xlsx). You decide that using Excel is the best way for you to present
the data. You are confident that the use of an Excel table will allow you to provide an appropriate interactive
dashboard, various pivot tables, charts, filters and slicers when presenting your information to the Chair.
__________________________________________________________________________________
Requirements:
1. Using the dataset provided and AT LEAST 3 charts, 3 pivot tables, one filter and 4 slicers, create a
DASHBOARD that summarises the sighting of whales and sharks data year on year for the Chair. This
submission makes up 17 of your 20 marks for Assessment 1.
2. You will need to convert the dataset to an Excel table and then do some work on it for the analysis:
a. Format all the rows in your table to a height of 13.2.
b. Add a column that contains the year extracted from the “Date”.
c. Add a column that contains the month number extracted from “Date”.
d. Add a column that contains the month formatted in words e.g. “Jan”, “Feb” etc.
3. You do some research about sharks on the Australian government website
https://www.environment.gov.au/marine/marine-species/sharks. Using this information, you should
add a column classifying the sharks into “Critically Endangered”, “Endangered”, “Vulnerable” and “Not
Endangered” (a category for those sharks that aren’t on the other 3 lists). You try the companion
website for “cetaceans” to find the comparable information to categorise the whales:
https://www.environment.gov.au/marine/marine-species/cetaceans (or search for more information
on whales online).
4. The Chair is also interested in the time of day that the marine life was sighted. You categorise these
using suitable group times of 2, 3, 4 or 5 hour bucket intervals. You decide on these yourself, using your
common sense and experience. Please note that the Chair would prefer a 24 hour clock for the time as
well – she says AM and PM seems less “scientific”.
5. The Chair is interested in finding out if there is a relationship between the water temperature, the
seasons and the length of the marine life sighted. Decide on how you will best present this information
to the Chair for review. Hint: you will notice that there are too many temperature numbers to make a
meaningful pivot table. The same is true of the length. Select some meaningful buckets to categorise
these in.
6. The chair is also interested in the number of sightings of marine life species by month (across all years)
and by year. Hint: Each row in the dataset is one sighting.
7. Finally, the Chair is interested in a specific subset of sightings – those from the Sunshine Coast North,
sighted between the hours of 10:15am and 4:40pm, and over the years 2001, 2005, 2010, 2014 & 2016.
8. The Chair needs to see the data represented in a variety of charts and pivot tables. She would also like
to be able to vary the data that is on these charts using filters and or slicers. In particular, she would
like to be able to slice the data by some of the following categories, as appropriate for the pivot tables
you have selected:
a. Year
b. Month
c. Area
d. Location
e. Length (categories)
f. Species