finance engineering questions
Note: Please upload your Excel file (in the .xlsx format – do not use the GetFormula function I used in my examples - include your name in the file name) on Moodle using the corresponding link under Week 8. Start with a new Excel file and create additional sheets within your file as appropriate. Please make sure you use cell references wherever appropriate, show your inputs at the top like I did in my examples, show and do all of your work directly in Excel, and answer all questions (you may have to type written answers to some questions if appropriate). After you upload the file, double-check the file you uploaded and make sure it contains all of your answers; let me know immediately in case there is an issue with the file you uploaded.
WACC (based on Chapter 13) Estimate the cost of capital for PepsiCo Inc. (ticker PEP), using income statement, balance sheet, and stock market data from www.morningstar.com, and using the historical stock price data for PEP 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 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 Pepsi’s cost of equity and beta.
2. Create a second sheet, where you use the capital asset pricing model to estimate Pepsi’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 Pepsi’s beta, using historical prices with DAILY frequency for the most recent YEAR. Use finance.yahoo.com to download the prices for PEP 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 PEP 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 Pepsi’s cost of equity smaller or larger than Pepsi’s cost of debt? Is that what we would expect? Explain your answer!