MINICASE 1 You need to show your working notes in excel for credit. You must sub
ID: 2818793 • Letter: M
Question
MINICASE 1
You need to show your working notes in excel for credit. You must submit your work using excel files (with .xls or .xlsx for credit).
This assignment will require you to analyze time series of monthly returns.
Start by retrieving MONTHLY data for the period of 01/01/2015 – 08/31/2018 from Yahoo website for
S&P 500 Index (ticker: ^GSPC)
General Electric Company (ticker: GE) Intel Corporation (ticker: INTC)
Chevron Corporation (ticker: CVX)
Apple Inc. (ticker: AAPL)
Instructions for downloading the data from Yahoo! Website (https://finance.yahoo.com/): To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click on “Historical Data”. Enter “Time Period” as given above. For “Frequency”, make sure Monthly is selected and then click on “Apply”. Click on “Download Data”.
Saving your final file:
Keep only Date and Adj Close columns for each company. Put all five sets of data in one excel file to do further analysis.
Very important: Save your final file as a .xls or .xlsx file before you start the computations. Comma delimited (.csv) files do not retain the formulae and cell references after closing the file. You will receive a grade of zero if your file does not contain cell references and formulae that show how you arrived at the various answers.
Calculating Returns:
Use the ‘Adj Close’ column to obtain returns for each period. Remember that the Adjusted Close column has already adjusted the prices for dividends and stock splits so you do not have to adjust for it again. Just use the adjusted close column to obtain the return R for each month t as: Rt = Adj Closet/Adj Closet-1 – 1
Solve for the following:
What is the average monthly return and standard deviation of returns for the 01/01/2015 – 8/31/2018 period for
(i) S&P 500
(ii) GE
(iii) Intel
(iv) Chevron
(v) Apple
(vi) Comment on your findings
Calculate the correlation between:
(i) GE and Intel
(ii) GE and Chevron
(iii) GE and Apple
(iv) Intel and Chevron
(v) Intel and Apple
(vi) Chevron and Apple
(vii) Comment on your findings
Calculate the stock betas for the 01/01/2015 – 8/31/2018 period for:
(i) GE
(ii) Intel
(iii) Chevron
(iv) Apple
(v) Comment on your findings.
If you were to form a portfolio that was equally invested in GE, Intel, and Chevron, what would be the average return and the standard deviation of returns for your portfolio?
If you were to add Apple to your portfolio so that the portfolio was equally invested in GE, Intel, Chevron, and Apple, what would be the new average return and standard deviation of returns for your portfolio? Is Apple a good addition to your portfolio? Why do you think so?
Explanation / Answer
Return for apple is highest and is most profitable while GE has performed worst. Apple and intel have performed better than market benchmark ie S&P 500 index.
Standard deviation of apple is most indicating its stock price is more volatile. If we see beta, For apple Beta is also most as beta is also a measure of volatility with respect to overall market
Date S&P 500 AAPL CVX GE Intel S&P 500 AAPL CVX GE Intel 01-01-2015 1994.99 109.6473 87.80766 20.97501 29.6411 5.4893% 9.6449% 4.0476% 8.7903% 0.6356% 01-02-2015 2104.5 120.2227 91.36176 22.81878 29.829502 -1.7396% -2.7549% -0.6267% -3.6627% -5.2803% 01-03-2015 2067.89 116.9107 90.78915 21.983 28.254402 0.8521% 0.5787% 5.7916% 9.1495% 4.0934% 01-04-2015 2085.51 117.5872 96.04728 23.99434 29.410965 1.0491% 4.0991% -7.2573% 0.7016% 5.8679% 01-05-2015 2107.39 122.4072 89.07681 24.16269 31.136766 -2.1012% -3.3206% -5.4065% -2.5669% -11.0854% 01-06-2015 2063.11 118.3426 84.26084 23.54245 27.685135 1.9742% -3.2927% -8.2823% -0.9334% -4.8324% 01-07-2015 2103.84 114.4459 77.28207 23.32271 26.34729 -6.2581% -6.9085% -8.4652% -4.1762% -1.4162% 01-08-2015 1972.18 106.5394 70.73999 22.3487 25.974154 -2.6443% -1.8781% -1.3781% 0.8396% 6.4835% 01-09-2015 1920.03 104.5386 69.76513 22.53635 27.658178 8.2983% 8.3409% 15.2130% 15.6971% 12.3424% 01-10-2015 2079.36 113.258 80.37848 26.07391 31.071863 0.0505% -1.0042% 0.4842% 3.5270% 2.6876% 01-11-2015 2080.41 112.1207 80.76763 26.99353 31.906937 -1.7530% -10.6419% -0.2861% 4.0414% -0.2224% 01-12-2015 2043.94 100.1888 80.53654 28.08446 31.835972 -5.0735% -7.5242% -3.8795% -5.8823% -9.9564% 01-01-2016 1940.24 92.65039 77.41213 26.43244 28.666241 -0.4128% -0.6677% -3.5041% 0.1374% -4.6099% 01-02-2016 1932.23 92.03172 74.69953 26.46877 27.344746 6.5991% 13.3327% 15.7838% 9.9674% 10.2897% 01-03-2016 2059.74 104.302 86.48998 29.10702 30.158445 0.2699% -13.9921% 7.1069% -3.2715% -6.3988% 01-04-2016 2065.3 89.70799 92.63674 28.15479 28.22868 1.5325% 6.5287% -1.1548% -1.6911% 4.3263% 01-05-2016 2096.95 95.56475 91.56695 27.67868 29.449932 0.0911% -3.6831% 4.8893% 4.1350% 4.7272% 01-06-2016 2098.86 92.04501 96.04391 28.82318 30.842089 3.5610% 9.0063% -2.2417% -0.3307% 6.2805% 01-07-2016 2173.6 100.3348 93.89085 28.72787 32.779121 -0.1219% 1.8137% -1.8540% 0.3211% 2.9547% 01-08-2016 2170.95 102.1546 92.15011 28.82013 33.747635 -0.1234% 7.1276% 3.4047% -5.1857% 5.9798% 01-09-2016 2168.27 109.4358 95.28752 27.32561 35.765675 -1.9426% 0.4334% 1.7781% -0.9888% -7.6291% 01-10-2016 2126.15 109.9101 96.98182 27.05541 33.037064 3.4175% -2.6598% 6.5012% 5.7045% -0.4875% 01-11-2016 2198.81 106.9866 103.2868 28.59878 32.875999 1.8201% 5.3335% 6.5600% 2.7308% 5.3159% 01-12-2016 2238.83 112.6928 110.0624 29.37976 34.623661 1.7884% 4.7747% -5.3951% -5.3053% 1.5164% 01-01-2017 2278.87 118.0735 104.1244 27.82107 35.148693 3.7198% 12.8883% 1.0328% 0.3704% -1.6839% 01-02-2017 2363.64 133.2912 105.1998 27.92411 34.556831 -0.0389% 5.3236% -3.6464% 0.7638% 0.3522% 01-03-2017 2362.72 140.3872 101.3638 28.13739 34.678543 0.9091% -0.0070% -0.6240% -2.7181% 0.2218% 01-04-2017 2384.2 140.3774 100.7312 27.37258 34.755466 1.1576% 6.3418% -3.0178% -5.5536% -0.1107% 01-05-2017 2411.8 149.2799 97.69136 25.85241 34.717003 0.4814% -5.3322% 1.8470% -1.3514% -5.8694% 01-06-2017 2423.41 141.3199 99.49571 25.50305 32.679306 1.9349% 3.2704% 4.6583% -3.5838% 5.1274% 01-07-2017 2470.3 145.9416 104.1305 24.58907 34.354916 0.0546% 10.2669% -1.4379% -4.1390% -1.1277% 01-08-2017 2471.65 160.9253 102.6333 23.57133 33.967491 1.9303% -5.6554% 10.2774% -1.5071% 9.3964% 01-09-2017 2519.36 151.8244 113.1813 23.21607 37.159225 2.2188% 9.6808% -1.3702% -15.7922% 19.4590% 01-10-2017 2575.26 166.5222 111.6305 19.54973 44.390053 0.3720% 1.6623% 2.6750% -9.2758% -1.4289% 01-11-2017 2584.84 169.2903 114.6166 17.73634 43.755768 3.4343% -1.1706% 6.1954% -4.5927% 3.5527% 01-12-2017 2673.61 167.3086 121.7176 16.92177 45.310299 5.6179% -1.0636% 0.1278% -6.0607% 4.2894% 01-01-2018 2823.81 165.5291 121.8731 15.89619 47.253849 -3.8947% 6.3848% -10.7140% -12.7396% 2.3889% 01-02-2018 2713.83 176.0977 108.8156 13.87107 48.382683 -2.6884% -5.4210% 2.9085% -3.6743% 6.3772% 01-03-2018 2640.87 166.5514 111.9806 13.36141 51.468147 0.2719% -1.5020% 9.7071% 4.3769% -0.8833% 01-04-2018 2648.05 164.0498 122.8507 13.94622 51.01355 2.1608% 13.0764% -0.6474% 0.0711% 6.9353% 01-05-2018 2705.27 185.5016 122.0553 13.95613 54.551491 0.4842% -0.5598% 2.6005% -3.3381% -9.4259% 01-06-2018 2718.37 184.4631 125.2294 13.49026 49.409515 3.6022% 2.7983% -0.1266% 1.0358% -3.2388% 01-07-2018 2816.29 189.625 125.0709 13.63 47.809246 3.0263% 19.6227% -6.1852% -5.0624% 0.6861% 01-08-2018 2901.52 226.8345 117.3351 12.94 48.137253 Average return 0.9157% 1.9370% 0.8393% -0.9540% 1.3163% Standard deviation 2.8673% 6.8889% 5.7722% 5.6988% 6.0926% S&P 500 AAPL CVX GE intel Correlation i) GE and Intel 0.100906 (ii) GE and Chevron 0.568386 (iii) GE and Apple 0.03177 (iv) Intel and Chevron 0.32023 (v) Intel and Apple 0.424739 (vi) Chevron and Apple -0.0285 Stock Beta GE 0.852481 Intel 0.914831 Chevron 1.050851 Apple 1.249376Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.