Exercise – Excel and Tableau Sem 1, 2021

ACCT5034 Analysis and Visualisation of Financial Data

1 | P a g e
Publication date: February, 2021
CRICOS Provider Code: WA 00301J

At the end of Week 6 this semester you will have learned how to clean, organise and visualize
data using R; specifically using the RStudio IDE (integrated development environment. You
should now understand the value of understanding the characteristics of your data, cleaning
your data and asking many different questions about your data when conducting exploratory
data analysis (EDA).

In this assessment you will apply your knowledge to tidying a financial dataset, creating new
financial data (variables), EDA and novel visualisations using a package called ggplot2, that
are not possible in Excel or Tableau.

On completion and submission of this assessment it is expected that you will have
demonstrated the following outcomes:
1. Understanding of using R in the RStudio IDE and data management best practice with
an R Project.
2. Write code in an R Script file, using R packages and functions for EDA of a dataset,
including learning the characteristics of your data and using statistical packages to
summarising your data. Filtering, selecting, arranging, summarising and grouping data
to create new datasets.
3. Creating financial data visualizations using RBase, Tidyverse ggplot2 and related
extension packages for ggplot2.

Please note that this assessment is 20% of your final mark and the due date is
communicated at the end of the Unit Outline, in the “Program” section.

Academic Integrity

All assessments in this Unit must be submitted online at the Unit’s Blackboard site and
Turnitin Assignment submission will be used to check identity of the written part against other
published and unpublished work. Academic Integrity and originality of your work will be
reviewed during the marking and moderation process so you are strongly advised to read the
Exercise – Excel and Tableau Sem 1, 2021

ACCT5034 Analysis and Visualisation of Financial Data

2 | P a g e
Publication date: February, 2021
CRICOS Provider Code: WA 00301J
information about academic integrity at this site if you have not used Turnitin before (Student
Plagiarism Guide PDF1). Please review this Guide to academic integrity and contact the Unit
Coordinator early in semester if you need further explanation or need any clarification about
your assessment submission.

A Case Study of Accounting Financial Performance
You are a new accountant in public practice at EY’s Perth Office2; you started work there 1st
January 2021 after finishing your Accounting course at Curtin University last November. Your
manager, Roseberry Peters, has asked you to download 30 years’ income statement data for
an operating Australian Mining or Energy company from Morningstar’s DATAnalysis as a
training exercise to learn something about the Mining & Energy industry in which EY’s has
many major clients.

A few days after your first meeting with Roseberry, she has checked the downloaded income
statement data that you have sent to her, and she has given you a list of things to do in an
email as follows:

Roseberry Peters
Monday 31/01/2021 5:45 AM
To: You (EY Grad Program)


I have just opened your email from last Friday and am happy with the numbers you have
downloaded. Thank you for downloading all of the financial data for the past 30 years from
that database at Curtin.

Please work through the following list of requirements. At a minimum I would like to have a
look through these with you this Friday morning, 5th Feb.



1 http://academicintegrity.curtin.edu.au/local/docs/StudentPlagiarismGuide.pdf
2 Retrieved from https://www.ey.com/en_au on 9th February, 2021.
Exercise – Excel and Tableau Sem 1, 2021

ACCT5034 Analysis and Visualisation of Financial Data

3 | P a g e
Publication date: February, 2021
CRICOS Provider Code: WA 00301J
1. Original historical income statement data (30 years comparative) for an ASX-listed
Mining or Energy company data saved as a .csv (comma-separated values) file. Note
that the MS Workbook of financial data you download from Morningstar’s
DATAnalysis database will have many worksheets but we are only interested in the
comparative income statements.

(NOTE: Every student must choose a unique company with 30 years historical income
statement information, showing year-to-year changes and therefore evidence of
operations. Please access the OneNote file via the link provided in the “Assessments”
folde provided for this purpose by your Unit Coordinator).

It is a requirement to have your company’s income statement financial dataset
prepared in csv format by the end of week 3 and have completed the details in the
OneNote file supplied for this purpose by your Unit Coordinator).

2. Demonstrate good R project management by creating an R Project, Script and Data
directories and using # [text] and # [text] ---- as demonstrated during semester to add
structure to your script file.

3. Script file and code to tidy income statement data and summary statistics: Use R
packages to clean and transform the data as required to create Tidy Data using the
principles described by Hadley Wickham in his 2014 paper you have read.3 During this
process you should make use of the following packages in R: tidyr, dplyr, janitor, base ,
tibble; using these is the minimum requirement but you can use others as appropriate.
Then use various statistical summary functions and packages to characterise your raw
data, for example, Hmisc::describe (package::function), skimr::skimrr.

3 Wickham, H. (2014) Tidy Data. Journal of Statistical Software, 59(10): 1-23
Exercise – Excel and Tableau Sem 1, 2021

ACCT5034 Analysis and Visualisation of Financial Data

4 | P a g e
Publication date: February, 2021
CRICOS Provider Code: WA 00301J
4. Script file and code to do exploratory data analysis (EDA): Use the newly tidy income
statement data and script with functions like filter(), arrange(), select(), group_by(),
summarise() to create new datasets based on the original data. Also use mutate()
and/or transmute() functions to create new variables during this process. After
creating a new dataframe relevant to your EDA please save it using write.csv().

5. Use the ggplot2 package and any extension package you like (Click here for a useful
resource as demonstrated in Module 1 learning materials) to create four unique
visualizations that specifically use new data you have generated through your process
of EDA in the item above. Output the the four plots using ggsave() function of ggplot2.

6. Create an MS Word document which contains the following:

a. A Copy/Paste of the full Script from the R Script file you have created above.
b. Screen-capture your “Files” window and paste the image of your “Exercise
Project” working directory.
c. After the screen-capture please write a section in the document which explains
the questions you asked about your data for your Exploratory Data Analysis.
(Hint: You have heard the saying, “There is no such thing as a dumb question.”
Well, during the process of EDA your Instructor believes this to be true, as long
as you are asking questions and trying to answer them about the observations
(years) and variables (line-items) in your company’s comparative income
statements financials.
d. At the end of the document use MS Word>Insert>picture to insert the .png
format plot outputs from your use of ggplot2 and ggsave above.

No marks are given for a formal report-style in the MS Word document; however the learning
outcomes of Module 1 will be assessed and you should click on the marking rubric link to
review the details of achievement levels. This will help you understand how the marker will
evaluate your effort. Minimum referencing requirements using the style identified in the Unit
Outline are required if you use others original or published work protected by copyright.
Exercise – Excel and Tableau Sem 1, 2021

ACCT5034 Analysis and Visualisation of Financial Data

5 | P a g e
Publication date: February, 2021
CRICOS Provider Code: WA 00301J

NOTE: It is important for you to review4 and understand Curtin University’s ‘Student Guidelines for Avoiding
Plagiarism’ before submitting your assessment. If you are in any doubt about this university policy please review
the Student Plagiarism Guide provided at the start of this Exercise assessment or call or email the Unit
Coordinator, Dr. Greg White.

To be submitted online through the ‘Assessment’ tab in the Unit’s Blackboard site.


4 http://academicintegrity.curtin.edu.au/local/docs/StudentPlagiarismGuide.pdf