QUESTION-1 Assume that you recently graduated and landed a job as a financial planner with Cicero Services, an investment advisory company. Your first client recently inherited some assets and has asked you to evaluate them. The client owns a bond portfolio with $1 million invested in zero coupon Treasury bonds that mature in 10 years.47 The client also has $2 million invested in the stock of Blandy, Inc., a company that produces meat-and-potatoes frozen dinners. Blandy’s slogan is “Solid food for shaky times.” Unfortunately, Congress and the president are engaged in an acrimonious dispute over the budget and the debt ceiling. The outcome of the dispute, which will not be resolved until the end of the year, will have a big impact on interest rates 1 year from now. Your first task is to determine the risk of the client’s bond portfolio. After consulting with the economists at your firm, you have specified five possible scenarios for the resolution of the dispute at the end of the year. For each scenario, you have estimated the probability of the scenario occurring and the impact on interest rates and bond prices if the scenario occurs. Given this information, you have calculated the rate of return on 10-year zero coupon Treasury bonds for each scenario. The probabilities and returns are shown here: You have also gathered historical returns for the past 10 years for Blandy, Gourmange Corporation (a producer of gourmet specialty foods), and the stock market. The risk-free rate is 4% and the market risk premium is 5%. a. What are investment returns? What is the return on an investment that costs $1,000 and is sold after 1 year for $1,060? b. Graph the probability distribution for the bond returns based on the 5 scenarios. What might the graph of the probability distribution look like if there were an infinite number of scenarios (i.e., if it were a continuous distribution and not a discrete distribution)? c. Use the scenario data to calculate the expected rate of return for the 10-year zero coupon Treasury bonds during the next year. d. What is stand-alone risk? Use the scenario data to calculate the standard deviation of the bond’s return for the next year. e. Your client has decided that the risk of the bond portfolio is acceptable and wishes to leave it as it is. Now your client has asked you to use historical returns to estimate the standard deviation of Blandy’s stock returns. (Note: Many analysts use 4–5 years of monthly returns to estimate risk and many use 52 weeks of weekly returns; some even use a year or less of daily returns. For the sake of simplicity, use Blandy’s 10 annual returns.) f. Your client is shocked at how much risk Blandy stock has and would like to reduce the level of risk. You suggest that the client sell 25% of the Blandy stock and create a portfolio with 75% Blandy stock and 25% in the high-risk Gourmange stock. How do you suppose the client will react to replacing some of the Blandy stock with high-risk stock? Show the client what the proposed portfolio return would have been in each year of the sample. Then calculate the average return and standard deviation using the portfolio’s annual returns. g. h. i. j. k. l. m. n. How does the risk of this two-stock portfolio compare with the risk of the individual stocks if they were held in isolation? Explain correlation to your client. Calculate the estimated correlation between Blandy and Gourmange. Does this explain why the portfolio standard deviation was less than Blandy’s standard deviation? Suppose an investor starts with a portfolio consisting of one randomly selected stock. As more and more randomly selected stocks are added to the portfolio, what happens to the portfolio’s risk? (1) Should portfolio effects influence how investors think about the risk of individual stocks? (2) If you decided to hold a one-stock portfolio and consequently were exposed to more risk than diversified investors, could you expect to be compensated for all of your risk; that is, could you earn a risk premium on that part of your risk that you could have eliminated by diversifying? According to the Capital Asset Pricing Model, what measures the amount of risk that an individual stock contributes to a well-diversified portfolio? Define this measurement. What is the Security Market Line (SML)? How is beta related to a stock’s required rate of return? Calculate the correlation coefficient between Blandy and the market. Use this and the previously calculated (or given) standard deviations of Blandy and the market to estimate Blandy’s beta. Does Blandy contribute more or less risk to a well-diversified portfolio than does the average stock? Use the SML to estimate Blandy’s required return. Show how to estimate beta using regression analysis. (1) Suppose the risk-free rate goes up to 7%. What effect would higher interest rates have on the SML and on the returns required on high-risk and low-risk securities? (2) Suppose instead that investors’ risk aversion increased enough to cause the market risk premium to increase to 8%. (Assume the risk-free rate remains constant.) What effect would this have on the SML and on returns of high- and low- risk securities? o. Your client decides to invest $1.4 million in Blandy stock and $0.6 million in Gourmange stock. What are the weights for this portfolio? What is the portfolio’s beta? What is the required return for this portfolio? p. Jordan Jones (JJ) and Casey Carter (CC) are portfolio managers at your firm. Each manages a well-diversified portfolio. Your boss has asked for your opinion regarding their performance in the past year. JJ’s portfolio has a beta of 0.6 and had a return of 8.5%; CC’s portfolio has a beta of 1.4 and had a return of 9.5%. Which manager had better performance? Why? q. What does market equilibrium mean? If equilibrium does not exist, how will it be established? r. What is the Efficient Markets Hypothesis (EMH) and what are its three forms? What evidence supports the EMH? What evidence casts doubt on the EMH? QUESTION-2 Your employer, a mid-sized human resources management company, is considering expan- sion into related fields, including the acquisition of Temp Force Company, an employment agency that supplies word processor operators and computer programmers to businesses with temporary heavy workloads. Your employer is also considering the purchase of Bigger- staff & McDonald (B&M), a privately held company owned by two friends, each with 5 million shares of stock. B&M currently has free cash flow of $24 million, which is expected to grow at a constant rate of 5%. B&M’s financial statements report short-term investments of $100 million, debt of $200 million, and preferred stock of $50 million. B&M’s weighted average cost of capital (WACC) is 11%. Answer the following questions. a. Describe briefly the legal rights and privileges of common stockholders. b. What is free cash flow (FCF)? What is the weighted average cost of capital? What is the free cash flow valuation model? c. Use a pie chart to illustrate the sources that comprise a hypothetical company’s total value. Using another pie chart, show the claims on a company’s value. How is equity a residual claim? d. Suppose the free cash flow at Time 1 is expected to grow at a constant rate of gL forever. If gL WACC, what is a formula for the present value of expected free cash flows when discounted at the WACC? If the most recent free cash flow is expected to grow at a constant rate of gL forever (and gL WACC), what is a formula for the present value of expected free cash flows when discounted at the WACC? e. Use B&M’s data and the free cash flow valuation model to answer the following questions. (1) What is its estimated value of operations? (2) What is its estimated total corporate value? (This is the entity value.) (3) What is its estimated intrinsic value of equity? (4) What is its estimated intrinsic stock price per share? f. You have just learned that B&M has undertaken a major expansion that will change its expected free cash flows to −$10 million in 1 year, $20 million in 2 years, and $35 million in 3 years. After 3 years, free cash flow will grow at a rate of 5%. No new debt or preferred stock was added; the investment was financed by equity from the owners. Assume the WACC is unchanged at 11% and that there are still 10 million shares of stock outstanding. (1) What is the company’s horizon value (i.e., its value of operations at Year 3)? What is its current value of operations (i.e., at Time 0)? (2) What is its estimated intrinsic value of equity on a price-per-share basis g. If B&M undertakes the expansion, what percent of B&M’s value of operations at Year 0 is due to cash flows from Years 4 and beyond? (Hint: Use the horizon value at t 3 to help answer this question.) h. Based on your answer to the previous question, what are two reasons why managers often emphasize short-term earnings? i. YouremployeralsoisconsideringtheacquisitionofHatfieldMedicalSupplies.Youhave gathered the following data regarding Hatfield, with all dollars reported in millions: (1) most recent sales of $2,000; (2) most recent total net operating capital, OpCap $1,120; (3) most recent operating profitability ratio,OP NOPAT Sales 45%;and (4) most recent capital requirement ratio, CR OpCap Sales 56%. You estimate that the growth rate in sales from Year 0 to Year 1 will be 10%, from Year 1 to Year 2 will be 8%, from Year 2 to Year 3 will be 5%, and from Year 3 to Year 4 will be 5%. You also estimate that the long-term growth rate beyond Year 4 will be 5%. Assume the operating profitability and capital requirement ratios will not change. Use this information to forecast Hatfield’s sales, net operating profit after taxes (NOPAT), OpCap, free cash flow, and return on invested capital (ROIC) for Years 1 through 4. Also estimate the annual growth in free cash flow for Years 2 through 4. The weighted average cost of capital (WACC) is 9%. How does the ROIC in Year 4 compare with the WACC? j. What is the horizon value at Year 4? What is the total net operating capital at Year 0? How does the value of operations compare with the current total net operating capital? k. What are value drivers? What happens to the ROIC and current value of operations if expected growth increases by 1 percentage point relative to the original growth rates (including the longterm growth rate)? What can explain this? (Hint: Use Scenario Manager.) l. Assume growth rates are at their original levels. What happens to the ROIC and current value of operations if the operating profitability ratio increases to 5.5%? Now assume growth rates and operating profitability ratios are at their original levels. What happens to the ROIC and current value of operations if the capital requirement ratio decreases to 51%? Assume growth rates are at their original levels. What is the impact of simultaneous improvements in operating profitability and capital requirements? What is the impact of simultaneous improvements in the growth rates, operating profitability, and capital requirements? (Hint: Use Scenario Manager.) m. What insight does the free cash flow valuation model provide regarding possible reasons for market volatility? (Hint: Look at the value of operations for the combinations of ROIC and gL in the previous questions n. (1) Write out a formula that can be used to value any dividend-paying stock, regardless of its dividend pattern. (2) What is a constant growth stock? How are constant growth stocks valued? (3) What happens if a company has a constant gL that exceeds its rs? Will many stocks have expected growth greater than the required rate of return in the short run (i.e., for the next few years)? In the long run (i.e., forever)? o. Assume that Temp Force has a beta coefficient of 1.2, that the risk-free rate (the yield on Tbonds) is 7.0%, and that the market risk premium is 5%. What is the required rate of return on the firm’s stock? p. Assume that Temp Force is a constant growth company whose last dividend (D0, which was paid yesterday) was $2.00 and whose dividend is expected to grow indefinitely at a 6% rate. (1) What is the firm’s current estimated intrinsic stock price? (2) What is the stock’s expected value 1 year from now? (3) What are the expected dividend yield, the expected capital gains yield, and the expected total return during the first year? q. Now assume that the stock is currently selling at $30.29. What is its expected rate of return? r. Now assume that Temp Force’s dividend is expected to experience nonconstant growth of 30% from Year 0 to Year 1, 25% from Year 1 to Year 2, and 15% from Year 2 to Year 3. After Year 3, dividends will grow at a constant rate of 6%. What is the stock’s intrinsic value under these conditions? What are the expected dividend yield and capital gains yield during the first year? What are the expected dividend yield and capital gains yield during the fourth year (from Year 3 to Year 4)? s. What is the market multiple method of valuation? What are its strengths and weaknesses? t. What are the advantages of the free cash flow valuation model relative to the dividend growth model? u. What is preferred stock? Suppose a share of preferred stock pays a dividend of $2.10 and investors require a return of 7%. What is the estimated value of the preferred stock? I HAVE ATTACHED EXCEL TOOL KITS TO ASSIST WITH THESE TWO QUESTION A 1 2 Tool Kit C D E Chapter 6 F 10/27/2015 Risk and Return 3 4 5 6 7 8 9 10 11 B 6-1 Investment Returns and Risk Amount invested Amount received in one year Dollar return (Profit) Rate of return = Profit/Investment = $1,000 $1,100 $100 10% 12 13 6-2 Measuring Risk for Discrete Distributions 14 15 16 17 18 19 20 21 The relationship between risk and return is a fundamental axiom in finance. Generally speaking, it is totally logical to assume that investors are only willing to assume additional risk if they are adequately compensated with additional return. This idea is rather fundamental, but the difficulty in finance arises from interpreting the exact nature of this relationship (accepting that risk aversion differs from investor to investor). Risk and return interact to determine security prices, hence it is of paramount importance in finance. 22 A listing of possible outcomes and their probabilities is called a probability distribution, as shown 23 below. 24 25 Rate of Scenario Probability of Return in 26 Scenario Scenario 27 Best Case 0.30 37% 28 Most Likely 0.40 11% 0.30 −15% 29 Worst Case 1.00 30 31 32 33 Figure 6-1 34 Discrete Probability Distribution for Three Scenarios 35 Probability of 36 Scenario 37 Most 0.5 Likely 38 39 0.4 Worst 40 0.4 Case 41 0.3 42 43 0.3 44 0.2 45 0.2 46 0.1 47 48 0.1 0.0 Best Case 49 50 51 52 53 54 55 0.1 A 0.0 B −15% C D E F 11% 37% Outcomes: Market Returns for 3 Scenarios 56 Given the probabilities and the outcomes for possible returns, it is possible to calculate the expected 57 return and standard deviation. A B C D E F 58 59 Figure 6-2 60 Calculating Expected Returns and Standard Deviations: Discrete Probabilities Standard Deviation 61 INPUTS: Expected Return Product of Squared Probability of Market Rate Probability and Deviation from Deviation Scenario of Return Return Expected Return (1) (2) (3) = (1) × (2) (4) = (2) − D66 (5) = (4)2 62 Scenario 63 Best Case 64 Most Likely 65 Worst Case 66 0.30 0.40 0.30 37% 11% −15% 1.00 Exp. ret. = 11.1% 4.4% −4.5% Sum = 11.0% 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Average = Std. dev. = 0.0002 0.0011 0.0054 0.0205 0.0575 0.1201 0.1870 0.2167 0.1870 0.1201 0.0575 0.0205 0.0054 0.0011 0.0002 1.0000 0.0198 0.0307 0.0452 0.0625 0.0806 0.0969 0.1082 0.1123 0.1082 0.0969 0.0806 0.0625 0.0452 0.0307 0.0198 1.0000 11.0% 20.2% 11.0% 36.2% Rate of Return in Scenario -66% -55% -44% -33% -22% -11% 0% 11% 22% 33% 44% 55% 66% 77% 88% Figure 6-3 Discrete Probability Distributions for 15 Scenarios Panel A: Market Return for 15 Scenarios: Standard Devation = 20.2% Probability 0.0676 0.0000 0.0676 Sum = Variance = Std. Dev. = Square root of variance = 67 68 Note: Calculations are not rounded in intermediate steps. 69 70 71 6-3 Risk in a Continuous Distribution 72 73 It is possible to add more scenarios. 74 Panel B: Panel A: Probability Probability of of Stock Scenario Market Return Return Scenario Scenario 75 0.2600 0.0000 -0.2600 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 A Probability 0.25 B C D E F 0.20 0.15 0.10 0.05 0.00 -66% -55% -44% -33% -22% -11% 0% 11% 22% 33% 44% 55% 66% 77% 88% Outcomes: Market Returns Panel B: Single Company's Stock Return for 15 Scenarios: Standard Devation = 36.2% Probability 0.25 0.20 0.15 0.10 0.05 0.00 −66% −55% −44% −33% −22% −11% 0% 11% 22% 33% 44% 55% 66% 77% 88% Outcomes: Stock Returns At some point, it becomes impractical to keep adding scenarios. Many analysts use the normal distribution to estimate stock returns. Here is an example of a normal distribution with a similar mean and standard deviation as the discrete distribution shown above. Normal Distribution Probability 0.2500 A 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 Probability B 0.2500 C D E F 0.2000 0.1500 0.1000 0.0500 0.0000 -100% -50% 0% 50% 100% Return 173 6-4 Using Historical Data to Estimate Risk 174 175 Investors often use historical data to estimate risk. This is quite easy in Excel by using the AVERAGE 176 and STDEV functions. 177 178 A 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 B C D Standard Deviation Based On a Sample of Historical Data Inputs: Year 2014 2015 2016 Calculations: =AVERAGE(E183:E185) =STDEV(E183:E185) E F Realized return 15.0% −5.0% 20.0% 10.0% 13.2% Measuring the Standard Deviation of MicroDrive The monthly stock returns for MicroDrive and one of its competitors, SnailDrive, during the past 48 months are shown in the figure below. The actual data are below the figure. Figure 6-5 Historical Monthly Stock Returns for MicroDrive and SnailDrive Monthly Rate of Return 50% MicroDrive 40% 30% SnailDrive 20% 10% 0% -10% -20% -30% 0 6 12 Average Return (annualized) 225 Standard Deviation (annualized) 226 227 228 229 230 18 24 30 Month of Return MicroDrive 14.6% 49.2% 36 42 48 SnailDrive 8.6% 25.8% Portfolio weights SnailDrive: MicroDrive: A B C 231 Period 232 1 233 2 234 3 235 4 236 5 237 6 238 7 239 8 240 9 241 10 242 11 243 12 244 13 245 14 246 15 247 16 248 17 249 18 250 19 251 20 252 21 253 22 254 23 255 24 256 25 257 26 258 27 259 28 260 29 261 30 262 31 263 32 264 33 265 34 266 35 267 36 268 37 269 38 270 39 271 40 272 41 273 42 274 43 275 44 276 45 277 46 278 47 279 48 280 Full 48 Months 281 Average monthly return: 282 Standard deviation of monthly returns: 283 Average return (annual): 284 Standard deviation (annual): D Market 2.37% 12.68% -1.13% 10.93% -0.02% -3.31% 12.68% -3.96% -4.90% 7.10% 2.94% -6.52% 3.72% 4.74% -8.21% -5.15% 3.92% 1.08% -2.48% 3.92% 3.13% 0.17% 5.17% 2.56% -5.41% -2.09% 1.08% 10.47% -3.74% 2.94% -9.50% 5.17% -0.75% -9.04% -9.50% 4.74% -0.38% 4.32% -1.89% -3.96% 6.58% -1.32% 4.74% -3.10% 7.95% 10.93% -1.70% -3.96% Market 0.9% 5.8% 11.0% 20.0% E MicroDrive 1.66% 23.52% -4.76% 38.58% -3.46% -5.37% 22.52% -8.58% -13.02% 0.17% 24.40% -18.05% 6.18% 12.24% -18.22% 7.15% 9.69% 12.21% -6.74% -16.00% -11.96% -19.00% 13.91% 17.84% 14.67% -16.88% 3.28% 28.86% 2.33% 12.48% -7.21% -9.79% 0.60% 0.88% -8.94% 2.49% -11.24% -9.47% -20.12% -0.15% 3.42% 4.07% -13.45% -13.05% -1.61% 29.01% 6.08% -2.82% MicroDrive 1.22% 14.19% 14.6% 49.2% F SnailDrive -7.41% 2.15% -0.16% -5.34% 10.13% 1.82% 0.67% -4.07% -3.25% 17.04% 4.28% 0.41% -6.90% 2.98% 0.29% -12.43% -0.48% -6.26% 4.44% 13.02% 9.67% -2.26% 2.90% 4.74% -10.96% 4.34% 4.32% 9.32% -3.34% -3.53% -1.01% 10.33% -7.79% -10.85% -9.91% 9.61% -0.01% 1.40% 4.37% -8.09% 16.51% 9.28% 1.72% -5.96% 12.41% -1.59% -12.09% -0.08% SnailDrive 0.72% 7.45% 8.6% 25.8% A 285 286 287 Past 12 Months 288 289 290 291 292 293 294 295 296 297 298 299 300 Past 12 Months 301 302 303 304 305 306 B C Maximum of monthly returns: Minimum of monthly returns: Month 37 38 39 40 41 42 43 44 45 46 47 48 Average return (annual): Standard deviation (annual): Total compound return: D 12.7% -9.5% Market -0.4% 4.3% -1.9% -4.0% 6.6% -1.3% 4.7% -3.1% 7.9% 10.9% -1.7% -4.0% Market 18.2% 17.8% 18.2% E 38.6% -20.1% MicroDrive -11.2% -9.5% -20.1% -0.2% 3.4% 4.1% -13.5% -13.0% -1.6% 29.0% 6.1% -2.8% MicroDrive -29.3% 44.5% -32.1% F 17.0% -12.4% SnailDrive 0.0% 1.4% 4.4% -8.1% 16.5% 9.3% 1.7% -6.0% 12.4% -1.6% -12.1% -0.1% SnailDrive 17.9% 28.8% 15.1% 307 6-5 Risk in a Portfolio Context 308 309 Now we are going to analyze the risk of a portfolio instead of the stand-alone risk of individual 310 assets. 311 312 Creating a Portfolio 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 Look at the data for MicroDrive and SnailDrive shown above. The last column shows a portfolio with the weights shown below. Here are the results for the two companies and for the portfolio. Notice that the portfolio has a higher return than SnailDrive and less risk than either of the two stocks. Portfolio weights SnailDrive: MicroDrive: 75% 25% Full 48 Months Average monthly return: Standard deviation of monthly returns: Average return (annual): Standard deviation (annual): Market 0.9% 5.8% 11.0% 20.0% MicroDrive 1.2% 14.2% 14.6% 49.2% Correlation 333 Loosely speaking, correlation measures the tendency of two variables to move together. 334 335 Correlation between MicroDrive and SnailDrive: 336 r= -0.104 =CORREL(E232:E279,F232:F279) SnailDrive 0.7% 7.4% 8.6% 25.8% A B C D E F 337 338 339 6-6 The Relevant Risk of a Stock: The Capital Asset Pricing Model (CAPM) 340 341 The Capital Asset Pricing Model (CAPM) provides a measure of risk. 342 343 Contribution to Market Risk: Beta 344 345 The relevant risk of an individual stock as defined by its beta. Beta measures how much risk a stock 346 contributes to a well-diversified portfolio. 347 Beta for Stock i = bi = riM(si/sM) 348 349 350 A portfolio's beta is the weighted average of the stock's individual betas. Consider the following 351 example. 352 Contribution of 353 354 Weight in Stock to Portfolio Beta: 355 Stock Beta: Portfolio: bi wi bi x wi x sM 356 357 358 359 360 361 362 Stock 1 Stock 2 Stock 3 Stock 4 0.6 1.2 1.2 1.4 25.0% 25.0% 25.0% 25.0% Portfolio beta = 0.150 0.300 0.300 0.350 1.100 363 The standard deviation of a well-diversified portfolio is: 364 Std. Dev. of portfolio = sp = bp (sM) 365 Note: if the bp is negative, then σp = |bp| (σM). 366 367 If the example portfolio had more than 4 stocks and was well-diversified, then its standard 368 deviation would be: 369 Beta of portfolio = bp = 1.1 370 371 372 373 374 375 Figure 6-7 Std. Dev. of market = sM = 20% Std. Dev. of portfolio = sp = 22% 376 The Contribution of Individual Stocks to Portfolio Risk: The Effect of Beta 377 378 Portfolio standard deviation = 22% b1w1sM = 3.0% 379 380 381 382 383 b4w4sM = 7.0% A B C D E F 384 385 b2w2sM = 6.0% 386 387 388 389 b3w3sM = 6.0% 390 391 392 393 394 395 396 397 398 Market standard deviation = sM = 20.0% Stock Beta: bi 399 Contribution of Contribution of Weight in Stock to Portfolio Stock to Portfolio Beta: Risk: Portfolio: wi bi x wi bi x wi x sM 400 Stock 1 0.6 25.0% 0.150 3.0% 401 Stock 2 1.2 25.0% 0.300 6.0% 402 Stock 3 1.2 25.0% 0.300 6.0% 403 Stock 4 1.4 25.0% 0.350 7.0% 1.100 22.0% 404 405 406 407 Estimating Beta 408 409 We can use the data shown previously for MicroDrive and SnailDrive to estimate their betas. 410 411 412 Calculating Beta Market MicroDrive SnailDrive 413 Standard deviation (annual): 20.0% 49.17% 25.80% 414 Correlation with the market: 0.582 0.465 bi = riM(si/sM) 1.430 0.600 415 416 417 Beta can also be calculated as the slope of a regression of the stock (on the y-axis) and the market 418 (on the x-axis). This can be done using the SLOPE function or by plotting the returns and specifying 419 that the chart show the TRENDLINE. 420 Calculating Beta as the Slope of a Regression Using Excel Functions (See Excel explanations to right) 421 422 423 424 425 bi = riM(si/sM) Intercept R squared 426 Calculating Confidence Intervals using Excel Functions 427 Input desired probability for confidence interval MicroDrive 1.430 -0.001 0.338 95% SnailDrive 0.600 0.002 0.216 95% A B C D E F 428 Lower boundary of confidence interval for beta 0.836 0.261 429 Upper boundary of confidence interval for beta 2.024 0.939 430 Lower boundary of confidence interval for intercept -0.035 -0.018 431 Upper boundary of confidence interval for intercept 0.033 0.021 432 433 434 Figure 6-8 435 Stock Returns of MicroDrive and the Market: Estimating Beta 436 y-axis: 437 Historical 438 MicroDrive 439 Returns 440 45.0% 441 442 443 444 y = 1.43x - 0.001 445 R² = 0.3383 446 447 448 Market vs. 449 Market 450 451 452 0.0% 453 -45% 0% 45% 454 455 x-axis: Historical 456 Market Returns 457 458 459 460 461 462 463 464 465 -45.0% 466 467 468 469 470 A 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 B C D E F EXAMPLE: CALCULATING BETA COEFFICIENTS FOR AN ACTUAL COMPANY Now we show how to calculate beta for an actual company, General Electric. Step 1. Retrieve Data We downloaded stock prices and dividends from http://finance.yahoo.com for General Electric, using its ticker symbol GE, and for the S&P 500 Index ( symbol ^SPX), which contains 500 actively traded large stocks. For example, to download the GE data, enter its ticker symbol in the upper left section and click Go. Then select Historical Prices from the upper left side of the new page. After the daily prices come up, click monthly prices, enter a start and stop date, and click "Get Prices." When presenting monthly data, the date shown is for the first date in the month, but the data are actually for the last day of trading in the month , so be alert for this. Note that these prices are "adjusted" to reflect any dividends or stock splits. Scroll to the bottom of the page and click "Download to Spreadsheet." The downloaded data are in csv format. Convert to xls by opening a new Excel worksheet, copying the date and adjusted index price data to it, and saving as an xls file. Then repeat the process to get the S&P index data. At this point you have returns data for GE and the S&P Index, as we show below. Step 2. Calculate Returns Next, calculate the percentage change in adjusted prices (which already reflect dividends) for GE and the S&P to obtain returns, with the spreadsheet set up as shown below. Yahoo actually adjusts the stock prices to reflect any stock splits or dividend payments. For example, suppose the stock price is $100 in July, the company has a 2-for-1 split, and the actual price in August is $60. The reported adjusted price for August would be $60, but the reported price for July would be $50, which reflects the stock split. This gives an accurate stock return of 20%: ($60-$50)/$50 = 20%, the same as if there had not been a split, in which case the return would have been ($120-$100)/$100 = 20%. Or suppose the actual price in September is $50, the company pays a $10 dividend, and the actual price in October is $60. Shareholders have had a return of ($60+$10-$50)/$50 = 40%. Yahoo reports an adjusted price of $60 for October, and an adjusted price of $42.857 for September, which gives a return of ($60-$42.857)/$42.857 = 40%. In other words, the percent change in the adjusted price accurately reflects the actual return. 507 At this point, we are ready to calculate some statistics and to find GE's beta coefficient. This is shown 508 below the data. 509 510 511 Not in Textbook: Stock Return Data for GE and the S&P 500 Index 512 513 514 515 516 517 518 519 Month February 2015 January 2015 December 2014 November 2014 October 2014 September 2014 August 2014 Market Level (S&P 500 Index) at Month End 2,104.50 1,994.99 2,058.90 2,067.56 2,018.05 1,972.29 2,003.37 Market's Return 5.5% -3.1% -0.4% 2.5% 2.3% -1.6% 3.8% GE Adjusted Stock Price at Month End $25.99 $23.67 $25.04 $26.00 $25.34 $25.15 $25.29 GE's Return 9.8% -5.5% -3.7% 2.6% 0.8% -0.6% 3.3% 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 A July 2014 June 2014 May 2014 April 2014 March 2014 February 2014 January 2014 December 2013 November 2013 October 2013 September 2013 August 2013 July 2013 June 2013 May 2013 April 2013 March 2013 February 2013 January 2013 December 2012 November 2012 October 2012 September 2012 August 2012 July 2012 June 2012 May 2012 April 2012 March 2012 February 2012 January 2012 December 2011 November 2011 October 2011 September 2011 August 2011 July 2011 June 2011 May 2011 April 2011 March 2011 February 2011 B 1,930.67 1,960.23 1,923.57 1,883.95 1,872.34 1,859.45 1,782.59 1,848.36 1,805.81 1,756.54 1,681.55 1,632.97 1,685.73 1,606.28 1,630.74 1,597.57 1,569.19 1,514.68 1,498.11 1,426.19 1,416.18 1,412.16 1,440.67 1,406.58 1,379.32 1,362.16 1,310.33 1,397.91 1,408.47 1,365.68 1,312.41 1,257.60 1,246.96 1,253.30 1,131.42 1,218.89 1,292.28 1,320.64 1,345.20 1,363.61 1,325.83 1,327.22 C -1.5% 1.9% 2.1% 0.6% 0.7% 4.3% -3.6% 2.4% 2.8% 4.5% 3.0% -3.1% 4.9% -1.5% 2.1% 1.8% 3.6% 1.1% 5.0% 0.7% 0.3% -2.0% 2.4% 2.0% 1.3% 4.0% -6.3% -0.7% 3.1% 4.1% 4.4% 0.9% -0.5% 10.8% -7.2% -5.7% -2.1% -1.8% -1.4% 2.8% -0.1% NA Description of Data Average return (annual): 12.2% Standard deviation (annual): 11.5% Minimum monthly return: -7.2% Maximum monthly return: 10.8% Correlation between GE and the market: 569 Beta: bGE = rGE,M (sGE / sM) 570 571 Beta (using the SLOPE function): 572 Intercept (using the INTERCEPT function): D $24.48 $25.58 $25.86 $25.96 $25.00 $24.59 $24.05 $26.83 $25.31 $24.82 $22.68 $21.80 $22.96 $21.85 $21.80 $20.83 $21.61 $21.70 $20.66 $19.46 $19.41 $19.35 $20.86 $18.88 $18.92 $19.00 $17.21 $17.65 $18.09 $17.17 $16.72 $16.00 $14.08 $14.79 $13.47 $14.30 $15.70 $16.53 $17.08 $17.78 $17.43 $18.19 E -4.3% -1.1% -0.4% 3.8% 1.7% 2.2% -10.4% 6.0% 2.0% 9.4% 4.0% -5.1% 5.1% 0.2% 4.7% -3.6% -0.4% 5.0% 6.2% 0.3% 0.3% -7.2% 10.5% -0.2% -0.4% 10.4% -2.5% -2.4% 5.4% 2.7% 4.5% 13.6% -4.8% 9.8% -5.8% -8.9% -5.0% -3.2% -3.9% 2.0% -4.2% NA 10.7% 18.9% -10.4% 13.6% 0.75 1.23 1.23 0.00 F A B 573 R2 (using the RSQ function): 574 575 C D E 0.57 F 576 577 578 579 580 581 582 583 584 585 A B C D E Step 3. Examine the Data and Calculate Beta Using the AVERAGE function and the STDEV function, we found the average historical return and standard deviation for GE and the market. (We converted these from monthly figures to annual figures. Notice that you must multiply the monthly standard deviation by the square root of 12, and not 12, to convert it to an annual basis.) These are shown in the rows above. We also used the CORREL function to find the correlation between GE and the market. We used the SLOPE, INTERCEPT, and RSQ functions to estimate the regression for beta. F 586 6-7 The Relationship between Risk and Return in the Capital Asset Pricing Model 587 588 The SML shows the relationship between the stock's beta and its required return, as predicted by the CAPM. 589 6%
Purchase answer to see full attachment