PART-C Instructions: Portfolio Analysis

1. Create a new worksheet in your Excel workbook. Name it as MyPortfolio and would look like below.

To fill this worksheet, follow the steps 2-5 below:

Company name Purchase price Sell price # of shares Initial $ Invested Profit/Loss ROI (%)

Company 1 $ Xxx 4

Company 2

Company 3

Company 4

Company 5

Portfolio Analysis Total Amt Invested Total Profit/Loss Total ROI Xxx

NASDAQ Purchase price Sell price # of shares Initial $ Invested Profit/Loss ROI (%) $ Xxx

2. Create your own (hypothetical) portfolio by distributing $100,000 across your five selected firms. Determine the number of shares you want to buy from each firm. One way to do this is to decide the amount to invest, and then divide it by the stock price. Do not buy fractions of shares.

a. You may allocate different amounts of money to each stock, but you must invest some money in each of the five firm (at least $5,000 and at most $50,000), and you must invest around $100,000 without going over.

b. You will invest in your portfolio by purchasing the shares at the opening prices on the first day i.e. Sept 15 th 2018 [this will be purchase price]. You will liquidate it by selling all the shares at the closing prices on the last day i.e. Sept 15 th 2019 [this will be sell price].

3. For each company: a. Develop and enter the appropriate Excel formulas to calculate: the initial amount invested in each stock, the profit (or loss) when you sold the shares of that firm, and Return on Investment (ROI). i. The formula to calculate ROI is: (sale price buy price) / (buy price). Label and format the columns appropriately.

4. For the entire portfolio: a. Develop and enter Excel formulas to calculate Total Amt Invested, Total Gain Amt (or Total Loss Amt) and Total ROI of the entire portfolio.

b. Hint: Total ROI is not sum of individual ROIs. To compute the Total ROI on the entire portfolio, use the total profit (or total loss) on the portfolio and the total amount invested.

5. NASDAQ market comparison: a. Similar to how you invested in the stocks of firms, invest another $100,000 in the NASDAQ Composite Index. In order to do that, assume you can buy the index at a price equal to its value divided by 100 (e.g. if the index is 2100, then the price is $21.00). You will buy the index at its value at the opening price on the first trading day (i.e. Sept 15 th 2018), and sell it at its value at the closing price of the last trading day (i.e. Sept 15 th 2019). b. Using the bottom part of the spreadsheet MyPortfolio that you have already designed, note down the number of units of the NASDAQ purchased, the opening price, the closing price, the total purchase price, and compute your profit (loss) and the ROI.