topic:physics and statistics

Please use Excel spreadsheet for questions (a) to (f) by using the data given the same spreadsheet. Spreadsheet contains a

sample of closing price data for TPG Technologies Ltd, PPT Technologies Ltd, FOX Transportation Ltd and ASX All Ordinaries

Index. You are required to use Excel functions for the calculation of average return, variance, covariance and correlation.
a.    Calculate the Covariance and Correlation of the monthly returns for TPG, PPT, FOX against ASX All Ordinaries Index.
b.    Calculate the correlation of monthly return:
1.    between TPG and PPT
2.    between PPT and FOX
3.    between TPG and FOX
c.    Compare the results in parts (a) and (b) and explain the  potential differences between correlation of TPG and PPT

and  correlation of TPG and FOX
d.    Calculate the betas for TPG, PPT and FOX.
e.    What does beta explain about these securities?
f.    If the relevant yield on government€“issued securities is 3% per annum, calculate the required rates of return for

TPG, PPT and FOX using the Capital Assets Pricing Model.
g.    If you owned a portfolio comprising $20,000 invested in TPG, $20,000 invested in PPT and $20,000 invested in FOX,

calculate the beta and the required return of the portfolio.
h.    Calculate the intrinsic values of shares in TPG, PPT and FOX according to the constant dividend growth model of

ordinary share valuation.
TPG    PPT    FOX
Growth rate    2%    3%    4%
Dividend Paid in 2007    $1.36    $2.56    $0.30

i.    Explain whether shares of these companies were overvalued or undervalued by using the market price given in the data

(2007).

provide details of formulae that they have used for the statistical calculations by printing Excel spread sheet using Excel’s

show formulas function.