Question

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. 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. You must design the Excel files so it is easy to read. Make sure all amounts are in a numeric format with two decimal places.

Answer 1

There are two charts:

1. Companies vs Dow - Image 1

2. Portfolio vs Dow - Image 2

$1,00,000 is almost equally invested in 5 IT companies - Refer Image 3

Relative return has been calculated in image 4

Image 5 is used for chart

Image 1

Individual Companies vs Dow 40.00% 30.00% 20.00% 10.00% 0.00% 10 1 6 11 12 13 -10.00% -20.00% INTC BM ORCL MSFT CSCO Dow Ret urn LD Ln

Image - 2

Portfolio Performance vs Dow 20.00% 15.00% 10.00% 5.00% 0.00% 1 2 4 5 6 8 9 11 12 13 -5.00% -10.00% Portfolio Return Dow Ret urn 10 Ln

Image 3

$1,00,000 is invested almost equally on each company.
Company	Quantity	Price on Investment	Investment Value
IBM	161	$                         124.34	$                   20,018.74
ORCL	410	$                           48.76	$                   19,991.60
MSFT	180	$                         110.89	$                   19,960.20
CSCO	418	$                           47.87	$                   20,009.66
INTC	406	$                           49.31	$                   20,019.86
			$               1,00,000.06

Image 4

Date	IBM	Investment Value	IBM	ORCL	Investment Value	ORCL	MSFT	Investment Value	MSFT	CSCO	Investment Value	CSCO	INTC	Investment Value	INTC		Portfolio Investment Value	Portfolio Return		Dow Jones	Dow Return
01-11-2018	$ 124.34	$              20,018.74	0.00%	$ 48.76	$              19,991.60	0.00%	$ 110.89	$              19,960.20	0.00%	$ 47.87	$              20,009.66	0.00%	$ 49.31	$              20,019.86	0.00%		$                               1,00,000.06	0.00%		25538.46	0.00%
01-12-2018	$ 113.67	$              18,300.87	-8.58%	$ 45.15	$              18,511.50	-7.40%	$ 101.57	$              18,282.60	-8.40%	$ 43.33	$              18,111.94	-9.48%	$ 46.93	$              19,053.58	-4.83%		$                                  92,260.49	-7.74%		23327.46	-8.66%
01-01-2019	$ 134.42	$              21,641.62	8.11%	$ 50.23	$              20,594.30	3.01%	$ 104.43	$              18,797.40	-5.83%	$ 47.29	$              19,767.22	-1.21%	$ 47.12	$              19,130.72	-4.44%		$                                  99,931.26	-0.07%		24999.67	-2.11%
01-02-2019	$ 138.13	$              22,238.93	11.09%	$ 52.13	$              21,373.30	6.91%	$ 112.03	$              20,165.40	1.03%	$ 51.77	$              21,639.86	8.15%	$ 52.96	$              21,501.76	7.40%		$                               1,06,919.25	6.92%		25916.00	1.48%
01-03-2019	$ 141.10	$              22,717.10	13.48%	$ 53.71	$              22,021.10	10.15%	$ 117.94	$              21,229.20	6.36%	$ 53.99	$              22,567.82	12.78%	$ 53.70	$              21,802.20	8.90%		$                               1,10,337.42	10.34%		25928.68	1.53%
01-04-2019	$ 140.27	$              22,583.47	12.81%	$ 55.33	$              22,685.30	13.47%	$ 130.60	$              23,508.00	17.77%	$ 55.95	$              23,387.10	16.88%	$ 51.04	$              20,722.24	3.51%		$                               1,12,886.11	12.89%		26592.91	4.13%
01-05-2019	$ 126.99	$              20,445.39	2.13%	$ 50.60	$              20,746.00	3.77%	$ 123.68	$              22,262.40	11.53%	$ 52.03	$              21,748.54	8.69%	$ 44.04	$              17,880.24	-10.69%		$                               1,03,082.57	3.08%		24815.04	-2.83%
01-06-2019	$ 137.90	$              22,201.90	10.91%	$ 56.97	$              23,357.70	16.84%	$ 133.96	$              24,112.80	20.80%	$ 54.73	$              22,877.14	14.33%	$ 47.87	$              19,435.22	-2.92%		$                               1,11,984.76	11.98%		26599.96	4.16%
01-07-2019	$ 148.24	$              23,866.64	19.22%	$ 56.30	$              23,083.00	15.46%	$ 136.27	$              24,528.60	22.89%	$ 55.40	$              23,157.20	15.73%	$ 50.55	$              20,523.30	2.51%		$                               1,15,158.74	15.16%		26864.27	5.19%
01-08-2019	$ 135.53	$              21,820.33	9.00%	$ 52.06	$              21,344.60	6.77%	$ 137.86	$              24,814.80	24.32%	$ 46.81	$              19,566.58	-2.21%	$ 47.41	$              19,248.46	-3.85%		$                               1,06,794.77	6.79%		26403.28	3.39%
01-09-2019	$ 145.42	$              23,412.62	16.95%	$ 55.03	$              22,562.30	12.86%	$ 139.03	$              25,025.40	25.38%	$ 49.41	$              20,653.38	3.22%	$ 51.53	$              20,921.18	4.50%		$                               1,12,574.88	12.57%		26916.83	5.40%
01-10-2019	$ 133.73	$              21,530.53	7.55%	$ 54.49	$              22,340.90	11.75%	$ 143.37	$              25,806.60	29.29%	$ 47.51	$              19,859.18	-0.75%	$ 56.53	$              22,951.18	14.64%		$                               1,12,488.39	12.49%		27046.23	5.90%
31-10-2019	$ 133.73	$              21,530.53	7.55%	$ 54.49	$              22,340.90	11.75%	$ 143.37	$              25,806.60	29.29%	$ 47.51	$              19,859.18	-0.75%	$ 56.53	$              22,951.18	14.64%		$                               1,12,488.39	12.49%		27046.23	5.90%

Image 5

IBM	ORCL	MSFT	CSCO	INTC	Portfolio Return	Dow Return
0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
-8.58%	-7.40%	-8.40%	-9.48%	-4.83%	-7.74%	-8.66%
8.11%	3.01%	-5.83%	-1.21%	-4.44%	-0.07%	-2.11%
11.09%	6.91%	1.03%	8.15%	7.40%	6.92%	1.48%
13.48%	10.15%	6.36%	12.78%	8.90%	10.34%	1.53%
12.81%	13.47%	17.77%	16.88%	3.51%	12.89%	4.13%
2.13%	3.77%	11.53%	8.69%	-10.69%	3.08%	-2.83%
10.91%	16.84%	20.80%	14.33%	-2.92%	11.98%	4.16%
19.22%	15.46%	22.89%	15.73%	2.51%	15.16%	5.19%
9.00%	6.77%	24.32%	-2.21%	-3.85%	6.79%	3.39%
16.95%	12.86%	25.38%	3.22%	4.50%	12.57%	5.40%
7.55%	11.75%	29.29%	-0.75%	14.64%	12.49%	5.90%
7.55%	11.75%	29.29%	-0.75%	14.64%	12.49%	5.90%

Feel fee to let me know in case of any questions.