Management Information System

For this assignment each team is required to create a fictitious stock portfolio by investing a total of $100,000 (paper money, of course! - not real money) in five companies in the Information Technology Industry, analyze the portfolio's performance using Excel, create a chart to visualize the relative performance of the stocks in the portfolio and of the Dow Jones Industrial Average (DJIA) index. If you do not know what the DJIA is, please read about the index in Wikipedia or a financial news site..

Create an Excel file, pick five technology companies like Apple, Oracle, SAP, etc. These stocks will represent your portfolio which you will track for a period of the past six consecutive months, Create your own (hypothetical) portfolio by distributing $100,000 across your five selected companies. Figure out the number of shares you want to buy from each company. 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 money in each of the five .-(all this is done)

step 1- Invest (about as close as you can without going over) $100,000 in the Dow Jones Industrial Average (DJIA). 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 23,700, then the price is $237.00). You will buy the index at its value at the opening of the market the first trading day, and sell it at its value at the end of the last trading day.

Using the bottom part of the Portfolio spreadsheet you have already designed, display the number of units of the DJIA purchased, the opening price, the closing price, the total purchase price, and compute your profit (loss) and the ROI.

Portfolio:this is how the portfolio looks like

Company	Ticker	Number of Shares Purchased	Unit Purchase Price	Total Purchase Price	Unit Selling Price	Total Selling Price	Gain/Loss	ROI
Apple Inc.	AAPL	97	$ 206.43	$ 20,023.71	$ 273.36	$ 26,515.92	$ 6,492.21	32.42%
Facebook, Inc.	FB	108	$ 184.00	$ 19,872.00	$ 192.47	$ 20,786.76	$ 914.76	4.60%
Microsoft Corporation	MSFT	146	$ 136.61	$ 19,945.06	$ 162.01	$ 23,653.46	$ 3,708.40	18.59%
Alphabet Inc.	GOOGL	17	$ 1,181.85	$ 20,091.45	$ 1,339.25	$ 22,767.25	$ 2,675.80	13.32%
Intel Corporation	INTC	425	$ 47.12	$ 20,026.00	$ 55.52	$ 23,596.00	$ 3,570.00	17.83%
		793	$ 1,756.01	$ 99,958.22	$ 2,022.61	$ 117,319.39	$ 17,361.17	17.37%
Entire Portfolio	Maximum Gain	Minimum Gain	Maximum ROI	Minimum ROI	Average ROI	Total Gain	Total ROI
	$ 6,492.21	$ 914.76	32.42%	4.60%	17.35%	$17,361.17	17.37%
Company	Ticker	Number of Shares Purchased	Unit Purchase Price	Total Purchase Price	Unit Selling Price	Total Selling Price	Gain/Loss	ROI
Dow Jones Industrial Average	DJIA

Monthly data

Ticker	Date	Open	High	Low	Close	Volume
AAPL	9/1/2019	$ 206.43	$ 226.42	$ 204.22	$ 223.97	542,567,100
AAPL	10/1/2019	$ 225.07	$ 249.75	$ 215.13	$ 248.76	608,302,700
AAPL	11/1/2019	$ 249.54	$ 268.00	$ 249.16	$ 267.25	448,331,500
AAPL	12/1/2019	$ 267.27	$ 293.97	$ 256.29	$ 293.65	597,198,700
AAPL	1/1/2020	$ 296.24	$ 327.85	$ 292.75	$ 309.51	733,592,600
AAPL	2/1/2020	$ 304.30	$ 327.22	$ 256.37	$ 273.36	754,962,800
FB	9/1/2019	$ 184.00	$ 193.10	$ 175.66	$ 178.08	264,538,500
FB	10/1/2019	$ 179.15	$ 198.09	$ 173.09	$ 191.65	326,207,200
FB	11/1/2019	$ 192.85	$ 203.80	$ 188.54	$ 201.64	258,303,900
FB	12/1/2019	$ 202.13	$ 208.93	$ 193.17	$ 205.25	276,257,100
FB	1/1/2020	$ 206.75	$ 224.20	$ 201.06	$ 201.91	347,314,600
FB	2/1/2020	$ 203.44	$ 218.77	$ 181.82	$ 192.47	317,576,800
MSFT	9/1/2019	$ 136.61	$ 142.37	$ 134.51	$ 139.03	472,544,800
MSFT	10/1/2019	$ 139.66	$ 145.67	$ 133.22	$ 143.37	549,523,400
MSFT	11/1/2019	$ 144.26	$ 152.50	$ 142.97	$ 151.38	392,371,800
MSFT	12/1/2019	$ 151.81	$ 159.55	$ 146.65	$ 157.70	450,303,300
MSFT	1/1/2020	$ 158.78	$ 174.05	$ 156.51	$ 170.23	558,530,000
MSFT	2/1/2020	$ 170.43	$ 190.70	$ 152.00	$ 162.01	887,625,300
GOOGL	9/1/2019	$ 1,181.85	$ 1,248.02	$ 1,163.71	$ 1,221.14	25,853,600
GOOGL	10/1/2019	$ 1,222.49	$ 1,299.24	$ 1,163.14	$ 1,258.80	30,181,500
GOOGL	11/1/2019	$ 1,265.80	$ 1,333.92	$ 1,259.71	$ 1,304.09	26,438,500
GOOGL	12/1/2019	$ 1,302.56	$ 1,367.05	$ 1,277.05	$ 1,339.39	27,955,300
GOOGL	1/1/2020	$ 1,348.41	$ 1,500.58	$ 1,346.49	$ 1,432.78	33,679,700
GOOGL	2/1/2020	$ 1,461.65	$ 1,530.74	$ 1,268.21	$ 1,339.25	41,560,800
INTC	9/1/2019	$ 47.12	$ 53.33	$ 46.50	$ 51.53	387,955,800
INTC	10/1/2019	$ 51.97	$ 57.24	$ 48.53	$ 56.53	438,267,300
INTC	11/1/2019	$ 55.94	$ 59.13	$ 55.62	$ 58.05	319,751,400
INTC	12/1/2019	$ 58.55	$ 60.48	$ 55.75	$ 59.85	419,629,000
INTC	1/1/2020	$ 60.24	$ 69.29	$ 58.52	$ 63.93	534,150,100
INTC	2/1/2020	$ 64.46	$ 68.09	$ 53.60	$ 55.52	441,521,000

Step 2- Create a graph to visualize the trading data of the 5 stocks and the DJIA. In a new worksheet, named All Closing, organize the monthly closing prices of the 5 stocks in your portfolio, for the time you held the stocks. Also copy the closing value of the index divided by 100 to get the "price" of the index for the same time period. The top of the worksheet should look like the following example:

Date	Stock1	Stock2	Stock3	Stock4	Stock5	DJIA
Jul-31	$ 57.28	$ 23.62	$ 100.95	$ 4.41	$ 19.98	$168.25
Aug-31	$ 58.28
Sep-30	$ 57.55
Oct-31	$ 67.29
Nov-30	$ 63.28
Dec-31	$ 65.33