This question illustrates two similar ways to forecast future stock prices. You will use SimpleForecast.xlsx for this

Fantastic news! We've Found the answer you've been seeking!

Question:

This question illustrates two similar ways to forecast future stock prices. You will use

SimpleForecast.xlsx for this exercise. It gives the historical monthly closing prices for a

five-year period for five major listed IT stocks in North America, denoted as X1-X5. If you

regress the stock price of each company on time the estimated slope and intercept parameters

will give the linear trend line for the stock price. I have estimated the regression coefficients

for you and are given in the data spreadsheet. I also provide you with the linear trend line

fits for each regression, and the residuals from these fits, along with their means (which must

be 0 because they are from the regression estimates), standard deviations, and correlations.

These correlations are fairly large and positive, not too surprising given that these companies

are all in the same industry.

I want you to forecast the stock prices for these companies for the next two years. First,

assume that these stock prices move independent of each other. Please model the long-term

upward trends with triangular distributions in @RISK, with the most likely values equal to

the slopes of the regression lines, and the min and max, respectively, are 20% below and

above the most likely values. Next, forecast future stock price as the actual final observed

value on December 2020 plus the long-term trend term (Triangularly distributed slope term

Time index) plus a noise term. Assume that the noise term is normally distributed with

mean 0 and standard deviation equal to the standard deviation of the residuals from the

regression. These noise terms are uncorrelated. Now plot each forecasted stock prices over

time.

Now assume that noise terms are independent across months but are correlated across stocks

for a given month. Next, forecast future stock price as the actual final observed value on

December 2020 plus the long-term trend term (Triangularly distributed slope term Time

index) plus the correlated noise term. Assume that the correlated noise term is normally

distributed with mean 0 and standard deviation equal to the standard deviation of the

residuals from the regression. Now plot each forecasted stock prices over time. Does it make

a difference in your forecasting with or without the correlation in your noise term?

Time Series Data		Forecasts from linear regressions		Residuals from linear regressions		Regression coefficients of prices vs time
Date	Time	X1	X2	X3	X4	X5	X1	X2	X3	X4	X5	X1	X2	X3	X4	X5		X1	X2	X3	X4	X5
Jan-16	1	7.16	13.00	15.79	68.68	20.31	-17.46	15.52	10.94	70.69	18.95	24.62	-2.52	4.85	-2.01	1.36	Intercept	-19.86	15.33	10.39	70.34	18.81
Feb-16	2	7.18	13.27	15.83	69.30	18.65	-15.07	15.70	11.48	71.04	19.10	22.25	-2.43	4.35	-1.74	-0.45	Slope	2.39	0.19	0.55	0.35	0.15
Mar-16	3	7.51	13.88	14.41	69.22	18.68	-12.68	15.89	12.03	71.38	19.25	20.19	-2.01	2.38	-2.16	-0.57
Apr-16	4	7.07	12.89	14.21	69.64	19.09	-10.28	16.08	12.58	71.73	19.39	17.35	-3.19	1.63	-2.09	-0.30
May-16	5	7.11	14.89	14.90	75.39	20.16	-7.89	16.27	13.13	72.08	19.54	15.00	-1.38	1.77	3.31	0.62	Means, stdevs of residuals
Jun-16	6	8.98	16.29	17.82	78.32	19.40	-5.50	16.46	13.67	72.43	19.68	14.48	-0.17	4.15	5.89	-0.28		X1	X2	X3	X4	X5
Jul-16	7	9.53	16.67	19.54	73.39	20.21	-3.10	16.65	14.22	72.78	19.83	12.63	0.02	5.32	0.61	0.38	Mean	0.00	0.00	0.00	0.00	0.00
Aug-16	8	10.54	19.35	19.43	72.28	20.82	-0.71	16.84	14.77	73.12	19.98	11.25	2.51	4.66	-0.84	0.84	Stdev	17.65	3.38	3.79	8.50	1.61
Sep-16	9	11.31	19.00	18.29	73.10	20.91	1.68	17.03	15.32	73.47	20.12	9.63	1.97	2.97	-0.37	0.79
Oct-16	10	10.36	19.45	17.84	78.73	21.92	4.08	17.22	15.86	73.82	20.27	6.28	2.23	1.98	4.91	1.65	Correlations of residuals
Nov-16	11	11.44	20.78	20.55	79.76	20.72	6.47	17.40	16.41	74.17	20.41	4.97	3.38	4.14	5.59	0.31		X1	X2	X3	X4	X5
Dec-16	12	10.45	22.54	20.03	80.85	20.38	8.86	17.59	16.96	74.51	20.56	1.59	4.95	3.07	6.34	-0.18	X1	1.00	0.34	0.69	0.58	0.60
Jan-17	13	10.69	24.05	21.24	82.76	21.70	11.26	17.78	17.51	74.86	20.71	-0.57	6.27	3.73	7.90	0.99	X2	0.34	1.00	0.64	0.78	0.44
Feb-17	14	11.28	25.52	22.00	88.61	21.92	13.65	17.97	18.05	75.21	20.85	-2.37	7.55	3.95	13.40	1.07	X3	0.69	0.64	1.00	0.57	0.42
Mar-17	15	11.96	22.99	21.00	86.31	21.03	16.05	18.16	18.60	75.56	21.00	-4.09	4.83	2.40	10.75	0.03	X4	0.58	0.78	0.57	1.00	0.55
Apr-17	16	13.52	23.40	21.19	82.14	19.76	18.44	18.35	19.15	75.91	21.14	-4.92	5.05	2.04	6.23	-1.38	X5	0.60	0.44	0.42	0.55	1.00
May-17	17	12.89	20.76	18.28	78.86	20.72	20.83	18.54	19.70	76.25	21.29	-7.94	2.22	-1.42	2.61	-0.57
Jun-17	18	14.03	22.21	19.71	79.40	20.80	23.23	18.73	20.24	76.60	21.44	-9.20	3.48	-0.53	2.80	-0.64
Jul-17	19	16.27	23.53	19.65	79.00	22.64	25.62	18.92	20.79	76.95	21.58	-9.35	4.61	-1.14	2.05	1.06
Aug-17	20	16.17	20.77	18.77	78.04	22.59	28.01	19.10	21.34	77.30	21.73	-11.84	1.67	-2.57	0.74	0.86
Sep-17	21	17.25	18.62	16.66	76.06	21.71	30.41	19.29	21.89	77.64	21.87	-13.16	-0.67	-5.23	-1.58	-0.16
Oct-17	22	19.38	17.97	17.54	77.01	21.99	32.80	19.48	22.43	77.99	22.02	-13.42	-1.51	-4.89	-0.98	-0.03
Nov-17	23	26.20	19.07	17.45	80.61	22.24	35.19	19.67	22.98	78.34	22.17	-8.99	-0.60	-5.53	2.27	0.07
Dec-17	24	33.53	18.61	18.71	84.80	23.76	37.59	19.86	23.53	78.69	22.31	-4.06	-1.25	-4.82	6.11	1.45
Jan-18	25	32.20	19.18	19.69	88.71	23.68	39.98	20.05	24.08	79.04	22.46	-7.78	-0.87	-4.39	9.67	1.22
Feb-18	26	38.45	17.91	18.40	84.07	23.29	42.37	20.24	24.63	79.38	22.61	-3.92	-2.33	-6.23	4.69	0.68
Mar-18	27	44.86	17.29	19.53	83.47	22.37	44.77	20.43	25.17	79.73	22.75	0.09	-3.14	-5.64	3.74	-0.38
Apr-18	28	41.67	17.76	20.68	82.39	21.49	47.16	20.62	25.72	80.08	22.90	-5.49	-2.86	-5.04	2.31	-1.41
May-18	29	36.06	17.14	19.30	68.86	22.49	49.56	20.81	26.27	80.43	23.04	-13.50	-3.67	-6.97	-11.57	-0.55
Jun-18	30	39.76	19.26	21.22	68.30	23.01	51.95	20.99	26.82	80.77	23.19	-12.19	-1.73	-5.60	-12.47	-0.18
Jul-18	31	36.81	18.94	22.24	67.08	22.15	54.34	21.18	27.36	81.12	23.34	-17.53	-2.24	-5.12	-14.04	-1.19
Aug-18	32	42.65	19.01	23.29	75.45	22.84	56.74	21.37	27.91	81.47	23.48	-14.09	-2.36	-4.62	-6.02	-0.64
Sep-18	33	46.89	17.49	26.26	73.06	24.49	59.13	21.56	28.46	81.82	23.63	-12.24	-4.07	-2.20	-8.76	0.86
Oct-18	34	53.61	17.79	27.70	72.69	23.01	61.52	21.75	29.01	82.17	23.77	-7.91	-3.96	-1.31	-9.48	-0.76
Nov-18	35	57.59	17.32	26.60	74.20	22.99	63.92	21.94	29.55	82.51	23.92	-6.33	-4.62	-2.95	-8.31	-0.93
Dec-18	36	67.82	17.41	28.15	80.75	24.83	66.31	22.13	30.10	82.86	24.07	1.51	-4.72	-1.95	-2.11	0.76
Jan-19	37	71.89	17.00	27.24	74.67	23.46	68.70	22.32	30.65	83.21	24.21	3.19	-5.32	-3.41	-8.54	-0.75
Feb-19	38	75.51	18.44	29.66	73.85	25.25	71.10	22.51	31.20	83.56	24.36	4.41	-4.07	-1.54	-9.71	0.89
Mar-19	39	68.49	20.09	31.21	73.07	24.19	73.49	22.69	31.74	83.90	24.50	-5.00	-2.60	-0.53	-10.83	-0.31
Apr-19	40	62.72	21.51	31.37	75.10	24.49	75.88	22.88	32.29	84.25	24.65	-13.16	-1.37	-0.92	-9.15	-0.16
May-19	41	70.39	20.80	30.96	74.98	21.74	78.28	23.07	32.84	84.60	24.80	-7.89	-2.27	-1.88	-9.62	-3.06
Jun-19	42	59.77	19.54	30.88	73.02	20.47	80.67	23.26	33.39	84.95	24.94	-20.90	-3.72	-2.51	-11.93	-4.47
Jul-19	43	57.27	19.39	30.29	70.21	21.05	83.07	23.45	33.93	85.30	25.09	-25.80	-4.06	-3.64	-15.09	-4.04
Aug-19	44	67.96	17.75	30.51	70.75	21.74	85.46	23.64	34.48	85.64	25.24	-17.50	-5.89	-3.97	-14.89	-3.50
Sep-19	45	67.85	21.83	34.96	74.29	23.31	87.85	23.83	35.03	85.99	25.38	-20.00	-2.00	-0.07	-11.70	-2.07
Oct-19	46	76.98	22.81	35.16	75.18	24.80	90.25	24.02	35.58	86.34	25.53	-13.27	-1.21	-0.42	-11.16	-0.73
Nov-19	47	81.08	23.95	37.12	84.72	26.04	92.64	24.21	36.13	86.69	25.67	-11.56	-0.26	0.99	-1.97	0.37
Dec-19	48	91.66	26.71	37.81	84.62	26.72	95.03	24.39	36.67	87.03	25.82	-3.37	2.32	1.14	-2.41	0.90
Jan-20	49	84.84	27.13	39.55	89.43	27.17	97.43	24.58	37.22	87.38	25.97	-12.59	2.55	2.33	2.05	1.20
Feb-20	50	85.73	26.43	41.56	91.27	28.08	99.82	24.77	37.77	87.73	26.11	-14.09	1.66	3.79	3.54	1.97
Mar-20	51	84.61	25.75	37.78	85.81	25.72	102.21	24.96	38.32	88.08	26.26	-17.60	0.79	-0.54	-2.27	-0.54
Apr-20	52	92.91	25.34	38.62	87.03	25.45	104.61	25.15	38.86	88.43	26.40	-11.70	0.19	-0.24	-1.40	-0.95
May-20	53	99.80	26.55	40.54	94.37	27.34	107.00	25.34	39.41	88.77	26.55	-7.20	1.21	1.13	5.60	0.79
Jun-20	54	121.19	26.72	43.98	98.81	28.12	109.39	25.53	39.96	89.12	26.70	11.80	1.19	4.02	9.69	1.42
Jul-20	55	122.04	27.65	43.01	97.56	27.00	111.79	25.72	40.51	89.47	26.84	10.25	1.93	2.50	8.09	0.16
Aug-20	56	131.76	28.70	44.36	102.56	26.56	114.18	25.91	41.05	89.82	26.99	17.58	2.79	3.31	12.74	-0.43
Sep-20	57	138.48	31.69	47.56	108.54	26.41	116.58	26.10	41.60	90.16	27.13	21.90	5.59	5.96	18.38	-0.72
Oct-20	58	153.47	32.89	48.07	109.58	27.08	118.97	26.28	42.15	90.51	27.28	34.50	6.61	5.92	19.07	-0.20
Nov-20	59	189.95	32.82	49.89	108.01	33.84	121.36	26.47	42.70	90.86	27.43	68.59	6.35	7.19	17.15	6.41
Dec-20	60	182.22	27.82	49.39	98.18	30.99	123.76	26.66	43.24	91.21	27.57	58.46	1.16	6.15	6.97	3.42