Individual Assignment

25624 Financial Metrics for Decision Making Autumn session 2022

General information and instructions

• This individual assignment accounts for 40% of your final grade.
• You must complete this assignment on an individual basis.
• Deadline: Friday 13th May 2022, 11.59pm AEST (Sydney time)
• You need to submit the Excel spreadsheet via Canvas:
  • Excel spreadsheet
    • .xlsx format only
    • The spreadsheet must contain all calculations you perform for the tasks.
    • Aim to structure and format your Excel solution in the most efficient way possible and use financial modelling best practices.
• This task includes a milestone assessment component that evaluates academic English language proficiency. You may be guided to further language support after the completion of this subject if your results in this milestone task indicate you need more help with your language skills.
• All assignment-related queries must be posted on the Canvas discussion board. E-mail queries related to the assignment will not be entertained.
• It is possible that you run int o technical difficulties while uploading your assignment to Canvas. In this case (and only in this case), can you submit your work via e-mail to the subject coordinator. Note that submissions must still be received prior to the deadline.

From prior experience, technical difficulties often can be traced back to students trying to upload their work a few minutes prior to the deadline. Tip: do not wait until 11.55pm on 13th May to submit your work via Canvas. Late submissions will receive zero credit unless a special consideration with valid reasons and documentation is filed.

Grading

• Both the Excel spreadsheet and the written component will be graded.
• The Excel spreadsheet will be marked based on the efficiency and clarity with which solutions are produced.
• Late submissions will receive zero credit unless a special consideration with valid rea- sons and documentation is filed.

Data

• In the Assignment page on Canvas, you will find an Excel spreadsheet that contains the data you need to complete the assignment tasks.
• There are 4 worksheets in the spreadsheet.
• Perform your calculations based on the data in these spreadsheets.

1. Refer to the “NationalExamInputs” worksheet.

You are given access to a data set that tracks student scores on the National Exam of a country. The National Exam is taken by students in their final year of high school, and has 4 components:

1. Natural Sciences
2. Social Sciences
3. Languages
4. Mathematics

The score students obtain is important in their application to universities. The data set also contains demographic details of students, as well as their individual circumstances. An overview of the different variables is provided below.

Perform the following tasks.

1. [1 mark] Use Cell D4 of the NationalExamSolutions worksheet to calculate the number of students who are 20 year of age or under
2. Use range D7:G9 of the NationalExamSolutions worksheet:
   • [2 marks] report the average score by component for the entire sample, the average score by component for students who are 20 year of age or under, and the % difference
   • [2 marks] Use Cell B1 of the Written Component worksheet to describe the exam components for which students 20 years of age or under have an advantage/disadvantage compared to the entire sample.
3. [1 mark] Your colleague believes that gender is related to students’ language score component. More specifically, she thinks that female students perform better than male students. Write down the null and alternative hypotheses [Cells D13 and D14]. Do you need a 1-tail or two-tail t-test? [D15]
4. Perform a hypothesis test of (c) using a 1% significance level.
   • [2 marks] Use Rows 19 to 22 to show your results
   • [2 marks] Use Cell B2 of the Written Component worksheet to answer:
     • Are you able to reject your null hypothesis? Explain why (not).
     • Provide an economic interpretation of your result.
5. [1 mark] Another colleague believes that access to a computer and internet is related to students’ math score component. He thinks that students with access to both a computer and an internet connection (Yes on both variables) perform better than students with no access to computer or internet (No on both variables). Write down the null and alternative hypotheses [Cells D25 and D26]. Do you need a 1-tail or two-tail t-test? [D27]
6. Perform a hypothesis test of (g) using a 1% significance level.
   • [2 marks] Use Rows 31 to 35 to show your results
   • [2 marks] Use Cell B3 of the Written Component worksheet to answer:
     • Are you able to reject your null hypothesis? Explain why (not).
     • Provide an economic interpretation of your result.
7. [2 marks] You want to further understand the impact of gender on the scores of the Language component of the exam. To do this, you run the following regression analysis:

𝑆𝑐𝑜𝑟𝑒𝐿𝑎𝑛𝑔 = 𝛽₀ + 𝛽₁𝐴𝑔𝑒 + 𝛽₂𝐹𝑒𝑚𝑎𝑙𝑒 + 𝛽₃𝐶𝑜𝑚𝑝𝑢𝑡𝑒𝑟 + 𝛽₄𝐼𝑛𝑡𝑒𝑟𝑛𝑒𝑡

Report the regression outputs starting in Cell D37 of the NationalExamSolutions worksheet

8. [3 marks] Use Cell B4 of the Written Component worksheet to discuss your main results: coefficients statistical and economic significance, model goodness of fit.

Make sure you relate these findings to those in questions 1.c and 1.d.

2. The “Modelling” worksheet contains assumptions to build an Income Statement forecast for

Chalten Ltd for the 2022 – 2026 period. Using the assumptions provided, please:

1. [2 marks] Build the Economic scenarios section [Rows 16 -33], including a scenario selector
2. [2 marks] Build the Revenue forecast [Rows 42 -58]
3. [2 marks] Build the Cost of Sales forecast [Rows 68 -78]
4. [2 marks] Build the Income Statement forecast [Rows 88 -96]
5. [6 marks] After analysing the company’s forecasts, Chalten’s CFO believes the sales volume growth is too high. The company aims to having an implied operating rate that does not exceed 80%. Given that the sales growth rate is the same every year, what should that rate be to make sure Chalten followsthe 80% rule?
6. [6 marks] The model is also assessed for consistency and use of best practices