Excel Project
Part 1
1. Download daily price data from July 31, 2020 through August 1, 2021 for the Vanguard S&P 500 ETF
(ticker:VOO,) Catepillar (ticker:CAT,) and Proctor & Gamble (ticker:PG.)
2. Using the adjusted daily price, calculate the daily return and convert to a percentage. (Do not use
the formatting feature of excel to make conversion.)
3. Obtain the Descriptive Statistics/Summary Statistics for the return of all three securities.
4. Download the 5-factor daily data for August 3, 2020 through July 30, 2021 from the Kenneth French
data library.
5. Merge the French data with the return/price data for all three securities.
6. Calculate the daily excess return for all three securities and obtain Descriptive Statistics/Summary
Statistics for the same.
7. Using the daily mean values, calculate the historic minimum variance portfolio for a portfolio
consisting of CAT and PG.
8. Using daily mean values, calculate the historic optimal portfolio for a portfolio consisting of CAT and
PG.
9. Using regression analysis, obtain the betas (coefficients) and alphas (intercepts) for VOO, CAT, and
PG using the CAPM, the F-F 3-factor model, and the F-F 5 factor model. Capture the error terms
(residuals) for each model.
Part 2
Prepare a report of your findings from Part 1. Provide the outputs of the analysis, with
descriptions and explanations of the outputs. The explanations/descriptions should be provided
immediately after the specific output. Do not list all the outputs followed by all the explanations or visa-
versa. The descriptions/explanations can be brief (three to four sentences) and should demonstrate an
understanding of the results being reported.
Provide the following Excel outputs:
1. The summary statistics for the daily nominal return and the daily excess return for VOO, CAT and
PG.
2. The summary statistics for the market return.
3. The correlation matrix for PG and CAT daily excess returns. (If you have other correlations report
those also.)
4. The covariance matrix of PG and Market, CAT and Market, VOO and Market. (If you have other
covariances report those also.)
5. The regression outputs without the residual output for each security using the CAPM, the Fama-
French 3-factor, and the Fama-French 5-factor models.
Report the following:
1. The Covariance/Variance beta for each of the securities.
2. The weights of CAT and PG in a minimum variance portfolio.
3. The weights of CAT and PG in an optimal two-risky asset portfolio.
The document should be readable in its entirety in portrait format. A cover page must be provided.
The cover page should be centered, contain a title, the date of submission, your name, the course
name and course number, and the instructor’s name.
The document must be submitted in .pdf format to the designated link in Blackboard. Do not submit
your Excel workbook or any Excel worksheets.
The report will be graded based on the accuracy of the outputs and the readability of the document.
———————————————————————————————————
Intermediate Investments, FIN36059, Fall 2021
Portfolio Evaluation Project
From the data set provided analyze and evaluate the target portfolio. The minimum analysis should be
calculating the metrics from the list below.
After performing the analysis, prepare a report providing your evaluation of the target portfolio. Your
evaluation must include the reporting of your analysis with 1.) an explanation of each metric, and 2.) your
evaluation of the portfolio’s performance based on the given metric.
0 comments