1. Calculating WACC Slope Function
Now, let's go ahead and use the slope function in Excel to calculate
beta. So we'll do equals slope, open the bracket,
and in this case, the first argument in this function is Excel is
asking for what is the Y variable. So the Y variable is the
individual stocks percentage change. The X variable is the independent variable.
That's the percentage change in the overall market.
So all of that data is on the info for beta tab.
So we can do Control Page Up. We'll select our Y variable
first. Again, it is the weekly percentage change on the stock,
comma, and then we will enter in the
percentage change in the overall market. That's our X variable.
So we'll select that data then we can press enter.
And you can see we have a beta 0.9085, which of course matches
the 0.9085 on our scatter plot. So now that we have beta, we
can actually go ahead and calculate the weighted average cost of capital.
We've given you the risk free rate, the equity risk premium,
we have beta. So we can calculate the cost of equity using the
capital asset pricing model. Take our risk free rate
plus our equity risk premium, times beta. So we have a cap M cost
of equity of 8%. Now let's go ahead and calculate our after tax
cost of debt. Again, remember, the yield on debt
is a company's true cost of debt, but because of interest expense typically
being tax deductible, we really want the after tax cost of debt.
So we take our yield, we'll multiply that by one minus the tax
rate for an after tax cost of debt of 3.9%.
Next, we actually need the weightings for our weighted average cost of capital.
We gave you the value for equity and the value for debt.
So our weighting is equity divided by equity, we'll anchor that with F4,
plus our debt financing. Go ahead and anchor that.
Close the bracket, 72.5% equity weighting and we can copy that,
Control C and then Control V, everything sums to 100. So now we
have our weightings in order to calculate the weighted average cost of capital.
So with our cost of equity, our after tax cost of debt,
our equity weighting, and our total capital structure and our debt weighting
and our capital structure, we can now calculate our WACC. So we'll take
the cost of equity, we'll multiply it by the
equity weighting, and we'll add the after tax cost of debt times the
debt weighting, and we arrive at a WACC of 6.8%.
And finally, we already demonstrated how to get an R squared using trend
lines. We have an R squared in the trend line in the scatter plot that
was already created for you. Of course, we created one together.
We also added the R squared there. But we can also use the
RSQ function in Excel to get the R squared. So we'll do RSQ. And
again, we need to pull in the dependent variables, the Y variables,
that is the percentage change in the individual stock price.
Then we'll reference the independent variable, the X variable, which is
the percentage change in the overall market price.
So we'll go back to the info for beta tab.
So again, we want to pull in the percentage change or the stock,
do comma, go back up to the top.
Then we will select the X variable, the independent variable,
the change in the stock market. Press enter and you'll see we have
R squared of 0.0899 and it matches the R squared in both of
our scatter plots. But remember that R squared measure
takes into account, it says, "Okay, how well does the data fit the
statistical regression model?" The higher the R squared, the better the
relationship. The lower the R squared, the worse the relationship.
In this case, we have a pretty low R squared.
So in that case, we may want to consider using an industry beta.
2. Let's practice!