B F G H K 1 Company P Market Cap o R Country Industry Mornir P/E Ratio (Price to Bool Price to Sal Price to Cash Flow (Profit Margin Debt to Tot EPS 2 Arconic Inc 11.82 B (Large) MEX EPS Growth EPS Growt Revenue 5 3 13.82 1.83 3.71 3 Bank of America Corp 23.85 27.01% 12.36% 5.77 265.45 B (Mega USA 1.94% 0.08% 10.22% 12 35.66 3.79 3.91 20.71 11% 4. Biogen Inc 27.56% 1.41 2.63% -0.10% 7.34% 66.61 B (Large) MEX 9 3 10.67 2.02 3.01 4.43 28% 5 Envision Healthcare Corp 13.11% 10.71 -35.73% 0.21% 5.41 B (Mediun USA 5.68% 33 12.72 1.46 0.28 18.69 2% 6 Aberdeen Asia-Pacific Income Fund Inc 1.30 B (Mediun MEX 37.34% 5.68 41.86% 0.08% -4.57% 1 3 14.55 1.85 3.68 2.86 7 Gabelli Healthcare & WellnessRx Trust 200.07 M (Micro USA 26% 11.39% 4.68 -3.23% 0.25% 8.67% 3 2 38.47 31.96 6.40 22.02 21% 8 Hawaiian Telcom Holdco Inc 29.59% 3.78 7.69% -0.09% 9.12% 342.54 M (Smal MEX 43 17.74 2.98 1.84 14.50 10% 21.95% 3.57 9 Kirkland Lake Gold Ltd 0.00% 0.19% -0.30% 2.73 B (Mediun USA 72 17.63 13.85 6.08 34.61 35% 59.49% 2.62 37.84% 0.02% 10 Knight-Swift Transportation Holdings In 7.34 B (Mediun MEX -2.81% 6 3 14.11 2.21 3.72 5.32 27% 12.36% 6.77 - 12.23% -0.05% 11 Liberty Interactive Corp 15.54% E 4.87 B (Mediun USA 13 14.94 1.85 2.53 8.09 17% 26.42% 3.75 -56.48% 1.40% 12 Liberty Interactive Corp 6.04% 4 4.87 B (Mediun MEX 12 34.46 2.41 2.70 8.41 22% 45.38% 2.24 -40.00% -0.58% 13 Nuveen New York Dividend Advantage 441.37 M (Smal USA 14.87% 8 13 16.46 5.69 2.27 16.54 14% 39.69% 5.6 -30.58% 0.08% 4.41% 0. 14 Nuveen Texas Quality Income Municipa 143.49 M (Micro USA 1 3 33.8 3.42 4.71 18.18 14% 34.09% 5.59 -31.49% 0.64% 15 Nuveen New Jersey Dividend Advantag 591.08 M (Smal MEX 13.86% 9. 1 2 30.73 10 0.62 14.08 2% 5.49 -30.34% 0.00% 3.23% 2. 16 Procter & Gamble Co 236.23 B (Mega USA 22 14.96 2.48 3.24 11.73 22% 27.80% 2.09 -17.86% -0.04% -2.60% 0. 17 Liberty Interactive Corp 10.44 B (Large) USA 11 25.3 5.93 24.58 10% 36.30% 4.81 2.33% -0.58% 6.39% 9.5 18 Liberty Interactive Corp 10.44 B (Large) USA 13 22.54 5.3 9.12 6% 76.46% -41.94% -0.18% -2.91% -7.0 19 RMR Real Estate Income Fund 150.12 M (Micro USA 13 12 1.6 0.89 19.18 7% 7.19% 2.48 -27.63% 0.31% 20.19% 12. 20 Vermilion Energy Inc 4.51 B (Mediun USA 8.4 19.64 3.99 3.21 14.72 16% 25.50% 5.54 41.82% -0.10% -3.02% 6.04 21 325.42% 2.43 1.29 4.81 22 Welcome Neste dif SORT COST DATATABLE CostVolume Profit PIVOT Table + OLUSIVID C. 2. Go to the SORT tab (10 points): Sort the entire worksheet by nation with USA first and then by industry with industry "1" first and then by CompanyName in reverse alphabetical order. b. For the Price to Book column (column h), use conditional formatting and have any number greater than 10 in green and any number 10 or less highlighted in red. Hide columns and R and S d. Unhide Column M e. Widen column K so that all numbers are available In cell A22 count the number of companies with P/E (column G) ratio less than 15 using a countif function In cell A23, sum column G if the company's name begins with "K"-use sumif function with a wild card h. In cell A24, use the right function to list the last four letters of Vermillion Energy Inc In cell A25, use the mid function to list the word Energy from Vermillion Energy Inc j. In cell A26, add cells B3 with D3 putting a space between the company name and nation k. Left justify ALL your answers in cells A22 through A26 60 2 B F G H K 1 Company P Market Cap o R Country Industry Mornir P/E Ratio (Price to Bool Price to Sal Price to Cash Flow (Profit Margin Debt to Tot EPS 2 Arconic Inc 11.82 B (Large) MEX EPS Growth EPS Growt Revenue 5 3 13.82 1.83 3.71 3 Bank of America Corp 23.85 27.01% 12.36% 5.77 265.45 B (Mega USA 1.94% 0.08% 10.22% 12 35.66 3.79 3.91 20.71 11% 4. Biogen Inc 27.56% 1.41 2.63% -0.10% 7.34% 66.61 B (Large) MEX 9 3 10.67 2.02 3.01 4.43 28% 5 Envision Healthcare Corp 13.11% 10.71 -35.73% 0.21% 5.41 B (Mediun USA 5.68% 33 12.72 1.46 0.28 18.69 2% 6 Aberdeen Asia-Pacific Income Fund Inc 1.30 B (Mediun MEX 37.34% 5.68 41.86% 0.08% -4.57% 1 3 14.55 1.85 3.68 2.86 7 Gabelli Healthcare & WellnessRx Trust 200.07 M (Micro USA 26% 11.39% 4.68 -3.23% 0.25% 8.67% 3 2 38.47 31.96 6.40 22.02 21% 8 Hawaiian Telcom Holdco Inc 29.59% 3.78 7.69% -0.09% 9.12% 342.54 M (Smal MEX 43 17.74 2.98 1.84 14.50 10% 21.95% 3.57 9 Kirkland Lake Gold Ltd 0.00% 0.19% -0.30% 2.73 B (Mediun USA 72 17.63 13.85 6.08 34.61 35% 59.49% 2.62 37.84% 0.02% 10 Knight-Swift Transportation Holdings In 7.34 B (Mediun MEX -2.81% 6 3 14.11 2.21 3.72 5.32 27% 12.36% 6.77 - 12.23% -0.05% 11 Liberty Interactive Corp 15.54% E 4.87 B (Mediun USA 13 14.94 1.85 2.53 8.09 17% 26.42% 3.75 -56.48% 1.40% 12 Liberty Interactive Corp 6.04% 4 4.87 B (Mediun MEX 12 34.46 2.41 2.70 8.41 22% 45.38% 2.24 -40.00% -0.58% 13 Nuveen New York Dividend Advantage 441.37 M (Smal USA 14.87% 8 13 16.46 5.69 2.27 16.54 14% 39.69% 5.6 -30.58% 0.08% 4.41% 0. 14 Nuveen Texas Quality Income Municipa 143.49 M (Micro USA 1 3 33.8 3.42 4.71 18.18 14% 34.09% 5.59 -31.49% 0.64% 15 Nuveen New Jersey Dividend Advantag 591.08 M (Smal MEX 13.86% 9. 1 2 30.73 10 0.62 14.08 2% 5.49 -30.34% 0.00% 3.23% 2. 16 Procter & Gamble Co 236.23 B (Mega USA 22 14.96 2.48 3.24 11.73 22% 27.80% 2.09 -17.86% -0.04% -2.60% 0. 17 Liberty Interactive Corp 10.44 B (Large) USA 11 25.3 5.93 24.58 10% 36.30% 4.81 2.33% -0.58% 6.39% 9.5 18 Liberty Interactive Corp 10.44 B (Large) USA 13 22.54 5.3 9.12 6% 76.46% -41.94% -0.18% -2.91% -7.0 19 RMR Real Estate Income Fund 150.12 M (Micro USA 13 12 1.6 0.89 19.18 7% 7.19% 2.48 -27.63% 0.31% 20.19% 12. 20 Vermilion Energy Inc 4.51 B (Mediun USA 8.4 19.64 3.99 3.21 14.72 16% 25.50% 5.54 41.82% -0.10% -3.02% 6.04 21 325.42% 2.43 1.29 4.81 22 Welcome Neste dif SORT COST DATATABLE CostVolume Profit PIVOT Table + OLUSIVID C. 2. Go to the SORT tab (10 points): Sort the entire worksheet by nation with USA first and then by industry with industry "1" first and then by CompanyName in reverse alphabetical order. b. For the Price to Book column (column h), use conditional formatting and have any number greater than 10 in green and any number 10 or less highlighted in red. Hide columns and R and S d. Unhide Column M e. Widen column K so that all numbers are available In cell A22 count the number of companies with P/E (column G) ratio less than 15 using a countif function In cell A23, sum column G if the company's name begins with "K"-use sumif function with a wild card h. In cell A24, use the right function to list the last four letters of Vermillion Energy Inc In cell A25, use the mid function to list the word Energy from Vermillion Energy Inc j. In cell A26, add cells B3 with D3 putting a space between the company name and nation k. Left justify ALL your answers in cells A22 through A26 60 2