110.309 ADVANCED FINANCIAL ACCOUNTING
ASSIGNMENT 2
100 marks (20% of final grade)
Financial Statement Analysis
Semester One, 2024
Please note that this assignment is due before Wednesday 22 April 2024, 11:59pm New Zealand Time
Objective of this assignment: Developing an analytics mindset
Having and using an analytics mindset is critical in accounting and business. This assignment focuses on developing all aspects of your analytics and critiquing mindset, namely the ability to:
• Ask the right questions (the assignment questions illustrate what these may look like).
• Apply appropriate data analytic techniques (Using Power BI, prepare appropriate tables and graphs).
• Interpret and share the results with stakeholders (Prepare a report for Mr. Clayton Tai).
How to Approach this Assignment
1. Please read all instructions before starting the assignment.
2. This assignment requires you to complete tasks using Power BI Desktop. It is free for Microsoft
Windows users. Mac users can useremote desktopwhich has Power BI Desktop installed.
3. Attend the Power BI Zoom workshop on 14 March, 12noon – 1.30pm.
4. Use the ‘Power BI Resources’ page provided on the 110.309 Stream site.
5. Study the Week 1 material on financial analysis to make sure you understand the concepts covered.
6. See Appendix C for marking rubric.
Before attempting the assignment
1. Download Power BI Desktop to your Windows device OR access Power BI through Massey’s Flexible Learning environment. See ‘Power BI Resources’ under ‘Course resources ’ tab on Stream.
2. Access the material provided for Power BI training under the ‘Power BI Resources ’ on Stream.
Submitting the Assignment files
1. The assignment will result in 2 separate files: one Power BI file (.pbix) and one Word file (.docx).
2. Submit both files in the provided submission box for Assessment 2.
3. Stream records the submission date and time. See Stream for policies regarding extensions and late submissions. Penalties will be applied to late submissions for which an extension has not been granted.
4. Note that substantial marks could be lost if you fail to submit any of the required files as marks are allocated to the content of each of the files.
Naming Convention and backups
1. Use the following naming convention for the files that you create: use your Last Name and Student ID number as the name of the file [Important: Do not change the file extension or you (and the marker) will not be able to open the file]. For example, if your Last Name is Smith and your Student ID number is 12345678; your files will be Smith12345678.pbix and Smith12345678.docx.
2. Once you have started working on an assignment you should make backups. We recommend you give each backup a new version number. (This allows you to go back to earlier versions)
ASSIGNMENT BRIEF
You have been hired by the consultancy firm, RME Investment Ltd, as a financial analyst. Your manager, Clayton Tai, has asked you to analyse and compare the financial statements of two selected manufacturing companies: Coca-Cola and PepsiCo. You have requested your technical support person to download financial statement data for all manufacturing companies from 2000 – 2022. In the data, there are columns for the relevant Income Statement and Balance Sheet items for your analysis. Load the data from the 110.309 Assignment 2 - Student Data Manufacturing.xlsx file into Power BI.
PART A (Calculation and presentation of ratios using Power BI)
Required:
(i) In Power BI Desktop, calculate the ratios in Appendix A for the average industry (all companies
in the dataset) and the two companies for the years 2000 – 2022. You are required to use:
• Power Query ‘computed columns ’ for profitability ratios,
• Dax ‘calculated columns ’ for efficiency and liquidity ratios. For these ratios, deal with outliers in calculating the industry average by removing the top and bottom 10%, and
• Dax ‘measures ’ for the financial gearing ratios. (30 marks)
(ii) Create 10 pages within your Power BI file. For each page, create a table/matrix for each ratio calculated in (i), for the years 2000 – 2022. Each table/matrix should include column and/or row averages. Use ‘on visual calculation ’ to create a column for the difference in averages between the two companies (see Appendix B). (15 marks)
(iii) Create appropriate line graphs for each ratio (table/matrix) in (ii). Present each graph on the page
created in (ii) above for the ratio (table/matrix) concerned (see Appendix B). (10 marks)
(iv) On each page, create a slicer showing the periods (in years) 2000 – 2012, 2013 – 2017, and 2018
– 2022. Name the slicer ‘Periods’ and link the slicer to each of the tables/matrices and graphs you created in (ii) and (iii) such that each of these can be toggled to show each of the periods (see Appendix B). (5 marks)
Sub-total: 60 marks
PART B (Memorandum)
Required:
Mr Tai is particularly interested in the performance of the companies. In a short memorandum (maximum 600 words), summarise the results from Part A by briefly comparing the averages for the five-year period 2013 – 2017 to the last five years (2018 – 2022) and in comparison with the industry for each ratio for the two companies. Your explanation should address the following:
(v) The profitability of the companies. (16 marks)
(vi) The efficiency of the management of inventories. (6 marks)
(vii) The liquidity and financial gearing of the companies. (15 marks)
(viii) Which company shares you recommend buying (better performing) or which you recommend selling (worse performing)? Include the reasons for your answer. (3 marks)
Sub-total: 40 marks
Presentation:
• See Appendix D for format of a memorandum.
• Answers to Part B should be concise but should convey important information in a well-reasoned manner, supported by the data and your analysis, where appropriate.
• Your memorandum for (v) to (viii) should be no more than 600 words (+/- 10%). Do not copy the tables and visualisations from (ii) and (iii) into the Word document as part of your discussions.
• References are not required.
• You must use font type ‘Times New Roman’ and font size 11. Failure to adhere to the word limit and/or format will lead to mark deductions. The reduction of marks for exceeding the word limit would be as follows: 1 mark for every 10 additional words over the word limit.