Discussion Response

north
HomeworkProject.xlsx
Home>Business & Finance homework help>Discussion Response

Export Summary

This document was exported from Numbers. Each table was converted to an Excel worksheet. All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel.
Numbers Sheet Name Numbers Table Name Excel Worksheet Name
Sheet1
Table 1 Sheet1
Beta
Table 1 Beta
R-SQUARED
Table 1 R-SQUARED
Lowest Risk and Optimal Portfol
Table 1 Lowest Risk and Optimal Portfol

Sheet1

A) S&P500 Stocks
Sector: NAME Recent price Price 5 year Price 10 year Historic Average Geometric Average
5 Years 10 Years 5 Years 10 Years
Consumer Dicretionary Apple 115.31 70.74 80 63% 44% 8.114 2.631
Information Technology Adobe 494.59 344.1 385.26 44% 28% 29.298 10.033
Consumer Staples General mills 61.41 54.95 54.7 12% 12% 0.492 -0.229
Materials Applied materials 64.56 51.74 52.04 25% 24% 1.764 0.352
Energy American electric power 91.61 81.39 78.43 13% 17% 1.244 0.418
Real Estate Alexandria real estate equities 158.92 149.49 146.99 6% 8% 1.086 0.293
Financials Ameriprise Financial 172.37 107.48 121.98 60% 41% 12.178 4.139
Commercials Services Accenture 230.57 175.46 193.71 31% 19% 10.222 2.786
Healthcare Abbott Laboratories 114.11 94.06 89.89 21% 27% 3.21 1.522
Utilities 3M 164.78 147 78.56 12% 110% 2.756 7.722
Industrials A.O Smith 53.28 40.39 42.22 32% 26% 1.778 0.206
Variance is the expectation of the squared deviation of a random variable from its mean. Informally, it measures how far a set of numbers is spread out from their average while standard deviation is a measure of the amount of variation or dispersion of a set of values (Sharma, Sharma and Singh, 2018).
Sector: NAME Recent price Price 5 Year Price 10 year Variance Standard Deviation
5 Years 10 Years 5 Years 10 Years
Consumer Dicretionary Apple 115.31 70.74 80 37.21 26.605 6.10 5.16
Information Technology Adobe 494.59 344.1 385.26 167.738 122.395 12.95 11.06
Consumer Staples General mills 61.41 54.95 54.7 23.272 17.106 4.82 4.14
Materials Applied materials 64.56 51.74 52.04 23.26 16.834 4.82 4.10
Energy American electric power 91.61 81.39 78.43 34.6 25.143 5.88 5.01
Real Estate Alexandria real estate equities 158.92 149.49 146.99 61.682 45.54 7.85 6.75
Financials Ameriprise Financial 172.37 107.48 121.98 55.97 40.183 7.48 6.34
Commercials Services Accenture 230.57 175.46 193.71 81.206 59.974 9.01 7.74
Healthcare Abbott Laboratories 114.11 94.06 89.89 41.634 29.806 6.45 5.46
Utilities 3M 164.78 147 78.56 62.356 39.034 7.90 6.25
Industrials A.O Smith 53.28 40.39 42.22 18.734 13.589 4.33 3.69
Sector: NAME Recent price Price 5 year Price 10 year Maximum Value Minimum Value Range
Consumer Dicretionary Apple 115.31 70.74 80 115.31 70.74 44.57
Information Technology Adobe 494.59 344.1 385.26 494.59 344.1 150.49
Consumer Staples General mills 61.41 54.95 54.7 61.41 54.7 6.71
Materials Applied materials 64.56 51.74 52.04 64.56 51.74 12.82
Energy American electric power 91.61 81.39 78.43 91.61 78.43 13.18
Real Estate Alexandria real estate equities 158.92 149.49 146.99 158.92 149.49 9.43
Financials Ameriprise Financial 172.37 107.48 121.98 172.37 107.48 64.89
Commercials Services Accenture 230.57 175.46 193.71 230.57 175.46 55.11
Healthcare Abbott Laboratories 114.11 94.06 89.89 114.11 89.89 24.22
Utilities 3M 164.78 147 78.56 164.78 78.56 86.22
Industrials A.O Smith 53.28 40.39 42.22 53.28 40.39 12.89
Sector: NAME Recent price Price 5 year Price 10 year Standard Deviation Coefficient of Variation
5 Years 10 Years 5 Years 10 Years
Consumer Dicretionary Apple 115.31 70.74 80 8.41 8.94 9% 9%
Information Technology Adobe 494.59 344.1 385.26 18.55 19.63 4% 4%
Consumer Staples General mills 61.41 54.95 54.7 7.41 7.40 13% 13%
Materials Applied materials 64.56 51.74 52.04 7.19 7.21 12% 12%
Energy American electric power 91.61 81.39 78.43 9.02 8.86 10% 10%
Real Estate Alexandria real estate equities 158.92 149.49 146.99 12.23 12.12 8% 8%
Financials Ameriprise Financial 172.37 107.48 121.98 10.37 11.04 7% 8%
Commercials Services Accenture 230.57 175.46 193.71 13.25 13.92 7% 7%
Healthcare Abbott Laboratories 114.11 94.06 89.89 9.70 9.48 9% 9%
Utilities 3M 164.78 147 78.56 12.12 8.86 8% 7%
Industrials A.O Smith 53.28 40.39 42.22 6.36 6.50 14% 14%
C) Correlation matrice
Sector: NAME Recent price Price 5 year Price 10 year Correlation Matrices
5 Years 10 Years
Consumer Dicretionary Apple 115.31 70.74 80 0.9835298733 0.9816744048
Information Technology Adobe 494.59 344.1 385.26 0.9863421979 0.9817452885
Consumer Staples General mills 61.41 54.95 54.7 0.9519710971 0.9104779822
Materials Applied materials 64.56 51.74 52.04 0.9450912401 0.9019260928
Energy American electric power 91.61 81.39 78.43 0.9313671278 0.8840075567
Real Estate Alexandria real estate equities 158.92 149.49 146.99 0.925099551 0.8795087951
Financials Ameriprise Financial 172.37 107.48 121.98 0.9490497407 0.903087365
Commercials Services Accenture 230.57 175.46 193.71 0.9855464502 0.9007444387
Healthcare Abbott Laboratories 114.11 94.06 89.89 0.9988189122 0.7645093521
Utilities 3M 164.78 147 78.56 1 1
Industrials A.O Smith 53.28 40.39 42.22 0 0

&"Helvetica Neue,Regular"&12&K000000&P

Beta

Beta Values
Variance Coefficient of variation Beta
Stock 5 Year 10 Year 5 Year 10 Year 5-Year 10 Year
Apple 37.21 26.605 9% 9% 0.0024 0.0034
Adobe 167.738 122.395 4% 4% 0.0003 0.0004
General mills 23.272 17.106 13% 13% 0.0055 0.0074
Applied materials 23.26 16.834 12% 12% 0.0053 0.0074
American electric power 34.6 25.143 10% 10% 0.0030 0.0041
Alexandria real estate equities 61.682 45.54 8% 8% 0.0013 0.0017
Ameriprise Financial 55.97 40.183 7% 8% 0.0013 0.0019
Accenture 81.206 59.974 7% 7% 0.0008 0.0011
Abbott Laboratories 41.634 29.806 9% 9% 0.0022 0.0031
3M 62.356 39.034 8% 7% 0.0012 0.0019
A.O Smith 18.734 13.589 14% 14% 0.0072 0.0100
Beta = covariance of stock/ variance of stock
Beta
Stock 5-Year 10 Year
Apple 0.0024 0.0034
Adobe 0.0003 0.0004
General mills 0.0055 0.0074
Applied materials 0.0053 0.0074
American electric power 0.0030 0.0041
Alexandria real estate equities 0.0013 0.0017
Ameriprise Financial 0.0013 0.0019
Accenture 0.0008 0.0011
Abbott Laboratories 0.0022 0.0031
3M 0.0012 0.0019
A.O Smith 0.0072 0.0100

&"Helvetica Neue,Regular"&12&K000000&P

Beta Values

10 Year

A.O Smith 3M Abbott Laboratories Accenture Ameriprise Financial Alexandria real estate equities American electric power Applied materials General mills Adobe Apple 0.010014 0.001866 0.003119 0.001094 0.001868 0.001741 0.004143 0.007350 0.007447 0.000365 0.003443 5-Year

A.O Smith 3M Abbott Laboratories Accenture Ameriprise Financial Alexandria real estate equities American electric power Applied materials General mills Adobe Apple 0.007243 0.001247 0.002238 0.000803 0.001324 0.001285 0.003014 0.005318 0.005475 0.000264 0.002430

R-SQUARED

R-Squared Values
Correlation Matrices R-Squared
Stock 5 Years 10 Years 5 Years 10 Years
Apple 0.9835298733 0.9816744048 0.9673310117 0.9636846371
Adobe 0.9863421979 0.9817452885 0.9728709314 0.9638238115
General mills 0.9519710971 0.9104779822 0.9062489697 0.8289701561
Applied materials 0.9450912401 0.9019260928 0.8931974521 0.8134706769
American electric power 0.9313671278 0.8840075567 0.8674447267 0.7814693602
Alexandria real estate equities 0.925099551 0.8795087951 0.8558091793 0.7735357206
Ameriprise Financial 0.9490497407 0.903087365 0.9006954104 0.8155667888
Accenture 0.9855464502 0.9007444387 0.9713018055 0.8113405438
Abbott Laboratories 0.9988189122 0.7645093521 0.9976392194 0.5844745495
3M 1 1 1 1
A.O Smith 0 0 0 0
R-Squared = Correlation squared
R-Squared
Stock 5 Years 10 Years
Apple 0.9673310117 0.9636846371
Adobe 0.9728709314 0.9638238115
General mills 0.9062489697 0.8289701561
Applied materials 0.8931974521 0.8134706769
American electric power 0.8674447267 0.7814693602
Alexandria real estate equities 0.8558091793 0.7735357206
Ameriprise Financial 0.9006954104 0.8155667888
Accenture 0.9713018055 0.8113405438
Abbott Laboratories 0.9976392194 0.5844745495
3M 1 1
A.O Smith 0 0

&"Helvetica Neue,Regular"&12&K000000&P

R-Squared

10 Years

A.O Smith 3M Abbott Laboratories Accenture Ameriprise Financial Alexandria real estate equities American electric power Applied materials General mills Adobe Apple 0.000000 1.000000 0.584475 0.811341 0.815567 0.773536 0.781469 0.813471 0.828970 0.963824 0.963685 5 Years

A.O Smith 3M Abbott Laboratories Accenture Ameriprise Financial Alexandria real estate equities American electric power Applied materials General mills Adobe Apple 0.000000 1.000000 0.997639 0.971302 0.900695 0.855809 0.867445 0.893197 0.906249 0.972871 0.967331

Lowest Risk and Optimal Portfol

Lowest Risk over the Previous 5 Years
NAME Recent price Price 5 year Variance Risk
Apple 115.31 70.74 37.21 6.10
Adobe 494.59 344.1 167.738 12.95
General mills 61.41 54.95 23.272 4.82
Applied materials 64.56 51.74 23.26 4.82
American electric power 91.61 81.39 34.6 5.88
Alexandria real estate equities 158.92 149.49 61.682 7.85
Ameriprise Financial 172.37 107.48 55.97 7.48
Accenture 230.57 175.46 81.206 9.01
Abbott Laboratories 114.11 94.06 41.634 6.45
3M 164.78 147 62.356 7.90
A.O Smith 53.28 40.39 18.734 4.33
7.05
Risk = Square root of variance
Optimal portfolio
Risk free return = 7.05
NAME Mean Returns Portfolio Allocation Covariance Expected Returns
Apple 8.114 0.0909 0.09 0.74
Adobe 29.298 0.0909 0.04 2.66
General mills 0.492 0.0909 0.13 0.04 Optimal Portfolio -0.49
Applied materials 1.764 0.0909 0.12 0.16
American electric power 1.244 0.0909 0.10 0.11
Alexandria real estate equities 1.086 0.0909 0.08 0.10
Ameriprise Financial 12.178 0.0909 0.07 1.11
Accenture 10.222 0.0909 0.07 0.93
Abbott Laboratories 3.21 0.0909 0.09 0.29
3M 2.756 0.0909 0.08 0.25
A.O Smith 1.778 0.0909 0.14 0.16
Total 1.000 1.01 6.56

&"Helvetica Neue,Regular"&12&K000000&P