1 2 3 4 5 6 7 8 A B Problem 12-11 C D E F G H Use the data far below to estimate the beta of Nike (NKE) and HP (HPQ) stock based on their monthly returns from 2011-2017. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Nike beta HP beta A B 11 D E F G H Excess Returns 12 Date Risk-free Rate NKE HPQ S&P 500 NKE HPQ S&P 500 13 Jan-11 0.001450 -0.034418 0.085273 0.023300 -0.035868 0.083823 0.021850 14 Feb-11 0.001350 0.079413 -0.045086 0.034737 0.078063 -0.046436 0.033387 15 Mar-11 0.000900 -0.135797 -0.056930 -0.000053 -0.136697 -0.057830 -0.000953 16 Apr-11 0.000400 0.087451 -0.014645 0.028961 0.087051 -0.015045 0.028561 17 May-11 0.000400 0.025875 -0.074065 -0.011215 0.025475 -0.074465 -0.011615 18 Jun-11 0.000150 0.080166 -0.019148 -0.017064 0.080016 -0.019298 -0.017214 19 Jul-11 0.000900 0.001889 -0.033791 -0.020005 0.000989 -0.034691 -0.020905 20 Aug-11 0.000150 -0.038824 -0.259881 -0.054976 -0.038974 -0.260031 -0.055126 21 Sep-11 0.000100 0.001154 -0.127382 -0.069097 0.001054 -0.127482 -0.069197 22 Oct-11 0.000050 0.126769 0.185301 0.109147 0.126719 0.185251 0.109097 23 Nov-11 0.000150 -0.001764 0.050357 -0.004064 -0.001914 0.050207 -0.004214 24 Dec-11 0.000050 0.016947 -0.068900 0.010241 0.016897 -0.068950 0.010191 25 Jan-12 0.000550 0.079070 0.086180 0.046375 0.078520 0.085630 0.045825 26 Feb-12 0.000750 0.037792 -0.095425 0.043405 0.037042 -0.096175 0.042655 27 Mar-12 0.000650 0.018162 -0.048035 0.032141 0.017512 -0.048685 0.031491 28 Apr-12 0.000900 0.031630 0.039026 -0.006676 0.030730 0.038126 -0.007576 29 May-12 0.000600 -0.020113 -0.084007 -0.060056 -0.020713 -0.084607 -0.060656 30 Jun-12 0.000800 -0.188575 -0.100500 0.040450 -0.189375 -0.101300 0.039650 31 Jul-12 0.001000 0.063454 -0.092989 0.011830 0.062454 -0.093989 0.010830 32 Aug-12 0.000850 0.058382 -0.074561 0.025053 0.057532 -0.075411 0.024203 33 Sep-12 0.000900 -0.025164 0.027883 0.025425 -0.026064 0.026983 0.024525 34 Oct-12 0.001050 -0.037193 -0.188159 -0.018198 -0.038243 -0.189209 -0.019248 35 Nov-12 0.000750 0.066754 -0.062094 0.005660 0.066004 -0.062844 0.004910 36 Dec-12 0.000350 0.075913 0.119374 0.009019 0.075563 0.119024 0.008669 37 Jan-13 0.000650 0.047481 0.158596 0.051190 0.046831 0.157946 0.050540 38 Feb-13 0.001000 0.015356 0.219867 0.012759 0.014356 0.218867 0.011759 39 Mar-13 0.000650 0.083548 0.198146 0.037953 0.082898 0.197496 0.037303 40 Apr-13 0.000450 0.077783 -0.135906 0.019212 0.077333 -0.136356 0.018762 41 May-13 0.000300 -0.023899 0.185437 0.023610 -0.024199 0.185137 0.023310 42 Jun-13 0.000300 0.032760 0.028654 -0.013405 0.032460 0.028354 -0.013705 43 Jul-13 0.000300 -0.011935 0.035484 0.051677 -0.012235 0.035184 0.051377 44 Aug-13 0.000200 0.005086 -0.130062 -0.029992 0.004886 -0.130262 -0.030192 45 Sep-13 0.000050 0.156320 -0.046118 0.031763 0.156270 -0.046168 0.031713 46 Oct-13 0.000330 0.042952 0.161029 0.046307 0.042622 0.160699 0.045977 47 Nov-13 0.000550 0.044615 0.122282 0.029638 0.044065 0.121732 0.029088 48 Dec-13 0.000630 -0.000253 0.034725 0.025802 -0.000883 0.034095 0.025172 49 Jan-14 0.000100 -0.073627 0.036455 -0.035248 -0.073727 0.036355 -0.035348 50 Feb-14 0.000430 0.081400 0.030345 0.045516 0.080970 0.029915 0.045086 51 Mar-14 0.000280 -0.056705 0.093699 0.008291 -0.056985 0.093419 0.008011 52 Apr-14 0.000200 -0.012321 0.021632 0.006952 -0.012521 0.021432 0.006752 53 May-14 0.000300 0.060864 0.013309 0.023206 0.060564 0.013009 0.022906 54 Jun-14 0.000200 0.008321 0.015890 0.020639 0.008121 0.015690 0.020439 55 Jul-14 0.000180 -0.005416 0.057304 -0.013438 -0.005596 0.057124 -0.013618 56 Aug-14 0.000180 0.024634 0.067116 0.039464 0.024454 0.066936 0.039284 57 Sep-14 0.000130 0.135582 -0.057307 -0.013705 0.135452 -0.057437 -0.013835 58 Oct-14 0.000030 0.042265 0.011559 0.023551 0.042235 0.011529 0.023521 59 Nov-14 0.000050 0.067979 0.088629 0.027472 0.067929 0.088579 0.027422 60 Dec-14 0.000370 -0.025984 0.036414 -0.002534 -0.026354 0.036044 -0.002904 61 Jan-15 0.000050 -0.040562 -0.099676 -0.029629 -0.040612 -0.099726 -0.029679 62 Feb-15 0.000080 0.058862 -0.035704 0.056205 0.058782 -0.035784 0.056125 J 62 63 A B C D E F G H Feb-15 0.000080 0.058862 -0.035704 0.056205 0.058782 -0.035784 0.056125 Mar-15 0.000250 0.033052 -0.095513 -0.015661 0.032802 -0.095763 -0.015911 64 Apr-15 0.000050 -0.014851 0.058087 0.009834 -0.014901 0.058037 0.009784 65 May-15 0.000030 0.034298 0.013042 0.012856 0.034268 0.013012 0.012826 66 Jun-15 0.000100 0.062457 -0.089894 -0.019986 0.062357 -0.089994 -0.020086 67 Jul-15 0.000580 0.066654 0.016994 0.022148 0.066074 0.016414 0.021568 68 Aug-15 0.000030 -0.030116 -0.080603 -0.061345 -0.030146 -0.080633 -0.061375 69 Sep-15 0.000050 0.105414 -0.073501 -0.024889 0.105364 -0.073551 -0.024939 70 Oct-15 0.000680 0.065544 0.052714 0.085286 0.064864 0.052034 0.084606 71 Nov-15 0.001700 0.009540 0.024224 0.003655 0.007840 0.022524 0.001955 72 Dec-15 0.001480 -0.050197 -0.045933 -0.017291 -0.051677 -0.047413 -0.018771 73 Jan-16 0.003000 -0.007840 -0.179899 -0.049787 -0.010840 -0.182899 -0.052787 74 Feb-16 0.003080 -0.006773 0.100927 -0.000826 -0.009853 0.097847 -0.003906 75 Mar-16 0.001930 0.000649 0.164079 0.067212 -0.001281 0.162149 0.065282 76 Apr-16 0.001930 -0.041158 -0.004058 0.003941 -0.043088 -0.005988 0.002011 77 May-16 0.002830 -0.063115 0.090465 0.017012 -0.065945 0.087635 0.014182 78 Jun-16 0.002480 0.002535 -0.052765 0.003424 0.000055 -0.055245 0.000944 79 Jul-16 0.002400 0.005435 0.116335 0.036471 0.003035 0.113935 0.034071 80 Aug-16 0.003200 0.038559 0.025696 0.001198 0.035359 0.022496 -0.002002 81 Sep-16 0.002600 -0.083796 0.089353 0.000010 -0.086396 0.086753 -0.002590 82 Oct-16 0.002950 -0.046914 -0.066967 -0.017337 -0.049864 -0.069917 -0.020287 83 Nov-16 0.004680 -0.002192 0.062802 0.036838 -0.006872 0.058122 0.032158 84 Dec-16 0.004800 0.018774 -0.027747 0.020324 0.013974 -0.032547 0.015524 85 Jan-17 0.005000 0.040724 0.014151 0.017895 0.035724 0.009151 0.012895 86 Feb-17 0.005230 0.080529 0.154153 0.039292 0.075299 0.148923 0.034062 87 Mar-17 0.007380 -0.021868 0.037000 0.001281 -0.029248 0.029620 -0.006099 88 Apr-17 0.007800 -0.005742 0.052573 0.009926 -0.013542 0.044773 0.002126 89 May-17 0.009500 -0.043674 -0.003188 0.014113 -0.053174 -0.012688 0.004613 90 Jun-17 0.009930 0.116814 -0.061157 0.006391 0.106884 -0.071087 -0.003539 91 Jul-17 0.010530 0.000847 0.092677 0.020554 -0.009683 0.082147 0.010024 92 Aug-17 0.009780 -0.102625 -0.001047 0.002918 -0.112405 -0.010827 -0.006862 93 Sep-17 0.010280 -0.018178 0.053076 0.020102 -0.028458 0.042796 0.009822 94 Oct-17 0.011230 0.060559 0.079659 0.023564 0.049329 0.068429 0.012334 95 Nov-17 0.012380 0.098745 -0.004640 0.030566 0.086365 -0.017020 0.018186 96 Dec-17 0.013550 0.038563 -0.014019 0.012079 0.025013 -0.027569 -0.001471 97 98 Requirements 99 1. Start Excel completed. 100 101 102 103 104 2. In cell D6, by using cell references and the function SLOPE, calculate the beta of the Nike stock (1 pt.). Hint: Use the excess return for Nike stock as the known y's and the excess return of the S&P 500 as the known x's. 3. In cell D7, by using cell references and the function SLOPE, calculate the beta of the HP stock (1 pt.). Hint: Use the excess return for HP stock as the known y's and the excess return of the S&P 500 as the known x's. 4. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 105