Question

1.     Coyote Cable has been experiencing an increase in cable service subscribers over the last few years due to increased advertising and an influx of new residents to the region. The numbers of subscribers (in 1000's) for the last 16 months are as follows:

?

Month	Sales	Month	Sales	Month	Sales
1	12.8	7	20.6	12	23.8
2	14.6	8	18.5	13	25.1
3	15.2	9	19.9	14	24.7
4	16.1	10	23.6	15	26.5
5	15.8	11	24.2	16	28.9
6	17.2	?	?	?	?

?

Using Linear Trend method, forecast the number of subscribers for months 17, 18, 19, and 20.

SHOW ALL WORK PLEASE.

Answer 1

Let the linear regression equation is :

Y = A + B.t

Y ( dependent variable ) = Forecasted value of sales

T=Serial number of month

A, B are constants

We place the month numbers and sales figures in two adjacent columns in excel and apply the formula LINEST ( ) and we obtain following values of A and B :

A = 11.93

B = 1.004

Therefore ,

Y = A + B.t = 11.93 + 1.004.t

Therefore , number of subscribers for month 17 = 11.93 + 1.004 x 17 = 11.93 + 17.068 = 28.998

Number of subscribers for month 18 = 11.93 + 1.004 x 18 = 11.93 + 18.072 = 30.002

Number of subscribers for month 19 = 11.93 + 1.004 x 19 = 11.93 + 19.076 = 31.006

Number of subscribers for month 20 = 11.93 + 1.004 x 20 = 11.93 + 20.08 = 32.01