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 BACKGROUND 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) Hi, 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. Cheers, Roseberry 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 Requirements 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. TOTAL MARKS 20 END OF ASSESSMENT GUIDE 4 http://academicintegrity.curtin.edu.au/local/docs/StudentPlagiarismGuide.pdf
