ACCT3003: Accounting Modelling and Data Visualisation

ACCT3003: Accounting Modelling and Data Visualisation

Folio Assignment Guide, Semester 2, 2024

Due Date for Submission: Sunday 15/9/2024 at 5.00 PM

Please note that the Practical Assignment for ACCT3003 Accounting Modelling and Data Visualisation is an individual assessment task worth 40% of your total marks in the unit. Please refer to the online marking rubric for an explanation of how individual marks will be allocated for this assessment task. The assignment is to be submitted online through the Blackboard “Assessment” folder.

High standards of academic integrity are expected. You are referred to the URL link provided in the ACCT3003 Unit Outline (see: Academic Integrity Website) for further information on Academic Integrity and originality.

No Turnitin submission is required for the Practical Assignment file because it is not a written submission, but an original Microsoft Office 365 / Excel Assignment.

VIVAS

To detect any academic misconduct, we will conduct vivas (i.e. short interviews with some students) on the assignment. Students will be randomly selected.

Interviews will be held face to face or using Blackboard Collaborate.

Required:

Question 1: Interactive Dashboard / Financial Statement Analysis                             (20 marks)

(a)     Using the Curtin University library link to: DatAnalysis Premium (Morningstar) Database. Download the 10-year comparative Ratio Analysis (see Financials folder) for any two (2) Australian listed company that you have been allocated. Please see the attached list of allocated companiesand ASX codes.

Save the original downloaded ratio analysis data for each company in the first worksheet in your Excel workbook. No marks are allocated for this task.

(b)    Using Microsoft Office 365 / Excel, prepare a single interactive dashboard of the key ratios that you feel would help an analyst to evaluate the financial performance of the two companies.

The dashboard should be based on the 10-year comparative ratio analysis workbook (where possible) that you have downloaded in Part (a) above.

Please select any four ratios from the following categories: Liquidity, Asset Management, Leverage / Debt Management, Profitability, Cash Flow and Market Based Ratios.

In addition, to the four ratios selected, students are also required to calculate the Altman Z score for the two companies allocated.

The single Dashboard should contain, at a minimum: 1) two different chart types; 2) images of company brand and other graphical images related to each company’s business; 3) filters for data in your charts to engage user interaction with your Dashboard; and 4) Title and text boxes to explain the charts and diagrams in your Dashboard. (15marks).

(c) Using Microsoft Office / Excel 365 STOCKHISTORY and FORECASTING functions available, prepare a line graph of the weekly share price history (from 1/7/2022 to

30/6/2024) for any two (2) listed companies that you have been allocated (separate graphs).  Forecast the share price for the next 6 months (i.e., 31/ 12/2024).  Please note, graphs should also include a trendline and formula. Predicted weekly share price figures should be shown using the FORECAST SHEET function or FORECAST formula function available. (5 marks).

Question 2: Capital Evaluation (8 marks)

Ingenious Devices, Inc. is considering a replacement for its damaged old cutting machine with a

new laser cutting machine. Repairing this machine would cost $5,700. Management has requested information about two new machines from two different vendors. The old machine has no salvage value. The estimated after-tax cash flow associated with the old machine for the next year is $2,000 and the firm expects that this cash flow will grow at a rate of 12% per year over the next four years. The first model of the new laser cutting machine has a price of $8,000 and the firm expects to generate after-tax cash flows of $3,000 with this machine for the next four years. These cash flows will grow at an annual rate of 15% over the next four years. Finally, the second model of the new laser cutting machine has a selling price of $9,000 and the firm expects to produce after-tax cash flows of $3,500 for the next year. This cash flow will grow at a rate of 5% per year over the next four years. If the firm’s WACC is 14% and its reinvestment rate is 10%, determine the following:

Required:

a)   Create an after-tax cash flow statement for the first model, second model and to repair the old machine. Compare the alternatives.

b)  Calculate the payback, discounted payback, NPV, PI, IRR, and MIRR of the replacement project. Compare old machine with first and second models?

Question 3: Excel Case Study: APAC Data Model (12 marks)

Case study detail: – imagine you are a recently appointed graduate accountant in APAC International Limited and your manager has asked you to investigate the overall sales and profitability for the company and to identify key areas for improvement and present your findings to the board. Using Microsoft Office 365 / Excel and the APAC Data Model provided (see Assessment folder), prepare a two-page interactive dashboard ofyour findings and recommendations. Your analysis should include all areas: customers, products and regions (i.e. locations). Please note, remember to select data model when creating and inserting pivot tables.

The visualisations should contain, at a minimum: 1) two interactive dashboards; 2) different chart types, and tables; 3) calculated fields; 4) filters for data in your visualisations to engage user interaction with visualisation; and 5) Title and text boxes to explain the charts and diagrams in your visualisations.

Format Requirements and marking note:

The Foliol Assignment task must be submitted online as one MS Excel workbook file with a separate cover page. Please note, the following submission format requirements:

Cover-page is to be submitted with full student name, student ID and student signature. Please note that this cover sheet is to be submitted separately to your Folio Assignment MS Excel workbook.

When naming your Cover Page in MS Word, please use the following file name protocol: Student ID_Surname_Folio Assignment_Cover Sheet.docx.

Practical Assignment task is to be submitted with full student name and student ID. Please note your MS Excel workbook files are to be submitted separately to your cover page.

When naming your MS Excel files, please use the following file name protocol:

Student ID_Folio Assignment_Q1_Q2.xlsx

Student ID_Folio Assignment_Q3.xlsx