Investment Portfolios You have started working as an investment intern for a financlal company that helps clients invest in the stock market. Your manager gives you a spreaduheet detailing some periodic investments in Nobel Enerpy. Inc. (NPL) made by one of the clients. The client would like a report and charts on the NEL investment and the stocks performance. Your manager has asked you to finish this report. a. Open workbook e02pf2investment. Save the workbook as e02phinvestment Lastfirst. Apply a theme. and then use cell styles where appropriate for a professional looking spreadsheet. b. On the NBUinvestment worksheet, add the following calculations. - In column F, a formula to calculate total value was already entered by your manager. Update the formula to refurn a value of only two decimak. Hint. The cell value needs to change, which is different than merely formatting to fwo decimats. - In column G, starting in the second row-cell G22 - enter a formula to sum up the quartecly investments as of the date in column A to provide a cumulative total. For example, ceil G22 should return the sum of the Quartedy imveitments on 1/2/2013 and 4/1/2013. Next, cell 623 should return the sum of the Ouarterly investments on 1/2/2013,4/1/2013, and 7/1/2013. Thus column 6 returns a running total of investments so that the last cell-G33-reflects the total of all investments from 1/2/2013 thru 1/3/2016. - In column H, calculate the total growth. If total value is more than 0 , then total growth is total value - total investment. Otherwive, the total growth is either 0 or a negative value. A negative value means that the investment shruni intead of grew. - In column 1 calculate the total growth He if total valoe is more than Q, then total growth Nis is total growth/ fotal investment otherwise, the tocal growth os is 0 . - In column d, calculate the quarterly growth: if total value is more than g then quartenly growth is total growth-total growth from the previous quarter ocherwise quarterly growth is 0. - In column K, calculate quarterly growth \%c if total value is more than 0 , then quarterly growth % is quartenly growth / total investment; otherwise, quarterly growth % is 0 . c. Create a combo chart for Total Portfolio Performance that displays the Total Value Total Imvestment and Total Growth \% over time. Hint: You will need to show a secondary axis for Total Growth 48. a. Create a combo chart for Quarterly Performance that displins the Quarterly Growth and the Quarterly Growth \% over time. Hint You will need to show a secondary axis for Quarterly Total Growth h: apstone Microsctt Office Excel 2013 6. Complete the Documentation worksheet according to your instructor's direction. Insert the filename in the left custom footer section of the Header/Footer tab in the Page Setup dialog box on all worksheets in the workbook. f. Save your work, and then close Excel. Submit the file as directed by your instructor
