1. WACC Calculation Scatter Plot
So we've discussed beta, but there's a quick recap. Beta measures how an
individual stock changes with the overall change in the stock market.
In this case, we aren't looking for absolute changes, but percentage changes.
Basically beta is the slope of the best fit line between the two
data points. And while we can pull a company's beta from various financial
data providers, in this case, we want to illustrate how beta is actually
calculated. So it's calculated using a statistical linear regression,
but we need to determine which is the independent variable and which is
the dependent variable. The independent variable or X variable
is the return on the stock market. The dependent variable is the individual
stocks return or Y variable. Now that we know
the proper variables, there are several ways to perform a regression analysis
in Excel. We're only going to look at two of them.
We can create a scatter plot, which you can see on the WACC worksheet,
or we can use the slope function in Excel.
So even though the scatter plot is built for you, let's walk through
the creation one as well. So let's move over to the WACC tab
to do this. So to create a scatter plot in Excel,
we go to Insert, click here for scatter, and then we'll just choose
the first one, and it puts in a blank...
Effectively, blank chart. That's not the data we want, but we'll fix that
here in a minute. Let's just go ahead and get rid of the
chart title and the legend there at the bottom.
Now what we can do is we can actually go in and select
our data, 'cause that's not the right data,
so we're going to remove those, we're going to add,
and we need our series X value, so again, we need the X
variable, which in this case is on the info for beta
tab, and it's the weekly percentage change on the index.
So we'll select that entire bunch of data from row D6 to D316, and
then the y values, the y variable is in column F,
so we do F6 to F316, hit okay.
Okay, again, and you could see we have our scatter plot. So let's
go ahead and clean up the chart some more. We can select the
data, and maybe we want to change colors,
so we could do Ctrl 1, to access our format data,
and in this case, we'll say No line, we need to choose Marker,
we'll do No line for border, but the Fill Color we want a solid
fill, and we'll just use the CFI dark blue,
so it matches the already complete chart. Then we wanna go ahead and
get rid of the lines. Like so, so we just select these vertical
lines that are already inserted, just hit delete, we wanna get rid of
the horizontal lines as well, so select them, hit Delete, and let's just
go ahead and get rid of the border as well. So border will
select No Line, was out of that, and now our chart is beginning
to look like the already completed chart. The next thing we want to
do is add a trendline. So just click the chart, go to chart
design, add chart element, go to trendline and select linear.
And it's a little hard to see but there is a light blue
dash line in there. Let's go ahead and format that so it stands
out a little bit more. So select the line, we go to format,
let's first just make it a lot thicker,
so we'll choose 4 1/2, and let's just change the color as well,
we'll use the CFI bright orange like so.
Now you can see the trendline. Now, what we want to do is
we want to add equations to that trendline. You can see that we
have some equations in the already completed scatter plot. The way you add
these equations is again, we'll go to, make sure your chart is select of
course, go to chart design, back to add chart element, down to trendline,
then we'll do more trendline options. Scroll down, and we want to check
Display equation on chart and display r squared value on chart.
We'll close out of that. And again, it's hard to see,
but we now have the y equation and the r squared equation in
our chart. Let's make that a little easier to see.
We'll use the bright orange CFI, and again, now we have our equations.
So what do these equations actually mean? Well, the y equation,
that's just the slope of the line, y equals mx plus b,
like we learned in math many years ago,
and we can see that that number 0.9085 next to the x,
that's actually the beta or the slope of this trendline. Then r square
is a measure of the goodness of fit
between the independent variable and the dependant variable. So the r square
is a little on the low side, safe to say,
but we do have our beta 0.9085. But there's an even simpler way
to calculate beta using the slope function in Excel.
2. Let's practice!