Question

1 Approved Answer

Posted on Jul 25, 2024

Stock Symbol AAPL ADBE AEG AEO AMD AMZN ANF ARMK ATH ATI BA BAM BBL BHP BMO BNS BP CAE CAT Stock Exchange Sector NASDAQ

image text in transcribed

Stock Symbol AAPL ADBE AEG AEO AMD AMZN ANF ARMK ATH ATI BA BAM BBL BHP BMO BNS BP CAE CAT Stock Exchange Sector NASDAQ Technology NASDAQ Technology NYSE Financial NYSE Retail NASDAQ Technology NYSE Technology NYSE Retail NASDAQ Technology TSX Energy NYSE Technology NYSE Industrial TSX Financial NYSE Mining NYSE Mining TSX Financial TSX Financial NYSE Energy NYSE Financial TSX Industrial TSX Industrial TSX Industrial TSX Financial NASDAQ Health-care NYSE Financial TSX Industrial NYSE Energy TSX Energy NASDAQ Technology NYSE Energy NYSE Energy NYSE Retail TSX Energy NASDAQ Financial TSX Energy Purchase Market Price Price beta $105.05 $216.47 1.36 $68.22 $249.01 1.08 $9.02 $6.02 1.22 $24.85 $20.94 0.83 $3.69 $26.61 3.11 $1,000.16 $1,776.22 1.58 $25.12 $17.57 0.45 $48.15 $39.31 0.69 $1.61 $48.25 0.34 $7.91 $27.01 2.46 $128.28 $360.02 1.40 $44.37 $41.75 1.02 $45.00 $40.56 1.12 $35.16 $46.53 1.14 $72.67 $79.31 1.09 $63.32 $54.48 1.27 $34.95 $43.84 0.90 $70.10 $68.71 1.46 $14.90 $18.70 0.66 $132.42 $135.75 1.38 $55.39 $62.60 0.72 $97.85 $115.72 1.35 $47.77 $72.89 0.51 $17.39 $19.77 1.12 $61.98 $109.01 0.93 $65.45 $72.591 1.23 $9.25 $6.85 2.61 $22.13 $45.500 1.07 $5.01 $8.501 0.60 $110.58 $117.47 1.07 $22.35 $26.38 0.88 $17.25 $14.54 5.88 $7.26 $6.52 0.31 $32.52 $42.35 1.371 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.061 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 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 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.521 $2.52 $2.95 $2.63 $2.44 $1.80 $0.31 $3.44 $1.68 $5.44 $0.80 $0.40 $1.82 $1.22 $0.36 $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% 4.67% 1.17% 1.97% 1.64% 1.68% 5.06% 2.87% 1.76% 3.82% 3.66% 0.53% 0.00% 6.36% 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% 0.00 CL CM CNMD CNO CNR COP CPG CSCO CVE CVX DSW ECA ELVT ENB 0.00 0.00 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) Stock Symbol AAPL ADBE AEG AEO AMD AMZN ANF ARMK ATH ATI BA BAM BBL BHP BMO BNS BP CAE CAT Stock Exchange Sector NASDAQ Technology NASDAQ Technology NYSE Financial NYSE Retail NASDAQ Technology NYSE Technology NYSE Retail NASDAQ Technology TSX Energy NYSE Technology NYSE Industrial TSX Financial NYSE Mining NYSE Mining TSX Financial TSX Financial NYSE Energy NYSE Financial TSX Industrial TSX Industrial TSX Industrial TSX Financial NASDAQ Health-care NYSE Financial TSX Industrial NYSE Energy TSX Energy NASDAQ Technology NYSE Energy NYSE Energy NYSE Retail TSX Energy NASDAQ Financial TSX Energy Purchase Market Price Price beta $105.05 $216.47 1.36 $68.22 $249.01 1.08 $9.02 $6.02 1.22 $24.85 $20.94 0.83 $3.69 $26.61 3.11 $1,000.16 $1,776.22 1.58 $25.12 $17.57 0.45 $48.15 $39.31 0.69 $1.61 $48.25 0.34 $7.91 $27.01 2.46 $128.28 $360.02 1.40 $44.37 $41.75 1.02 $45.00 $40.56 1.12 $35.16 $46.53 1.14 $72.67 $79.31 1.09 $63.32 $54.48 1.27 $34.95 $43.84 0.90 $70.10 $68.71 1.46 $14.90 $18.70 0.66 $132.42 $135.75 1.38 $55.39 $62.60 0.72 $97.85 $115.72 1.35 $47.77 $72.89 0.51 $17.39 $19.77 1.12 $61.98 $109.01 0.93 $65.45 $72.591 1.23 $9.25 $6.85 2.61 $22.13 $45.500 1.07 $5.01 $8.501 0.60 $110.58 $117.47 1.07 $22.35 $26.38 0.88 $17.25 $14.54 5.88 $7.26 $6.52 0.31 $32.52 $42.35 1.371 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.061 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 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 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.521 $2.52 $2.95 $2.63 $2.44 $1.80 $0.31 $3.44 $1.68 $5.44 $0.80 $0.40 $1.82 $1.22 $0.36 $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% 4.67% 1.17% 1.97% 1.64% 1.68% 5.06% 2.87% 1.76% 3.82% 3.66% 0.53% 0.00% 6.36% 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% 0.00 CL CM CNMD CNO CNR COP CPG CSCO CVE CVX DSW ECA ELVT ENB 0.00 0.00 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)