View the step-by-step solution to:

Question

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

 2 

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: 

Firm name 

Purchase price 

Sell price 

# of shares 

Initial $ Invested Profit/Loss ROI (%) Firm 1 Xxx   Firm 2    Firm 3    Firm 4    Firm 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    

 

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"  

 3 

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

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Let our 24/7 Computer Science tutors help you get unstuck! Ask your first question.
A+ icon
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes
A+ icon
Ask Expert Tutors