ACFI3009 - Contemporary Accounting Issues 2020 - Janvrin Case Study - Accounting and Finance Assignment Help

Assignment Task

Purpose: This assessment task allows you to develop practical skills using a software package commonly used in Accounting practice.

Acknowledgements: This assessment task draws on resources obtained from a government data site. 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, 32:31-48.

Instructions:

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.

The Scenario:

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.

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

g. Time
h. Endangered status
i. Season
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 counts 3/20.