9 Search utoSave @ Off Raez_Smart_Zutter_Problem_4.25_Start - Excel Home Insert Page Layout Formulas Data Review View Help ACROBAT Power Pivot & Cut 29 Wrap Text Custom Style 3 Custom Style 4 Times New Roman ~ 14 ~ A A General Insert Delete Form LG Copy BIU- - A- BEE E Merge & Center % 2 00 Conditional Format as DOSZCLI7NH... FzQU1/K7H2... Formatting ~ Table Format Painter Styles Cells Number Clipboard Font Alignment X fx Use the data that Laura has found on the three securities and calculate the holding period return for each year and the average return over a five-year period. Specifically, 21 N A B Spreadsheet Exercise: Problem 4.25 Solution Use the data that Laura has found on the three securities and calculate the holding From her Investment Analysis class, Laura has been given an assignment to evaluate several securities on a risk-return period return for each year and the average return over a five-year period. Specifically tradeoff basis. The specific securities to be researched are International Business Machines, Helmerich & Payne, Inc, and the the HPR will be based upon five unique one-year periods (Le., 2012 to 2013, 2013 to S&P 500 Index. The respective ticker symbols for the stocks are IBM and HP. She finds the following data on the securities in 2014, 2014 to 2015, 2015 to 2016, and 2016 to 2017). Use the following formula: question. HPR = [Inc + (Vn - Vo)YVo Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell where reference is all you need To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed Inc = income during period, cell reference may be preferred, If a specific Excel function is to be used, the directions will specify the use of that function. Vn = ending investment value, Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make Vol= beginning investment value Year 2012 2013 2014 2015 2016 2017 2011 2012 2013 2014 2015 2016 AVG HPR/ IRR PriceIBM $191 55 $187 57 $160.44 $137.62 $165.99 $153.42 HPRIBM DividendIBM $3.30 $3.70 $4.25 $5 00 $5.50 $5.90 HPRHE PriceHP $56.01 $84.08 $67.42 $53 55 $77.40 $64.64 HPRS&P DividendHe $0 28 $1 30 $2.63 $2.78 $2.80 Cash flowsIBM Values&P 1426.19 1848 36 2058 90 2043.94 2238 83 2673 61 Cash flowsHP Note: The value of the S&P 500 Index includes dividends. Price values are the beginning of the year values. Dividends are the end of year values. To Do Calculate the standard deviations of the returns for IBM, HP, and the S&P 500 Index Create a spreadsheet and analyze the following market transactions: a. Use the data that Laura has found on the three securities and calculate the holding period return for each year and the average return over a five-year period. (Rravg) St Dev b. Calculate the standard deviations of the returns for IBM, HP, and the S&P 500 Index Year 2012 2013 2014 2015 2016 C. What industries are associated with IBM and HP? IBM d. Based on your answer in part c and your results for the average return and the standard deviation, what conclusions can HP Laura make about investing in either IBM or HP? S&I Solution C. What industries are associated with IBM and HP? average return over a five-year period. Specifically, the HPR will be based upon five unique one-year periods (Le, 2012 to 2013, 2013 to 2014, 2014 to 2015, 2015 to 2016, and 2016 to 2017). Use the following formula: HPR = [Inc + (Vn - Vo)VV. is an information technology company that creates both hardwa 2011 2012 2013 2014 2015 2016 AVG HPR/ IRR is an oilfield services corporation manufacturing and servicing e HPRIBM HPRHP Based on your answer in part c and your results for the average return and the standard HPRS&P Cash flowsIBM the standard deviation, IBM is risky than HP. If looking at the returns over the 1 Cash flowsHe would have been the better inve 4.25 +