Stock portfolio analysis
For this assignment you are required to create fictitious stock
portfolio by investing a total of $100,000 (paper money, of course!) in five firms in the Information Technology Industry (Firms must be listed on a U.S. stock exchange such as New York Stock Exchange--NYSE--or NASDAQ). The assignment will be graded based on the completion of the required parts according to the instructions and not on the financial performance of the portfolio. In other words, if you lose money, you don't lose points. You can use Yahoo! or Google Finance or any other resource to get information on stock prices.
Assignment Details: You are working for a mutual fund manager at a boutique investment firm in downtown. The fund manager has been looking for new investment opportunities. She is particularly interested in the IT industry. She has asked you to identify five firms in IT industry listed on one of the U.S. stock exchange such as New York Stock Exchange -NYSE -- or NASDAQ. Using these five firms she wants you to create IT portfolio. Since she is new to the IT industry she first wants you to do some basic research using historical pricing. Once this research is complete, she wants you to prepare business memo that makes a recommendation based on the findings of your research. This includes 1. Your recommendation on which firms would be the best candidates for future investment, and 2. Your reasoning behind the recommendation, and 3. A brief response to each of the items in the following list: A. She wants to first find out when and by how much stock prices for the selected firms fluctuate drastically. See Step 3-A instructions to complete in Excel.
B. The fund manager plans to discuss these results with her colleagues to get their feedback. She wants a visual summary of the stock pricing data and NASDAQ market index. She wants you to create pivot chart that will help her during her discussion. She wants to know how prices of these 5 stocks in your portfolio and the NASDAQ go up and down around the same timeframe? See Step 3-B instructions to create the chart in Excel.
C. The fund manager also wants to know how the ROI of your IT portfolio compares with that of NASDAQ, the market index for IT firms. She wants to know whether it was better than that of NASDAQ (i.e. whether you beat the market). See Step 3-C instructions to complete portfolio analysis in Excel.
D. Your manager has recently learned about a powerful Excel feature called, Solver, in Excel. She is interested in finding out how it may be used to optimize the performance of your portfolio (in other words, improve ROI or profit of your portfolio). She has asked you to use the solver and compare the ROI returned by solver with the ROI of your original portfolio. (e.g. how were investments split across multiple firms, whether you were happy with Solver's solution). See Step 3-D instructions to setup and run Solver in Excel.
STEP 3-A Instructions: Stock Price analysis A. Create separate worksheet for each selected firm and enter the name of the Firm in the index tab. i. Obtain the stock prices of your selected firm since the Feb 1st 2015(including Feb 1st 2015) until Feb 15th 2019 (including Feb 15th 2019). For each day, retrieve the open, high, low, close prices, and volumes traded, for each of your selected stocks. ii. From your data remove any rows (if any) that have dividend/split information. B. For each stock, calculate % change in that day's closing price from the previous day's closing price and store it in a new column called '% Change from Previous Day's Closing'.
C. Using conditional formatting (CF), highlight the entire rows for top five highest positive and top five highest negative % change values i. You will need to use LARGE and SMALL functions to identify these numbers (we do not learn these functions in class but you need to find how they work online). ii. Then use these functions in the formulas in 'Manage Rules' option in CF to create new rules for highlight the rows1. STEP 3-B Instructions: Visual stock data summary A. Create new worksheet named All Closing. Copy the daily closing prices of the 5 stocks in your portfolio, for the duration you held the stocks. B. Also copy the closing value of the NASDAQ divided by 100 to get the "price" of the index for the same time period. The top of the All Closing worksheet would look like following: Date Stock1 Stock2 Stock3 Stock4 Stock5 NASDAQ 9/15/2015 $ 57.28 $ 23.62 $ 100.95 $ 4.41 $ 19.98 $ 23.40 9/12/2015 ... C. Create pivot table and pivot chart in a new worksheet that shows the monthly average closing prices for all the five stocks and NASDAQ D. Make sure that the chart has a title, the appropriate legends, and is properly formatted. i. Make sure lines are clearly visible (increase the thickness of the lines if needed) and
STEP 3-C Instructions: Portfolio Analysis A. Create new worksheet in your Excel workbook. Name it as MyTechPortfolio. MyTechPortfolio worksheet would look like below. To fill this worksheet, follow the steps B through E below:
# of shares
Initial $ Invested Profit/Loss ROI (%) Firm 1 Xxx Firm 2 Firm 3 Firm 4 Firm 5
Portfolio Analysis →→→→
Total Amt Invested
Total ROI Xxx
# of shares
Initial $ Invested Profit/Loss ROI (%) XXX
B. 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 dothis is to decide the amount to invest, and then divide it by the stock price. Do not buy fractions of shares.
1 Also helpful is the tutorial "Format entire row with Conditional Formatting, http://www.contextures.com/xlCondFormat02.html"
i. 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. ii. You will invest in your portfolio by "purchasing" the shares at the opening prices on the first day (i.e. Feb 1st 2015) and liquidate it by "selling" all the shares at the closing prices on the last day (i.e. Feb 15th 2019). C. For each firm: i. 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). The formula to calculate ROI is: (sale price - buy price) / (buy price). Label and format the columns appropriately. D. For the entire portfolio: i. Develop and enter Excel formulas to calculate Total Amt Invested, Total Gain Amt (or Total Loss Amt) and Total ROI of the entire portfolio. (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.) E. NASDAQ market comparison: i. 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. Feb 1st 2015), and sell it at its value at the closing price of the last trading day (i.e. Feb 15th 2019). ii. Using the bottom part of the spreadsheet 'MyTechPortfolio' 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.
STEP 3-D Instructions: Solver setup A. Identify your goal (It could be to maximize the Portfolio ROI or Portfolio Profit). B. Identify which cells should vary to set up the solver model. C. Use the following constraints: i. Initial amount invested in each firm must be at least $5,000 and should not exceed $50,000. ii. Total amount invested across all the five firms should be no less than $95,000 and no greater than $100,000. After you setup the solver parameters and run it, you will see that solver has identified a solution that meets your objective. EXTREMELY IMPORTANT: At this stage, solver will ask you which solution to keep. DO NOT select "Keep Solver Solution". SELECT 2nd option of "Retain Original Values'. Put meaningful row/column labels and format all worksheets appropriately i.e. wherever appropriate use $, %, etc. Make sure row/column titles are clearly visible at all times.
Recently Asked Questions
- The jackpot of a lottery is paid in 20 annual installments.There is also a cash option, which pays the winner 65% of the jackpot instantly.In either case 30%
- What impact does the explosion of the number of nodes on the Internet of things have on the routing fabric? How feasible is it to maintain home routers
- Please refer to the attachment to answer this question. This question was created from ml25.pdf.