Pick 5 large ( > $1 billion market cap) publicly traded companies that you might want to invest in. Make sure all 5 companies have been publicly traded with daily price data available in Yahoo Finance for at least the last 5 years. Create a portfolio that invests 24% of $1 million in company A, 22% in company B, 20% in company C, 18% in company D, and 16% in company E.

Please take a look at the Excel template that has been uploaded. The purpose of the template is to make it easier for you to figure out what data you need to use and what the answers should look like. The file has six tabs. You should start with the first tab, "Summary statistics" and progress tab by tab towards the last one, "Alphas and betas".

Tab 1. Please provide information, from Bloomberg, on the following (for each company): Company name, Ticker, CEO, Market capitalization, P/E ratio, Dividends, Competitor 1, Competitor 2, Competitor 3, Competitor 4, Revenue (most recent year), Revenue projection (current year), Revenue growth projection, EPS (most recent year), EPS projection (current year), EPS growth projection, Long-term credit rating.

Tab 2. Please enter the buy-and-hold return (HPR) for each of the 5 stocks and for the portfolio during the last calendar year (2018). This means you are buying on Dec 31, after markets close, and selling on Dec 31 of the following year, after markets close. Please make sure to show your work. You will need stock price/return data on the 5 companies. To get the stock price/return data, go to Yahoo Finance, enter the ticker, click on "Historical Prices", select the start and end date, and make sure "daily" is selected. Click on "Get Prices", and use Adj. Close to calculate returns (Adj. Close adjusts for dividends, stock splits, etc.).

Tab 3. Please enter the return correlations for the 5 stocks during the last calendar year. Please use daily returns, and make sure to show your work.

Tab 4. Please enter the return standard deviations for each of the 5 stocks and for the portfolio during the last calendar year. Please use daily returns, and make sure to show your work. Please keep in mind that the standard deviation you will get using daily data is the daily standard deviation. In finance, the practice is to annualize the standard deviation by multiplying the daily standard deviation by sqrt(252) - there are approximately 252 trading days in a year. This is especially important if you want to be able to compute the Sharpe ratio correctly. The numerator in the Sharpe ratio is the ***annual*** excess return. Therefore, the denominator, which is the standard deviation, should also be annualized.

Tab 5. Please enter the Sharpe ratios for each of the 5 stocks and for the portfolio during the last calendar year. Please make sure to show your work. You will need the risk-free interest rate. Note: 1^stcolumn is date in YYYYMMDD format; 2^ndcolumn is excess return on the market portfolio, in percent; 3^rdcolumn is return on SMB, in percent; 4^thcolumn is return on HML, in percent; 5^thcolumn is the risk-free interest rate, in percent.

Tab 6. Please enter the one-factor alphas and betas of each of the 5 stocks and of the portfolio estimated from the three calendar years PRIOR to the last calendar year (i.e.,2015-2017). Please also enter the expected and actual returns for2018, where the expected return is that given by the one-factor model, and the actual return is your answer from tab 2. Make sure that the risk-free rate and the market excess return is from2018, but the beta is estimated using2015-2017 data. To run the necessary regressions, you will need the market excess return data.

x template Q ~ Search Sheet Home Insert Page Layout Formulas Data Review View do Cut Calibri (Body) 11 . A- A Wrap Text Custom 2 AutoSum Copy + Fill Paste Format B I U . .MAY = = E E Merge & Center $ % 2 00 +20 Conditional Format Cell Insert Delete Format Formatting as Table Styles Clear Sort & Filter C22 X fx S A C D G H K M N 0 P Q R U V W X Stock A Stock B Stock C Stock D Stock E 2 Company name 3 Ticker 4 CEO (Hint: look in Bloomberg; ticker -> Security Description) 5 Market capitalization (Hint: look in Bloomberg; ticker -> Security Description) 6 P/E ratio (Hint: look in Bloomberg; ticker -> Security Description) 7 Dividends (Hint: look in Bloomberg; ticker -> Security Description) 8 Competitor 1 (Hint: look in Bloomberg; ticker -> Security Description) 9 Competitor 2 Hint: look in Bloomberg; ticker -> Security Description) 10 Competitor 3 (Hint: look in Bloomberg; ticker -> Security Description) 11 Competitor 4 (Hint: look in Bloomberg; ticker -> Security Description) 12 Revenue (most recent year) (Hint: look in Bloomberg; ticker -> Financial Analysis) 13 Revenue projection (current year) (Hint: look in Bloomberg; ticker -> Financial Analysis) 14 Revenue growth projection (Hint: look in Bloomberg; ticker -> Financial Analysis) 15 EPS (most recent year) (Hint: look in Bloomberg; ticker -> Financial Analysis) 16 EPS projection (current year) (Hint: look in Bloomberg; ticker -> Financial Analysis) 17 EPS growth projection (Hint: look in Bloomberg; ticker -> Financial Analysis] Long-term credit rating (Hint: look in Bloomberg; ticker -> Credit Rating Profile) Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 100%Excel File Edit View Insert Format Tools Data Window Help 100% 27. Fri 4:17 PM Q O . . template Q v Search Sheet do Cut Calibri (Body) General -Copy ) - 11 . A- A Wrap Text AutoSum + Fill Paste Format B I U . .M . A = = EE Merge & Center $ % 2 700 +10 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear Filter T28 X V fx D E G H N Q R T U V W S Stock A Stock B Stock C Stock D Stock E Portfolio Show your work here -- Date Stock A Stock B Stock C Stock D Stock E Portfolio Buy-and-hold return Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 100%Excel File Edit View Insert Format Tools Data Window Help 100% 27. Fri 4:17 PM Q O . . template Q v Search Sheet do Cut Calibri (Body) Wrap Text General AutoSum + Fill Paste -Copy ) - 11 . A- A Format B I U . M . A Merge & Center $ % 2 100 420 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear Filter G15 X V fx B D E F G H K M 0 P Q R S U W X 1 Correl. Stock A Stock B Stock C Stock D Stock E Show your work here -> Date Stock A Stock B Stock C Stock D Stock E 2 Stock A Stock B 4 Stock C Stock D Stock E Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 100%Excel File Edit View Insert Format Tools Data Window Help 100% 27. Fri 4:17 PM Q O . . template Q v Search Sheet do Cut Calibri (Body) General + Fill Paste -Copy ) - 11 . A- A Wrap Text AutoSum Format B I U V . M . A = = E .E Merge & Center $ % 2 00 420 Conditional Format Cell Insert Delete Format Sort & Formatting as Table Styles Clear Filter G16 X V fx B D E G H K M N 0 Q R S U W X Stock A Stock B Stock C Stock D Stock E Portfolio Show your work here --> Date Stock A Stock B Stock C Stock D Stock E Portfolio Std. dev. 5 NOVAWN Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 100%x template Q ~ Search Sheet Home Insert Page Layout Formulas Data Review View do Cut Calibri (Body) 11 . A- A Wrap Text General 2 AutoSum -Copy Fill Paste Format B I U . . A- # Merge & Center $ % 2 00 +20 Conditional Format Cell Insert Delete Format Formatting as Table Styles Clear Sort & Filter E10 X fx D G M H N 0 Q R S U Stock A Stock B Stock C Stock D Stock E Portfolio Show your work here --> Stock A Stock B Stock C Stock D Stock E Portfolio Sharpe ratio Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 100%x template Q ~ Search Sheet Home Insert Page Layout Formulas Data Review View do Cut Calibri (Body) 11 . A- A Wrap Text General AutoSum Copy Fill Paste Format B I U . MAY E .E # Merge & Center $ % 21 80 60 Conditional Format Cell Insert Delete Format Formatting as Table Styles Clear Sort & Filter D13 X V fx C M N R W Stock A Stock B Stock C Stock D Stock E Portfolio Show your work here -> Stock A regression output 2 Alpha 3 Beta 4 Expected Return 5 Actual Return Outperformed (Yes/No)? Stock B regression output Stock C regression output Summary statistics Buy-and-hold return Correlation matrix Standard deviation Sharpe ratio Alphas and betas + Ready 94%