Question

The Wellton Fund is a balanced mutual fund that includes a mix of stocks and bonds. Following are the year-end share prices of the fund and Dow Jones Industrial Average (DJIA) for a period: Year 3 4 5 6 7 8 9 10 Share Price $14.75 15.06 14.98 15.73 16.11 16.07 16.78 17.69 16.90 17.81 19.08 DJIA 1,046 1.258 1.211 1,546 1,895 1,938 2,168 2,753 2.633 3,168 3,301 (a) Develop a linear trend model to forecast Share Price for year 12. (b) Develop linear trend model for DJIA to predict its value for year 12. (c) Develop regression model to forecast DJIA using Share Price. Predict value of DJIA for year 12 using the value of Share Price predicted in in (a). (d) Which DJIA forecast, regression (c) or liear trend (b), is more accurate. Compute appropriate measures (MAD and MAPD).

Answer using excel

Answer 1

a.

Linear Trend model for Share Price:

The linear trend equation format:

y = a + bx

where y = share price at a time period x

x = time period

a = intercept

b = slope of the trend line

Determining values of a and b using Least Square Method:

Year(x)	Share Price (y)	xy	x^2
1	14.75	14.75	1
2	15.06	30.12	4
3	14.98	44.94	9
4	15.73	62.92	16
5	16.11	80.55	25
6	16.07	96.42	36
7	16.78	117.46	49
8	17.69	141.52	64
9	16.9	152.1	81
10	17.81	178.1	100
11	19.08	209.88	121
66	180.96	1128.76	506	Total

xy is x*y

X^2 is the squared value of x

x̄ = ∑x /n = 66/11 = 6

ȳ = ∑y/n = 180.96/11 = 16.45

b = (∑xy – nx̄ȳ) / (∑(x^2)-n (x̄^2)) = (1128.76-11*6*16.45)/(506-11*6^2) = 0.39

a = ȳ - b x̄ = 16.45-0.391*6 = 14.1

trend line equation:

y = 14.1 + 0.39x

forecast for the 12th year = 14.1+0.39*12 = 18.78

Forecast for shareprice for year 12 is 18.78

Excel Formula

A C D E F G 15 16 B Linear Trend Year (x) 1 17 Share Price (y) 14.75 15.06 18 2 19 3 14.98 15.73 ху =C17*B17 =C18*B18 =C19*B19 =C20*B20 =C21*B21 =C22*B22 20 4 21 5 22 6 17 16.11 16.07 16.78 17.69 x^2 =B17^2 =B1842 =B1912 =B2012 =B2142 =B2212 =B2312 =B24^2 =B25^2 =B26^2 =B2712 =SUM(E17:E27) 23 =C23*B23 x bar = =B28/11 y bar = =C28/11 b = =(D28-11*G21*G22)/(E28-11*G21^2) a = =G22-G23 G21 forecast for year 12 =G24+G23*12 24 8 25 9 16.9 26 10 =C24*B24 =C25*B25 =C26*B26 =C27*B27 =SUM(D17:D27) 27 11 17.81 19.08 =SUM(C17:C27) 28 Total Total =SUM(B17:327) 29

b.

Linear Trend Model for DJIA:

Equation is y = a + bx

where y = DJIA

and x = time period

a and b represent the same as before

Determining values of a and b using Least Square Method:

Year(x)	DJIA (y)	xy	x^2
1	1046	1046	1
2	1258	2516	4
3	1211	3633	9
4	1546	6184	16
5	1895	9475	25
6	1938	11628	36
7	2168	15176	49
8	2753	22024	64
9	2633	23697	81
10	3168	31680	100
11	3301	36311	121
66	22917	163370	506	Total

The linear trendline equation format is as mentioned above.

x̄ = ∑x /n = 66/11 = 6

ȳ = ∑y/n = 22917/11 = 2083.36

b = (∑xy – nx̄ȳ) / (∑(x^2)-n (x̄^2)) = (163370-11*6*2083.364)/(506-11*6^2) = 235.16

a = ȳ - b x̄ = 2083.364-235.163*6 = 672.4

trend line equation:

y = 672.4 + 235.16x

forecast for the 12th year = 672.4 + 235.16*12 = 3494.3

Forecast for DJIA for year 12 is 3494

Excel Formula

А B с D E F G Linear Trend Year (x) 15 16 17 18 19 1 2 3 DJIA (Y) 1046 1258 1211 1546 1895 1938 2168 4 20 21 5 22 6 ху =C17*B17 =C18*B18 =C19*B19 =C20*B20 =C21*B21 =C22*B22 =C23*B23 =C24*B24 =C25*B25 =C26*B26 =C27*B27 =SUM(D17:D27) x^2 =B1742 =B18^2 =B1942 =B2012 =B2142 =B2242 =B2312 =B2412 =B25^2 =B26^2 =B27^2 =SUM(E17:E27) 7 23 24 x bar = =B28/11 y bar = =C28/11 b = |=(D28-11*G21*G22)/(E28-11*G21^2) a = I=G22-G23 G21 forecast for year 12 =G24+G23*12 8 2753 25 9 2633 26 27 10 11 =SUM(B17:B27) 3168 3301 =SUM(C17:C27) 28 Total 29

c.

Regression model to forecast DJIA using share price.

As the DJIA is to be determined as the function of share price,

Share Price is independent variable, x

DJIA is the dependent variable y

The regression equation is similar to that of the trend line as determined in above questions and the value of a and b can be determined using the least square method.

Equation is y = a + bx

where y = DJIA

and x =share price

a and b represent the same as before

Least Square Method:

Year	Share Price (x)	DJIA (y)	xy	x^2
1	14.75	1046	15428.5	217.5625
2	15.06	1258	18945.48	226.8036
3	14.98	1211	18140.78	224.4004
4	15.73	1546	24318.58	247.4329
5	16.11	1895	30528.45	259.5321
6	16.07	1938	31143.66	258.2449
7	16.78	2168	36379.04	281.5684
8	17.69	2753	48700.57	312.9361
9	16.9	2633	44497.7	285.61
10	17.81	3168	56422.08	317.1961
11	19.08	3301	62983.08	364.0464
Total	180.96	22917	387487.9	2995.333

x̄ = ∑x /n = 180.96/11 = 16.45091

ȳ = ∑y/n = 22917/11 = 2083.364

b = (∑xy – nx̄ȳ) / (∑(x^2)-n (x̄^2)) = (387487.9-11*16.45091*2083.364)/(2995.333-11*16.45091^2) = 570.43

a = ȳ - b x̄ = 2083.364-570.43*16.45091 = -7300.7

Regression model equation:

y = (-7300.7)+570.43x

the value of share price for 12th year as calculated in a is 18.78

forecast for the 12th year = (-7300.7)+570.43*18.78 = 3411.98

Excel Formula

A B с D E F 15 DJIA (y) 1046 16 Year 17 1 18 2 19 3 1258 20 4 Regression Share Price (x) 14.75 15.06 14.98 15.73 16.11 16.07 16.78 17.69 16.9 1211 1546 1895 21 5 1938 22 6 23 7 ху =C17B17 =C18*B18 =C19*B19 =C20*B20 =C21*B21 =C22*B22 =C23*B23 =C24*B24 =C25B25 =C26*B26 =C27*B27 =SUM(D17:D27) x^2 =B1712 =B18^2 =B19^2 =B2012 =B2142 =B2212 =B2342 =B2412 =B2542 =B26^2 =B2712 =SUM(E17:E27) 2168 x bar = =B28/11 y bar = =C28/11 b= = (D28-11*G21*G22)/(E28-11*G21^2) =G22-G23*G21 forecast for year 12 =G24+G23*18.78 a = 24 8 25 9 2753 2633 17.81 26 10 27 11 28 Total 29 19.08 =SUM(B17:B27) 3168 3301 =SUM(C17:C27) 30

d.

MAD and MAPD for the forecasts calculated in b and c

MAD and MAPD for Regression Model:

Year	Share Price	DJIA	Regression Forecast	Absolute Deviation
1	14.75	1046	1113.143	67.1425
2	15.06	1258	1289.976	31.9758
3	14.98	1211	1244.341	33.3414
4	15.73	1546	1672.164	126.1639
5	16.11	1895	1888.927	6.0727
6	16.07	1938	1866.11	71.8899
7	16.78	2168	2271.115	103.1154
8	17.69	2753	2790.207	37.2067
9	16.9	2633	2339.567	293.433
10	17.81	3168	2858.658	309.3417
11	19.08	3301	3583.104	282.1044
	Total	22917	MAD	123.7989
			MAPD	0.059423

The forecast values are calculated by replacing the value of x in the regression equation (derived in c) with the values of the corresponding share price.

MAD = Mean Absolute Deviation = Average of Absolute Deviation values

MAPD = Mean Absolute Percentage Deviation = Sum of Absolute Deviations/Sum of Actual DJIA

Excel Formulas:

A B с D F 1 Year DJIA 1046 2 1 Share Price 14.75 15.06 14.98 3 2 1258 4 3 1211 5 4 15.73 1546 6 5 16.11 1895 7 6 16.07 1938 E Regression Forecast Absolute Deviation = (-7300.7)+570.43*C2 =ABS(E2-D2) = (-7300.7)+570.43*C3=ABS(E3-D3) =(-7300.7)+570.43*04 ABS(E4-D4) =(-7300.7)+570.43*C5 =ABS(E5-05) =(-7300.7)+570.43*C6 =ABS(E6-D6) =(-7300.7)+570.43*C7 =ABS(E7-07) = (-7300.7)+570.43*C8 =ABS(E8-08) = (-7300.7)+570.43*C9ABS(E9-09) = (-7300.7)+570.43*c10 =ABS(E10-D10) =(-7300.7)+570.43*011 =ABS(E11-D11) =(-7300.7)+570.43*C12 =ABS(E12-D12) MAD =AVERAGE(F2:F12) MAPD =SUM(F2:F12)/D13 8 7 2168 9 8 16.78 17.69 16.9 2753 10 9 2633 11 10 17.81 3168 12 11 19.08 3301 =SUM(D2:012) 13 Total 14

MAD and MAPD for Linear Trend Model:

Year	Share Price	DJIA	Linear Trend Forecast	Absolute Deviation
1	14.75	1046	907.56	138.44
2	15.06	1258	1142.72	115.28
3	14.98	1211	1377.88	166.88
4	15.73	1546	1613.04	67.04
5	16.11	1895	1848.2	46.8
6	16.07	1938	2083.36	145.36
7	16.78	2168	2318.52	150.52
8	17.69	2753	2553.68	199.32
9	16.9	2633	2788.84	155.84
10	17.81	3168	3024	144
11	19.08	3301	3259.16	41.84
	Total	22917	MAD	124.6655
			MAPD	0.059839

The forecast values are calculated by replacing the values of x in the trend line equation (derived in b) with the corresponding time period values.

Excel Formulas

N P Q 1 Year DJIA 2 1 Share Price 14.75 15.06 1046 3 2 4 3 14.98 5 4 15.73 1258 1211 1546 1895 1938 6 5 16.11 7 6 16.07 R S Linear Trend Forecast Absolute Deviation = 672.4+235.16*02 =ABS(R2-02) = 672.4+235.16*03 =ABS(R3-Q3) = 672.4+235.16*04 =ABS(R4-24) = 672.4+235.16*05 =ABS(R5-05) = 672.4+235.16*06 =ABS(R6-06) = 672.4+235.16*07 =ABS(R7-07) = 672.4+235.16*08 =ABS(R8-08) = 672.4+235.16*09 =ABS(R9-09) = 672.4+235.16*010 =ABS(R10-Q10) = 672.4+235.16*011 =ABS(R11-Q11) = 672.4+235.16*012 =ABS(R12-Q12) MAD =AVERAGE(S2:512) MAPD =SUM(S2:512)/Q13 8 7 16.78 2168 9 8 17.69 10 9 11 10 11 16.9 17.81 19.08 Total 2753 2633 3168 3301 =SUM(Q2:012) 12 13 14 15 16 17

Though the MAD and MAPD values are very close for both of the forecast models, both the values of regression model are lower as compared to the linear trend model. Hence the regression model is more accurate.