Question 2 (based on Week 5 Excel Examples Chapter 10) 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 doesnt 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 PGs 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 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.

Note: The best way to access the balance sheet and income statement data is the following. Go to morningstar.com, pull the company (make sure you pull the PG ticker symbol), click on Key Ratios -> Full Key Ratios Data -> Financials, where you can access the balance sheet and income statement. Note that you can collapse and un-collapse some rows by clicking on the arrows on the left side.