Correlation of BHP and CBP and CSL †Free Samples to Students

Question: Discuss about the Correlation of BHP and CBP and CSL. Answer: Introduction: The cell B66, C66 and D66 portrays the overall average returns of the three companies, which is derived from the formula in Excel [=AVERAGE(B3:B64)]. Moreover, the same equation used on column C and D to derive the average returns of the stock. In the same stances, equation [=(PRODUCT(E3:E64)^(1/COUNT(E3:E64)))-1] is used to derive the overall Geometric mean of the companies. The cell B67, C67 and D67 has the geometric mean of the stocks used in the assessment. The same equation of geometric mean is used in all the three stops to derive the mean of the organization which will be used for constructing the portfolio. In addition, standard deviation is calculated with the help of =(SUM(H3:H64)/COUNT(H3:H64))^(1/2) equation for all the three stocks. The cell B68, C68 and D68 has the standard deviation value, which will be used in deriving the portfolio. The correlation of BHP and CBP and CSL is relatively calculated in column B71 by using the following formula: =((COUNT(K3:K64)*SUM(K3:K64))-(SUM(B3:B64)*SUM(C3:C64)))/((((COUNT(N3:N64)*SUM(N3:N64))-(SUM(B3:B64)^2))*((COUNT(O3:O64)*SUM(O3:O64))-(SUM(C3:C64)^2)))^(1/2)) . The correlations between BHP CSL and CBP CSL are calculated in the same manner. The formula used for computing the covariance between BHP and CBP in the cell E71 is as follows: =SUM(T3:T64)/(COUNT(T3:T64)-1) The same formula is applied to derive the other two covariances. Portfolio was used in the creation of efficient Frontier are placed in N10:N12, where adequate portfolio is created by using mean standard deviation and variance as their constraint. Weights are mainly calculated by using solver function of the excel, which detects the equalized portfolio value. In addition, the subject to constraints in the solver equation needs weights of each stock to be higher than zero and total weights to be 100%. This constraint mainly helps in detecting the actual variance and return that will be provided from the stock.The portfolio has Same return with Minimum Risk, which needs to be adjusted in the portfolio. In addition, the changes in weights is conducted by solver to by using the constraint of weights of each stock to be higher than zero, total weights to be 100% and minimum variance needs to be calculated. Moreover, weights are depicted in section N18:N20 for the minimum risk portfolio. The use of solver can be conducted for identify portfolio with Higher return with Minimum Risk, which can be conducted with adequate constraints in solver equation. The weights are depicted in section N26:N28. In the same condition and constraint tangent portfolio is calculated, where the weights are depicted in S26:S28. The slope is calculated with the equation [=(U25-U28)/U26]. In addition, the total weights are calculated in CSL, which is identified to be the adequate stock for tangent portfolio. The weights of S18:S20 is calculated with the help of solver equation, which helps in calculation the minimum variance portfolio and has the least risk involved in investment. The equation of solver discussed above can be conducted to identify the actual minimum variance portfolio. The Covar is calculated by using the function =MMULT(MMULT(TRANSPOSE(N18:N20),N3:P5),N26:N28), while the weight is detected to be 50%. In addition, the mean is calculated by using =(N34*P17)+((1-N34)*P25) and standard deviation equation is =SQRT(((N34^2)*(P18^2))+(((1-N34)^2)*(P26^2))+(2*N34*(1-N34)*N33)). The weight of portfolio is detected at 50%, while the mean is calculated from the equation =(S34*U28)+((1-S34)*U25) and standard deviation is calculated by suing =(1-S34)*U26 equation. Bibliography: Chandra, P. (2017).Investment analysis and portfolio management. McGraw-Hill Education. HA Davis, M., Lleo, S. (2015).Risk-Sensitive Investment Management