Question $5$: Cost of Equity Case Study $($Excel$)$

In the earlier data case, you relied on the beta estimate for Walt Disney provided by Yahoo! Finance. You decide to dig deeper and estimate Disney$$s equity beta on your own.

$1.$ Get the monthly adjusted closing prices for Disney and the S&P $500$ from Yahoo! Finance $($finance$.$yahoo.com$)$ from $2013$ January to $2023$ November. $($The symbol for the S&P $500$ in Yahoo! Finance is $^$GSPC$.)$

$1.$ Get the three$-$month Treasury Bill rate from the Federal Reserve Web site $($www$.$federalreserve.gov$/$datadownload$/$Choose$.$aspx?rel$=$H$15).$ Click the Build Package button and make the following selections:

$1.$ Series Type$$Selected Interest Rates

$2.$ Instrument$$U$.$S$.$ government securities$/$Treasury bills $($secondary market$)$

$3.$ Maturity$3-$month

$4.$ Frequency$$Monthly

Click Go to Package and then Format Package. Select Dates$$From $2013$ January to $2023$ November, and select File Type$$Excel$.$ Click Go to download and Download file. Then open and save these rates to an Excel file.

$2.$ To convert the Treasury Bill rate to a monthly rate, take the yield and divide it by $100$ to convert it to a decimal. Then divide the decimal by $12.$ The resulting rate will be the monthly risk$-$free return in the CAPM $($you may need to re$-$sort the dates to match the Yahoo! data$).$

$3.$ Create separate return columns that compute the excess returns for Walt Disney and the S&P $500.$ Recall that the excess return is the actual monthly return minus the risk$-$free rate.

$4.$ Compute the beta of Disney stock using the SLOPE function in Excel. How does it compare with the beta currently reported by Yahoo! Finance? Why might the results differ?

$5.[$New concept$]$ Compute Disney$$s alpha over this period using the INTERCEPT function in Excel. How might you interpret this alpha?