1. BACKGROUND
This assignment requires students to develop a spreadsheet model for a listed Australian company using advanced MS Excel functions as part of its construction with a written explanation of your financial model and how the output from it can be used.
The marking rubric will be available on the Unit's Blackboard site early in the semester and the assignment is to be submitted online as you did for the earlier Folio assessment.
Finding an appropriate company for analysis
Before commencing you must choose a listed Australian company. When you select the company, it is strongly suggested that you choose a GICS code of: 1) Pharmaceuticals, Biotechnology & Life Sciences; 2) Food Beverage & Tobacco; 3) Health Care Equipment & Services; 4) Technology, Hardware and Equipment; and, 4) Retailing.
Important: Do one more check before you start analysing the company data. Access the financial statement data using DatAnalysis Premium (MorningStar) as you did for the ‘Folio' assessment and download the spreadsheet as demonstrated in this YouTube Clip. Do a check that the company has financial data indicating real operations over the past five years, for example, changing balances for revenues, cash at bank, inventories, debt and shareholders' funds.
2. REQUIREMENTS
a. Requirements for your financial model
The assignment is a challenging assessment that requires you to develop a small financial model from knowledge gained during your study in this unit. Students should know that the financial model using MS Excel spread sheet developed for this section does not have to be large but it should have the features identified in the section below.
b. Features of MS Excel financial model
• Begin the financial model worksheets in MS Excel with a worksheet containing the raw data you have downloaded for your company from DatAnalysis.
• The second worksheet in your workbook should contain edited financial data that formats the raw financial data into financial statements which will be more useful to a client or manager reviewing the model.
• The third worksheet must be a common-size analysis of the company's comparative balance sheets for at least the past five years and also an index number trend analysis of the income statements over the same period.
• Application of concepts and advanced MS Excel functions (for example, Scenario Manager, What- if Analysis, Regression Analysis, Monte Carlo simulation or MS Excel Solver).
• The worksheet or worksheets should be organised so that they have a data entry section, calculation section and output section. It is important to create comments and text guidance to a user of your financial model.
• The output section should employ visually useful output like graphs and charts to display results and should be organised to print neatly on one A4 page.
• Demonstrate a clear understanding of academic integrity through correctly referencing the original work of others used in the assignment.
c. Explanation of Financial Model
A written explanation of how your financial model works. That is, you are to write about how your financial model can be used to examine the company financial statements and how the output of your financial model will be useful to a financial manager of the firm? (12pt, Ariel font, 1.5 line spacing; no more than 300 words).
This explanation is to be submitted as an MS Word or PDF file that has been separately labelled.