I would really appreciate if you can add excel file or show the excel calculations.

Estimate the weighted average cost of capital for Caterpillar, Inc. (ticker CAT), using the income statement and balance sheet data for CAT from www.morningstar.com, and using the historical stock price data and the market capitalization data for CAT and VFINX from www.finance.yahoo.com. (Do not use balance sheet and income statement from finance.yahoo.com, it has errors in it. Make sure you use year 2019 data from the balance sheet and income statement on Morningstar do not use the TTM column.) Note: From Morningstar, go to financials, access the balance sheet and income statement for year 2019, use interest expense net of capitalized interest for interest expense (it will be showing as a negative number it simply means the company paid this amount in interest expense), and use longterm debt for debt (do not include capital lease, ignore short-term debt this is to make it simpler for you). You will also need pre-tax income and provision for income tax to calculate the tax rate. To get the market value of equity use market cap from the summary page on Yahoo Finance.

1. Create a sheet in your Excel file (Q5a) 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 CATs cost of equity and beta.

2. Create a second sheet (Q5b), where you use the capital asset pricing model to estimate CATs 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 (Q5c), where you estimate CATs beta, using historical prices with WEEKLY frequency for the following dates: starting date 07/01/2016, and ending date 07/01/2020 (note: input the dates 7/1 when you click done the dates showing will be 6/30, thats okay). Use finance.yahoo.com to download the prices for CAT 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 CAT on the VFINX returns, and displays the estimated equation.