代做ACCT7106 Financial Statement Analysis – Individual Project Semester 2, 2024代做留学生SQL 程序

ACCT7106

Financial Statement Analysis – Individual Project

Semester 2, 2024

PROJECT OVERVIEW AND ADMINISTRATION

Objective

The project provides students with an important opportunity to ‘learn by doing’ . You will be asked to apply most of the analysis from the first five seminars to an Australian public company.

Start working on the project early. Leaving it to the last minute will be stressful and may result in poor performance.

Company

You need to analyse the financial statements of Universal Store Holdings (ASX ticker: UNI). The financial statements and notes in Excel format and the annual reports for Universal Store Holdings for FY 20, FY 21, FY 22 and FY23 will be shared with you on Learn.UQ.

Deliverables

The project consists of both short answer questions and Excel spreadsheet work. Please submit answers to short answer questions in one Word or PDF document (PDF document is recommended) and answers to spreadsheet questions in one Excel workbook, as indicated in the questions.

Page Limits

I have specified page limits for the questions that are to be answered in the Word document. These are maximum page limits. I will enforce maximum page limits by simply not marking any material that appears beyond the page limit for a particular question. You do not necessarily need to write as much as the page limit if you can provide a correct, but succinct, answer.

Cover pages and references lists are not counted towards the page limit.

Formatting

Your written answers should be in A4 page size, in Times New Roman font, 12pt font size, at least single line-spacing, margins at least 2cm on all sides.

Please clearly separate and label your written answers to each question.

Your Excel file should be formatted neatly and following the conventions described in class.

Marking Guide

A marking guide is provided at the end of the document. This contains the number of marks assigned to each part of the project.

Frequently Asked Questions (FAQ)

I will issue, and may update, an FAQ addressing frequently asked questions about the project. Please ensure that you check the Assessment tab on Learn.UQ on a regular basis.

Submission of the Project

The project is to be completed individually.

The due date for the project is 13:00 (Monday) September 9, 2024.

Cover Page

Please include a cover page at the beginning of your Word or PDF document with your name and student number. This is not strictly necessary, as Learn.UQ manages assignment submissions and matches them to students, but it provides a useful backup for me to identify the author of each submission.

Please include your name and student number in the excel file as well in “Student Details” worksheet.

Project Submissions Instructions

For the project, please submit two files by the submission date/time:

-     Your written answers must be submitted through the Turnitin link on Learn.UQ. Please use a format that Turnitin can accept, such as a Word document or PDF.

-     Your Excel file must be submitted through the Blackboard submission link on Learn.UQ.

Note that both your written report and Excel workings must be submitted to achieve any marks. This is because Ineed to seeyour workings for financial calculations.

Retention of Submission Receipts

Please keep all submission receipt numbers provided by submission systems such as Turnitin and Learn.UQ as proof of your submission.

Email Backstop

If you encounter any problems with the  submission systems (Turnitin and Learn.UQ), please alert technical support and me of the problem. (You may email me a copy of your assignment if you have trouble submitting through the normal systems and if you are concerned about incurring a late penalty due to technical problems.)

Late Submission of Assessment

Unless you have an approved extension, you will lose 10% of your assessed grade for the assignment for each 24-hour period (or part thereof) after the designated due date-time that your submission remains outstanding.  Your submission time is based on the later of when your written component is submitted and when your Excel file is submitted, so please make sure to submit both on time. To be clear, you will  incur  a  late  submission  penalty  even  if one  component  is  submitted  on  time  and  the  other component is submitted late. Please note that the 10% penalty rate for submission are set byUQ policy, not by me.

Multiple Submission ‘Attempts’

Some submission systems,e.g. Learn.UQ, allow students to submit multiple ‘attempts’ at an assessment before the due date. Only the most recently submitted attempt will be graded, unless otherwise agreed between the course coordinator and the student involved. 

For the avoidance of doubt, in the event that a student submits the assignment on time and then submits a different version of their assignment late, I will mark the late submission applying the normal late penalty.

Applying for an Extension

I do not decide on extensions. These are decided by UQ professional staff. Please see the instructions in Section 5 of the ACCT7106 ECP.

Getting Started

To help you,I will provide my spreadsheet for JB Hi-Fi, which you can adapt to the allocated company.

DETAILED PROJECT INSTRUCTIONS AND MARKING GUIDE

Complete the following tasks in your Word document or Excel workbook as indicated in the question.

A.  Reformulation and Accounting Analysis (Classes 2-4)

You will be given an Excel file with the raw financial statements and notes for the company, which you should use as inputs in the reformulation. This information was obtained from the 2021, 2022 and 2023 annual reports of the company. These annual reports have been shared with you on Learn.UQ under Assessment tab. You should go through the annual reports carefully to better understand the company’s accounting.

Answer the following in your Excel workbook in the indicated worksheet/tab:

A1 Balance Sheet Reformulation (Excel worksheet: BS_R)

•    Reformulate the Balance Sheet (including any relevant information in the notes), following the approach taught in class, for FY20, FY21, FY22 and FY23.

•    For ambiguous accounts, state any assumptions you made with justifications in the Excel file next to the relevant account (in red text). You should consult the annual reports to help you to interpret the accounts.

•    Ensure you provide workings for how you separated operating and financing cash.

•    Ensure you utilise any relevant information from the notes to the financial statements.

A2 Income Statement Reformulation (Excel worksheet: IS_R)

•    Reformulate  the  Income  Statement/Statement  of  Comprehensive  Income  (including  any relevant information in the notes), following the approach taught in class, for FY21, FY22 and FY23.

•    For ambiguous accounts, state assumptions you made with justifications in the Excel file next to the relevant account (in red text). You should consult the annual reports to help you to interpret the accounts.

•    Ensure you utilise any relevant information from the notes to the financial statements.

A3 Equity Statement Reformulation (Excel worksheet: EQ_R)

•    Reformulate the Equity Statement (including any relevant information in the notes), following the approach taught in class, for FY21, FY22 and FY23.

•    For ambiguous accounts, state assumptions you made with justifications in the Excel file next to the relevant account (in red text). You should consult the annual reports to help you to interpret the accounts.

A4 Cash Flow Statement Reformulation (Excel worksheet: CF_R)

•    Reformulate  the  Cash  Flow  Statement  (including  any  relevant  information  in  the  notes), following the approach taught in class, for FY21, FY22 and FY23.

•    For ambiguous accounts, state assumptions you made with justifications in the Excel file next to the relevant account (in red text). You should consult the annual reports to help you to interpret the accounts.

A5 Check Financial Statement Relations (Excel worksheet: Relations)

•    Calculate FCF = OI (after tax) – ∆ NOA for all four years using inputs from your reformulated Income Statement and Balance Sheet.

•    Calculate  FCF = NFE  (after tax) – ∆ NFO + d for all four years using inputs from your reformulated Income Statement, Balance Sheet and Equity Statement.

•    Calculate ∆ CSE for all four years using data from your reformulated Balance Sheet.

•    Calculate ∆ CSE = CI – d using data from your reformulated Income Statement and Equity Statement.

•    If your two FCF calculations produce different numbers, double-check that your reformulation is correct. If it is correct, explain why there is a difference between the two FCF numbers.

o Hint: this maybe due to dirty surplus gains/losses recognised directly in equity.

•    If  your   two   ∆   CSE  calculations  produce  different  numbers,  double-check  that  your reformulation is correct. If it is correct, explain why there is a difference between the two FCF numbers.

o Hint: this maybe due to dirty surplus gains/losses recognised directly in equity.

Tips: Follow the approach in Classes 2-4 and my JB Hi-Fi example spreadsheet. Use the Penman (2013) textbook as an additional source, but note that we are not doing everything described in the textbook.

Please follow the Excel formatting convention described in class and in the document ‘ACCT7106 Excel Formatting Conventions’. There are marks allocated for correct Excel formatting.

B.  Ratio Analysis (Class 5)

Answer the following in your Excel workbook. Please label the Excel worksheet as indicated in each part of the question:

B1 Ratio calculations (Excel worksheet: Ratios)

•    Calculate RNOA and ROCE for 2021, 2022 and 2023 following the approach taught in class. Use averages in the denominators, e.g. average NOA and average CSE.

•     First-level break-down: Calculate FLEV, NBC and SPREAD for 2021, 2022 and 2023. Show that: ROCE = RNOA + FLEV x SPREAD.

•     Second-level break-down: Calculate PM and ATO for 2021, 2022 and 2023. Show that: RNOA = PM x ATO.

•     Break-down PM into different components of OI (after tax): Calculate Core Sales PM as Core OI from Sales (after tax)/Sales. Also, calculate the other components of PM as Core Other OI (after tax)/Sales, Unusual OI (after tax)/Sales, and Operating OCI (after tax)/Sales.

Show that:

PM = Core Sales PM + Core Other OI (after-tax)/Sales

+ Unusual OI (after-tax)/Sales + Operating OCI (after-tax)/Sales

•     Break-down RNOA into different components of OI (after tax): Calculate Core RNOA from Sales as Core OI from Sales (after tax)/AvgNOA. Also, calculate the other components of RNOA as Core Other OI (after tax)/AvgNOA, Unusual OI (after tax)/AvgNOA, and Operating

OCI (after tax)/AvgNOA. Show that:

RNOA = Core RNOA from Sales + Core Other OI (after-tax)/AvgNOA

+ Unusual OI (after-tax)/AvgNOA + Operating OCI (after-tax)/AvgNOA

•     Third-level break-down: Break-down core sales PM and FLEV into their individual accounts.

•     Change in ROCE: Decompose ∆ ROCE using the following relation:

∆ ROCE = ∆ RNOA + ∆ SPREAD x FLEV (beginning) + ∆ FLEV x SPREAD (ending) Answer the following in your Word document.

B2 PM interpretation

•    Explain the reasons for changes in PM over the period FY21-FY23. You will need to research what happened to the company over this period to understand the major causes of changes in PM. Refer to your calculations in B1 [the breakdown of PM and third-level break-down ofcore sales PM] to support your answer, e.g. “gross margin up 27 basis points to 22.2%, driven by a combination  of  lower  discounting  and  continued  buying  improvements”  (2021  Earnings Conference Call Transcripts)” .

•    Your explanations must be supported by evidence. Make sure to include in-text references or footnotes to your sources of information. Make sure to provide answer in your own words.

B3 RNOA interpretation

•    Explain the operational factors/reasons for changes in RNOA over the period FY21-FY23. You will need to research what happened to the company over this period to understand the major causes of changes in RNOA [e.g. “the company opened ten new stores, driving an increase in sales (2023 Annual Report)”]. Refer to your calculations in B1 to support your answer, e.g. the breakdown of RNOA & PM.

•    Your explanations must be supported by evidence. Make sure to include in-text references or footnotes to your sources of information. Make sure to provide answer in your own words.

B4 ROCE interpretation

•    Consider the first-level break-down: ROCE = RNOA + FLEV x SPREAD. How does financial leverage affect the company’s ROCE over the period FY21-FY23? Has FLEV increased or decreased ROCE compared to RNOA? Why? If there have been significant changes in FLEV, discuss why they occurred. How much of the change in ROCE during FY21-FY23 is due to operative activities and how much is due to financing activities? Make sure to assess and discuss the effects that changes in accounting standards may have had. Refer to your calculations in B1 to support your answer, e.g. the breakdown ofFLEV and the decomposition of ∆ROCE.

•    Your explanations must be supported by evidence. Make sure to include in-text references or footnotes to your sources of information. Make sure to provide answer in your own words.

You may need to refer to the commentary at the beginning of the company’s annual report, company’s investor presentation, earnings conference call transcripts or newspaper articles to discover the cause of changes in the company’s performance and financial leverage.

Total Page limit (For B2, B3 and B4 combined): 3 pages (A4; Font: Times New Roman 12pt)

Tips: Consult Class 5 to help you. You do not have to do the third-level break-down of ATO as discussed in Class 5 and Penman (2013) Chapter 12.

All Parts of the Assignment:

•    Marks will be  awarded for clear and correct English expression. Please ensure your Word document is carefully proof-read.

•    Marks will be awarded for ensuring your Excel file is correctly formatted and clearly presented. Please follow the Excel conventions described at the end of Class 2 and applied throughout the course.

•    Marks  will  be  awarded  for  correct  and  complete  referencing  of  sources.  See  below  for referencing requirements.

•    Note  that the use of AI technologies to develop responses is  strictly prohibited and may constitute student misconduct under the Student Code of Conduct.

Referencing and Research Requirements

Some parts of this assignment require students to conduct relevant research about the company. For written answers, evidence of appropriate research and understanding of the company is included in the marks  allocated  to  the  question.  Appropriate  references  help  to  provide  evidence  that  you  have conducted research to support your answers. There are also a small number of marks allocated to proper referencing formats/conventions.

Research

I expect you to conduct research into the company and its operating environment. To be clear, Ido not want you to reference academic research (e.g. journal articles), unless they are highly relevant to the company or the point you are making. This is a practical course, not an academic research course. The emphasis  of  your  research   should  be  on  practical  materials,   such  as,  annual  reports,  investor presentations, earnings conference call transcripts, company websites, industry reports, newspaper articles, analyst reports etc.

Source of information should be credible. For example, materials produced by a company, by a major financial institution, by a financial data platform. (e.g. Morningstar or CapitalIQ), or by a mainstream newspaper are credible sources of information. Examples of low credibility sources include (but are not limited to): obscure websites not associated with a credible source, and articles from obscure news organisations. To be clear, references to low credibility sources will not betaken as evidence of research and will generally result in low grades. In addition, much of the information available from low- credibility sources is also low-quality and so relying on this information will usually result in low grades.

Referencing

Proper referencing is crucial for two reasons:

•     To comply with plagiarism rules.

•     To provide evidence that you have conducted research in answering your questions.

You must follow one of the following two approaches to referencing for the project:

•     Brief in-text references AND a reference list. The in-text reference should be brief but clear, while the reference list should provide sufficient detail forme to locate the source information. Use one consolidated reference list for the whole project, rather than a reference list for each answer.

•     Footnote references with  similar detail to a reference list. You may use  standard footnote referencing conventions like ‘ibid’ if you like.

In-text references and footnotes should be included in every sentence where you make a claim that requires supporting evidence. Do not simply put one reference at the end of an entire paragraph or page.

Please include the page number or numbers when referencing a lengthy document, such as an annual report or investor presentation document. If you are referring to a note to the financial statements, you can also mention the note number instead of a page number.

References are essential for direct quotes, for example:

JB Hi-Fi describes its one of the competitive advantages as follows: “#1player in the Australian market with the opportunity for further consolidation” (2020 Annual Report, p. 19).

References should also be used when you make a claim about a company that requires some evidence to support it, for example:

Most of JB Hi-Fi’s revenue is generated in Australia (2020 Annual Report, p. 70). The following is an example of what to avoid:

In the future, more and more people will choose to travel by air, or prefer aircraft as the main long-distance transportation.

This is a claim without any evidence to support it. Who says that ‘more and more people will choose to travel by air’? A statement like this needs to be supported by a reference or by a surrounding sentence which provides evidence.

Certain claims are so self-evident or obvious that they don’t need a supporting reference. However, note that these should generally form. only a small part of your answer. For example:

Qantas faces virtually no threat of substitutes on most of its international flights, as Australia’s major population centres are geographically distance from most overseas destinations, so that travelling by other means (e.g. by ship) would take a prohibitively longtime.

It is obvious the Australia is geographically isolated from most overseas destinations, so no reference would be needed for this claim. But be careful with relying on a statement being obvious. It is generally better to provide a reference.

There is also no need to put a reference if you are citing the class PowerPoint or the textbook, unless you are directly quoting.

The following is a guide to how you might reference some typical sources:

Source	Example In-Text Reference (Brief)	Example Reference List Entry (Detailed)
Company annual reports for   the company you are   analysing	(2020 Annual Report, p. X)	JB Hi-Fi 2020 Annual Report.
Company annual report for   another company	(2020 Harvey Norman Annual Report, p. X)	Harvey Norman 2020 Annual Report.
Company investor presentations for the company  you are analysing	(FY20 Investor Presentation, p. X)	JB Hi-Fi FY20 Investor Presentation, 17 August 2020.
A company announcement, other than the annual report or investor presentation	(ASX announcement, 4 August 2020)	JB Hi-Fi ASX Announcement, 4 August 2020, “Temporary closure of metropolitan Melbourne stores” .
News reports	Newspaper + date, e.g. (Australian Financial Review, 12 June 2020)4 You may abbreviate Australian Financial Review to AFR if you like.	Australian Financial Review, 12 June 2020, “JB Hi-Fi set for biggest profit growth in years” .
Website	(JB Hi-Fi investor website)	JB Hi-Fi investor relations website, https://www.jbhifi.com.au/.

Notes:

•     For sources not listed in the table, use your common sense, but make sure you provide enough information forme to clearly identify the source.

•     If you provide footnotes instead of in-text references + a reference list, then please provide as much detail as the ‘Example Reference List Entry’ column of the table.