Questions for CAPM Analysis of your company. Use the excel template and guidelines attached, or use the Bloomberg BETA function, to answer the following questions. Please answer at least five of the questions

.1. On the basis of your company?s beta, evaluate the expected volatility of the stockrelative to the total market. Suggest an appropriate trading strategy for this stock depending on the direction of themarket

.2. Evaluate the scatter chart of returns and the R2. What proportion of the total volatility of the stock has been ?explained? by the market? What is the meaning of the residual, i.e. the portion that is not explained by the market?

3. What are the non-market factors affecting this company that we might be overlooking in the single-index model?

4. Has the beta for this company remained stationary over the five-year period, or did you detect a significant difference between the two halves of the period? Has the company experienced some changes that would account for the non-stationarity ?

5. What is the company?s ?alpha? based on the expected (consensus) rate of return and the Security Market Line? Outline a scenario for this stock that would bring its EROR back to the SML equilibrium.

6. Consider the results of under-weighting or over-weighting the stock in order to?enhance? an index portfolio. Describe what happens to risk and return as you increase the weight of the stock in the portfolio. Which enhanced portfolio is the minimum variance portfolio? Which is the optimal portfolio based on maximizing the slope of the Capital Allocation Line (Sharpe ratio)?

7. The Sharpe and Treynor ratios are alternate measures to evaluate portfolio performance. Sharpe relates excess return to the standard deviation, while Treynor relates excess return to the beta. Do both of these measures identify the same optimal portfolio? Why or why not?

Instructions for Developing the CAPM template 1. Pick your favorite stock (perhaps your single egg-wity). Submit your selection to the Discussion forum \"CAPM project\" (making sure that no one else has already taken it). 2. Download the most recent 61 months of month-end price and dividend data (July 30, 2010 through July 31, 2015) from Yahoo's \"historical prices.\" The file will download as a .csv file. Save the downloaded file as an Excel workbook. Note that the downloaded data are in descending order chronologically (most recent date is first). To avoid confusion you should invert the file: Place your cursor on any cell under the heading in the date column and select Data/Sort A to Z (ascending). Repeat these steps for the S& P 500 index (symbol ^GSPC in Yahoo), or go to step 3 to use the S&P series already developed for the template. 3. At this point you may drop your company data into the CAPM template, making sure to use the same cell range and date range. If you have executed this correctly, you should see that the returns and return statistics for your company have been calculated automatically. Go to step 6 to confirm that your data have deployed correctly. All others complete steps 4 and 5 first. 4. Using the "adjusted price" column, which is adjusted for stock splits and dividends, compute monthly returns for your company and the S&P. You will wind up with 60 data points for each series. (Note: you may compute your monthly returns using a single period arithmetic formula {R = (EV - BV)/BV}, where BV = beginning of month and EV = end of month; or you may use the continuously compounded formula, R = ln (EV/BV) - 1. 5. Compute the following return statistics: mean (using the AVERAGE function), variance (using the VAR function), standard deviation (using the STDEV function), covariance (using the COVAR function), and Beta (using the SLOPE function). 6. Find commercial betas for your company at Yahoo, Google, Reuters, Investors' Business Daily, or other sites. Google and Reuters use the same technique and time period, so their results provide a reality check on your data and return calculations. 7. Show the partitioning of total risk (variance) for your company, using the single index formula: i2 = i2 m2 + ei2, where i2 is the observed sample variance, i2 m2 approximates the systematic risk, and the residual ei2 can be backed out to approximate the idiosyncratic risk. 8. Divide the data into two periods (October 2009 - April 2012 and April 2012 - October 2014) and compute betas for each period. 9. Determine 1-year forward target price based on analysts' consensus (Yahoo). Use the mean target price and expected dividend to calculate the expected rate of return (EROR) 10. Using the 10-year Treasury bond as the risk-free rate, and 5.6% as the equity risk premium (spread of the market return over the risk-free rate), develop the Security Market Line. 11. Annualize the monthly return statistics and use them to construct an enhanced index portfolio. Questions for Analysis of the CAPM Template 1. On the basis of your company's beta, evaluate the expected volatility of the stock relative to the total market. Suggest an appropriate trading strategy for this stock depending on the direction of the market. 2. Evaluate the scatter chart of returns and the R2. What proportion of the total volatility of the stock has been \"explained\" by the market? What is the meaning of the residual, i.e. the portion that is not explained by the market? 3. What are the non-market factors affecting this company that we might be overlooking in the single-index model? 4. Has the beta for this company remained stationary over the five-year period, or did you detect a significant difference between the two halves of the period? Has the company experienced some changes that would account for the non-stationarity? 5. What is the company's \"alpha\" based on the expected (consensus) rate of return and the Security Market Line? Outline a scenario for this stock that would bring its EROR back to the SML equilibrium. 6. Consider the results of under-weighting or over-weighting the stock in order to \"enhance\" an index portfolio. Describe what happens to risk and return as you increase the weight of the stock in the portfolio. Which enhanced portfolio is the minimum variance portfolio? Which is the optimal portfolio based on maximizing the slope of the Capital Allocation Line (Sharpe ratio)? 7. The Sharpe and Treynor ratios are alternate measures to evaluate portfolio performance. Sharpe relates excess return to the standard deviation, while Treynor relates excess return to the beta. Do both of these measures identify the same optimal portfolio? Why or why not? Date Open High Low Close Volume Adj Close IBM Return 10/31/2011 185.59 186.91 184.63 184.63 9420000 161.565002 11/1/2011 181.55 189.97 177.06 188 4992900 165.175339 2.23% 12/1/2011 187.01 194.9 179.04 183.88 5093600 161.555542 -2.19% 1/3/2012 186.73 193.1 177.35 192.6 5742000 169.216858 4.74% 2/1/2012 193.21 199.23 190.83 196.73 4196300 173.518509 2.54% 3/1/2012 197.23 209.12 196.81 208.65 4150500 184.032104 6.06% 4/2/2012 208.96 210.69 196.79 207.08 4468800 182.647354 -0.75% 5/1/2012 207.18 208.93 192 192.9 4331400 170.853165 -6.46% 6/1/2012 190.12 199.99 187 195.58 4277000 173.226868 1.39% 7/2/2012 196.36 197.84 181.85 195.98 4524900 173.581146 0.20% 8/1/2012 196.96 202 193.02 194.85 2775900 173.317154 -0.15% 9/4/2012 196.61 208.32 193.25 207.45 4329000 184.524719 6.47% 10/1/2012 208.01 211.79 190.56 194.53 4867000 173.032516 -6.23% 11/1/2012 194.68 198 184.78 190.07 4134700 169.805313 -1.87% 12/3/2012 190.76 196.45 186.94 191.55 4200200 171.127502 0.78% 1/2/2013 194.09 208.58 190.39 203.07 4320300 181.419281 6.01% 2/1/2013 204.65 205.35 197.51 200.83 3622800 180.173309 -0.69% 3/1/2013 200.65 215.9 199.36 213.3 3988600 191.360703 6.21% 4/1/2013 212.8 214.89 187.68 202.54 5282100 181.707428 -5.04% 5/1/2013 201.87 211.98 199.2 208.02 4346900 187.49852 3.19% 6/3/2013 208.25 210.05 188.41 191.11 4513500 172.256714 -8.13% 7/1/2013 192.15 200.94 190.26 195.04 4176700 175.798996 2.06% 8/1/2013 196.65 197.17 181.1 182.27 3445800 165.110062 -6.08% 9/3/2013 183.63 194.89 182.31 185.18 3773300 167.746094 1.60% 10/1/2013 185.34 186.99 172.57 179.21 5881900 162.33815 -3.22% 11/1/2013 179.81 186.24 177.31 179.68 5192700 163.637985 0.80% 12/2/2013 179.46 187.79 172.73 187.57 4838700 170.823563 4.39% 1/2/2014 187.21 190.81 175.34 176.68 6127700 160.905823 -5.81% 2/3/2014 176.02 186.12 172.19 185.17 4827500 169.562317 5.38% 3/3/2014 183.33 195.63 182.21 192.49 6072400 176.265335 3.95% 4/1/2014 193.12 199.21 187.01 196.47 5840300 179.909866 2.07% 5/1/2014 196.31 196.74 182.33 184.36 3554200 169.803513 -5.62% 6/2/2014 184.76 187.65 179.27 181.27 3939300 166.957504 -1.68% 7/1/2014 181.7 196.4 181.7 191.67 4627500 176.536346 5.74% 8/1/2014 190.5 194.13 183.58 192.3 2683900 178.164062 0.92% 9/2/2014 192.68 195 188.12 189.83 3195800 175.875641 -1.28% 10/1/2014 189.91 190.89 161.1 164.4 6611800 152.314987 -13.40% 11/3/2014 164.25 164.97 159.8 162.17 4337900 151.277252 -0.68% 12/1/2014 161.64 164.52 150.5 160.44 4799700 149.663452 -1.07% 1/2/2015 161.31 163.31 149.52 153.31 6198100 143.01236 -4.44% 2/2/2015 154 164.99 151.51 161.94 4319300 152.122375 6.37% 3/2/2015 161.94 165.35 153.4 160.5 4962100 150.769669 -0.89% 4/1/2015 160.23 175.13 158.39 171.29 4709400 160.905518 6.72% 5/1/2015 173.2 176.3 168.84 169.65 3101000 160.570999 -0.21% 6/1/2015 170.21 171.56 162.12 162.66 3838200 153.955093 -4.12% 7/1/2015 163.97 173.78 158.5 161.99 4426000 153.320938 -0.41% 8/3/2015 9/1/2015 10/1/2015 11/2/2015 12/1/2015 1/4/2016 2/1/2016 3/1/2016 4/1/2016 5/2/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/3/2016 161.7 144.91 145.31 140.5 139.58 135.6 124.4 132.24 150.51 146.56 153 151.78 160.65 158.32 158.06 161.85 149.68 153.15 142.8 141.4 136.89 134.92 153.1 153.52 153.81 155.48 163.6 164.95 165 158.53 140.62 141.15 137.33 131.65 133.91 118 116.9 132.03 142.61 142.9 142.5 149.92 157.85 153.21 147.79 147.89 144.97 140.08 139.42 137.62 124.79 131.03 151.45 145.94 153.74 151.78 160.62 158.88 158.85 153.69 4656700 141.137512 4083700 138.35083 5652800 133.684113 4499100 134.302307 4619900 132.568375 7072000 120.209335 5557100 127.50956 5114800 147.38092 4660200 142.018967 3857300 151.053207 3678500 149.127457 3648600 157.812973 3131700 157.451599 3501600 157.421875 3899000 152.308258 -7.95% -1.97% -3.37% 0.46% -1.29% -9.32% 6.07% 15.58% -3.64% 6.36% -1.27% 5.82% -0.23% -0.02% -3.25% Return Statistics: ProfM: Be sure to replace these numbers with several commercially- produced betas for your company. Expect some variation; for example, Yahoo covers a shorter time period (36 months) and IBD covers only the last 3 months. Mean (Sample) Variance (Sample) Std dev Covar Correl 0.0002 0.0025 0.0497 0.0008 0.5588 Beta 0.9345 0.9345 0.8639 0.9200 0.9470 0.9400 Beta Beta 1.1284 0.7925 Stationarity of the Beta 1st half 2nd half Analysts' Consensus Estimates (1 year) Source: Yahoo! Finance "Analyst Opinion" Security Market Line Risk-free (10 year Treasury) Mean Median High Low #brokers $ $ $ $ Price 157.10 156.00 186.00 110.00 21 Beta -0.5000 0.0000 0.5000 Your company actual beta Market (based on historical spread) 0.9345 1.0000 1.5000 2.0000 2.5000 Security Market Line R e q u i r e d 20.00% 15.00% 10.00% R e t u -1.0000 r n 5.00% 0.00% -0.5000 0.0000 -5.00% 0.5000 1.0000 1.5000 Beta RROR EROR Enhanced Indexing: Tilting a Market Portf Annualized Mean return Variance Standard deviation Covariance Beta IBM S&P 500 0.0028 0.111369 0.029675 0.010612 0.172265 0.103017 0.009752 0.9345 1.0000 Weight IBM -0.4 -0.3 -0.2 -0.1 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 1.1 1.2 1.3 1.4 Weight S&P 500 1.4 1.3 1.2 1.1 1 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0 -0.10 -0.20 -0.30 -0.40 Portfolio Variance 0.0146 0.0130 0.0118 0.0110 0.0106 0.0106 0.0111 0.0120 0.0132 0.0149 0.0171 0.0196 0.0225 0.0259 0.0297 0.0339 0.0385 0.0435 0.0489 Enhanced Indexing 0.1800 0.1600 0.1400 0.1200 0.1000 Mean Return 0.0800 0.0600 0.0400 0.0200 0.0000 0.0900 0.1000 0.1100 0.1200 0.1300 0.1400 0.1500 Stanadard Deviation S & P Returns -0.51% 0.85% 4.36% 4.06% 3.13% -0.75% -6.27% 3.96% 1.26% 1.98% 2.42% -1.98% 0.28% 0.71% 5.04% 1.11% 3.60% 1.81% 2.08% -1.50% 4.95% -3.13% 2.97% 4.46% 2.80% 2.36% -3.56% 4.31% 0.69% 0.62% 2.10% 1.91% -1.51% 3.77% -1.55% 2.32% 2.45% -0.42% -3.10% 5.49% -1.74% 0.85% 1.05% -2.10% 1.97% ProfM: Drop the values for your company here, making sure that your start with the monthly data for October 2011 and end with the monthly data for October 2016. ^ | 1st half 2nd half | v Scatter Chart of Company versus S&P 500 Monthly Returns, Nov 2011 - Oct 2016 10.00% 8.00% 6.00% 4.00% 2.00% -15.00% -10.00% -5.00% 0.00% 0.00% -2.00% -4.00% 5.00% 10.00% 15.00% 20.00% 6.00% 4.00% 2.00% -6.26% -2.64% 8.30% 0.05% -1.75% -5.07% -0.41% 6.60% 0.27% 1.53% 0.09% 3.56% -0.12% -0.12% -1.94% -15.00% -10.00% -5.00% 0.00% 0.00% -2.00% 5.00% 10.00% 15.00% 20.00% -4.00% -6.00% -8.00% Partitioning of Total Risk: Pop. Var. 0.0093 0.0009 0.0297 Market i2*Varm + All other + Residual* 0.0007722 + 0.0017 31.23% + 68.77% R-squared * Estimate of non-systematic risk 0.00086963 = = = = Total risk Vari 0.0025 100.00% calculated by formula: Beta = Covari,m / Varm calculated by slope function Yahoo! Finance Investors Business Daily Bloomberg Google Finance Based on monthly price change, relative to the monthly price change of the S&P 500, over a ti Based on price changes versus the S&P 500 over the past 12 months. Based on trailing 5-year prices, on a monthly basis, relative to S&P 500 Based on returns from Nov 2011 - Apr 2014 Based on returns from Apr 2014 - Oct 2016 $ $ $ $ Dividend 5.60 5.60 5.60 5.60 EROR 6.82% 6.10% 25.80% -24.10% RROR -0.60% 2.20% 5.00% EROR ProfM: Be sure to replace these numbers with the price target and dividend data for your company. ProfM: Calculated Expected Rate of Return (EROR) = (Expected change in price + Expected Dividend) /Current price ProfM: Calculated Required Rate of Return (RROR) = Riskfree rate +(Beta x Historical Spread of Market Return over Riskfree rate) Calculated RROR = Rf + Beta x Historical Spread (Rm - Rf) Source: Yahoo! Finance/Investing/Bonds 11/15/2016 Calculated 7.43% 7.80% 10.60% 13.40% 16.20% 6.82% EROR as calculated above in cell J83 Assume historical spread of 5.60% over 10-year Treasury bonds Calculated ProfM: Based on the Calculated SML, is the company Calculated fairly valued, overor under-valued? Security Market Line 00% 00% 00% 00% 00% 0.0000 00% 0.5000 1.0000 1.5000 2.0000 2.5000 3.0000 ProfM: What is the minimum variance portfolio? Beta RROR EROR nced Indexing: Tilting a Market Portfolio Portfolio Std dev 0.1209 0.1140 0.1086 0.1048 0.1030 0.1032 0.1054 0.1094 0.1151 0.1223 0.1306 0.1400 0.1501 0.1609 0.1723 0.1840 0.1962 0.2086 0.2212 Mean 0.1548 0.1439 0.1331 0.1222 0.1114 0.1005 0.0897 0.0788 0.0679 0.0571 0.0462 0.0354 0.0245 0.0137 0.0028 -0.0081 -0.0189 -0.0298 -0.0406 Risk free= Portfolio Beta 1.03 1.02 1.01 1.01 1.00 0.99 0.99 0.98 0.97 0.97 0.96 0.95 0.95 0.94 0.93 0.93 0.92 0.91 0.91 Sharpe 1.0980 1.0695 1.0231 0.9559 0.8675 0.7609 0.6422 0.5192 0.3991 0.2870 0.1855 0.0955 0.0168 -0.0518 -0.1114 -0.1633 -0.2086 -0.2482 -0.2831 2.20% Treynor 0.1294 0.1196 0.1096 0.0996 0.0894 0.0790 0.0686 0.0579 0.0472 0.0363 0.0252 0.0140 0.0027 -0.0089 -0.0205 -0.0324 -0.0444 -0.0566 -0.0690 ProfM: What is the portfolio for the maximum Sharpe Ratio? Enhanced Indexing .1000 0.1100 0.1200 0.1300 0.1400 0.1500 0.1600 0.1700 0.1800 Stanadard Deviation 11 - Oct 2016 20.00% 20.00% ce change of the S&P 500, over a time period of 3 years (36 months) to S&P 500 Treasury bonds ProfM: What is the portfolio for the maximum Sharpe Ratio