Question
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
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 rowcell 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 cellG33reflects 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 growthtotal 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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started