In this workout. We are asked to calculate the correlation between the Facebook and Ford stock price performance over the course of the 2021 year where we have Returns on a monthly basis.

To be able to calculate the correlation. We first of all need to calculate the mean for the stocks on an individual basis. So we need to take the average function so the average of those 12 months returns.

For Facebook and for forward respectively.

We then also need to calculate the standard deviation.

and to calculate the standard deviation we're going to use the standard deviation function, but we're going to use the sample calculation here So the sample standard deviation will calculate for Facebook.

Which will give us 6.4.

And then 6% as the standard deviation.

For Ford these numbers will be relevant for us as we go on through the calculations.

The next stage of the correlation calculation is the calculate the difference between each month's return.

And the average or mean return for the whole year for the whole set of data.

Here effort to lock onto c22 here so that we can then just copy this all the way down for.

Facebook and then we'll do the same thing for Ford to look at the difference between the return in a month.

and the overall mean and by looking onto d22 with our F4 function, we can then copy this down to calculate it for every one of our months.

The final calculation that we need to do on a month by month basis is to multiply together the difference from the mean for Facebook by the difference from the mean for forward for each of those months.

And then copy this down for each of the 12 months that we have for 2021.

Moving on to the calculation of our correlation coefficient. We then need to add up all of the product or multiplications of the difference in Facebook's return per month from Its main by Fords difference from its return per month by its main.

To give us the sum of the product of the variances.

We then need to identify the number of observations that we have where we have 12 months in the year. But since we're doing our calculations on a sample basis, we then need to subtract one way from this number of observations.

We can now go on to calculate the covariance. The covariance is calculated by taking the product of the variances and dividing that by our number of observations for Ford and Facebook that gives us a covariance of 4.6. This in itself doesn't mean anything other than the fact that it's positive.

The fact that the covariance is positive tells us that there is a positive relationship between these two variables.

Or in other words, they generally move in the same direction.

To be able to understand the strength of that relationship with any to go on to look at the correlation coefficient and the correlation coefficient is calculated by taking the covariance and dividing this by the standard deviation of each of the two component parts multiply together.

This gives us a correlation of positives 0.12.

So it's telling us that there is a positive correlation between the performance of Ford and Facebook's stock price, but it's not a very strong correlation at all. Given that the correlation coefficient can range anywhere from -1 to plus one. We can effectively say here that we have almost no correlation between these two variables.

correlation can be arrived at using the Excel function of Co r r e l and for that we need to identify the underlying data sets individually. So the array one is the monthly Returns on Facebook.

And a Row 2 is the monthly Returns on four.

So again, give us the same correlation coefficient between these two variables as we had calculating it from first principles.