diff_months: 3

Suppose you want to copy the data shown in the excel file below into word.

Flat 50% Off Order New Solution
Added on: 2024-11-12 20:00:09
Order Code: SA Student Sai Accounting and Finance Assignment(5_24_41995_13)
Question Task Id: 506283

Suppose you want to copy the data shown in the excel file below into word.

Step 1:

Highlight the table as shown below. Right click and select Copy.

Step 2:

Open your word document. Place the cursor where you want the table to appear. Click on Paste tab (top left corner below File), then on paste special and then choose Picture (enhanced metafile).

You will get the excel table as a picture file (see below) which you can rescale to fit your page as desired.

PS: You can also use a similar strategy to copy graphs/charts from excel to word. Just click on the chart and follow Steps 1 & 2 outlined above.

FIN503 Finance, Investment and Sustainability

Assessment 2 Written Assessment

Semester 1, 2024 Dr. D Chakrabarty

-1677597544

Maximum Marks: 45 (worth 45% of the overall assessment)

Due date: Tuesday, 7th May 2024, 23:59 hours, Darwin time

Instructions:

1. Download BOTH the Assignment Question file and Data file to complete your assignment.

2. Use Microsoft Word for the main assignment

3. Use Microsoft Excel wherever possible for numerical calculations and graphs/plots.

4. Copy and paste Excel outputs (e.g. plots, tables) into your Microsoft Word document (to protect the formatting of Excel output, use the "paste as picture" option in Word.

5. How to Submit: Click in the submission box in the Assessment 2 folder and click on the attachment icon . Attach the files with the completed assignment (Word document and the Excel file).

6. Keep a hard copy of the submitted assignment, in case there are problems with the electronic submission.

Important Notice:

Any answer in the Excel document, but not in the main Word document will NOT be marked.

As this is an individual assessment, students should submit their own assignment. All assignments submitted will go through a matching process. If found to have cheated/plagiarised, all submissions will receive a mark of zero for this assessment item. It is up to you to keep your assessment confidential.

This Assignment consists of 4 parts. Attempt all parts.

Consider the following scenario:

8778289714In January 2023, you joined Tri-Star Management Pty Ltd as an intern. On your first day, your CEO asks you to analyze information about the share prices of five companies: Australia and New Zealand Banking Group Limited (ANZ.AX), Coles Group Limited (COL.AX), National Australia Bank Limited (NAB.AX), Qantas Airways Limited (QAN.AX) and Woolworths Group Limited (WOW.AX).

The file FIN503 A2.xlsx contains data on these five companies' adjusted beginning-of-month share prices from January 2020 to December 2022. In addition, you are provided with the value of the S&P/ASX200 market index over this period. The primary data are provided in columns A G.

00In January 2023, you joined Tri-Star Management Pty Ltd as an intern. On your first day, your CEO asks you to analyze information about the share prices of five companies: Australia and New Zealand Banking Group Limited (ANZ.AX), Coles Group Limited (COL.AX), National Australia Bank Limited (NAB.AX), Qantas Airways Limited (QAN.AX) and Woolworths Group Limited (WOW.AX).

The file FIN503 A2.xlsx contains data on these five companies' adjusted beginning-of-month share prices from January 2020 to December 2022. In addition, you are provided with the value of the S&P/ASX200 market index over this period. The primary data are provided in columns A G.

You will be asked a series of questions to guide your analysis. You will need to manipulate the data to answer the questions below. Some Excel formulas are provided in the worksheet. Some questions requiring explanations have a suggested word limit.

Question 1: Average returns [7 marks = 4 + 3 marks]

Compute the annualized monthly return on the shares of each company and the ASX200 index by using the following formula:

Returnt=Pt-Pt-1Pt-110012,where Pt is the share price (or the value of ASX200 index) in time period t. (Please note that you lose the first observation when calculating returns.)

(a) Using the annualized monthly returns, calculate each company's average return on shares from February 2020 to December 2022. Compare them with the average return on the ASX200 index.

(b) Suppose an investor created an equally weighted portfolio of these five companies (i.e. invested 20% of funds in each company). What would be the average return on the portfolio? Explain how you derived your answer.

Question 2: Volatility [10 Marks = 3 + 3 + 4 marks]

(a) Calculate the standard deviation of returns for the five companies. What does the standard deviation tell us about the overall risk of these companies?

(b) Compute the standard deviation of the equally weighted portfolio of these five shares. Is it higher/lower compared to the standard deviation of the individual companies? Provide a brief explanation of your finding.

(c) Compute the standard deviation of returns of the ASX200 index. Is it higher/lower compared to the standard deviation of the individual companies and the equally weighted portfolio? Provide a brief explanation of your finding.

Question 3: Betas [10 Marks = 2.5 + 7.5 marks]

(a) Based on the data provided find an estimate of beta for each company.

For this you will have to first calculate the covariance of a companys returns with the ASX200 index. Use the following formula in excel:

=covariance(Return of company X,Return of ASX200)

Betas can then be calculated by

X=Covariance(Return of Company X, Return of ASX200)Variance (return of ASX200).(b) Briefly explain why each company has high/low values of beta.

(300 words)

Question 4: Forecast & Investment strategy [18 Marks = 8 + 10 marks]

The research department of Tri-Star Management has come up with the following forecast of the share prices and the ASX200 index for the month of December 2023:

They have also concluded that in 2023, the annual risk-free rate on average will be 3.87%.

Based on the forecasts above, write a short report for the clients of Tri-Star Management. Your report should have two parts.

[3 + 3 + 2]

(a) The first part approximately 400 words long should contain: (i) an assessment of the risk and returns of each company, (ii) an evaluation of whether a share is overpriced, underpriced or correctly priced and (iii) your recommendation regarding investment in these companies.

(400 words)

[2 + 2 + 6]

(b) The second part approximately 800 words long should answer: (i) Would you recommend the clients of Tri-Star Management invest in an equally weighted portfolio of these five companies? (ii) How does the risk-return trade-off of the equally weighted portfolio compare to the individual companies? (iii) Would you recommend an alternative portfolio to the clients of Tri-Star Management? Provide a brief explanation.

(800 words)

END OF WRITTEN ASSIGNMENT

  • Uploaded By : Pooja Dhaka
  • Posted on : November 12th, 2024
  • Downloads : 0
  • Views : 50

Order New Solution

Can't find what you're looking for?

Whatsapp Tap to ChatGet instant assistance

Choose a Plan

Premium

80 USD
  • All in Gold, plus:
  • 30-minute live one-to-one session with an expert
    • Understanding Marking Rubric
    • Understanding task requirements
    • Structuring & Formatting
    • Referencing & Citing
Most
Popular

Gold

30 50 USD
  • Get the Full Used Solution
    (Solution is already submitted and 100% plagiarised.
    Can only be used for reference purposes)
Save 33%

Silver

20 USD
  • Journals
  • Peer-Reviewed Articles
  • Books
  • Various other Data Sources – ProQuest, Informit, Scopus, Academic Search Complete, EBSCO, Exerpta Medica Database, and more