Finance

Department of Finance

David Eccles School of Business

University of Utah

Fall 2023

FINAN 4070-Investments

1

15

Project 2 – submission closes at 11:59 pm on Sunday, Dec 10th

Please note that all guidelines below apply to all students and all projects. Guidelines are nonnegotiable.

Guidelines for submissions:

· Projects can be submitted to Canvas from the time the assignment is posted until the deadline. Project 2 must be submitted by Sunday, Dec 10th at 11:59 pm. At 11:59 pm, submissions will close. No late projects will be accepted or graded. To ensure a project is turned in on time, students are encouraged to submit early, incomplete versions of projects periodically leading up to the due date. Only the last project submission before the due date will be graded.

· Hard copies of projects will not be accepted. Only digital submissions will be graded.

· You are expected to cite any and all sources used in the completion of this project. Anything taken directly from the course notes or text should be quoted appropriately. An informal list of resources at the end of the project write-up is sufficient for a works cited page.

· Turn in both a Microsoft Word document of your project write-up (fill in the blanks) and a Microsoft Excel spreadsheet with your analysis by uploading them to Canvas. Only Word documents and Excel spreadsheets will be accepted (no pdfs allowed). You are expected to show all work in your spreadsheet. If I cannot find the work in your spreadsheet, you will receive a 0 for the question, regardless of what is presented in your write-up.

The Excel workbook (file) should have the following spreadsheets (tabs):

· Data (include all data used in the estimations)

· CAPM estimation for The Coca-Cola Company (KO) (output from the regression analysis)

· CAPM estimation for Lululemon Athletica Inc. (LULU)

· Index model estimation for The Coca-Cola Company (KO) using XLP as the index

· Index model estimation for Lululemon Athletica Inc. (LULU) using XLP as the index

· Index model estimation for The Coca-Cola Company (KO) using XLY as the index

· Index model estimation for Lululemon Athletica Inc. (LULU) using XLY as the index

· Fama French model estimation for The Coca-Cola Company (KO)

· Fama French model estimation for Lululemon Athletica Inc. (LULU)

· Analysis of optimal risky portfolios

· If you have difficulty turning your project in via Canvas, email the project to me immediately. If you wait until after the deadline to alert me to a problem, your project will be counted as late and will not be graded.

· Any projects with blatant plagiarism—sentences or phrases directly lifted from the internet without appropriate quotations and citation— will receive a zero for the project and all students involved will be charged with an Honor Code violation.

· I expect students to conform to basic conventions of standard written English. If your writing is riddled with errors, misspellings, typos, random capitalizations, and/or font changes to the point where it becomes distracting to your reader, I reserve the right to deduct up to 10 points from your final grade.

Please round all final answers to four decimal places. Do not round intermediate calculations.

Guidelines for groups:

· Students are expected to complete this project individually or in a group of two or three students.

· You are responsible for forming your own teams.

· You may only work with students in the same section of FINAN 4070.

·

· If you choose to work in a pair or team of three, you do not need to work with your partner or team from project 1. (You can form entirely new partnerships, or work alone – it’s up to you.)

· Each group/pair of students need only turn in one project—please remember to put the names of all student collaborators on the first page.

· Each student in the group will receive the same grade.

· If you have any questions or need assistance, please see me in office hours or contact me. Please keep mind that many students will wait until the last minute to seek help. Come to office hours the week before the project is due and beat the rush.

· I will not “pre-grade” assignments. If you have specific questions, I am happy to answer them. However, I will not look over assignments to see if everything is “right” before the submissions are due.

· There should be no discussion of this project between teams. If you have elected to work alone, the only person with whom you may discuss this project is me. All teams of students are expected to perform the entire analysis (collecting data, analyzing data, and interpreting the results) by themselves. Therefore, illicit collaboration on this project includes, but is not limited to: sharing data between groups, sharing spreadsheets between groups, looking at another team’s spreadsheet for “inspiration,” sharing write-ups between groups, discussing the answers to short-answer questions with a person in another group, discussing any part of this project with students from previous years’ classes, and consulting with or using spreadsheets from students from previous years’ classes. Any discussion of this project outside teams constitutes a violation of the Honor Code.

FREEMAN SCHOOL OF BUSINESS

FINE 4110-05

Investments in Equities

Spring 2018

1

14

Project 2 Write-up

Name ________________________________________

Section I – Data and Descriptive Analysis

1. Download 5 years (January 1st, 2018 to December 31st 2022) of monthly price data from Yahoo!Finance for Lululemon Athletica Inc. (LULU), The Coca-Cola Company (KO) and two ETFs, XLP and XLY. Compute the HPRs to your securities and indexes. Use the Fama French risk free rate to compute excess return. You are going to estimate several models, including a CAPM and Fama French. Please use the Fama French market excess return (Mkt – rf) as your market index for all models. Fill in the following table: (5 points)

(Note: The Fama French data are in percentage format, you need to transform it into decimals for calculation. Also, you need to line up your data so that the dates are correctly matched. The Fama French data can be found in the labs)

Date	Mkt – Rf	SMB	HML	Rf	Excess LULU	Excess KO	Excess XLP	Excess XLY
2/1/2018
3/1/2018
4/1/2018

2. Compute the arithmetic average and sample standard deviation using HPRs (note: not excess returns) and the Sharpe ratio of LULU and KO, as well as the correlations between the HPRs of the two securities and indexes. Assume a monthly risk free rate equal to 0.37%. Fill in the following table: (5 points)

	LULU	KO
Average
Sample standard deviation
Sharpe

Correlations	LULU	KO	XLP	XLY
LULU	1	--	--	--
KO		1	--	--
XLP			1	--
XLY				1

3. Research LULU and KO and give a brief discussion of their industry, competitors, and general business. Try to focus your discussion on aspects of the company pertinent to understanding the firms’ betas. (5 points)

4. Discuss the average returns and standard deviations for each firm (LULU and KO). If possible, relate them to firm specifics. Also discuss the correlation between the two firms. Does the correlation make sense given what you know about the firms? (5 points)

5. Provide a brief discussion of XLP and XLY. What are they? How are they alike? Different? (5 points)

6. Discuss the correlations of XLP, XLY, LULU, and KO. Do the correlations make sense given what you know about the indexes and firms? (5 points)

Section II – Analysis of the Optimal Risky Portfolio

1. Use your arithmetic average as the expected return and your standard deviation as the estimated risk for each security, compute the optimal risky portfolio for LULU and KO (assume no shorting and use Solver to find your solution). Assume a monthly risk free rate equal to 0.37%. Fill in the following table (hint: this is the same thing you did for project 1): (3 points)

Weight LULU	Weight KO	Portfolio return	Portfolio risk	Portfolio Sharpe

2. Use your excess returns to estimate a CAPM regression for each security. Use your beta estimates, along with a monthly risk free rate of 0.37% and a monthly market risk premium of 0.50% to estimate the stocks’ expected returns. Continue to use your sample standard deviation of percent return ( not excess returns) as your measure of risk. Fill in the following table: (5 points)

	LULU	KO
Alpha
Beta
Expected return
Risk

3. Interpret your alpha and beta coefficients for LULU. Are they significant? What do they mean? Make sure to use your knowledge of LULU in your beta discussion (see question #3 in part I). (3 points)

4. Interpret your alpha and beta coefficients for KO. Are they significant? What do they mean? Make sure to use your knowledge of KO in your beta discussion (see question #3 in part I). (3 points)

5. Find the optimal risky portfolio for LULU and KO using:

- The expected returns derived from the CAPM as your measure of expected return for each security

- The sample standard deviation of percent return as the measure risk for each security (from table one of question #2 in part I)

- The correlation between HPRs as your correlation (from table two of question #2 in part I)

- A risk free rate of 0.37%

Use Solver to find your answer, and assume no shorting. Fill in the following table: (3 points)

Weight LULU	Weight KO	Portfolio return	Portfolio risk	Portfolio Sharpe

6. How does your Sharpe ratio for the optimal risky portfolio computed with CAPM estimates of expected return (#5, part II) differ from the optimal risky portfolio computed with sample averages as expected return (#1, part II). Explain the difference (Note: if you’re going to say a number is bigger because another number is smaller, a complete answer will include why that number is smaller.). (5 points)

7. Use your excess returns to estimate a Fama French regression for each security. Use your coefficient estimates, along with a monthly risk free rate of 0.37%, a monthly market risk premium of 0.50%, a monthly SMB of 0.18%, and a monthly HML of 0.34% to estimate the stocks’ expected returns. Continue to use your sample standard deviation of percent return ( not excess returns) as your measure of risk. Fill in the following table: (5 points)

	LULU	KO
Alpha
Beta
s (SMB beta)
h (HML beta)
Expected return
Risk

8. Interpret your alpha, beta, SMB coefficient (s), and HML coefficient (h) for LULU. What do they mean? Are they significant? Make sure to use your knowledge of LULU in your beta, s, and h discussion (see question #3 in part I). (5 points)

9. Interpret your alpha, beta, SMB coefficient (s), and HML coefficient (h) for KO. What do they mean? Are they significant? Make sure to use your knowledge of KO in your beta, s, and h discussion (see question #3 in part I). (5 points)

10. Find the optimal risky portfolio for LULU and KO using:

- The expected returns derived from the Fama French models as your measure of expected return for each security

- The sample standard deviation of percent return as the measure risk for each security (from table one of question #2, part I)

- The correlation between HPRs as your correlation (from table two of question #2, part I)

- A risk free rate of 0.37%

Use Solver to find your answer, and assume no shorting. Fill in the following table: (3 points)

Weight LULU	Weight KO	Portfolio return	Portfolio risk	Portfolio Sharpe

11. You have now computed optimal risky portfolios using expected returns from historic averages, CAPM estimates, and Fama French estimates. Which portfolio would you choose in which to invest and why? (HINT: this looks like a question from project 1 in which I asked you to choose between several different portfolios, and you were supposed to select the portfolio with the highest Sharpe ratio. This is not the same question. If you are tempted to make a decision based on the Sharpe ratio, I strongly recommend you re-think what you’ve done in this assignment.) (5 points)

Section III – Index Model Analysis

1. Using your excess returns to LULU and KO, and the excess returns to XLP, estimate an index model. Also, please estimate the systematic risk, idiosyncratic risk, and total risk (expressed in variance) of each firm. Finally, compute the rho-squared and information ratio for each firm. Fill in the following table: (5 points)

	LULU	KO
Alpha
Beta
Systematic risk
Idiosyncratic risk
Total risk
Rho2
Information ratio

2. Compare the betas of LULU and KO estimated in the index model to those estimated earlier using CAPM. Which betas are higher or lower? Why? Also, please compare the R2 for the index and CAPM estimations. Again, which is higher? Why? (Hint: a successful answer here will include discussions of firm and index specifics – go back to part I and re-read your answers to #3 – #6.) (5 points)

3. Using your excess returns to LULU and KO, and the excess returns to XLY, estimate an index model. Also, please estimate the systematic risk, idiosyncratic risk, and total risk (expressed in variance) of each firm. Finally, compute the rho-squared and information ratio for each firm. Fill in the following table: (5 points)

	LULU	KO
Alpha
Beta
Systematic risk
Idiosyncratic risk
Total risk
Rho2
Information ratio

4. Compare the betas of LULU and KO estimated in the index model with XLP as the index (#1 of part III) and the betas estimated using XLY as the index (#3 of part III). Which betas are higher or lower? Why? Also, please compare the R2 for each index estimation. Again, which is higher? Why? (Hint: a successful answer here will include discussions of firm and index specifics – go back to part I and re-read your answers to #3 – #6.) (5 points)

5. In general, how do R2 estimates relate to beta estimates? If a model’s beta is high, does it necessarily mean the R2 is high? If a model’s beta is significant, does it necessarily mean that the R2 is high? (5 points)

BONUS – (Note: Prof. He will not provide any additional assistance or guidance on completing the bonus beyond what is given on this page.)

For up to 5 bonus points:

In order to estimate the optimal risky portfolios, thus far we have only used sample standard deviation of percent returns to measure risk. Remember that one benefit of the index model is to make computing portfolio risk easier. Try to compute an optimal risky portfolio using your index model estimates, computed using XLY as the index (assume no shorting).

For each firm, find the expected return to the firm using the index model estimate, as well as a monthly risk free rate of 0.37% and a monthly expected return to XLY of 1%.

The portfolio expected return is a weighted average of the expected return of the two securities.

The portfolio risk has two components: systematic risk and idiosyncratic risk. Each firm’s systematic risk expressed in variance is equal to:

(beta squared times the variance of the index). The portfolio systematic risk component measured in standard deviation is the weighted average of the systematic risk of each firm’s systematic risk measured in standard deviation.

The idiosyncratic variance of the portfolio is computed using the following formula:

with “w” equal to the weight in each security.

The portfolio’s total risk is the sum of the systematic and idiosyncratic components. Remember that this should be expressed in standard deviation. (HINT: watch your standard deviation vs. variance – the square of a sum is not equal to the sum of a square)

(Note: your portfolio standard deviation computed using this method will differ from that computed using the Markowitz portfolio standard deviation formula. If this confuses you, it may be helpful to consider the assumptions of the index model and whether they are true – and, most importantly, how it affects your estimates if they are not true.)

Weight LULU	Weight KO	Portfolio return	Portfolio risk	Portfolio Sharpe