Regressions using dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

petess - 16 Sep 2007 12:02 GMT

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

Reply to this Message

Gary''s Student - 16 Sep 2007 12:44 GMT

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

Signature

Gary''s Student - gsnu200745

> Greetings All,
>
[quoted text clipped - 26 lines]
>
> Peter SS

Reply to this Message

petess - 16 Sep 2007 15:08 GMT

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

> With your data in cols A & B, define x to be A1:A10
> define y to be Y1:Y10
[quoted text clipped - 62 lines]
> >
> > Peter SS

Reply to this Message