beta Stock Stock Symbol Exchange Sector AAPL INASDAQ Technology ADBE NASDAQ Technology AEG NYSE Financial AEO NYSE Retail AMD NASDAQ Technology AMZN NYSE Technology ANF NYSE Retail ARMK NASDAQ Technology ATH TSX Energy ATI NYSE Technology BA NYSE Industrial BAM TSX Financial BBL NYSE Mining BHP NYSE Mining BMO TSX Financial BNS TSX Financial BP NYSE Energy NYSE Financial CAE TSX Industrial CAT TSX Industrial CL TSX Industrial CM TSX Financial CNMD NASDAQ Health-care CNO NYSE Financial CNR TSX Industrial COP NYSE Energy CPG TSX Energy CSCO NASDAQ Technology CVE NYSE Energy CVX NYSE Energy DSW NYSE Retail ECA TSX Energy ELVT NASDAQ Financial ENB TSX Energy Purchase Price $105.05 $68.22 $9.02 $24.85 $3.69 $1,000.16 $25.12 $48.15 $1.61 $7.91 $128.28 $44.37 $45.00 $35.16 $72.67 $63.32 $34.95 $70.10 $14.90 $132.42 $55.39 $97.85 $47.77 $17.39 $61.98 $65.45 $9.25 $22.13 $5.01 $110.58 $22.35 $17.25 $7.26 $32.52 Market Price $216.47 $249.01 $6.02 $20.94 $26.61 $1,776.22 $17.57 $39.31 $48.25 $27.01 $360.02 $41.75 $40.56 $46.531 $79.31 $54.48 $43.84 $68.71 $18.70 $135.75 $62.60 $115.72 $72.89 $19.77 $109.01 $72.59 $6.85 $45.50 $8.50 $117.47 $26.38) $14.54) $6.52 $42.35 1.36 1.08 1.22 0.83 3.11 1.58 0.45 0.69 0.34 2.46 1.40 1.02 1.12 1.14 1.09 1.27 0.90 1.46 0.66 1.38 0.72 1.35 0.51 1.12 0.93 1.23 2.61 1.07 Share 6,537,457 8,418,000 1,961,021 8,400,132 1,724,530 2,541,377 388,117 3,504,286 64,307,155 2,218,944 2,700,123 786,309 32,499,221 50,689,600 95,912,773 43,936,969 3,011,821 23,123,888 3,559,000 40,010,000 19,678,110 11,769,366 63,588,789 1,883,098 616,505 16,155,100 2,695,600 1,121,664 4,760,000 7,414,997 91, 122,000 9,462,000 461,863 8,268,200 EPS D/A D/E 0.54 12.23 15.63 7.34 15.76 37.27 6.44 18.35 41.59 11.08 8.05 13.70 0.61 43.45 148.98 1.06 44.96 191.76 0.99 24.86 55.38 11.28 13.56 23.45 5.23 9.92 16.63 1.82 11.16 20.51 -0.30 53.71 195.84 0.64 67.25 78.25 6.62 0.00 0.00 -0.06 31.93 63.52 6.01 0.00 0.00 5.20 12.46 24.75 -1.26 28.91 64.76 5.54 19.13 45.69 2.26 47.81 139.11 7.37 6.55 13.05 0.54 3.49 48.75 2.13 1.98 29.35 3.47 42.70 0.00 6.25 10.38 183.46 5.15 11.99| 203.14 8.24 2.94 63.26 2.09 10.84 76.06 -30.91 8.33 35.85 0.73 16.89 368.82 3.99 17.23 395.53 1.54 0.00 0.00 1.61 1.78 31.96 -0.80 4.03 83.69 2.14 3.02| 102.12 Dividend $2.92 $0.00 $0.14 $0.55 $0.00 $0.00 $0.80) $0.22 $0.00 $0.00 $6.84 $0.00 $2.52 $2.52 $2.95 $2.63 $2.44 $1.80 $0.31 $3.44 $1.68 $5.44 $0.80 $0.401 $1.82 $1.22 $0.361 $1.32 $0.15 $4.48 $1.00 $0.06 $0.00 $2.68 Dividend Yield 1.32% 0.00% 4.53% 2.56% 0.00% 0.00% 4.43% 1.04% 0.00% 0.00% 1.87% 1.40% 6.05% 5.27% 3.69% 4.75% 5.50% 2.58% 1.63% 2.43% 2.64% Return 4.52% 7.43% 4.78% 3.98% 6.69% 12.86% 10.82% 10.82% 14.73% 15.50% 14.78% 2.62% 12.10% 3.04% 3.83% 8.07% 6.51% 14.12% 10.71% 14.00% 14.56% 0.06% 3.63% 11.86% 3.88% 13.38% 11.71% 1.35% 2.34% 2.45% 13.60% 3.02% 11.74% 6.98% 4.67% 1.17% 1.97% 1.64% 1.68% 5.06% 2.87% 1.76% 3.82% 0.60 1.07 0.88 5.88 0.31 3.66% 0.53% 0.00% 6.36% 1.37 14) Use Conditional Formatting to highlight: a) Top 12 on EPS as "Green Fill with Dark Green Text (0.5 mark) b) Bottom 12% on Return as "Light Red Fill with Dark Red Text (0.5 mark) c) Dividend are between $1.1 and $8.5 as Yellow Fill with Dark Yellow Text (1 mark) 15) Use Excel function determine how many stocks in Energy sector, have Return less than or equal to 14.78%, beta greater than 0.58, and EPS between 2.13 and 11.08. (3 marks) 16) Use Excel function calculate the average Purchase Price for stocks have beta between 0.05 and 5.88, Shares less than 3 million and Dividend Yield greater than 0.53%. (3 marks) 17) Use Excel function to calculate total Dividend on stocks are list in NASDAQ Stock Exchange, Dividend Yield between 1.13% and 5.06%, D/A greater than 28.91, D/E equal to zero and Return less than 5.04%. (4 marks) beta Stock Stock Symbol Exchange Sector AAPL INASDAQ Technology ADBE NASDAQ Technology AEG NYSE Financial AEO NYSE Retail AMD NASDAQ Technology AMZN NYSE Technology ANF NYSE Retail ARMK NASDAQ Technology ATH TSX Energy ATI NYSE Technology BA NYSE Industrial BAM TSX Financial BBL NYSE Mining BHP NYSE Mining BMO TSX Financial BNS TSX Financial BP NYSE Energy NYSE Financial CAE TSX Industrial CAT TSX Industrial CL TSX Industrial CM TSX Financial CNMD NASDAQ Health-care CNO NYSE Financial CNR TSX Industrial COP NYSE Energy CPG TSX Energy CSCO NASDAQ Technology CVE NYSE Energy CVX NYSE Energy DSW NYSE Retail ECA TSX Energy ELVT NASDAQ Financial ENB TSX Energy Purchase Price $105.05 $68.22 $9.02 $24.85 $3.69 $1,000.16 $25.12 $48.15 $1.61 $7.91 $128.28 $44.37 $45.00 $35.16 $72.67 $63.32 $34.95 $70.10 $14.90 $132.42 $55.39 $97.85 $47.77 $17.39 $61.98 $65.45 $9.25 $22.13 $5.01 $110.58 $22.35 $17.25 $7.26 $32.52 Market Price $216.47 $249.01 $6.02 $20.94 $26.61 $1,776.22 $17.57 $39.31 $48.25 $27.01 $360.02 $41.75 $40.56 $46.531 $79.31 $54.48 $43.84 $68.71 $18.70 $135.75 $62.60 $115.72 $72.89 $19.77 $109.01 $72.59 $6.85 $45.50 $8.50 $117.47 $26.38) $14.54) $6.52 $42.35 1.36 1.08 1.22 0.83 3.11 1.58 0.45 0.69 0.34 2.46 1.40 1.02 1.12 1.14 1.09 1.27 0.90 1.46 0.66 1.38 0.72 1.35 0.51 1.12 0.93 1.23 2.61 1.07 Share 6,537,457 8,418,000 1,961,021 8,400,132 1,724,530 2,541,377 388,117 3,504,286 64,307,155 2,218,944 2,700,123 786,309 32,499,221 50,689,600 95,912,773 43,936,969 3,011,821 23,123,888 3,559,000 40,010,000 19,678,110 11,769,366 63,588,789 1,883,098 616,505 16,155,100 2,695,600 1,121,664 4,760,000 7,414,997 91, 122,000 9,462,000 461,863 8,268,200 EPS D/A D/E 0.54 12.23 15.63 7.34 15.76 37.27 6.44 18.35 41.59 11.08 8.05 13.70 0.61 43.45 148.98 1.06 44.96 191.76 0.99 24.86 55.38 11.28 13.56 23.45 5.23 9.92 16.63 1.82 11.16 20.51 -0.30 53.71 195.84 0.64 67.25 78.25 6.62 0.00 0.00 -0.06 31.93 63.52 6.01 0.00 0.00 5.20 12.46 24.75 -1.26 28.91 64.76 5.54 19.13 45.69 2.26 47.81 139.11 7.37 6.55 13.05 0.54 3.49 48.75 2.13 1.98 29.35 3.47 42.70 0.00 6.25 10.38 183.46 5.15 11.99| 203.14 8.24 2.94 63.26 2.09 10.84 76.06 -30.91 8.33 35.85 0.73 16.89 368.82 3.99 17.23 395.53 1.54 0.00 0.00 1.61 1.78 31.96 -0.80 4.03 83.69 2.14 3.02| 102.12 Dividend $2.92 $0.00 $0.14 $0.55 $0.00 $0.00 $0.80) $0.22 $0.00 $0.00 $6.84 $0.00 $2.52 $2.52 $2.95 $2.63 $2.44 $1.80 $0.31 $3.44 $1.68 $5.44 $0.80 $0.401 $1.82 $1.22 $0.361 $1.32 $0.15 $4.48 $1.00 $0.06 $0.00 $2.68 Dividend Yield 1.32% 0.00% 4.53% 2.56% 0.00% 0.00% 4.43% 1.04% 0.00% 0.00% 1.87% 1.40% 6.05% 5.27% 3.69% 4.75% 5.50% 2.58% 1.63% 2.43% 2.64% Return 4.52% 7.43% 4.78% 3.98% 6.69% 12.86% 10.82% 10.82% 14.73% 15.50% 14.78% 2.62% 12.10% 3.04% 3.83% 8.07% 6.51% 14.12% 10.71% 14.00% 14.56% 0.06% 3.63% 11.86% 3.88% 13.38% 11.71% 1.35% 2.34% 2.45% 13.60% 3.02% 11.74% 6.98% 4.67% 1.17% 1.97% 1.64% 1.68% 5.06% 2.87% 1.76% 3.82% 0.60 1.07 0.88 5.88 0.31 3.66% 0.53% 0.00% 6.36% 1.37 14) Use Conditional Formatting to highlight: a) Top 12 on EPS as "Green Fill with Dark Green Text (0.5 mark) b) Bottom 12% on Return as "Light Red Fill with Dark Red Text (0.5 mark) c) Dividend are between $1.1 and $8.5 as Yellow Fill with Dark Yellow Text (1 mark) 15) Use Excel function determine how many stocks in Energy sector, have Return less than or equal to 14.78%, beta greater than 0.58, and EPS between 2.13 and 11.08. (3 marks) 16) Use Excel function calculate the average Purchase Price for stocks have beta between 0.05 and 5.88, Shares less than 3 million and Dividend Yield greater than 0.53%. (3 marks) 17) Use Excel function to calculate total Dividend on stocks are list in NASDAQ Stock Exchange, Dividend Yield between 1.13% and 5.06%, D/A greater than 28.91, D/E equal to zero and Return less than 5.04%. (4 marks)

Answer rating: 100% (QA)

The Answer is in the image, click to view ... View the full answer