7) Use Excel function to calculate the following

a) Average of Dividend for all stocks

b) Variance of Market Return for all stocks

c) Standard deviation of beta for all stocks

d) Total of Dividend Yield for all stocks

8) Use Conditional Formatting to highlight Dividend column with below criteria.

a) Dividend greater than 123.5% of the average of Dividend for all stocks, show as Light Red Fill with Dark Red Text

b) Dividend between 76.5% and 123.5% of the average of Dividend for all stock, show as Yellow Fill with Dark Yellow Text.

c) Dividend less than 76.5% of the average of Dividend for all stock, show as Green Fill with Dark Green Text

9) Use Conditional Formatting to highlight Market Return column as below: a) Show the top 15% as Green Fill with Dark Green Text (0.5 mark) b) Show the bottom 15 as Light Red Fill with Dark Red Text

10) Use Excel function to calculate the total Dividend for stocks in Portfolio A and B.

11) Use Excel function to determine how many stocks are NOT in Financials Sector and NOT listed in NASDAQ Stock Exchange.

12) Use Excel function to calculate the average Market Price for stocks listed in Hong Kong stock exchange, have beta between 0.0213 and 0.1108, EPS less than 2.75 in Portfolio A.

13) Create Excel formula to calculate RTI for each stock. (3 marks) RTI = Market Price + Quarter Dividend Market Price 1 Change the column category to percentage, and set decimal to 4.

14) Create Excel formula to calculate the PEG for each stock. (3 marks) PEG = EPS + beta (DE DA) 0.5 Market Price Change the column category to percentage, and set decimal to 4.

15) In Sell/Hold Column, use Logical function to determine which stock should sell or hold. For stock in Financials or Energy Sector, have negative Correlation, and Dividend Yield between 0.40% and 2.79%, stock will set for Sell. Otherwise, stock will set for Hold.

16) In Expected Return column, calculate Expected Return for each stock.

Expected Return = beta (Market Return + Yield Rate) 2 (1 Interest Rate + Dividend Yield) 1.5 .

17) Use Solver function to determine the value of Yield Rate for the Median Expected Return equal to 1%.

Expected Return RTI PEG Sell/Hold Answer $27.04 18.49% Question 7 Average of Dividend Variance of Market Return Standard deviation of beta Total of Dividend Yield Answer Question 10 11 12 Question 16-17 Interest Rate Yield Rate 3.75% 3.88% Answer Question 17 Median Expected Return 21.38% Stock Name ABSA GROUP LTD AGRICULTURAL BANK AIRPORTS OF AMBEV SA AMERICA MOVILL ANHUI CONCH ASE INDUSTRIAL ASTRA AXIS BANK LTD B3 BRASIL BOLSA BAIDU ADR REPTG BANCO BRADESCO BANCO BRADESCO BANCO DO BRASIL SA BANK CENTRAL ASIA BANK MANDIRI BANK OF CHINA LTD BANK RAKYAT BID CORPORATION CATHAY FINANCIAL CELLTRION INC CEMEX CPO CHINA CHINA EVERGRANDE CHINA LIFE CHINA MENGNIU CHINA MERCHANTS CHINA MOBILE LTD CHINA OVERSEAS CHINA PACIFIC CHINA PETROLEUM CHINA RESOURCES CHINA SHENHUA CHINA STEEL CORP CHINA TELECOM CHINA TOWER CORP CHINA UNICOM CHUNGHWA CIA VALE DO RIO CIMB GROUP CITIC LTD CNOOC LTD Market Market Dividend Price Portfolo Sector Stock Exchange Quarter Return Correlation beta Dividend Yield EPS DA DE $439.48 A Financials Johannesburg 13.29% -0.0495 0.0035 $2.21 1.49% 6.44 18.35| 41.59| $142.00 B Financials Hong Kong 6 21.38% 0.0214 0.0584 $6.00 1.12% 4.43 17.46 26.56 A Industrials Thailand 1 0.0461 0.0926 $5.52 1.87% 40.32 7.53 13.08 $60.49 C Consumer Staples XBSP 2 21.38% 0.0135 0.0929 $2.68 0.75% -0.65 0.00 0.00 $9.26 A Communication Bolsa Mexicana 2 7.51% -0.0011 0.1007 $2.84) 2.99% 2.7323.421 88.61 $71.23 Materials Hong Kong 3 22.53% 0.0411 0.1425 $5.05 2.61% 3.33 16.89 32.20 $23.95 B Information Technology Taiwan 7 27.73% -0.0529 0.0764 $3.79 3.36% 5.58 23.84 46.96 $0.54 B Consumer Discretionary Indonesia 4 17.91% -0.0146 0.0328 $0.00 0.75% 1.61 1.78 31.96 $121.72 Financials India 3 7.51% -0.0450 0.1280 $3.94 1.49% 9.04 17.00 125.09 $109.75 E Financials |XBSP 6 16.18% -0.0011 0.1508 $6.00 1.49% 0.57 14.73 22.84 $167.17 Communication NASDAQ 3 5.20% 0.0225 0.1362 $4.58 1.49% 2.03 21.20 55.25 $147.40 B Financials XBSP 4 14.44% -0.0096 0.1267 $1.10 1.49% 0.99 24.85 55.38 $10.74 Financials XBSP 2 8.09% 0.0534 0.0729 $0.63 2.99% 2.03 21.20 55.25 $14.21 B Financials IXBSP 6 18.49% 0.0028 0.1491 $0.47 3.36% 40.32 7.53 13.08 $24.07 Financials Indonesia 1 27.15% 0.0259 0.1422 $4.42 1.12% -1.46 2.51 3.45 $6.30 B Financials Indonesia 3 6.36% 0.0563 0.0770 $0.16 2.99% 0.00 2.22 2.55 $146.00 A Financials Hong Kong 4 0.0546 0.0222 $1.26 2.24% 2.26 47.81 139.11 $100.28 B Financials Indonesia 5 16.75% -0.0242 0.0208 $4.58 0.75% 5.54 19.13 45.69 $121.11 Consumer Staples Johannesburg 3 24.84% -0.0518 0.0609 $5.84 0.37% 0.34 24.53 296.91 $115.60 Financials Taiwan 3 6.36% -0.02361 0.0311 $4.73 2.99% 0.73 16.89 368.82 $183.97 Health Care Korea 4 24.26% 0.0416 0.0267 $4.10 3.36% 5.15 11.99 203.14 $685.15 Materials Bolsa Mexicana 4 10.98% -0.0456 0.0024 $5.36 7.34 15.76 37.27 $188.00 B Financials Hong Kong 4 11.55% 0.0231 0.0429 $2.68 2.61% 2.78 2.63 30.26 $316.00 B Real Estate Hong Kong 1 6.93% 0.0242 0.0238 $2.21 0.37% 3.47 0.00 0.75 $33.33 B Financials Hong Kong 7 14.44% -0.01291 0.0912 $4.10 3.36% -4.0849.70 369.18 $40.24 Consumer Staples Hong Kong 4 27.73% 0.0158 0.0166 $0.32 6.62 0.00 0.00 $564.17 C Financials Hong Kong 5 24.26% 0.0068 0.0235 $3.47 1.12% 2.13 1.98 29.35 $108.30 Communication Hong Kong 4 12.13% -0.0056 0.0636 $4.26 3.36% 3.0925.99 60.53 $233.80 B Real Estate Hong Kong 4 28.89% 0.0512 0.0418 $5.52 1.49% 5.54 13.58 246.96 $33.84 B Financials Hong Kong 2 28.89% -0.0315 0.0650 $2.21 0.75% 0.99 24.85 55.38 $846.25 B Energy Hong Kong 6 0.58% -0.0321 0.0693 $4.10 2.99% 1.13 11.15 18.01 $48.52 B Real Estate Hong Kong 3 24.84% -0.0180 0.0801 $5.36 2.61% 3.33 16.89 32.20 $74.25 B Energy Hong Kong 7 12.13% 0.0191 0.0499 $2.37 1.49% 3.74 32.82 63.13 $82.00 Materials Taiwan 3 16.75% -0.0467 0.0442 $5.21 1.27 19.77 35.55 $6.91 Communication Hong Kong 5 6.36% -0.0079 0.1097 $3.79 3.73% 0.91 0.001 0.00 $0.25 A Communication Hong Kong 3 23.69% 0.0079 0.0249 $0.00 3.73% 6.25 10.38 183.46 $121.00 B Communication Hong Kong 1 16.18% -0.0231 0.0151 $2.05 0.75% -0.3053.71 195.84 $42.84 D Communication Taiwan 7 5.20% 0.0163 0.0983 $5.52 1.51 17.42 30.58 $12.58 B Materials XBSP 4 1.73% -0.0191 0.0959 $0.16 1.49% 0.54 0.26 0.32 $114.30 C Financials Bursa Malaysia 7 -0.0079 0.0664 $1.89 0.75% 9.04 17.00 125.09 $1.53 B Industrials Hong Kong 1 26.00% 0.0203 0.1052 $0.32 3.36% 1.88 14.56 23.08 $179.00 A Energy Hong Kong 5 6.93% -0.0197 0.0224 $0.79| 1.49% 7.37 6.55 13.05 2.24% 2.99% 1.49% 1.49% 19.64% Expected Return RTI PEG Sell/Hold Answer $27.04 18.49% Question 7 Average of Dividend Variance of Market Return Standard deviation of beta Total of Dividend Yield Answer Question 10 11 12 Question 16-17 Interest Rate Yield Rate 3.75% 3.88% Answer Question 17 Median Expected Return 21.38% Stock Name ABSA GROUP LTD AGRICULTURAL BANK AIRPORTS OF AMBEV SA AMERICA MOVILL ANHUI CONCH ASE INDUSTRIAL ASTRA AXIS BANK LTD B3 BRASIL BOLSA BAIDU ADR REPTG BANCO BRADESCO BANCO BRADESCO BANCO DO BRASIL SA BANK CENTRAL ASIA BANK MANDIRI BANK OF CHINA LTD BANK RAKYAT BID CORPORATION CATHAY FINANCIAL CELLTRION INC CEMEX CPO CHINA CHINA EVERGRANDE CHINA LIFE CHINA MENGNIU CHINA MERCHANTS CHINA MOBILE LTD CHINA OVERSEAS CHINA PACIFIC CHINA PETROLEUM CHINA RESOURCES CHINA SHENHUA CHINA STEEL CORP CHINA TELECOM CHINA TOWER CORP CHINA UNICOM CHUNGHWA CIA VALE DO RIO CIMB GROUP CITIC LTD CNOOC LTD Market Market Dividend Price Portfolo Sector Stock Exchange Quarter Return Correlation beta Dividend Yield EPS DA DE $439.48 A Financials Johannesburg 13.29% -0.0495 0.0035 $2.21 1.49% 6.44 18.35| 41.59| $142.00 B Financials Hong Kong 6 21.38% 0.0214 0.0584 $6.00 1.12% 4.43 17.46 26.56 A Industrials Thailand 1 0.0461 0.0926 $5.52 1.87% 40.32 7.53 13.08 $60.49 C Consumer Staples XBSP 2 21.38% 0.0135 0.0929 $2.68 0.75% -0.65 0.00 0.00 $9.26 A Communication Bolsa Mexicana 2 7.51% -0.0011 0.1007 $2.84) 2.99% 2.7323.421 88.61 $71.23 Materials Hong Kong 3 22.53% 0.0411 0.1425 $5.05 2.61% 3.33 16.89 32.20 $23.95 B Information Technology Taiwan 7 27.73% -0.0529 0.0764 $3.79 3.36% 5.58 23.84 46.96 $0.54 B Consumer Discretionary Indonesia 4 17.91% -0.0146 0.0328 $0.00 0.75% 1.61 1.78 31.96 $121.72 Financials India 3 7.51% -0.0450 0.1280 $3.94 1.49% 9.04 17.00 125.09 $109.75 E Financials |XBSP 6 16.18% -0.0011 0.1508 $6.00 1.49% 0.57 14.73 22.84 $167.17 Communication NASDAQ 3 5.20% 0.0225 0.1362 $4.58 1.49% 2.03 21.20 55.25 $147.40 B Financials XBSP 4 14.44% -0.0096 0.1267 $1.10 1.49% 0.99 24.85 55.38 $10.74 Financials XBSP 2 8.09% 0.0534 0.0729 $0.63 2.99% 2.03 21.20 55.25 $14.21 B Financials IXBSP 6 18.49% 0.0028 0.1491 $0.47 3.36% 40.32 7.53 13.08 $24.07 Financials Indonesia 1 27.15% 0.0259 0.1422 $4.42 1.12% -1.46 2.51 3.45 $6.30 B Financials Indonesia 3 6.36% 0.0563 0.0770 $0.16 2.99% 0.00 2.22 2.55 $146.00 A Financials Hong Kong 4 0.0546 0.0222 $1.26 2.24% 2.26 47.81 139.11 $100.28 B Financials Indonesia 5 16.75% -0.0242 0.0208 $4.58 0.75% 5.54 19.13 45.69 $121.11 Consumer Staples Johannesburg 3 24.84% -0.0518 0.0609 $5.84 0.37% 0.34 24.53 296.91 $115.60 Financials Taiwan 3 6.36% -0.02361 0.0311 $4.73 2.99% 0.73 16.89 368.82 $183.97 Health Care Korea 4 24.26% 0.0416 0.0267 $4.10 3.36% 5.15 11.99 203.14 $685.15 Materials Bolsa Mexicana 4 10.98% -0.0456 0.0024 $5.36 7.34 15.76 37.27 $188.00 B Financials Hong Kong 4 11.55% 0.0231 0.0429 $2.68 2.61% 2.78 2.63 30.26 $316.00 B Real Estate Hong Kong 1 6.93% 0.0242 0.0238 $2.21 0.37% 3.47 0.00 0.75 $33.33 B Financials Hong Kong 7 14.44% -0.01291 0.0912 $4.10 3.36% -4.0849.70 369.18 $40.24 Consumer Staples Hong Kong 4 27.73% 0.0158 0.0166 $0.32 6.62 0.00 0.00 $564.17 C Financials Hong Kong 5 24.26% 0.0068 0.0235 $3.47 1.12% 2.13 1.98 29.35 $108.30 Communication Hong Kong 4 12.13% -0.0056 0.0636 $4.26 3.36% 3.0925.99 60.53 $233.80 B Real Estate Hong Kong 4 28.89% 0.0512 0.0418 $5.52 1.49% 5.54 13.58 246.96 $33.84 B Financials Hong Kong 2 28.89% -0.0315 0.0650 $2.21 0.75% 0.99 24.85 55.38 $846.25 B Energy Hong Kong 6 0.58% -0.0321 0.0693 $4.10 2.99% 1.13 11.15 18.01 $48.52 B Real Estate Hong Kong 3 24.84% -0.0180 0.0801 $5.36 2.61% 3.33 16.89 32.20 $74.25 B Energy Hong Kong 7 12.13% 0.0191 0.0499 $2.37 1.49% 3.74 32.82 63.13 $82.00 Materials Taiwan 3 16.75% -0.0467 0.0442 $5.21 1.27 19.77 35.55 $6.91 Communication Hong Kong 5 6.36% -0.0079 0.1097 $3.79 3.73% 0.91 0.001 0.00 $0.25 A Communication Hong Kong 3 23.69% 0.0079 0.0249 $0.00 3.73% 6.25 10.38 183.46 $121.00 B Communication Hong Kong 1 16.18% -0.0231 0.0151 $2.05 0.75% -0.3053.71 195.84 $42.84 D Communication Taiwan 7 5.20% 0.0163 0.0983 $5.52 1.51 17.42 30.58 $12.58 B Materials XBSP 4 1.73% -0.0191 0.0959 $0.16 1.49% 0.54 0.26 0.32 $114.30 C Financials Bursa Malaysia 7 -0.0079 0.0664 $1.89 0.75% 9.04 17.00 125.09 $1.53 B Industrials Hong Kong 1 26.00% 0.0203 0.1052 $0.32 3.36% 1.88 14.56 23.08 $179.00 A Energy Hong Kong 5 6.93% -0.0197 0.0224 $0.79| 1.49% 7.37 6.55 13.05 2.24% 2.99% 1.49% 1.49% 19.64%