Business Finance (Problem based Case Study)

profileTazim
2019FIN2051.xlsx
Home>Business & Finance homework help>Accounting homework help>Business Finance (Problem based Case Study)

Data FIN510

Date S&P/ASX200 AGL.AX ($A) ANZ.AX ($A) NAB.AX($A) QAN.AX ($A) WBC.AX ($A) Returns in % Date ASX200 AGL.AX ($A) ANZ.AX ($A) NAB.AX($A) QAN.AX ($A) WBC.AX ($A) Portfolio (equal weight) ASX200 AGL.AX ($A) ANZ.AX ($A) NAB.AX($A) QAN.AX ($A) WBC.AX ($A) Portfolio (equal weight)
Jan-15 5928.80 12.39 28.77 28.55 2.89 28.55 Jan-15 Mean
Feb-15 5891.50 12.76 29.83 29.03 3.12 29.59 =(100*(Pt-Pt-1))/Pt-1 Feb-15 -0.63 Variance
Mar-15 5790.00 12.75 27.67 27.69 3.39 27.39 Mar-15 Standard Deviation
Apr-15 5777.20 13.62 27.02 26.22 3.52 25.21 Apr-15 Covariance
May-15 5459.00 13.05 26.91 26.20 3.16 25.01 May-15 Beta
Jun-15 5699.20 14.00 27.32 27.35 3.75 26.93 Jun-15
Jul-15 5207.00 14.18 23.34 24.52 3.36 24.56 Jul-15 Expected return (monthly) 0.52
Aug-15 5021.60 13.69 22.63 23.58 3.72 22.94 Aug-15 Required return
Sep-15 5239.40 14.34 22.74 23.72 3.70 24.41 Sep-15
Oct-15 5166.50 14.19 22.69 23.12 3.41 25.01 Oct-15 Excel formulas
Nov-15 5295.90 15.50 24.21 24.60 3.84 26.92 Nov-15 Mean =AVERAGE(K3:K37)
Dec-15 5005.50 15.94 20.96 22.53 3.64 24.75 Dec-15 Variance =VAR.S(K3:K37)
Jan-16 4880.90 15.72 19.41 20.42 3.62 23.06 Jan-16 Standard Deviation =SQRT(U3)
Feb-16 5082.80 15.92 20.33 22.15 3.82 24.35 Feb-16 Covariance =COVARIANCE.S(Array 1,Array 2)
Mar-16 5252.20 15.95 21.04 22.96 3.02 24.91 Mar-16
Apr-16 5378.60 16.21 22.08 22.92 2.89 24.63 Apr-16 Risk free rate (monthly) 0.125
May-16 5233.40 16.82 21.59 22.23 2.64 24.31 May-16
Jun-16 5562.30 17.95 23.13 23.20 2.96 25.71 Jun-16
Jul-16 5433.00 16.18 24.08 23.90 3.04 24.36 Jul-16
Aug-16 5435.90 16.93 24.73 24.36 2.93 24.41 Aug-16
Sep-16 5317.70 17.05 24.93 24.47 2.93 25.20 Sep-16
Oct-16 5440.50 18.65 25.43 25.29 3.16 25.86 Oct-16
Nov-16 5665.80 19.63 28.02 26.93 3.19 27.52 Nov-16
Dec-16 5620.90 20.09 26.98 27.50 3.27 27.02 Dec-16
Jan-17 5712.20 21.44 28.47 29.01 3.59 28.72 Jan-17
Feb-17 5864.90 23.84 29.31 30.23 3.73 29.88 Feb-17
Mar-17 5924.10 24.19 30.18 30.83 4.14 29.88 Mar-17
Apr-17 5724.60 23.82 25.80 27.31 4.89 25.99 Apr-17
May-17 5721.50 23.05 27.17 27.67 5.58 26.78 May-17
Jun-17 5720.60 21.78 28.03 28.00 5.19 27.93 Jun-17
Jul-17 5714.50 21.68 27.81 28.24 5.58 27.45 Jul-17
Aug-17 5681.60 21.58 28.00 29.45 5.69 28.02 Aug-17
Sep-17 5909.00 23.34 28.30 30.54 6.08 28.96 Sep-17
Oct-17 5969.90 23.08 26.92 27.67 5.60 27.62 Oct-17
Nov-17 6065.10 22.50 27.92 28.54 4.98 28.32 Nov-17
Dec-17 6037.70 21.65 27.77 28.12 5.21 27.97 Dec-17
Forecast Expected return (monthly)
Jun-18 6227.60 20.81 29.22 28.20 6.72 27.47 =(100*(B40-B37)/B37)/6 0.52