Reference no: EM133221476

Investment Portfolios

You have started working as an investment intern for a financial company that helps clients invest in the stock market. Your manager gives you a spreadsheet detailing some periodic investments in Nobel Energy, Inc. (NBL) made by one of the clients. The client would like a report and charts on the NBL investment and the stock's performance. Your manager has asked you to finish the report.

Note: Workbook means Excel.

a. Open workbook e02pf2Investment. Save the workbook as e02pf2Investment_LastFirst. Apply a theme, and then use cell styles where appropriate for a professional looking spreadsheet.

b. On the NBL Investment worksheet, add the following calculations.

• In column F, a formula to calculate total value was already entered by your manager. Update the formula to return a value of only two decimals. Hint: The cell value needs to change, which is different than merely formatting to two decimals.
• In column G, starting in the second row-cell G22 -enter a formula to sum up the quarterly investments as of the date in column A to provide a cumulative total. For example, cell G22 should return the sum of the Quarterly investments on 1/2/2013 and 4/1/2013. Next, cell G23 should return the sum of the Quarterly investments on 1/2/2013, 4/1/2013, and 7/1/2013. Thus, column G 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. Otherwise, the total growth is either 0 or a negative value. A negative value means that the investment shrunk instead of grew.
• In column I, calculate the total growth %: if total value is more than 0, then total growth % is total growth / total investment; otherwise, the total growth % is 0.
• In column J, calculate the quarterly growth: if total value is more than 0, then quarterly growth is total growth-total growth from the previous quarter; otherwise, quarterly growth is 0.
• In column K, calculate quarterly growth %: if total value is more than 0, then quarterly growth % is quarterly growth / total investment; otherwise, quarterly growth % is 0.

c. Create combo chart for Total Portfolio Performance that displays the Total Value, Total Investment and Total Growth % over time. Hint: You will need to show a secondary axis for Total Growth %.

d. Create combo chart for Quarterly Performance that displays the Quarterly Growth and the Quarterly Growth % over time. Hint: You will need to show a secondary axis for Quarterly Total Growth %.

e. 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.