Assignment: WACC
Estimate the cost of capital for Ford Motor company (ticker F), using income statement, balance sheet, and stock market data from morningstar website, and using the historical stock price data for F and VFINX from yahoo finance website. (Do not use balance sheet and income statement from yahoo finance website, it has errors in it. Make sure you use year 2018 data from the balance sheet and income statement on Morningstar - do not use the TTM column.)
1. Create a sheet in your Excel file that estimates the weighted average cost of capital, listing the necessary inputs at the top of your sheet. Obtain any necessary data from the sources listed above. Link to other sheets in your file that will calculate Ford's cost of equity and beta.
2. Create a second sheet, where you use the capital asset pricing model to estimate Ford's cost of equity, assuming 3.5% risk-free rate and 5.5% market risk premium. Link to a separate sheet with beta estimation.
3. Create a third sheet, where you estimate Ford's beta, using historical prices with WEEKLY frequency for the most recent 3 YEARS. Use yahoo finance website to download the prices for F and VFINX (make sure you use the ADJUSTED CLOSE price). Use the SLOPE function to estimate beta. Insert a scatter chart that shows the trendline from regressing returns of F on the VFINX returns, displays the estimated equation, and the R-squared (check the boxes to display the equation and the R-squared).
4. In the context of your regression analysis shown on the chart EXPLAIN AND INTERPRET THE MEANING OF
a. THE SLOPE COEFFICIENT FROM THE ESTIMATED EQUATION.
b. THE R-SQUARED FROM THE ESTIMATED EQUATION.
You may need to look up the meaning of the slope coefficient and of R-squared in a regression analysis if you are not familiar with regressions. Make sure your interpretation is specific to your variables and results.
5. Is Ford's cost of equity smaller or larger than Ford's cost of debt? Is that what we would expect? Explain your answer!
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.