For this assignment you are required to create a 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 an 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 a business memo that makes a recommendation based on the findings of your research. This includes1. 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 this 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 a 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 a 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 1 st 2013(including Feb 1 st 2013) until Feb 15th 2017 (including Feb 15th 2017). For each day, retrieve the open, high, low, close prices, and volumes traded, for each of your selected stocks. 2 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 a 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/2014 $ 57.28 $ 23.62 $ 100.95 $ 4.41 $ 19.98 $ 23.40 9/12/2014 … C. Create a 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 ii. Use the secondary axis to plot the data as required. STEP 3-C Instructions: Portfolio Analysis A. Create a 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 1 Also helpful is the tutorial “Format entire row with Conditional Formatting, http://www.contextures.com/xlCondFormat02.html” 3 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 do this is to decide the amount to invest, and then divide it by the stock price. Do not buy fractions of shares. 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. Sep 1st 2012) and liquidate it by “selling” all the shares at the closing prices on the last day (i.e. Sep 15th 2016). 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 2013), and sell it at its value at the closing price of the last trading day (i.e. Feb 15th 2017). 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 2 nd 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. 4 Instructions for Memo Writing2 Your memo should be between 400-600 words and should be typed up using word processing software. It should be single-spaced, 1 inch margin, and with 12 point Times New Roman font. Memo details must include:  Date: The date on which the memo is (to be) distributed (This will be the assignment due date)  To: The person(s) to who it is primarily addressed (sometimes with job title)  Cc: Name(s) of anyone else who receives a copy (sometimes with job title)  From: Name of the writer(s)  Subject: or Re: Concise statement of the memo’s topic You must also consider the following before writing your memo: i. Audience identity (who is the audience?) – clearly state that in the memo ii. Motivation/objective of memo (e.g. should this be persuasive, directive, or technical) iii. Is the memo concise? Does it come to the point? iv. Maintain a business-like tone v. Use headings, bullets, and/or numbered lists so key points stand out and the document is easy to read vi. Short paragraphs vii. Proofread! viii. Identification of any attachments ix. Suitable signoff (e.g. “from”, “sincerely”, etc.) Deliverables 1. Your Word document and Excel spreadsheet 2. Name Excel and Word file properly: FirstNameLastName.xlsx and FirstNameLastName.docx. 3. Cover sheet shows the team member names if you work in a group. 4. Submit the file through Blackboard’s Assignment submission system by the above deadline. Important… Presentation of the EXCEL AND WORD document matters and will be graded. Format your files to look clean and professional.