50.1K

Verified Solution

Link Copied!

Estimate the weighted average cost of capital for Procter & Gamble Co. (ticker PG), using the income statement and balance sheet data for year 2021 for PG from www.morningstar.com, and using the historical stock price data for PG and VFINX from www.finance.yahoo.com. You will also need to look up the market cap for PG. (Do not use balance sheet and income statement from finance.yahoo.com, it has errors in it. Make sure you use year 2021 data from the balance sheet and income statement on Morningstar do not use the TTM column.) Note: Follow the video example on Moodle!

1. Create a sheet in your Excel file (Q5a) that estimates the weighted average cost of capital, listing the necessary inputs and calculations. Obtain any necessary data from the sources listed above. To estimate PGs cost of equity, use the Capital Asset Pricing Model, assuming 2.5% risk-free Trine University, Fall 2021, Dr. Kolar Page 3 rate and 5.5% market risk premium. Calculate your own beta (see below). Link to the beta, calculated on sheet b, using a cell reference.

2. Create a second sheet (Q5b), where you estimate PGs beta, using historical prices with DAILY frequency for the following dates: starting date 07/01/2020, and ending date 07/01/2021 (note: input the dates 7/1 using the calendar icon, when you click done the dates showing may be 6/30, thats okay). Use finance.yahoo.com to download the prices for PG 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 PG on the VFINX returns, and displays the estimated equation.

Please answer in excel.

Answer & Explanation Solved by verified expert

See Answer