ACFI3009 CONTEMPORARY ACCOUNTING ISSUES 2020
ASSESSMENT 1: EXCEL EXERCISE
Purpose: This assessment task allows you to develop practical skills using a software package commonly used
in Accounting practice.
Weighting: 20%. Excel: 17% Reflection 3%.
Acknowledgements: This assessment task draws on resources obtained from a government data site
https://data.gov.au/. Original questions were adapted from an edX course “Introduction to Data Analysis using
Excel”, accessible at https://courses.edx.org/courses/course-v1:Microsoft+DAT205x+1T2020/course/. Thanks
to Mary Kazembe for her research assistance in the adaptation of this assessment task and in location of the
dataset being used.
The scenario is adapted from Janvrin, Raschke and Dilla (2014): Janvrin, D.J., Raschke, R.L. and Dilla, W.N.
(2014). Making sense of complex data using interactive data visualisation, Journal of Accounting Education,
1. Please download the Assessment data file (Assessment 1 Excel file 2020) from Blackboard.
2. To complete this assessment, you will need access to a copy of Microsoft Excel, which, as an enrolled
student, you can obtain for free from the University.
3. Complete the requirements as listed below, and submit your completed worksheet to Blackboard by
the due date.
4. Complete the reflection task and submit it in the folder provided on Blackboard by the due date.
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).
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.
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 research essay assignment help temperature numbers to make a
meaningful pivot table. The same is true of the length. Select some meaningful buckets to categorise
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:
e. Length (categories)
h. Endangered status
j. Water Temperature (categories)
9. Please make sure that your charts and other dashboard elements are appropriately labelled, i.e. with
titles and chart labels.
10. You need to submit the file containing your worksheet and dashboard to Blackboard for the Chair to
look at. Please use the Excel Files – Assessment Task 1 folder on Blackboard and please make sure
that your filename contains your last name and student number.
11. You should also send the Chair a ONE PAGE executive summary of your work (maximum 1000 words –
+/- 10%). This should explain how to use your dashboard, why you have included the tables, charts,
filters and slicers you have chosen, and highlights any specific choices you have made when
categorising the above. Submit this to the folder supplied in Blackboard as well.
12. You should also prepare a 500 word reflection on this task. You will find some resources for writing
these in the Assessments folder. Please consider your position at the outset, during and at completion
of the task, make reference to the course materials and other resources used along the way, and
reflect on how this assessment task might impact your approach to data visualisation in the future.
Submit this to the “Reflections: Assessment 1” folder on Blackboard. This part of the assessment