Regressions using dates

[petess]

Greetings All,

I have the following array of data, column 1 is the date and column 2 is the
amount in US$. I am trying to use regression to forecast three years or so
hence.

3-Jan-06 750,000
3-Apr-06 1,000,000
27-Apr-06 1,009,778
14-Jul-06 1,139,739
26-Oct-06 1,109,484
5-Dec-06 1,169,886
9-Jan-07 1,244,587
10-Mar-07 1,421,393
28-Mar-07 1,419,597
14-Aug-07 1,642,892

If I use a linear regression trendline on this data, the R^2 is around 90%
which is not bad. If I use the "Forecast Forward Periods" function, this
gives me an idea of when the regression line passes 2,000,000 or 2,500,000.
However, my question is this: what is the correct x-value input to use with
the regression equation

y = 1332.4x - 5E+07

since using the 1900-calendar number clearly does not work.

Many thanks,

Peter SS

 

[Gary''s Student]

With your data in cols A & B, define x to be A1:A10
define y to be Y1:Y10

In A11, enter:

=DATE(YEAR(A10)+3,MONTH(A10),DAY(A10)) the three years hence date

In any two un-used cells, say C1 and C2:
=SLOPE(y,x) in C1 and
=INTERCEPT(y,x) in C2. They will display:

1332.387797
-50781315.47

Finally lets put the projection equation in B11:

=C2+C1*A11 simple linear fit

We end up seeing:

03-Jan-06 750,000 1332.387797
03-Apr-06 1,000,000 -50781315.47
27-Apr-06 1,009,778
14-Jul-06 1,139,739
26-Oct-06 1,109,484
05-Dec-06 1,169,886
09-Jan-07 1,244,587
10-Mar-07 1,421,393
28-Mar-07 1,419,597
14-Aug-07 1,642,892
14-Aug-10 3,052,481

For other models, see:

http://j-walk.com/ss///excel/tips/tip101.htm

 

[petess]

Dear Gary''s Student,

Many thanks for your kind assistance.

As a matter of interest, what "x-value" is being used? Is it the
1900-convention date or something else? My understanding of regression
statistics is not complete.

All the best.

Peter SS