Question

Question 1: Bivariate analysis

reviewed).

Stock prices often correlate with the stock market index and tend to change as the market index changes. Investigate the relationship between the market index and each of the two stock prices included in the dataset, by following these steps:

• Determine the explanatory and response variables.
• Run a regression analysis for each of the two stock prices against the index price.

Interpret the results by answering the following questions:

• Plot each of the two stocks against the market index on a scatterplot. Include these scatterplots in your submission. What are the expected correlations that might exist between each of the stocks and the market index?
• What are the calculated correlation coefficients? Compare these to your plots. Were your initial expectations correct?
• Comment on the coefficients of determination. Which of the two models has the higher explanatory power?
• What are the beta coefficients for each of the two stocks?
• Suppose you were to purchase one of these two stocks, and your decision relied on their volatility in relation to the market index (i.e. their beta coefficients); which of the two stocks would you purchase if you were interested in buying a volatile stock?

  Period	Index	Stock 1	Stock 2
  1	£         20.00	£         22.00	£         25.00
  2	£         50.00	£         47.00	£         35.00
  3	£         30.00	£         33.00	£         55.00
  4	£         80.00	£         85.00	£         55.00
  5	£         50.00	£         75.00	£         60.00
  6	£       100.00	£       105.00	£         90.00
  7	£         70.00	£         75.00	£       100.00
  8	£         80.00	£         88.00	£       105.00
  9	£       140.00	£       120.00	£       115.00
  10	£       105.00	£       140.00	£       150.00
  11	£       135.00	£       130.00	£       120.00
  12	£       120.00	£       128.00	£       150.00
  13	£       170.00	£       150.00	£       145.00
  14	£       150.00	£       165.00	£       175.00
  15	£       200.00	£       180.00	£       190.00
  16	£       160.00	£       185.00	£       175.00
  17	£       180.00	£       185.00	£       195.00
  18	£       220.00	£       215.00	£       225.00
  19	£       200.00	£       230.00	£       240.00
  20	£       230.00	£       245.00	£       220.00
  21	£       220.00	£       240.00	£       260.00
  22	£       270.00	£       265.00	£       260.00
  23	£       240.00	£       280.00	£       290.00
  24	£       300.00	£       280.00	£       260.00

Answer 1

I have coppied given data into excel and convert it into number format.

First to plot Scatter plot, I select Index and Stock 1 column and then in INSERT tab i use scatter plot.

same for Stock 2 i selected Stock 2 and Index to plot scatter plot

1) To answer :What are the expected correlations that might exist between each of the stocks and the market index?

From both the graph we can se stocks are highly possitive correlate with Index hence correlation will be grater than 0.7.

2) What are the calculated correlation coefficients? Compare these to your plots. Were your initial expectations correct?

To calculate actual correlation coef. i use formula in Excel(given in below image).

We can as we have said correlation coef for both the stocks with index in highly positive correlated.

3)Comment on the coefficients of determination. Which of the two models has the higher explanatory power?

To do this we have to fit reggression model for both stock with Index.

I have used excel to do this.

Follow this step to do it in Excel: Go to Data tab in Excel->Data Analysis -> scroll to get "Reggression" ->on Y axis put Stock 1 and on X axis put Index. and the Run it. you will get following result.

Repeate same step for Stock 2:

You can see from both the model R2(Coef of determination) for Stock 1 is 0.9522 and R2(Coef of determination) for Stock 2 is 0.8973, Hence Model 1 with stock 1 and indexhas higher explanatory power.

4)What are the beta coefficients for each of the two stocks?

Form above image Beta for Stock 1 is 0.98096 and Beta for Stock 2 is 0.95969.

5)Suppose you were to purchase one of these two stocks, and your decision relied on their volatility in relation to the market index (i.e. their beta coefficients); which of the two stocks would you purchase if you were interested in buying a volatile stock?

High volatile means high Beta value.So,model with high beta is Stock 1 model with Beta=0.98096.In order to select high volatile stock we sholud select stock 1.

Hope this will help you.

Please give like if my answer is helpful.

Cheers!!