I have a database of about 2000 entries each with about 25 data
points. They are each dated by month for about the past year. I use
this data to create a linear regression model. I have analyzed the
data as a group and then analyzed the most recent month. I have
discovered the r squared value is higher for the most recent data. So
I began testing models where I added another month and tested again.
Conclusion: the older the data, the less predictable the model.
However, I would like to include ALL of the data and weight the older
data as less important. How do I determine weights for the older data
(sequentially, the older the data the less weighting) where most
recent period = 100. Then how do I incorporate this data into a linear
regression model with weighted data used. I am a statistical novice
using Analyze-It for Excel.

---

Clarification of Question by keithp-ga on 24 Aug 2004 13:38 PDT

--I have: observations of different sales transactions (first
dimension) over time (second dimension) with other dimensions - about
25 in all.

When you run a regression do you pool all data (i.e. aggregate all
individuals/firms) or do you run separate regressions for all
individuals/firms? In the former case you might not get consistent
estimates (i.e. your results may be wrong), unless some additional
assumptions hold (for instance, all individuals/firms must have
similar means). I am not sure, however, whether you can run panel data
estimation with Excel's Analyze-It...

----Currently, I run separate regressions for each month to create a
sales forecast formula. The mean has been nearly identical across all
samples. Here is my dilemma - hypothetically,

-I use data from July, where n=500, only and get .80 r-squared.
-If I add in June data then run the model with June and July (n=1000),
I get r-squared of .70
-If I repeat this process of adding in months of data, the sample size
increases, but the r-squared decreases
-Theoretically, if I use the past 12 months of data, adding a new
month and removing the oldest month, the regression model from month
to month results in fewer fluctuations for Y as a result
-This tells me that I would like to use older data to increase the
sample size, but weight the newest data more and the older data less
to reduce the fluctuations from month to month and create a more
consistent model. But how do I determine the weight for each month and
how do I incorporate that into the regression model?

---

Clarification of Question by keithp-ga on 24 Aug 2004 15:49 PDT

Your stat terminology is above my level of experience. My situation: I
am trying to create a forecasting model. Linear regression may not be
ideal, nor might Excel with Analyse-It.com add-in. But it's all I know
and can afford. While I appreciate the suggestions, even these seem to
much for a casual stat person like me. Are there tools that are
simple/guided to use that can create stats for the non-stat person and
inexpensive. Also, if not linear regression for modeling, then what
are the other options?

here are some of my stats and the graph looks like a bell curve:
Source of variation 	SSq	DF	MSq	F	p
Due to regression 	1624672397.7	24	67694683.2	2.84	<0.0001
About regression 	9045471601.6	379	23866679.7		
Total 	10670143999.2	403

To provide an answer some more information about your data would be
helpful. If I understood correctly you have a panel, i.e. observations
of different individuals or firms (first dimension) over time (second
dimension).

When you run a regression do you pool all data (i.e. aggregate all
individuals/firms) or do you run separate regressions for all
individuals/firms? In the former case you might not get consistent
estimates (i.e. your results may be wrong), unless some additional
assumptions hold (for instance, all individuals/firms must have
similar means). I am not sure, however, whether you can run panel data
estimation with Excel's Analyze-It...

Mhmmm. It seems to me what rather than weighting the data you should
maybe re-consider the model you are estimating. If the fit of the
model worsens this may point to (i) a structural break in the data,
i.e. you cannot really compare the first x observations with the last
y, unless you accout for (e.g.) a change in the external environment -
or (ii) the residuals are not normally distributed. In essence, the
classical linear regression assumes that the residuals - i.e. the
unexplained part of your data - are normally distributed, but your
residuals are probably heteroskedastic (i.e. increasing/decreasing
over time). In that case you should estimate a different model.

Given these considerations I am not sure Excel is the right tool to
analyse the data. Stata (www.stata.com) or Eviews (www.eviews.com),
two popular statistical software packages, seem more appropriate.