Question

Consider a portion of monthly return data (In %) on 20-year Treasury Bonds from 2006–2010.

Date	Return
Jan-06	5.12
Feb-06	4.14
⋮	⋮
Dec-10	5.47

Date	Return
ene-06	5.12
feb-06	4.14
mar-06	4.68
abr-06	5.25
may-06	5.35
jun-06	3.64
jul-06	4.68
ago-06	4.65
sep-06	3.55
oct-06	3.55
nov-06	4.3
dic-06	3.54
ene-07	3.8
feb-07	3.98
mar-07	4.33
abr-07	4.69
may-07	5.37
jun-07	4.74
jul-07	5.17
ago-07	3.22
sep-07	4.97
oct-07	5.13
nov-07	3.35
dic-07	3.86
ene-08	4.06
feb-08	4.64
mar-08	4.83
abr-08	5.06
may-08	5.46
jun-08	5.22
jul-08	4.29
ago-08	4.79
sep-08	5.45
oct-08	4.85
nov-08	3.54
dic-08	4.9
ene-09	3.6
feb-09	4.48
mar-09	3.51
abr-09	3.72
may-09	4.24
jun-09	4.36
jul-09	5.17
ago-09	3.25
sep-09	4.74
oct-09	5.03
nov-09	5.44
dic-09	3.55
ene-10	4.21
feb-10	5.27
mar-10	5.07
abr-10	3.7
may-10	4.65
jun-10	4.21
jul-10	4.38
ago-10	4.29
sep-10	4.93
oct-10	4.48
nov-10	3.32
dic-10	5.47

Estimate a linear trend model with seasonal dummy variables to make forecasts for the first three months of 2011. (Round intermediate calculations to at least 4 decimal places and final answers to 2 decimal places.)

Year	Month	yˆt
2011	Jan
2011	Feb
2011	Mar

Answer 1

Soln

We will be calculating the seasonality of each Month (ie Jan – Dec) and use it to predict Revenue for 2011 (Jan – Dec)

Steps:

1. First we calculate 4 Period(Months) moving Average.
2. Then we calculate Centred Average of these 4 Period moving Average.
3. Then % Average ie Return/Centred Average
4. Then we table the % Average for each Month (1-12) for the five years and calculate mean for each Month. (As Shown in Table 2)
5. 1200%/The total of these 12 Period (Months) mean will be the x Adjustment Factor
6. Seasonality Index for each Month will be X Adj Factor * Month’s Mean ie Seasonality Index for Jan = 93.5%*1 = 0.93
7. Then we calculate ∑X, ∑Y, ∑XY, ∑X2 and using these values we calculate the regression equation.
8. Regression Equation will be: Y = 4.4392 + 0.0005*X
9. Using this equation and Seasonality Index, we calculate value for Predicted Revenue for next Year. Eg: Jan (2011) Return = (4.4392 + 0.0005*61)*0.93

Table 1

Table 2

Season	2006	2007	2008	2009	2010	Mean	X ADJ Factor	SEASONAL INDEX	Cumulative Indx	2011 Return (Predicted)		Regression Equation: Y = a + bX
Jan		97.2%	97.5%	87.2%	92.1%	93.5%	1.00	0.93	61	4.18		n	60
Feb		98.1%	103.2%	112.7%	116.0%	107.5%	1.00	1.07	62	4.80		b	0.0005
Mar	97.0%	98.5%	100.2%	89.8%	109.8%	99.0%	1.00	0.99	63	4.42		a	4.4392
Apr	109.5%	100.1%	99.8%	93.6%	81.5%	96.9%	1.00	0.97	64	4.33
May	113.1%	109.9%	107.6%	101.8%	107.6%	108.0%	1.00	1.08	65	4.82		∑ XY	8,159
Jun	78.2%	98.6%	105.0%	101.1%	97.7%	96.1%	1.00	0.96	66	4.29		∑ X2	73,810
Jul	107.5%	113.0%	86.9%	119.7%	99.2%	105.2%	1.00	1.05	67	4.70		∑ X	1,830
Aug	112.9%	70.4%	97.9%	72.8%	95.6%	89.9%	1.00	0.90	68	4.02		∑ Y	267
Sep	87.4%	113.1%	114.7%	103.5%	112.4%	106.2%	1.00	1.06	69	4.75
Oct	91.6%	120.8%	103.8%	108.1%	101.8%	105.2%	1.00	1.05	70	4.70
Nov	114.2%	79.5%	79.5%	117.7%		97.7%	1.00	0.98	71	4.37
Dec	91.9%	95.6%	117.3%	77.4%		95.6%	1.00	0.95	72	4.27
					Total	1200.9%

Final Predicted Values:

Season	2011 Return (Predicted)
Jan	4.18
Feb	4.80
Mar	4.42
Apr	4.33
May	4.82
Jun	4.29
Jul	4.70
Aug	4.02
Sep	4.75
Oct	4.70
Nov	4.37
Dec	4.27

PS: If Images are not visible, Right click on image and select open image in new tab option.