(Excel )

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 includes

Your recommendation on which firms would be the best candidates for future investment, and

Your reasoning behind the recommendation, and

A brief response to each of the items in the following list:

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.

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.

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.

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 Solvers solution). See Step 3-D instructions to setup and run Solver in Excel.

STEP 3-A Instructions: Stock Price analysis

Create a separate worksheet for each selected firm and enter the name of the Firm in the index tab.

Obtain the stock prices of your selected firm since the Feb 1^st 2013(including Feb 1^st 2013) until Feb 15^th 2017 (including Feb 15^th 2017). For each day, retrieve the open, high, low, close prices, and volumes traded, for each of your selected stocks.

From your data remove any rows (if any) that have dividend/split information.

For each stock, calculate % change in that days closing price from the previous day's closing price and store it in a new column called % Change from Previous Days Closing.

Using conditional formatting (CF), highlight the entire rows for top five highest positive and top five highest negative % change values

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

Then use these functions in the formulas in Manage Rules option in CF to create new rules for highlight the rows[1].

STEP 3-B Instructions: Visual stock data summary

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.

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

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

Make sure that the chart has a title, the appropriate legends, and is properly formatted.

Make sure lines are clearly visible (increase the thickness of the lines if needed) and

Use the secondary axis to plot the data as required.

STEP 3-C Instructions: Portfolio Analysis

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

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.

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.

You will invest in your portfolio by "purchasing" the shares at the opening prices on the first day (i.e. Sep 1^st 2012) and liquidate it by "selling" all the shares at the closing prices on the last day (i.e. Sep 15^th 2016).

For each firm:

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.

For the entire portfolio:

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

NASDAQ market comparison:

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 15^th 2017).

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

Identify your goal (It could be to maximize the Portfolio ROI or Portfolio Profit).

Identify which cells should vary to set up the solver model.

Use the following constraints:

Initial amount invested in each firm must be at least $5,000 and should not exceed $50,000.

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.

[1] Also helpful is the tutorial Format entire row with Conditional Formatting, http://www.contextures.com/xlCondFormat02.html