Question 2

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! (This question doesn't require a written answer)

1. Create a sheet in your Excel file (Q2a) 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 PG's

cost of equity, use the Capital Asset Pricing Model, assuming 2.5% risk-free 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 (Q2b), where you estimate PG's 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, that's 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.