Assignment Detail:- ACST6003 Principles of Finance - Macquarie University
Corporate Valuation Report
You have been appointed as an Intern for a Consulting company headquartered in Sydney CBD- As part of your role in the area of Financial Valuation team, you have been assigned to examine three companies: Woolworth Limited -WOL-, Woodside Petroleum Limited -WPL- and CSL limited -CSL- performance- Your research team has extracted the raw price data and dividend data of the companies from website- Your role requires you to use research, analysis, application of financial models to prepare a valuation report- Your supervisor has given you a set of questions to assist financial valuation team to prepare the report with data description as shown below-
Data Description
Excel file "PART A-acst6003-assignment-xlsx" contains the following worksheet:• A worksheet name "Price_WOL" for the period 2005 - 2021-• A worksheet name "Price_WPL" for the period 2005 - 2021-• A worksheet name "Price_CSL" for the period 2005 - 2021-• A worksheet name "Div data" for the period 2005 - 2021-• Annual yields on 30-day Treasury bill rate -worksheet name "price data"-• ASX 200 Index -worksheet name "price data"-
For the purposes of this report, when you look at the prices of all companies only consider the data column "Adj Close price"-
List of Parts
Q1A- Your research team recommends calculating the annual growth rate in dividend for all three
shares using the formula -gt = Dt/Dt-1 - 1-- Also compute the average of the annual growth in dividend for the entire 16 years to complete the blank table provided in worksheet "Q1"-
Q1B- Compute the monthly returns for the ASX200 Index -Rm-, WOL -RWOL-, WPL-RWPL- & CSL-RCSL- using the following formula Rt = 100 × -pt/pt-1 - 1-- You are also required to compute a monthly 30-day Treasury-bill -T-bill- rate as follows: Rrf = Ranual/12-
Q1C- Compute E-R- and standard deviation for all four returns series over the 01/02/2005 - 1/12/2021 time period- You are also required to compute the sharp ratio for WOL, WPL, CSL & market index- Comment on your analysis-
Q2A- Compute excess returns for the market, -i-e-,ReASX200 = RASX200 - Rrf, where RASX200 is the return on the market, and Rrf represents the monthly return on the 30-day T-bill rate- Also compute excess return for WOL, WPL and CSL as follows ReWOL = RWOL- Rrf, ReWPL = RWPL - Rrf and ReCSL = RCSL - Rrf
Q2B- Using CAPM formula stated below run the regression model for WOL, WPL and CAL- Comment on systematic risk parameter of the regression output-
Where:• Rrf is the average of the monthly risk free rate• E-RASX200- is the average of the market return
Q2C- Plot the excess returns of each company against ASX200 market index in three different scatter plots with best fitted line and equation-
Q3A- Compute the expected returns for the three companies using CAPM formula- Comment on which share you should buy or sell and provide justification
Q3B- Compute the effective annualised return of the three companies -i-e-, WOL, WPL and CSL--
Q3C- Calculate the share price of the WOL and WPL at time 2021 if the average of the annual growth in dividend is expected to grow at constant rate specified in Q1A from 2022 onwards infinitely- Your financial analyst team expect the average of the annual growth in dividend to decline at constant rate for CSL from 2022 onwards indefinitely- Compute the share price of CSL at time 2021- You should use the dividend in 2021 to complete this task- Show all working- Round up final answer to the nearest dollar-
Q4A- Assume all the three shares are normally distributed and the returns and standard deviation are as follows:
Shares
Returns
Standard deviation
WOL
80
5
WPL
65
8
CSL
98
6
Compute the range of possible returns for the three investments with 95 percent certainty if you consider all three shares for investment purpose- Show all working using formula- Comment on the results-
Q4B- Your friend Ella-Rose wishes to invest 50%, 30% and 20% of her overall wealth in each of the three shares WOL, WPL and CSL respectively- Explain in plain and/or technical language using formula how you would calculate her portfolio expected return and standard deviation-
Attachment:- Principles of Finance-rar
Most Recent Questions