Any help would be greatly appreciated.

Due 6pm (submit to ReggieNet) Thursday Feb 27, 2020 25 points Note: Set Excel to show 4 decimal places in your answers, whether decimal format (0.0123) or percentage format (1.2345%) is used. Note: neatness and good spreadsheet layout are important. All analysis is to be done using Excel and Excel functions whenever possiblem Hardwire" your formulas---that is, use cell references, not numbers, whenever possible. And use Excel functions: Average, Geomean, etc., whenever possible and Excel math formulas when necessary (e.g., =1+C5 to compute the return relative for the HPR that is in cell C5). Points will be deducted if appropriate Excel functions or formulas are not used. Upload the spreadsheet onto ReggieNet: use the following filename convention: yourlastname. FIL242Assignment2.xls (1 point penalty if not labeled correctly) (0.8., =l unctions: Ale" Your Fount. All anah. 1. You will be doing analysis using four of the following ten firms: "ABCD digit 0 1 1 2 3 4 5 6 7 8 9 Ticker AAA | BBB | CCC | DDD | EEE FFF | GGGHHH | III JJJ Which four? What I am calling the "ABCD number is the last four digits of your University ID (UID): 888-88-ABCD. 2. Get the assignment data in the ReggieNet assignment folder for the monthly "close" prices and dividends from January 31 through December 31 for the firms corresponding to the last four digits of your UIDthis is your 'ABCD'number. If the last 4 digits of your 'ABCD' contains a double digit, then use the next firm on the list. For example: if the last 4 digits of your UID number ends with: 7890: You'll be using HHH, III, JJJ, and AAA in your project 7789: You'll be using HHH (#7), III (#8, taking the place of the second 7'), JJJ (you are already using firm #8 so we go to the next firm, #9) and AAA (again, #9 is used so we go back to the beginningto #0). 7770: You will use HHH, III, JJJ, and AAA. 3382: You will use DDD (#3), EEE #4, since #3 was already used), III (#8), and CCC (for #2) Zum (8 points) Place your four firms in alphabetical order by ticker symbol (in columns) showing the month- end prices and dividends. Compute holding period returns, in percents, for each month for each stock. With 12 months of data you will compute 11 HPRs for each of your four stocks. If a dividend is paid on, say, March 31, it should be included in the March HPR calculation. Set Excel to show 4 decimal places in your answers. Hint: the Excel formula for calculating HPR should be the same for each firmthat is, the formula shouldn't change whether a dividend was paid that month or not. 4. (6 points) a. For each of your stocks, use the shortcut Excel functions to compute the average HPR and standard deviation of the HPR and display your answer in percents. b. (2 points) For each of your stocks, use the shortcut Excel function to compute the variance of the HPRS and show/label it in units of percent squared (see example below). Note you will need to use VAR in a formula and use math to adjust it to have variance properly display in percent squared format. For example, if Excel computes a standard deviation of 10%, it will display a variance of 1%--and that isn't correct! It should be 100, of course. Think how you need to adjust the formula for variance so it is displayed correctly. Time HPR 10.0000% 0.0000% -10.0000% WN Average Std Dev Variance 0.0000% 10.0000% 1.0000% 100.0000 100.0000% NOT THIS! percent squared squared (note: you add the labels) You want this... Or this 5. Compute the return relative for each of your four stocks to four decimal places. a. Use the return relatives to compute the geometric mean return for each of your stocks in percents. (2 points) Use the shortcut Excel formula (GEOMEAN) in your formula for computing the geometric meandon't write a long line of calculations to multiply 11 return relatives together! b. Among your four firms, identify the firm with the smallest difference between its arithmetic and geometric mean return and the firm with the largest difference between its arithmetic and geometric mean return. Explain why the difference between the arithmetic and geometric mean return is larger for one firm than the other. Use the wrap text feature and/or merge cells) so your response doesn't stretch out in one line for many columns!(3 points) [Hint: if you need help answering this, on another "scrap paper" spreadsheet (no need to turn it in), find the arithmetic mean, geometric mean, and their difference when all the HPRs are the samee.g., 5%, 5%, 5%, 5%, 5%....what happens to the difference in means if you change two of these numbers to 6%? To 8%? To 14%?]. c. Suppose $100 was invested in each of your four stocks at the beginning of the project time frame as shown on the data spreadsheet. How much would the investment in each stock be worth on Dec 31? Assume any dividends are re-invested in the stock. Report the ending wealth in each stock to 2 decimal places that is, the nearest penny). HINT: this involves compounding the returns and will use the GEOMEAN function again). (4 points) 5%, 5%... Wpis mean, and paper" spreadsheolumns!(3 p WTO 14%?]. your four stoc Grades will be affected by the ability to follow instructions, the accuracy of analysis, the correctness of your answers, and your spreadsheet format (readability). The work you turn in must only be your own. This is not a group/team/shared project. ABCD' digit 0 AAA BBB CCC DDD EEE FFF GGG HHH Dividend Dividend Dividend Dividend Dividend Dividend Dividend Dividend Dividend 0.32 0.12 0.12 0.62 0.14 0.22 0.1 Firm name/ticker symbol Month-end Date 31-Jan 28-Feb 31-Mar 30-Apr 31-May 30-Jun 31-Jul 31-Aug 30-Sep 31-Oct 30-Nov 31-Dec 0.4 0.35 0.12 0.22 0.12 Close 53.11 52.5 50.3 52.57 53.07 49.01 47.98 50.78 52.5 54.06 53.85 54.21 0.62 Close 74.1 71.68 75.45 79.38 83.17 83.88 85.13 85.73 92.56 91.99 89.16 93.69 Close 39.33 37.15 34.87 34.43 35.33 36.29 36.02 32.92 33.36 37.43 34.41 33.14 0.17 Close Dividend 24.73 26.56 0.09 26.24 27.06 29.14 0.09 29.32 27.93 0.09 28.77 26.3 27.72 26.290.09 27.55 Close 21.25 19.86 19.13 21.5 22.18 23.74 23.62 25.75 25.86 26.9 26.08 26.66 Close 48.32 48.31 46.95 47.92 47.6 48.11 45.95 43.63 43.65 45.21 47.9 47.53 Close 44.01 43.69 45.05 48.28 50.55 50.76 47.87 49.25 54.47 59.75 58.47 58.91 Close 43.1 43.1 41.99 41.7 40.68 39.06 41.75 42.26 44.73 50.9 49.69 48.83 0.1 Close 61.36 61.53 59.26 59.37 62.43 63.6 60.57 65.93 63.57 61.36 59.97 50 Close 38.52 37.4 37.92 38.18 43.53 41.17 42.62 41.88 44.28 46.07 43.21 43.69 0.42 0.35 0.12 0.22 0.14 1 0.7 0.1 0.43 0.35 0.12 2.5 0.14 O 0.7 0.2 0.22 0.1 Note: The dividends occur in the month in which they appear. Thus, a dividend appearing in the "31-Mar" row was paid during the month of March. Due 6pm (submit to ReggieNet) Thursday Feb 27, 2020 25 points Note: Set Excel to show 4 decimal places in your answers, whether decimal format (0.0123) or percentage format (1.2345%) is used. Note: neatness and good spreadsheet layout are important. All analysis is to be done using Excel and Excel functions whenever possiblem Hardwire" your formulas---that is, use cell references, not numbers, whenever possible. And use Excel functions: Average, Geomean, etc., whenever possible and Excel math formulas when necessary (e.g., =1+C5 to compute the return relative for the HPR that is in cell C5). Points will be deducted if appropriate Excel functions or formulas are not used. Upload the spreadsheet onto ReggieNet: use the following filename convention: yourlastname. FIL242Assignment2.xls (1 point penalty if not labeled correctly) (0.8., =l unctions: Ale" Your Fount. All anah. 1. You will be doing analysis using four of the following ten firms: "ABCD digit 0 1 1 2 3 4 5 6 7 8 9 Ticker AAA | BBB | CCC | DDD | EEE FFF | GGGHHH | III JJJ Which four? What I am calling the "ABCD number is the last four digits of your University ID (UID): 888-88-ABCD. 2. Get the assignment data in the ReggieNet assignment folder for the monthly "close" prices and dividends from January 31 through December 31 for the firms corresponding to the last four digits of your UIDthis is your 'ABCD'number. If the last 4 digits of your 'ABCD' contains a double digit, then use the next firm on the list. For example: if the last 4 digits of your UID number ends with: 7890: You'll be using HHH, III, JJJ, and AAA in your project 7789: You'll be using HHH (#7), III (#8, taking the place of the second 7'), JJJ (you are already using firm #8 so we go to the next firm, #9) and AAA (again, #9 is used so we go back to the beginningto #0). 7770: You will use HHH, III, JJJ, and AAA. 3382: You will use DDD (#3), EEE #4, since #3 was already used), III (#8), and CCC (for #2) Zum (8 points) Place your four firms in alphabetical order by ticker symbol (in columns) showing the month- end prices and dividends. Compute holding period returns, in percents, for each month for each stock. With 12 months of data you will compute 11 HPRs for each of your four stocks. If a dividend is paid on, say, March 31, it should be included in the March HPR calculation. Set Excel to show 4 decimal places in your answers. Hint: the Excel formula for calculating HPR should be the same for each firmthat is, the formula shouldn't change whether a dividend was paid that month or not. 4. (6 points) a. For each of your stocks, use the shortcut Excel functions to compute the average HPR and standard deviation of the HPR and display your answer in percents. b. (2 points) For each of your stocks, use the shortcut Excel function to compute the variance of the HPRS and show/label it in units of percent squared (see example below). Note you will need to use VAR in a formula and use math to adjust it to have variance properly display in percent squared format. For example, if Excel computes a standard deviation of 10%, it will display a variance of 1%--and that isn't correct! It should be 100, of course. Think how you need to adjust the formula for variance so it is displayed correctly. Time HPR 10.0000% 0.0000% -10.0000% WN Average Std Dev Variance 0.0000% 10.0000% 1.0000% 100.0000 100.0000% NOT THIS! percent squared squared (note: you add the labels) You want this... Or this 5. Compute the return relative for each of your four stocks to four decimal places. a. Use the return relatives to compute the geometric mean return for each of your stocks in percents. (2 points) Use the shortcut Excel formula (GEOMEAN) in your formula for computing the geometric meandon't write a long line of calculations to multiply 11 return relatives together! b. Among your four firms, identify the firm with the smallest difference between its arithmetic and geometric mean return and the firm with the largest difference between its arithmetic and geometric mean return. Explain why the difference between the arithmetic and geometric mean return is larger for one firm than the other. Use the wrap text feature and/or merge cells) so your response doesn't stretch out in one line for many columns!(3 points) [Hint: if you need help answering this, on another "scrap paper" spreadsheet (no need to turn it in), find the arithmetic mean, geometric mean, and their difference when all the HPRs are the samee.g., 5%, 5%, 5%, 5%, 5%....what happens to the difference in means if you change two of these numbers to 6%? To 8%? To 14%?]. c. Suppose $100 was invested in each of your four stocks at the beginning of the project time frame as shown on the data spreadsheet. How much would the investment in each stock be worth on Dec 31? Assume any dividends are re-invested in the stock. Report the ending wealth in each stock to 2 decimal places that is, the nearest penny). HINT: this involves compounding the returns and will use the GEOMEAN function again). (4 points) 5%, 5%... Wpis mean, and paper" spreadsheolumns!(3 p WTO 14%?]. your four stoc Grades will be affected by the ability to follow instructions, the accuracy of analysis, the correctness of your answers, and your spreadsheet format (readability). The work you turn in must only be your own. This is not a group/team/shared project. ABCD' digit 0 AAA BBB CCC DDD EEE FFF GGG HHH Dividend Dividend Dividend Dividend Dividend Dividend Dividend Dividend Dividend 0.32 0.12 0.12 0.62 0.14 0.22 0.1 Firm name/ticker symbol Month-end Date 31-Jan 28-Feb 31-Mar 30-Apr 31-May 30-Jun 31-Jul 31-Aug 30-Sep 31-Oct 30-Nov 31-Dec 0.4 0.35 0.12 0.22 0.12 Close 53.11 52.5 50.3 52.57 53.07 49.01 47.98 50.78 52.5 54.06 53.85 54.21 0.62 Close 74.1 71.68 75.45 79.38 83.17 83.88 85.13 85.73 92.56 91.99 89.16 93.69 Close 39.33 37.15 34.87 34.43 35.33 36.29 36.02 32.92 33.36 37.43 34.41 33.14 0.17 Close Dividend 24.73 26.56 0.09 26.24 27.06 29.14 0.09 29.32 27.93 0.09 28.77 26.3 27.72 26.290.09 27.55 Close 21.25 19.86 19.13 21.5 22.18 23.74 23.62 25.75 25.86 26.9 26.08 26.66 Close 48.32 48.31 46.95 47.92 47.6 48.11 45.95 43.63 43.65 45.21 47.9 47.53 Close 44.01 43.69 45.05 48.28 50.55 50.76 47.87 49.25 54.47 59.75 58.47 58.91 Close 43.1 43.1 41.99 41.7 40.68 39.06 41.75 42.26 44.73 50.9 49.69 48.83 0.1 Close 61.36 61.53 59.26 59.37 62.43 63.6 60.57 65.93 63.57 61.36 59.97 50 Close 38.52 37.4 37.92 38.18 43.53 41.17 42.62 41.88 44.28 46.07 43.21 43.69 0.42 0.35 0.12 0.22 0.14 1 0.7 0.1 0.43 0.35 0.12 2.5 0.14 O 0.7 0.2 0.22 0.1 Note: The dividends occur in the month in which they appear. Thus, a dividend appearing in the "31-Mar" row was paid during the month of March