In this workout. We are looking at an investor who is considering an investment into XYZ stock.

While she believes the company produces products that is superior to its competitors and has strong competitive advantages. She is concerned about how the overall Market environment would affect the returns if they were to invest in XYZ stock.

We're asked to determine the systematic risk of the investment by comparing the returns of XYZ.

to the returns of the market and then to describe the relative riskiness of XYZ stock.

We're presented with a lot of data here in terms of the relative returns of the market. So in month one a loss of 5% on the market as a whole and XYZ stock generated a return of negative 3.6% We have a lot of data here. We have 60 months or five years worth of returns and that's quite hard to look at the data and to draw any conclusions. So the way in which we can identify the systematic risk or the beta is relatively straightforward within Excel or we need to use is the slope function.

The slope function asks us to identify the known y's. So the y-axis variable here is the dependent variable which is the x y z stock this depends on the returns of the market as a whole.

And if we hit comma and we'll ask for the x's and the x is the independent variable the element that moves on its own which is the market as a whole.

So here what we're trying to identify is as the market moves. How does XYZ stock respond to that? And this slope function gives us a conclusion that we have a beta of 1.2.

As the market moves, we'd expect XYZ stock to move by 1.2 times.

So in terms of describing what xyzstock looks like we can say that x y z stock is riskier than the overall markets.

Given that has a beta of more than one.

As the market moves our stock XYZ is going to move by more than that amount both up and down.

So we could say similarly if the market were to go down by 10% this beta calculation estimates that x y z stock will decrease in value by 12% We can also represent this data graphically if we select all of the data.

And then insert a chart.

I in alt and r so into a recommended chart because you can see we have a scatter plot here.

On the scatter diagram we can see that there is a positive relationship between the Returns on the markets on the x-axis or the horizontal axis and the Returns on x y z stock on the vertical axis or the y axis.

Identify the slope of this line. We can click on the plus symbol to add a chart element and add a trendline.

So we now have the slope of our line.

and then if we go into the format trend line option.

We can then scroll down on this right hand menu.

to display the equation on the chart this then gives us that the XYZ return can be calculated by taking 1.19 times the x-axis value the return of the market and then adding on this constant value of 0.013 or another words. The slope of that line is 1.1903 as calculated by the slope function.

This equates to the beta of this stock.